关于mysql:MySQL第03篇SQL进阶

外围概述：在上一篇，咱们学会了应用 SQL 实现对数据库的基本操作，本篇咱们将持续进阶学习 SQL，其中有多表操作、数据库设计规范、事务、子查问、数据库的备份与还原及 DCL 等。

第一章：表与表之间的关系

1.1- 概述（理解）

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

表与表之间的三种关系

1. 一对多：最罕用的关系 部门和员工
2. 多对多：学生选课表 和 学生表，一门课程能够有多个学生抉择，一个学生抉择多门课程
3. 一对一：绝对应用比拟少。员工表 简历表，公民表 护照表

1.2- 一对多（重点）

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

1.3- 多对多（重点）

多对多（m:n）例如：老师和学生，学生和课程，用户和角色 多对多关系建表准则: 须要创立第三张表，两头表中至多两个字段，这两个字段别离作为外键指向各自一方的 主键。

1.4- 一对一（理解）

一对一（1:1）在理论的开发中利用不多. 因为一对一能够创立成一张表。两种建表准则：

1. 外键惟一：主表的主键和从表的外键（惟一），造成主外键关系，外键惟一 UNIQUE
2. 外键是主键：主表的主键和从表的主键，造成主外键关系

1.5- 一对多案例（重点）

需要：一个游览线路分类中有多个游览线路

界面

表与表的关系

演示

