视图是指计算机数据库中的视图,是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。——百度百科
MySQL相关的视图指令
操作指令 | 代码 |
---|---|
创建视图 | CREATE VIEW 视图名(列1,列2...) AS SELECT (列1,列2...) FROM ...; |
使用视图 | 当成表使用就好 |
修改视图 | CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...]; |
查看数据库已有视图 | >SHOW TABLES [like...]; (可以使用模糊查找) |
查看视图详情 | DESC 视图名 或者SHOW FIELDS FROM 视图名 |
视图条件限制 | [WITH CHECK OPTION] |
视图
和数据库相对应,每次进行查询工作,都需要编写查询代码进行查询;而视图的作用就是不必每次都重新编写查询的SQL代码,而是通过视图直接查询即可。因此:
视图是虚拟表,本身不存储数据,而是按照指定的方式进行查询。
为什么要定义视图呢?因为它有如下许多好处:
视图能简化用户操作
视图机制使用户可以将注意力集中在所关心地数据上。如果这些数据不是直接来自基本表,则可以通过定义视图,使数据库看起来结构简单、清晰,并且可以简化用户的的数据查询操作。例如,那些定义了若干张表连接的视图,就将表与表之间的连接操作对用户隐藏起来了。换句话说,用户所作的只是对一个虚表的简单查询,而这个虚表是怎样得来的,用户无需了解。
视图使用户能以多种角度看待同一数据
视图机制能使不同的用户以不同的方式看待同一数据,当许多不同种类的用户共享同一个数据库时,这种灵活性是非常必要的。
视图对重构数据库提供了一定程度的逻辑独立性
视图能够对机密数据提供安全保护
有了视图机制,就可以在设计数据库应用系统时,对不同的用户定义不同的视图,使机密数据不出现在不应该看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能
适当的利用视图可以更清晰地表达查询
视图的使用
下面我们会介绍视图的使用,基于的数据任然是我们上一篇文章里面的 emp
员工表和 dept
部门表。
单表上创建视图
在员工表上创建视图,使其能查询 ename
,job
和 sal
1 | create view view_1 as select ename,job,sal from emp; |
然后使用:
1 | select * from view_1; |
多表上创建视图
将 emp
表和 dept
表内连接进行查询:
1 | create view view_2 as select a.ename,a.sal,a.deptno,b.loc from emp a inner join dept b on a.deptno = b.deptno; |
然后使用:
1 | select * from view_2; |
查看视图
DESC 命令
1 | desc view_1; |
SHOW TABLE STATUS 命令
1 | show table status like 'view_1'; |
SHOW CREATE view 命令
1 | show create view view_1; |
修改视图
CREATE OR REPLACE 命令
1 | create or replace view view_1 as select ename,sal,job from emp; |
ALTER 命令
1 | alter view view_1 as select ename,sal from emp; |
更新视图
在MySQL中,更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚拟表,其中没有数据,所以通过视图更新时,都是转换到基本表来更新。
更新视图时,只能更新权限范围内的数据。超出了范围,就不能更新。
例如:
1 | update view_1 set ename = 'SMITH2' where ename = 'SMITH'; |
更新后真实表的数据也会发生改变。
不可更新的视图:
某些视图是可更新的。也就是说,可以在诸如UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。更具体地讲,如果视图包含下述结构中的任何一种,那么它就是不可更新的:
· 聚合函数(SUM(), MIN(), MAX(), COUNT()等)。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 位于选择列表中的子查询
· Join
· FROM子句中的不可更新视图
· WHERE子句中的子查询,引用FROM子句中的表。
· 仅引用文字值(在该情况下,没有要更新的基本表)。
· ALGORITHM = TEMPTABLE(使用临时表总会使视图成为不可更新的)。
注意:视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,就可能会造成数据更新失败。
with check option
如果在创建视图的时候制定了“WITH CHECK OPTION”,那么更新数据时不能插入或更新不符合视图限制条件的记录。
例如,我们创建一个视图,它只能查询出来 job 为 MANAGER
的员工:
1 | create view view_3 as select ename,job from emp where job = 'MANAGER' with check option; |
表示只限定插入job为 MANAGER
的员工。(with check option对于没有where条件的视图不起作用的)
然后我们插入员工进行测试:
1 | insert into view_3(ename,job) values('测试1','MANAGER'); |
这个员工的信息就能插入进去,原表中也会有这条信息。
我们再插入一条测试:
1 | mysql> insert into view_3(ename,job) values('测试2','CLERK'); |
可以看到会报错。
对于with check option用法,总结如下:
插入后的数据,通过视图能够查询出来就符合WITH CHECK OPTION, 否则就不符合;
对于update,有with check option,要保证update后,数据要被视图查询出来
对于delete,有无with check option都一样
对于insert,有with check option,要保证insert后,数据要被视图查询出来
对于没有where 子句的视图,使用with check option是多余的
删除视图
1 | drop view if exists view_3; |