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,...]