九、MYSQL8数据库视图
- 什么是视图?
- 数据库视图的特点：
- 视图常见用法
- 创建视图
- 创建视图时指定字段名
- 创建多表视图
- 修改视图
- 从视图更新数据--`强烈不建议`
- 重命名视图
- 删除视图
- 视图的约束
- WITH LOCAL CHECK OPTION
- WITH CASCADE CHECK OPTION
- 视图安全验证方法
- definer定义者权限
- invoker调用者权限
- 检测视图
- 查看视图
- 查看视图结构
- 查看创建视图的预计
- 查看视图的元数据
什么是视图?
个人的理解：数据库视图就是把一段子查询创建一个快捷方式.
视图是保存查询的结果，把查询的结果当做一张虚拟的表,视图依赖于原始的表数据和结构，如果原来的表数据或者结构发生了改变，视图的结构和数据也会发生相应改变。
数据库视图的特点：
- 简化代码
- 安全性，可以指定展示其中某几列数据，隐藏敏感信息
视图常见用法
创建视图
-- 创建视图语法结构
-- or replace如果视图存在则替换
CREATE or replace
VIEW 视图名 AS SQL查询语句;
-- 创建视图示例
CREATE VIEW ShowStu AS SELECT * FROM student;
创建视图时指定字段名
-- 创建视图时指定字段名
CREATE VIEW view_show_stu (sid,sname,sex,birth,classid) AS SELECT * FROM student;
创建多表视图
-- 创建多表视图
CREATE VIEW Get_student_score AS SELECT student.sno,student.sname,student.ssex,student.sbirthday,student.class,score.cno,score.degree FROM student,score WHERE student.sno = score.sno
修改视图
-- 修改视图语法结构：
ALTER VIEW 视图名 AS 新的查询sql
-- 修改视图示例
ALTER VIEW showstu AS SELECT student.sno,student.sname,student.ssex,student.sbirthday,student.class,score.cno,score.degree FROM student,score WHERE student.sno = score.sno
从视图更新数据–强烈不建议
视图的目的是为了使查询语句更易读，而不是为了写入数据。生产环境下禁止更新视图。
不可更新的：
- 聚合函数
- DISTINCT去重后的结果
- GROUP BY
- HAVING
- UNION或UNION ALL
- 位于选择列表中的子查询
- JOIN
- FROM子句中的不可更新视图
- WHERE子句中的子查询，引用FROM子句中的表
- 仅引用文字值(在该情况下，没有要更新的基本表)
重命名视图
-- 重命名视图
reanme table 修改前视图名 to 修改后视图名;
删除视图
-- 删除视图
DROP VIEW IF EXISTS 视图名;
视图的约束
WITH LOCAL CHECK OPTION
WITH LOCAL CHECK OPTION：该选项确保在插入或更新行时，仅限于符合视图定义条件的那些行。如果尝试插入或更新违反此条件的行，则将拒绝该操作。
例如：
以下视图只包含性别为男性的员工：
如果尝试插入一个性别为女性的员工，则该操作将被拒绝。
CREATE VIEW male_employees AS
SELECT *
FROM employees
WHERE gender = 'M'
WITH LOCAL CHECK OPTION;
WITH CASCADE CHECK OPTION
WITH CASCADE CHECK OPTION：该选项确保在插入或更新行时，不仅限于符合视图定义条件的那些行，而且还限制了所有与该视图有关的表。如果尝试插入或更新违反这些条件的行，则将拒绝该操作。
例如：
以下视图包含名字以“S”开头的员工及其所在的部门：
如果尝试插入一个名字不以“S”开头的员工并将其分配到已存在的部门，则该操作将被拒绝。
# 创建一个视图，只查询包含名字以“S”开头的员工及其所在的部门
CREATE VIEW s_员工部门 AS
SELECT e.*, d.部门名称
FROM 员工表 AS e
JOIN 部门表 d ON e.部门编号 = d.部门编号
WHERE e.名字 LIKE 'S%'
WITH CASCADE CHECK OPTION;
视图安全验证方法
MySQL 8 视图中的 Invoker 和 Definer 控制了视图的访问权限，分别基于使用视图的用户和视图的创建者的权限进行控制。在创建视图时，可以使用 SQL SECURITY 指定所需的访问控制级别。
definer定义者权限
在 MySQL 8 视图中，definer 表示视图的创建者或所有者。在创建视图时，可以使用 definer 指定视图的创建者，也可以使用 CURRENT_USER() 函数指定当前登录用户作为视图的创建者。创建视图的用户具备访问表的权限，同时视图的访问权限受到创建者角色的限制。在执行视图查询时，MySQL服务器会使用创建者的权限去访问底层表，以此来保护数据的安全性。
假设我们有一个名为employees的表，其中包含有关员工薪水的信息。现在我们想要创建一个只包含经理信息的视图，并将视图的创建者设置为 “admin” 用户。
示例代码：
定义了一个名为managers的视图，并将其设置为仅包含 title 字段值为 "Manager" 的记录。此外，我们还指定了 WITH CHECK OPTION，这将确保任何针对视图的 INSERT、UPDATE 或 DELETE 操作都必须符合筛选条件。最后，我们使用 SQL SECURITY DEFINER 来指定视图的访问权限是基于其创建者的权限进行控制的，并使用 DEFINDER 关键字将视图的创建者设置为 "admin@localhost"。
CREATE VIEW managers AS
SELECT * FROM employees
WHERE title = 'Manager'
WITH CHECK OPTION
SQL SECURITY DEFINER
DEFINER = 'admin@localhost';
invoker调用者权限
MySQL8 视图中的 Invoker 是指使用视图的用户。与 Definer 不同，Invoker 将使用自己的权限来执行对视图的查询，而不是使用视图的创建者的权限。当视图创建时，如果使用 SQL SECURITY INVOKER 来指定视图的访问控制，则视图将基于当前用户的权限进行访问控制。如果没有指定 SQL SECURITY 选项，则默认使用 SQL SECURITY DEFINER，这意味着视图将基于视图的创建者的权限进行访问控制。
示例代码：
假设我们有两个表 employees 和 salaries，其中 employees 表包含有关员工的详细信息，salaries 表包含有关员工薪水的信息。现在我们要在这两个表之间建立一个视图，并将其设置为仅显示工资大于等于10000的所有员工，并将视图的访问控制权限设置为基于当前用户的权限：
定义了一个名为 high_paid_employees 的视图，并将其设置为仅包含工资大于等于10000的员工记录。使用 SQL SECURITY INVOKER 可以将该视图的访问控制设置为基于当前用户的权限进行控制。
# 将该视图的访问控制设置为基于当前用户的权限进行控制。
CREATE VIEW high_paid_employees AS
SELECT e.*, s.salary FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.salary >= 10000
WITH CHECK OPTION
SQL SECURITY INVOKER;
检测视图
当视图引用的对象，比如表，列，视图，不存在时，视图变为不可用的，使用check table 视图名;可以检测视图的可用性
-- 检测视图的可用性
check table 视图名；
查看视图
查看视图结构
# 查看表和视图
show full tables;
-- 查看视图结构
DESC 视图名 \G;
查看创建视图的预计
-- 查看创建视图的预计
SHOW CREATE VIEW 视图名 \G;
查看视图的元数据
-- 使用information_schema查看元数据
-- table_schema是数据库的名称
SELECT * FROM information_schema,views WHERE table_schema='表名' AND table_name='视图名或表名'\G
