MySQL视图

一、创建修改及删除

1.1 创建视图
CREATE VIEW：

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

OR REPLACE子句：替换已有的视图。
select_statement该语句可从基表或其他视图进行选择。

视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，应将名称指定为db_name.view_name。

例：创建一个视图v，该视图包含从另一个表t中选择两列并用一个表达式计算这两列的数据：

视图定义限制：

（1）SELECT语句不能包含FROM子句中的子查询。

（2）SELECT语句不能引用系统或用户变量。

（3）SELECT语句不能引用预处理语句参数。

（4）在存储子程序内，定义不能引用子程序参数或局部变量。

（5）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。

（6）在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

（7）在视图定义中命名的表必须已存在。

（8）不能将触发程序与视图关联在一起。

1.2 修改视图

ALTER VIEW

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

1.3 删除视图

DROP VIEW语法：

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

可以使用关键字IF EXISTS来防止因不存在的视图而出错，给定了该子句时，将为每个不存在的视图生成NOTE。

二、视图处理算法

ALGORITHM：MERGE、TEMPTABLE或UNDEFINED。默认是UNDEFINED

MERGE会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

TEMPTABLE视图的结果将被置于临时表中，然后使用它执行语句。

UNDEFINED，MySQL需要自己选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

MERGE的工作方式：
在该示例中，假定有1个具有下述定义的视图v_merge，

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) 
AS SELECT c1, c2 FROM t WHERE c3 > 100;

示例1：

SELECT * FROM v_merge;

MySQL以下述方式处理语句：1.v_merge成为t；2.*成为vc1、vc2，与c1、c2对应；3.给视图添加WHERE子句

最后被执行的语句为：

SELECT c1, c2 FROM t WHERE c3 > 100;

示例2：

SELECT * FROM v_merge WHERE vc1 < 100;

所得的将要执行的语句变为：

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

三、 更新和插入

3.1 更新
在UPDATE、DELETE或INSERT等语句中使用，可更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。不可更新的：
1.聚合函数（SUM()， MIN()， MAX()， OUNT()等）
2.DISTINCT
3.GROUP BY
4.HAVING
5.UNION或UNION ALL
6.位于选择列表中的子查询
7.Join
8.FROM子句中引用不可更新视图
9.WHERE子句中的子查询引用的是FROM子句中的表。
10.仅引用文字值（在该情况下，没有要更新的基表）。
11.ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

3.2 插入
可插入性：1.不得有重复的视图列名称。2.视图必须包含没有默认值的基表中的所有列。3.视图列必须是简单的列引用而不是导出列。导出列不是简单的列引用，而是从表达式导出的。

混合了简单列引用和导出列的视图是不可插入的，但是，如果仅更新非导出列，视图是可更新的。考虑下述视图：

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

该视图是不可插入，因为col2是从表达式1导出的。但是，如果更新时不更新col2，它是可更新的。

更新允许：

UPDATE v SET col1 = 0;

更新不允许，它试图更新导出列：

UPDATE v SET col2 = 0;

3.3 使用WITH CHECK OPTION子句的视图
对于可更新视图，可给定WITH CHECK OPTION子句来防止插入或更新行，除非作用在行上的select_statement中的WHERE子句为“真”。

在关于可更新视图的WITH CHECK OPTION子句中，当视图是根据另一个视图定义的时，LOCAL和CASCADED关键字决定了检查测试的范围。

LOCAL关键字对CHECK OPTION进行了限制，使其仅作用在定义的视图上;

CASCADED会对将进行评估的基表进行检查。如果未给定任一关键字，默认为CASCADED。

例：

视图v2和v3是根据另一视图v1定义的。
v2具有LOCAL检查选项，因此，仅会针对v2检查对插入项进行测试。
v3具有CASCADED检查选项，因此，不仅会针对它自己的检查对插入项进行测试，也会针对基本视图的检查对插入项进行测试。
在下面的语句中，介绍了这些差异：