MySQL学习笔记高级使用篇

Posted on 2022-11-08 Edited on 2023-09-08 In MySQL Views:

这是学习MySQL的笔记高级使用篇，主要是视图、存储、函数、变量、流程和触发器等。

数据库对象

常见的数据库对象

对象	描述
表 `TABLE`	数据库里最基础的存储单元，表里的数据由行和列组织排列的。
数据字典	就是系统表，存放数据库相关信息的表，系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看。MySQL 8新增。
约束 `CONSTRAINT`	执行数据校验规则，用于保证数据完整性的规则
视图 `VIEW`	一个或多个数据表里的数据的逻辑显示，视图并不存储数据。视图是一个虚拟的表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集合的形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
索引 `INDEX`	索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的指定信息。例如，想按指定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
存储过程 `PROCEDIRE`	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数 `FUNCTION`	用于完成一次特定的计算，具有一个返回值
触发器 `TRIGGER`	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

视图

视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）
从而加强了安全性，使用户只能看到视图所显示的数据。
视图还可以被嵌套，一个视图中可以嵌套另一个视图。

基本定义

视图是一种虚拟表，本身不具有数据，占用很少的内存空间，是SQL中的一个重要概念。
视图建立在已有表的基础上，视图依赖建立的这些表称为基表。
视图的创建和删除只影响视图本身，不影响对应的基表。当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然
向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句
视图是向用户提供基表数据的另一种表现形式。在大型项目中以及数据表比较复杂的情况下，视图的价值就凸显出来了。

创建视图

在create view语句中嵌套子查询

SELECT * FROM employee;
SELECT * FROM department;

-- 创建视图
-- 针对单表
CREATE VIEW vu_emp1 AS SELECT emp_id,fname FROM employee;
-- 字段的别名会作为视图中的别名出现
CREATE VIEW vu_emp2 AS SELECT emp_id `id`,fname `name` FROM employee;
-- 别名的第二种方式，定义的名称与SELECT的名称需要一一对应
CREATE VIEW vu_emp3(id,`name`) AS SELECT emp_id ,fname FROM employee;

-- 使用视图
SELECT * FROM vu_emp6;

-- 查询在表中不存在的字段
CREATE VIEW vu_emp6 AS 
SELECT max(start_date),dept_id FROM employee GROUP BY dept_id;

-- 针对多表
CREATE VIEW vu_emp_dept1 AS 
SELECT employee.emp_id,employee.fname,department.name FROM employee JOIN department ON employee.dept_id = department.dept_id;
SELECT * FROM vu_emp_dept1 ORDER BY emp_id;

-- 利用视图对数据进行格式化
CREATE VIEW vu_emp_dept2 AS 
SELECT employee.emp_id,CONCAT(employee.fname,' ',employee.lname,' =》 ',department.`name`) emp_info FROM employee JOIN department ON employee.dept_id = department.dept_id;
SELECT * FROM vu_emp_dept2;
DESC vu_emp_dept2;

-- 基于视图创建视图
CREATE VIEW vu_emp7 AS SELECT emp_id,fname,dept_id FROM employee;
CREATE VIEW vu_emp8 AS SELECT fname,dept_id FROM vu_emp7;
SELECT * FROM vu_emp8;

查看视图

-- 查看数据库的表对象、视图对象
SHOW TABLES;
-- 查看视图结构
DESC vu_emp1;
-- 查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp1';
-- 查看视图详细定义信息
SHOW CREATE VIEW vu_emp1;

更新视图中的数据

更新视图中的数据要更新成功，需要保证更新的数据要有表中的数据一一对应。

-- 更新视图中的字段，会导致表中的数据更改
SELECT * FROM vu_emp7;
UPDATE vu_emp7 SET dept_id = 1 WHERE emp_id = 1;
SELECT * FROM vu_emp7;
SELECT * FROM employee;
-- 更新表中的数据，会导致视图中的数据更改
UPDATE employee SET dept_id = 1 WHERE emp_id = 3;
SELECT * FROM vu_emp7;

-- 删除视图中的数据，会导致表中的数据删除
DELETE FROM vu_emp7 WHERE emp_id = 18;
-- 删除表中的数据也会导致视图中的数据删除
DELETE FROM employee WHERE emp_id = 19;

-- 更新视图中的数据
-- 一般情况下，视图中的数据可以更新，视图中数据更新会导致表中数据也更新
UPDATE vu_emp7 SET dept_id = 3 WHERE emp_id = 1;
-- 有时候不能更新视图中的数据，例如表中没有对应字段，例如最大值
CREATE VIEW vu_emp9 AS SELECT max(start_date) max_date,dept_id FROM employee GROUP BY dept_id;
SELECT * FROM vu_emp9;
-- The target table vu_emp9 of the UPDATE is not updatable
UPDATE vu_emp9 SET max_date = 2000-01-01 WHERE dept_id = 1;
-- 删除同样不行 The target table vu_emp9 of the DELETE is not updatable
DELETE FROM vu_emp9 WHERE dept_id = 1;

虽然可以更新视图数据，但是总得来说，视图作为虚拟表，是方便查询，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

删除、修改视图

-- 修改、删除视图
DESC vu_emp1;
-- 方式1，重新创建
CREATE OR REPLACE VIEW vu_emp1 AS SELECT emp_id,lname FROM employee WHERE start_date > '2002-01-01';
-- 方式2，更新
ALTER VIEW vu_emp1 AS SELECT emp_id,lname FROM employee WHERE start_date > '2002-01-01';

SHOW TABLES;
-- 删除视图
DROP VIEW vu_emp9;
DROP VIEW IF EXISTS vu_emp8;

-- 当表删除，视图查询会报错
SELECT * FROM mitaka_test1;
CREATE VIEW vu_mita1 AS SELECT id,last_name FROM mitaka_test1;
SELECT * FROM vu_mita1;
DROP TABLE mitaka_test1;
-- View 'mitaka.vu_mita1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
SELECT * FROM vu_mita1;

当基于视图a、视图b创建新的视图c，如果视图a后者视图b删除，会导致视图c的查询失败，这样的视图c需要手动删除或者修改，否则会影响使用。

总结

优势：

操作简单：不需要理解表和表之间的关系，操作视图即可
减少数据冗余：视图本身不占用存储资源
数据安全：可通过管理权限实现对数据的保护，实现隔离性
适应灵活多变的需求：不用修改数据库字段，通过更新视图即可完成
能够分解复杂的查询逻辑：创建多个视图获取数据，再将多个视图结合起来，完成复杂的查询逻辑

