MySQL（Ⅲ）
- 11、 进阶篇 —— 视图
- 12、 进阶篇 —— 存储过程
- 13、 进阶篇 —— 触发器
- 14、 进阶篇 —— 锁
- 15、 终结篇 —— InnoDB 存储引擎
- 16、 终结篇 —— MySQL管理工具
11、 进阶篇 —— 视图
11.1、概述
视图（View）是MySQL中的一种虚拟表，其内容是根据预保留的 SQL 逻辑动态地查询生成的，使用视图时，数据库系统会自动执行相关的 SQL 逻辑，并返回查询结果。因此，视图可以看作是一个预定义的查询语句
| 优点 | 描述 |
|---|---|
| 简化复杂查询 | 通过视图，可以将复杂的查询简化为一个简单的查询，使得数据访问更加方便 |
| 安全性 | 通过视图，可以限制用户对基表的访问，只暴露需要的数据，从而提高数据的安全性 |
| 数据抽象 | 视图可以隐藏表的复杂结构，为用户提供一个更简洁的接口 |
| 数据一致性 | 通过视图，可以确保不同用户看到的数据一致 |
| 视图的限制 |
|---|
| ① 视图不支持索引、触发器和默认值 |
| ② 某些复杂的查询可能不能直接转换为可更新的视图 |
| ③ 视图的性能可能不如直接查询基表，因为视图需要动态生成数据 |
11.2、基本语法
创建：
CREATE [ OR REPLACE ] VIEW 视图名 AS SELECT语句
自定义列名字：CREATE [ OR REPLACE ] VIEW(列名1，列名2...) 视图名 AS SELECT语句
查询创建视图的语句：SHOW CREATE VIEW 视图名
查询视图的数据：跟查询普通的表一样
修改视图法1：CREATE OR REPLACE VIEW 视图名 AS SELECT语句
修改视图法2：ALTER VIEW 视图名 AS SELECT语句
删除视图：DROP VIEW [ IF EXISTS ] 视图名
-- 创建视图
create OR REPLACE VIEW user_v_1 AS SELECT * FROM tb_user where age < 30;
-- 查询创建视图的语句
SHOW CREATE VIEW user_v_1;
-- 查询视图数据
SELECT * FROM user_v_1;
-- 修改视图
CREATE OR REPLACE VIEW user_v_1 AS SELECT name,age FROM tb_user WHERE age < 30;
SELECT * FROM user_v_1;
ALTER VIEW user_v_1(姓名,年龄) AS SELECT name,age FROM tb_user WHERE age < 30;
SELECT * FROM user_v_1;
-- 删除视图
DROP VIEW user_v_1;
11.3、检查选项 CASCADED
当我们往视图中插入数据时，其实会插入到关联的基表中，然而当我们插入的数据不符合视图的 SQL 逻辑时（例如视图呈现年龄小于30的，然后插入了一条年龄40的数据），会出现一种通过这个视图进行插入数据，但插入后这个视图又看不到的情况，显得不太尊重这个视图，例如
-- 建表
CREATE TABLE student(
id int AUTO_INCREMENT PRIMARY KEY,
name varchar(10),
age int,
gender char(1)
)
--插入数据
INSERT INTO student(name,age,gender) VALUES ('Niki',21,'男'),('Tom',23,'男'),('Siri',27,'女'),('Jerry',28,'男');
-- 创建视图
CREATE VIEW stu_v_1 AS SELECT name,age FROM student WHERE age < 25;
-- 通过视图插入数据
INSERT INTO stu_v_1(name,age) VALUES ('Sara',24);
INSERT INTO stu_v_1(name,age) VALUES ('Zayn',29);
SELECT * FROM stu_v_1; # 第二条插入的数据看不到
因此我们可以给视图添加 CASCADED 检查选项，当为视图插入或更新的数据，需要满足其 SQL 逻辑才能插入成功
语法：
CREATE VIEW 视图名 AS SELECT语句 WITH [ CASCADED ] CHECK OPTION ;（ WITH CHECK OPTION 默认选 CASCADE ）
-- 先删掉上插入的两条数据
DELETE FROM student WHERE name = 'Sara' OR name = 'Zayn';
-- 修改视图
ALTER VIEW stu_v_1 AS SELECT name,age FROM student WHERE age < 25 WITH CASCADED CHECK OPTION ;
-- 再次通过视图插入数据
INSERT INTO stu_v_1(name, age) VALUES ('Sara', 24);
INSERT INTO stu_v_1(name, age) VALUES ('Zayn', 29); # 插入失败
CASCADED 的工作原理是，检查插入的数据是否满足当前视图的 SQL 逻辑，以及满足所依赖的视图的 SQL 逻辑，例如
| 表 | 依赖 | 选项 | SQL逻辑 | 在v3插入age=11、17、28 |
|---|---|---|---|---|
| 视图 v3 | v2 | - | age<=15 | 没有检查选项，直接通过 |
| 视图 v2 | v1 | CASCADED | age>=10 | 11：符合，17：符合，28：符合 |
| 视图 v1 | 基表 | - | age<=20 | 受 v2 的CASCADED影响，v1也要检查 11：符合，17：符合，28：拦截 |
| 基表 | - | - | - | 11、17插入成功，28插入失败 |
11.4、检查选项 LOCAL
LOCAL 也是一种检查约束，与 CASCADED 不同的是，它只会对当前的视图做检查，而不会让其父视图也要做检查
语法：
CREATE VIEW 视图名 AS SELECT语句 WITH LOCAL CHECK OPTION ;
| 表 | 依赖 | 选项 | SQL逻辑 | 在v3插入age=14 |
|---|---|---|---|---|
| 视图 v3 | v2 | - | age<<20 | 没有检查选项，直接通过 |
| 视图 v2 | v1 | LOCAL | age>=10 | 14：符合 |
| 视图 v1 | 基表 | - | age<=15 | 没有检查选项，直接通过 |
| 基表 | - | - | - | 14插入成功 |
11.5、视图的更新原则
当视图的每一行与基表中的每一行是一一对应的关系时，视图才可以更新，如果视图的 SQL 逻辑包含以下任何一项，则视图不能更新
| 不可更新的关键字 |
|---|
| 聚合函数，例如 SUM()，MIN()、MAX()、COUNT()等 |
| DISTINCE |
| GROUP BY |
| HAVING |
| UNION 或者 UNION ALL |
12、 进阶篇 —— 存储过程
12.1、概述
存储过程是一组预编译的SQL语句，这些语句存储在数据库中，可以通过调用来执行。存储过程允许你将业务逻辑封装在数据库中，提供了一种更高效和灵活的方法来处理数据（函数）
| 优点 | 描述 |
|---|---|
| 提高性能 | 存储过程在首次执行时会被编译并缓存起来，以后再调用时直接执行编译好的代码，减少了SQL解析和编译的时间 |
| 重用代码 | 存储过程可以多次调用，避免了代码重复，提高了代码的可维护性 |
| 安全性 | 通过存储过程，可以控制对数据库的访问权限，保护底层数据 |
| 简化复杂操作 | 将复杂的业务逻辑封装在存储过程中，使得应用程序调用更简洁 |
| 减轻网络负担 | 将多个SQL语句封装起来存储在数据库中，现在发送一次请求即可完成相应的业务，而不是将多个SQL语句一句一句地与数据库交互，减轻了网络负担 |
12.2、基本语法
创建：
CREATE PROCEDURE 存储过程名([IN输入的参数列表，OUT返回的参数列表]) BEGIN ... END
调用：CALL 存储过程名([参数])
查看指定数据库的存储过程信息：SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名'
查看存储过程的创建语句：SHOW CREATE PROCEDURE 存储过程名
删除：DROP PROCEDURE 存储过程名
-- 创建存储过程
CREATE PROCEDURE p1()
BEGIN
SELECT COUNT(*) FROM student;
END;