深入浅出mysql_视图
来源:互联网 发布:传奇天下轮回每层数据 编辑:程序博客网 时间:2024/06/05 16:57
十一 . 视图
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`)))...........