视图

## 1 含义

1. 视图是一种**虚拟存在的逻辑表**，**本身并不包含数据**，只是**作为一个 `select` 语句保存在数据字典中**。
2. 视图**数据来自定义视图的查询中使用的表**，**使用视图动态生成**，因此**基表数据发生了改变时**，**视图也会跟着改变**，同时**对视图数据的修改也会影响基表的数据**。

## 2 优点

1. **简单：** 使用视图的**用户完全不需要关心后面对应的表结构**、**关联条件**和**筛选条件**，对用户来说**已经是过滤好的复合条件的结果集**。
2. **安全：** 使用视图的用户**只能访问他们被允许查询的结果集**，对**表的权限管理并不能限制到某个行某个列**，但是**通过视图就可以简单实现**。
3. **数据独立：** 一旦视图的结构定了，可以**屏蔽表结构变化对用户的影响**，**源表增加列对视图没有影响**，**源表修改列名**，则**可以通过修改视图来解决**，**不会造成对访问者的影响**。

## 3 相关操作

### 3.1 创建视图

#### 3.1.1 语法

```sql
-- 创建视图
CREATE VIWE 视图名称 AS SQL 语句

-- 完整
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED|LOCAL] CHECK OPTION]
```

#### 3.1.2 注意事项

1. **创建视图需要 `CREATE VIEW` 的权限**，并且**对于查询涉及的列有 `SELECT` 权限**，如果**使用 `CREATE OR REPLACE` 或者 `ALTER` 修改视图**，那么还**需要该视图的 `DROP` 权限**。
2. `OR REPLACE`：在**视图存在的情况下可对视图进行修改**，**视图不存在的情况下可创建视图**。
3. `ALGORITHM`：表示**视图选择算法**：
   1. `MERGE`：
      1. **将引用视图语句的文本与视图定义合并起来**，**使得视图定义的某一部分取代语句的对应部分**。
         1. 假设有如下视图定义语句：

```sql
            CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
            SELECT c1, c2 FROM t WHERE c3 > 100;
            ```
         2. 示例一：

1. 假如我们有以下查询语句：

```sql
               SELECT * FROM v_merge;
               ```
            2. 此时 MySQL 会对上面的查询语句做如下处理：

1. 将`v_merge` 变成`t`。
               2. 将`*` 变成`vc1`、`vc2`，对应于`c1`、`c2`。
               3. 将视图定于中的`WHERE` 语句添加到查询语句后面。
            3. 因此，上面的查询语句就会变成下面的形式：

```sql
               SELECT c1, c2 FROM t WHERE c3 > 100;
               ```
         3. 示例二：

1. 假如我们有以下查询语句：

```sql
               SELECT * FROM v_merge WHERE vc1 < 100;
               ```
            2. 这个查询语句的处理和上面查询语句的处理类似，只不过会把 `vc1 < 100` 变成 `c1 < 100`，然后使用 `AND` 把视图定义语句中的 `WHERE` 条件和查询语句的条件连接起来，处理后的查询语句就会变成下面的形式：

```sql
               SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
               ```
      2. 如果视图中**包含下面的结构**，则**不可以使用 `MERGE` 算法**：
         1. **聚合函数**（例如`SUM()`、`MIN()`、`MAX()`、`COUNT`）。
         2. `DISTINCT`。
         3. `GROUP BY`。
         4. `HAVING`。
         5. `LIMIT`。
         6. **`UNION` 或者是 `UNION ALL`**。
         7. **`SELECT` 中包含子查询**。
         8. **分配给用户的变量**。
         9. **仅含有常量**。
      3. 如果`MERGE`**算法不能使用时**，则**会使用 `TEMPTABLE` 算法**。
   2. `TEMPTABLE`：
      1. **视图的结果将被置于临时表中**，然后**使用他执行语句**。
      2. 该算法的一大优点是**创建临时表之后**，并**在完成语句处理之前**，**能够释放基表上的锁定**，与`MERGE` 算法相比，**锁定释放的速度更快**，这样，**使用视图的其他客户端不会被屏蔽过长时间**。
   3. `UNDEFINED`：
      1. 该种情况下 MySQL 会**自动选择要使用的算法**。
      2. 相比于`TEMPTABLE`，在可以的情况下 MySQL 会更**倾向于选择 `MERGE` 算法**，因为`MERGE` 算法更加高效，而且如果**使用了 `TEMPTABLE`**，**视图是不可更新的**。
      3. 如果**没有指定任何算法的话**，MySQL 将**会使用 `UNDEFINED` 算法**。