-- 创立游览线路分类表 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 ('周边游'), ('出境游'), ('国内游'), ('港澳游');
select * from tab_category;
-- 创立游览线路表 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', 1),
(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-
22', 3),
(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州
往返 特价团】', 1699,'2018-01-27', 2),
23 / 26
(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',
2),
(NULL, ' 香港迪士尼乐园自在行 2 天【永东跨境巴士广东至迪士尼去程交通 + 迪士尼一日门票 + 香港如心海景酒店
暨会议核心规范房 1 晚住宿】', 799,'2018-04-10', 4);

1.6- 多对多案例（重点）

案例形容

一个用户珍藏多个线路，一个线路被多个用户珍藏。

演示

/*
创立用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，惟一，非空
password 明码长度 30，非空
name 实在姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/
create table tab_user (
 uid int primary key auto_increment,
 username varchar(100) unique not null,
 password varchar(30) not null,
 name varchar(100),
 birthday date,
 sex char(1) default '男',
 telephone varchar(11),
 email varchar(100)
)
-- 增加用户数据
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
select * from tab_user;
/*
创立珍藏表 tab_favorite
rid 游览线路 id，外键
date 珍藏工夫
uid 用户 id，外键
rid 和 uid 不能反复，设置复合主键，同一个用户不能珍藏同一个线路两次
*/
create table tab_favorite (
 rid int,
 date datetime,
 uid int,
 -- 创立复合主键
 primary key(rid,uid),
 foreign key (rid) references tab_route(rid),
 foreign key(uid) references tab_user(uid)
)
-- 减少珍藏表数据
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王抉择厦门
(2, '2018-02-11', 1), -- 老王抉择桂林
(3, '2018-03-21', 1), -- 老王抉择泰国
(2, '2018-04-21', 2), -- 小王抉择桂林
(3, '2018-05-08', 2), -- 小王抉择泰国
(5, '2018-06-02', 2); -- 小王抉择迪士尼
select * from tab_favorite;

1.7- 总结（重点）

第二章：数据库设计

2.1- 数据规范化（理解）

什么是范式

好的数据库设计对数据的存储性能和前期的程序开发，都会产生重要的影响。建设迷信的，标准的数据库就需 要满足一些规定来优化数据的设计和存储，这些规定就称为范式。

三大范式

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

2.2-1NF（理解）

概念

数据库表的每一列都是不可分割的原子数据项，不能是汇合、数组等非原子数据项。即表中的某个列有多个值 时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。

班级表

2.3-2NF（理解）

概念

在满足第一范式的前提下，表中的每一个字段都齐全依赖于主键。所谓齐全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的根底上所有列齐全 依赖于主键列。当存在一个复合主键蕴含多个主键列的时候，才会产生不合乎第二范式的状况。比方有一个主键有 两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不合乎第二范式。

第二范式的特点：

1. 一张表只形容一件事件。
2. 表中的每一列都齐全依赖于主键

示例

借书证表

分成两张表

2.4-3NF（理解）

概念

在满足第二范式的前提下，表中的每一列都间接依赖于主键，而不是通过其它的列来间接依赖于主键。简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的根底上，任何非主列不得传递 依赖于主键。所谓传递依赖，指的是如果存在 ”A → B → C” 的决定关系，则 C 传递依赖于 A。因而，满足第三范 式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y

示例

学生信息表

存在传递的决定关系：学号→所在学院 →学院地点

拆分成两张表

2.5- 三大范式小结（理解）

第三章：数据库备份和还原

3.1- 备份的利用场景（理解）

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

3.2- 备份与还原的语句（重点）

备份格局：DOS 下，未登录的时候。这是一个可执行文件 exe，在 bin 文件夹

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

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

-- 备份 day21 数据库中的数据到 d:\day21.sql 文件中
mysqldump -uroot -proot day21 > d:/day21.sql

还原格局：mysql 中的命令，须要登录后才能够操作

USE 数据库;
SOURCE 导入文件的门路;

还原 day21 数据库中的数据，留神：还原的时候须要先登录 MySQL, 并选中对应的数据库。

1. 删除 day21 数据库中的所有表
2. 登录 MySQL
3. 选中数据库
4. 应用 SOURCE 命令还原数据
5. 查看还原后果

use day21;
source d:/day21.sql;

3.3- 图形化界面备份与还原（重点）

备份数据库中的数据

1. 选中数据库，右键”备份 / 导出”
2. 指定导出门路，保留成.sql 文件即可。

还原数据库中的数据

1. 删除数据库
2. 数据库列表区域右键“执行 SQL 脚本”，指定要执行的 SQL 文件，执行即可

第四章：多表查问

4.1- 多表查问介绍（理解）

数据筹备

# 创立部门表
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,
 foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
)
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);

为什么要多表查问？

比方：咱们想查问孙悟空的名字和他所在的部门的名字，则须要应用多表查问。

如果一条 SQL 语句查问多张表，因为查问后果在多张不同的表中。每张表取 1 列或多列。

多表查问的分类

4.2- 笛卡尔积景象（理解）

什么是笛卡尔积景象？

-- 需要：查问所有的员工和所有的部门
select * from emp,dept;

后果剖析

如何革除笛卡尔积无用的数据影响？

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

-- 设置过滤条件 Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- 查问员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

4.3- 内连贯（重点）

用右边表的记录去匹配左边表的记录，如果符合条件的则显示。如：从表. 外键 = 主表. 主键

隐式内连贯

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

SELECT 字段名 FROM 左表, 右表 WHERE 条件
select * from emp,dept where emp.`dept_id` = dept.`id`;

显示内连贯

显示内连贯：应用 INNER JOIN … ON 语句, 能够省略 INNER

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

需要： 查问唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，咱们发现须要联结 2 张表同时能力 查问出须要的数据，应用内连贯。

1. 确定查问哪些表

select * from emp inner join dept;

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

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

3. 确定查问条件，咱们查问的是唐僧的信息，员工表.name=’ 唐僧 ’

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

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

select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

5. 咱们发现写表名有点长，能够给表取别名，显示的字段名也应用别名

select e.`id` 编号,e.`name` 姓名,e.`gender` 性别,e.`salary` 工资,d.`name` 部门名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

内连贯总结

1. 确定查问哪些表
2. 确定表连贯的条件
3. 确定查问的条件
4. 确定查问的字段

4.4- 左外连贯（重点）

左外连贯：应用 LEFT OUTER JOIN ... ON，OUTER 能够省略

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

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL。能够了解为：在内连贯的根底上保障左表的数据全副显示(左表是部门，右表员工)

-- 在部门表中减少一个销售部
insert into dept (name) values ('销售部');
select * from dept;
-- 应用内连贯查问
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- 应用左外连贯查问
select * from dept d left join emp e on d.`id` = e.`dept_id`;

4.5- 右外连贯（重点）

右外连贯：应用 RIGHT OUTER JOIN ... ON，OUTER 能够省略

SELECT 字段名 FROM 左表 RIGHT [OUTER]JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL。能够了解为：在内连贯的根底上保障右表的数据全副显示

-- 在员工表中减少一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 应用内连贯查问
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 应用右外连贯查问
select * from dept right join emp on dept.`id` = emp.`dept_id`;

第五章：子查问

5.1 什么是子查问（理解）

子查问的概念

1. 一个查问的后果做为另一个查问的条件
2. 有查问的嵌套，外部的查问称为子查问
3. 子查问要应用括号

演示

-- 需要：查问开发部中有哪些员工
select * from emp;
-- 通过两条语句查问
select id from dept where name='开发部' ;
select * from emp where dept_id = 1;
-- 应用子查问
select * from emp where dept_id = (select id from dept where name='市场部');

5.2- 子查问的三种状况（理解）

子查问的后果是单行单列

子查问的后果是多行单列

子查问的后果是多行多列

5.3- 子查问后果是一个值（重点）

子查问后果只有是单行单列，必定在 WHERE 前面作为条件，父查问应用：比拟运算符，如：>、<、<>、= 等

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

案例：查问工资最高的员工是谁？

-- 1) 查问最高工资是多少
select max(salary) from emp;
-- 2) 依据最高工资到员工表查问到对应的员工信息
select * from emp where salary = (select max(salary) from emp);

