【学习笔记】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;