目录1.基本使用创建视图修改了视图对基表数据有影响修改了基表对视图有影响删除视图2.视图规则和限制视图是一个虚拟表其内容由查询定义。同真实的表一样视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表基表的数据变化也会影响到视图。1.基本使用创建视图create view 视图名 as select语句终端2 [rootiZ5waahoxw3q2bZ ~]# cd /var/lib/mysql [rootiZ5waahoxw3q2bZ mysql]# cd scott; 终端1 [rootiZ5waahoxw3q2bZ ~]# mysql -uroot -p mysql use scott; mysql select ename,dname from emp inner join dept on emp.deptnodept.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)我们可以将这些select出来的当作一张视图去创建mysql create view myview as select ename,dname from emp inner join dept on emp.deptnodept.deptno; Query OK, 0 rows affected (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)好处是对于访问一些高频的表时就不需要去那样查。简化复杂查询提高代码复用性修改了视图对基表数据有影响mysql update myview set enamesmith where enameSMITH; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 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) 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 * 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 update dept set dnameaaaaa where deptno30; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from dept; ------------------------------ | deptno | dname | loc | ------------------------------ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | aaaaa | CHICAGO | | 40 | OPERATIONS | BOSTON | ------------------------------ 4 rows in set (0.00 sec) mysql select * from myview; -------------------- | ename | dname | -------------------- | smith | RESEARCH | | ALLEN | aaaaa | | WARD | aaaaa | | JONES | RESEARCH | | MARTIN | aaaaa | | BLAKE | aaaaa | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | aaaaa | | ADAMS | RESEARCH | | JAMES | aaaaa | | FORD | RESEARCH | | MILLER | ACCOUNTING | -------------------- 14 rows in set (0.00 sec)删除视图mysql drop view myview; Query OK, 0 rows affected (0.00 sec)2.视图规则和限制与表一样必须唯一命名不能出现同名视图或表名创建视图数目无限制但要考虑复杂查询创建为视图之后的性能影响视图不能添加索引也不能有关联的触发器或者默认值视图可以提高安全性必须具有足够的访问权限order by 可以用在视图中但是如果从该视图检索数据 select 中也含有 order by ,那么该视图 中的 order by 将被覆盖视图可以和表一起使用感谢你的观看期待我们下次再见