视图

数据库对象与视图的理解

image-20220427224153204

1、视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

2、视图

  • 视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
  • 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
  • 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
  • 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

image-20220427224349462

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
+ 常见的数据库对象
    + 表
    + 数据字典
        + 系统表
    + 约束
        + 保证数据完整性
    + 视图
        + 一个或多个数据表里的数据的逻辑显示
    + 索引
        + 提高查询性能
    + 存储过程
    + 存储函数
    + 触发器
        + 事件监听器，完成事件处理
+ 视图
    + 就是原表，只是看到部分的
+ 视图方式存储sql语句
+ 对视图的DML操作等同原表的操作
+ 视图就是简化查询、控制权限

创建视图

1、单表视图

说明1：实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。

说明2：在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

2、多表联合视图

利用视图对数据进行格式化

当我们创建好一张视图之后，还可以在它的基础上继续创建视图。

1
2
3
4
5
6
7
8
CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句 
[WITH [CASCADED|LOCAL] CHECK OPTION]

CREATE VIEW 视图名称 
AS 查询语句
1
2
3
4
5
6
7
8
9
10
11
12
+ 创建视图
+ 针对单表的视图
    + 字段名
    + 聚合函数
+ 针对多表的视图
    + 数据进行格式化
+ 基于视图创建视图
+ 查看视图
    + SHOW TABLES
    + DESC xxx
    + SHOW TABLE STATUS LIEK ‘xxx’
    + SHOW CREATE VIEW xxx

查看、删除、更新、修改视图

1、查看视图

1
2
3
4
5
6
7
8
9
10
#语法1：查看数据库的表对象、视图对象
SHOW TABLES;
#语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
#语法3：查看视图的属性信息
#查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等） 
SHOW TABLE STATUS LIKE '视图名称'\G 
#执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
#语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

2、更新视图

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

3、不可更新的视图

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

image-20220427225001786

4、修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图

说明：CREATE VIEW 子句中各列的别名应和子查询中各列相对应。

1
CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) ASSELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80;

方式2：ALTER VIEW

1
2
3
ALTER VIEW 视图名称 
AS
查询语句

5、删除视图只是删除视图的定义，并不会删除基表的数据。

DROP VIEW IF EXISTS 视图名称;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+ 视图数据和表数据为同一数据
+ 不能更新视图中的数据
    + 视图字段在表中不存在（聚合函数）
+ 视图的行和表的行之间必须存在一对一关系
+ 不能更新
    + JOIN
    + 子查询
    + 聚合函数等
+ 主要为了方便查询！！
+ 优点
    + 操作简单
        + 基于视图简化查询
    + 减少数据冗余
    + 数据安全
        + 控制访问权限
    + 适应需求
        + 可以修改视图而非修改表
    + 分解复杂查询逻辑
        + 多个视图结合完成复杂查询
+ 缺点
    + 维护成本高
    + 表结构改变必须修改视图

总结

1. 操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

2. 减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3. 数据安全

MySQL将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性 。视图相当于在用户和实际的数据表之间加了一层虚拟表。同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4. 适应灵活多变的需求 当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

5. 能够分解复杂的查询逻辑 数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

image-20220427225255586

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。

实际项目中，如果视图过多，会导致数据库维护成本的问题。

所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。

存储过程与函数

MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。

存储过程

1、存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。

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

2、特点

  • 简化操作，提高了sql语句的重用性，减少了开发程序员的压力
  • 减少操作过程中的失误，提高效率
  • 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
  • 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

3、和视图、函数对比

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是 虚拟表 ，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以 直接操作底层数据表 ，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

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

1
2
3
4
5
6
7
8
9
+ 视图是虚拟表
+ 存储过程是存储SQL
+ 存储过程没有返回值
+ 分类（携带的参数）
    1、没有参数（无参数无返回） 
    2、仅仅带 IN 类型（有参数无返回） 
    3、仅仅带 OUT 类型（无参数有返回） 
    4、既带 IN 又带 OUT（有参数有返回） 
    5、带 INOUT（有参数有返回）

存储过程的创建与调用

1、参数前面的符号的意思

  • IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
  • OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
  • INOUT ：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。

3、 characteristics 表示创建存储过程时指定的对存储过程的约束条件

image-20220427230133319

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

5、需要设置新的结束标记

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

6、存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。

1
2
3
4
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) [characteristics ...] 
BEGIN
    存储过程体
