记录生活中的点点滴滴

0%

MySQL视图学习

视图是指计算机数据库中的视图,是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。——百度百科

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代码,而是通过视图直接查询即可。因此:

视图是虚拟表,本身不存储数据,而是按照指定的方式进行查询。

为什么要定义视图呢?因为它有如下许多好处:

  1. 视图能简化用户操作

    视图机制使用户可以将注意力集中在所关心地数据上。如果这些数据不是直接来自基本表,则可以通过定义视图,使数据库看起来结构简单、清晰,并且可以简化用户的的数据查询操作。例如,那些定义了若干张表连接的视图,就将表与表之间的连接操作对用户隐藏起来了。换句话说,用户所作的只是对一个虚表的简单查询,而这个虚表是怎样得来的,用户无需了解。

  2. 视图使用户能以多种角度看待同一数据

    视图机制能使不同的用户以不同的方式看待同一数据,当许多不同种类的用户共享同一个数据库时,这种灵活性是非常必要的。

  3. 视图对重构数据库提供了一定程度的逻辑独立性

  4. 视图能够对机密数据提供安全保护

    有了视图机制,就可以在设计数据库应用系统时,对不同的用户定义不同的视图,使机密数据不出现在不应该看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能

  5. 适当的利用视图可以更清晰地表达查询

视图的使用

下面我们会介绍视图的使用,基于的数据任然是我们上一篇文章里面的 emp 员工表和 dept 部门表。

单表上创建视图

在员工表上创建视图,使其能查询 enamejobsal

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
2
3
mysql> insert into view_3(ename,job) values('测试2','CLERK');

ERROR 1369 (HY000): CHECK OPTION failed 'mydatabase.view_3'

可以看到会报错。

对于with check option用法,总结如下:

插入后的数据,通过视图能够查询出来就符合WITH CHECK OPTION, 否则就不符合;

  1. 对于update,有with check option,要保证update后,数据要被视图查询出来

  2. 对于delete,有无with check option都一样

  3. 对于insert,有with check option,要保证insert后,数据要被视图查询出来

  4. 对于没有where 子句的视图,使用with check option是多余的

删除视图

1
drop view if exists view_3;