记录生活中的点点滴滴

0%

MySQL视图学习

发表于 Valine：

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

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 部门表。

单表上创建视图

在员工表上创建视图，使其能查询 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
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;