END
1
2
3
4
5
6
7
8
9
10
11
+ 无参数、无返回值
+ 存储过程的调用
    + CALL
+ 带OUT
+ INTO
+ IN参数
+ IN和OUT
    + 返回值不是真正意义上的返回
+ INOUT
idea公司的datagrip可以报错
+ 缺点

存储函数的创建与调用

学过的函数：LENGTH、SUBSTR、CONCAT等

1、说明

  • 参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数
  • RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，对函数而言这是 强制 的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。
  • characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
  • 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

2、调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是 用户自己定义 的，而内部函数是MySQL 的 开发者定义 的。

1
CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回值类型 [characteristics ...] BEGIN函数体 #函数体中肯定有 RETURN 语句 END
1
2
3
4
5
6
7
8
9
10
11
12
+ 存储函数
    + 只有IN参数
    + 一定有return
+ 创建函数
    + 必须指明特性
+ 主要是函数体
    + DML——SELECT查询
+ 对比
    + 存储函数一定有返回值
        + 查询操作使用
    + 存储过程返回值相当于赋给传入的变量、改变引用
        + 更新操作使用

image-20220427230503246

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

1
2
3
4
5
6
7
8
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
+ 查看
    + 1：SHOW CREATE
    + 2：SHOW STATUS
    + 3：information_schema.Routines
+ 修改
    + 特性、名字
+ 删除
+ 使用建议
    + 优点
        + 一次编译多次使用
        + 减少开发工作量
        + 封装性
        + 安全
            + 使用权限
        + 减少网络传输
    + 缺点
        + 可移植性差
        + 调试难
        + 版本管理不好
        + 不适合高并发

总结

优点

  • 存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译， 这就提升了 SQL 的执行效率。
  • 可以减少开发工作量。将代码 封装 成模块，实际上是编程的核心思想之一，这样可以把复杂的问题 拆解成不同的模块，然后模块之间可以 重复使用 ，在减少开发工作量的同时，还能保证代码的结构清 晰。
  • 存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限 ，这样就和视图一样具有较强的安全性。
  • 可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
  • 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要 连接一次即可 。

缺点

image-20220427230830120

  • 可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
  • 调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
  • 存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
  • 它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就不适用了。

存储过程既方便，又有局限性。尽管不同的公司对存储过程的态度不一，但是对于我们开发人员来说，不论怎样，掌握存储过程都是必备的技能之一。

变量、流程控制与游标

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

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

系统变量

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

系统变量分为全局系统变量（需要添加 global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。

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

image-20220427231148342

2、特点

  • 全局系统变量针对于所有会话（连接）有效，但 不能跨重启

  • 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。

3、查看系统变量

  • 查看所有或部分系统变量

  • 作为 MySQL 编码规范，MySQL 中的系统变量以 两个“@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

  • 有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。具体方法：

    方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）

    方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

1
2
3
4
5
6
7
8
9
10
11
12
13
+ 变量
+ 系统变量
    + 全局系统变量
    + 会话系统变量
+ 全局系统变量不能跨重启
+ 会话系统变量，当前会话有效（建立连接）
+ 查看系统变量
+ 查看指定系统变量
    + @@开头表示系统变量
+ 修改系统变量
    + 配置文件
    + set
        + 重启数据库、重启会话连接失效

用户变量

1、分类

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 一个“@” 开头。根据作用范围不同，又分为 会话用户变量 和 局部变量 。

  • 会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效。
  • 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数 中使用。

2、会话用户变量

1
2
3
4
#方式1：“=”或“:=” 
SET @用户变量 = 值; SET @用户变量 := 值; 
#方式2：“:=” 或 INTO关键字 
SELECT @用户变量 := 表达式 [FROM 等子句]; SELECT 表达式 INTO @用户变量 [FROM 等子句];

3、局部变量

定义：可以使用 DECLARE 语句定义一个局部变量

作用域：仅仅在定义它的 BEGIN … END 中有效

位置：只能放在 BEGIN … END 中，而且只能放在第一句

1
2
3
4
5
6
7
8
9
10
BEGIN
    #声明局部变量
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; 
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值]; 
    #为局部变量赋值 
    SET 变量名1 = 值; 
    SELECT 值 INTO 变量名2 [FROM 子句]; 
    #查看局部变量的值 
    SELECT 变量1,变量2,变量3; 
END

image-20220427231621416

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+ 用户变量
    + 会话用户变量@
    + 局部变量
        + BEGIN和END中有效
