@9 视图

常见的数据库对象

对象	描述
表 (TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束 (CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图 (VIEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引 (INDEX)	用于提高查询性能，相当于书的目录
存储过程 (PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数 (FUNCTION)	用于完成一次特定的计算，具有一个返回值
触发器 (TRIGGER)	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

视图概述

视图解决的需求：针对不同用户给定不同的查询视图，一般不作为修改使用。

视图是一种 虚拟表，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
在数据库中，视图不会保存数据，数据真正保存在数据表中。

创建视图

在 CREATE VIEW 语句中嵌入子查询

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]  # 别名声明处1
AS 查询语句   # 别名声明处2 查询语句中别名声明 # 查询语句可以基于表，也可以基于视图
[WITH [CASCADED|LOCAL] CHECK OPTION]

精简版

CREATE VIEW 视图名称 AS 查询语句

举例：

CREATE VIEW empvu80 AS SELECT  employee_id, last_name, salary FROM employees WHERE department_id = 80;

OR REPLACE：表示替换已有视图
ALGORITHM：表示视图选择算法，默认算法是 UNDEFINED(未定义的)：MySQL 自动选择要使用的算法；merge 合并；temptable 临时表
select_statement：表示 select 语句
[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内
- cascade 是默认值，表示更新视图的时候，要满足视图和表的相关条件
- local 表示更新视图的时候，要满足该视图定义的一个条件即可
TIPS：推荐使用 WHIT [CASCADED|LOCAL] CHECK OPTION 选项，可以保证数据的安全性

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。
基于视图 a、b 创建了新的视图 c，如果将视图 a 或者视图 b 删除，会导致视图 c 的查询失败。

DROP VIEW [IF EXISTS] 视图名称;
DROP VIEW [IF EXISTS] 视图名称1,视图名称2,视图名称3,...;

查看视图

语法 1：查看数据库的表对象、视图对象

SHOW TABLES;

语法 2：查看视图的结构

DESC / DESCRIBE 视图名称;

语法 3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

执行结果显示，注释 Comment 为 VIEW，说明该表为视图，其他的信息为 NULL，说明这是一个虚表。

语法 4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

更新视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

在定义视图的时候指定了“ ALGORITHM = TEMPTABLE ”，视图将不支持 INSERT 和 DELETE 操作；
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持 INSERT 操作；
在定义视图的 SELECT 语句中使用了 JOIN联合查询，视图将不支持 INSERT 和 DELETE 操作；
在定义视图的 SELECT 语句后的字段列表中使用了 数学表达式 或 子查询，视图将不支持 INSERT，也不支持 UPDATE 使用了数学表达式、子查询的字段值；
在定义视图的 SELECT 语句中有 嵌套子查询 、 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持 INSERT、UPDATE、DELETE；
视图定义基于一个 不可更新视图；
常量视图。

修改视图

修改视图是指修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。

方式 1：使用 CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)
AS
SELECT  employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees

说明：CREATE VIEW 子句中各列的别名应和子查询中各列相对应。

方式 2：ALTER VIEW

ALTER VIEW 视图名称 AS 查询语句
# 更复杂的语句
ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

使用 WITH CHECK OPTION 约束

对于可以执行 DML 操作的视图，定义时可以带上 WITH CHECK OPTION 约束。

作用：对视图所做的 DML 操作的结果，不能违反视图的 WHERE 条件的限制。

mysql> create view v_veterans
    -> as
    -> select * from PLAYERS
    -> where birth_date < '1960-01-01'
    -> with check option;
Query OK, 0 rows affected (0.01 sec)
 
mysql> create view v_veterans
    -> as
    -> select * from PLAYERS
    -> where birth_date < '1960-01-01'
    -> with check option;
Query OK, 0 rows affected (0.01 sec)

利用 with check option 约束限制，保证更新视图是在该视图的权限范围之内。

嵌套视图：定义在另一个视图的上面的视图

mysql> create view v_ear_veterans
    -> as
    -> select * from v_veterans
　　 -> where JOINED < 1980;

使用 WITH CHECK OPTION 约束时，(不指定选项则默认是 CASCADED)

可以使用 CASCADED 或者 LOCAL 选项指定检查的程度：

①WITH CASCADED CHECK OPTION：检查所有的视图，例如：嵌套视图及其底层的视图

②WITH LOCAL CHECK OPTION：只检查将要更新的视图本身，对嵌套视图不检查其底层的视图

定义视图时的其他选项

CREATE [OR REPLACE]
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
　　[DEFINER = { user | CURRENT_USER }]
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
　　[WITH [CASCADED | LOCAL] CHECK OPTION]

1、ALGORITHM 选项：选择在处理定义视图的 select 语句中使用的方法

①UNDEFINED：MySQL 将自动选择所要使用的算法

②MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分

③TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句

缺省 ALGORITHM 选项等同于 ALGORITHM = UNDEFINED

2、DEFINER 选项：指出谁是视图的创建者或定义者

①definer= ‘用户名’@‘登录主机’

② 如果不指定该选项，则创建视图的用户就是定义者，指定关键字 CURRENT_USER(当前用户)和不指定该选项效果相同

3、SQL SECURITY 选项：要查询一个视图，首先必须要具有对视图的 select 权限。

但是，如果同一个用户对于视图所访问的表没有 select 权限，那会怎么样？

SQL SECURITY 选项决定执行的结果：

①SQL SECURITY DEFINER：定义(创建)视图的用户必须对视图所访问的表具有 select 权限，也就是说将来其他用户访问表的时候以定义者的身份，此时其他用户并没有访问权限。

②SQL SECURITY INVOKER：访问视图的用户必须对视图所访问的表具有 select 权限。

缺省 SQL SECURITY 选项等同于 SQL SECURITY DEFINER

视图权限总结：

使用 root 用户定义一个视图(推荐使用第一种)：u1、u2

1）u1 作为定义者定义一个视图，u1 对基表有 select 权限，u2 对视图有访问权限：u2 是以定义者的身份访问可以查询到基表的内容；

2）u1 作为定义者定义一个视图，u1 对基表没有 select 权限，u2 对视图有访问权限，u2 对基表有 select 权限：u2 访问视图的时候是以调用者的身份，此时调用者是 u2，可以查询到基表的内容。

视图优缺点

视图的优点

1. 操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

2. 减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3. 数据安全

MySQL 将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。

同时，MySQL 可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4. 适应灵活多变的需求
当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

5. 能够分解复杂的查询逻辑
数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

视图的不足

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。

实际项目中，如果视图过多，会导致数据库维护成本的问题。

🪴 Obsidian Publish

探索