MySQL视图简介

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL视图。

Table of Contents

• 一.视图语法
• 二.视图案例
• 1.视图能够简化用户内的操作
• 2.视图使用户能以多种角度看待同一数据
• 3.视图对重构数据库提供了一定程度的逻辑独立性
• 4.视图能够对机容密数据提供安全保护

前言:
视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。
即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。
视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。
引进VIEW的优点有：
(1)视图能够简化用户内的操作。
(2)视图使用户能以多种角度看待同一数据。
(3)视图对重构数据库提供了一定程度的逻辑独立性。
(4)视图能够对机容密数据提供安全保护。

一.视图语法

视图创建语法:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

视图修改语法:

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

视图删除语法:

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

语法解读：
1）OR REPLACE: 如已存在同名的视图，进行替换

2）ALGORITHM : 表示视图选择算法,一般无需指定，使用默认值即可。

3）DEFINER: 视图的定义用户，如不指定，则为创建用户。

4）SQL SECURITY：SQL安全性。

5）select_statement：查询的SQL语句，可以从基表也可以从视图出。

6）WITH CHECK OPTION：表示视图在更新时保证约束，默认是CASCADED。

二.视图案例

1.视图能够简化用户内的操作

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

需求:需要查询员工表该部门最高薪水以及最低薪水以及全公司最高最低薪水

with tmp1 as
(
select d1.dname,
			 max(e1.sal) sal_dept_max,
			 min(e1.sal) sal_dept_min
from emp e1
left join dept d1
on e1.deptno = d1.deptno
group by d1.dname
),
tmp2 as
(
select max(e1.sal) sal_com_max,
			 min(e1.sal) sal_com_min
from emp e1
left join dept d1
on e1.deptno = d1.deptno
)
select  dname,     -- 部门名称
        sal_dept_max,  -- 部门最高工资
				sal_dept_min,  -- 部门最低工资
				(select sal_com_max from tmp2) sal_com_max, -- 公司最高工资
				(select sal_com_min from tmp2) sal_com_min  -- 公司最低工资
from tmp1

现在，我已经写了上述的一个复杂的sql，实现了需求。
过了一段时间后，如果业务再次需要这个数据，我又得重新花时间写一遍。
如果此时我创建了视图，将复杂sql语句保存为视图，下次我直接查询视图即可。

mysql> create view v1 as with tmp1 as
    -> (
    -> select d1.dname,
    ->                   max(e1.sal) sal_dept_max,
    ->                   min(e1.sal) sal_dept_min
    -> from emp e1
    -> left join dept d1
    -> on e1.deptno = d1.deptno
    -> group by d1.dname
    -> ),
    -> tmp2 as
    -> (
    -> select max(e1.sal) sal_com_max,
    ->                   min(e1.sal) sal_com_min
    -> from emp e1
    -> left join dept d1
    -> on e1.deptno = d1.deptno
    -> )
    -> select  dname,     -- 部门名称
    ->         sal_dept_max,  -- 部门最高工资
    ->                          sal_dept_min,  -- 部门最低工资
    ->                          (select sal_com_max from tmp2) sal_com_max, -- 公司最高工资
    ->                          (select sal_com_min from tmp2) sal_com_min  -- 公司最低工资
    -> from tmp1;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> select * from v1;
+------------+--------------+--------------+-------------+-------------+
| dname      | sal_dept_max | sal_dept_min | sal_com_max | sal_com_min |
+------------+--------------+--------------+-------------+-------------+
| RESEARCH   |      3000.00 |       800.00 |     5000.00 |      800.00 |
| SALES      |      2850.00 |       950.00 |     5000.00 |      800.00 |
| ACCOUNTING |      5000.00 |      1300.00 |     5000.00 |      800.00 |
+------------+--------------+--------------+-------------+-------------+
3 rows in set (0.01 sec)

2.视图使用户能以多种角度看待同一数据

员工表员工的工资存的是金额，假设领导需要看的数据是:
sal < 1000 低工资
1000<= sal < 2000 中等工资
sal >= 2000 高工资
此时肯定不能修改底层数据，只能自己再做一层逻辑。
此时也可以创建一个视图，将emp列的sal定义改为上述需求

mysql> create view v2 as
    -> select e.empno,e.deptno,e.hiredate,       case when e.sal < 1000 then '低工资'
    ->                        when e.sal >= 1000 and e.sal < 2000 then '中等工资'
    ->                        when e.sal >= 2000 then '高工资'
    ->                        else '' end  as sal
    -> from emp e;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v2;
