登录注册写文章

MySQLday03（DCL 事务权限管理）

MySQLday03（DCL 事务权限管理）

MySQL_day03

多表查询

查询语法

select 列名列表

from 表名列表

where 条件

group by 分组

having 条件

order by asc或者desc

limit 分页
笛卡尔积

假如有A集合和 B集合，取A集合和B集合的所有组合情况

消除无用的数据（根据一定规则）
多表查询分类
1. 内连接查询
  - 隐式内连接
    
    使用where条件来消除无用的数据
    
    例如；
    
    select * from emp,dept where emp.dept_id = dept.id;
    
    查询员工表名称、性别、部门表名称
    
    select emp.name , emp.gender ,dept.name where emp.dept_id = dpet .id;
    
    标准格式：
    
    select
    
    t1.name, -- 员工表的姓名
    
    t1.gemder, --员工表的性别
    
    t2.name -- 部门表的姓名
    
    from
    
    emp t1,
    
    dept t2
    
    where
    
    t1.dept_id = t2.id;
  - 显示内连接
    
    语法：
    
    select 字段列表 from 表名1 innner join 表名2 on 条件 可以省略inner
    
    注意：
    
    从哪些表中查询数据
    
    查询条件是什么
    
    查询哪些字段
    
    内连接就是查询交集部分
2. 外连接查询
  - 左外连接
    
    语法：
    
    select 字段列表 from 表1 left [outer] join 表2 on 条件;
    
    查询所有员工信息，如果有部门则查询部门名称，没有部门，不显示名称
    
    使用左外连接
    
    select
    
    t1.*,t2.name
    
    from
    
    emp t1 left join dept t2
    
    on
    
    t1.dpet_id = t2.id;
    
    左外连接是坐标所有数据以及其交集部分
  - 右外连接
    
    语法：
    
    select 字段列表 from 表1 right [outer] join 表2 on 条件;