不足：

如果实际数据表的结构变更，就需要及时对相关的视图进行相应的维护
如果视图过多，会导致数据库维护成本的问题

存储过程与函数

存储过程

Stored Procedure，一组经过预先编译的SQL语句的封装。

执行过程：存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

好处：

简化操作，体现于sql语句的重用性，一次编译多次使用
减少操作过程中的失误，提高效率
减少网络传输量
减少SQL语句暴露在网上的风险，也提高了数据查询的安全性

缺点：

阿里开发规范：【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有一致性。

可移植性差，不同的数据库支持粒度不一样
调试过程困难，当函数非常复杂，开发维护都不容易
存储过程的版本管理很困难，例如当索引发生变化，可能导致存储过程失效
不适合高并发，高并发通常会采用分库分表，对扩展性要求很高，这种情况下存储过程很难维护，增加数据库压力，显然不适用。

一旦存储过程被创建出来，使用就像使用函数一样简单，可以直接通过调用存储过程名即可。相较于函数，存储过程没有返回值。

存储过程的参数类型可以是IN、OUT和INOUT分类：(这里的返回是指函数的return，不代表执行存储过程的返回。)

没有参数（无参数无返回）
仅仅带IN类型（有参数无返回）
仅仅带OUT类型（无参数有返回）
既带IN又带OUT类型（有参数有返回）
带INOUT（有参数有返回）

IN、OUT、INOUT都可以在一个存储过程中带多个。

创建和使用存储过程

无参数无返回

-- 创建存储过程
-- 无参数无返回值
-- 将结束符改为 $
delimiter $
-- 创建存储过程  存储过程名称 
CREATE PROCEDURE select_all_data() 
BEGIN 
    SELECT * FROM employee;
END$
-- 将结束符改回;
delimiter;

-- 调用存储过程
CALL select_all_data();

无参数有返回

-- 无参数有返回值
delimiter $
-- 输出参数 max_start_date 类型是date
CREATE PROCEDURE select_max_start_date(OUT max_start_date DATE)
BEGIN
-- 将查询到的结果定义到max_start_date
    SELECT MAX(start_date) INTO max_start_date FROM employee;
END $
delimiter;
-- 调用
-- 传入一个变量，用于接收函数的返回
CALL select_max_start_date(@start_date);
-- 查看变量值
SELECT @start_date;

有参数没有返回值

-- 有参数没有返回
delimiter $
-- 输入参数 emp_id_in 类型是SMALLINT
CREATE PROCEDURE show_someone_start_date(IN emp_id_in SMALLINT)
BEGIN
-- 使用的条件是emp_id_in，入参名称需要注意，不能与表字段一致，不然会检索到所有数据，类似于1=1
    SELECT start_date FROM employee WHERE emp_id = emp_id_in;
END $
delimiter;
-- 调用
CALL show_someone_start_date(1);
-- 或者使用变量
-- @代表用户定义变量，@@代表系统变量
set @emp_id = 1;
CALL show_someone_start_date(@emp_id);

有输入有输出

-- 有输入和输出
delimiter $
-- 输入参数 emp_id_in 类型是SMALLINT，输出参数是emp_end_date_out类型是DATE
CREATE PROCEDURE show_someone_end_date(IN emp_id_in SMALLINT,OUT emp_end_date_out DATE)
BEGIN
    SELECT end_date INTO emp_end_date_out FROM employee WHERE emp_id = emp_id_in;
END $
delimiter;
-- 调用
CALL show_someone_end_date(1,@emp_end_date_out);
SELECT @emp_end_date_out;

有输入有输出的INOUT

-- 带INOUT
-- 获取某个员工的管理员名称
delimiter $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
    SELECT fname INTO empname FROM employee WHERE emp_id = (SELECT superior_emp_id FROM employee WHERE fname = empname);
END $
delimiter ;
-- 执行
SET @empname = 'Jane';
CALL show_mgr_name(@empname);
SELECT @empname;

如果使用navicat，会自动设置delimiter为其他符号，可以不需要进行delimiter操作。

存储函数

存储函数指的是用户自定义的函数。

函数一定有返回值。函数只有IN类型，也就是只需要填入入参。

DESC employee;
SELECT * FROM employee;

-- 创建存储函数
-- 使用navicat，可以不使用 delimiter
CREATE FUNCTION title_by_name()
RETURNS VARCHAR(20)
-- 必须要加上特性
    DETERMINISTIC	-- 确定性
    CONTAINS SQL -- 包含sql语句
    READS SQL DATA -- 读取sql数据
BEGIN
    RETURN (SELECT title FROM employee WHERE fname = 'Robert') ;
END;

-- 加上特性的第二种方式，全局加
SET GLOBAL log_bin_trust_function_creators = 1;
-- 再创建存储函数
CREATE FUNCTION title_by_name1()
RETURNS VARCHAR(20)
BEGIN
    RETURN (SELECT title FROM employee WHERE fname = 'Robert') ;
END;

-- 调用
SELECT title_by_name1();

SET GLOBAL log_bin_trust_function_creators = 1;
-- 有参数
CREATE FUNCTION title_by_id(in_emp_id INT)
RETURNS VARCHAR(20)
BEGIN
    RETURN (SELECT title FROM employee WHERE emp_id = in_emp_id) ;
END;
-- 调用
SELECT title_by_id(1);

-- 传入多个参数
CREATE FUNCTION count_by_id(in_dept_id INT,in_assigned_branch_id INT)
RETURNS INT 
BEGIN 
    RETURN (SELECT COUNT(*) FROM employee WHERE dept_id = in_dept_id AND assigned_branch_id = in_assigned_branch_id);
END;
SELECT count_by_id(1,3);

-- 删除存储函数
DROP FUNCTION title_by_name1;

对比

	存储过程	存储函数
关键字	PROCEDURE	FUNCTION
调用语法	`CALL 存储过程()`	`SELECT 存储函数()`
返回值	理解为没有返回值（可以放在OUT里面）	只能是一个，RETURNS后
应用场景	一般用于更新	一般用于查询结果为一个值并返回时

存储函数可以放在查询语句中使用，存储过程不行。

存储过程的功能更加强大，包括能够执行对表的操作（比如创建表、删除表等）和事务操作，这些功能是存储函数不具备的。

存储过程和函数的查看、修改、删除

查看

