13-视图

视图的概念

数据库中的视图是一种存储查询的数据库对象，是基于查询的一种虚拟表。是从一个或多个数据表或视图中导出的虚拟表或查询表，是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。视图保存的是一条查询语句，本身不含数据。视图其实只储存了它的定义（SELECT语句），而没有储存视图对应的数据，这些数据仍存放在原来的数据表中，在视图中看到的数据其实是基本表中的数据。视图可以象表一样使用。通过视图不仅可以查询获得数据，还可以修改数据。当对视图的数据进行操作时，系统是根据视图的定义去操作与视图相关联的基本表中的数据的。

视图的优点

直观的查询：用户只需要关注需要的数据，而不必关心底层复杂的实现。安全的查询：可以使用视图可以屏蔽、隐藏底层表的物理结构和数据；视图的权限与表的权限也可以完全不同。可以更新的查询：可以通过视图增、删、改底层源表的记录。

创建视图

语法

CREATE 
		[ALGORITHM = {UNDEFIEND | MERGE | TEMPTABLE}]
		VIEW <视图名>[(<字段名>[, ……n])]
		AS <SELECT语句>
		[WITH [CASCADED | LOCAL] CHECK OPTION]

说明：
ALGORITHM是可选参数，表示视图选择的算法，包括UNDEFIEND、MERGE和TEMPTABLE三个选项。

UNDEFINED

默认算法，由MySQL自动选择最合适的执行方式。通常情况下优先采用MERGE算法（合并视图定义与查询语句），但在涉及聚合函数（如
SUM()、
MIN()）、
DISTINCT、
GROUP BY、
HAVING、
UNION等操作时，会自动转为TEMPTABLE算法（使用临时表处理复杂查询）。 ‌

MERGE

将视图定义与查询语句合并执行，适用于一对一的行映射场景（如普通查询）。此方式效率较高，且支持视图更新操作。

TEMPTABLE

将查询结果存储在临时表中，适用于需要释放基表锁定的复杂查询（如涉及多表联合、复杂计算等），可减少锁定时间，但可能降低更新效率

<字段名>：视图字段的名称。一般该名称为所选数据源的字段名，也可以重新命名字段。
<SELECT语句>：用于创建视图的SELECT语句（查询语句）。可以单表查询，也可以多表查询。
WITH [CASCADED | LOCAL] CHECK OPTION：带有检查选项，默认不检查。

**CASCADED（默认）**‌：检查当前视图及其所有依赖视图（如基于该视图创建的子视图）的定义条件。例如，若视图A基于视图B创建，且视图B设置了CASCADED检查，则通过视图A插入数据时需同时满足视图A和B的条件。 ‌12‌LOCAL‌：仅检查当前视图自身的定义条件。例如，通过视图A插入数据时，只需满足视图A的条件即可，无需考虑其依赖的子视图

示例

创建视图v_stu，列出所有1995年9月1日及之后出生的学生名单。

创建视图v_cou，列出学分大于4的课程ID、课程编号、课程名称、学分，要求使用“WITH CHECK OPTION”子句。

创建视图v_stu_grade，列出学号、姓名、性别、班级、课程名称、成绩。

查看视图

使用DESCRIBE | DESC语句查看视图基本信息

DESCRIBE | DESC <视图名>

使用DESCRIBE | DESC语句查看视图v_stu的基本信息。

使用SHOW TABLE STATUS语句查看视图基本信息

SHOW TABLE STATUS [LIKE '视图名']

说明：LIKE关键字后面的字符串中也可以使用通配符。

使用SHOW TABLE STATUS语句查看视图v_stu的基本信息。

说明：以上执行结果显示，表的Comment项的值为VIEW，表明该表为视图。Engine、Data_length等项都为NULL或0，表明视图是虚拟表。

使用SHOW CREATE VIEW语句查看视图详细信息

SHOW CREATE VIEW <视图名>

使用SHOW CREATE VIEW语句查看视图v_stu的详细信息。

在views表中查看视图详细信息

在MySQL中，所有视图的定义都保存在information_schema数据库下的views表中。查询views表，可以用来查看数据库中所有视图的详细信息。

SELECT * FROM information_schema.views
	[WHERE TABLE_NAME='视图名']

说明：WHERE子句中也可以使用LIKE模糊查询

使用SELECT语句查询views表中的信息。

修改视图

CREATE OR REPLACE VIEW

语法

使用CREATE OR REPLACE VIEW语句可以来修改视图。如果视图已经存在，则对视图进行修改；如果不存在，则创建视图。其语法格式如下：

CREATE OR REPLACE
	[ALGORITHM = {UNDEFIEND | MERGE | TEMPTABLE}]
	VIEW <视图名>[(<字段名>[, ……n])]
	AS <SELECT语句>
	[WITH [CASCADED | LOCAL] CHECK OPTION]

示例

修改视图v_stu，列出所有1995年9月1日及之后出生的女生名单。

ALTER VIEW

语法

