创建视图需要有CREATE VIEW的权限,并且对于查询设计的列有SELECT权限。如果使用CRESTE 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、GROUPBY、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[CASEADED | LOCAL] CHECK OPTION决定了是否允许更新数据使记录不再满足视图的条件。这个选项与Oracle数据库中的选项是类似的,其中:
- LOCAL是只要满足本视图的条件就可以更新;
- CASCADED则是必须满足所有针对该视图的所有视图的条件才可以更新。
如果没有明确的LOCAL还是CASCADED,则默认是CASEADED。
例如,对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 的条件,所以更新操作提示错误退出。
搜索微信公众号:TestingStudio霍格沃兹的干货都很硬核