-- 获取存储过程和函数
-- 方式1，用 show create
SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION title_by_name;
-- 方式2，查看状态信息
SHOW PROCEDURE STATUS LIKE 'show_mgr_name';
SHOW FUNCTION STATUS LIKE 'title_by_name';
-- 方式3，在information_schema.ROUTINES表中查看信息
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='show_mgr_name' AND ROUTINE_TYPE = 'PROCEDURE';
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='title_by_name' AND ROUTINE_TYPE = 'FUNCTION';

修改

-- 修改
-- 修改不影响存储过程和函数功能，只是修改相关特性
ALTER PROCEDURE show_mgr_name 
-- 安全调用，也就是需要有权限
SQL SECURITY INVOKER
COMMENT '查询管理者名字';

删除

1
2
3

-- 删除存储过程和函数
DROP PROCEDURE show_mgr_name;
DROP FUNCTION title_by_name;

变量、流程控制与游标

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。

在MySQL数据库中，变量分为系统变量以及用户自定义变量。

系统变量

变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些变量要么是编译MySQL时参数的默认值，要么是配置文件中的参数值。

MySQL 8.0系统变量：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

MySQL 5.5系统变量：http://download.nust.na/pub6/mysql/doc/refman/5.5/en/using-system-variables.html

系统变量分为全局系统变量（或者称为全局变量，通过关键字global）以及会话系统变量（或者称为local变量，通过关键字session）。如果不写。默认会话级别。静态变量（不能使用set动态修改）属于特殊的全局系统变量。

客户端连接之后，都会产生对应的会话。会话期间，MySQL服务实例会在内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。

全局系统变量针对所有会话都有效，重启后失效
会话系统变量针对对应连接的会话，会话断开重连之后，会话系统变量失效
会话1对某个全局系统变量的修改会导致会话2中同一个全局系统变量

MySQL有些系统变量的作用域是全局的（例如max_connections最大连接数），有些变量的作用域既可以是全局又可以是会话（例如字符集character_set_client），有些变量的作用域是会话（例如pseudo_thread_id标记当前会话的MySQL连接ID）。

查看系统变量

-- 查看所有系统变量
SHOW GLOBAL VARIABLES;
-- 查看所有会话变量
SHOW SESSION VARIABLES;
-- 查询会话变量的另外一种方式
SHOW VARIABLES;
-- 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%version%';
-- 查看指定系统变量
SELECT @@global.admin_port;
-- 查看指定回话变量
SELECT @@session.collation_connection;
SELECT @@global.collation_connection;
-- 先查看会话变量，再查看系统变量
SELECT @@collation_connection;

修改系统变量

方式1：永久修改，修改配置文件，然后重启

方式2：临时修改，一旦重启MySQL就失效

-- 修改系统变量
SET @@global.max_connections = 161;
SET GLOBAL max_connections  = 171;
-- 修改回话系统变量
-- 一旦断开回话重新建立回话，修改失效
SHOW SESSION VARIABLES LIKE '%char%';
SELECT @@session.character_set_client;
SET SESSION character_set_client = 'gbk';

用户变量

用户变量是用户自定义的，以一个@开头，根据作用范围分为会话用户变量和局部变量。

会话用户变量：作用域为当前会话
局部变量：作用域为存储过程和函数中

定义

-- 设置会话用户变量
SET @var1 = 1;
SET @var2 := 2;
-- 使用变量
SELECT @var1;
SELECT @var2;
-- 只要会话还在，则变量还可以访问
SELECT @var1;

-- 设置会话用户变量方式2
SELECT @count := COUNT(*) FROM employee;
SELECT @count;

-- 设置会话用户变量方式3
SELECT MAX(start_date) INTO @max_start_date FROM employee;
SELECT @max_start_date;

-- 局部变量
-- 1. 使用 DECLARE 声明
-- 2. 必须放在BEGIN END中
-- 3. DECLARE 的方式声明的局部变量必须声明在 BEGIN 中的首行位置
CREATE PROCEDURE get_max_start_date()
BEGIN 
-- 声明局部变量
    DECLARE a INT DEFAULT 0;
    DECLARE b CHAR(20);
--     赋值
    SET a = 1;
    SET b = 'name';
    SELECT lname INTO b FROM employee WHERE emp_id = a;
--     使用
    SELECT a,b;
END;
-- 调用存储过程
CALL get_max_start_date();

定义条件与处理程序

主要解决当程序在执行过程中出错时如何处理错误。定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

1 2	UPDATE employee SET fname = NULL WHERE emp_id = 19; ERROR 1048 (23000): Column 'fname' cannot be null

例如上面这个SQL语句，执行之后出现报错。1048 (23000)，其中1048是MySQL_error_code，23000是sqlstate_value

MySQL_error_code：数值类型错误代码
sqlstate_value：长度为5的字符串类型错误代码

CREATE PROCEDURE test_pro1()
BEGIN 
-- 设置用户变量
    SET @x = 1;
--     fname有非空约束
    UPDATE employee SET fname = NULL WHERE emp_id = 19;
--     验证报错后执行的变量是否会声明成功
    SET @y = 2;
    UPDATE employee SET fname = 'test' WHERE emp_id = 19;
    SET @z = 3;
END;
CALL test_pro1();
-- 出现报错
-- 1048 - Column 'fname' cannot be null
-- 查看值，只有x声明成功
SELECT @x,@y,@z;

定义条件与处理程序解决报错：

定义条件在外部定义即可

-- 定义条件
-- 方式1，使用MySQL_error_code
DECLARE Field_Not_Be_NULL1 CONDITION FOR 1048;
-- 方式2，使用sqlstate_value，通过SQLSTATE防止将字符串转化成整形
DECLARE Field_Not_Be_Null2 CONDITION FOR SQLSTATE '23000';

定义处理程序

1	DECLARE 声明处理方式 HANDLER FOR 错误类型处理语句

处理方式：

CONTINUE 错误不处理，继续运行；

EXIT 碰到错误马上退出；

UNDO 撤回之前的操作，MySQL暂时不支持 UNDO

错误类型：

SQLSTATE ‘字符串错误码’

SQLSTATE ‘字符串错误码’：匹配字符串类型错误码

MySQL_error_code: 匹配错误数值类型错误码

错误名称：匹配前面定义的错误条件

SQLWARNING: 匹配所有以01开头的 SQLSTATE 的错误码

NOT FOUND: 匹配所有以02开头的 SQLSTATE 的错误码