3. 子查询
  - 概念：
    
    查询中嵌套查询，称嵌套查询为子查询
  - 示例：
    
    查询工资最高的员工信息
    
    select max(salary) from emp; -----> 9000
    
    select * from emp where emp.salary = 9000;
    
    一步：
    
    select * from emp where emo.salary = (select max(salary) from emp);
  - 子查询情况_结果是单行单列
    
    子查询可以作为条件，使用运算符(> >= < <= =)去判断
    
    查询员工工资小于平均工资的人
    
    select * from emp where emp.salary < (select avg(salary) from emp);
  - 子查询情况_结果是多行单列
    
    使用运算符 in
    
    查询财务部和市场部的员工信息
    
    select * from emp where dept_id in (select id from dept where name = '市场部' or name = '财务部');
  - 子查询情况_结果是多行多列
    
    子查询可以作为一张虚拟表来进行表的查询
    
    查询员工日期是2011-11-11日后的员工信息和部门信息
    
    select * from dept t1,(select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id;
    
    普通内连接：
    
    select * from emp t1,dept t2 where t1.dept_id = t2.id and t1.join_date > '2011-11-11';
4. SQL练习
  
  表架构图
image.png

案例一：

image.png

案例二：

image.png

案例三：

image.png

image.png

案例四：

image.png

image.png

事务

事务基本介绍
- 概念：
  
  如果一个包含多个步骤的业务操作被事务管理，这些操作要么同时成功要么同时失败
- 三个最重要的操作
  - 开启事务
    
    start transaction;
  - 回滚
    
    rollback;
  - 提交事务
    
    commit;
示例：

image.png
- 开启事务
  
  start transaction;
- 张三账户 -500
  
  update account set balance = balance - 500 where name = 'zhangsan';
- 李四账户 +500
  
  update account set balance = balance + 500 where name = 'lisi';
- 根据是否出错提交/回滚
  
  commit; / rollback;
  
  当事务未提交时，变更的数据是临时数据，没有持久化
- 在mysql数据库中事务默认自动提交
  - 事务提交的两种方式：
    - 自动提交
      - mysql就是自动提交的
      - 一条DML语句会自动提交事务（持久化变更）
    - 手动提交
      - Oracle默认手动提交事务
      - 需要先开启事务，再提交
  - 修改事务的默认提交方式
    - 查看事务的默认提交方式
      
      select @@autocommit;
      
      -- 1 代表自动提交
      
      -- 0 代表手动提交
    - 修改默认提交方式
      
      set @@autocommit = 0;
事务四大特征
- 原子性
  
  是不可分割的最小操作单位，要么同时成功要么同时失败
- 一致性
  
  事务操作前后，数据总量不变
- 隔离性
  
  多个事务之间，相互独立
- 持久性
  
  当事务提交/回滚后，数据库将持久化的保存数据
事务的隔离级别
- 概念：
  
  多个事务之间是隔离（相互独立）的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不用的隔离级别可以解决这些问题
- 存在的问题：
  - 脏读
    
    一个事务读取到另一个事务中没有提交的数据
  - 不可重复读（虚读）
    
    在同一个事务两次读取到的数据不一样
  - 幻读
    
    一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
- 隔离级别：
  - read uncommitted：读未提交
    - 产生的问题：脏读、不可重复读、幻读
  - read commited：读已提交
    - 产生的问题：不可重复读、幻读
  - repeatable read：可重复读
    - 产生的问题：幻读
  - serializable：串行化
    - 可以解决所有的问题
  - 注意：隔离级别从小到大，安全性越来越高，但是效率越来越低
  mysql 默认隔离级别 repeatable read
  
  oracle 默认隔离级别 read commited
  - 数据库查询隔离级别
    
    select @@tx_isolation;
    
    在mysql8中使用select @@transaction_isolation;
  - 数据库设置隔离级别
    
    set global transaction isolation level 级别字符串;
演示隔离级别

张三向李四借500
1. 设置读未提交 ----> 演示脏读
  
  set global transaction isolation lecvel read uncommited;
image.png
1. 开启事务
  
  start transaction;
2. 转账操作
  
  张三加500
  
  update account set balance = balance + 500 where name = 'zhangsan';
  
  李四减500
  
  update account set balance = balance - 500 where name = 'lisi';
  
  image.png
3. 在新事务中可以查看到数据的变化
4. 在事务回滚后，数据又会变化回去
5. 出现脏读
  
  即新事物中读取到了另一个事务未提交的数据
6. 解决方案
  
  提高隔离级别
image.png
```
`set global transaction isolation level read committed;`

虽然解决了脏读问题，但是还是有不可重复读问题

![image.png](https://upload-images.jianshu.io/upload_images/22778943-7b8abca25187828b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

> 读取到了另一个事务已提交的数据（本事务未提交）
```
1. 解决不可重复度
  
  提高隔离级别
  
  set global transavtion isolation level reapeatable read;
  - 解决了脏读
  image.png
  - 解决了不可重复读
  image.png
  
  在事务提交后才可以查询到另一个事务修改后的数据
  
  image.png

DCL控制权限和管理用户

SQL分类：
1. DDL：操作数据库和表的
2. DML：对数据增删改
3. DQL：查询表中数据
4. DCL：管理用户，授权
DCL：
1. 管理用户
  - 添加用户
    
    使用自带的DCL语法：
    
    创建当前主机的用户
    
    create user '用户名' @ 'localhost' identified by '密码';
    
    创建任意用户都能访问：
    
    create user '用户名' @ '%' identified by '密码';
  - 删除用户
    
    drop user '用户名' @ '主机名';
  - 修改用户密码
    
    例如：修改lisi密码为abc
    
    update user set password = password('新密码') where user = '用户名';
    
    set password for '用户名' @ '主机名' = password('新密码');
    
    在mysql8 中改密码方式：
    
    ALTER USER 'zhangsan'@'%' IDENTIFIED WITH MYSQL_Native_password BY 'abc';
    
    在mysql中忘记了root用户密码
    1. cmd ---> net stop mysql 停止mysql的服务（需要管理员运行命令提示符）
    2. 启动mysql服务，使用无验证方式启动
      
      mysqld --skip-grant-tables
    3. 打开新的命令提示符直接键入mysql命令敲回车就可以登录成功
    4. use mysql
    5. update user set password = password = '新密码';
    6. 关闭两个窗口
    7. 打开任务管理器，手动结束mysqld.exe进程
    8. 启动mysql服务
    9. 使用新密码登录
  - 查询用户
  image.png
  
  用户数据存放在mysql数据库的user表
  
  切换到mysql数据库
  
  use mysql;
  
  查询user表
  
  select ^ from user;
  
  其中：
  - localhost：当前主机
  - 通配符% 表示：可以在任意主机使用用户登陆数据库（远程登陆）
2. 权限管理
  - 查询权限
    
    show grants for '用户名' @ '主机名';
  - 授予权限
    
    grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
  image.png
  
  授予zhangsan用户所有数据库中所有表的所有权限
  
  grant all on *.* to 'zhangsan' @ 'localhost';
  - 撤销权限
    
    revoke 权限列表 on 数据库名.表名 from '用户名' @ '主机名';

©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

Oracle总结【视图、索引、事务、用户权限、批量操作】
前言在Oracle总结的第一篇中，我们已经总结了一些常用的SQL相关的知识点了...那么本篇主要总结关于Oral...
Java3y阅读 3,696评论 0赞 9
MYSQL多表事务
1. 多表查询 2. 事务 3. DCL ## 多表查询： * 查询语法： select 列名列表 from 表名...
吕游_b601阅读 3,751评论 0赞 0

MySQL-数据库管理与事务
数据库管理新建数据库示例数据类型数值类型日期类型字符串类型特殊字符序列约束重要，记下非空约束（...
艾欧恩阅读 4,862评论 0赞 0
MySQL多表&事务学习笔记
笔记大纲多表查询事务 DCL 多表查询查询语法准备数据笛卡尔积有两个集合A,B .取这两个集合的所有组成...
h2coder阅读 1,445评论 0赞 0
2019-05-16多表查询和事务操作
内连接隐式内连接看不到Join关键字，条件使用where指定语法：select 列名，列名...from 表...
纪绘阅读 2,241评论 0赞 0

友情链接更多精彩内容

赞1赞

赞赏

手机看全文