Mysql入门【MySQL多表查询与事务】

回顾

排序语句

关键字： ORDER BY [ASC|DESC]
升序或降序 asc desc

聚合函数

聚合函数	作用
sum	求和
count	统计数量
max	获取最大值
min	获取最小值
avg	获取平均值

分页查询

SELECT 字段名 FROM 表名 LIMIT 跳过的数量， 获取的数量;

分组查询

SELECT 字段名 FROM 表名 GROUP BY 字段;
通常是分组后跟上聚合函数

约束的关键字

约束名	约束关键字
主键	PRIMARY KEY 非空，唯一
唯一	UNIQUE
非空	NOT NULL
默认	DEFAULT 默认值
外键	FOREIGN KEY

表与表之间的关系

关系	维护
一对多，多对一	通过在多方建立外键
多对多	添加中间表，分别建立外键

学习目标

1. 能够使用内连接进行多表查询
2. 能够使用左外连接和右外连接进行多表查询
3. 能够使用子查询进行多表查询
4. 能够理解多表查询的规律
5. 能够理解事务的概念
6. 能够说出事务的原理
7. 能够在MySQL中使用事务
8. 能够理解脏读,不可重复读,幻读的概念及解决办法

1. 多表查询介绍

目标

了解什么是多表查询，及多表查询的两种方式

讲解

什么是多表查询

同时查询多张表获取到需要的数据
比如：我们想查询到开发部有多少人，需要将部门表和员工表同时进行查询

多表查询的分类

小结

什么是多表查询？

通过查询多张表获取我们想要的数据

---

2. 笛卡尔积现象

目标

能够说出什么是笛卡尔积，以及如何消除笛卡尔积

讲解

数据准备

CREATE DATABASE day03;
USE day03;

-- 创建部门表
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1);

什么是笛卡尔积现象

需求：查询每个部门有哪些人

具体操作：

SELECT * FROM dept, emp;

以上数据其实是左表的每条数据和右表的每条数据组合。左表有3条，右表有5条，最终组合后3*5=15条数据。

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积

如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

SELECT * FROM dept, emp WHERE emp.`dept_id`=dept.`id`; 

小结

1. 能够说出什么是笛卡尔积?
   左表的每条记录和右表的每条记录会组合起来
2. 如何消除笛卡尔积
   只查询满足要求的数据，通常都是外键等于主键

3. 内连接

目标

能够掌握内连接的使用

讲解

什么是内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示

隐式内连接

隐式内连接：看不到JOIN关键字，条件使用WHERE指定

SELECT 字段 FROM 左表, 右表 WHERE 条件;

显示内连接

显示内连接：使用左边 INNER JOIN 右表 ON语句, 可以省略INNER

SELECT 字段 FROM 左表 INNER JOIN 右表 ON 条件；
SELECT 字段 FROM 左表 JOIN 右表 ON 条件；

【练习】

• 查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称

  【分析】我们发现需要联合2张表同时才能查询出需要的数据，我们使用内连接

【实现步骤】

1. 确定查询哪些表

   SELECT * FROM dept INNER JOIN emp;
   

2. 确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的

   SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id`;
   

3. 确定查询条件，我们查询的是唐僧的信息，部门表.name=‘唐僧’

   SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id` AND emp.`NAME`='唐僧';
   

4. 确定查询字段，查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称

   SELECT emp.`id`, emp.`NAME`, emp.`gender`, emp.`salary`, dept.`NAME` FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id` AND emp.`NAME`='唐僧';
   

5. 我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名

   SELECT e.`id` 员工编号, e.`NAME` 员工姓名, e.`gender` 性别, e.`salary` 工资, d.`NAME` 部门名称 FROM dept d INNER JOIN emp e ON e.`dept_id`=d.`id` AND e.`NAME`='唐僧';
   

小结

1. 什么是隐式内连接和显示内连接？
   隐式内连接：看不到JOIN：SELECT 字段 FROM 左表, 右表 WHERE 条件；
   显示内连接：看得到JOIN：SELECT 字段 FROM 左表 INNER JOIN 右表 ON 条件；

2. 内连接查询步骤？
   1.确定查询几张表
   2.确定表连接条件
   3.根据需要在操作

3. 内连接查询结果图解

4. 左外连接

目标

能够掌握左外连接查询

讲解

【语法格式】

左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略

SELECT 字段名 FROM 左表 LEFT OUTTER JOIN 右表 ON 条件;

左外连接可以理解为：在内连接的基础上保证左表的数据全部显示，右表中没有对应的记录，使用NULL填充。

【练习】

• 在部门表中增加一个销售部

  INSERT INTO dept (NAME) VALUES ('销售部');
  

• 使用内连接查询

  SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id`;
  

