MySQL视图全面详解

快来打我* 2023-02-10 12:29 14阅读 0赞

# 视图 #

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

视图其实就是一个select返回的结果集，用于方便我们查询而创建的"临时表"，简化我们的查询语句。

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

> 使用视图的大部分情况是为了保障数据安全性，提高查询效率。

创建测试表：

CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `db01`;
    
    DROP TABLE IF EXISTS `class`;
    CREATE TABLE `class` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    insert  into `class`(`id`,`name`) values 
    (1,'Java'),
    (2,'UI'),
    (3,'产品');
    
    
    DROP TABLE IF EXISTS `student`;
    
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `class_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    /*Data for the table `student` */
    
    insert  into `student`(`id`,`name`,`class_id`) values 
    (1,'张三',1),
    (2,'李四',1),
    (3,'王五',2),
    (4,'赵刘',1),
    (5,'钱七',3);

## 1.1 创建视图 ##

*  语法

CREATE [O
    R 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]

*  **OR REPLACE**：如图视图名重复，则表示替换原有视图
 *  **ALGORITHM**：表示创建视图时所使用的算法
    
     *  **UNDEFINED**：默认值，没有指定算法时一般采用merge算法
     *  **MERGE**：合并算法，在基于视图创建新的视图时，将创建视图时所使用的select语句与将要创建新的视图的select语句进行合并处理，效率较高。
     *  **TEMPTABLE**：临时表算法，在基于视图创建新的视图时，先执行原有视图的select语句，然后再执行新视图的select语句，查询两次，效率较低。

示例：

create or replace ALGORITHM=MERGE view test1 as 
    select * from user;

> 创建`test1`视图，如果此前已经有`test1`视图，那么替换他，并且该视图的算法为`merge`。（合并算法）

*  DEFINER：指出谁是视图的创建者
    
     *  USER：指定创建视图的用户
     *  CURRENT\_USER：不指定创建视图的用户（默认是当前操作用户）

示例：

create or replace definer='root' view test2 as 
    select * from user;

> 定义`test2`视图是root用户创建的。搭配`SQL SECURITY`一起使用。

*  SQL SECURITY：设置操作的视图的安全策略
    
     *  DEFINER：只有创建视图的用户才有权访问
     *  INVOKER：任何用户都可以访问（前提必须有操作权限，如select、update权限等）  
        示例：

create sql security=invoker view test3 as 
    select * from user;

> 代表任何人都具备操作`test3`视图的权限。

*  **WITH**：更改视图数据时，检查数据
    
     *  **LOCAL**：更改视图时，如果更改后的数据不符合当初创建视图时的条件时，则不予修改，并报如下错误：
    
        CHECK OPTION failed 'dbName.viewName
    
    示例：
    
        -- 创建视图
        mysql> create view test4 as select * from student where class_id=1 with local check option;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> update test4 set class_id=2 where id=1;
        ERROR 1369 (HY000): CHECK OPTION failed 'db01.test4'
        mysql>
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]
    
    > LOCAL：只会检查当前修改的条件是否符合当前视图创建时的条件，如果在创建此视图时使用到了别的视图，LOCAL方式不会检查其嵌套的视图。
    
    小测试：
    
        mysql> select * from test4;
        +----+--------+----------+
        | id | name   | class_id |
        +----+--------+----------+
        |  1 | 张三   |        1 |
        |  2 | 李四   |        1 |
        |  4 | 赵刘   |        1 |
        +----+--------+----------+
        3 rows in set (0.00 sec)
        
        mysql> create or replace view test5 as select * from test4 where name='张三' with local check option;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> update test5 set class_id=2 where id=1;
        Query OK, 1 row affected (0.00 sec)
        Rows matched: 1  Changed: 1  Warnings: 0
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]
    
    > 只要修改后的数据满足本次视图创建的条件即可。不需要满足创建此视图所引用的其他视图的创建条件。
    
     *  **CASCADED**：在视图嵌套的过程中，修改当前视图中的数据，必须满足创建此视图所引用到的所有视图的创建条件。
    
    示例：
    
        mysql> select * from student;
        +----+--------+----------+
        | id | name   | class_id |
        +----+--------+----------+
        |  1 | 张三   |        1 |
        |  2 | 李四   |        1 |
        |  3 | 王五   |        2 |
        |  4 | 赵刘   |        1 |
        |  5 | 钱七   |        3 |
        +----+--------+----------+
        5 rows in set (0.00 sec)
        
        mysql> select * from test4;
        +----+--------+----------+
        | id | name   | class_id |
        +----+--------+----------+
        |  1 | 张三   |        1 |
        |  2 | 李四   |        1 |
        |  4 | 赵刘   |        1 |
        +----+--------+----------+
        3 rows in set (0.00 sec)
        
        mysql> create or replace view test5 as select * from test4 where name='张三' with cascaded check option;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> update test5 set class_id=2 where id=1;		
        # 不满足创建test4视图的条件
        ERROR 1369 (HY000): CHECK OPTION failed 'db01.test5'
        mysql> update test5 set name='zs' where id=1;
        # 不满足创建test5视图的条件
        ERROR 1369 (HY000): CHECK OPTION failed 'db01.test5'
        mysql>
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]
    
    > `CASCADED`不仅要满足当前视图的创建条件，也需要满足本次创建视图时引用到的其他视图的创建条件。

## 1.2 修改视图 ##

### 1）创建时修改 ###

*  语法：

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 view test4 as select * from student where class_id=1;

### 2）使用alter语句更改视图 ###

*  语法：

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]

*  示例：

alter ALGORITHM=TEMPTABLE view test4 as select * from student where class_id=3;

### 3）更新视图 ###

*  语法：

update viewName set column=newValue

*  示例：

update test4 set name='qq' where id=5;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]  
有下列内容之一，视图不能做DML操作：

1）select子句中包含distinct

2）select子句中包含组函数

3）select语句中包含group by子句

4）select语句中包含order by子句

5）select语句中包含union 、union all等集合运算符

6）where子句中包含相关子查询

7）from子句中包含多个表

8）如果视图中有计算列，则不能更新

9）如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

## 1.3 删除视图 ##

删除视图时，只能删除视图的定义，不会删除数据，也就是说不动基表

*  语法：

DROP VIEW [IF EXISTS]   
    view_name [, view_name] ...

*  示例：

drop view test1,test2,test3,test4;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200520230152167.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200520233742914.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200520234252294.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200520235141394.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70