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

这里写图片描述

### 创建、查询和管理 MySQL 视图的方法 #### 一、创建视图MySQL 中,可以通过 `CREATE VIEW` 语句来创建视图。以下是具体的语法: ```sql CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]; ``` - **`view_name`**: 示要创建的视图名称。 - **`select_statement`**: 是一条标准的 SQL 查询语句,用于定义视图的内容[^4]。 ##### 示例 假设有一个名为 `employees` 的,包含字段 `id`, `name`, 和 `salary`。如果想创建一个只显示员工姓名和薪水的视图,则可以这样写: ```sql CREATE VIEW employee_salary_view AS SELECT name, salary FROM employees; ``` 此命令将创建一个名为 `employee_salary_view` 的视图[^1]。 #### 二、查询视图 一旦视图被创建,就可以像对待普通一样对其进行查询操作。例如,继续上面的例子,我们可以这样查询该视图: ```sql SELECT * FROM employee_salary_view WHERE salary > 5000; ``` 这将返回所有工资超过 5000 的员工的名字及其对应的薪资情况[^2]。 #### 三、管理视图 除了创建和查询之外,还需要掌握如何更新或者删除已有的视图。 ##### 修改视图 可以直接使用带有 OR REPLACE 子句的新 CREATE VIEW 命令重新定义现有视图而不必先将其删除再重建新版本: ```sql CREATE OR REPLACE VIEW employee_salary_view AS SELECT id, name, salary FROM employees WHERE department='Sales'; ``` 这里我们不仅增加了部门筛选条件还加入了 ID 字段作为额外信息展示的一部分[^3]。 ##### 删除视图 当不再需要某个特定视图时,应该考虑移除它以保持数据库整洁有序。执行如下指令即可完成这一过程: ```sql DROP VIEW IF EXISTS employee_salary_view; ``` 这条语句会安全地尝试销毁指定名字下的任何现存实例——即如果没有找到同名对象则不会报错而是静默忽略掉请求[^3]。 --- ### 注意事项 需要注意的是,在构建新的虚拟格之前应当清楚了解哪些类型的 SELECT 结构支持转换成持久化形式供后续调用者反复利用。比如不能含有某些特殊关键字如 DISTINCT , GROUP BY 等等因为它们可能破坏了原本打算呈现出来的直观映射关系[^3]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值