【每日一学】数据库大揭秘:MySQL 事务学习手册，精通数据操作!

学习总目标

本次学习目标

第16章 用户管理

16.1 用户管理的目标

1、登录验证：主机IP地址+用户名+密码三重验证

IP地址可以是一个明确的IP（例如：192.168.1.25），可以是某个IP段（例如：192.168.1.%)，可以是任意IP地址（%)。

mysql -h mysql服务器的IP地址 -P端口号 -u用户名 -p
Enter password: ******

用户的IP是客户端主机的IP和mysql服务器的IP地址不一样。

演示用户的创建

例如：mysql服务器的IP地址是192.168.31.152，使用下面的命令登录是错误的。

mysql -h192.168.31.23 -P端口号 -u用户名 -p
Enter password: ******

因为mysql服务器不在192.168.31.23上。

刚才创建了一个用户：

用户名：yan，主机：192.168.31.%，表示可以从192.168.31段的其他客户端都可以连接到192.168.31.152机器上的mysql服务。

用户名：lin，主机：%，表示可以从任意一台主机的客户端都可以连接到192.168.31.152机器上的mysql服务。

用户名：chai，主机：192.168.31.23，表示只能从192.168.31.23主机的客户端，连接到192.168.31.152机器上的mysql服务。

2、权限管理

• 全局权限
• 数据库权限
• 数据表权限
• 字段权限
• 存储过程或函数子程序的权限

对用户的操作进行逐级权限验证，如果上一级有这个权限，下一级就不用验证了。

16.2 用户管理演示

当具有权限管理的用户通过SQLyog图形界面工具连接MySQL服务之前，可以按照如下步骤进行用户和权限管理。

第1步，选择工具栏中的用户管理器工具按钮，打开用户管理界面。

第2步，如果要创建新用户，选择“添加新用户”按钮，弹出新用户信息填写窗口。用户名和主机文本框必须填写，其他项可以不填写，按照默认值处理。如果密码和再一次输入密码框为空，表示密码为空。如果要设置密码必须保证密码框和再一次输入密码框输入相同字符，并在Plugin选择合适的插件“caching_sha2_password”或“mysql_native_password”，默认是“caching_sha2_password”插件。如果需要还可以在下面填写用户资源限制参数，默认值是0表示不限制。

第3步，如果要修改用户信息，可以直接在“用户”下拉列表中选择用户，然后在右边直接修改用户信息。

第4步，如果是对已有的用户进行授权操作，或撤销已有用户的授权，可以直接在“用户”下拉列表中选择用户，然后在左下方选择权限等级，右边对应权限打对勾表示授予该项权限，不打对勾表示不授予该项权限。

第17章 MySQL8的部分新特性

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)

在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)

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

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)

使用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)

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

• 两个不同的字符集不能有相同的校对规则。
• 每个字符集有一个默认校对规则。
• 校对规则存在命名约定，以其相关的字符集名开始，中间包括一个语言名，并且以_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)

3、支持检查约束（见上面检查约束）

4、支持窗口函数（见上面窗口函数）

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表。

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)] ...

通用表达式以“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;