MySQL的视图(二十二)

勿以恶小而为之，勿以善小而不为--------------------------刘备

上一章简单介绍了 MySQL合并查询结果(二十一),如果没有看过,​​请观看上一章​​

一 . 视图

一.一 视图的出现

前面讲解了各种查询和子查询，如果要进行一个复杂功能的查询，那么就可能需要写很长很长的一个sql, 在代码中将这些sql进行硬编码进去，不便于后期维护和复用。 有没有一种技术，可以像使用表查询那样简单 (select * from 表名 )， 将那个很长很长的sql的查询结果封装到一个独特的表里面呢? 以后在查询的时候，直接查询那个独特的表即可? 实际上，数据库提供了这么一个技术， 这个技术叫做 视图 (view) .

视图(view) 和 表(table) 是有区别的。 表中的数据是一个真正存在的，存储于物理结构中， 而视图中的数据是虚拟的，
并不真实存在。

视图，常常适用于用于多个表连接查询之间的结果。

一.二 视图的优点

一.二.一 简单化

看到的，就是查询出来的。 直接 select * from 视图 即可， 并不需要向以前那样，写一个很长的sql语句(sql语句中，包括where，分组，排序等)

一.二.二 安全性

视图中只能查询和修改视图使用者所能看到的数据，不能维护其看不到的数据。 也就是， 表使用者和视图使用者，两个人的权限是不一样的， 从而保证了数据的安全性。

如在 user 表里面，存在以下数据:

select * from user;

共九个数据。

那么， 表使用者可以看到这九个员工， 同样，也可以维护 这九个员工的信息。

如果将

select * from user where id=1;

这一条数据，封装成视图， 那么，视图使用者只能看到这一个姓名为 ‘两个蝴蝶飞’ 的数据，也只能维护这一个员工的信息， 不能维护他看不到的，如 ‘岳泽霖’ 等这些数据。 从而，保证了重要数据的安全性。

如 员工工资数据，只有财务部门可以看，人事部门是没有办法查看的。

一.二.三 逻辑数据独立性

可以屏蔽真实表的表结构信息。

如 查询真实表的话， select * from user, 那么用户就可以看到，这个表 user 里面有 id,name,sex,birthday,age,deptId 六个字段。

如将 :

select id,name from user ;

的查询结果封装成一个视图，如视图 v_user 的话， 那么在查询 视图 v_user 时，只会显示 id,name 两个字段， 从而避免了真实表 user 的数据结构。

如将

select a.id as uId,a.name as uName,d.id as dId,d.name as dName from user a
    left join dept d on a.deptId=d.id;

的查询结果封装成一个视图， 如视图 v_user_dept 的话， 那么在查询 视图 v_user_dept 时，只会显示 员工编号，员工名称 ，部门编号，部门名称 四个字段， 从而屏蔽了真实 user 表和 真实 dept 表中的字段。

一.三 视图的操作

视图，通常有以下几种操作

下面，老蝴蝶 分别对每一种操作进行讲解。

视图，仍然使用的是 user 表和 dept 表进行讲解。

use yuejl;
  select * from user;
  select * from dept;

二. 创建视图 create view view_name

二.一 创建的命令

创建视图的完整命令是:

create [or replace] [algorithm={undefined|megre|temptable}]
  view view_name [{column_list}]
  as select_statement
  [with {cascaded|local} check option]

通常，会简写成:

create [or replace]
  view view_name [{column_list}]
  as select_statement

create 表示创建， or replace 表示 替换。 连用的话， create or replace 表示，如果存在这个视图名，就替换，
如果不存在，就创建。

其中，{column_list} 常常会省略，省略时，表示 视图中的字段名称 与 select_statement 中的字段名称一致。

其中，创建视图，可以在单表上，也可以在多表上。

二.二 单个表省略字段创建视图

如，查询员工的id,name,age 和 id和age的积 的值。

查询语句为:

select id,name,age,id*age from user;

那么， 创建 单表视图， 如 v_user1 就是:

create view v_user1
  as select id,name,age,id*age from user;

查询 视图 v_user1, 就像查询表 那么操作

select * from v_user1;

其中， 视图的字段列表，就是查询的语句的字段列表。

二.三 单个表重新定义字段 创建视图

将视图的字段列表，重新定义， 不用原先的 查询表中的字段， 需要用到 [{column_list}]

create view v_user2 (nId,nName,nAge,nIdAge)
  as select id,name,age,id*age from user;

再次查询时:

select * from v_user2;

发现，视图字段就变成了新定义的字段列表了。

二.四 多个表创建视图

如，查询每个员工的编号，姓名，部门编号和部门的名称。

查询语句为:

select a.id as uId,a.name as uName,d.id as dId,d.name as dName from user a
    left join dept d on a.deptId=d.id;

创建视图 v_user_dept

create view v_user_dept (uId,uName,dId,dName)
  as (select a.id as uId,a.name as uName,d.id as dId,d.name as dName from user a 
    left join dept d on a.deptId=d.id) ;

查询视图

