MySQL多表和事务

MySQL约束

MySQL管理数据库

创建数据库

CREATE DATABASE 数据库名;

删除数据库

DROP DATABASE 数据库名;

表的管理

查看所有表

SHOW TABLES;

创建表:student(整数id,字符串name,整数age)

CREATE TABLE student (
	id int,
    name varchar(20),
    age int
);

查看一个表结构

DESC 表名;

修改表结构

所有修改表前面都一样:ALTER TABLE 表名 xxx;

向学生表中添加1列remark varchar(100)

ALTER TABLE 表名 ADD remark varchar(100);

删除学生表列remark

ALTER TABLE 表名 DROP remark;

修改表student名字为student1

RENAME TABLE student TO student1;

删除表

DROP TABLE 表名;

管理数据：数据增删改DML

插入数据

INSERT INTO 表名 (字段名1, 字段名2...) VALUES (值1, 值2...);

修改数据

UPDATE 表名 SET 字段名=新的值;

删除表中的所有数据

DELETE FROM 表名;

查询数据

带条件的查询
SELECT * FROM 表名 WHERE 条件;

排序: ORDER BY 字段 [ASC|DESC]
分组: GROUP BY 字段, 相同的数据作为一组,分组通常是和聚合函数一起使用
LIMIT: LIMIT 跳过的记录数, 显示的记录数

DCL创建用户，给用户授权，撤销授权(使用较少)

目标

学习DCL创建用户，给用户授权，撤销授权

我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

关键字说明：

1.`用户名`：将创建的用户名
2.`主机名`：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
3.`密码`：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

具体操作：

-- user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-- user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '123';

授权用户

用户创建之后，基本没什么权限！需要给用户授权

授权格式

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

关键字说明

1.`GRANT` 授权关键字
2.授予用户的权限，如`SELECT`，`INSERT`，`UPDATE`等。如果要授予所的权限则使用`ALL`
3.`数据库名.表名`：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如`*.*`
4.`'用户名'@'主机名'`: 给哪个用户授权

具体操作

1. 给user1用户分配对test这个数据库操作的权限

   GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';
   

2. 给user2用户分配对所有数据库操作的权限

GRANT ALL ON *.* TO 'user2'@'%';

撤销授权

REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';

具体操作：

• 撤销user1用户对test操作的权限

  REVOKE ALL ON test.* FROM 'user1'@'localhost';
  

查看权限

SHOW GRANTS FOR '用户名'@'主机名';

具体操作：

• 查看user1用户的权限

  SHOW GRANTS FOR 'user1'@'localhost';
  

小结

1. 创建用户

   CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
   

2. 添加权限

   GRANT 权限1, 权限2 ... ON 数据库名.表名 TO '用户名'@'主机名';
   

3. 删除权限

   REVOKE 权限1, 权限2 ... ON 数据库名.表名 FROM '用户名'@'主机名';
   

DCL删除用户，修改用户密码

目标

学习DCL删除用户，修改用户密码

删除用户

DROP USER '用户名'@'主机名';

具体操作：

• 删除user2

   DROP USER 'user2'@'%';
  

修改管理员密码

注意：需要在未登陆MySQL的情况下操作。

mysqladmin -uroot -p密码 password 新密码  -- 新密码不需要加上引号

具体操作：

mysqladmin -uroot -p password 123456
输入老密码

修改普通用户密码

注意：需要使用root/本用户登陆MySQL的情况下操作。

set password for '用户名'@'主机名' = password('新密码');

具体操作：

set password for 'user1'@'localhost' = password('666666');

小结

1. 删除用户？

   DROP USER '用户名'@'主机名';
   

2. 修改用户密码？

   修改root用户: 不需要登录: mysqladmin -uroot -p密码 password 新密码
   修改普通用户: 需要登录: set password for '用户名'@'主机名' = password('密码');
   

数据库备份(重要)

目标

1. 学习命令行的方式备份和还原表中的数据
2. 学习图形客户端来备份和还原数据

讲解

备份的应用场景

​ 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

DOS命令行方式备份与还原

备份格式：

注意：这个操作不用登录

mysqldump -u用户名 -p密码 数据库 > 文件的路径

还原格式：

注意：还原的时候需要先登录MySQL,并选中对应的数据库

SOURCE 导入文件的路径

具体操作：

• 备份day22数据库中的数据

mysqldump -uroot -proot day22 > C:\work\code\bak.sql

数据库中的所有表和数据都会导出成SQL语句

