九、MYSQL8数据库视图

什么是视图?

个人的理解：数据库视图就是把一段子查询创建一个快捷方式.
视图是保存查询的结果，把查询的结果当做一张虚拟的表,视图依赖于原始的表数据和结构，如果原来的表数据或者结构发生了改变，视图的结构和数据也会发生相应改变。

数据库视图的特点：

• 简化代码
• 安全性，可以指定展示其中某几列数据，隐藏敏感信息
  

视图常见用法

创建视图

-- 创建视图语法结构
-- 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