MySQL视图使用动态SQL mysql中的视图

1 什么是视图

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

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

简单：使⽤视图的⽤户完全不需要关⼼后⾯对应的表的结构、关联条件和筛选条件，对⽤户来说已经是过滤好 的复合条件的结果集。

安全：使⽤视图的⽤户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个⾏某个列，但是 通过视图就可以简单地实现。

数据独⽴：⼀旦视图的结构确定了，可以屏蔽表结构变化对⽤户的影响，源表增加列对视图没有影响；源表修 改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2 视图操作

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

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 ;

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的条件，所以更新操作提⽰错误退出。

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)

2.3 查看视图

使⽤SHOW TABLES命令的时候不仅显⽰表的名字，同
时也会显⽰视图的名字，⽽不存在单独显⽰视图的SHOW VIEWS命令。 

同样，在使⽤SHOW TABLE STATUS命令的时候，不但可以显⽰表的信息，同时 也可以显⽰视图的信息。所以，可以通过下⾯的命令显⽰视图的信息：

如果需要查询某个视图的定义，可以使⽤SHOW CREATE VIEW命令进⾏查看：