• 还原day22数据库中的数据
  • 删除day22数据库中的所有表
    
  • 登录MySQL

  mysql -uroot -proot
  

  • 选中数据库

  use day22;
  select database();
  

• 使用SOURCE命令还原数据

source C:\work\课改\MYSQL课改资料\Day02-MYSQL多表查询\code\bak.sql

图形化界面备份与还原

• 备份day22数据库中的数据
  ​ 选中数据库，右键 ”备份/导出”，指定导出路径，保存成.sql文件即可。
  ​
  ​
  ​
• 包含创建数据库的语句
  ​
• 还原day22数据库中的数据
  • 删除day22数据库
  • 数据库列表区域右键“执行SQL脚本”， 指定要执行的SQL文件，执行即可
    
    

小结

1. 使用命令行的方式备份和还原表中的数据
   备份: 不要登录: mysqldump -uroot -p密码 数据库名 > 文件路径
   还原: 要登录: source 文件路径

2. 使用图形客户端来备份和还原数据

   右键

数据库的三大范式

目标

学习数据库中三大范式

什么是范式

范式是指:设计数据库表的规则(Normal Form)
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储

范式的基本分类

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

第一范式

即数据库表的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中每个列的值只能是表的一个属性或一个属性的一部分。简而言之，第一范式每一列不可再拆分，称为原子性。
第一范式：表中每一列不能再拆分

第二范式

第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

第二范式：

1. 一张表只描述一件事情
2. 表中的每一个字段都依赖于主键
   

第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。

第三范式：一张表的字段必须引用另一张表的主键

小结

第一范式要求? 字段原子性,表中的字段不能再拆分了.可以直接使用啦

第二范式要求? 1.一张表描述一件事情 2.每张表都有主键

第三范式要求? 一张表的字段必须引用另一张表的主键

反三范式

表关系的概念

目标

学习表之间的3种关系

现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！分成三种：

1. 一对一
2. 一对多
3. 多对多

一对多

例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

多对多

例如：老师和学生，学生和课程
多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
[外链图片转存失败(img-IaXKoeE3-1564752140431)(/多对多.png)]

一对一

在实际的开发中应用不多.因为一对一可以创建成一张表。
两种建表原则：

• 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一UNIQUE
• 外键是主键：主表的主键和从表的主键，形成主外键关系
  
  

一对多关系练习

目标

以下案例是我们JavaWeb课程最后的小项目.我们拿出其中一部分需求,根据需求来设计数据库表之间的关系

一个旅游线路分类中有多个旅游线路

讲解

具体操作：

• 创建旅游线路分类表

-- 创建旅游线路分类表:tab_category
-- cid旅游线路分类主键，自动增长
-- cname旅游线路分类名称非空，唯一，字符串100
CREATE TABLE tab_category (
   cid INT PRIMARY KEY AUTO_INCREMENT,
   cname VARCHAR(100) NOT NULL UNIQUE
);

• 添加旅游线路分类数据

INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');

• 创建旅游线路表

-- 创建旅游线路表:tab_route
-- rid旅游线路主键，自动增长
-- rname旅游线路名称非空，唯一，字符串100
-- price价格
-- rdate 上架时间，日期类型
-- cid 外键，所属分类
create table tab_route(
   rid int primary key auto_increment,
   rname varchar(100) not null unique,
   price double,
   rdate date,
   cid int,  -- 外键
   foreign key (cid) references tab_category(cid)
);

• 添加旅游线路数据

INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁3天 惠贵团】尝味友鸭面线 住1晚鼓浪屿', 1499, '2018-01-27', 3),
(NULL, '【浪漫桂林 阳朔西街高铁3天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-01-27', 3),
(NULL, '【爆款￥1699秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '2018-01-27', 2),
(NULL, '【经典·狮航 ￥2399秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2018-01-27', 2),
(NULL, '香港迪士尼乐园自由行2天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房1晚住宿】', 799, '2018-01-27', 4);

小结

一对多的关系，创建表的顺序：先建主表，再建从表。

多对多关系练习

目标

一个用户收藏多个线路，一个线路被多个用户收藏

讲解

对于多对多的关系我们需要增加一张中间表来维护他们之间的关系

具体操作：

• 创建用户表

-- 创建用户表tab_user
-- 用户id主键,int类型,自动增长
-- 用户名username,字符串,长度30
-- 密码PASSWORD,字符串,长度30
-- 用户姓名NAME,字符串,长度100
-- 性别sex,字符串,长度1
CREATE TABLE tab_user (
   uid INT PRIMARY KEY AUTO_INCREMENT, -- 用户id
   username VARCHAR(100) NOT NULL UNIQUE, -- 用户名
   PASSWORD VARCHAR(30) NOT NULL, -- 密码
   NAME VARCHAR(100), -- 真实姓名
   sex CHAR(1) -- 性别
);

• 添加用户数据

INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '男'),
(NULL, 'cz119', 654321, '小王', '男');