查问工资小于平均工资的员工有哪些？

-- 1) 查问平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查问小于均匀的员工信息
select * from emp where salary < (select avg(salary) from emp);

5.4- 子查问后果是多行单列（重点）

子查问后果是单例多行，后果集相似于一个数组，父查问应用 IN 运算符

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

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

-- 先查问大于 5000 的员工所在的部门 id
select dept_id from emp where salary > 5000;
-- 再查问在这些部门 id 中部门的名字 Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

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

-- 先查问开发部与财务部的 id
select id from dept where name in('开发部','财务部');
-- 再查问在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));

5.5- 子查问的后果是多行多列（重点）

子查问后果只有是多列，必定在 FROM 前面作为表

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

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

查问出 2011 年当前入职的员工信息，包含部门名称

-- 查问出 2011 年当前入职的员工信息，包含部门名称
-- 在员工表中查问 2011-1-1 当前入职的员工
select * from emp where join_date >='2011-1-1';
-- 查问所有的部门信息，与下面的虚构表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;

也能够应用表连贯

select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

5.6- 总结（重点）

1. 子查问后果只有是单列，则在 WHERE 前面作为条件
2. 子查问后果只有是多列，则在 FROM 前面作为表进行二次查问

第六章：事务

6.1- 事务概述（理解）

事务的利用场景

什么是事务：在理论的开发过程中，一个业务操作如：转账，往往是要屡次拜访数据库能力实现的。转 账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。

事务执行是一个整体，所有的 SQL 语句都必须执行胜利。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

转账操作

模仿张三给李四转 500 元钱，一个转账的业务操作起码要执行上面的 2 条语句：张三账号 -500 李四账号 +500

-- 创立数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 增加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);
-- 张三账号 -500
update account set balance = balance - 500 where name='张三';
-- 李四账号 +500
update account set balance = balance + 500 where name='李四';

假如当张三账号上 -500 元, 服务器解体了。李四的账号并没有 +500 元，数据就呈现问题了。咱们须要保障其中 一条 SQL 语句呈现问题，整个转账就算失败。只有两条 SQL 都胜利了转账才算胜利。这个时候就须要用到事务。

6.2- 手动提交事务（重点）

MYSQL 中能够有两种形式进行事务的操作：

1. 手动提交事务
2. 主动提交事务

手动提交事务的 SQL

手动提交事务的执行过程

1. 执行胜利的状况：开启事务 → 执行多条 SQL 语句 → 胜利提交事务
2. 执行失败的状况：开启事务 → 执行多条 SQL 语句 → 事务的回滚

案例 1：模仿张三给李四转 500 元钱（胜利）目前数据库数据如下

1- 应用 DOS 控制台进入 MySQL

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

3- 应用 SQLYog 查看数据库：发现数据并没有扭转

4- 在控制台执行 commit 提交事务：

