视图

视图是一种虚拟表，本身不存储数据，占用很少的内存空间，可以理解为存储查询语句的对象。本章介绍视图的概念，如何创建视图，查看、更新视图的数据，以及修改和删除视图本身。

代码和笔记存储在我的 Github 仓库 【持续更新中，建议 star ！】

1 视图概述

1.1 数据库对象

对象 描述
表 TABLE 表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典 就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，只可查看，不建议修改
约束 CONSTRAINT 执行数据校验的规则，用于保证数据完整性的规则
视图 VIEW 一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引 INDEX 用于提高查询性能，相当于书的目录
存储过程 PROCEDURE 用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数 FUNCTION 用于完成一次特定的计算，具有一个返回值
触发器 TRIGGER 相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

1.2 视图

视图：是一种 虚拟表 ，本身是 不具有数据的 ，占用很少的内存空间。视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

  • 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
  • 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
  • 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。

2 创建视图

1
2
3
4
5
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
  • OR REPLACE ：如果视图已存在，则替换它
  • ALGORITHM ：指定 MySQL 处理视图的算法
  • 字段列表（可选）：为视图的列指定自定义名称
  • WITH CHECK OPTION ：确保通过视图修改的数据符合视图定义的条件
1
2
CREATE VIEW 视图名称
AS 查询语句;

2.1 创建单表视图

例如：创建视图，查找 employees 表中 department_id = 80 的 employee_id, last_name, salary 字段信息。这里相当于存储了一个 SELECT 查询语句，在后面调用时，自动展示视图。

1
2
3
4
5
CREATE VIEW emp_dept_id80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

使用 SELECT 语句查看视图名称 emp_dept_id80 的内容：

1
2
SELECT *
FROM emp_dept_id80;

再例如：指定字段名 (emp_name, year_salary) 即为从表 employees 取出的数据进行列名重命名。

1
2
3
4
CREATE VIEW emp_year_salary (emp_name, year_salary)
AS
SELECT last_name, salary * 12 * (1 + IFNULL(commission_pct, 0))
FROM employees;

最后会展示新的列名 emp_name 代表 last_name 而 year_salary 代表 salary * 12 * (1 + IFNULL(commission_pct, 0))

1
2
3
4
5
6
7
8
SELECT *
FROM emp_year_salary;
+-------------+-------------+
| emp_name    | year_salary |  -- 新的列名
+-------------+-------------+
| King        |   288000.00 |
| Kochhar     |   204000.00 |
...

第一个语句，相当于在 SQL 中封装了视图 VIEW 。这样再次调用时，就会基于 SQL 语句的结果形成一张虚拟表。

2.2 创建多表联合视图

例如：

