有志者，事竟成
文章持续更新，可以微信搜索【小奇JAVA面试】第一时间阅读，回复【资料】获取福利，回复【项目】获取项目源码，回复【简历模板】获取简历模板，回复【学习路线图】获取学习路线图。

MySQL-视图_mysql

文章目录

前言

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。

一、视图简介

视图由数据库中的一个表或多个表导出的虚拟表。其作用是方便用户对数据的操作。

1、视图的含义

视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

下面用一个例子来具体的讲解视图的含义。

下面一个公司的数据库中有一张公司部门表department。表中包括部门号(d_id)、部门名称(d_name)、功能(function)和办公地址(address)。department表结构如下：

MySQL-视图_数据_02

还有一张员工表worker。表中包含了员工的工作号（num）、部门号（d_id）、姓名（name）、性别（sex）、出生日期（birthday）和家庭住址（homeaddress）。worker表结构如下：

MySQL-视图_mysql_03


由于各部门的领导的权力范围不同。因此，各部门的领导只能看到该部门的员工的信息。而且，领导可能不关心员工的生日和家庭住址。为了达到这个目的，可以为各部门的领导建立一个视图。通过该视图，领导只能看到本部门的员工的指定信息。

例如，为生产部门建立一个名为product_view的视图。通过视图product_view，生产部门的领导只能看到生产部门员工的工作号、姓名和性别等信息。这些department表的信息和worker表的信息依然存在于各自的表中，而视图product_view中不保存任何数据信息。当department表和worker表的信息发生改变时，视图product_view展示的信息也发生相应的变化。

2、视图的作用

视图是在原有的表或者视图的基础上重新定义的虚拟表，这可以从原有的表上选取对用户有用的信息。那些对用户没有用，或者用户没有权限了解的信息，都可以直接屏蔽掉。这样做即使应用简单化，也保证了系统的安全。视图起着类似于筛选的作用。视图的作用归纳为如下几点：

1、使操作简单化

视图需要达到的目的就是所见即所需。也就是说，从视图看到的信息就是所需要了解的信息。视图可以简化对数据的操作。例如，可以为经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件。这样可以很大程度上方便用户的操作。

2、增加数据的安全性

通过视图，用户只能查询和修改指定的数据。指定数据以外的信息，用户根本接触不到。数据库授权命令可以限制用户的操作权限，但不能限制到特定行和列上。使用视图后，可以简单方便地将用户的权限限制到特定的行和列上。这样可以保证敏感信息不会被没有权限的人看到，可以保证一些机密信息的安全。

3、提高表的逻辑独立性

视图可以屏蔽原有表结构变化带来的影响。例如，原有表增加列和删除未被引用的列，对视图不会造成影响。同样，如果修改了表中的某些列，可以使用修改视图来解决这些列带来的影响。

二、创建视图

创建视图是指在已存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。

1、创建视图的语法形式

MySQL中，创建视图是通过SQL语句CREATE VIEW实现的。其语法形式如下：

create [algorithm = {undefined | merge | temptable}]
  view 视图名 [{属性清单}]
  as select 语句
  [with [cascaded | local] check option];

其中，algorithm是可选参数，表示视图选择的算法；“视图名”参数表示要创建的视图的名称；“属性清单”是可选参数，其指定了视图中各个属性的名词，默认情况下与select语句中查询的属性相同；select语句参数是一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中；with check option是可选参数，表示更新视图时要保证在该视图的权限范围之内。

algorithm包括3个选项undefined、merge和temptable。其中，undefined选项表示MySQL将自动选择所要使用的算法；merge选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分；temptable选项表示将视图的结果存入临时表，然后使用临时表执行语句。

cascaded是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；local表示更新视图时，要满足该视图本身的定义的条件即可。

创建视图时，需要有create view的权限。同时，应该具有查询涉及的列的select权限。在MySQL数据库下面的user表中保存这些权限信息，可以使用select语句查询。