5- 应用 SQLYog 查看数据库：发现数据扭转

案例演示 2：事务回滚

模仿张三给李四转 500 元钱（失败）目前数据库数据如下：

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

2- 应用 SQLYog 查看数据库：发现数据并没有扭转

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

4- 应用 SQLYog 查看数据库：发现数据没有扭转

总结: 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行扭转。如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

6.3- 主动提交事务（理解）

概述

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

案例：主动提交事务

1. 将金额重置为 1000
2. 更新其中某一个账户
3. 应用 SQLYog 查看数据库：发现数据曾经扭转

勾销主动提交

查看 MySQL 是否开启主动提交事务

@@示意全局变量，1 示意开启，0 示意敞开

勾销主动提交事务

执行更新语句，应用 SQLYog 查看数据库，发现数据并没有扭转 , 在控制台执行 commit 提交工作

6.4- 事务原理（了解）

事务开启之后, 所有的操作都会长期保留到事务日志中, 事务日志只有在失去 commit 命令才会同步到数据表 中，其余任何状况都会清空事务日志(rollback，断开连接)

原理图

事务的步骤

1. 客户端连贯数据库服务器，创立连贯时创立此用户长期日志文件
2. 开启事务当前，所有的操作都会先写入到长期日志文件中
3. 所有的查问操作从表中查问，但会通过日志文件加工后才返回
4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件

6.5- 回滚点（重点）

什么是回滚点

在某些胜利的操作实现之后，后续的操作有可能胜利有可能失败，然而不论胜利还是失败，后面操作都曾经成 功，能够在以后胜利的地位设置一个回滚点。能够供后续失败操作返回到该地位，而不是返回所有操作，这个点称 之为回滚点。

回滚点的操作语句

具体操作

1. 将数据还原到 1000
2. 开启事务
3. 让张三账号减 3 次钱，每次 10 块
4. 设置回滚点：savepoint three_times;
5. 让张三账号减 4 次钱，每次 10 块
6. 回到回滚点：rollback to three_times;
7. 剖析执行过程

设置回滚点能够让咱们在失败的时候回到回滚点，而不是回到事务开启的时候。

6.6- 事务的四大个性（理解）

6.7- 事务的隔离级别（理解）

隔离级别介绍

概述

事务在操作时的现实状态：所有的事务之间放弃隔离，互不影响。因为并发操作，多个用户同时拜访同一个 数据。可能引发并发拜访的问题：

MySQL 数据库有四种隔离级别

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

隔离级别越高，性能越差，安全性越高。

MySQL 事务隔离级别相干的命令

1. 查问隔离级别 select @@tx_isolation;
2. 设置隔离级别 set global transaction isolation level 级别字符串;

设置事务隔离级别，须要退出 MySQL 再从新登录能力看到隔离级别的变动

脏读问题及解决方案演示

演示

将数据进行复原：UPDATE account SET balance = 1000;

【01- 关上 A 窗口登录 MySQL，设置全局的隔离级别为最低】

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

【02- 关上 B 窗口,AB 窗口都开启事务】

【03-A 窗口更新 2 集体的账户数据，未提交】

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

【04-B 窗口查问账户】

【05-A 窗口回滚】

【06-B 窗口查问账户，钱没了】

脏读十分危险的，比方张三向李四购买商品，张三开启事务，向李四账号转入 500 块，而后打电话给李四说钱 曾经转了。李四一查问钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

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

将数据进行复原：

UPDATE account SET balance = 1000;

【01- 在 A 窗口设置全局的隔离级别为 read committed】

set global transaction isolation level read committed;

【02-B 窗口退出 MySQL，B 窗口再进入 MySQL】

【03-AB 窗口同时开启事务】

【04-A 更新 2 集体的账户，未提交】

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

【05-B 窗口查问账户】

【06-A 窗口 commit 提交事务】

【08-B 窗口查看账户】

论断：read committed 的形式能够防止脏读的产生

不可反复读问题及解决方案

问题演示

将数据进行复原：

UPDATE account SET balance = 1000;

【01- 开启 A 窗口】

set global transaction isolation level read committed;

【02- 开启 B 窗口，在 B 窗口开启事务】