+ 会话用户变量
    + 创建
        + SET
        + SELECT
    + 使用
        + SELECT
+ 局部变量
    + 创建在过程、函数中
    + 创建在第一句
        + DECLARE
    + 指明类型

定义条件与处理程序

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

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

2、定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个 错误名字 和 指定的 错误条件 关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。

image-20220427231916497

1
2
3
4
5
6
7
8
9
10
11
12
#方法1：捕获sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE'; 
#方法2：捕获mysql_error_value 
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE'; 
#方法3：先定义条件，再调用 
DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE'; 
#方法4：使用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR'; 
#方法5：使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE'; 
#方法6：使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+ 定义条件和处理程序
    + 可能遇到的问题
    + 处理方式
+ 定义条件
+ 定义处理程序
    + 处理方式
        + CONTINUE
        + EXIT
        + UNDO
    + 错误类型
        + 错误码、错误字符
        + 错误条件
        + 匹配代码
    + 处理语句

流程控制

image-20220427232105832

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
+ IF
    + 这些是dba用的存储过程，大厂对开发也有要求的
    + 举例1
        + IF XXX
            THEN SELECT XXX
        + ELSEIF XXX
            + THEN SELECT
        + END IF
    + 举例2
        + 输入员工id
            + 根据xx情况判断涨薪资等UPDATE
+ CASE语句
    + CASE XX
        + WHEN XX THEN SELECT
        + WHEN XX THEN SELECT
        + ELSE SELECT XXX
    + END CASE
    + 输入员工id
        + 根据xx情况进行不同的操作
        + case：离散型
+ 三种循环
    + loop
    + while
    + repeat
    + （标签）: LOOP
        + XXX
        + IF XX THEN LEAVE （标签）
    + END LOOP（标签）
    + while
        + 循环条件
        + 循环体
        + 迭代条件
    + repeat
        + 上来必先执行一次
    + WHILE √
+ leave
    + break
+ iterate	
    + continue

游标

1、什么是游标（或光标）

虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是 随意定位到某一 条记录 ，并对记录的数据进行处理。

这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面集合的语言有了面向过程开发的能力。

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

MySQL中游标可以在存储过程和函数中使用。

我们就可以通过游标来操作数据行，如图所示此时游标所在的行是“108”的记录，我们也可以在结果集上滚动游标，指向结果集中的任意一行。

image-20220427232523168

2、第一步，声明游标

在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。

3、第二步，打开游标

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。

4、第三步，使用游标（从游标中取得数据）

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误

5、第四步，关闭游标

有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。

6、游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

7、MySQL8全局变量持久化

image-20220427232835869

1
2
3
4
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name
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
+ 游标
    + 对每一条记录进行处理
+ 使用步骤
    + 声明游标
        + DECLAER XXX CURSOR FOR XXX
    + 打开游标
        + OPEN
    + 使用游标（从游标中获取数据）
        + FETCH
    + 关闭游标
        + CLOSE
+ 举例cursor
    + 声明局部变量
    + 声明游标
    + 打开游标
    + 循环
        + 使用游标
        + 循环体
    + 关闭游标
+ 小结
    + 优点
        + 逐条读取数据
    + 缺点
        + 使用游标，会对数据加锁
        + 影响系统效率和内存
+ MYSQL8.0
    + 全局变量的持久化

触发器

在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如 商品信息 和 库存信息 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。

这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用 事务 包裹起来，确保这两个操作成为一个 原子操作 ，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很 容易忘记其中的一步 ，导致数据缺失。

这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。

概述

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。

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

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

创建

1、CREATE TRIGGER

2、参数

  • 表名 ：表示触发器监控的对象。
  • BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
  • INSERT|UPDATE|DELETE ：表示触发的事件。
    • INSERT 表示插入记录时触发；
    • UPDATE 表示更新记录时触发；
    • DELETE 表示删除记录时触发
1
2
3
4
CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW
触发器执行的语句块;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+ 触发器
    + 事件操作，触发监听器，执行任务
+ 触发器的创建
    + 作用在某个表上
    + INSERT、UPDATE、DELETE时
    + BEFORE、AFTER进行操作
    + 执行语句
+ 不忘初心！！
+ 创建触发器
    + 插入触发器
+ 示例
    + NEW
        + 获取插入...的那条记录
    + 手动抛出错误
        + SIGNAL xxx

查看、删除触发器

1、看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等

方式1：查看当前数据库的所有触发器的定义