select语句的查询的方式如下：

select Select_priv,Create_view_priv from mysql.user where user='用户名';

MySQL-视图_数据库_04

其中，Select_priv属性表示用户是否具有select权限，Y表示拥有select权限，N表示没有；Create_view_priv属性表示用户是否具有create view权限；mysql.user表示MySQL数据库下面的user表；“用户名”参数表示要查询哪个用户是否拥有DROP权限，该参数需要单引号引起来。因为该数据库系统中只有root用户，所以查询出来的结果只有root用户的权限。

2、在单表上创建视图

MySQL中可以在单个表上创建视图。

下面在department表上创建一个简单的视图，视图名称为department_view1。创建视图的代码如下：

create view department_view1
as select * from department;

MySQL-视图_数据库_05


我们使用desc来查看表结构

MySQL-视图_java_06

结果显示，视图department_view1的属性与department表的结果一样。因为，在未指定视图的属性列表的情况下，视图的属性名与select语句查询的属性名相同。该示例中的select语句查询出了department表的所有列。那么，视图department_view1就包含了department表的所有列。

下面在department表上创建一个名为department_view2的视图。创建视图的代码如下：

MySQL-视图_数据_07


我们使用desc来查看表结构

MySQL-视图_java_08

结果显示，视图department_view2的属性分别为name、fuction和location。因为，在创建视图时指定了属性列表。视图的属性名与属性列表中的属性名相同。该示例中的select语句查询出了department表的d_name、function和address这3列。那么，视图department_view2中的列就分别对应着这3列。使用视图时，用户接触不到实际操作的表和字段。这样可以保证数据库的安全。

3、在多表上创建视图

MySQL中也可以在两个或两个以上的表上创建视图，也是使用create view语句实现的。

下面在department表和worker表上创建一个名为worker_view1的视图。

创建视图的代码如下：

MySQL-视图_mysql_09

使用desc查看表结构

MySQL-视图_数据库_10

结果显示，视图worker_view1的属性分别为name、department、sex、age和location。视图指定的属性列表对应着两个不同的表的属性列。视图的属性名与属性列表中的属性名相同。该示例中的select语句查询出了department表的d_name字段，还有worker表的name、sex、birthday和address。其中，department表的d_name字段对应视图的department字段；worker表的birthday字段进行减法操作后，对应视图的age字段。而且，视图worker_view1的algorithm的值指定为merge。还增加了with local check option约束。本实例说明，视图可以将说个表上的操作简洁的表示出来。

三、查看视图

查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有show view的权限，MySQL数据库下的user表中保存着这个信息。查看视图的方法包括describe语句、show table status语句、show create view语句和查询information_schema数据库下的views表等。

1、describe语句查看视图基本信息

可以使用describe语句来查看视图的基本定义。基本形式如下：

describe 视图名;

其中，“视图名”参数指所要查看的视图的名称。

下面是用describe语句查看视图worker_view1的定义，代码如下：

describe worker_view1;

查询结果如下：

MySQL-视图_数据_11

结果中显示了字段的名称（Field）、数据类型（Type）、是否为空（Null）、是否为主外键（Key）、默认值（Default）和额外信息（Extra）。

describe可以缩写成desc。可以直接使用desc查看worker_view1表的结构，代码如下：

desc worker_view1;

2、show table status语句查看视图基本信息

在MySQL中，可以使用show table status语句来查看视图的信息。其语法形式如下：

show table status like '视图名';

其中，“like”表示后面匹配的是字符串；“视图名”参数指要查看的视图的名称，需要用单引号引起了。

下面是用show table status语句查看视图‘worker_view1’的信息，代码如下：

show table status like 'worker_view1';

代码执行结果如下：

MySQL-视图_数据_12


执行结果显示，表的说明（Comment）项的值为VIEW，说明该表为视图。存储引擎数据长度等信息都显示为null，说明视图是虚拟表，与普通表是有差异的。同样适用show table status语句来查看department表的信息。查询结果如下：