+-------+--------+------------+--------------+
| empno | deptno | hiredate   | sal          |
+-------+--------+------------+--------------+
|  7369 |     20 | 1980-12-17 | 低工资       |
|  7499 |     30 | 1981-02-20 | 中等工资     |
|  7521 |     30 | 1981-02-22 | 中等工资     |
|  7566 |     20 | 1981-04-02 | 高工资       |
|  7654 |     30 | 1981-09-28 | 中等工资     |
|  7698 |     30 | 1981-05-01 | 高工资       |
|  7782 |     10 | 1981-06-09 | 高工资       |
|  7788 |     20 | 1987-06-13 | 高工资       |
|  7839 |     10 | 1981-11-17 | 高工资       |
|  7844 |     30 | 1981-09-08 | 中等工资     |
|  7876 |     20 | 1987-06-13 | 中等工资     |
|  7900 |     30 | 1981-12-03 | 低工资       |
|  7902 |     20 | 1981-12-03 | 高工资       |
|  7934 |     10 | 1982-01-23 | 中等工资     |
+-------+--------+------------+--------------+
14 rows in set (0.00 sec)

3.视图对重构数据库提供了一定程度的逻辑独立性

承接第2步的需求，假设此时，因为数据重构了，之前存的工资单位是美元，现在变成美分了，表数据也被同步更改了。
此时给老板呈现的多张报表依旧使用了v2这个视图，此时我们只需要修改v2这个视图即可，无需一个一个的报表的修改。

mysql> alter table emp modify sal decimal(10,2);
Query OK, 14 rows affected (0.21 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> update emp set sal = sal*100 where 1=1;
Query OK, 14 rows affected (0.01 sec)
Rows matched: 14  Changed: 14  Warnings: 0
mysql> create or replace view v2 as
    -> select e.empno,e.deptno,e.hiredate,       case when e.sal < 100000 then '低工资'
    ->                        when e.sal >= 100000 and e.sal < 200000 then '中等工资'
    ->                        when e.sal >= 200000 then '高工资'
    ->                        else '' end  as sal
    -> from emp e;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> select * from v2;
+-------+--------+------------+--------------+
| empno | deptno | hiredate   | sal          |
+-------+--------+------------+--------------+
|  7369 |     20 | 1980-12-17 | 低工资       |
|  7499 |     30 | 1981-02-20 | 中等工资     |
|  7521 |     30 | 1981-02-22 | 中等工资     |
|  7566 |     20 | 1981-04-02 | 高工资       |
|  7654 |     30 | 1981-09-28 | 中等工资     |
|  7698 |     30 | 1981-05-01 | 高工资       |
|  7782 |     10 | 1981-06-09 | 高工资       |
|  7788 |     20 | 1987-06-13 | 高工资       |
|  7839 |     10 | 1981-11-17 | 高工资       |
|  7844 |     30 | 1981-09-08 | 中等工资     |
|  7876 |     20 | 1987-06-13 | 中等工资     |
|  7900 |     30 | 1981-12-03 | 低工资       |
|  7902 |     20 | 1981-12-03 | 高工资       |
|  7934 |     10 | 1982-01-23 | 中等工资     |
+-------+--------+------------+--------------+
14 rows in set (0.00 sec)

4.视图能够对机容密数据提供安全保护

员工表有一列是工资，假设此时由于业务需求，需要将员工表授权给第三方，但是不能让第三方看到员工的工资信息，此时可以创建视图，把工资md5进行加密后，再将视图授权给第三方。

mysql> create or replace view v4 as
    -> select e.empno AS empno,e.ename AS ename,e.job AS job,e.mgr AS mgr,e.hiredate AS hiredate,md5(e.sal) AS sal,e.comm AS comm,e.deptno AS deptno f
rom emp e;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v4;
+-------+--------+-----------+------+------------+----------------------------------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal                              | comm    | deptno |
+-------+--------+-----------+------+------------+----------------------------------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | ad0468d096f9a46f86de298bc4031909 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | ed5114151590ffb59b82a158b7a1ce32 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 3b53f191f0323f4f8d701383134188c8 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 1e5ec10e1bb3a1a5f06ac19672655eaf |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 3b53f191f0323f4f8d701383134188c8 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 67fe5a29d9fc8755043122764a39eab1 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | d93933c3e7913cf23186c2becb6b8378 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-06-13 | 2cc1edc04e43cf2fd631135f48bdcee2 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 6fa125b4fbf44f9496fa39eb0857f0ed |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | a164216bd04f07561e19f3df84b2c81d |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | fc414d1c217862b390e3a13aa87b8dc6 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 0308b51428b11efaa1472d1ac60c8a11 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 2cc1edc04e43cf2fd631135f48bdcee2 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | c3037fae265a75ecd02da892b2f4b758 |    NULL |     10 |
+-------+--------+-----------+------+------------+----------------------------------+---------+--------+
14 rows in set (0.00 sec)