MySQL 多表查询与事务的操作
表连接查询
什么是多表查询
# 数据准备
# 多表查询的作用
* 比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询
# 如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中，每张表取 1 列或多列
# 多表查询的分类
笛卡尔积现象
# 什么是笛卡尔积现象？
# 如何消除笛卡尔积现象的影响
* 我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据
内连接
用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键 = 主表.主键
# 隐式内连接
* 看不到 JOIN 关键字，条件使用 WHERE 指定
# 显示内连接
* 使用 INNER JOIN ... ON 语句，可以省略 INNER
# 内连接查询的步骤：
* 确定查询哪些表
* 确定表连接的条件
* 确定查询的条件
* 确定查询的字段
左外连接
# 使用 LEFT OUTER JOIN ... ON 语句，OUTER 可以省略
* 用左表的记录去匹配右边表的记录，如果符合条件的则显示，否则，显示 NULL
* 可以理解为：在内连接的基础上保证坐标的数据全部显示
右外连接
# 使用 RIGHT OUTER JOIN ... ON 语句，OUTER 可以省略
* 用右边表的记录去匹配左边表的记录，如果符合条件的则显示
* 可以理解为：在内连接的基础上保证右表的数据全部显示
子查询
什么是子查询
# 子查询的概念：
* 一个查询的结果作为另一个查询的条件
* 有查询的嵌套，内部的查询称为子查询
* 子查询要用括号
子查询结果的三种情况
# 子查询的结果是单行单列的
# 子查询的结果是多行单列的
# 子查询的结果是多行多列的
子查询的结果是一个值的时候
# 子查询的结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询一般使用比较运算符
# 案例：查询工资最高的员工是谁
# 案例：查询工资低于平均工资的员工有哪些
子查询的结果是多行单列的时候
# 子查询的结果是单列多行，结果集类似于一个数组，父查询使用 IN 运算符。
# 案例：查询工资大于 5000 的员工来自哪些部门
# 案例：查询开发部与财务部所有的员工信息
子查询的结果是多行多列的
# 子查询的结果只要是多列的，肯定在 FROM 后面作为表
* 子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
# 案例：查询 2011 年后入职的员工信息，包括部门名称
* 使用表连接完成
子查询小结
# 子查询结果只要是单列，则在 WHERE 后面作为条件
# 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询
事务
事务的应用场景说明
# 什么是事务：在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 语句可能执行失败。
# 事务的执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有一条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。
# 转账操作
* 模拟张三给李四转 500 元钱，一个转账业务至少要执行下面的 2 条语句：
* 张三账号 - 500
* 李四账号 + 500
* 假设当张三账号上 -500 元操作发生后，服务器立刻崩溃了，李四的账户并没有 +500，那么此时数据就出现问题了。
手动提交事务
# MySQL 中可以有两种方式进行事务的操作：
* 手动提交事务
* 自动提交事务
# 手动提交事务的 SQL 语句
# 手动提交事务使用过程
* 执行成功的情况：开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
* 执行失败的情况：开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
# 案例演示：事务提交
* 模拟张三给李四成功转账 500 元钱
1) 使用 DOS 控制台进入 MySQL
2) 执行以下 SQL 语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500
3) 使用 SQLYog 查看数据库：发现数据并没有改变
4) 在控制台执行 commit 提交事务
5) 使用 SQLYog 查看数据库：发现数据改变 
# 案例演示：事务回滚
* 模拟张三给李四失败地转账 500 元钱
1) 在控制台执行以下 SQL 语句：1.开启事务， 2.张三账号-500
2) 使用 SQLYog 查看数据库：发现数据并没有改变
3) 在控制台执行 rollback 回滚事务：
4) 使用 SQLYog 查看数据库：发现数据没有改变 
* 总结：如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库的数据进行改变。如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态
自动提交事务
# MySQL 默认每一条 DML（增删改）语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务
# 案例演示：自动提交事务
1) 将金额重置为 1000
2) 更新其中某一个账户
3) 使用 SQLyog 查看数据库：发现数据已经改变
# 取消自动提交
* 查看 MySQL 是否开启自动提交事务
@@表示全局变量，1 表示开启，0 表示关闭
* 取消自动提交事务
* 执行更新语句，使用 SQLyog 查看数据库，发现数据并没有改变
* 在控制台执行 commit 提交任务
事务原理
# 事务开启之后，所有的操作都会临时保存到事务的日志中，事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志（rollback，断开连接）
# 原理图
# 事务的步骤
* 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
* 开启事务以后，所有的操作都会先写入到哦临时日志文件中
* 所有的查询操作从表中查询，但会经过日志文件加功后才返回
* 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件
回滚点
# 什么是回滚点
* 在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称为回滚点。
# 回滚点的操作语句
# 具体操作
1）将数据还原到 1000
2）开启事务
3）让张三账号减3次钱，每次10块
4）设置回滚点：savepoint three_times;
5）让张三账号减4次钱，每次10块
6）回到回滚点：rollback to three_times;
7）分析执行过程
# 总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候
事务的隔离级别
# 事务的四大特性 ACID
# 并发访问的问题
* 事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据可能引发并发访问的问题：
# MySQL 数据库有四种隔离级别
* 上面的级别最低，下面的级别最高。
* 隔离级别越高，性能越差，安全性越高。
# MySQL 事务隔离级别相关的命令
* 查询全局事务隔离级别
* 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
# 脏读 的演示
* 将数据进行恢复：UPDATE account SET balance = 1000;
1）打开 A 窗口登录 MySQL，设置全局的隔离级别为最低
2）打开 B 窗口，A B 窗口都开启事务
3）A 窗口更新 2 个人的账户数据，未提交
4）B 窗口查询账户
5）A 窗口回滚
6) B 窗口查询账户，钱没了
# 解决脏读的问题：将全局的隔离级别升级为 read committed
* 将数据进行恢复：UPDATE account SET balance = 1000;
1）在 A 窗口设置全局的隔离级别为 read committed
2）B 窗口重新登录 MySQL
3） 在 A B 窗口同时开启事务
4）A 更新 2 个人的账户，未提交
5）B 窗口查询账户
6）A 窗口 commit 提交事务
7）B 窗口查看账户
* 结论：read committed 的方式可以避免脏读的发生
# 不可重复读的演示
* 将数据进行恢复：UPDATE account SET balance = 1000;
1）开启 A 窗口
2）开启 B 窗口，在 B 窗口开启事务
3）在 A 窗口开启事务，并更新数据
4）B 窗口查询
# 解决不可重复的读的问题：将隔离级别提升为 repeatable read
* 将数据进行恢复：UPDATE account SET balance = 1000;
1）A 窗口设置隔离级别为：repeatable read
2）B 窗口退出 MySQL，B 窗口再进入 MySQL
3）A 窗口更新数据
4）B 窗口查询
* 结论：统一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离
# 幻读 的演示
* 在 MySQL 中无法看到幻读的效果，但我们可以将事务隔离级别设置到最高，以挡住幻读的发生，将数据进行恢复
* 将数据进行恢复：UPDATE account SET balance = 1000;
1）开启 A 窗口
2）A 窗口退出 MySQL，A 窗口重新登录 MySQL
3）再开启 B 窗口，登录 MySQL
4）在 B 窗口中开启事务，添加一条记录
5）在 A 窗口中 commit 提交事务，B 窗口中的 insert 语句会在 A 窗口事务提交后马上运行
6）在 A 窗口中接着查询，发现数据不变
7）B 窗口中 commit 提交当前事务
8）A 窗口就能看到最新的数据
* 结论：使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读
DCL（Data Control Language）
创建用户
# 语法
* CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 关键字说明
# 具体操作
* 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
* 创建 user2 用户，可以在任何电脑上登录 mysql 服务器，密码为123
# 创建的用户名都在 mysql 数据库中的 user 表中可以查看到，密码经过加密
给用户授权
# 语法：
* GRANT 权限1，权限2，... ON 数据库.数据表 TO ‘用户名’@‘主机名’;
# 关键字说明
# 具体操作：
* 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
* 给 user2 用户分配所有权限，对所有数据库的所有表
撤销权限
# 语法：
* REVOKE 权限1，权限2，... ON 数据库.表名 FROM '用户名'@'主机名';
# 关键字说明
# 具体操作
* 撤销 user1 用户对 test 数据库所有表的操作权限
查看权限
# 语法：
* SHOW GRANTS FOR '用户名'@'主机名';
# 具体操作：
* 查看 user1 用户的权限
# usage 是指连接（登录）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）
删除用户
# 语法：
* DROP USER ‘用户名’@‘主机名’;
# 具体操作
* 删除 user2
修改管理员密码
# 语法1：
* 管理员权限下 cmd 中输入 net stop mysql 关闭mysql服务
* 使用无验证方式启动 mysql服务：mysql --skip-grant-tables
* 打开新的cmd，输入 mysql，可以直接登录
* 修改密码
* 手动结束 mysqld.exe，并且启动 mysql 服务
# 语法2：
* mysqladmin -uroot -p password 新密码（前提是知道当前密码）
# 具体操作
* 将 root 管理员的新密码改为 123456
修改普通用户密码
# 语法：
* SET PASSWORD FOR ‘用户名’@‘主机名’=password('新密码');
* 或者 UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
* 需要在 MySQL 登录的情况下操作
# 具体操作：
* 将 user1@localhost 用户的密码改成 666666