4. `select_statement`：表示`select`**语句**。
5. `WITH [CASCADED|LOCAL] CHECK OPTION`：表示**视图在更新时检查更新语句的范围**：
   1. `CASCADED`：
      1. **不仅对当前视图的定义条件进行检查**，**而且会对所有依赖的视图的条件进行检查**。
      2. **默认为 `CASCADED`**。
      3. 示例：
         1. 假如有如下视图：

```sql
            mysql> CREATE OR REPLACE VIEW payment_view AS
                -> SELECT payment_id,amount FROM payment
                -> WHERE amount < 10 WITH CHECK OPTION;

mysql> CREATE OR REPLACE VIEW payment_view2 AS
                -> SELECT payment_id,amount FROM payment_view
                -> WHERE amount > 5 WITH CASCADED CHECK OPTION;
            ```
         2. 更新 `payment_view2`：

```
            mysql> UPDATE payment_view2 SET amount=10
                -> WHERE payment_id = 3;
            ERROR 1369 (HY000): CHECK OPTION failed 'sakila.payment_view2'
            ```
         3. 在**更新的时候会报错**，因为 `amount = 10`**虽然满足了当前视图 `payment_view2`的定义条件**，**但不满足其所依赖的 `payment_view`的定义条件**（`amount < 10`）。
   2. `LOCAL`：
      1. **只对当前视图的定义条件进行检查**。
      2. 示例：
         1. 假如有如下视图，其中视图 `payment_view`的定义和上面一样：

```shell
            mysql> CREATE OR REPLACE VIEW payment_view1 AS
                -> SELECT payment_id,amount FROM payment_view
                -> WHERE amount < 5 WITH LOCAL CHECK OPTION;
            ```
         2. 更新 `payment_view1`：

```shell
            mysql> UPDATE payment_view1 SET amount=10
                -> WHERE payment_id = 3;
            Query OK, 1 row affected (0.03 sec)
            Rows matched: 1 changed: 1 warnings: 0
            ```
         3. **更新的时候没有报错**，因为 `amount = 10`**符合当前视图 `payment_view1`的定义条件**（`amount < 5`），而且在这种范围下**不需要检查其所依赖的视图的定义条件**。

#### 3.1.3 示例

##### 3.1.3.1 在单表上创建视图

```shell
mysql> create view v_F_players(编号,名字,性别,电话)
    -> as
    -> select PLAYERNO,NAME,SEX,PHONENO from PLAYERS
    -> where SEX='F'
    -> with check option;
Query OK, 0 rows affected (0.00 sec)

mysql> desc v_F_players;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 编号    | int(11)  | NO   |     | NULL    |       |
| 名字    | char(15) | NO   |     | NULL    |       |
| 性别    | char(1)  | NO   |     | NULL    |       |
| 电话    | char(13) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from  v_F_players;
+--------+-----------+--------+------------+
| 编号    | 名字      | 性别    | 电话        |
+--------+-----------+--------+------------+
|      8 | Newcastle | F      | 070-458458 |
|     27 | Collins   | F      | 079-234857 |
|     28 | Collins   | F      | 010-659599 |
|    104 | Moorman   | F      | 079-987571 |
|    112 | Bailey    | F      | 010-548745 |
+--------+-----------+--------+------------+
5 rows in set (0.02 sec)
```

##### 3.1.3.2 在多表上创建视图

```shell
mysql> create view v_match
    -> as 
    -> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
    -> from 
    -> PLAYERS a,MATCHES b,TEAMS c
    -> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_match;
+----------+-----------+---------+-----+------+--------+----------+
| PLAYERNO | NAME      | MATCHNO | WON | LOST | TEAMNO | DIVISION |
+----------+-----------+---------+-----+------+--------+----------+
|        6 | Parmenter |       1 |   3 |    1 |      1 | first    |
|       44 | Baker     |       4 |   3 |    2 |      1 | first    |
|       83 | Hope      |       5 |   0 |    3 |      1 | first    |
|      112 | Bailey    |      12 |   1 |    3 |      2 | second   |
|        8 | Newcastle |      13 |   0 |    3 |      2 | second   |
+----------+-----------+---------+-----+------+--------+----------+
5 rows in set (0.04 sec)
```

