MySQL 视图简介

概述

数据库中关于数据的查询有时非常复杂，例如表连接、子查询等，这种查询编写难度大，很容易出错。另外，在具体操作表时，有时候要求只能操作部分字段。

为了提高复杂 SQL 语句的复用性和表的操作的安全性，MySQL 提供了视图特性。所谓视图，本质上是一种虚拟表，同样包含一系列带有名称的列和行数据。行和列的数据来自自定义视图的查询所引用的基本表，并在具体引用视图时动态生成

视图的特点如下：

• 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系
• 视图是由基本表（实表）产生的表（虚表）
• 视图的建立和删除不影响基本表
• 对视图内容的更新（增删改）直接影响基本表
• 当视图来自多个基本表，不允许添加和删除数据

创建视图

1. 创建视图的语法形式

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

• CREATE 表示创建新的视图，REPLACE 表示替换已经创建的视图
• ALGORITHM 表示视图选择的算法，取值有三种：
  • UNDEFINED：MySQL 自动选择算法
  • MERGE：将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分
  • TEMPLATE：表示将视图的结果放入临时表，然后用临时表来执行语句
• view_name 表示视图的名称
• column_list 为属性列
• SELECT statement 表示 SELECT 语句
• 参数 [WITH [CASCADED | LOCAL] CHECK OPTION] 表示视图在更新时保证在视图的权限范围内，CASCADED 与 LOCAL 为可选参数：
  • CASCADED 为默认值，表示更新视图时要满足所有相关视图和表的条件
  • LOCAL 表示更新视图时满足该视图本身定义的条件即可

该语句要求具有针对视图的 CREATE VIEW 权限，以及针对由 SELECT 语句选择的每一列上的某些权限。对于在 SELECT 语句中其他地方使用的列，必须具有 SELECT 权限，如果还有 OR REPLACE 子句，就必须在视图上具有 DROP 权限

2. 在单表创建视图

示例：在数据库 company 中，由员工表 t_employee 创建出隐藏工资字段 salary 的视图 view_selectemployee

CREATE VIEW view_selectemployee AS
SELECT id,name,gender,age,deptno FROM t_employee;

查询视图

SELECT * FROM view_selectemployee;

3. 在多表创建视图

示例：在数据库 company 中，由部门表 t_dept 和员工表 t_employee 创建一个名为 view_dept_employee 的视图

CREATE ALGORITHM=MERGE VIEW
view_dept_employee(name,dept,gender,age,loc)
AScSELECT iname,t_dept.deptname,gender,age,t_dept.location
FROM t_employee, t_dept WHERE t_employee.deptno = t_dept.deptno
WITH LOCAL CHECK OPTION;

查看视图

使用 DESCRIBE | DESC 语句查看视图基本信息，因为视图也是一张表，只不过比较特殊

DESCRIBE | DESC viewname

在 MySQL 中，所有视图的定义都存在数据库 information_schema 中的表 views 中，查询 views 表可以得到数据库中所有视图的详细信息

SELECT * FROM information_schema.views
WHERE table_name = 'viewname' \G

修改视图

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致

在 MySQL 中，CREATE OR REPLACE VIEW 语句可以用来修改视图。当视图已经存在，对视图进行修改，否则创建视图

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

也可以使用 ALTER 语句修改视图，其语法中的关键字和参数都与创建视图是一样的

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

更新视图

更新视图是指通过视图来插入、更新和删除表中的数据。可以使用 SQL 语句更新视图，达到更新基本表数据。反过来，更新基本表的数据，也会更新视图的数据

但实际上，并不是所有视图都可以更新，以下几种情况是不能更新视图的：

• 视图中包含 SUM()、COUNT()、MAX() 和 MIN() 函数
• 视图中包含 UNION、UNION ALL、DISTINCT、GROUP BY 和 HAVING 等关键字
• 常量视图，例如：CREATE VIEW view_1 AS SELECT 'Rebecca' AS name;
• 包含子查询的视图
• 由不可更新的视图导出的视图
• 创建视图时，ALGORITHM 为 TEMPTABLE 类型
• 视图对应的列存在没有默认值的列，而且该列没有包含在视图里
• 设置了 [WITH [CASCADED | LOCAL] CHECK OPTION] 参数，需要符合对应条件才能更新

删除视图

删除视图是指删除数据库中已存在的视图，不会删除数据

在 MySQL 中，可以使用 DROP VIEW 语句来删除视图，但是用户必须拥有 DROP 权限

DROP VIEW viewname [viewname1,viewname2,...]