视图（View）

视图（View）

视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。(视图不是真实存在磁盘上的)

优点

1. 视图机制可以对关系模型中的逻辑数据独立性提供支持，通过使用视图定义外部模式中的关系，可以对应用隐藏数据库概念模式的变化。例如：如果有一个关系的模式发生了变化，可以基于旧的模式定义一个视图，这样使用旧模式的应用可以继续使用这个视图
2. 试图机制对于数据安全也很有意义：可以通过为某些用户定义视图，给出允许他们访问的信息。例如：可以定义视图，只允许学生看到其他学生的姓名和年龄，而不允许他们看到成绩，并且所有学生只能访问这个视图，而不允许直接访问记录学生信息的关系表。

缺点

1. 操作视图会比直接操作基础表要慢，要尽量避免在大型表上创建视图
2. 使用嵌套视图时，会重复访问基础表，带来性能损耗
3. 视图不能索引，也不能有关联的触发器或默认值

基本操作

注意：本文的SQL 在 MySQL 8.0版本中测试通过

创建视图

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]

CREATE OR REPLACE：如果不存在这个视图，则创建视图，如果已经存在了视图，则将视图替换掉，or replace 是可选择的。

ALGORITHM：表示视图选择的算法，可选择

DEFINER：限制权限，表示可使用该视图的用户

SQL SECURITY：指定视图查询数据时的安全验证方式

WITH CHECK OPTION：表示更新视图时要保证在该视图的权限范围内（针对可更新视图）

CASCADED：更新视图时要满足所有相关视图和表的条件

LOCAL：更新视图时满足该视图本身定义的条件即可

查看视图

show table status where comment='view';

查看视图结构

describe view_name;

修改视图

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]

更新视图

更新视图是指通过视图来插入(insert)、更新(update)和删除(delete)基本表中的数据，虽然视图是一个虚拟表，但是视图更新时，视图基于的基本表也会更新。

可更新视图：视图中的行和基本表的行之间必须具有一对一的关系。

不可更新视图：如果视图包含以下结构中的任何一种，它就是不可更新的。

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等；
• DISTINCT 关键字；
• GROUP BY 子句；
• HAVING 子句；
• UNION 或 UNION ALL 运算符；
• 位于选择列表中的子查询；
• FROM 子句中的不可更新视图或包含多个表；
• WHERE 子句中的子查询，引用 FROM 子句中的表；
• ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

删除视图

drop view if exists view_name;

应用实例

假如有初始商品和订单表信息如下：

mysql> select * from goods;
+-------+----------+-------+--------+
| id    | name     | price | number |
+-------+----------+-------+--------+
| 10001 | cream    |    80 |    950 |
| 10002 | cleanser |    40 |   5000 |
| 10003 | sweater  |    99 |  13000 |
| 10004 | honey    |    88 |  14000 |
| 10005 | keyboard |    66 |  12000 |
+-------+----------+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from orders;
+----------+---------+--------------+
| order_id | good_id | order_number |
+----------+---------+--------------+
|     1001 |   10001 |           10 |
|     1002 |   10002 |          200 |
|     1003 |   10003 |          300 |
|     1004 |   10004 |          250 |
|     1005 |   10005 |          150 |
|     1006 |   10001 |           50 |
+----------+---------+--------------+
6 rows in set (0.01 sec)

建立各种商品销售单价和销售数量的视图order_view，即显示各项商品的id，商品 的单价，商品的销售数量。

create or replace View order_view as
	select goods.id as id, goods.price as price, sum(orders.order_number) as number 
    from goods join orders 
    where goods.id = orders.good_id 
    group by orders.good_id;

验证结果如下：

mysql> select * from order_view;
+-------+-------+--------+
| id    | price | number |
+-------+-------+--------+
| 10001 |    80 |     60 |
| 10002 |    40 |    200 |
| 10003 |    99 |    300 |
| 10004 |    88 |    250 |
| 10005 |    66 |    150 |
+-------+-------+--------+
5 rows in set (0.00 sec)

由于order_view为不可更新视图，尝试进行更新操作时将会得到如下错误：

mysql> update order_view set number = 120 where order_id = 10001;
ERROR 1288 (HY000): The target table order_view of the UPDATE is not updatable

如果创建good_view让商品按照价格排序，行和基本表的行之间保持一对一的关系。

create or replace View good_view as 
	select * from goods 
    order by price;

当对视图进行更新操作时，基本表也会更新。

mysql> update good_view set price = 123 where id = 10002;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+-------+----------+-------+--------+
| id    | name     | price | number |
+-------+----------+-------+--------+
| 10001 | cream    |    80 |    950 |
| 10002 | cleanser |   123 |   5000 |
| 10003 | sweater  |    99 |  13000 |
| 10004 | honey    |    88 |  14000 |
| 10005 | keyboard |    66 |  12000 |
+-------+----------+-------+--------+
5 rows in set (0.00 sec)