1. 视图
视图是一个虚拟表,可以将查询出来的结果,以表结构的形式保存起来。
视图的数据变化会影响到基表,基表的数据变化也会影响到视图。
2. 基本使用
创建视图:
create view 视图名 as select 语句
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| salgrade |
+-----------------+
3 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> create view myview as select ename, dname from emp inner join dept on emp.deptno=dept.deptno;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| myview |
| salgrade |
+-----------------+
4 rows in set (0.00 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)
修改视图,对基表的影响:
修改基表,对视图的影响:
删除视图:
drop view view_name;
3. 视图使用规则和限制
- 视图和表一样,必须唯一命名
- 创建视图数目没有限制
- 视图不能添加索引
order by
可以用在视图中,但是如果从该视图检索数据select
中也含有order by
,那么该视图中的order by
将被覆盖- 视图可以和表一起使用
牛客OJ: