深入浅出mysql_视图

十一 . 视图


11.1 什么是视图


视图view 是虚拟存在的表，视图并不在数据库中实际存在，行和列的数据来自定义视图总查询使用用的表。并且是在使用视图的时候动态生成的


优势：


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


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]


范例


例如,要创建了视图 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> 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> 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> 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'


修改视图


语法


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


视图的可更新性:


视图的可更新性和视图中查询的定义有关，下列情况视图不可更新
1.包含以下关键字的SQL语句：聚合函数（sum count max  min等），DISTINCT 、GROUP BY 、HAVING 、 UNION、UNION ALL；
2、常量视图：
3、SELECT 中包含子查询；
4、JION；
5、FROM 一个不能更新的视图；
6、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)


11.2.2 删除视图


用户可以一次删除一个或者多个视图，前提是必须有该视图的 DROP 权限。


语法


DROP VIEW [IF EXISTS] viewname [,viewname] [RESTRICT|CASCADE] 


范例


删除 staff_list 视图：
mysql> drop view staff_list;
Query OK, 0 rows affected (0.00 sec)


11.2.3 查看视图


 SHOW TABLES


从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。


mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
……


SHOW TABLE STATUS


语法


SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']


范例


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


如果需要查询某个视图的定义，可以使用 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`)))...........