SQLEXCEPTION: 匹配所有没有被 SQLWARNING 或 NOT FOUND捕获的 SQLSTATE 错误代码

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像 SET 变量 = 值这样的简单语句，也可以是使用 BEGIN ... END 编写的复合语句。

-- 定义条件，使用MySQL_error_code
DECLARE Field_Not_Be_NULL1 CONDITION FOR 1048;
-- 定义存储过程
CREATE PROCEDURE test_pro2()
BEGIN 
--  定义处理程序
    DECLARE Field_Not_Be_NULL1 CONDITION FOR 1048;
    DECLARE CONTINUE HANDLER FOR Field_Not_Be_NULL1 SET @info='Column cannot be null';
--     处理方式2 
    # DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @info2='this is 23000';
-- 设置用户变量
    SET @x = 1;
--     fname有非空约束
    UPDATE employee SET fname = NULL WHERE emp_id = 19;
--     验证报错后执行的变量是否会声明成功
    SET @y = 2;
    UPDATE employee SET fname = 'test' WHERE emp_id = 19;
    SET @z = 3;
END;
-- 调用
CALL test_pro2();
-- 没有报错
-- 查看值，x,y,z,info都声明成功
SELECT @x,@y,@z,@info;

流程控制

控制存储过程中SQL语句的执行顺序。

顺序结构：从上往下
分支结构：按照条件选择某一个路径
循环结构：满足条件时，重复执行一组数据

流程控制语句：

条件判断：IF 和 CASE
循环语句：LOOP、WHILE和REPEAT
跳转语句：ITERATE和LEAVE

IF

-- 条件判断
CREATE PROCEDURE test_if()
BEGIN
-- 声明局部变量
    DECLARE stu_name  VARCHAR(15);
--         使用 IF 结构
IF stu_name IS NULL
THEN    SELECT 'stu_name is null';
END IF;
END;
-- 调用
CALL test_if();

-- 二选一
CREATE PROCEDURE test_if2()
BEGIN
    DECLARE email VARCHAR(25) DEFAULT '[email protected]';
    IF email IS NULL 	
    THEN SELECT 'email is null';
    ELSE SELECT 'email is not null';
    END IF;
END;
-- 调用
CALL test_if2();

-- 多选一
CREATE PROCEDURE test_if3()
BEGIN 
    DECLARE age INT DEFAULT 20;
    
    IF age > 40 
        THEN SELECT '中年';
    ELSEIF age > 20
        THEN SELECT '青年';
    ELSE SELECT '少年';
    END IF;
END;
CALL test_if3();

CASE

情况一：类似于switch

-- CASE
CREATE PROCEDURE test_case1()
BEGIN
    DECLARE var INT DEFAULT 2;
    
    CASE var 
    WHEN 1 THEN SELECT 'var = 1';
    WHEN 2 THEN SELECT 'var = 2';
    WHEN 3 THEN SELECT 'var = 3';
    ELSE SELECT 'other value';
    END CASE;
END;
-- 调用
CALL test_case1();

情况二：类似于多重if

CREATE PROCEDURE test_case2()
BEGIN
    DECLARE var INT DEFAULT 10;
    CASE
    WHEN var >= 100 THEN SELECT 'var 3位数';
    WHEN var >= 10 THEN SELECT 'var 2位数';
    ELSE SELECT '个位数';
    END CASE;
END;
CALL test_case2();

LOOP

循环语句主要由四个部分组成：

初始化条件
循环条件
循环体
迭代条件

CREATE PROCEDURE test_loop()
BEGIN
-- 初始化条件
    DECLARE num INT DEFAULT 1;
    loop_label: LOOP
    -- 循环体和迭代条件
    SET num = num +1;
    -- 循环条件
    IF num > 10 THEN
        LEAVE loop_label; 
    END IF; 
END LOOP loop_label;
SELECT num;
END;
-- 调用
CALL test_loop();

WHILE

CREATE PROCEDURE test_while()
BEGIN
-- 初始化条件
  DECLARE num INT DEFAULT 0;
  -- 循环条件
    WHILE num < 10 DO
    -- 循环体和迭代条件
        SET num = num + 1;
    END WHILE;
    SELECT num;
END;
-- 执行
CALL test_while();

REPEAT

CREATE PROCEDURE test_repeat()
BEGIN
-- 初始化条件
  DECLARE num INT DEFAULT 0;
    REPEAT
    -- 循环体和迭代条件
    SET num = num + 1;
    -- 循环条件
UNTIL num > 10 END REPEAT;
    SELECT num;
END;
-- 执行
CALL test_repeat();

LEAVE和ITERATE

LEAVE：使用在循环体内或者使用在BEGIN和END之间，表示跳出循环体或者跳出程序体的操作，类似于break。

CREATE PROCEDURE test_leave(IN num INT)
begin_label:BEGIN
    IF num <= 0 
-- 跳出begin    
            THEN LEAVE begin_label;
        ELSEIF num = 1
            THEN SELECT max(start_date) FROM employee;
        ELSEIF num = 2
            THEN SELECT min(start_date) FROM employee;
        ELSE 
            SELECT 'null';
    END IF;
END;
-- 调用
CALL test_leave(3);

ITERATE：只能使用在循环体内，表示跳过当前循环，进入下次循环。

CREATE PROCEDURE test_iterate(IN num INT)
BEGIN
    DECLARE size INT DEFAULT 0;
    loop_label: LOOP
    SET num = num - 1;
    IF num <= 0 THEN
-- 跳出loop    
            LEAVE loop_label; 
    ELSEIF num % 2 = 0 THEN SET size = size +1;
    -- 跳过此次loop，进入下次loop
    ELSE ITERATE loop_label;
    END IF; 
END LOOP loop_label;
    SELECT size;
END;
-- 调用
CALL test_iterate(10);

游标

提供一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向记录中的数据进行操作的数据结构。

游标让SQL这种面向集合的语言有了面向过程开发的能力。

游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。

游标的使用分为四个步骤

声明游标
打开游标
使用游标：使用该游标读取当前行，ZEROFILL
关闭游标：打开游标就需要关闭游标，游标会占用系统资源，不及时关闭的话，游标会一直保持到存储过程结束，影响系统效率。关闭游标会释放系统资源。关闭游标之后，就无法再次打开

CREATE PROCEDURE get_count_by_limit_total_balance(IN limit_total_balance DOUBLE,OUT total_count INT)
BEGIN 
--     声明局部变量
    DECLARE sum_balance DOUBLE DEFAULT 0.0; -- 记录累加的balance
    DECLARE account_balance DOUBLE; -- 记录每一个account的balance
    DECLARE account_count INT DEFAULT 0; -- 记录叠加的account

