MySQL6️⃣视图，存储过程，触发器
1、视图
1.1、简介
视图（view）
基于数据库表（基表）创建的虚拟表。
-
本质：虚拟表
-
保存基表查询逻辑，对 view 执行 DQL 时自动查询基表。
-
数据存储在基表中，对 view 执行 DML 时变更基表数据。
-
-
作用：
- 简化：简化对数据的理解和操作。
- 安全：DCL 只能控制到表级别，无法控制到特定行列，view 可弥补这一功能。
- 数据独立：屏蔽真实表结构变更的影响。
嵌套视图
基于 view 创建的 view。
- 嵌套视图也是 view，操作相同。
- 嵌套的 view 之间形成一条链，且层层依赖。
1.2、DDL 语法
1.2.1、创建
-
CREATE：
- OR REPLACE：若 view 名已存在则替换。
- 字段列表：view 中包含的字段，可任意命名但不可重复。
-
SELECT：基表查询语句。
- 查询字段列表需与 view 字段列表一致。
- 既可以查表，也可以查 view。
-
WITH CHECK OPTION：约束检查选项。
CREATE [OR REPLACE] VIEW 视图名[(字段列表)] AS SELECT 语句 [WITH ([CASCADED|LOCAL]) CHECK OPTION];
1.2.2、查询
-
查看创建语句：仅关键字不同。
# 视图创建语句 SHOW CREATE VIEW 视图名; # 表创建语句 SHOW CREATE TABLE 表名; -
查看结构：
# 视图结构 DESC 视图名; # 表结构 DESC 表名;
1.2.3、更新
两种方式
-
REPLACE：替换同名视图。
CREATE OR REPLACE VIEW 视图名[(字段列表)] AS SELECT 语句 [WITH ([CASCADED|LOCAL]) CHECK OPTION]; -
ALTER：变更视图。
ALTER VIEW 视图名称[(字段列表)] AS SELECT 语句 [WITH ([CASCADED | LOCAL]) CHECK OPTION];
1.2.4、删除
仅关键字不同，可指定多个。
# 删除视图
DROP VIEW [IF EXISTS] 视图名[, 视图名...]
# 删除表
DROP TABLE [IF EXISTS] 表名[, 视图名...]
1.3、DML 执行
1.3.1、特点
view 是虚拟表。
数据存储在基表中，对 view 执行 DML 时变更基表数据。
Hint：DELETE 和 UPDATE 只能变更 view 可见的记录。
-
MySQL 会自动追加视图约束。
-
视图约束 = 定义 view 的基表查询 WHERE 条件。
# 执行 INSERT INTO 视图名[(字段列表)] VALUES (字段值); DELETE FROM 视图名 WHERE 条件; UPDATE 视图名 SET xxx WHERE 条件; # 实际执行 INSERT INTO 基表 VALUES (字段值); DELETE FROM 基表 WHERE 条件 AND 视图约束; UPDATE 表名 SET xxx WHERE 条件 AND 视图约束;
1.3.2、执行前提
前提：一行 view 记录唯一对应一行基表记录。
反例：若定义 view 的 SELECT 语句出现以下情况，则无法对 view 执行 DML。
- 聚合函数/窗口函数
- DISTINCT
- GROUP BY，HAVING
- UNION [ALL]
1.3.3、检查选项
① 简介
CHECK OPTION
对 view 执行 DML 时，MySQL 检测到 view 开启了 CHECK OPTION，会对 DML 进行约束。
语法：定义 view 时使用 CHECK OPTION，有两种范围。
-
CASCADED：级联（默认）
-
LOCAL：局部
CREATE [OR REPLACE] VIEW[(字段列表)] AS SELECT 语句 WITH ([CASCADED|LOCAL]) CHECK OPTION;
② 约束
约束行为：若 DML 变更的记录不满足 view 约束，则无法执行。
view 约束指的是定义时的基表查询条件。
约束范围：
| CASCADED | LOCAL | |
|---|---|---|
| 含义 | 级联 | 局部 |
| 检查范围 | 当前 view + 依赖链 | 当前 view |
| 说明 | MySQL 沿着 view 的依赖链，检查 DML 是否满足所有 view 的约束，直到基表 | 仅检查当前 view 的约束，若 CASCADED 遇到 LOCAL 会被中断 |
| 理解 | 一道洪水 | 一堵墙 |
2、存储过程（TODO）
存储过程：预编译并存储在数据库中的 SQL 语句的集合。
- 思想：数据库层面的 SQL 代码封装与复用。
- 特点：
- 简化开发。
- 可接收参数，返回数据。
- 减少 DB 与服务器之间的数据传输，提高数据处理效率。
DML
创建
CREATE PROCEDURE 存储过程名称( [参数列表] )
BEGIN
SQL语句
END;
创建语法中涉及分号，若在命令行中执行会被视为 SQL 语句的结束。
解决方案：使用 DELIMITER 关键字，指定 SQL 语句的结束符（如 $）
删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;
使用
调用
CALL 名称 ([参数列表])
查看
# 指定数据库的存储过程及状态信息
SELECT* FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'xxx';
# 存储过程的创建语句
SHOW CREATE PROCEDURE
游标
游标（CURSOR）：存储查询结果集。
在存储过程和函数中，使用游标对结果集进行循环处理。
-
定义：
DECLARE 游标名称 CURSOR FOR 查询语句; -
打开：
OPEN 游标名称; -
获取记录：
FETCH 游标名称 INTO 变量[变量];
条件处理程序
条件处理程序（Handler）：定义在流程控制结构执行过程中，遇到问题时相应的处理步骤。
DECLARE handler action HANDLER
FOR condition value L condition value]..statement
handler_action CONTINUE：继续执行当前程序
EXIT：终止执行当前程序
condition_value :
SQLSTATE sqlstate_value：状态码，如02000
SQLWARNING：所有以01开头的SQLSTATE代码的简写
NOT FOUND：所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写
例子：
NOTE：要先声明普通变量，再申请游标。
要求：
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表（id，name，profession）中。
create procedure p1l(in uage int)
begin
declare uname varchar(100);
decLare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
当 条件处理程序的处理的状态码为02000的时候，就会退出。
declare exit handler for SQLSTATE '02000'close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,Upro;
insert into tb_user_pro values(null,uname,Upro);
end while;
close u_cursor;
end;
3、触发器（TODO）
触发器：与表有关的数据库对象。
在 DML 执行前/后触发并执行的 SQL 语句集合。
-
作用：在数据库端确保数据的完整性，日志记录，数据校验等操作。
-
用法：使用别名 OLD 和 NEW，引用触发器中发生变化的记录内容。
OLD NEW INSERT - 待新增或已新增数据 UPDATE 更新前数据 待更新或已更新数据 DELETE 待删除或已删除数据 - -
特点：仅支持行级触发，不支持语句级触发。
（即若一条语句有 k 行，则会触发 k 次触发器。）