start transaction;
select * from account;

【03- 在 A 窗口开启事务，并更新数据】

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

【04-B 窗口查问】

两次查问输入的后果不同，到底哪次是对的？不晓得以哪次为准。很多人认为这种状况就对了，毋庸困惑，当然是前面的为准。咱们能够思考这样一种状况，比方银行程序须要将查问后果别离输入到电脑屏幕和发短信给客 户，后果在一个事务中针对不同的输入目的地进行的两次查问不统一，导致文件和屏幕中的后果不统一，银行工作 人员就不晓得以哪个为准了。

解决不可反复读的问题

将全局的隔离级别进行晋升为：repeatable read 将数据进行复原：

UPDATE account SET balance = 1000;

【01-A 窗口设置隔离级别为：repeatable read】

set global transaction isolation level repeatable read;

【02-B 窗口退出 MySQL，B 窗口再进入 MySQL，开启事务，查问数据】

start transaction;
select * from account

【03-A 窗口更新数据】

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

【04-B 窗口查问】

论断：同一个事务中为了保障屡次查问数据统一，必须应用 repeatable read 隔离级别。

幻读的演示

在 MySQL 中无奈看到幻读的成果。

但咱们能够将事务隔离级别设置到最高，以挡住幻读的产生 将数据进行复原：

UPDATE account SET balance = 1000;

【01- 开启 A 窗口】

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

【02-A 窗口退出 MySQL，A 窗口从新登录 MySQL】

start transaction;
select count(*) from account;

【03- 再开启 B 窗口，登录 MySQL】

【04- 在 B 窗口中开启事务，增加一条记录】

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

【05- 在 A 窗口中 commit 提交事务，B 窗口中 insert 语句会在 A 窗口事务提交后立马运行】

【06- 在 A 窗口中接着查问，发现数据不变】

【07-B 窗口中 commit 提交以后事务】

【08-A 窗口就能看到最新的数据】

论断：应用 serializable 隔离级别，一个事务没有执行完，其余事务的 SQL 执行不了，能够挡住幻读

第七章：DCL

咱们当初默认应用的都是 root 用户，超级管理员，领有全副的权限。然而，一个公司外面的数据库服务器下面 可能同时运行着很多个我的项目的数据库。所以，咱们应该能够依据不同的我的项目建设不同的用户，调配不同的权限来管 理和保护数据库。注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

7.1- 创立用户

语法

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

关键字阐明

具体操作

-- 创立 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，明码为 123 
create user 'user1'@'localhost' identified by '123';
-- 创立 user2 用户能够在任何电脑上登录 mysql 服务器，明码为 123
create user 'user2'@'%' identified by '123';

注：创立的用户名都在 mysql 数据库中的 user 表中能够查看到，明码通过了加密。

7.2- 给用户受权

用户创立之后，没什么权限！须要给用户受权

语法

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

关键字阐明

具体操作

-- 给 user1 用户调配对 test 这个数据库操作的权限：创立表，批改表，插入记录，更新记录，查问
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';
-- 给 user2 用户调配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';
-- 注：用户名和主机名要与下面创立的雷同，要加单引号。

7.3- 撤销受权

语法

REVOKE 权限 1, 权限 2... ON 数据库. 表名 revoke all on test.* from 'user1'@'localhost'; '用户名'@'主机 名';

具体操作

-- 撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';

7.4- 查看权限

语法

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

具体操作

注：usage 是指连贯（登陆）权限，建设一个用户，就会主动授予其 usage 权限（默认授予）。

7.5- 删除用户

语法

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

具体操作

-- 删除 user2
drop user 'user2'@'%';

7.6- 批改管理员明码

语法

mysqladmin -uroot -p password 新密码

留神：须要在未登陆 MySQL 的状况下操作，新密码不须要加上引号。

具体操作

1. 将 root 管理员的新密码改成 123456
2. 要求输出旧明码
3. 应用新密码登录

7.7- 批改普通用户明码

语法

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

留神：须要在登陆 MySQL 的状况下操作，新密码要加单引号。

具体操作

1. 将 ’user1’@’localhost’ 的明码改成 ’666666′
2. 应用新密码登录，老明码登录不了