MySQL笔记（四）——视图

港控/mmm° 2023-07-04 04:49 13阅读 0赞

# 视图 #

视图是一种虚拟存在的表，并不在数据库中实际存在，它的列和数据行都是对原查询中使用的表的列和数据行的映射。由于视图是虚拟的表，它并没有存放任何数据，只有在使用视图时，才会从原表中动态地生成数据。对于用户来说，视图真实使用的表基本上是透明的。

## 视图操作 ##

视图的操作主要是创建、修改、更新、删除和查询，语句都和表的操作类似。笔记中以MySQL官方的demo库sakila来做练习。

### 创建 ###

创建视图的用户需要拥有create view的权限，并且对于视图中查询语句涉及的表拥有select权限。  
*create view `view_name` \[(col\_list)\] as select\_statement \[with \[cascaded | local\] check option\];*  
例子：查询city表中所有country\_id为23的城市id、名称，对这一查询创建视图。  
*create view city\_list(`city_id`, `city_name`) as  
select city\_id, city  
from  
sakila.city  
where  
country\_id=23;*  
使用这个视图的方式和查表类似，因为对于用户而言，视图就相当于表：  
*select \* from sakila.city\_list limit 10;*  
±--------±----------+  
| city\_id | city |  
±--------±----------+  
| 46 | Baicheng |  
| 47 | Baiyin |  
| 80 | Binzhou |  
| 109 | Changzhou |  
| 136 | Datong |  
| 139 | Daxian |  
| 145 | Dongying |  
| 157 | Emeishan |  
| 159 | Enshi |  
| 166 | Ezhou |  
±--------±----------+  
10 rows in set (0.00 sec)

### 修改 ###

### 更新 ###

更新视图，是指通过视图来插入、更新或删除表中的数据，其过程实质上是对视图更新数据，再由视图通过映射将数据写入真实的表中，视图相当于是用户与真实表之间的桥梁。  
例子：建立用于查询payment表中所有amount>5的数据行的视图，查询结果集包括payment\_id、customer\_id、amount，并使用这个视图来更新数据。  
先建立视图：  
*create view payment\_list(`payment_id`, `customer_id`, `amount`) as  
select  
payment\_id, customer\_id, amount  
from  
payment  
where  
amount > 5;*  
查询前10列：  
*select \* from sakila.payment\_list limit 10;*  
±-----------±------------±-------+  
| payment\_id | customer\_id | amount |  
±-----------±------------±-------+  
| 3 | 1 | 5.99 |  
| 5 | 1 | 9.99 |  
| 10 | 1 | 5.99 |  
| 11 | 1 | 5.99 |  
| 14 | 1 | 7.99 |  
| 32 | 1 | 5.99 |  
| 36 | 2 | 6.99 |  
| 38 | 2 | 5.99 |  
| 39 | 2 | 5.99 |  
| 40 | 2 | 5.99 |  
±-----------±------------±-------+  
10 rows in set (0.00 sec)  
接下来更新视图数据，令payment\_id=3对应的行customer\_id为2：  
*update `payment_list` set customer\_id=2 where payment\_id=3;*  
查询验证：  
*select \* from sakila.payment\_list where payment\_id=3;*  
±-----------±------------±-------+  
| payment\_id | customer\_id | amount |  
±-----------±------------±-------+  
| 3 | 2 | 5.99 |  
±-----------±------------±-------+  
1 row in set (0.00 sec)  
上文说过，视图中是不含有数据的，更新视图最终更新的还是查询的表中的数据，为了验证我们还可以查一下原表：  
*select \* from sakila.payment where payment\_id=3;*  
±-----------±------------±---------±----------±-------±--------------------±--------------------+  
| payment\_id | customer\_id | staff\_id | rental\_id | amount | payment\_date | last\_update |  
±-----------±------------±---------±----------±-------±--------------------±--------------------+  
| 3 | 2 | 1 | 1185 | 5.99 | 2005-06-15 00:54:12 | 2020-02-08 11:27:46 |  
±-----------±------------±---------±----------±-------±--------------------±--------------------+  
需要注意，有些情况下视图是不可更新的：  
1、查询语句中包含聚合函数（sum、min、max、count等）、distinct、group by、having、union或者union all。  
很容易理解，视图对应的SQL语句中如果包含聚合函数，那么视图中的这一列并不对应任何表中的列，它只是一个动态计算出的值，自然不能更新这样的视图。通过一个例子来看一下：  
*create view test\_customer\_list(`customer_id`, `payment_count`) as  
select  
customer\_id, count(customer\_id)  
from  
payment  
group by  
customer\_id;*

*update sakila.test\_customer\_list set payment\_count=10 where customer\_id=1;*  
ERROR 1288 (HY000): The target table test\_customer\_list of the UPDATE is not updatable  
2、常量视图  
3、from一个不可更新的视图。例如用上述视图test\_customer\_list再创建一个视图：  
*create view test\_customer\_list\_2 as select \* from test\_customer\_list;*