• 创建收藏表

-- 创建收藏表:tab_favorite
-- 收藏主键fid,int类型,主键,自动增长
-- 用户id int,不为null
-- 收藏时间date,不为null
-- 旅游线路id,int类型,不为null
CREATE TABLE tab_favorite (
   fid INT PRIMARY KEY AUTO_INCREMENT, -- 收藏主键
   uid INT NOT NULL, -- 用户id
   DATE DATE NOT NULL, -- 收藏时间
   rid INT NOT NULL -- 旅游线路id
);

• 增加收藏表数据

INSERT INTO tab_favorite VALUES
(NULL, 1, '2018-01-01', 1), -- 老王选择厦门
(NULL, 1, '2018-01-01', 2), -- 老王选择桂林
(NULL, 1, '2018-01-01', 3), -- 老王选择泰国
(NULL, 2, '2018-01-01', 2), -- 小王选择桂林
(NULL, 2, '2018-01-01', 3), -- 小王选择泰国
(NULL, 2, '2018-01-01', 5); -- 小王选择迪士尼

小结

一对多:在多方(从表)建立外键引用主表的主键

多对多：创建一张中间表，中间表与其它两张表是多对一的关系

外键约束(重要)

目标

学习创建外键约束

准备部门表和员工表的数据

-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 创建员工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);

-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

问题：
当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id

需要达到目的:需要约束dep_id只能是department表中已经存在id
解决方式:使用外键约束

什么是外键约束

一张表的某个字段,引用另一张表的主键

主表： 主键所在的表,约束别人的表,将数据给别人用
副表/从表： 外键所在的表,被约束的表,使用别人的数据

创建外键

1. 新建表时增加外键：

   CREATE TABLE 表名 (
   	字段名 字段类型,
       字段名 字段类型,
   	-- 外键约束
       [CONSTRAINT 外键约束名] FOREIGN KEY(外键字段名) REFERENCES 主表(主键)
   );
   

   关键字解释：
   CONSTRAINT:

   FOREIGN KEY(外键字段名):

   REFERENCES 主表名(主键字段名) :

2. 已有表增加外键：

   ALTER TABLE 从表 ADD [CONSTRAINT 外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
   

具体操作：

• 副表/从表: 被别人约束,表结构添加外键约束
• 删除副表/从表 employee
• 创建从表 employee 并添加外键约束

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 添加一个外键
	-- 外键取名公司要求,一般fk结尾
	CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);

• 正常添加数据

INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

• 部门错误的数据添加失败

INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

具体操作：

• 删除employee表的emp_depid_ref_dep_id_fk外键

ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;

• 在employee表情存在况下添加外键

ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

小结

什么是外键?有什么好处?

表中的字段引用其他表的主键

外键什么好处:外键的数据不能够随便写,只能是主表的主键值

1. 创建外键约束格式？
   创建表时添加外键

   CREATE TABLE 表名 (
   	字段名 字段类型,
       字段名 字段类型,
       CONSTRAINT 外键约束名 FOREIGN KEY(外键字段名) REFERENCE 主表(主键字段名)
   );
   

   在已有表基础上增加外键

   ALTER TABLE 表名 ADD CONSTRAINT 外键约束名 FOREIGN KEY(外键字段名) REFERENCE 主表(主键字段名);
   

2. 删除外键格式？

   ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
   

外键的级联

目标

了解外键的级联操作

有了外键约束后能直接修改和删除数据吗?

要把部门表中的id值2，改成5，能不能直接修改呢？

UPDATE department SET id=5 WHERE id=2;

不能直接修改:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接修改主表(部门表)主键

要删除部门id等于1的部门, 能不能直接删除呢？

DELETE FROM department WHERE id = 1;

不能直接删除:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接删除主表(部门表)数据

什么是级联操作

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
ON UPDATE CASCADE ：主表主键修改后,从表的数据也跟着修改
ON DELETE CASCADE ：主表主键删除后,从表数据也跟着删除

具体操作：

