【学习笔记】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 逻辑，例如

      11.4、检查选项 LOCAL

        LOCAL 也是一种检查约束，与 CASCADED 不同的是，它只会对当前的视图做检查，而不会让其父视图也要做检查

语法：CREATE VIEW 视图名 AS SELECT语句 WITH LOCAL CHECK OPTION ;

      11.5、视图的更新原则

        当视图的每一行与基表中的每一行是一一对应的关系时，视图才可以更新，如果视图的 SQL 逻辑包含以下任何一项，则视图不能更新

12、 进阶篇 —— 存储过程

      12.1、概述

        存储过程是一组预编译的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;