视图（View）

迷南。 2021-09-25 14:28 421阅读 0赞

## 视图（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)