• 删除employee表
• 重新创建employee表，添加级联更新和级联删除

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,
	-- 添加外键约束,并且添加级联更新和级联删除
	CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);

• 再次添加数据到员工表和部门表

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

• 把部门表中id等于1的部门改成id等于10

UPDATE department SET id=10 WHERE id=1;

• 删除部门号是2的部门

DELETE FROM department WHERE id=2;

小结

级联更新：ON UPDATE CASCADE 主表更新时,从表跟着更新

级联删除：ON DELETE CASCADE 主表删除时,从表跟着删除

多表查询介绍

目标

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

讲解

什么是多表查询

需要同时查询多张表才能获取到需要的数据

比如：我们想查询到开发部有多少人，需要将部门表和员工表同时进行查询
[外链图片转存失败(img-akftzKvL-1564752140434)

多表查询的分类

小结

什么是多表查询？

需要同时查询多张表才能获取到需要的数据

说出多表查询的2种方式?

1. 表连接查询
2. 子查询

笛卡尔积现象

目标

学习什么是笛卡尔积，以及如何消除笛卡尔积

准备数据

-- 创建部门表
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);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

什么是笛卡尔积现象

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

具体操作：

SELECT * FROM dept, emp;

[外链图片转存失败(img-Evl5D9cF-1564752140434)

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

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积
[外链图片转存失败(img-JjK6srm3-1564752140434)

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

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。
[外链图片转存失败(img-00ZlqWoS-1564752140435)

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

[外链图片转存失败(img-8VYJrg2c-1564752140436)

小结

1. 能够说出什么是笛卡尔积?
   左表的每条数据会和右表的每条数据组合

2. 如何消除笛卡尔积
   从表的外键=主表的主键这样的数据才是有用的
   表连接条件

内连接(重要)

目标

学习内连接的使用

什么是内连接

多表查询时获取符合条件的数据

隐式内连接

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

SELECT 字段 FROM 表1, 表2 WHERE 条件;

显式内连接

显式内连接：使用INNER JOIN ... ON语句, 可以省略INNER

SELECT 字段 FROM 左表 INNER JOIN 右表 ON 表连接条件 WHERE 查询条件;

具体操作：

• 查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称，我们发现需要联合2张表同时才能查询出需要的数据，我们使用内连接

[外链图片转存失败(img-mdvmehpd-1564752140436)

1. 确定查询哪些表

SELECT * FROM dept INNER JOIN emp;

[外链图片转存失败(img-bfcGWoVD-1564752140437)

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

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

[外链图片转存失败(img-vxffImcn-1564752140437)

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

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

[外链图片转存失败(img-qudtdJpQ-1564752140437)

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`='唐僧';

[外链图片转存失败(img-5N3w6uzL-1564752140438)

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`='唐僧';

[外链图片转存失败(img-gyrAVGhe-1564752140438)

小结

1. 什么是隐式内连接和显示内连接？
   隐式内连接, 看不到join,表之间使用逗号分割

   SELECT * FROM 表1, 表2 WHERE 条件;
   

   显示内连接

   SELECT * FROM 左表 INNER JOIN 右表 ON 表连接条件 WHERE 查询条件;
   

左外连接(重要)

目标

学习左外连接查询

讲解

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

SELECT 字段 FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件；

左外连接可以理解为：将满足要求的数据显示,左表不满足要求的数据也显示(在内连接的基础上,保证左表的数据全部显示)

具体操作：

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

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

[外链图片转存失败(img-WFfJJTJp-1564752140438)

• 使用内连接查询

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

[外链图片转存失败(img-otQtqVEv-1564752140439)

• 使用左外连接查询

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

[外链图片转存失败(img-mgg917P7-1564752140439)

小结

1. 左外连接查询格式？

   SELECT 字段 FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件；
   

2. 左外连接查询特点？

   满足条件的数据显示，左表不满足条件的数据也显示

右外连接

目标

学习右外连接查询

讲解

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

SELECT 字段 FROM 左表 RIGHT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件；

右外连接可以理解为：满足要求的数据显示,并且右表不满足要求的数据也显示(在内连接的基础上保证右边的数据全部显示)

具体操作：

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

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

[外链图片转存失败(img-tICvQZ2V-1564752140440)

• 使用内连接查询

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 表连接条件 WHERE 查询条件;
   

2. 右外连接查询特点？

   满足条件的数据显示，右表不满足条件的数据也显示

子查询

目标

学习子查询的概念

子查询的三种情况

讲解

什么是子查询

一个查询语句的结果作为另一个查询语句的一部分

SELECT 查询字段 FROM 表 WHERE 条件;

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

子查询需要放在（）中
先执行子查询,将子查询的结果作为父查询的一部分

子查询结果的三种情况

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

小结

1. 什么是子查询？

   一个查询语句的结果作为另一个查询语句的一部分

2. 子查询结果的三种情况？

   单行单列

   多行单列

   多行多列

子查询的结果是单行单列(重要)

目标

学习子查询的结果是单行单列的查询

讲解

子查询结果是单列，在WHERE后面作为条件
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);

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

   1. 查询最高工资是多少

   SELECT MAX(salary) FROM emp;
   

   

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

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

   

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

   1. 查询平均工资是多少

   SELECT AVG(salary) FROM emp;
   

   

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

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

   

小结

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

​ 将子查询放在父查询条件的位置

子查询结果是多行单列(重要)

目标

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

讲解

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

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

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

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

   SELECT dept_id FROM emp WHERE salary > 5000;
   

   

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

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

   

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

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

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

   

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

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

   

小结

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

将子查询作为父查询的条件使用in关键字匹配

子查询的结果是多行多列

目标

学习子查询的结果是多行多列的查询

讲解

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

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

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

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

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

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

  

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

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

  

小结

三种子查询情况：
单行单列：作为父查询的条件 >, <, = 处理
多行单列：作为父查询的条件 in ();
多行多列：作为父查询的一张虚拟表

多表查询案例

​ 我们在公司开发中，根据不同的业务需求往往需要通过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张表可以查出一个员工的所有信息

练习1

目标

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

讲解

具体操作：

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

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

![](/多表查询06.png)
![](/多表查询07.png)

2. 确定表连接条件 e.job_id=j.id and e.dept_id=d.id
```sql
SELECT * FROM emp e INNER JOIN job j ON e.job_id=j.id INNER JOIN dept d ON 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 ON e.job_id=j.id INNER JOIN dept d ON e.dept_id=d.id;
   

   

练习2

目标

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

讲解

具体操作：

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 ON e.job_id=j.id INNER JOIN dept d ON e.dept_id=d.id INNER JOIN salarygrade s  ON 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 ON e.job_id=j.id INNER JOIN dept d ON e.dept_id=d.id INNER JOIN salarygrade s  ON e.salary BETWEEN s.losalary AND s.hisalary AND j.jname='经理';
   

   

练习3

目标

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

讲解

具体操作：

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`;
   

   
   最终效果：
   

事务的概念

目标

1. 学习事务的概念
2. 了解事务的四大特性

什么是事务

由多条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)

事务特性	含义
原子性（Atomicity）	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）	事务前后数据的完整性必须保持一致。
隔离性（Isolation）	是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。
持久性（Durability）	指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

小结

什么是事务？
由多条SQL语句组成一个功能，这些SQL语句组成事务

事务四个特性？

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

手动提交事务(重要)

目标

学习手动的方式提交事务

讲解

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. 使用SQLYog查看数据库：发现数据并没有改变
   
4. 在控制台执行commit提交任务：
   
5. 使用SQLYog查看数据库：发现数据改变
   

---

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

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

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

   
2. 使用SQLYog查看数据库：发现数据并没有改变
   
3. 在控制台执行rollback回滚事务：
   
4. 使用SQLYog查看数据库：发现数据没有改变
   

小结

1. 如何开启事务: START TRANSACTION;
2. 如何提交事务: COMMIT;
3. 如何回滚事务: ROLLBACK;

自动提交事务(重要)

目标

了解自动提交事务

讲解

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

1. 将金额重置为1000
   

2. 执行以下SQL语句

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

3. 使用SQLYog查看数据库：发现数据已经改变
   

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

show variables like ‘%commit%’;
– 或
SELECT @@autocommit; – 推荐

通过修改MySQL全局变量"autocommit"，取消自动提交事务
![](/事务13.png)
0:OFF（关闭自动提交）
1:ON（开启自动提交）

4. 取消自动提交事务，设置自动提交的参数为OFF，执行SQL语句：`set autocommit = 0;`
![](/事务14.png)

5. 在控制台执行以下SQL语句：张三-500
```sql
UPDATE account SET balance = balance - 500 WHERE id=1;

6. 使用SQLYog查看数据库，发现数据并没有改变
   

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

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

小结

1. 查询事务提交状态：select @@autocommit; 1表示开启自动提交

2. 关闭事务自动提交：set autocommit = 0; 0表示关闭自动提交

事务原理(重要)

目标

学习事务原理

讲解

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

小结

说出事务原理？

客户端连接mysql服务端，连接成功会创建一个临时日志文件

普通的SQL操作直接操作数据库

开启事务，后续的SQL操作会存放到临时日志文件

查询时，先得到数据库的数据，通过临时日志文件的SQL处理，返回数据

执行COMMIT，将临时日志文件的所有SQL作用到数据库上

执行ROLLBACK，将临时日志文件的所有SQL清空

事务的操作	MySQL操作事务的语句
开启事务
提交事务
回滚事务
查询事务的自动提交情况
设置事务的自动提交方式

回滚点

之前我们事务在回滚的时候都是回滚到开始事务的地方，能不能再回滚事务的时候回滚到一个指定的地方呢？

目标

学习事务回滚点的设置和回滚

我们可以在事务中设置回滚点，点回滚到指定的位置。

设置回滚点语法

savepoint 回滚点名字;

回到回滚点语法

rollback to 回滚点名字;

小结

设置回滚点

saveponit 回滚点名字；

回到回滚点
rollback to 回滚点；

事务的隔离级别

目标

1. 学习并发访问的三个问题
2. 学习mysql的四种隔离级别

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

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致
幻读	一个事务内读取到了别的事务插入的数据，导致前后读取记录行数不同

MySQL数据库有四种隔离级别：上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
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

脏读的演示

目标

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 day23;
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. 查询全局事务隔离级别？select @@tx_isolation;

2. 设置全局事务隔离级别？set global transaction isolation level 级别字符串;

3. 如何解决赃读? 将隔离级别设置为read committed;

不可重复读的演示

目标

解决不可重复读

讲解

不可重复读：一个事务中两次读取的数据内容不一致，这是事务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

幻读的演示

目标

解决幻读

讲解

幻读：**一个事务中指的是多次读取,数据量不一样。**这是insert或delete时引发的问题

幻读演示

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

[外链图片转存失败(img-pjYjK910-1564752140465)(/1551413050280.png)]

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

   [外链图片转存失败(img-7wUXtego-1564752140466)(/1551413102060.png)]

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

[外链图片转存失败(img-KlIZHnaA-1564752140468)(/1551413309793.png)]

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

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执行不了，可以挡住幻读。

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

小结

如何解决幻读？

将隔离级别设置成serializable

隔离级别越高，效率越低。

总结

1. 能够说出多表之间的关系及其建表原则

   一对一：外键唯一

   一对多：在多方建立外键，引用主表的主键

   多对多：建立中间表

2. 能够理理解三⼤大范式

   第一范式：表中的字段不能够再拆分，原子性

   第二范式：1.一张表只描述一件事情，2.每张表添加主键

   第三范式：表的字段引用其他表的主键

3. 能够使⽤用内连接进⾏多表查询

   内连接：

   ​ 隐式内连接：SELECT * FROM 表1, 表2 WHERE 条件;
   ​ 显示内连接: SELECT * FROM 表1 INNER JOIN 表2 ON 表连接条件 WHERE 查询条件;

4. 能够使⽤用左外连接和右外连接进行多表查询

   左外连接：SELECT * FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件; 满足条件的数据显示，左表不满足条件的数据也显示

   右外连接：SELECT * FROM 左表 RIGHT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件; 满足条件的数据显示，右表不满足条件的数据也显示

5. 能够使⽤用⼦查询进⾏多表查询

   SELECT * FROM 表名 WHERE 字段 = (SELECT MAX(age) FROM 表名);

   一个查询的结果作为另一个查询的一部分

   子查询有三种情况：

   单行单列： 作为父查询的条件

   多行单列： 作为父查询的条件 使用 in

   多行多列： 作为虚拟表来处理

6. 能够理理解多表查询的规律

   三步走：

   1. 明确查询哪些表
   2. 明确表连接条件
   3. 后续查询

7. 能够理理解事务的概念

   多条SQL语句组成一个功能，这些SQL语句就组成一个事务

8. 能够说出事务的原理理

   当开启事务后，后续执行的SQL会保存到临时日志文件中，如果提交事务，临时日志文件的SQL会作用到数据上，如果回滚事务，清空临时日志文件。

9. 能够在MySQL中使用事务

   开启事务：START TRANSACTION

   执行SQL

   如果成功：COMMIT 提交事务

   如果失败：ROLLBACK 回滚事务