MySQL——多表查询、事务、DCL

1. 多表查询

1.1.查询语法

select
        要查询的字段列表
from
        表名列表
where. . . .

1.2.多表查询的分类

• 笛卡尔积：由集合A和集合B，取两个集合的所有组成情况称为笛卡尔积
• 做多表查询要消除无用的笛卡尔积

1.2.1.内连接查询

• 隐式内连接：使用where条件消除无用数据
• 显示内连接：

1.语法：select 字段列表 from 表名1 inner join 表名2 on 条件
2.inner可以省略

• 内连接查询注意事项：
  从哪些表中查询数据
  条件是什么
  查询哪些字段

1.2.2.外连接查询

• 左外连接：查询的是左表所有数据以及其交集部分

1.语法：select 字段列表 from 左表 left outer join 右表 on 条件;
2.outer可以省略

• 右外链接：查询的是右表所有数据以及其交集部分

1.语法：select 字段列表 from 左表 right outer join 右表 on 条件;
2.outer可以省略

1.2.3.子查询

• 子查询：循环中嵌套查询，我们称嵌套的这个查询为子查询
• 子查询的不同情况

1.子查询的结果是单行单列的

• 子查询可作为条件，使用运算符（>、<、=等）去判断

2.子查询的结果是多行单列的

• 子查询可作为条件，使用运算符IN去判断

3.子查询的结果是多行多列的

• 子查询可以作为一张虚拟表来进行表的查询

2.事务

2.1.事务的基本介绍

2.1.1.事务概念

事务：如果一个包含多个步骤的业务操作被事务管理（要被事务管理首先要开启事务），那么这些操作要么同时成功，成功就提交，要么同时失败，失败就回滚

比如：张三给李四转账500

• 1.查询张三账户金额多余500
• 2.张三账户金额减500
• 3.李四账户金额加500

如果被事务管理，三个操作变成一个整体，要么同时成功，要么同时失败，就可以防止一些异常情况，比如第二步操作完出现问题，中断整个事件进行，那么张三账户少了500李四账户却没多500，就不合理了

2.1.2.事务操作步骤

• 开启事务：start transaction
• 提交事务：commit（手动提交）
• 回滚事务：roll back

2.1.3.事务提交

1.事务提交分类：

• 手动提交：要先开启事务，然后使用commit提交
• 自动提交：在MySQL数据库中，一条DML（增删改）会默认自动提交一次事务，数据会持久化更新

2.查看事务的默认提交方式：select @@autocommit;

3.修改事务提交方式：set @@autocommit = 0;

• 注意：修改为手动提交以后，修改了数据一定要commit才能生效，否则一关闭图形化窗口后再打开数据就会回到原来的状态

2.2.事务的四大特征

• 原子性：是不可分隔的最小操作单位，要么同时成功，要么同时失败
• 持久性：如果事务一旦提交或者回滚，数据库会持久化的保存数据
• 隔离性：多个事务之间相互独立（隔离），互不影响
• 一致性：事务操作前后数据总量不变。比如转账，装完两人账户总金额还是一样的

2.3.事务的隔离级别

2.3.1.隔离级别概念

事务的隔离级别：多个事务之间是相互独立的，如果多个事务操作同一批数据就会造成一些问题，通过设置不同的隔离级别就能解决这些问题

2.3.2.事务操作数据时的一些问题

• 脏读：是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据
• 不可重复读：是指在一个事务内，多次读同一数据，在这个事务还没有结束时，另外一个事务也访问该同一数据并做了修改，那么在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的的数据可能是不一样的。这样就导致了在一个事务内两次读到的数据不一样，因此称为是不可重复读，即不能读到相同的数据内容
• 幻读（虚读）：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象自己修改了全部数据行是发生了幻觉一样

2.3.3.四种隔离级别

• 读未提交：read uncommitted，产生问题：脏读、不可重复读、幻读
• 读已提交：read committed，产生问题：不可重复读、幻读
• 可重复读：repeatable read，产生问题：幻读（MySQL默认）
• 串行化：serializable，不产生问题

隔离级别越高，安全系数越高，但是效率越来越低，所以我们要选择相对合适的隔离级别，既保证安全性又保证效率

2.3.4.查询隔离级别

select @@tx_isolation;

2.3.5.修改隔离级别

set global transaction isolation level 级别字符串;

• 修改隔离级别之后关闭SQLyog再重新进入就能生效

3.DCL：管理用户，授权

3.1.管理用户

• 添加用户：create user ‘用户名’@‘主机名’ identified by ‘密码’;
• 删除用户：drop user ‘用户名’@‘主机名’;
• 修改用户密码

• 方法一：update user set password = password(‘新密码’) where user = ‘用户名’;
• 方法二：set password for ‘用户名’@‘主机名’ = password(‘新密码’);

！！！mysql中忘记了root用户的密码
1.以管理员身份运行cmd，net stop mysql，停止mysql服务
2.使用无验证方式启动mysql服务：mysqld --skip-grant-tables，回车，这时打开新的cmd窗口，输入mysql回车，就可以登陆成功
3.use mysql，回车，然后update user set password = password(‘新密码’) where user = ‘root’，修改密码
4.关闭两个窗口，打开任务管理器，手动结束mysql.exe的进程
5.启动mysql服务，使用新密码登录

• 查询用户

1.* 切换到MySQL数据库：use mysql;
2.* 查询user表：select * from user;
注意：通配符“%”表示可以在任意主机使用用户登录数据库

3.2.权限管理

• 查询权限：show grants for ‘用户名’@‘主机名’;
• 授予权限：grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
• 撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;