*update sakila.test\_customer\_list\_2 set payment\_count=10 where customer\_id=1;*  
ERROR 1288 (HY000): The target table test\_customer\_list\_2 of the UPDATE is not updatable  
4、select中包含子查询  
5、对单表查询创建的视图，视图没有包含主键  
6、对于多层视图，创建视图时的with \[cascaded | local\] check option也会影响视图是否可以更新。  
***local**：满足本视图条件即可更新*  
以上文的视图payment\_list为例，再对它创建一个视图：  
*create view sakila.payment\_list2 as select \* from sakila.payment\_list where amount < 10 with local check option;*  
尝试更新视图payment\_list2，使payment\_id=5对应记录的amount=10。这显然不满足本表的条件，更新不会生效：  
*update sakila.payment\_list2 set amount=10 where payment\_id=5;*  
ERROR 1369 (HY000): CHECK OPTION failed ‘sakila.payment\_list2’  
再使payment\_id=5对应记录的amount=5，这虽然不满足payment\_list的条件，但由于payment\_list2使用的是local选项，更新会生效：  
*update sakila.payment\_list2 set amount=5 where payment\_id=5;*  
Query OK, 1 row affected (0.01 sec)  
Rows matched: 1 Changed: 1 Warnings: 0  
***cascaded**：必须满足所有相关视图的条件才可更新*  
*create view sakila.payment\_list3 as select \* from sakila.payment\_list where amount < 10 with cascaded check option;*  
尝试更新视图payment\_list3，使payment\_id=3对应记录的amount=3。这显然不满足payment\_list的条件，更新不会生效：  
*update sakila.payment\_list3 set amount=5 where payment\_id=3;*  
ERROR 1369 (HY000): CHECK OPTION failed ‘sakila.payment\_list3’

另外，还需要注意的是，由于视图只是一张虚拟表，视图中的数据行只是对查询语句结果集的映射，利用视图更新数据本质上是将数据写回表中，但是在有的情况下，直接写回可能会对表造成预期外的影响，举个例子，先将上面例子中的payment\_list改写一下，在原有的基础上还要查询customer的名字：  
*alter view payment\_list(`payment_id`, `customer_id`, `first_name`, `amount`) as  
select  
payment.payment\_id, payment.customer\_id, customer.first\_name, payment.amount  
from  
sakila.payment  
join  
sakila.customer  
on payment.customer\_id=customer.customer\_id  
where  
amount > 5;*  
对于这个视图，我们可以试想一下，视图中payment\_id、customer\_id、amount这几列来自于payment表，而first\_name这一列来自于customer表，它们之间的关联条件是customer\_id。现在如果我们想更新视图，使payment\_id=5的那一行数据中的first\_name为‘Jack’：  
*update payment\_list set first\_name=‘Jack’ where payment\_id=5;*  
如果这条更新语句生效的话，会发生什么——可以查到payment\_id=5这一行数据对应的customer\_id为1，于是customer表中的customer\_id=1所对应的那一行的first\_name列将被改为‘Jack’；payment表中，还有很多customer\_id=1的记录，这一改动将导致所有的这些记录对应的付款人名字都被改为‘Jack’。这显然不是我们的初衷，对于表操作，我们需要做的其实是修改payment\_id=5所对应记录的customer\_id。因此在对视图进行更新时，一定要慎重。

### 删除视图 ###

用户可以一次删除多个视图，语句和删除表类似。用户必须拥有drop权限。  
drop view \[if exists\] `view_name` \[, `view_name`\] …;  
删除视图payment\_list2、payment\_list3、test\_customer\_list：  
drop view `payment_list2`, `payment_list3`, `test_customer_list`;

### 查看视图 ###

#### 查看视图基本信息 ####

使用  
desc `view_name`;  
或者是  
show table status \[from `db_name`\] \[like ‘pattern’\];

#### 查看视图创建语句 ####

show create view `view_name`;

#### information\_schema ####

所有数据库的视图信息都存放在information\_schema库的views表中，可以直接查询：  
select \* from `information_schema.views`;

## 视图的优势 ##

通过上文的例子，很容易总结出视图相对于普通的表具有如下优势：  
1、简单：用户无需关心详细的表结构、关联条件和筛选条件，对用户而言，视图就是一个已经做好了筛选的结果集；  
2、安全：表无法将用户的访问权限限制到某一行某一列，而对视图而言，用户只能访问他们被允许访问的结果集；  
3、数据独立：由视图作为用户和实际查询的表之间的桥梁，视图相当于编程中所说的接口层，为用户提供封装好的统一语句，使表结构的变化几乎不对用户造成影响，即实现表结构对一般用户透明。