方式2：查看当前数据库中某个触发器的定义

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息

2、触发器也是数据库对象，删除触发器也用DROP语句。

1
2
3
4
5
SHOW TRIGGERS\G 
SHOW CREATE TRIGGER 触发器名 
SELECT * FROM information_schema.TRIGGERS;

DROP TRIGGER IF EXISTS 触发器名称;

总结

1、优点

  • 触发器可以确保数据的完整性。
  • 触发器可以帮助我们记录操作日志。
    • 利用触发器，可以具体记录什么时间发生了什么。比如，记录修改会员储值金额的触发器，就是一个很好的例子。这对我们还原操作执行时的具体场景，更好地定位问题原因很有帮助。
  • 触发器还可以用在操作数据前，对数据进行合法性检查。

2、缺点

  • 触发器最大的一个问题就是可读性差。
    • 因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能 不受应用层的控制 。这对系统维护是非常有挑战的。
  • 相关数据的变更，可能会导致触发器出错。
    • 特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率

注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
+ 查看、删除触发器
    + SHOW TRIGGERS
    + DROP TRIGGER
+ 优点
        + 保证数据的完整性
            + 更新其他表的数据
        + 记录操作日志
            + 每次操作记录日志
        + 对数据进行合法性检验
            + 防止录入错误数据
    + 缺点
        + 可读性差
            + 不受应用层控制
        + 数据的更新，可能造成触发器出错
+ 注意点
    + 外键约束引起表的改动，触发器不会激活
+ 复制表结构
+ 触发器
    + 备份存储删除的记录：回收站

MySQL8.0新特性

写在前面

MySQL从5.7版本直接跳跃发布了8.0版本 ，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。

新特性

1.更简便的NoSQL

2.更好的索引

3.更完善的JSON支持

4.安全和账户管理

5.InnoDB的变化

6.数据字典

7.原子数据

8.资源管理

9.字符集支持

10.优化器增强

11.公用表表达式

12.窗口函数

13.正则表达式支持

14.内部临时表

15.日志记录

16.备份锁

17.增强的MySQL复制

移除特性

1.查询缓存

2.加密相关

3.空间函数相关

4.N和NULL

5.mysql_install_db

6.通用分区处理程序

7.系统和状态变量信息

8.mysql_plugin工具

窗口函数

1、使用窗口函数，只用了一步就完成了查询。而且，由于没有用到临时表，执行的效率也更高了。很显然，在需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好。

MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

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

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

image-20220427234258613

2、语法结构

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

  • OVER 关键字指定函数窗口的范围。
    • 如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
    • 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
  • 窗口名：为窗口设置一个别名，用来标识窗口。
  • PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
  • ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
  • FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

3、序号函数、分布函数、前后函数、首尾函数、其他函数

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

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
+ 新特性
    + nosql支持
    + 更好的索引
    + JSON支持
    + 安全和账户管理
    + InnoDB的优化
    + 数据字典
    + 原子DDL
    + 资源管理
    + 默认字符集
    + 优化器增强
    + 公用表表达式
        + 替换子查询
    + 窗口函数
    + 正则表达式
    + 内部临时表
    + 日志
    + 备份锁
    + mysql复制
+ 新特性1：窗口函数
    + 方式一
        + 使用临时表
    + 方式二：窗口函数
+ 窗口函数
    + 介绍窗口函数
+ 语法结构
    + partition by xxx
        + 分区
    + 函数
+ 分布函数
+ LAG函数、LEAD函数
    + 查询差值
+ 首尾函数
    + FIRST
    + LAST
+ NTILE：分组

公用表表达式

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。依据语法结构和执行方式的不同，公用表表达式分为

普通公用表表达式 和 递归公用表表达式 2 种。

1、普通公用表表达式

1
2
3
WITH CTE名称 
AS （子查询）
SELECT|DELETE|UPDATE 语句;

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

2、递归公用表表达式

递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己。

递归公用表表达式由 2 部分组成，分别是种子查询和递归查询，中间通过关键字 UNION [ALL]进行连接。这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集，之后递归查询会一直执行，直到没有任何新的查询数据产生，递归返回。

1
2
3
WITH RECURSIVE CTE名称 
AS （子查询）
SELECT|DELETE|UPDATE 语句;

3、总之，递归公用表表达式对于查询一个有共同的根节点的树形结构数据，非常有用。它可以不受层级的限制，轻松查出所有节点的数据。如果用其他的查询方式，就比较复杂了。

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