Day03-数据库管理(事务管理,用户管理,MySQL8的部分新特性)

Day03 数据库管理

学习目标

• 能够说出事务的概念和特点
• 能够使用代码开启和处理事务
• 能够说出事务脏读，幻读和不可重复读的概念
• 能够说出事务隔离级别的意义
• 能够使用数据创建和删除用户
• 能够对用户进行权限管理

1. 事务管理

在日常生活中，经常有A用户给B用户转账这种需求。转账100元的基本逻辑就是将A账户的里的余额减掉100元,然后再将B账户里的余额增加100元。

思考：是否可能出现A账户的100元减掉了，但是此时数据库出现了问题，账户B里没有成功增加100元的这种情况？

解决方法：将A账户减掉100元和B账户增加100元的两个操作放到一起执行，要么都成功（A减100,B加100，转账完成）；要么都失败（A没有减100，B也没有加100，转账失败，但是账户的余额不变）。

1.1 事务的概念

事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的操作都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃本次事务中的所有修改，将整个事务回滚(rollback)到最初状态。

注意：在MySQL中，只有InnoDB存储引擎才支持事务。

show variables like "%storage_engine%";  -- 查看数据使用的存储引擎
show engines;  -- 查看各存储引擎
1
2

1.2 事务的特性

在写入或更新资料的过程中, 为保证事务 (transaction) 是正确可靠的, 所必须具备的四个特性 (ACID)：

• 原子性（Atomicity）
  • 事务中的所有操作, 要么全部完成, 要么全部不完成, 不会结束在中间某个环节。
  • 事务在执行过程中发生错误, 会被回滚 (Rollback) 到事务开始前的状态, 就像这个事务从来没有执行过一样。
• 一致性（Consistency）
  • 在事务开始之前和事务结束以后, 数据库的完整性没有被破坏。
  • 这表示写入的资料必须完全符合所有的预设规则, 这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