### 3.2 查看视图

1. **使用 `show create view`语句查看视图信息**：

```shell
   mysql> show create view v_F_players\G;
   *************************** 1. row ***************************
                   View: v_F_players
            Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_F_players` AS select `PLAYERS`.`PLAYERNO` AS `编号`,`PLAYERS`.`NAME` AS `名字`,`PLAYERS`.`SEX` AS `性别`,`PLAYERS`.`PHONENO` AS `电话` from `PLAYERS` where (`PLAYERS`.`SEX` = 'F') WITH CASCADED CHECK OPTION
   character_set_client: utf8
   collation_connection: utf8_general_ci
   1 row in set (0.00 sec)
   ```
2. 有关**视图的信息记录在 `information_schema.views`中**：

```shell
   mysql> select * from information_schema.views 
       -> where TABLE_NAME='v_F_players'\G;
   *************************** 1. row ***************************
          TABLE_CATALOG: def
           TABLE_SCHEMA: TENNIS
             TABLE_NAME: v_F_players
        VIEW_DEFINITION: select `TENNIS`.`PLAYERS`.`PLAYERNO` AS `编号`,`TENNIS`.`PLAYERS`.`NAME` AS `名字`,`TENNIS`.`PLAYERS`.`SEX` AS `性别`,`TENNIS`.`PLAYERS`.`PHONENO` AS `电话` from `TENNIS`.`PLAYERS` where (`TENNIS`.`PLAYERS`.`SEX` = 'F')
           CHECK_OPTION: CASCADED
           IS_UPDATABLE: YES
                DEFINER: root@localhost
          SECURITY_TYPE: DEFINER
   CHARACTER_SET_CLIENT: utf8
   COLLATION_CONNECTION: utf8_general_ci
   1 row in set (0.00 sec)   
   ```

### 3.3 修改视图

1. 当**视图使用的算法为 `MERGE`时才可以更新视图**，**使用 `TEMPTABLE`算法的视图不可以更新**，因此更新视图时需要看一下**视图的定义中是否满足[使用 MERGE算法的条件](#3-1-2-注意事项)**，如果**不满足条件**的话**就不可以对视图进行修改**。
2. 修改视图的方法有以下两种：
   1. **使用 `CREATE OR REPLACE VIEW`语句**：

```sql
      create or replace view view_name as select_statement;
      ```
   2. **使用 `ALTER`语句**：

```sql
      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]
      ```

上面的**部分参数的使用方式可以参考[3.1.2 注意事项](#3-1-2-注意事项)**。
   3. **`DML`操作**，因为**视图本身没有数据**，所以**对视图进行的 `DML`操作最终都体现在基表中**：

```shell
      mysql> create view v_student as select * from student;

mysql> select * from v_student;
      +--------+--------+------+
      | 学号    | name   | sex  |
      +--------+--------+------+
      |      1 | 张三    | M    |
      |      2 | 李四    | F    |
      |      5 | 王五    | NULL |
      +--------+--------+------+

mysql> update v_student set name='钱六' where 学号='1';

mysql> select * from student;
      +--------+--------+------+
      | 学号    | name   | sex  |
      +--------+--------+------+
      |      1 | 钱六    | M    |
      |      2 | 李四    | F    |
      |      5 | 王五    | NULL |
      +--------+--------+------+
      ```
   4. **使用 `drop`删除视图**，删除视图时，**只删除视图的定义**，**不会删除数据**：

```sql
      DROP VIEW [IF EXISTS] view_name [, view_name];
      ```

## 参考文献

1. [什么是视图？什么是游标？](https://github.com/wolverinn/Waking-Up/blob/master/Database.md#%E4%BB%80%E4%B9%88%E6%98%AF%E8%A7%86%E5%9B%BE%E4%BB%80%E4%B9%88%E6%98%AF%E6%B8%B8%E6%A0%87)
2. [深入解析 MySQL 视图 VIEW](https://www.cnblogs.com/geaozhang/p/6792369.html)。
3. [MySQL - 视图](https://lyonyang.github.io/blogs/03-MySQL/07-MySQL%20-%20%E8%A7%86%E5%9B%BE.html)。
4. [mysql 视图 algorithm_MySQL 的视图讲解](https://blog.csdn.net/weixin_26789847/article/details/113200428)。
5. [20.5.2 View Processing Algorithms](https://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html).