MySQL(十) 视图

1.什么是试图

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

2.为什么用视图

在多表联合查询的时候，每次select的时候都要编写复杂的MySQL语句，效率低下，为了解决这个问题，数据库提供了视图。

视图就相当于把查询的MySQL语句保存下来了，并没有创建新的表，本身不存储数据。

3.基本命令

案例

mysql> create view v1_ename_dname(员工名,部门名) as select ename,dname from emp inner join dept on emp.deptno=dept.deptno;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1_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 emp set deptno=10 where ename='JAMES';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from v1_ename_dname;
+-----------+------------+
| 员工名    | 部门名     |
+-----------+------------+
| JAMES     | ACCOUNTING |
+-----------+------------+
14 rows in set (0.00 sec)

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

mysql> update v1_ename_dname set 员工名='JAMESSSS' where 员工名='JAMES';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from emp;
+--------+----------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename    | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+----------+-----------+------+---------------------+---------+---------+--------+
| 007900 | JAMESSSS | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     10 |
+--------+----------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

插入数据

mysql> insert into v1_ename_dname(员工名,部门名) values('Bob','SALES');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'scott.v1_ename_dname'

看报错信息显示，不能跨表更新数据。可以更改一个表中数据。

删除视图

drop view 视图名；

WITH CHECK OPTION选项

创建有条件限制的视图

// 查看基表
mysql> select * from emp1;
+----------+---------+
| ename    | sal     |
+----------+---------+
| SMITH    |  800.00 |
| ALLEN    | 1600.00 |
| WARD     | 1250.00 |
| JONES    | 2975.00 |
| MARTIN   | 1250.00 |
| Bob      | 2850.00 |
| CLARK    | 2450.00 |
| SCOTT    | 3000.00 |
| cccc     | 5000.00 |
| TURNER   | 1500.00 |
| ADAMS    | 1100.00 |
| JAMESSSS |  950.00 |
| Bob      | 3000.00 |
| MILLER   | 1300.00 |
+----------+---------+
14 rows in set (0.00 sec)

//创建视图
mysql> create view vvv as select ename,sal from emp1 where sal >= 4000 with check option;
Query OK, 0 rows affected (0.00 sec)
//查看视图
mysql> select * from vvv;
+-------+---------+
| ename | sal     |
+-------+---------+
| cccc  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
//插入满足条件的
mysql> insert into vvv(ename,sal) values('a','4500');
Query OK, 1 row affected (0.01 sec)

mysql> select * from vvv;
+-------+---------+
| ename | sal     |
+-------+---------+
| cccc  | 5000.00 |
| a     | 4500.00 |
+-------+---------+
2 rows in set (0.00 sec)
//插入不满足条件的
mysql> insert into vvv(ename,sal) values('a','3500');
ERROR 1369 (HY000): CHECK OPTION failed 'scott.vvv'

不加with check option 

mysql> create view vvv1 as select ename,sal from emp1 where sal >= 4000;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into vvv1(ename,sal) values('a','3500');
Query OK, 1 row affected (0.00 sec)

mysql> select * from vvv;
+-------+---------+
| ename | sal     |
+-------+---------+
| cccc  | 5000.00 |
| a     | 4500.00 |
+-------+---------+
2 rows in set (0.00 sec)

4.视图规则和限制

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