MySQL笔记（四）——视图

视图

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

视图操作

视图的操作主要是创建、修改、更新、删除和查询，语句都和表的操作类似。笔记中以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)

修改

修改视图和修改表类似，使用alter view语句。
例子：修改上文的视图city_list，在原视图的基础上增加一列，查询城市对应的国家名称。
alter view city_list(city_id, city_name, country_name) as
select
city.city_id, city.city, country.country
from
sakila.city
join
sakila.country
on city.country_id=country.country_id
where
city.country_id=23;
再来查询一下：
select * from sakila.city_list limit 10;
±--------±----------±--------+
| city_id | city | country |
±--------±----------±--------+
| 46 | Baicheng | China |
| 47 | Baiyin | China |
| 80 | Binzhou | China |
| 109 | Changzhou | China |
| 136 | Datong | China |
| 139 | Daxian | China |
| 145 | Dongying | China |
| 157 | Emeishan | China |
| 159 | Enshi | China |
| 166 | Ezhou | China |
±--------±----------±--------+
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、数据独立：由视图作为用户和实际查询的表之间的桥梁，视图相当于编程中所说的接口层，为用户提供封装好的统一语句，使表结构的变化几乎不对用户造成影响，即实现表结构对一般用户透明。