/*
原子性：
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
成功：张三账号变为500，李四变为1500，
失败：张三账号是1000，李四还是1000.


#一致性
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000

要么他俩的余额不变， 还是1000，总和就是2000
要么他俩的余额有变化，张三500，李四1500，总和仍然是2000
错误：
张三500，李四1000，总和是1500，结果不对
张三1000，李四1500，总和是2500，结果不对

#隔离性
例如：张三要给李四转500，
      王五要给李四转500，
      张三转账是否成功，和王五是否转账成功无关。
      
      
#持久性：
例如：张三要给李四转500，一旦成功提交，就转账成功，撤不回来了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

1.3 语法

mysql默认是自动提交，执行一句就提交一句，即默认情况下，每一条sql都是一个独立的sql。

• 关闭自动提交事务: set autocommit=false
• 开启事务: BEGIN或START TRANSACTION
• 提交事务: COMMIT, 提交会让所有修改生效
• 回滚: ROLLBACK, 撤销正在进行的所有未提交的修改
• 创建保存点: SAVEPOINT identifier
• 删除保存点: RELEASE SAVEPOINT identifier
• 把事务回滚到保存点: ROLLBACK TO identifier

例如：

SET AUTOCOMMIT = false;
START TRANSACTION;
UPDATE t_employee SET salary = 0 
WHERE ename = '李冰冰'; 
commit;
1
2
3
4
5

1.4 事务的并发问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

• 脏读：一个事务读取了另一个事务未提交数据；
• 不可重复读：同一个事务中前后两次读取同一条记录不一样。因为被其他事务修改了并且提交了。
• 幻读：一个事务读取了另一个事务新增、删除的记录情况，记录数不一样，像是出现幻觉。

1.5 事务隔离级别

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。**一个事务与其他事务隔离的程度称为隔离级别。**数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

数据库提供的 4 种事务隔离级别：

• Oracle 支持的 2 种事务隔离级别：READ-COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
• Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE-READ。在mysql中REPEATABLE READ的隔离级别也可以避免幻读了。

1.6 设置和查看隔离级别

每启动一个 mysql 客户端程序, 就会获得一个单独的数据库连接。每个数据库连接都有一个变量来记录当前连接的事务隔离级别。mysql服务器里也有一个全局变量，用来表示所有连接的默认事务隔离级别。

• 查看当前mysql连接的隔离级别:

select @@tx_isolation;  -- MySQL5.7
select @@transaction_isolation; -- MySQL8.0
1
2

• 查看全局的隔离级别：

select @@global.tx_isolation;  -- MySQL5.7
select @@global.transaction_isolation; -- MySQL8.0
1
2

• 设置当前 mysql连接的隔离级别:

set tx_isolation ='repeatable-read'; -- MySQL 5.7
set transaction_isolation='repeatable-read'; -- MySQL8.0
1
2

• 设置数据库系统的全局的隔离级别:

set global tx_isolation ='read-committed';  -- MySQL5.7
set @@global.transaction_isolation='read-committed';  -- MySQL8.0
1
2

注意：这里的隔离级别中间是减号，不是下划线。

2 用户管理

初次登录MySQL服务器时，能够看到里面有四个默认的数据库，其中mysql库主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。

2.1 创建删除用户

MySQL数据库默认有一个root用户，这个用户拥有着至高无上的权限。为了保证数据的安全，我们不会轻易的使用root用户，而是创建一些新的用户，并赋予有限的权限，从而保证数据库的安全。

MySQL数据库中的用户都保存在mysql库的user表中，添加用户其实就是往mysql.user表添加一条记录。但是我们不直接修改mysql.user表，而是通过特定的命令来添加用户。

• 添加用户

  create user `username`@`允许登录的地址` identified by "密码";  
  create user chris@localhost identified by "abcd1234";  -- 创建了用户chris,密码是abcd1234,它只能本机登录
  1
  2

• 删除用户

  drop user `username`@`允许登录的主机名`;  -- 使用 drop 命令删除用户
  drop user chris@localhost;
  1
  2

2.2 权限管理

MySQL中的权限验证分为两个阶段:

1. 第一阶段为连接验证，主要限制用户连接 MySQL服务器时使用密码是否正确，IP地址是否允许。
2. 第二阶段为操作检查，主要检查用户执行的指令是否被允许，一般非管理员账户不被允许执行 drop、delete 等危险操作

使用命令创建用户以后，这个用户默认是没有任何权限的，也就是说，用户不能做任何的操作。我们在给用户赋予权限的时候，要遵守一定的原则，不能直接把所有的权限都给到用户。

2.2.1 权限赋予的原则

• 只授予能满足需要的最小权限，防止用户执行危险操作。
• 限制用户的登录主机，防止不速之客登录数据库。
• 禁止或删除没有密码的用户。
• 禁止用户使用弱密码。
• 定期清理无效的用户，回收权限或者删除用户。

2.2.2 权限赋予

show privileges;  -- 可以查看系统中所有可用的权限
1

/*
权限列表，可以选择 SELECT,UPDATE,DELETE等，也可以使用 ALL PRIVILEGES 赋予所有的权限。
数据库名，表示权限赋予给了这个用户能够那个数据库。如果是所有数据库，可以使用 *
表名，表示权限赋予给了这个用户能够访问哪个表。如果是所有的表，可以使用 *
WITH GRANT OPTION, 表示权限是否还能向下下发。
*/
GRANT 权限列表 on 数据库名.表名 to '用户名'@'主机' [WITH GRANT OPTION];
-- 刷新使权限生效
flush privileges;


GRANT SELECT ON *.* to chris@localhost;  -- chirs 用户能够查询所有数据库的所有表格，但是这个权限不能再下发
GRANT ALL PRIVILEGES *.* to chris@localhost WITH GRANT OPTION; -- chirs 用户对所有数据所有表格都有权限，而且这个权限可以下发!
1
2
3
4
5
6
7
8
9
10
11
12
13

2.2.3 权限回收

show grants;  -- 查看当前用户的权限
show grants for 用户名@主机; -- 查看指定用户的权限

revoke 权限列表 ON 数据库名.表名 FROM '用户名'@'主机IP地址';
1
2
3
4

2.2.4 登录管理

MySQL的root用户默认只允许本机登录，不能通过其他计算机连接到本机的MySQL服务器。

mysql -h192.168.2.134 -uroot -p  # 连接到 192.68.2.134，一个远程的MySQL服务器，会连接失败
1

如果想要让自己电脑上安装的MySQL服务器被网络上的其他电脑访问，需要对账户允许登录的服务器进行修改。

update user set host="%" where user="root";  -- 允许 root 用户从任意的主机登录
1

请注意，上述的操作非常危险！root用户可以通过网络上任意一台电脑登录，由于root用户拥有至高无上的权限，这样会导致数据库非常的不安全！