-- 声明游标
    DECLARE account_balance_cursor CURSOR FOR SELECT avail_balance FROM account ORDER BY avail_balance DESC;
    
--     打开游标
    OPEN account_balance_cursor;
    
    REPEAT
    -- 	使用游标
    FETCH account_balance_cursor INTO account_balance;
    SET sum_balance = sum_balance + account_balance;
    SET account_count = account_count + 1;
UNTIL sum_balance >= limit_total_balance END REPEAT;
--     关闭游标
    CLOSE account_balance_cursor;
    SET total_count = account_count;
END;
-- 调用
SET @limit_total_balance = 100000.0;
CALL get_count_by_limit_total_balance(@limit_total_balance,@total_count);
SELECT @total_count;

优点：为逐条读取结果集中的数据提供完美的解决方案，相比全部获取出来再应用程序中计算，使用游标效率高。

缺点：使用游标过程中，会对数据行加锁，高并发下不仅会影响业务效率，还会降低系统资源，造成内存不足，这是因为游标是在内存中处理的。

MySQL 8.0特性

全局系统变量持久化，通过persist参数
1
2
SET persist innodb_buffer_pool_size = 134217727;
SET persist max_connections = 200;
服务器重启之后，会加载这部分的配置。

触发器

触发器是由事件来触发某个操作，这些事件包括insert、update、delete事件。所谓的事件就是指用户的操作或者触发某项行为。如果定义了触发程序，当数据执行这些语句的时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

创建触发器

CREATE TABLE test_trigger(
id INT PRIMARY KEY auto_increment,
t_note VARCHAR(30)
);

CREATE TABLE test_log(
id INT PRIMARY KEY auto_increment,
t_log VARCHAR(30)
);

-- 创建触发器
-- 创建触发器名称
CREATE TRIGGER before_insert_test_tri 
-- 在 INSERT 到 test_trigger 之前 ，对于每一行插入的数据
BEFORE INSERT ON test_trigger FOR EACH ROW 
-- 在insert之后
-- AFTER INSERT INTO test_trigger FOR EACH ROW
-- 触发之后的动作
BEGIN
-- 插入确定数据
--     INSERT INTO test_log(t_log) VALUES ('before insert');
--     插入跟插入数据有关联的数据
    INSERT INTO test_log(t_log) VALUES (new.t_note);
END

-- 测试
INSERT INTO test_trigger(t_note) VALUES ('test');
-- 获取触发器结果
SELECT * FROM test_log;

查看、删除触发器

查看触发器

-- 查看触发器
-- 查看所有的触发器定义
SHOW TRIGGERS;
-- 查看触发器的创建定义
SHOW CREATE TRIGGER before_insert_test_tri;
-- 从 information_schema.TRIGGERS 表中查看
SELECT * FROM information_schema.TRIGGERS WHERE trigger_name = 'before_insert_test_tri';

删除触发器

1 2	-- 删除触发器 DROP TRIGGER IF EXISTS before_insert_test_tri;

优缺点和注意点

优点

触发器可以确保数据完整性
触发器可以帮助记录操作日志
在操作数据前，对数据合法性进行检验

缺点

1、可读性差，触发器存储在数据库中，由事件驱动，不受应用层的控制

2、相关数据更新，可能导致触发器出错

注意点

如果在子表中定义了外键约束，并且外键指定了触发器，此时修改父表被引用的键值或者删除父表被引用的记录时，会引擎子表的修改和删除操作，此时基于子表的触发器，并不会被激活。

窗口函数

MySQL 8新特性。在需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数效果更好。

窗口函数的作用类似于在查询过程中对数据进行分组，而且是将结果置于每一条数据记录中。相比较GROUP BY，不会聚合成一行。

窗口函数可以分为静态窗口函数和动态窗口函数

静态窗口函数：窗口大小固定，不会因为记录的不同而不同
动态窗口函数：窗口大小会随着记录的不同而变化

窗口函数总体上可以分为序号函数、分布函数、前后函数、首位函数和其他函数。

序号函数

增加一个序号，顺序排序

-- ROW_NUMBER()
-- 例如列出来同department中start_date 降序排序
SELECT ROW_NUMBER() over 
-- 通过 dept_id 分区，通过 start_date 排序，将分区和降序的结果序号作为row_num 
    (PARTITION BY dept_id ORDER BY start_date) AS row_num,
emp_id,start_date,dept_id FROM employee;
+---------+--------+------------+---------+
| row_num | emp_id | start_date | dept_id |
+---------+--------+------------+---------+
|       1 |     19 | 2002-12-12 |    NULL |
|       1 |      3 | 2000-02-09 |       1 |
|       2 |     13 | 2000-05-11 |       1 |
|       3 |     11 | 2000-10-23 |       1 |
|       4 |     16 | 2001-03-15 |       1 |
|       5 |      4 | 2002-04-24 |       1 |
|       6 |      9 | 2002-05-03 |       1 |
|       7 |     17 | 2002-06-29 |       1 |
|       8 |     10 | 2002-07-27 |       1 |
|       9 |     14 | 2002-08-09 |       1 |
|      10 |      8 | 2002-12-02 |       1 |
|      11 |     12 | 2003-01-08 |       1 |
|      12 |     15 | 2003-04-01 |       1 |
|      13 |      6 | 2004-03-17 |       1 |
|      14 |      7 | 2004-09-15 |       1 |
|       1 |      5 | 2003-11-14 |       2 |
|       1 |      2 | 2002-09-12 |       3 |
|       2 |      1 | 2022-10-11 |       3 |
+---------+--------+------------+---------+
18 rows in set (0.01 sec)

-- RANK()，并列排序，会跳过重复的序号
SELECT RANK() over 
-- 通过 dept_id 分区，通过 start_date 排序，将分区和降序的结果序号作为row_num 
    (PARTITION BY dept_id ORDER BY start_date) AS row_num,
emp_id,start_date,dept_id FROM employee;

-- DENSE_RANK(),并列排序，不会跳过重复的序号
SELECT DENSE_RANK() over 
-- 通过 dept_id 分区，通过 start_date 排序，将分区和降序的结果序号作为row_num 
    (PARTITION BY dept_id ORDER BY start_date) AS row_num,
emp_id,start_date,dept_id FROM employee;

-- 窗口函数的另外一种写法，将窗口函数作为一个 window 声明
SELECT DENSE_RANK() over w AS row_num,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date);

