MySQL视图
来源:互联网 发布:java 软引用 弱引用 编辑:程序博客网 时间:2024/05/22 16:06
一、创建修改及删除
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检查选项,因此,不仅会针对它自己的检查对插入项进行测试,也会针对基本视图的检查对插入项进行测试。
在下面的语句中,介绍了这些差异: