MySQL第03篇:SQL进阶

Wesley13
• 阅读 904

核心概述:在上一篇,我们学会了使用SQL实现对数据库的基本操作,本篇我们将继续进阶学习SQL,其中有多表操作、数据库设计规范、事务、子查询、数据库的备份与还原及DCL等。 MySQL第03篇:SQL进阶

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

1.1-概述(了解)

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

表与表之间的三种关系

  1. 一对多:最常用的关系 部门和员工
  2. 多对多:学生选课表 和 学生表, 一门课程可以有多个学生选择,一个学生选择多门课程
  3. 一对一:相对使用比较少。员工表 简历表, 公民表 护照表

1.2-一对多(重点)

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

MySQL第03篇:SQL进阶

1.3-多对多(重点)

多对多(m:n) 例如:老师和学生,学生和课程,用户和角色 多对多关系建表原则: 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的 主键。

MySQL第03篇:SQL进阶

1.4-一对一(了解)

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

  1. 外键唯一 :主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE
  2. 外键是主键 :主表的主键和从表的主键,形成主外键关系

MySQL第03篇:SQL进阶

1.5-一对多案例(重点)

需求:一个旅游线路分类中有多个旅游线路

界面

MySQL第03篇:SQL进阶

表与表的关系

MySQL第03篇:SQL进阶

MySQL第03篇:SQL进阶

演示

-- 创建旅游线路分类表 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-多对多案例(重点)

案例描述

一个用户收藏多个线路,一个线路被多个用户收藏 。

MySQL第03篇:SQL进阶

MySQL第03篇:SQL进阶

MySQL第03篇:SQL进阶

演示

