MySQL 基础知识（九）之视图

目录

1 视图的介绍

2 视图算法

3 创建视图

4 查看视图结构

5 修改视图

6 删除视图

7 参考文档

---

1 视图的介绍

视图是一张并不存储数据的虚拟表，其本质是根据 SQL 语句动态查询数据库中的数据。数据库中只存放了视图的定义，通过 SQL 语句使用视图时，会根据视图的定义进行查询。 优点： 简化代码：对于复杂的查询，通过视图可以不用每次都写那么多代码 增加数据的安全性：通过视图，用户只能对指定的数据进行操作 缺点： 查询性能不好：在很多场景下，对视图的查询无法使用基表（基表是创建视图时使用的表）的索引，需要对所有基表进行全表扫描后，将返回结果保存到临时表，再进行查询 维护代价高：基表发生变化时，视图也要进行更改，这需要一定的维护成本。尤其是对于复杂的视图而言，理解和维护视图更加困难 注：关于在实际开发中是否应该使用视图，大家各执一词。随着 MySQL 8.0 版本的视图有了较大的提升，这个话题又引起讨论。非必要不使用，谨慎使用。

2 视图算法

MySQL 提供了两种视图算法：MERGE 算法和 TEMPTABLE 算法 MERGE 算法：会将查询视图的 SQL 语句和创建视图时的查询 SQL 语句进行优化组合，然后对基表进行查询操作 TEMPTABLE 算法：首先通过创建视图时定义的查询 SQL 语句在基表上查询，之后将查询结果存入临时表，然后根据临时表重写用户查询视图的 SQL 语句，最后在临时表上执行重写的 SQL 语句并返回结果 不能使用 MERGE 算法的情况： 如果定义视图时的 SELECT 语句包含聚合函数、DISTINCT、GROUP BY、HAVING、UNION (ALL)、子查询，则不允许使用 MERGE 算法 此外，如果 SELECT 语句没有引用表（没有 from 表名），也不允许使用 MERGE 算法 注：对于应该使用 TEMPTABLE 算法却使用 MERGE 算法的视图，MySQL 将生成警告，并将算法设置为 UNDEFINED （ MySQL 会从 MERGE 算法和 TEMPTABLE 算法中选择适合的算法）

提示：客户端输入 SQL 语句字符串 -> 解析器进行解析（获得用户输入的 SQL 语句，query SQL ) -> 获得视图定义（创建视图时使用的 查询 SQL语句，view SQL）-> 优化组合 query SQL 语句和 view SQL 语句 -> 在基表上执行组合后的 SQL 语句 -> 返回查询结果

3 创建视图