3. MySQL8的部分新特性

MySQL8.0在MySQL5.7的基础上做了较大改动，下面我们介绍一下MySLQ8的部分新特性。

3.1 系统表全部为InnoDB表

从 MySQL 8.0 开始，mysql 系统表和数据字典表使用 InnoDB 存储引擎，存储在 MySQL 数据目录下的 mysql.ibd 表空间文件中。在 MySQL 5.7 之前，这些系统表使用 MyISAM 存储引擎，存储在 mysql 数据库文件目录下各自的表空间文件中。关于数据库存储引擎的详细内容，在MySQL高级课程讲解。

在MySQL5.7版本中查看系统表类型，结果如下：

mysql> #MySQL5.7
mysql> #查看系统表类型
mysql> SELECT DISTINCT(ENGINE) FROM information_schema.tables;
+----------------------+
| ENGINE             		|
+----------------------+
| MEMORY             		|
| InnoDB             		|
| MyISAM             		|
| CSV                		|
| PERFORMANCE_SCHEMA 	|
| NULL               		|
+----------------------+
6 rows in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在MySQL8.0版本中查看系统表类型，结果如下：

mysql> #MySQL8.0
mysql> #查看系统表类型
mysql> SELECT DISTINCT(ENGINE) FROM information_schema.tables;
+--------------------+
| ENGINE                |
+--------------------+
| InnoDB                |
| NULL                  |
| PERFORMANCE_SCHEMA |
| CSV                   |
+--------------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

系统表全部换成事务型的InnoDB表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表。

3.2 默认字符集改为utf8mb4

在8.0版本之前，MySQL默认的字符集为Latin1，而8.0版本默认字符集为utf8mb4。

Latin1是ISO-8859-1的别名，有些环境下写作Latin-1。ISO-8859-1编码是单字节编码，不支持中文等多字节字符，但向下兼容ASCII。

MySQL中utf8字符集，它是utf8mb3的别称，使用三个字节编码表示一个字符。自MySQL4.1版本被引入，能够支持绝大多数语言的字符，但依然有些字符不能正确编码，如emoji表情字符等，为此MySQL5.5引入了utf8mb4字符集。在MySQL5.7对utf8mb4进行了大幅优化，并丰富了校验字符集。mb4就是“most byte 4”的意思，专门用来兼容四字节的Unicode，utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。如果原来某些库和表的字符集是utf8，可以直接修改为utf8mb4，不需要做其他转换。但是从uft8mb4转回utf8就会有问题。

使用SHOW语句查看MySQL5.7版本数据库的默认编码。

mysql> #查看MySQL5.7数据库的默认编码
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name            | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

使用SHOW语句查看MySQL8.0版本数据库的默认编码。

mysql> #查看MySQL8.0数据库的默认编码
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+---------+
| Variable_name            | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

字符集校对规则是在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的则无视。校对规则特征：

• 两个不同的字符集不能有相同的校对规则。
• 每个字符集有一个默认校对规则。
• 校对规则存在命名约定，以其相关的字符集名开始，中间包括一个语言名，并且以_ci、_cs或_bin结尾。其中_ci表示大小写不敏感、_cs表示大小写敏感、bin表示直接比较字符的二进制编码，即区分大小写。

使用SHOW语句查看utf8mb4字符集的部分校对规则如下：

mysql> SHOW COLLATION LIKE 'utf8mb4_0900%';
+-----------+---------+-----+---------+----------+---------+--------------+
| Collation  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute|
+-------------------+---------+-----+---------+----------+-------+--------+
|utf8mb4_0900_ai_ci	| utf8mb4 | 255 | Yes    | Yes     |   0 | NO PAD        	|
|utf8mb4_0900_as_ci	| utf8mb4 | 305 |         | Yes     |   0 | NO PAD        	|
|utf8mb4_0900_as_cs	| utf8mb4 | 278 |         | Yes     |   0 | NO PAD        	|
|utf8mb4_0900_bin	| utf8mb4 | 309 |         | Yes     |   1 | NO PAD        	|
+-------------------+---------+-----+---------+----------+---------+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

3.3 支持检查约束（见上面检查约束）

3.4 支持窗口函数（见上面窗口函数）

3.5 用户管理

在MySQL 8.x中，默认的身份认证插件是“caching_sha2_password”，替代了之前的“mysql_native_password”。可以通过系统变量default_authentication_plugin和mysql数据库中的user表来看到这个变化。