/*
创建用户表 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-总结(重点)

表与表的关系

关系的维护

一对多

主外键的关系

多对多

中间表,两个一对多

一对一

  1. 特殊一对多,从表中的外键设置为唯一 2) 从表中的主键又是外键

第二章:数据库设计

2.1-数据规范化(了解)

什么是范式

好的数据库设计对数据的存储性能和后期的程序开发,都会产生重要的影响。建立科学的,规范的数据库就需 要满足一些规则来优化数据的设计和存储,这些规则就称为范式。

三大范式

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

2.2-1NF(了解)

概念

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

班级表

MySQL第03篇:SQL进阶

2.3-2NF(了解)

概念

在满足第一范式的前提下,表中的每一个字段都完全依赖于主键。 所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之,第二范式就是在第一范式的基础上所有列完全 依赖于主键列。当存在一个复合主键包含多个主键列的时候,才会发生不符合第二范式的情况。比如有一个主键有 两个列,不能存在这样的属性,它只依赖于其中一个列,这就是不符合第二范式。

第二范式的特点:

  1. 一张表只描述一件事情。
  2. 表中的每一列都完全依赖于主键

示例

借书证表

MySQL第03篇:SQL进阶

分成两张表

MySQL第03篇:SQL进阶

2.4-3NF(了解)

概念

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

示例

学生信息表

MySQL第03篇:SQL进阶

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

拆分成两张表

MySQL第03篇:SQL进阶

2.5-三大范式小结(了解)

范式

特点

1NF

原子性:表中每列不可再拆分。

2NF

不产生局部依赖,一张表只描述一件事情

3NF

不产生传递依赖,表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。

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

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 文件即可。

MySQL第03篇:SQL进阶

MySQL第03篇:SQL进阶

还原数据库中的数据

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

MySQL第03篇:SQL进阶

MySQL第03篇: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 列或多列。

多表查询的分类

MySQL第03篇:SQL进阶

4.2-笛卡尔积现象(了解)

什么是笛卡尔积现象?

-- 需求:查询所有的员工和所有的部门
select * from emp,dept;

结果分析

MySQL第03篇:SQL进阶

如何清除笛卡尔积无用的数据影响?

我们发现不是所有的数据组合都是有用的,只有员工表.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`;

MySQL第03篇:SQL进阶

显示内连接

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

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

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

MySQL第03篇:SQL进阶

1.确定查询哪些表

select * from emp inner join dept;

MySQL第03篇:SQL进阶

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

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

MySQL第03篇:SQL进阶

3.确定查询条件,我们查询的是唐僧的信息,员工表.name='唐僧'

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

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

内连接总结

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

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

第五章:子查询

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-子查询的三种情况(了解)

子查询的结果是单行单列

MySQL第03篇:SQL进阶

子查询的结果是多行单列

MySQL第03篇:SQL进阶

**子查询的结果是多行多列 **

MySQL第03篇:SQL进阶

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

功能

SQL语句

开启事务

start transaction;

提交事务

commit;

回滚事务

rollback;

手动提交事务的执行过程

MySQL第03篇:SQL进阶

  1. 执行成功的情况: 开启事务 → 执行多条 SQL 语句 → 成功提交事务
  2. 执行失败的情况: 开启事务 → 执行多条 SQL 语句 → 事务的回滚

案例1:模拟张三给李四转 500 元钱(成功) 目前数据库数据如下

MySQL第03篇:SQL进阶

1- 使用 DOS 控制台进入 MySQL

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

3- 使用 SQLYog 查看数据库:发现数据并没有改变

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

5- 使用 SQLYog 查看数据库:发现数据改变

MySQL第03篇:SQL进阶

**案例演示 2:事务回滚 **

模拟张三给李四转 500 元钱(失败) 目前数据库数据如下:

MySQL第03篇:SQL进阶

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

2- 使用 SQLYog 查看数据库:发现数据并没有改变

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

4- 使用 SQLYog 查看数据库:发现数据没有改变

MySQL第03篇:SQL进阶

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

6.3-自动提交事务(了解)

概述

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

MySQL第03篇:SQL进阶

案例:自动提交事务

  1. 将金额重置为 1000
  2. 更新其中某一个账户
  3. 使用 SQLYog 查看数据库:发现数据已经改变

MySQL第03篇:SQL进阶

取消自动提交

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

MySQL第03篇:SQL进阶

@@表示全局变量,1 表示开启,0 表示关闭

**取消自动提交事务 **

MySQL第03篇:SQL进阶

执行更新语句,使用 SQLYog 查看数据库,发现数据并没有改变 ,在控制台执行 commit 提交任务

MySQL第03篇:SQL进阶

6.4-事务原理(理解)

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

原理图

MySQL第03篇:SQL进阶

事务的步骤

  1. 客户端连接数据库服务器,创建连接时创建此用户临时日志文件
  2. 开启事务以后,所有的操作都会先写入到临时日志文件中
  3. 所有的查询操作从表中查询,但会经过日志文件加工后才返回
  4. 如果事务提交则将日志文件中的数据写到表中,否则清空日志文件

6.5-回滚点(重点)

什么是回滚点

在某些成功的操作完成之后,后续的操作有可能成功有可能失败,但是不管成功还是失败,前面操作都已经成 功,可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称 之为回滚点。

回滚点的操作语句

操作

语句

设置回滚点

savepoint 名字

回到回滚点

rollback to 名字

具体操作

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

设置回滚点可以让我们在失败的时候回到回滚点,而不是回到事务开启的时候。

6.6-事务的四大特性(了解)

事务特性

含义

原子性(Atomicity)

每个事务都是一个整体,不可再拆分,事务中所有的 SQL 语句要么都执行成功, 要么都失败。

一致性(Consistency)

事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前2个人的 总金额是 2000,转账后 2 个人总金额也是 2000

隔离性(Isolation)

事务与事务之间不应该相互影响,执行时保持隔离的状态。

持久性(Durability)

一旦事务执行成功,对数据库的修改是持久的。就算关机,也是保存下来的。

6.7-事务的隔离级别(了解)

隔离级别介绍

概述

事务在操作时的理想状态: 所有的事务之间保持隔离,互不影响。因为并发操作,多个用户同时访问同一个 数据。可能引发并发访问的问题:

并发访问的问题

含义

脏读

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

不可重复读

一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的,这 是事务 update 时引发的问题

幻读

一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致 的,这是 insert 或 delete 时引发的问题

MySQL 数据库有四种隔离级别

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

级别

名字

隔离级别

脏读

不可重复读

幻读

数据库默认隔离级别

1

读未提交

read uncommitted

2

读已提交

read committed

Oracle 和 SQL Server

3

可重复读

repeatable read

MySQL

4

串行化

serializable

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

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;

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

【03-A 窗口更新 2 个人的账户数据,未提交】

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

MySQL第03篇:SQL进阶

【04-B 窗口查询账户】

MySQL第03篇:SQL进阶

【05-A 窗口回滚 】

MySQL第03篇:SQL进阶

【06-B 窗口查询账户,钱没了 】

MySQL第03篇:SQL进阶

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

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

将数据进行恢复:

UPDATE account SET balance = 1000;

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

set global transaction isolation level read committed;

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

【04-A 更新 2 个人的账户,未提交 】

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

MySQL第03篇:SQL进阶

【05-B 窗口查询账户】

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

【08-B 窗口查看账户 】

MySQL第03篇:SQL进阶

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

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

问题演示

将数据进行恢复:

UPDATE account SET balance = 1000;

【01-开启 A 窗口 】

set global transaction isolation level read committed;

MySQL第03篇:SQL进阶

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

start transaction;
select * from account;

MySQL第03篇:SQL进阶

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

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

MySQL第03篇:SQL进阶

【04-B 窗口查询 】

MySQL第03篇:SQL进阶

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

解决不可重复读的问题

将全局的隔离级别进行提升为:repeatable read 将数据进行恢复:

UPDATE account SET balance = 1000;

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

set global transaction isolation level repeatable read;

MySQL第03篇:SQL进阶

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

start transaction;
select * from account

MySQL第03篇:SQL进阶

【03-A 窗口更新数据 】

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

MySQL第03篇:SQL进阶

【04-B 窗口查询 】

MySQL第03篇:SQL进阶

MySQL第03篇:SQL进阶

结论:同一个事务中为了保证多次查询数据一致,必须使用 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;

MySQL第03篇:SQL进阶

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

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

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

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

【07-B 窗口中 commit 提交当前事务 】

MySQL第03篇:SQL进阶

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

MySQL第03篇:SQL进阶

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

第七章:DCL

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

MySQL第03篇:SQL进阶

7.1-创建用户

语法

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

关键字说明

关键字

说明

用户名

将创建的用户名

主机名

指定该用户在哪个主机上可以登陆,如果是本地用户可用 localhost,
如果想让该用户可以 从任意远程主机登陆,可以使用通配符%

密码

该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器

具体操作

-- 创建 user1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123 
create user 'user1'@'localhost' identified by '123';
-- 创建 user2 用户可以在任何电脑上登录 mysql 服务器,密码为 123
create user 'user2'@'%' identified by '123';

注:创建的用户名都在 mysql 数据库中的 user 表中可以查看到,密码经过了加密。

MySQL第03篇:SQL进阶

7.2-给用户授权

用户创建之后,没什么权限!需要给用户授权

语法

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

关键字说明

关键字

说明

GRANT…ON…TO

授权关键字

权限

授予用户的权限,如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。
如果要授 予所有的权限则使用 ALL

数据库名.表名

该用户可以操作哪个数据库的哪些表。
如果要授予该用户对所有数据库和表的相应操作 权限则可用*表示,如 *.*

'用户名'@'主机名'

给哪个用户授权,注:有 2 对单引号

具体操作

-- 给 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'; '用户名'@'主机 名';

关键字

说明

REVOKE…ON…FROM

撤销授权的关键字

权限

用户的权限,如 CREATE、ALTER、SELECT、INSERT、UPDATE 等,
所有的权 限则使用 ALL

数据库名.表名

对哪些数据库的哪些表,
如果要取消该用户对所有数据库和表的操作权限则可用 *表 示,如*.*

'用户名'@'主机名'

给哪个用户撤销

具体操作

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

7.4-查看权限

语法

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

具体操作

MySQL第03篇:SQL进阶

注:usage 是指连接(登陆)权限,建立一个用户,就会自动授予其 usage 权限(默认授予)。

7.5-删除用户

语法

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

具体操作

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

MySQL第03篇:SQL进阶

7.6-修改管理员密码

语法

mysqladmin -uroot -p password 新密码

注意:需要在未登陆 MySQL 的情况下操作,新密码不需要加上引号。

具体操作

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

MySQL第03篇:SQL进阶

7.7-修改普通用户密码

语法

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

注意:需要在登陆 MySQL 的情况下操作,新密码要加单引号。

具体操作

  1. 将'user1'@'localhost'的密码改成'666666'
  2. 使用新密码登录,老密码登录不了

MySQL第03篇:SQL进阶

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这