分布函数

-- PERCENT_RANK() 等级值百分比，(rank - 1) / (rows - 1)
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);

--  CUME_DIST() 查询小于或等于的比例
SELECT CUME_DIST() OVER w AS c,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);

前后函数

-- 查询前一个员工的start_date
SELECT LAG(start_date,1) OVER w AS pre_start_date,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);
-- 可以作为一个子函数，实现差值
SELECT pre_start_date,DATEDIFF(pre_start_date,start_date) AS diff_start_date,
emp_id,start_date,dept_id FROM (
SELECT LAG(start_date,1) OVER w AS pre_start_date,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC))t

-- 查询后一个员工的start_date
SELECT LEAD(start_date,1) OVER w AS behind_start_date,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);

首位函数

-- 查询分组后最头一个的start_date，可以用来获取差值
SELECT FIRST_VALUE(start_date) OVER w AS first_start_date,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);
-- 查询分组后最尾部一个的start_date，可以用来获取差值

-- LAST_VALUE是获取当前行与之前行的比较，因此获取到的是本行的
SELECT LAST_VALUE(start_date) OVER w AS last_start_date,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);
-- 正确用法
SELECT LAST_VALUE(start_date) OVER w AS last_start_date,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC rows between unbounded preceding and unbounded following);

其他函数

-- 获取start_date排名第三的start_date
SELECT NTH_VALUE(start_date,3) OVER w AS 3th_start_date,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);

-- 将分组后的数据，再次平均分组，
SELECT NTILE(3) OVER w AS nt,
emp_id,start_date,dept_id FROM employee
window w AS (PARTITION BY dept_id ORDER BY start_date DESC);

小结

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这在原表数据的基础上进行统计和排序非常有用。

公用表表达式

MySQL 8新特性。也叫做通用表表达式，Common Table Expressions（CTE）。

CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，CTE可以引用其他CTE，但子查询不能引用其他子查询，所以可以考虑代替子查询。

依据语法结构和执行方式不同，公用表达式分为普通公用表表达式和递归公用表表达式

普通公用表表达式

-- 普通公用表表达式
-- 查看员工所在部门的信息
SELECT * FROM department WHERE dept_id IN (
-- 使用子查询
    SELECT dept_id FROM employee);
    
--     将子查询替换成公用表表达式
-- 声明公用表表达式，需要与调用一起使用
WITH cte_emp 
AS ( 
SELECT DISTINCT dept_id FROM employee
)
-- 使用公用表表达式
SELECT * FROM department d JOIN cte_emp c ON d.dept_id = c.dept_id;

递归公用表表达式

递归公用表表达式除了普通公用表表达式的特点之外，还可以自己调用自己。

 -- 递归公用表表达式
-- 例如查询出有下属后，下属还有的下属
WITH recursive cte_emp AS (
SELECT emp_id,lname,superior_emp_id,1 AS n FROM employee WHERE emp_id = 1 -- 种子查询,先取老板
-- 
UNION ALL 
SELECT a.emp_id,a.lname,a.superior_emp_id,n+1 FROM employee AS a JOIN cte_emp ON 
-- 递归查询，找出递归公用表表达式的人为领导的人，也就是老板的下属，递归查询下属的下属
(a.superior_emp_id = cte_emp.emp_id))
SELECT emp_id,lname FROM cte_emp WHERE n >= 3;

总结

公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。

字符集的相关操作

修改字符集

MySQL 8.0版本之前，默认字符集为latin1。utf8字符集指向的是utf8mb3，其中3代表1个字符占用3个字节。如果遗忘修改字符集，就会出现乱码的问题。从MySQL 8.0开始，数据库的默认编码将改成utf8mb4，代表1个字符占用4个字节。

-- 查看默认使用的字符集
SHOW VARIABLES LIKE '%character%';
-- 或者
SHOW VARIABLES LIKE '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.02 sec)

CREATE DATABASE dbtest1;
SHOW CREATE DATABASE dbtest1;
-- 修改数据库默认字符集
ALTER DATABASE CHARACTER SET 'latin1';
SET @@character_set_database = 'utf8mb4';
-- 修改某个数据库的字符集
ALTER DATABASE dbtest1 CHARACTER SET 'latin1';
-- 创建表时如果没有指定字符集，则使用数据库默认字符集
CREATE TABLE table1(id INT,`name` VARCHAR(20));
SHOW CREATE TABLE table1;
-- 更改表字符集
ALTER TABLE table1 CHARACTER SET 'utf8mb4';

如果表中有数据，并且数据兼容新的字符集，则可以成功修改。

-- 获取所有支持的字符集和比较规则
SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
...
-- 字符集            描述																默认比较规则						最大占用字节数

各级别字符集

服务器级别
数据库级别
表级别
列级别

1 2	\| character_set_database \| utf8mb4 \| -- 数据库级别 \| character_set_server \| utf8mb4 \| -- 服务器级别

从上到下，如果没有指明字符集，创建时则默认使用上级字符集。

比较规则

比较规则是在当前字符集下的排序计量方式，规则名称的后缀表示该比较规则是否区分语言中的重音、大小写。具体如下

后缀	英文释义	描述
`_ai`	`accent insensitive`	不区分重音
`_as`	`accent sensitive`	区分重音
`_ci`	`case insensitive`	不区分大小写
`_cs`	`case sensitive`	区分大小写
`_bin`	`binary`	以二进制方式比较

1 2	-- 查看比较规则 SHOW COLLATION;

-- 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server%';
-- 查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database%';

-- 修改具体数据库的字符集和比较规则
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

-- 查看表的比较规则
SHOW TABLE STATUS FROM dbtest1 WHERE Name = 'table1';
-- 修改表的比较规则
ALTER TABLE table1 DEFAULT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

说明

一般情况，utf8mb4_general_ci最优，如果是德语、法语或者俄语，使用utf8mb4_unicode_ci更好。
修改了数据库的默认字符集和比较规则后，原来已经创建的表的字符集和比较规则不会改变，如果要改，那么需要单独修改。

请求到响应过程字符集的变化

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        | -- 客户端的字符集，传输数据到服务端使用对应字符集对数据进行编码
| character_set_connection | utf8mb4                        | -- 从这个字符集转换为具体的列使用的字符集
| character_set_results    | utf8mb4                        |    -- 将查询结果从具体的列使用的字符转换位这个字符集

总结：

通常把character_set_client，character_set_connection，character_set_results设置成和客户端使用的字符集一致，减少很多无谓的字符集转换。