在MySQL8之前默认的身份插件是“mysql_native_password”，即MySQL用户的密码使用PASSWORD函数进行加密。在MySQL 8.x中，默认的身份认证插件是“caching_sha2_password”，替代了之前的“mysql_native_password”，PASSWORD函数被弃用了。

在MySQL版本5.6.6版本起，在mysql.user表中添加了“password_expired”字段，它允许设置密码是否失效。如果“password_lifetime”字段值不为NULL，那么从MySQL服务启动时间开始，经过“password_lifetime”字段值的时间间隔之后，密码就过期了，即“password_expired”字段就为“Y”。任何密码超期的账号想要连接服务器端进行数据库操作都必须更改密码。MySQL8.0版本允许数据库管理员手动设置账户密码过期时间。

从MySQL 8.x版本开始允许限制重复使用以前的密码。

在MySQL8之前，如果要给多个用户授予相同的角色，需要为每个用户单独授权。在MySQL8之后，可以为多个用户赋予统一的角色，然后给角色授权即可，角色可以看成是一些权限的集合，这样就无须为每个用户单独授权。如果角色的权限修改，将会使得该角色下的所有用户的权限都跟着修改，这就非常方便。

mysql的密码字段有变化：

• mysql5.7之前mysql系统库的user表，密码字段名是password

• mysql5.7版本mysql系统库的user表，密码字段名是authentication_string

• mysql8.0版本mysql系统库的user表，密码字段名是authentication_string，另外用户管理还有角色概念，mysql系统库中有default_roles表。

3.6 其他新特性（略）

通用表达式、计算列、DDL操作支持原子性、数据字典合并等等。

通用表达式简称为CTE（Common Table Expressions）。CTE是命名的临时结果集，作用范围是当前语句。CTE可以理解为一个可以复用的子查询，但是和子查询又有区别，一个CTE可以引用其他CTE，CTE还可以是自引用(递归CTE)，也可以在同一查询中多次引用，但子查询不可以。

WITH [RECURSIVE]
 cte_name [(col_name [, col_name] ...)] AS (subquery)
 [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
1
2
3

通用表达式以“WITH”开头，如果“WITH”后面加“RECURSIVE”就表示接下来在通用表达式中需要递归引用自己，否则就不递归引用。每一个通用表达式都需要有一个名字，它相当于是子查询结果集的名字。

#（1）在“t_employee”表中查询每个人薪资和公司平均薪资的的差值。
WITH temp AS (SELECT ROUND(AVG(salary),2) AS pingjun FROM t_employee)
SELECT ename AS "员工姓名",
    salary AS "薪资",
    pingjun "公司平均薪资",
    ROUND(salary - pingjun,2) "差值"
FROM t_employee,temp
HAVING ABS(差值)>5000;

#（2）查询薪资低于9000的员工编号，员工姓名，员工薪资，领导编号，领导姓名，领导薪资
WITH 
emp AS (SELECT eid,ename,salary,`mid` FROM t_employee WHERE salary <9000),
mgr(meid,mename,msalary) AS (SELECT eid,ename,salary FROM t_employee)
  
SELECT eid AS "员工薪资",
	ename AS "员工姓名",
	salary AS "员工薪资",
	meid AS "领导编号",
	mename AS "领导姓名",
	msalary AS "领导薪资"
FROM emp INNER JOIN mgr ON emp.mid = mgr.meid;

#（3）查询eid为21的员工，和他所有领导，直到最高领导。
CREATE TABLE emp AS (SELECT eid,ename,salary,tel,`mid` FROM t_employee WHERE salary < 10000);
UPDATE emp SET MID=19 WHERE eid=21; 
UPDATE emp SET MID=17 WHERE eid=19; 
UPDATE emp SET MID=16 WHERE eid=17; 
UPDATE emp SET MID=15 WHERE eid=16;
UPDATE emp SET MID=4 WHERE eid=15; 
UPDATE emp SET MID=NULL WHERE eid=4;
SELECT * FROM emp;


WITH RECURSIVE cte
AS ( 
	SELECT eid,ename,`mid`
 	FROM emp
 	WHERE eid = 21

UNION ALL

 	SELECT emp.eid,emp.ename,emp.mid
 	FROM emp INNER JOIN cte
 	ON emp.eid = cte.mid
 	WHERE emp.eid IS NOT NULL
)
SELECT * FROM cte;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47