MySQL-视图_java_13

从结果可以看出，department表的基本信息都显示出来，包括存储引擎、创建时间等。但是Comment项没有信息。这就是视图和普通表最直接的区别。

说明：show table status语句虽然也可以查看视图的基本信息， 但是通常很少使用。因为，使用show table status语句查询视图信息时，各个属性显示的值都是null。只有comment属性显示值为view。

3、show create view语句查看视图的详细信息

在MySQL中，show create view语句可以查看视图的详细定义。其语法形式如下：

show create view 视图名

下面是用show create view语句查看视图worker_view1的信息，代码如下：

show create view worker_view1;

代码执行结果如下：

MySQL-视图_mysql_14

执行结果显示了详细的信息。包括视图的各个属性、with local check option条件和字符编码（character_set_client）等信息。通过show create view语句，可以查看视图的所有信息。

4、在views表中查看视图详细信息

在MySQL中，所有视图的定义都存在information_schema数据库下的views表中。查询views表，可以查看到数据库中所有视图的详细信息。查询的语句如下：

select * from information_schema.views;

其中，“*”表示查询所有的列的信息；information_schema.views表示information_schema数据库下面的views表。

下面是用select语句查询views表中的信息。代码执行如下：

MySQL-视图_java_15

我们可以看到数据库中所有的视图信息。

技巧：show create view语句可以查看视图的详细信息，如果读者希望了解详细信息可以使用这个语句。所有视图的定义都是存储在information_schema数据库下的views表中，也可以在这个表中查看视图的定义。不过，通常情况下都是使用show create view语句。

四、修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过create or replace view语句和alter语句来修改视图。

1、create or replace view语句修改视图

在MySQL中，create or replace view语句可以用来修改视图。该语句的使用非常灵活。在视图已经存在的情况下，对视图进行修改；视图不存在时，可以创建视图。

create or replace view语句的语法形式如下：

create or replace [algorithm = {undefined | merge | temptable}]
view 视图名 [{属性清单}]
as select 语句
[with [cascaded | local] check option];

这里的所有参数都与创建视图的参数是一样的。

下面是用create or replace view语句修改视图department_view1。代码如下：

MySQL-视图_数据_16


修改成功后，执行desc语句来查看视图的详细信息。

MySQL-视图_java_17


技巧：create or replace view语句不仅可以修改已经存在的视图，也可以创建新的视图。下面会介绍使用alter语句修改视图的方法，不过alter语句只能修改已经存在的视图。通常情况下，最好选择create or replace view语句修改视图。

2、alter语句修改视图

在MySQL中，alter语句可以修改表的定义，可以创建索引。不仅如此，alter语句还可以用来修改视图。alter语句修改视图的语法格式如下：

alter [algorithm = {undefined | merge | temptable}]
view 视图名 [{属性清单}]
as select 语句
[with [cascaded | local] check option];

这里的所有参数都跟创建视图的参数是一样的，不再进行赘述。

下面是用alter语句修改视图department_view2.代码如下：

MySQL-视图_数据_18


修改后使用desc查看视图结构。

MySQL-视图_数据库_19

五、更新视图

更新视图是指通过视图来插入（insert）、更新（update）和删除（delete）表中的数据。因为视图是一个虚拟表，其中没有数据。通过视图更新时，都是转换到基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

下面在视图department_view3中对视图进行更新。department_view3是department表的视图。department表的记录如下：

MySQL-视图_数据库_20


在更新之前，先创建视图department_view3。代码如下：

MySQL-视图_数据库_21


向视图department_view3中更新一条记录。新记录的name的值为“科研部”，function的值为“新产品研发”，address的值为“3号楼5层”。更新语句执行如下：

MySQL-视图_java_22

然后我们查询department_view3的记录。

MySQL-视图_数据库_23


我们查询department表的记录。

MySQL-视图_数据库_24