-- 一次性设置传输的字符集
SET NAMES utf8mb4;
-- 想等于
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

SQL 大小写规范

通常情况下，MySQL在Linux下大小写敏感，在Windows下大小写不敏感。

查看大小写是否敏感

1	SHOW VARIABLES LIKE '%lower_case_table_name%';

默认为0，代表大小写敏感
设置1，大小写不敏感。创建的表、数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库查询
设置2，创建的表和数据库依据语句上格式存放，范式查找都是转换为小写进行

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名、表名、表的别名、变量名是严格区分大小写的；

2、关键字、函数名称在 SQL 中不区分大小写；

3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

MySQL在Windows的环境下全部不区分大小写

修改大小写规则：

MySQL 5版本需要修改my.cnf配置文件的参数，然后重启，但是在重启之前，需要将原来的数据库和表转换为小写，不然重启后将找不到数据库名
MySQL 8版本修改之后，重启会失败，必须要删除 /var/lib/mysql中的文件，也就是数据中的数据

SQL编写建议：

关键字和函数名全部大写
数据库名、表名、表别名、字段名、字段别名全部小写
SQL语句必须以分号结尾

sql_model

sql_mode会影响MySQL支持的SQL语法以及它执行的数据验证检查。通过设置sql_mode可以完成不同严格程度的数据校验，有效的保障数据准确性。

宽松模式：插入数据时，即便给了一个错误的数据，也可能会被接受，而且不报错。例如长度为2，插入abc，实际上会插入ab而不会报错

主要应用于数据库之间进行迁移。
严格模式：插入数据时，如果给了一个错误的数据，就会报错。

主要应用于生产、开发、测试环境。

-- 查看当前 sql_mode 
SELECT @@session.sql_mode; 
-- 设置 sql_mode 
SET SESSION sql_mode = '';
-- 永久设置需要配置在my.cnf配置文件中。

更多sql_mode：

https://mariadb.com/kb/en/sql-mode/

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

MySQL的数据目录

物理存储目录

库是以目录的方式存储。表存储在对应的目录中。MySQL数据库文件存放路径：/var/lib/mysql

1	ls -l /var/lib/mysql

1	SHOW VARIABLES LIKE '%datadir%';

数据库和文件系统的管理

像InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘上，操作系统用来管理磁盘的结构被称为文件系统，也就是说，像InnoDB、MyISAM这样的存储引擎都是把表存储在文件系统上的。

-- 默认数据库
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |            -- 保存MySQL服务器维护其他的数据库的基本信息，例如表、视图、触发器、列、索引的信息
| mysql              |      -- 核心数据库，存储用户、权限的信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息
| performance_schema |            -- 保存MySQL服务器运行过程中的一些状态信息，用来监控MySQL服务的各类性能指标
| sys                |            -- 通过视图的形式，把 information_schema 和 performance_schema结合起来，帮助管理员监控MySQL的技术性能
+--------------------+
9 rows in set (0.01 sec)

数据库在文件系统中的表示

不同的存储引擎，不同的MySQL版本，文件存储方式不同，这里用MySQL 8版本说明。

使用MyISAM引擎

1
2
3

CREATE DATABASE dbtest2;
USE dbtest2;
CREATE TABLE myisam1(id INT,`name` varchar(15)) ENGINE=MYISAM;

在/var/lib/mysql/dbtest2下，表文件：

1
2
3

-rw-r-----  1 mysql mysql 2.4K Nov 11 14:26 myisam1_539.sdi        // 元数据，列的信息：列名，属性，约束等	
-rw-r-----  1 mysql mysql 1.0K Nov 11 14:26 myisam1.MYI                // 索引
-rw-r-----  1 mysql mysql    0 Nov 11 14:26 myisam1.MYD                // 数据

使用InnoDB引擎（默认），表文件如下

1	-rw-r----- 1 mysql mysql 327680 Jul 16 09:42 time_zone_name.ibd // 列的结构：列名，属性，约束等，以及数据(如果存储在独立表空间)

InnoDB还提供系统表空间，查看配置，是否存在系统表空间还是独立表空间

1 2	SHOW VARIABLES LIKE 'innodb_file_per_table'; -- ON 代表使用独立表空间，OFF代表使用系统表空间

如果存在系统表空间，则会将数据信息和索引都存储在ibdata1中。

配置文件目录

1	cat /etc/my.cnf

用户与权限

MySQL的用户可以分为普通用户和root用户，root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码登管理权限；普通用户只拥有被授予的各种权限。

MySQL数据库的安全性通过账户管理来保证。

用户和密码的管理

用户和密码的增删改查

USE mysql;
-- 查看用户
SELECT `host`,`USER`,authentication_string FROM user;
-- 创建用户，不写host默认为%
CREATE USER user01 IDENTIFIED BY '123';
-- 增，创建一个host为127.0.0.1的用户
CREATE USER user01@127.0.0.1 IDENTIFIED BY '123';
-- 因此，user表中，是以 user 和 host 字段一起创建的联合主键 

-- 修改用户，其实是修改user表中的数据
UPDATE user SET user = 'user02' WHERE user = 'user01' AND host = '%';
-- 修改之后，一定要刷新用户信息
FLUSH PRIVILEGES;

-- 删除,如果没有设置host，默认是%
DROP USER user01;
DROP USER user01@127.0.0.1;
-- 另外一种方式，不推荐，这种方式删除可能会有残留
DELETE FROM user WHERE user = 'user01' AND host = '127.0.0.1';

密码管理

-- 修改自己的密码
ALTER user user() IDENTIFIED BY '123456';
SET PASSWORD = '123456';
-- 修改某个用户的密码
ALTER user 'user01'@'127.0.0.1' IDENTIFIED by '456';
-- 方式二 
SET PASSWORD FOR 'user01'@'127.0.0.1'='456';
-- 密码存储字段，是 authentication_string ，经过了加密处理
DESC user;
SHOW CREATE TABLE user;

密码可以通过配置插件设置安全性

密码复杂度
密码加密策略
密码过期策略（不需要插件）
密码重用策略

-- 设置密码马上过期，用户可登录，但是不能查询，需要重置自己的密码
ALTER user 'user01'@'127.0.0.1' PASSWORD EXPIRE;
-- 设置全局密码过期时间，也可以在配置文件中设置
SET PERSIST default_password_lifetime = 180; -- 每隔180天过期
-- 设置单独的账号过期时间
-- 90天过期
ALTER user 'user01'@'127.0.0.1' PASSWORD EXPIRE INTERVAL 90 DAY;
-- 永不过期
ALTER user 'user01'@'127.0.0.1' PASSWORD EXPIRE NEVER;
-- 沿用全局密码过期
ALTER user 'user01'@'127.0.0.1' PASSWORD EXPIRE DEFAULT;

