mysql 视图

基本概念

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化 (增删改) 会影响到基表，基表的数据变化也会影响到视图

什么是视图？

视图是一种虚拟存在的表，并不在数据库中实际存在，对用户透明，行列数据来自定义视图的查询中使用的表，并且在使用的过程中动态生成的

视图是干什么用的？

通过视图，可以展现基表的部分数据；
视图数据来自定义视图的查询中使用的表，使用视图动态生成。
基表：用来创建视图的表叫做基表

为什么要使用视图？

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

视图的基本使用

创建视图

基本语法：create view 视图名 as select 字段名 from 表名
进阶语法：create view 视图名 as select 字段名 from 视图名2

mysql> create view myview as select deptno,loc from dept;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from myview;
+--------+----------+
| deptno | loc      |
+--------+----------+
|     10 | new york |
|     20 | dallas   |
|     30 | chicago  |
|     40 | bosion   |
+--------+----------+
4 rows in set (0.00 sec)

修改视图数据，对基表数据有影响

mysql> update myview set loc='china' where deptno=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from myview;
+--------+---------+
| deptno | loc     |
+--------+---------+
|     10 | china   |
|     20 | dallas  |
|     30 | chicago |
|     40 | bosion  |
+--------+---------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+---------+
| deptno | dname      | loc     |
+--------+------------+---------+
|     10 | accounting | china   |
|     20 | research   | dallas  |
|     30 | sales      | chicago |
|     40 | operations | bosion  |
+--------+------------+---------+
4 rows in set (0.00 sec)

修改基表数据，对视图数据也有影响

mysql> update dept set loc='new york' where deptno=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | new york |
|     20 | research   | dallas   |
|     30 | sales      | chicago  |
|     40 | operations | bosion   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from myview;
+--------+----------+
| deptno | loc      |
+--------+----------+
|     10 | new york |
|     20 | dallas   |
|     30 | chicago  |
|     40 | bosion   |
+--------+----------+
4 rows in set (0.00 sec)

修改视图

alter view 视图名 as select 字段名 from 表名;

mysql> alter view myview as select deptno,dname,loc from dept;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from myview;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | new york |
|     20 | research   | dallas   |
|     30 | sales      | chicago  |
|     40 | operations | bosion   |
+--------+------------+----------+
4 rows in set (0.00 sec)

删除视图

mysql> drop view myview;
Query OK, 0 rows affected (0.00 sec)

视图实践

查找每个部门工资最高的人的资料（子查询的案例）

mysql> select * from (select * from emp order by deptno,sal desc) temp group by deptno;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7698 | blake | manager   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

视图中不能包含子查询

//报错： 原因 视图中不能包含子查询
mysql> create view myview1 as select * from (select * from emp order by deptno,sal desc) temp group by deptno;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

视图中包含视图

create view 视图名1 as select 字段名 from 视图名2

//视图中不能包含子查询  换个思路 将子查询做成视图
mysql> create view myview2 as select * from emp order by deptno,sal desc;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from myview2;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

//create view 视图名1 as select 字段名 from 视图名2
mysql> create view myview1 as select * from myview2 temp group by deptno;
Query OK, 0 rows affected (0.04 sec)

//对试图分组时，没有返回上一个视图分组的第一条记录
//原因是，在默认情况下，回到原来的基表上进行分组处理，没有使用视图2的结果
//解决这个问题 只需设置 algorithm=temptable 需要先了解一下视图的算法
mysql> select * from myview1;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7782 | clark | manager  | 7839 | 1981-06-09 | 2450.00 |   NULL |     10 |
|  7369 | smith | clerk    | 7902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)

algorithm 算法

algorithm 三个值： merge，temptable 或 undefined（默认）

algorithm = undefined， 由系统决定，视图尽可能的选择merge算法而不是temptable算法，因为merge算法更高效，而且临时表被使用时视图不能够被更新