创建视图常用代码格式 CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] OR REPLACE：用于替换已有的视图 ALGORITHM：用于设置视图算法 VIEW view_name：用于设置视图名称 column_list：用于设置视图列名，默认视图的列名是对应的基表的列名 select_statement：select 查询语句 WITH CASCADED | LOCAL CHECK OPTION：用于限制对视图进行的插入和更新操作，以确保插入或更新的数据符合视图的定义条件（where 语句中的条件） cascaded： 更新视图时要满足所有相关视图和表的条件 local：表示更新视图时满足该视图本身定义的条件即可 WITH CHECK OPTION 默认是 WITH CASCADED CHECK OPTION goods 表 drop table if exists goods; create table goods ( id int(10) primary key auto_increment, name varchar(14), netprice float(7,2), saleprice float(7,2), weight float(7,2), stockdate date )charset=utf8; #单条插入 insert into goods(name,netprice, saleprice, weight, stockdate) values('香蕉', 2.5, 3.8, 24, '2024-02-13'); #多条插入 insert into goods(name,netprice, saleprice, weight, stockdate) values ('苹果', 4.5, 7.2, 15, '2024-02-12'), ('苹果', 4.5, 7.5, 65, '2024-02-14'), ('橘子', 3.2, 4.5, 52, str_to_date('02-12-2024', '%m-%d-%Y')), ('橘子', 2.8, 4.5, 76, '2024-02-13'), ('橘子', 3.1, 5.2, 63, '2024-02-14'), ('葡萄', 2.1, 4.7, 26, str_to_date('2024/02/14', '%Y/%m/%d')); 创建视图 g_view 并查询视图数据 create view g_view as (select id, name, netprice, stockdate from goods); select * from g_view; 可更新视图（可以使用 insert、update、delete） 对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系 不是可更新视图的情况： 如果定义视图时的 SELECT 语句包含聚合函数、DISTINCT、GROUP BY、HAVING、UNION (ALL)、子查询（请看补充 1），则视图不是可更新视图 如果 SELECT 语句没有引用表（没有 from 表名）、包含 JOIN，则视图也是不可更新视图 如果 FROM 子句中包含不可更新视图、WHERE 子句中的子查询引用了 FROM 子句中的表（请看补充 2）、ALGORITHM = TEMPTABLE，则视图不是可更新视图 补充： 补充 1：对于该子查询，官方文档表述为：“位于选择列表中的子查询”，我的理解是该子查询是 select 子查询、列名。。。中的子查询。该子查询的结果只能是一行，不能是多行（多行报错：“Subquery returns more than 1 row”），对于“视图中的子查询为什么不能返回多行的问题”，GPT 给出：“数据库系统在执行视图中的子查询时，会将其视为一个独立的查询，并将结果限制为一行。这种限制是为了保持视图的简洁性和一致性。如果允许视图中的子查询返回多行结果，可能会导致复杂的查询逻辑和不确定的行为。” 补充 2：这种情况，无法更新视图，插入视图报错：“The target table 视图名 of the INSERT is not insertable-into”         在下图代码中，创建完视图 g_view_test1 后，向该视图插入一条数据，结果在该视图中查询不到该数据，查询基表后，可以发现数据已经插入基表。         分析代码可以得知：该视图采用 MERGE 算法，合并 select * from g_view_test1 和 select id, name from goods where name in (select distinct name from sales) 后的 SQL 语句为  select id, name from goods where name in (select distinct name from sales) 谨慎使用视图，更新视图时，若基表中的列值不能为 NULL，会更新数据失败   g_view 视图是可更新视图，以下代码对该视图进行插入数据测试，从测试结果我们可以知道：对可更新视图的插入（更新、删除）操作会影响基表，此外，可更新视图的插入（更新、删除）操作只能用于视图已有的列 # 向视图 g_view 插入数据 insert into g_view(name, netprice, stockdate) values('龙眼', 4.5, '2024-02-17'); # 查看视图 g_view select * from g_view; # 查看基表 goods select * from goods; 对插入可更新视图数据进行检验 (with cascaded | local check option) 以下代码在创建视图 g_view_1 时，设置筛选条件 where netprice > 4.5 并通过  with cascaded check option 启用检验。之后插入两条数据，一条数据的 netprice = 3.2 < 4.5，插入失败；一条数据的 netprice = 4.9 > 4.5，插入成功。 # 创建 g_view_1 视图，其中 where netprice > 4.5 create view g_view_1 as (select id,name,netprice,stockdate from goods where netprice > 4.5) with cascaded check option; # 向视图 g_view_1 插入数据 netprice = 3.2 < 4.5 insert into g_view_1(name, netprice, stockdate) values('柚子', 3.2, '2024-02-17'); # 向视图 g_view_1 插入数据 netprice = 4.9 > 4.5 insert into g_view_1(name, netprice, stockdate) values('车厘子', 4.9, '2024-02-17');

4 查看视图结构

显示表或视图的类型（base table 表示基表，view 表示视图） show full tables; 显示视图的结构 # 查看视图 g_view 的结构 show create view g_view; 查看视图列信息 # 查看视图 g_view 的列信息 desc g_view;

5 修改视图

使用 alter 修改视图 （和创建一个新的视图没什么区别，只是将 create 换成了 alter） # 通过 alter 修改视图 g_view alter view g_view as (select id,name,stockdate from goods); # 查看 g_view 数据 select * from g_view; 使用 create or replace 修改视图（实际是创建一个同名的新视图替换旧视图） # 通过 create or replace 修改视图 g_view create or replace view g_view as (select id,name,netprice,stockdate from goods); # 查看 g_view 数据 select * from g_view; 使用 rename 重命名视图 # 修改 g_view 视图的视图名为 g_view_new; rename table g_view to g_view_new; # 查看 g_view_new 数据 select * from g_view_new;

6 删除视图

# 删除视图 g_view_new; drop view g_view_new; # 查看视图或表 show full tables;

7 参考文档

MySQL视图https://www.zutuanxue.com/home/4/8_341MySQL 8.0 中文文档 第22章：视图 / 22.2. CREATE VIEW语法https://www.mysqlzh.com/doc/185.html