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检查选项,因此,不仅会针对它自己的检查对插入项进行测试,也会针对基本视图的检查对插入项进行测试。
在下面的语句中,介绍了这些差异:

这里写图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值