mysql> show create view myview1;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View    | Create View                                                                                                                                                                                                                                                                                                                                                  | character_set_client | collation_connection |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| myview1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview1` AS select `temp`.`empno` AS `empno`,`temp`.`ename` AS `ename`,`temp`.`job` AS `job`,`temp`.`mgr` AS `mgr`,`temp`.`hiredate` AS `hiredate`,`temp`.`sal` AS `sal`,`temp`.`comm` AS `comm`,`temp`.`deptno` AS `deptno` from `myview2` `temp` group by `temp`.`deptno` | latin1               | latin1_swedish_ci    |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

merge： 服务器将视图SQL和查询SQL进行合并，然后基于底层表查询，最终查的是基表，没有使用视图即虚拟表的查询结果

//创建视图a1 查询工资大于2000的员工信息
mysql> create view a1 as select * from emp where sal>2000;
Query OK, 0 rows affected (0.65 sec)

mysql> show create view a1;
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                                                                                                                                                                            | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| a1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `a1` AS select `emp`.`empno` AS `empno`,`emp`.`ename` AS `ename`,`emp`.`job` AS `job`,`emp`.`mgr` AS `mgr`,`emp`.`hiredate` AS `hiredate`,`emp`.`sal` AS `sal`,`emp`.`comm` AS `comm`,`emp`.`deptno` AS `deptno` from `emp` where (`emp`.`sal` > 2000) | latin1               | latin1_swedish_ci    |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> select * from a1;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | blake | manager   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | clark | manager   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)

//在查询视图 a1 的时候，再加一个where条件 sal<3000
mysql> select * from a1 where sal<3000;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

//以上语句合并 最终等同于 下面的select语句
mysql> select * from emp where sal>2000 and sal<3000;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

temptable： 根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表 (视图) 中查数据

上面案例中 merde最终查的是goods表 temptable去查的是虚拟表

//视图中不能包含子查询  换个思路 将子查询做成视图，设置 algorithm=temptable
mysql> create algorithm=temptable view myview2 as select * from emp order by deptno,sal desc;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from myview2;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

//create view 视图名1 as select 字段名 from 视图名2
mysql> create view myview1 as select * from myview2 temp group by deptno;
Query OK, 0 rows affected (0.04 sec)

//查到的结果完全一样
mysql> select * from myview1;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7698 | blake | manager   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

练习题

查询雇员编号，雇员名，部门名称 和 薪水级别（普通方式）

mysql> select emp.empno,ename,dept.dname,salgrade.grade from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal between losal and hisal;
+-------+--------+------------+-------+
| empno | ename  | dname      | grade |
+-------+--------+------------+-------+
|  7369 | smith  | research   |     1 |
|  7499 | allen  | sales      |     3 |
|  7521 | ward   | sales      |     2 |
|  7566 | jones  | research   |     4 |
|  7654 | martin | sales      |     2 |
|  7698 | blake  | sales      |     4 |
|  7782 | clark  | accounting |     4 |
|  7788 | scott  | research   |     4 |
|  7839 | king   | accounting |     5 |
|  7844 | iurner | sales      |     3 |
|  7900 | james  | sales      |     1 |
|  7902 | ford   | research   |     4 |
|  7934 | miller | accounting |     2 |
+-------+--------+------------+-------+
13 rows in set (0.64 sec)

查询雇员编号，雇员名，部门名称 和 薪水级别（视图）

mysql> create view b1 as select * from emp;
Query OK, 0 rows affected (0.05 sec)

mysql> create view b2 as select * from dept;
Query OK, 0 rows affected (0.05 sec)

mysql> create view b3 as select * from salgrade;
Query OK, 0 rows affected (0.03 sec)

//多表查询  虽然用到了视图  但感觉更复杂了   可能我的方法错了 ...
mysql> select b1.empno,b1.ename,b2.dname,b3.grade from b1,b2,b3 where b1.deptno=b2.deptno and b1.sal between losal and hisal;
+-------+--------+------------+-------+
| empno | ename  | dname      | grade |
+-------+--------+------------+-------+
|  7369 | smith  | research   |     1 |
|  7499 | allen  | sales      |     3 |
|  7521 | ward   | sales      |     2 |
|  7566 | jones  | research   |     4 |
|  7654 | martin | sales      |     2 |
|  7698 | blake  | sales      |     4 |
|  7782 | clark  | accounting |     4 |
|  7788 | scott  | research   |     4 |
|  7839 | king   | accounting |     5 |
|  7844 | iurner | sales      |     3 |
|  7900 | james  | sales      |     1 |
|  7902 | ford   | research   |     4 |
|  7934 | miller | accounting |     2 |
+-------+--------+------------+-------+
13 rows in set (0.00 sec)