【Mysql第十二期 视图】

1. 常见的数据库对象

2. 视图概述

2.1 为什么使用视图？

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查
询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的
价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他
人的查询视图中则不提供这个字段。
刚才讲的只是视图的一个使用场景，实际上视图还有很多作用。最后，我们总结视图的优点。

2.2 视图的理解

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

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

3.大白话理解

百度百科定义了什么是视图，但是对缺乏相关知识的人可能还是难以理解或者只有一个比较抽象的概念，笔者举个例子来解释下什么是视图。

朕想要了解皇宫的国库的相关情况，想知道酒窖有什么酒，剩多少，窖藏多少年，于是派最信任的高公公去清点，高公公去国库清点后报给了朕；朕又想知道藏书情况，于是又派高公公去清点并回来报告给朕，又想知道金银珠宝如何，又派高公公清点。。。过一段时间又想知道藏书情况，高公公还得重新再去清点，皇上问一次，高公公就得跑一次路。
后来皇上觉得高公公不容易，就成立了国库管理部门，小邓子负责酒窖，小卓子负责藏书，而小六子负责金库的清点。。。后来皇上每次想了解国库就直接问话负责人，负责人就按照职责要求进行汇报。

和数据库相对应，每次进行查询工作，都需要编写查询代码进行查询；而视图的作用就是不必每次都重新编写查询的SQL代码，而是通过视图直接查询即可。因此：

• 在 CREATE VIEW 语句中嵌入子查询

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

CREATE VIEW 视图名称
AS 查询语句

3.1 创建单表视图

举例：

CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

查询视图：

ELECT *
FROM salvu80;

举例：

CREATE VIEW emp_year_salary (ename,year_salary)
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;

举例：

CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER, last_name NAME,salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

说明1：实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形
成一张虚拟表。
说明2：在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字
段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

3.2 创建多表联合视图

举例：

CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
CREATE VIEW emp_dept
AS
SELECT ename,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
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;

• 利用视图对数据进行格式化

我们经常需要输出某个格式的内容，比如我们想输出员工姓名和对应的部门名，对应格式为
emp_name(department_name)，就可以使用视图来完成数据格式化的操作：

CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id

4. 查看视图

语法1：查看数据库的表对象、视图对象

SHOW TABLES;

语法2：查看视图的结构

DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

5. 更新视图的数据

5.1 一般情况

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的
数据发生变化时，数据表中的数据也会发生变化，反之亦然。
举例：UPDATE操作

mysql> SELECT ename,tel FROM emp_tel WHERE ename = '孙洪亮';
+---------+-------------+
| ename | tel |
+---------+-------------+
| 孙洪亮 | 137098765 |
+---------+-------------+
1 row in set (0.01 sec)
mysql> UPDATE emp_tel SET tel = '137091234' WHERE ename = '孙洪亮';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT ename,tel FROM emp_tel WHERE ename = '孙洪亮';
+---------+-------------+
| ename | tel |
+---------+-------------+
| 孙洪亮 | 137091234 |
+---------+-------------+
1 row in set (0.00 sec)
mysql> SELECT ename,tel FROM t_employee WHERE ename = '孙洪亮';
+---------+-------------+
| ename | tel |
+---------+-------------+
| 孙洪亮 | 137091234 |
+---------+-------------+
1 row in set (0.00 sec)

举例：DELETE操作

mysql> SELECT ename,tel FROM emp_tel WHERE ename = '孙洪亮';
+---------+-------------+
| ename | tel |
+---------+-------------+
| 孙洪亮 | 137091234 |
+---------+-------------+
1 row in set (0.00 sec)
mysql> DELETE FROM emp_tel WHERE ename = '孙洪亮';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT ename,tel FROM emp_tel WHERE ename = '孙洪亮';
Empty set (0.00 sec)
mysql> SELECT ename,tel FROM t_employee WHERE ename = '孙洪亮';
Empty set (0.00 sec)

5.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；
• 视图定义基于一个 不可更新视图 ；
• 常量视图。

举例：

mysql> CREATE OR REPLACE VIEW emp_dept
-> (ename,salary,birthday,tel,email,hiredate,dname)
-> AS SELECT ename,salary,birthday,tel,email,hiredate,dname
-> FROM t_employee INNER JOIN t_department
-> ON t_employee.did = t_department.did ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO emp_dept(ename,salary,birthday,tel,email,hiredate,dname)
-> VALUES('张三',15000,'1995-01-08','182015876',
-> 'zs@atguigu.com','2022-02-14','新部门');
#ERROR 1393 (HY000): Can not modify more than one base table through a join view
'atguigu_chapter9.emp_dept'

从上面的SQL执行结果可以看出，在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持更新 操作。

6. 修改、删除视图

6.1 修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

说明：CREATE VIEW 子句中各列的别名应和子查询中各列相对应。

方式2：ALTER VIEW
修改视图的语法是：

ALTER VIEW 视图名称
AS
查询语句

6.2 删除视图

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

DROP VIEW IF EXISTS 视图名称;

举例：DROP VIEW empvu80;

说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这 样的视图c需要手动删除或修改，否则影响使用。

7. 总结