ALTER
	[ALGORITHM = {UNDEFIEND | MERGE | TEMPTABLE}]
	VIEW <视图名>[(<字段名>[, ……n])]
	AS <SELECT语句>
	[WITH [CASCADED | LOCAL] CHECK OPTION]

示例

修改视图v_stu_grade，列出“网络131”班学生的学号、姓名、班级、课程编号、课程名称、成绩。

更新视图

说明

通过视图除了可以查询表中数据以外，还可以通过视图更新（插入/修改/删除）表中数据。因为视图是一个虚拟表，其中没有数据。通过视图更新时，都是转换到基本表来更新。更新视图的语法与更新数据表的语法相同。更新视图时，只有更新权限范围内的数据，超出了范围，就不能更新。以下几种情况是不能更新视图的：
视图中包含由COUNT()、SUM()、AVG()、MAX()、MIN()等函数生成的列。
视图中包含DISTINCT、GROUP BY、HAVING等关键字。
视图中包含由常量、或者通过计算生成的列。
视图中包含由子查询生成的列。除了上述情况不能更新视图以外，WITH CHECK OPTION选项也将决定视图能否更新。

插入数据

通过视图向表中插入数据使用INSERT语句，其语法格式与直接向表中插入数据的语法格式相同。

通过视图v_stu插入一条新的学生记录。

通过视图v_cou插入一条新的课程记录。

通过视图v_cou再插入一条学分（credit）为4的课程记录。

说明：若在创建视图时指定了WITH CHECK OPTION选项，插入的记录必须要符合视图定义时指定的条件范围，否则不允许插入。

修改表中数据

通过视图修改表中数据使用UPDATE语句，其语法格式与直接修改表中数据的语法格式相同。

通过视图v_stu把学号为“1308013110”学生的班级修改为“网络131”。

说明：通过视图修改表中数据，必须要符合视图定义时指定的条件范围，只有满足条件范围内的数据才可以修改；同时，若在创建视图时指定了WITH CHECK OPTION选项，修改后的数据也要符合视图定义时指定的条件范围，否则不允许修改。

删除表中数据

通过视图删除表中数据使用DELETE语句，其语法格式与直接删除表中数据的语法格式相同。

通过视图v_cou删除课程编号为“01004”的课程记录。

说明：通过视图删除表中数据，必须要符合视图定义时指定的条件范围，只有满足条件范围内的数据才可以删除。

删除视图

删除视图使用DROP VIEW语句，其语法格式如下：

DROP VIEW [IF EXISTS] <视图名> [, …]

删除视图v_stu

练习题

在sales数据库中创建视图v_seller：列出销售员的编号、姓名、性别、地址。

CREATE VIEW v_seller
AS
SELECT saleNo,saleName,sex,address FROM seller;

在sales数据库中创建视图v_stocks：列出库存量小于500的商品记录，要求使用“WITH CHECK OPTION”选项。

CREATE VIEW v_stocks
AS
SELECT * FROM product WHERE stocks<500
WITH CHECK OPTION;

在sales数据库中创建视图v_sale_total：利用orderDetail表和product表列出每一种商品的销售数量和销售总额。

CREATE VIEW v_sale_total
AS
SELECT productNo AS '商品编号',productName AS '商品名称',SUM(quantity) AS '销售数量',SUM(totalMoney) AS '销售总额' 
FROM product JOIN orderDetail ON product.id=orderDetail.productId
GROUP BY productNo,productName;

在视图v_seller中查询所有男销售员的信息。

SELECT * FROM v_seller WHERE sex='男';

在视图v_sale_total中查询销售数量大于500的商品记录。

select * FROM v_sale_total WHERE 销售数量>500;

向视图v_seller中插入如下记录：
# S10 刘文明 男 金梅花园302号

INSERT v_seller(saleNo,saleName,sex,address) VALUES('S10', '刘文明', '男', '金梅花园302号');

通过视图v_stocks插入如下两条记录：
# P01100 白猫洗洁精500g 1 3.2 1175
# P02100 恒顺香醋500g 2 6.5 439

INSERT v_stocks(productNo,productName,categoryId,price,stocks) VALUES('P01100','白猫洗洁精500g', 1, 3.2, 1175);		# 插入失败	
INSERT v_stocks(productNo,productName,categoryId,price,stocks) VALUES('P02100','恒顺香醋500g', 2, 6.5, 439);				

通过视图v_seller把“S10”销售员的地址更改为“蓝钻小区176号”。

UPDATE v_seller SET address='蓝钻小区176号' WHERE saleNo='S10';

通过视图v_stocks把“P02100”商品的库存量更改为1392。

UPDATE v_stocks SET stocks=1392 WHERE productNo='P02100';		# 更改失败

通过视图v_seller删除编号为“S10”销售员的记录。

DELETE FROM v_seller WHERE saleNo='S10';

通过视图v_stocks删除编号为“P02100”商品的记录。

DELETE FROM v_stocks WHERE productNo='P02100';

删除以上创建的所有视图。

DROP VIEW v_seller,v_stocks,v_sale_total;