• 使用左外连接查询

  SELECT * FROM dept LEFT OUTER JOIN emp ON emp.`dept_id`=dept.`id`;
  

小结

1. 掌握左外连接查询格式？

   SELECT 字段 FROM 左表 LEFT OUTER JOIN 右表 ON 条件
   

2. 左外连接查询特点？
   在满足要求的基础上保证左表的数据全部显示
   在内连接的基础上保证左表的数据全部显示

3. 左外连接查询结果图解

5.右外连接

目标

能够掌握右外连接查询

讲解

右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略

SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.`dept_id`=dept.`id`;

右外连接可以理解为：在内连接的基础上保证右表的数据全部显示，左表中没有对应的记录，使用NULL填充。

【练习】

• 在员工表中增加一个员工

  INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2013-02-24',NULL);
  

• 使用内连接查询

  SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id`;
  

• 使用右外连接查询

  SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.`dept_id`=dept.`id`;
  

小结

1. 掌握右外连接查询格式？

   SELECT 字段 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件;
   

2. 右外连接查询特点？
   在满足要求的基础上,保证右表的数据全部显示.

3. 右连接查询结果

6.子查询

目标

能够掌握子查询的概念

能够理解子查询的三种情况

讲解

什么是子查询

一条查询语句结果作为另一条查询语法一部分。

SELECT 查询字段 FROM 表 WHERE 条件;

SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

子查询需要放在（）中

子查询结果的三种情况

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

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

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

小结

1. 什么是子查询？
   一个查询的结果作为另一个查询语句的一部分
2. 子查询结果的三种情况？
   单行单列
   多行单列
   多行多列【表】

7.子查询结果是单行单列

目标

能够掌握子查询的结果是单行单列的查询

讲解

子查询结果是单列，在WHERE后面作为条件

【格式语法】

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

【代码实践】

1. 查询工资最高的员工是谁？

   1. 查询最高工资是多少

      SELECT MAX(salary) FROM emp;
      

2. 根据最高工资到员工表查询到对应的员工信息

     SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);
   

2. 查询工资小于平均工资的员工有哪些？

   1. 查询平均工资是多少

      SELECT AVG(salary) FROM emp;
      

2. 到员工表查询小于平均的员工信息

   SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
   

小结

子查询的结果是单行单列时父查询如何处理？

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

通常作为父查询的条件

8.子查询结果是多行单列

目标

能够掌握子查询的结果是多行单列的查询

讲解

子查询结果是单列多行，结果集类似于一个数组，在WHERE后面作为条件，父查询使用IN运算符

【格式语法】

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

【代码实践】

查询工资大于5000的员工，来自于哪些部门的名字

1. 先查询大于5000的员工所在的部门id

   SELECT dept_id FROM emp WHERE salary > 5000;
   

2. 再查询在这些部门id中部门的名字

   SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000);
   

查询开发部与财务部所有的员工信息

1. 先查询开发部与财务部的id

   SELECT id FROM dept WHERE NAME IN('开发部','财务部');
   

2. 再查询在这些部门id中有哪些员工

   SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'));
   

小结

子查询的结果是多行单列时父查询如何处理？

放在父查询的条件位置,使用in

9.子查询结果是多行多列

目标

能够掌握子查询的结果是多行多列的查询

讲解

子查询结果是多列，在FROM后面作为表

【格式语法】

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

子查询作为表需要取别名，否则这张表没用名称无法访问表中的字段

【代码实践】

查询出2011年以后入职的员工信息，包括部门名称

1. 在员工表中查询2011-1-1以后入职的员工

   SELECT * FROM emp WHERE join_date > '2011-1-1';
   

2. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于dept_id

   SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id;
   

使用表连接：

SELECT d.*, e.* FROM dept d INNER JOIN emp e ON d.id = e.dept_id WHERE e.join_date > '2011-1-1';

小结

三种子查询情况：单行单列，多行单列，多行多列
单行单列：作为父查询的条件
多行单列：作为父查询的条件,通常使用 IN
多行多列：作为父查询的一张表(虚拟表)

10. 多表查询案例

我们在公司开发中，根据不同的业务需求往往需要通过2张及以上的表中去查询需要的数据。所以我们有必要学习2张及以上的表的查询。其实不管是几张表的查询，都是有规律可循的。

准备数据

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,
  losalary INT,
  hisalary INT
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

分析4张表的关系：通过4张表可以查出一个员工的所有信息

11 练习1

【需求】

查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

【步骤】

1. 确定查询的表有那些
2. 确定表连接之间的条件
3. 确定查询字段

【实现】

1. 确定要查询哪些表：emp e, job j

   SELECT * FROM emp e INNER JOIN job j;
   

2. 确定表连接条件： e.job_id=j.id

   SELECT * FROM emp e INNER JOIN job j ON e.job_id=j.id;
   

3. 确定查询字段：员工编号，员工姓名，工资，职务名称，职务描述

   SELECT e.`id`, e.`ename`, e.`salary`, j.`jname`, j.`description` FROM emp e INNER JOIN job j ON e.job_id=j.id;
   

12 练习2

【需求】

查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

【步骤】

1. 确定表有那些
2. 确定连接条件
3. 确定查询字段

【实现】

1. 确定要查询哪些表emp e, job j, dept d

   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d;
   

2. 确定表连接条件 e.job_id=j.id and e.dept_id=d.id

   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d ON e.job_id=j.id AND e.dept_id=d.id;
   

3. 确定查询字段：员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

   SELECT e.`id`, e.`ename`, e.`salary`, j.`jname`, j.`description`, d.`dname`, d.`loc` FROM emp e INNER JOIN job j INNER JOIN dept d ON e.job_id=j.id AND e.dept_id=d.id;
   

13 练习3

【需求】

​ 查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

【步骤】

1. 确定表有那些
2. 确定连接条件
3. 确定查询字段

【实现】

1. 确定要查询哪些表，emp e, job j, dept d, salarygrade s

   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s;
   

2. 确定表连接条件 e.job_id=j.id and e.dept_id=d.id and e.salary between s.losalary and s.hisalary

   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND s.hisalary;
   

3. 确定查询字段：员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

   SELECT e.`ename`, e.`salary`, j.`jname`, j.`description`, d.`dname`, d.`loc`, s.`grade` FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary;
   

多表查询规律总结

1. 不管我们查询几张表，表连接查询会产出笛卡尔积，我们需要消除笛卡尔积，拿到正确的数据。
   我们需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）
2. 消除笛卡尔积规律：2张表需要1个条件，3张表需要2个条件，4张表需要3个条件。
   （条件数量=表的数量-1），每张表都要参与进来
3. 多表连接查询步骤：
   1. 确定要查询哪些表
   2. 确定表连接条件
   3. 根据需求查询字段信息

14 练习4

【需求】

​ 查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

【步骤】

1. 确定要查询哪些表
2. 确定表连接条件
3. 确定查询字段

【实现】

1. 确定要查询哪些表，emp e, job j, dept d, salarygrade s

     SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s;
   

2. 确定表连接条件 e.job_id=j.id and e.dept_id=d.id and e.salary between s.losalary and s.hisalary

   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND s.hisalary;
   

额外条件：只需要查询经理的信息（j.jname=‘经理’）

3. 确定查询字段：员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

   SELECT e.`ename`, e.`salary`, j.`jname`, j.`description`, d.`dname`, d.`loc`, s.`grade` FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary AND j.jname='经理';
   

15 练习5

【需求】

​ 查询出部门编号、部门名称、部门位置、部门人数

【步骤】

1. 去员工表中找到每个部门的人数和部门id，得到临时表
2. 将步骤一得到的临时表与部门表进行连接
3. 连接条件部门id要相等
4. 确定查询字段

【实现】

1. 去员工表中找到每个部门的人数和部门id

   SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id;
   

2. 再和部门表连接查询

   SELECT * FROM dept d INNER JOIN (SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id) e ON e.dept_id=d.`id`;
   

3. 显示对应的字段

   SELECT d.`id`, d.dname, d.`loc`, e.total 部门人数 FROM dept d INNER JOIN (SELECT dept_id, COUNT(*) total FROM emp GROUP BY dept_id) e ON e.dept_id=d.`id`;
   

最终效果：

16 事务的概念

目标

1. 能够理解事务的概念
2. 了解事务的四大特性

讲解

什么是事务

数据库事务(简称:事务)是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

事务的应用场景说明

例如: 张三给李四转账，张三账号减钱，李四账号加钱

-- 创建数据表
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

模拟张三给李四转500元钱，一个转账的业务操作最少要执行下面的2条语句：

1. 张三账号-500
2. 李四账号+500

-- 1. 张三账号-500
UPDATE account SET balance = balance - 500 WHERE id=1;
-- 2. 李四账号+500
UPDATE account SET balance = balance + 500 WHERE id=2;

​ 假设当张三账号上-500元,服务器崩溃了。李四的账号并没有+500元，数据就出现问题了。我们需要保证其中一条SQL语句出现问题，整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

事务的四大特性(ACID)

增删改查 CRUD ( create , retrieve , update ,delete)

事务特性	含义
原子 【Atomicity】[ætəˈmɪsɪti]	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性 【Consistency】[kənˈsɪstənsi]	事务前后数据的完整性必须保持一致
隔离性 【Isolation】[ˌaɪsəˈleʃən]	是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。
持久性 【Durability】[ˌdjʊrəˈbɪlətɪ]	指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

小结

什么是事务？ 一组操作, 要么同时成功,要么同时失败!

事务四个特性？
原子性
一致性
隔离性
持久性

17 手动提交事务

目标

能够使用手动的方式提交事务

讲解

MYSQL中可以有两种方式进行事务的操作：

1. 手动提交事务
2. 自动提交事务(默认的)

事务有关的SQL语句：

SQL语句	描述
start transaction;	开启事务
commit;	提交事务
rollback;	回滚事务

手动提交事务使用步骤

​ 第1种情况：开启事务 -> 执行SQL语句 -> 成功 -> 提交事务
​ 第2种情况：开启事务 -> 执行SQL语句 -> 失败 -> 回滚事务

案例演示1：模拟张三给李四转500元钱（成功）
目前数据库数据如下：

1. 使用DOS控制台进入MySQL

2. 执行以下SQL语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500

   START TRANSACTION;
   UPDATE account SET balance = balance - 500 WHERE id=1;
   UPDATE account SET balance = balance + 500 WHERE id=2;
   

3. 使用可视化数据库客户端查看数据库：发现数据并没有改变
   

4. 在控制台执行commit提交任务：
   

5. 使用可视化数据库客户端查看数据库：发现数据改变
   

---

案例演示2：模拟张三给李四转500元钱（失败）
目前数据库数据如下：

1. 在控制台执行以下SQL语句：1.开启事务， 2.张三账号-500

   START TRANSACTION;
   UPDATE account SET balance = balance - 500 WHERE id=1;
   

2. 使用可视化数据库客户端查看数据库：发现数据并没有改变
   

3. 在控制台执行rollback回滚事务：
   

4. 使用可视化数据库客户端查看数据库：发现数据没有改变
   

小结

1. 如何开启事务: start transaction;
2. 如何提交事务: commit;
3. 如何回滚事务: rollback;

18 自动提交事务

目标

了解自动提交事务

能够关闭自动提交事务

讲解

MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。

1. 将金额重置为1000
   

2. 执行以下SQL语句

   UPDATE account SET balance = balance - 500 WHERE id=1;
   

3. 使用可视化数据库客户端查看数据库：发现数据已经改变
   

可以使用SQL语句查看MySQL是否开启自动提交事务

show variables like '%commit%';
-- 或
SELECT @@autocommit;

通过修改MySQL全局变量"autocommit"，取消自动提交事务

   0--OFF（关闭自动提交）
   1--ON （开启自动提交）

4. 取消自动提交事务，设置自动提交的参数为OFF，执行SQL语句：set autocommit = 0;
   

5. 在控制台执行以下SQL语句：张三-500

   UPDATE account SET balance = balance - 500 WHERE id=1;
   

6. 使用可视化工具查看数据库，发现数据并没有改变
   

7. 在控制台执行commit提交任务
   

8. 查看数据库，发现数据改变
   

小结

1. 查询事务提交状态：

   show variables like '%commit%';
   select @@autocommit;
   

2. 关闭事务自动提交：

   mysql 是自动提交

   set autocommit = 0;

19 事务原理

目标

能够理解事务原理

讲解

​ 事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

小结

说出事务原理？

1. 开启事务后,SQL语句会放在临时的日志文件,如果提交事务,将日志文件中SQL的结果放在数据库中

2. 如果回滚事务清空日志文件.

事务的操作	MySQL操作事务的语句
开启事务	start transaction
提交事务	commit
回滚事务	rollback
查询事务的自动提交情况	show variables like ‘%commit%’; / select @@autocommit;
设置事务的自动提交方式	set autocommit = 0 – 关闭自动提交 1–-开启自动提交

mysql默认自动提交

20 事务的隔离级别

目标

1. 能够理解并发访问的三个问题
2. 能够说出mysql的四种隔离级别

讲解

并发访问问题

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
幻读	一个事务内读取到了别的事务插入的数据，导致前后读取记录行数不同。这是insert或delete时引发的问题

数据库四种隔离级别

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

小结

1. 能够理解并发访问的三个问题
   赃读:一个事务读取另一个事务还没有提交的数据
   不可重复读:一个事务读取多次数据内容不一样
   幻读:一个事务读取多次数量不一样
2. 能够说出mysql的四种隔离级别
   读未提交:read uncommitted
   读已提交:read committed
   可重复读:repeatable read
   串行化:serializable

21 脏读的演示

目标

1. 能够设置mysql的隔离级别
2. 能够解决赃读

讲解

查询和设置隔离级别

1. 查询全局事务隔离级别

   show variables like '%isolation%';
   -- 或
   select @@tx_isolation;
   

2. 设置事务隔离级别，需要退出MSQL再进入MYSQL才能看到隔离级别的变化

   set global transaction isolation level 级别字符串;
   -- 如:
   set global transaction isolation level read uncommitted;
   

脏读的演示

脏读：一个事务读取到了另一个事务中尚未提交的数据。

将数据进行恢复：

UPDATE account SET balance = 1000;

1. 打开A窗口登录MySQL，设置全局的隔离级别为最低

   mysql -uroot -proot
   set global transaction isolation level read uncommitted;
   

2. 打开B窗口,AB窗口都开启事务

   use 数据库;
   start transaction;
   

3. A窗口更新2个人的账户数据，未提交

   update account set balance=balance-500 where id=1;
   update account set balance=balance+500 where id=2;
   

4. B窗口查询账户

   select * from account;
   

5. A窗口回滚

   rollback;
   

6. B窗口查询账户，钱没了
   

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入500块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

脏读解决方案

解决脏读的问题：将全局的隔离级别进行提升

将数据进行恢复：

UPDATE account SET balance = 1000;

1. 在A窗口设置全局的隔离级别为read committed

   set global transaction isolation level read committed;
   

2. B窗口退出MySQL，B窗口再进入MySQL
   

3. AB窗口同时开启事务
   

4. A更新2个人的账户，未提交

   update account set balance=balance-500 where id=1;
   update account set balance=balance+500 where id=2;
   

5. B窗口查询账户
   

6. A窗口commit提交事务
   

7. B窗口查看账户
   

结论：read committed的方式可以避免脏读的发生

小结

1. 查询全局事务隔离级别？

   show variables like '%isolation%';
   select @@tx_isolation;
   

2. 设置全局事务隔离级别？

   set global transaction isolation level 隔离级别字符串;
   

3. 如何解决赃读?

   将隔离级别设置为:read committed
   

22 不可重复读的演示

目标

能够解决不可重复读

讲解

不可重复读：一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题。

不可重复读演示

将数据进行恢复：

UPDATE account SET balance = 1000;

1. 开启A窗口

   set global transaction isolation level read committed;
   

2. 开启B窗口，在B窗口开启事务，并查询数据

   start transaction;
   select * from account;
   

3. 在A窗口开启事务，并更新数据

   start transaction;
   update account set balance=balance+500 where id=1;
   commit;
   

4. B窗口查询

   select * from account;
   

两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。
很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致短信和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

不可重复解决方案

解决不可重复读的问题：将全局的隔离级别进行提升为：repeatable read
将数据进行恢复：

UPDATE account SET balance = 1000;

1. A窗口设置隔离级别为：repeatable read

   set global transaction isolation level repeatable read;
   

2. B窗口退出MySQL，B窗口再进入MySQL，并查询数据

   start transaction;
   select * from account;
   

3. A窗口更新数据

   start transaction;
   update account set balance=balance+500 where id=1;
   commit;
   

4. B窗口查询

   select * from account;
   

结论：同一个事务中为了保证多次查询数据一致，必须使用repeatable read隔离级别

小结

如何解决不可重复读？

将数据库的隔离级别设置为:repeatable read

23幻读的演示

目标

能够解决幻读

讲解

幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取记录行数不同。

这是insert或delete时引发的问题

幻读演示

1. 开启A窗口，开启事务，并查询id>1的数据

2. 开启B窗口进入数据库，开启事务，添加一条数据，并提交事务

3. 在A窗口修改id>数据的balance为0，并重新查询id>1的数据

幻读解决方案

我们可以将事务隔离级别设置到最高，以挡住幻读的发生
将数据进行恢复：

UPDATE account SET balance = 1000;

1. 开启A窗口

   set global transaction isolation level serializable; -- 设置隔离级别为最高
   

2. A窗口退出MySQL，A窗口重新登录MySQL

   start transaction;
   select count(*) from account;
   

3. 再开启B窗口，登录MySQL

4. 在B窗口中开启事务，添加一条记录

   start transaction; -- 开启事务
   insert into account (name,balance) values ('LaoWang', 500);
   

5. 在A窗口中commit提交事务，B窗口中insert语句会在A窗口事务提交后立马运行
   

6. 在A窗口中接着查询，发现数据不变

   select count(*) from account;
   

7. B窗口中commit提交当前事务
   

8. A窗口就能看到最新的数据
   

结论：使用serializable隔离级别，一个事务没有执行完，其他事务的SQL执行不了，可以挡住幻读。

通过提高隔离级别到串行化，可以避免并发访问的所有的问题，但效率太低。

小结

如何解决幻读？

总结

1. 能够使用内连接进行多表查询
   隐式: SELECT 字段 FROM 左表, 右表 WHERE 条件;

   显示: SELECT 字段 FROM 左表 INNER JOIN 右表 ON 条件;

2. 能够使用左外连接和右外连接进行多表查询
   左外连接: SELECT 字段 FROM 左表 LEFT OUTER JOIN 右表 ON 条件;
   右外连接: SELECT 字段 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件;

3. 能够使用子查询进行多表查询
   SELECT 字段 FROM 表名 WHERE 字段=(SELECT MAX(age) FROM 表名);

4. 能够理解多表查询的规律
   1.明确查询哪些表
   2.明确表之间的连接条件,外键=主键
   3.根据需求

5. 能够理解事务的概念
   多条SQL语句组成一个功能,要么一起成功,要么一起失败.

6. 能够说出事务的原理
   当开始事务后,执行的SQL会放在临时日志文件中.提交数据时,日志文件中的数据就会放到数据库中,如果回滚事务,清空日志文件

7. 能够在MySQL中使用事务
   开启事务: start transaction;
   提交事务: commit;
   回滚事务: rollback;
   查看是否自动提交事务: show variables like '%commit%';
   关闭事务自动提交: set autocommit = 0;

8. 能够理解脏读,不可重复读,幻读的概念及解决办法
   脏读: 一个事务读到另一个事务还没有提交的数据, 将隔离级别设置为 read commited;
   不可重复读:一个事务多次读取,每次数据不一样,将隔离级别设置为 repeatable read;
   幻读: 一个事务多次读取,数量不一样,将隔离级别设置为 serializable;