权限

给某个对象赋予操作某个范围的某个权限(8.0版本中，创建用户和授权分来，不能在一条命令中实现)

1 2	-- 查看所有权限 SHOW PRIVILEGES;

权限可分为针对表权限、列权限、过程（存储过程、存储函数）权限。

-- 将 mitaka 数据库的所有的表 的 所有的 权限，赋予 [email protected]
GRANT ALL ON mitaka.* TO 'user01'@'127.0.0.1';
-- 赋予部分权限，例如查询和更新
GRANT SELECT,UPDATE ON mitaka.* TO 'user01'@'127.0.0.1';
-- 将所有权限赋予所有的库的所有表给用户，但是这个用户不能赋予其他用户权限
GRANT ALL PRIVILEGES ON *.* TO 'user01'@'127.0.0.1';

查看当前用户的权限

-- 权限带有 WITH GRANT OPTION 则代表可以将权限赋予其他用户
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
-- 查看某个用户的权限
SHOW GRANTS FOR 'user01'@'127.0.0.1';

回收权限

1	REVOKE ALL ON mitaka.* FROM 'user01'@'127.0.0.1';

注意：用户需要重新登录才能生效。

刷新权限

1	FLUSH PRIVILEGES;

权限表

MySQL通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。

连接核实阶段：用户登录，通过账号密码验证用户，使用user表的host、user、authentication_string字段匹配客户端登录信息。
请求核实阶段：当用户操作某个数据库或者表，也就是发送请求，通过以下权限表逐个核实权限，如果有权限则允许，如果没有权限，则查询下一个权限表，直到columns_priv表。

user表

DESC user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
-- 以下是权限
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
-- 以上是权限
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
| max_updates              | int unsigned                      | NO   |     | 0                     |       |
-- 最大连接数
| max_connections          | int unsigned                      | NO   |     | 0                     |       |
| max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
-- 插件
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
-- 密码
| authentication_string    | text                              | YES  |     | NULL                  |       |
-- 密码是否过期
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+

db表，体现用户在具体某个数据库的权限

desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(255)     | NO   | PRI |         |       | -- 主机 
| Db                    | char(64)      | NO   | PRI |         |       | -- 表
| User                  | char(32)      | NO   | PRI |         |       | -- 用户
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)

表tables_priv，体现表中的权限

desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type                                                                                                                              | Null | Key | Default           | Extra                                         |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host        | char(255)                                                                                                                         | NO   | PRI |                   |                                               |
| Db          | char(64)                                                                                                                          | NO   | PRI |                   |                                               |
| User        | char(32)                                                                                                                          | NO   | PRI |                   |                                               |
| Table_name  | char(64)                                                                                                                          | NO   | PRI |                   |                                               |
| Grantor     | varchar(288)                                                                                                                      | NO   | MUL |                   |                                               |
| Timestamp   | timestamp                                                                                                                         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   |     |                   |                                               |
| Column_priv | set('Select','Insert','Update','References')                                                                                      | NO   |     |                   |                                               |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.02 sec)

表columns_priv，体现列的权限

desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                                         |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host        | char(255)                                    | NO   | PRI |                   |                                               |
| Db          | char(64)                                     | NO   | PRI |                   |                                               |
| User        | char(32)                                     | NO   | PRI |                   |                                               |
| Table_name  | char(64)                                     | NO   | PRI |                   |                                               |
| Column_name | char(64)                                     | NO   | PRI |                   |                                               |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                                               |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.01 sec)

表procs_priv，存储过程和存储函数的权限

desc procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type                                   | Null | Key | Default           | Extra                                         |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host         | char(255)                              | NO   | PRI |                   |                                               |
| Db           | char(64)                               | NO   | PRI |                   |                                               |
| User         | char(32)                               | NO   | PRI |                   |                                               |
| Routine_name | char(64)                               | NO   | PRI |                   |                                               |
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                                               |
| Grantor      | varchar(288)                           | NO   | MUL |                   |                                               |
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                                               |
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.01 sec)

角色

MySQL 8.0引入的新功能，角色是权限的集合，可以给角色添加或移除权限，给用户赋予角色之后，用户就拥有角色中包含的权限。

管理角色

-- 创建角色
CREATE ROLE 'manager'@'%';
CREATE ROLE 'boss'@'%';
-- 给角色赋予权限
GRANT SELECT,UPDATE ON dbtest1.* TO 'manager';
GRANT ALL PRIVILEGES ON dbtest1.* TO 'boss';
-- 查看角色的权限
SHOW GRANTS FOR 'manager'@'%';
SHOW GRANTS FOR 'boss'@'%';
-- 回收角色的权限
REVOKE UPDATE ON dbtest1.* FROM 'manager';
-- 删除角色
DROP ROLE 'boss';

角色和用户

CREATE USER 'user4'@'%' IDENTIFIED BY '123456';
-- 将角色赋予到用户
GRANT 'manager'@'%' TO 'user4'@'%';
-- 角色赋予用户之后，还不能直接使用，还需要将角色激活
-- 角色创建之后，默认没有被激活
-- 使用用户的user4登录，查看当前角色
SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.01 sec)
-- 激活角色
SET DEFAULT ROLE 'manager'@'%' TO 'user4'@'%';
-- 再次退出、登录
select current_role();
+----------------+
| current_role() |
+----------------+
| `manager`@`%`  |
+----------------+
1 row in set (0.01 sec)

-- 激活方式2，设置默认激活状态
SHOW VARIABLES LIKE '%activate_all_roles_on_login%';
SET GLOBAL activate_all_roles_on_login=ON;
-- 对所有角色激活

-- 从用户撤销角色
REVOKE 'manager'@'%' FROM 'user4'@'%';
-- 需要重新登录，才会应用新的权限

-- 设置强制角色
-- 强制角色是给每个创建账户的默认角色，不需要手动设置，强制角色无法被REVOKE或者DROP 
-- 方式1：服务启动前设置，在配置文件中增加配置
-- 方式2：运行时设置 
-- 系统重启后仍然有效
SET PERSIST mandatory_roles = 'role1,role2@localhost,r2@%'; 
-- 系统重启后失效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r2@%';