| 创建视图常用代码格式 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');
|
文章来源：https://www.toymoban.com/news/detail-826387.html
