视图【MySQL】

创建视图

CREATE VIEW view_name AS SELECT …;

查询每个员工及其对应的部门名称时，需要使用员工表和部门表进行多表查询，并筛选出员工的部门号等于部门的部门号的记录

mysql> select ename ,dname from emp ,dept where emp.deptno=dept.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

给上述查询结果创建视图

mysql> create view myview as select  ename ,dname from emp ,dept where emp.deptno=dept.deptno;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| exam            |
| myview          |
| salgrade        |
| stu             |
+-----------------+
6 rows in set (0.01 sec)


mysql> select * from myview;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

​

在数据库对应的目录下，会增加一个对应的xxx.frm文件，但并没有与之对应的xxx.ibd文件，证明了视图和基表使用的是同一份数据

修改视图影响基表

将员工CLARK所在部门的部门名改为HR后，会看到其他一些员工所在部门的部门名也变为HR了

视图和基表使用的是同一份数据，将视图中CLARK所在部门的部门名改为HR后，部门表中10号部门的部门名也就变成HR了。

修改基表影响视图

查询员工表，可以看到员工JAMES所在部门的部门号为30

30号部门的部门名为SALES，因此查询视图时可以看到JAMES所在的部门名为SALES

将员工表中，员工JAMES对应的部门号改为20

修改后再查询视图，就会发现JAMES所在部门的部门名，变成了20号部门的部门名RESEARCH

mysql> select * from emp where ename='JAMES';
+--------+-------+-------+------+---------------------+--------+------+--------+
| empno  | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+--------+-------+-------+------+---------------------+--------+------+--------+
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL |     20 |
+--------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)

mysql> select * from myview;
+--------+----------+
| ename  | dname    |
+--------+----------+
| SMITH  | RESEARCH |
| ALLEN  | SALES    |
| WARD   | SALES    |
| JONES  | RESEARCH |
| MARTIN | SALES    |
| BLAKE  | SALES    |
| CLARK  | HR       |
| SCOTT  | RESEARCH |
| KING   | HR       |
| TURNER | SALES    |
| ADAMS  | RESEARCH |
| JAMES  | RESEARCH |
| FORD   | RESEARCH |
| MILLER | HR       |
+--------+----------+
14 rows in set (0.00 sec)

删除视图

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

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| exam            |
| salgrade        |
| stu             |
+-----------------+
5 rows in set (0.00 sec)

该视图在数据库目录下对应的xxx.frm文件也会被删除

视图图规则和限制:

与普通表一样，视图的命名也必须是唯一的，不能出现同名视图或表名。
创建视图的数目无限制，但要考虑复杂查询创建为视图之后的性能影响。
视图不能添加索引，也不能有关联的触发器或者默认值。
视图可以提高安全性，在访问视图时必须具有足够的访问权限。
创建视图时可以使用order by子句，但如果从该视图检索数据时也含有order by子句，那么该视图中的order by将被覆盖。
视图可以和普通表一起使用，比如进行多表查询，内外连接等