1
2
3
4
5
6
CREATE VIEW emp_dept_name
AS
SELECT e.employee_id emp_id, e.last_name emp_name, d.department_name dept_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;
1
2
3
4
5
6
7
CREATE VIEW dept_sal (dept_name, min_sal, max_sal, avg_sal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e,
     departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

2.3 利用视图进行格式化

例如：输出员工姓名和对应的部门名，对应格式为 "emp_name(department_name)" ，就可以使用视图来完成数据格式化的操作

1
2
3
4
5
6
CREATE VIEW employee_department_name
AS
SELECT CONCAT(e.last_name, '(', d.department_name, ')') AS emp_dept
FROM employees e
         JOIN departments d
WHERE e.department_id = d.department_id;
1
2
3
4
5
6
7
8
9
SELECT *
FROM employee_department_name;
+-------------------------+
| emp_dept                |
+-------------------------+
| Whalen(Administration)  |
| Hartstein(Marketing)    |
| Fay(Marketing)          |
...

2.4 基于视图创建视图

当我们创建好一张视图之后，还可以在它的基础上继续创建视图。例如：联合 emp_dept_name 视图和 emp_year_salary 视图查询员工姓名、部门名称、年薪信息创建 emp_dept_year_salary 视图。

1
2
3
4
5
6
CREATE VIEW emp_dept_year_salary
AS
SELECT edn.emp_name, edn.dept_name, eys.year_salary
FROM emp_dept_name AS edn
         INNER JOIN emp_year_salary AS eys
                    ON edn.emp_name = eys.emp_name;
1
2
SELECT *
FROM emp_dept_year_salary;

3 查看视图

1. 查看数据库的表对象、视图对象

1
SHOW TABLES; -- 视图和表都会展示

2. 查看视图的结构

1
DESC 视图名称;

例如：

1
2
3
4
5
6
7
8
9
10
11
DESC emp_dept_year_salary;
DESCRIBE emp_dept_year_salary;

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| emp_name    | varchar(25)  | NO   |     | NULL    |       |
| dept_name   | varchar(30)  | NO   |     | NULL    |       |
| year_salary | double(19,2) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3. 查看视图的属性信息

1
SHOW TABLE STATUS LIKE "视图名称";  -- 一定要用 "" 包裹

例如：

1
2
SHOW TABLE STATUS LIKE "emp_dept_year_salary";
-- 其中 Comment 为 VIEW -> 说明为视图

4. 查看视图的详细定义信息

1
SHOW CREATE VIEW 视图名称;

例如：

1
SHOW CREATE VIEW emp_dept_year_salary;

4 更新视图的数据

使用 INSERT UPDATE 和 DELETE 语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。（与正常对表操作的使用方法相同）

4.1 可更新的视图

为展示例子，先创建表和视图：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. 创建表
CREATE TABLE view_test
(
    name VARCHAR(10),
    age  INT,
    tel  VARCHAR(20)
);

-- 2. INSERT: 插入一些数据后

-- 3. 创建视图
CREATE VIEW view_changed
AS
SELECT *
FROM view_test
WHERE age < 30;

此时的表结果为 view_test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT *
    -> FROM view_test;
+--------+------+-------------+
| name   | age  | tel         |
+--------+------+-------------+
| Mike   |   30 | 12345678911 |
| Sarah  |   25 | 12345678912 |
| John   |   45 | 12345678913 |
| Emily  |   18 | 12345678914 |
| David  |   62 | 12345678915 |
| Lisa   |   55 | 12345678916 |
| Tom    |   12 | 12345678917 |
| Anna   |    8 | 12345678918 |
| Robert |   70 | 12345678919 |
| Grace  |   33 | 12345678920 |
+--------+------+-------------+
10 rows in set (0.01 sec)

此时的视图结果为 view_changed

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT *
    -> FROM view_changed;
+-------+------+-------------+
| name  | age  | tel         |
+-------+------+-------------+
| Sarah |   25 | 12345678912 |
| Emily |   18 | 12345678914 |
| Tom   |   12 | 12345678917 |
| Anna  |    8 | 12345678918 |
+-------+------+-------------+
4 rows in set (0.00 sec)

1. UPDATE 操作

1
2
3
UPDATE view_changed
SET tel = '0000000'
WHERE name = 'Sarah';

此时表 view_test 和视图 view_changed 的 Sarah 行数据均发生改变。

2. INSERT 操作

1
2
INSERT view_changed
VALUES ('TEST', 0, '???');

此时表 view_test 和视图 view_changed 增加了一行 TEST 的数据。

3. DELETE 操作

1
2
3
DELETE
FROM view_changed
WHERE name = 'TEST';

此时表 view_test 和视图 view_changed 的 TEST 均被删除。

4.2 不可更新的视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。

另外当视图定义出现如下情况时，视图不支持更新操作：

  • 在定义视图的时候指定了 ALGORITHM = TEMPTABLE，视图将不支持 INSERT 和 DELETE 操作；

  • 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持 INSERT 操作；

  • 在定义视图的 SELECT 语句中使用了 JOIN ，视图将不支持 INSERT 和 DELETE 操作；

  • 在定义视图的 SELECT 语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT ，也不支持 UPDATE 使用了数学表达式、子查询的字段值；

  • 在定义视图的 SELECT 语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、UNION 等，视图将不支持 INSERT、UPDATE、DELETE；

  • 在定义视图的 SELECT 语句中包含了子查询，而子查询中引用了 FROM 后面的表，视图将不支持INSERT、UPDATE、DELETE；

  • 视图定义基于一个 不可更新视图 ，例如常量视图。

【注意】即使视图可以进行更新、修改和删除数据，也不建议使用。视图的本意就是为了查询和展示，如果要对数据进行修改，一般对数据表进行操作，而非视图！

5 修改和删除视图

5.1 修改视图

1. 使用 CREATE OR REPLACE VIEW 子句修改视图

相当于检查是否存在，存在则替换原视图中的一些设置。

1
2
3
4
5
CREATE OR REPLACE VIEW view_changed
AS  -- 修改条件
SELECT *
FROM view_test
WHERE age > 30;

2. 使用 ALTER VIEW 修改

1
2
3
ALTER VIEW 视图名称
	AS
		查询语句;

例如：

1
2
3
4
5
ALTER VIEW view_changed
    AS
        SELECT *
        FROM view_test
        WHERE age > 30;

5.2 删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

1
DROP VIEW IF EXISTS 视图名称 [视图名称1, 视图名称2, ...];

例如：

1
DROP VIEW IF EXISTS view_changed;

【注意】基于视图创建的视图，如果被依赖的视图被删除，则基于其创建的视图功能丧失，也需要被删除。