【4.0】 MySQL视图

说明：偷懒了。。。。。。

 

MySQL 从5.0.1 版本开始提供视图功能，本章将对MySQL 中的视图进行介绍。

11.1 什么是视图

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并
不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时
动态生成的。

视图相对于普通的表的优势主要包括以下几项。

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

11.2 视图操作

视图的操作包括创建或者修改视图、删除视图，以及查看视图定义。

11.2.1 创建或者修改视图
创建视图需要有CREATE VIEW 的权限，并且对于查询涉及的列有SELECT 权限。如果使用
CREATE OR REPLACE 或者ALTER 修改视图，那么还需要该视图的DROP 权限。

创建视图的语法为：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

 

修改视图的语法为：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

　　

例如,要创建了视图staff_list_view,可以使用以下命令：

mysql> CREATE OR REPLACE VIEW staff_list_view AS
-> SELECT s.staff_id,s.first_name,s.last_name,a.address
-> FROM staff AS s,address AS a
-> where s.address_id = a.address_id ;
Query OK, 0 rows affected (0.00 sec)

 

MySQL 视图的定义有一些限制，例如，在FROM 关键字后面不能包含子查询，这和其他数
据库是不同的，如果视图是从其他数据库迁移过来的，那么可能需要因此做一些改动，可以
将子查询的内容先定义成一个视图，然后对该视图再创建视图就可以实现类似的功能了。
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。

 包含以下关键字的SQL 语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP
BY、HAVING、UNION 或者UNION ALL。
 常量视图。
 SELECT 中包含子查询。
 JION。
 FROM 一个不能更新的视图。
 WHERE 字句的子查询引用了FROM 字句中的表。

例如，以下的视图都是不可更新的：
--包含聚合函数
mysql> create or replace view payment_sum as
-> select staff_id,sum(amount) from payment group by staff_id;
Query OK, 0 rows affected (0.00 sec)
--常量视图
mysql> create or replace view pi as select 3.1415926 as pi;
Query OK, 0 rows affected (0.00 sec)
--select 中包含子查询
mysql> create view city_view as
-> select (select city from city where city_id = 1) ;
Query OK, 0 rows affected (0.00 sec)

WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条
件。这个选项与Oracle 数据库中的选项是类似的，其中：

 LOCAL 是只要满足本视图的条件就可以更新；
 CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新。
如果没有明确是LOCAL 还是CASCADED，则默认是CASCADED。　

例如，对payment 表创建两层视图，并进行更新操作：

mysql> create or replace view payment_view as
-> select payment_id,amount from payment
-> where amount < 10 WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)


mysql>
mysql> create or replace view payment_view1 as
-> select payment_id,amount from payment_view
-> where amount > 5 WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)


mysql>
mysql> create or replace view payment_view2 as
-> select payment_id,amount from payment_view


-> where amount > 5 WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from payment_view1 limit 1;
+------------+--------+
?
| payment_id | amount |
+------------+--------+
| 3 | 5.99 |
+------------+--------+
1 row in set (0.00 sec)


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


mysql> update payment_view2 set amount=10
-> where payment_id = 3;
ERROR 1369 (HY000): CHECK OPTION failed 'sakila.payment_view2'

 

从测试结果可以看出，payment_view1 是WITH LOCAL CHECK OPTION 的，所以只要满足本视
图的条件就可以更新，但是payment_view2 是WITH CASCADED CHECK OPTION 的，必须满足
针对该视图的所有视图才可以更新，因为更新后记录不再满足payment_view 的条件，所以
更新操作提示错误退出。

11.2.2 删除视图

用户可以一次删除一个或者多个视图，前提是必须有该视图的DROP 权限。
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]
例如，删除staff_list 视图：
mysql> drop view staff_list;
Query OK, 0 rows affected (0.00 sec)

11.2.3 查看视图

从MySQL 5.1 版本开始，使用SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示
视图的名字，而不存在单独显示视图的SHOW VIEWS 命令。
mysql> use sakila
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
……

| staff |
| staff_list |
| store |
+----------------------------+
26 rows in set (0.00 sec)
同样，在使用SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视
图的信息。所以，可以通过下面的命令显示视图的信息：
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

下面演示的是查看staff_list 视图信息的操作：
mysql> show table status like 'staff_list' \G
*************************** 1. row ***************************
Name: staff_list
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.01 sec)

如果需要查询某个视图的定义，可以使用SHOW CREATE VIEW 命令进行查看：
mysql> show create view staff_list \G
*************************** 1. row ***************************
View: staff_list
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `staff_list` AS select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8'
',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip
code`,`a`.`phone` AS `phone`,`city`.`city` AS `city`,`country`.`country` AS
`country`,`s`.`store_id` AS `SID` from (((`staff` `s` join `address` `a` on((`s`.`address_id`
= `a`.`address_id`))) join `city` on((`a`.`city_id` = `city`.`city_id`))) join `country`
on((`city`.`country_id` = `country`.`country_id`)))
1 row in set (0.00 sec)
最后，通过查看系统表information_schema.views 也可以查看视图的相关信息：

mysql> select * from views where table_name = 'staff_list' \G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: sakila
TABLE_NAME: staff_list
VIEW_DEFINITION: select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8'
',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip
code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS
`country`,`s`.`store_id` AS `SID` from (((`sakila`.`staff` `s` join `sakila`.`address` `a`
on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` =
`sakila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` =
`sakila`.`country`.`country_id`)))
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
1 row in set (0.00 sec)

11.3 小结

本章主要介绍了MySQL 提供的视图创建、维护等相关语法。如果从不支持视图的旧版本升
级到提供视图功能的新版本后，要想使用视图，则需要升级授权表，使之包含与视图有关的
权限。相关的升级步骤，可以参见27.4 节MySQL 升级内容。