约 910 字大约 3 分钟
2025-08-06
前要：视图和上一节的读视图没有任何关系。
视图是一个虚拟表，其内容由查询定义，和真实表结构一样。视图包含一系列带有名称的列数据和行数据，视图的数据变化会影响到基表，基表的数据变化也会影响到视图。
基表按照 CREATE VIEW view_name AS SELECT...; 挑选出来构成一个子表，也就是视图，也就是把一个临时表当做一个真实表来处理。
实际用一下您就明白了：
# 视图的使用
# (1)创建一个视图
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | accounting | new york |
| 20 | research | dallas |
| 30 | sales | chicago |
| 40 | operations | boston |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select ename, dname from emp inner join dept on 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> select ename, dname from emp inner join dept on emp.deptno=dept.deptno where ename='smith';
+-------+----------+
| ename | dname |
+-------+----------+
| smith | research |
+-------+----------+
1 row in set (0.00 sec)
mysql> create view new_table as (select ename, dname from emp inner join dept on emp.deptno=dept.deptno);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------------------+
| Tables_in_limou_database |
+--------------------------+
| dept |
| emp |
| new_table | # 这好像就是一个表？其实直接把这个视图当作一个表来操作也不是不行
+--------------------------+
3 rows in set (0.00 sec)
# (2)对视图进行修改
mysql> update new_table set ename='SMITH' where ename='smith';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from new_table;
+--------+------------+
| 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> select ename, dname from emp inner join dept on emp.deptno=dept.deptno where ename='smith';
+-------+----------+
| ename | dname |
+-------+----------+
| SMITH | research |
+-------+----------+
1 row in set (0.00 sec)那这个“临时表/视图”结果有什么用呢？好处就是方便快捷，查找出来的结果（例如笛卡尔积的结果）可以直接当做一个完整表来操作，并且如果对视图做修改，会影响原表的数据。
但是必须使用 DROP VIEW view_name; 来删除视图。
此外，关于视图有几点需要您注意一下：
- 与表一样，必须唯一命名（不能出现同名视图或表名）
- 创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响
- 视图不能添加索引，不能有关联的触发器或者默认值
- 视图可以提高安全性，必须具有足够的访问权限
ORDER BY可以用在视图中，但是如果从该视图检索数据select中也含有order by,那么该视图中的order by将被覆盖- 视图可以和表一起使用（例如：再做笛卡尔积）
- 有些数据库的技术是被禁用的，导致维护成本变高，包括视图