select * from v_user_dept;

会发现， 视图的查询，远比表连接查询，更简单，也更易复用。

三. 查看视图

如何像查看 表结构 那样， 查看视图结构呢?

三.一 describe view_name

如 查询刚才创建的 v_user1 视图

describe v_user1;

与 describe 表名 查询出来的结构一致。

其中，也可以用其简写的形式， desc view_name

desc v_user1;

三.二 show create view view_name

如 查询刚才创建的 v_user2 视图

show create view v_user2 \G

与 show create table table_name 一致。

三.三 show table status like ‘视图名’

查看 视图结构的详细信息

如 查看 视图 v_user1

show table status like 'v_user1' \G

comment 为 view 表示，是视图

如果是表的话， comment 显示的是 空字符串

show table status like 'user' \G

三.四 information_schema 数据库下的 views 表

如 查询一下 v_user1 视图

select * from information_schema.views t where t.table_name='v_user1' \G

information_schema.views, 表示去查询 information_schema 数据库下的 views 表(注意，此时仍然在 yuejl数据库中)

一般 使用 describe(desc) 视图名 进行查询视图的相关信息。

四. 修改视图

修改已经存在的视图，有两种方式，一种是 create or replace, 另外一种是用 alter .

四.一 create or replace 重新创建视图

修改视图，用的是 or replace ，会重新创建一个新的视图。

如 修改 视图 v_user1, 改变 查询 name 和 age 的视图。 不能改变视图名称，只改变查询结果。

create or replace view v_user1(name,age)
  as (select name,age from user) ;

查询一下数据:

select * from v_user1;

会发现，视图 v_user1 被修改了。

注意，此时如果视图名称不存在，那么将会创建一个新视图。

四.二 alter view view_name 重新创建视图

修改视图，而不是 存在时修改，不存在时创建，那么可以直接使用 alter 进行修改

如 修改 v_user2 ,让其只查询 id 和 name 两个列。

alter view v_user2 (id,name)
  as (select id,name from user);

将 create or replace 替换成 alter .

重新查询 v_user2 的话:

select * from v_user2;

问题1: 如果 alter 修改的是一个不存在的视图， 如 v_user100 时，会出现什么情况呢?

alter view v_user100 (id,name)
  as (select id,name from user);

ERROR 1146 (42S02): Table ‘yuejl.v_user100’ doesn’t exist

会报错，提示视图不存在。

五. 使用/更新 视图

视图是一个虚拟表，没有存储数据，所以对视图数据的更新，实际上都转移到对基本表的更新上了。但是，不建议对视图进行更新操作， 只提倡查询。

五.一 查询数据

如 查询视图 v_user2

select * from v_user2;

v_user2 里面，有两个字段， id 和 name.

五.二 插入数据

当前， user 表 还是9条数据， 没有发生改变。

select * from user;

往视图 v_user2 里面插入一条数据， id 是10， 名称为 新员工5.

insert into v_user2(id,name) values(10,'新员工5');

这个时候，重新查看一下 v_user2 视图数据

select * from v_user2;

查看一下基本表 user中的数据

select * from user;

可以看出，正常的插入到视图(准确说是基本表)里面了.

五.三 更新数据

如将 视图 v_user2 里面的 id=10 新插入的那个数据 的员工名称 改变 ‘我是新来的员工5’

update v_user2 set name='我是新来的员工5' where id=10;

查看一下基本表 user 中的数据

select * from user;

五.四 删除数据

如将 视图 v_user2 里面的那条 id=10 的数据删除。

delete from v_user2 where id=10;

再重新查询一下 user 表的数据

select * from user;

五.五 约束性视图

在实际开发生活中，是不允许你通过更新视图进行改变基本表的数据， 所以常常会增加一些限制。

如 创建一个新视图 v_user3, 里面有 id 和 name 两个字段，但是有限制。

create or replace 
  view v_user3 (id,name)
  as (select id,name from user where id<20)
  with check option;

从表里面查询出来的数据，必须要保证 id<20.

那么，此时 查询一下视图:

select * from v_user3;

如果此时，往视图里面 添加 一条 id=100 的新数据(100>20)

insert into v_user3 (id,name) values(100,'新约束员工');

ERROR 1369 (HY000): CHECK OPTION failed ‘yuejl.v_user3’

会发现，不能插入进去 。

如果此时，往视图里面添加 一条 id=11 的新数据 (11<20)

insert into v_user3 (id,name) values(11,'新约束员工');

可以正确地插入

重新查询一下数据:

select * from user;

添加 with check option 之后，维护后的数据不能确定视图的完整性。

六. 删除视图

命令:

drop [if exists] view view_name;

如删除 视图 v_user3

drop view v_user3;

再次查询视图 v_user3的话:

select * from v_user3;

如果删除一个不存在的视图， 那么也会报错的。

删除视图，支持同时删除多个视图， 如再删除 v_user1和 v_user2

drop view v_user1,v_user2;

视图是非常重要的，老蝴蝶建议一定要掌握其常见用法。

谢谢!!!