结果显示，d_id为1001的记录已经更新。虽然，update语句更新的是视图department_view3。但实际上更新的是department表。上面的update语句可以等价为：

update department set d_name='科研部',function='新产品研发',address='3号楼5层' where d_id=1001;

由上面可以看出，对视图的更新最后都是实现在基本表上的。更新视图时，实际上更新的是基本表上的记录。但是，并不是所有的视图都可以更新的。以下这几种情况是不能更新视图的：

1、视图中包含sum()、count()、max()和min()等函数。

下面视图worker_view4创建视图的代码如下：

MySQL-视图_数据库_25


因为该视图包含count()，所以该视图是不能更新的。

2、视图中包含union、union all、distinct、group by和havig等关键字。

下面视图worker_view5创建视图的代码如下：

MySQL-视图_mysql_26


因为该视图包含group by，所以该视图也是不能更新的。

3、常量视图。

下面视图worker_view6创建视图的代码如下：

MySQL-视图_java_27


因为该视图的name字段是个字符串常量“Aric”，所以该视图也是不能更新的。使用update语句更新时，会出现系统报错。信息显示如下：

MySQL-视图_数据库_28

4、视图中的select中包含子查询

下面视图worker_view7创建视图的代码如下：

MySQL-视图_java_29


该视图包含了子查询，因此也是不能更新的。

5、由不可更新的视图导出的视图。

下面视图worker_view8创建视图的代码如下：

MySQL-视图_java_30


因为worker_view7是不可更新的视图，所以worker_view8也是不可以更新的视图。使用update语句更新时，会出现系统报错。信息显示如下：

MySQL-视图_数据库_31


6、创建视图时，algorithm为temptable类型。

下面视图worker_view9创建视图的代码如下：

MySQL-视图_数据_32


因为该视图的algorithm为temptable类型，所以worker_view9为不可以更新的视图。temptable类型就是临时表类型。系统默认临时表是不能更新的。

7、视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。例如，表中包含的name字段没有默认值，但是视图中不包括该字段。那么这个视图是不能更新的。因为，在更新视图时，这个没有默认值的记录将没有值插入，也没有null值插入。数据库系统是不会允许这样的情况出现的，其会阻止这个视图更新。

六、删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除的数据。MySQL中，使用drop view语句来删除视图。但是，用户必须拥有drop权限。

对需要删除的视图，使用drop view语句进行删除。基本形式如下：

drop view [if exists] 视图名列表 [restrict | cascade]

其中，if exists参数指判断视图存在，如果存在则执行，不存在则不执行；“视图名列表”参数表示要删除的视图的名称的列表，各个视图名称之间用逗号隔开。

下面将删除视图worker_view1。代码如下：

MySQL-视图_数据_33


执行结果显示，视图删除成功。为了验证视图是否确实已经删除，执行show create view语句查看。执行结果如下：

MySQL-视图_数据库_34


结果显示，视图work _view1已经不存在。这说明drop view语句删除视图成功。

下面将同时删除department_view1和department_view2这两个视图。代码如下：

MySQL-视图_数据库_35


执行结果显示，这两个视图已经删除成功。

用户必须拥有drop权限才可以删除视图。MySQL中，MySQL数据库下的user表中可以查询到是否存在drop权限。查看drop权限的语句如下：

select Drop_priv from mysql.user where user='用户名';

其中，“Drop_priv”属性表示用户是否具有drop权限，Y表示拥有drop权限，N表示没有；“用户名”参数表示要查询哪个用户是否拥有drop权限，该参数需要单引号引起来。因为该数据库系统中只有root用户，所以查询出来的结果只有root用户的权限。

该语句的执行结果如下：

MySQL-视图_mysql_36

结果显示，“Drop_priv”属性的值为Y，表示具有drop权限。

七、总结


文章中涉及到的命令大家一定要像我一样每个都敲几遍，只有在敲的过程中才能发现自己对命令是否真正的掌握了。