MySQL 基础知识（九）之视图

         视图是一张并不存储数据的虚拟表，其本质是根据 SQL 语句动态查询数据库中的数据。数据库中只存放了视图的定义，通过 SQL 语句使用视图时，会根据视图的定义进行查询。

优点：

• 简化代码：对于复杂的查询，通过视图可以不用每次都写那么多代码
• 增加数据的安全性：通过视图，用户只能对指定的数据进行操作

缺点：

• 查询性能不好：在很多场景下，对视图的查询无法使用基表（基表是创建视图时使用的表）的索引，需要对所有基表进行全表扫描后，将返回结果保存到临时表，再进行查询
• 维护代价高：基表发生变化时，视图也要进行更改，这需要一定的维护成本。尤其是对于复杂的视图而言，理解和维护视图更加困难

注：关于在实际开发中是否应该使用视图，大家各执一词。随着 MySQL 8.0 版本的视图有了较大的提升，这个话题又引起讨论。非必要不使用，谨慎使用。

 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 算法中选择适合的算法）

创建视图常用代码格式

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)、子查询（官方文档表述为：位于选择列表中的子查询，我的理解是该子查询是 select 子查询、列名。。。中的子查询），则视图不是可更新视图
• 如果 SELECT 语句没有引用表（没有 from 表名）、包含 JOIN，则视图也是不可更新视图
• 如果 FROM 子句中包含不可更新视图、WHERE 子句中的子查询引用了 FROM 子句中的表、ALGORITHM = TEMPTABLE，则视图不是可更新视图

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');

显示表或视图的类型（base table 表示基表，view 表示视图）

show full tables;

显示视图的结构

# 查看视图 g_view 的结构
show create view g_view;

查看视图列信息

# 查看视图 g_view 的列信息
desc g_view;

使用 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;

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