【MySQL进阶】MySQL视图详解

最新推荐文章于 2024-07-25 16:06:56 发布

小杨MiManchi

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量5k

点赞数 58

分类专栏： MySQL 文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/m0_64338546/article/details/128763574

版权

MySQL 专栏收录该内容

11 篇文章 22 订阅

订阅专栏

序号	系列文章
6	【MySQL基础】MySQL单表操作详解
7	【MySQL基础】运算符及相关函数详解
8	【MySQL基础】MySQL多表操作详解
9	【MySQL进阶】MySQL事务详解

前言

大家好，我是小杨！前面我已经为大家介绍了MySQL中的进阶知识->事务，那么今天我们就给大家讲解MySQL中的视图这部分的相关内容，希望大家能够收获多多！

1，视图

1.1，视图概述

视图的定义：一种从一个或多个数据表中导出来的虚拟存在的表，本身是不具有数据记录的。

视图的创建是建立已有表的基础之上，而这些视图赖以建立的表称为基表。也就是，创建的表结构和表数据记录都是依赖于基表。

视图的功能：不仅可以查看到存放在基表中的数据，还可以像操作基本表一样，对数据进行查询，添加，更新，修改和删除的操作。

视图的总结：在数据库中，视图不会保存数据，是虚拟存在的表，数据真正保存在数据表中。视图的创建和删除只会影响视图本身，不会影响对应的基表。当对视图中的数据进行增加、删除和修改操作时，与之对应的基表中的数据记录会相应地发生变化；当对基表中的数据进行增加，删除和操作时，与之对应的视图中的数据记录也会发生相对应的变化。

1.2，视图使用环境

在公司中，每个部门之间相互配合，完成自己对应的任务。而公司的信息数据(人员信息，财务信息，… )都被保存到公司的数据库中，如果不同部门的员工想对公司的信息数据进行操作时，直接将公司数据都打印出来，这就会导致一系列的问题。

而我们希望的是不同部门，不同级别的员工所拥有的权限是不同的，进而操作所得到的结果也是不一样的。那如何才能实现呢？

视图的存在就可以实现上述希望，视图一方面可以让我们只使用表中的一部分数据，而不是使用表中的所有数据；另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只希望给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么这个字段的数据的操作权限只能给特定级别以上的人员开放，而其他人在查询视图时，则不会查询到这个字段的信息。

总的来说，视图其实就是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率，理解和使用起来都非常方便。

1.3，视图创建格式

创建视图通过使用CREATE VIEW 语句来进行创建，其基本语法为：

CREATE [OR REPLACE] [ALGORITHM][DEFINER][SQL SECURITY] 
VIEW view_name [column_list] AS select_statement [WITH [CASCADED | LOCAL]] CHECK OPTION;

上述语法中各个参数代表的含义为：

1，OR REPLACE，可选参数，表示替换已有视图

2，ALGORITHM，可选，表示视图算法，会影响查询语句的解析方法，它的取值有以下3种：

UNDEFINED（默认），由MySQL自动选择算法。
MERGR，将select_statement和查询视图时的SELECT语句合并起来进行查询。
TEMPTABLE，先将select_statement的查询结果存入临时表，然后用临时表进行查询

3，DEFINER，可选，表示定义视图的用户，与安全控制有关，默认为当前用户。

4，SQL SECURITY，可选，用于视图的安全控制，它的取值有以下2种：

DEFINER（默认），由定义者指定的用户的权限来执行。
INVOKER，由调用视图的用户的权限来进行。

5，view_name，表示要创建的视图名称

6，column_list，可选，用于指定视图中的各个字段名。默认情况下，与SELECT语句查询的字段相同。

7，AS，表示视图要执行的操作

8，select_statement，查询语句，表示从基表或视图中查询出满足条件的记录，然后将这些数据记录导入视图中。

9，WITH CHECK OPTION，可选，用于视图数据操作时的检查条件，若省略，则不进行检查，它的取值有以下2种：

CASCADED（默认），操作数据时满足所有有关视图和表定义的条件。
LOCAL，操作数据时只需要满足该视图本身定义的条件。

看到上面的这些参数，可能会对视图产生一种畏惧心理了，尽管视图的创建语法参数这么多，但我们在实际创建时，因只是简单操作，不注重什么用户，权限问题，因此就只需要使用以下缩减版语法来进行视图的创建。语法缩减为：

CREATE [OR REPLACE] VIEW view_name [column_list] 
AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];

1.4，视图创建注意

1，在默认情况下，新创建的视图保存在当前选择的数据库中。若想要明确地指定在某个数据库中创建视图，在创建时应将名称指定数据库名.视图名。

2，在进行SHOW TABLES操作时，查询到的结果中不仅包含了该数据库中的数据表，还包含了已经创建好的视图。

3，创建视图要求用户具有CREATE VIEW权限，以及查询涉及到的SELECT权限。若有OR REPLACE子句，还必须具有视图的DROP权限。

4，在同一个数据库中，新创建视图的视图名称不能与已经存在的表名称相同，如果相同，会创建失败。

5，当一个视图创建完成后，MySQL就会在数据库目录上创建一个名为视图名.frm的文件。

2，视图操作

视图是虚拟存在的表，本身是不具有数据记录的。视图的创建和删除只会影响视图本身，不会影响对应的基表。

2.1，创建视图

1，单表视图创建

CREATE VIEW view_name [column_list] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];

为了更好的理解单表视图的创建，示例如下：

在上述单表视图的创建后，从视图中查询到的数据记录与直接在基表查询的数据相同。视图也能像基表一样对查询到的数据记录进行排序，限量，排序等一系列操作。

在进行SHOW TABLES操作时，查询到的结果中不仅包含了该数据库中的数据表，还包含了已经创建好的视图。

在查询视图时，SELECT字段列表和WHERE等子句中的字段，只能使用创建视图时指定的SELECT语句中的字段，就如上述操作中的id，name，score，group_id 这些字段，而其它的字段无法通过view_group_1视图进行查询，若使用，则会报错。

在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

在创建视图时，可以对视图的字段名称进行自定义，而自定义列名称的顺序与AS后的SELECT字段列表顺序一致，且自定义列名称的数量必须与SELECT字段列表的数量一致，若数量不一致，MySQL会报错，无法成功创建视图。

2，多表视图创建

CREATE VIEW view_name [column_list] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];

为了更好的理解多表视图的创建，示例如下：

在上述视图创建时，AS后的select_statement涉及到多张数据表的查询时，则创建出来的视图就被称为多表视图。

在进行SHOW TABLES操作时，查询到的结果中不仅包含了该数据库中的数据表，还包含了已经创建好的视图。

2.2，查看视图

1，查看视图的字段信息

DESC view_name；

为了更好的理解视图的查看操作，示例如下：

在上述的操作中，我们看到可以通过DESC view_name查询到视图的字段信息。

因此，我们可以得出:DESC在MySQL中不仅可用来查询数据表的字段信息，也可以用来查询视图的字段信息。

2，查看视图状态信息

SHOW TABLE STATUS LIKE 'view_name'\G;

为了更好的理解视图状态信息的查看，示例如下：

在上述操作结果中，我们可以发现除了Name和Comment之外，其余的信息均为NULL。Name表示名称，而Comment的值为VIEW，表示所查的view_emp是一个视图。

因此，我们可以得出:SHOW TABLE STATUS在MySQL中不仅可用来查询数据表的状态信息，也可以用来查询视图的状态信息。

3，查看视图的创建语句

#方式1:
SHOW CREATE VIEW view_name \G;

#方式2:
SHOW CREATE TABLE view_name \G;

为了更好的理解视图创建信息的查看，示例如下：

在上述操作结果中，我们可以发现使用SHOW CREATE VIEW view_emp或使用SHOW CREATE TABLE view_emp可以查看到view_emp视图的名称，创建语句以及其的字符编码等信息。

因此，我们可以得出:SHOW CREATE VIEW view_name或SHOW CREATE TABLE view_name在MySQL中可用来查询创建视图时的定义语句以及视图的字符编码等信息。

2.3，修改视图

修改视图是指修改数据库中存在的视图的定义。例如当基本表的某些字段发生变化时，视图必须修改后才能正常使用。

在MySQL中，修改视图的方式有2种，分别为替换已有的视图和使用ALTER VIEW语句修改视图这两种方式。

1，替换已有的视图

CREATE OR REPLACE VIEW view_name [column_list] AS select_statement [WITH [CASCADED | LOCAL]] CHECK OPTION;

语法：通过CREATE OR REPLACE VIEW语句可以在创建视图时替换已有的同名视图，如果视图不存在，则进行创建一个视图操作。

为了更好的理解通过替换已有的视图来修改视图，示例如下：

从上述操作中，通过CREATE OR REPLACE VIEW语句可以在创建视图时替换已有的同名视图，如果视图不存在，则进行创建一个视图操作。

2，使用ALTER VIEW语句修改

ALTER [ALGORITHM][DEFINER][SQL SECURITY] VIEW view_name [column_list] 
AS select_statement [WITH [CASCADED | LOCAL]] CHECK OPTION;

语法：ALTER后面的各部分子句与CREATE VIEW语句中的含义相同。

为了更好的理解通过使用ALTER VIEW语句修改视图，示例如下：

2.4，删除视图

当视图不再需要时，可以将其删除，在删除视图时不会删除基本表中的数据。

删除单个视图或多个视图都是通过使用DROP VIEW语句，基本语法如下：

#删除单个视图
DROP VIEW [IF EXISTS] view_name;

#删除多个视图
DROP VIEW [IF EXISTS] view_name,view_name...;

为了更好的理解视图的删除操作，示例如下：

在上述操作中，删除单个或者多个视图操作中加上IF EXISTS避免了视图不存在而发生报错的情况，而视图名之间用逗号隔开。而这个视图的删除操作只会影响视图本身，不会影响对应的基表。

3，视图数据操作

视图数据操作就是通过视图来进行查询，添加，修改或删除基本表的数据。因为视图是一个虚拟存在的表，不保存数据，当对视图数据进行操作时，就相当于对基本表数据进行操作。

3.1，添加数据

通过视图向基本表添加数据时，可以使用INSERT INTO语句完成数据的添加操作。

INSERT INTO view_name VALUES(column_value,...);

为了更好的理解视图数据的添加操作，示例如下：

在上述视图进行添加数据时，与之对应的基表的数据会发生变化，数据数添加。如果添加的数据与视图的创建条件相符合，则添加的数据就可以在视图中显示出来，如果不符合，就不可以在视图中显示，但要记住一点，不管添加的数据是否符合视图的创建条件，只要在创建视图时没有加上WITH CASCADED CHECK OPTION 或者WITH LOCAL CHECK OPTION检查条件，这个数据都会在对应的基表中添加并显示。

还有就是，直接对数据表进行数据添加操作，如果添加的数据符合视图的创建条件，则添加的数据也能够在视图中显示，如果不符合，就不显示出来。

添加数据的操作本质为：当对视图数据进行添加操作时，就相当于对基本表数据进行添加操作。

3.2，修改数据

通过视图修改基本表数据时，可以使用UPDATE语句完成数据的修改操作。

#修改单个字段数据:
UPDATE view_name SET column_name = column_value [WHERE 条件表达式];

#修改多个字段数据:
UPDATE view_name SET column_name = column_value,column_name = column_value,... [WHERE 条件表达式];

为了更好的理解视图数据的修改操作，示例如下：

在对上述视图进行修改数据操作时，如果修改后的数据不符合视图的创建条件，就不会在视图中显示，不要因此就认为该数据记录消失了，其实该数据还是存在于基表中，并完成了对应的修改操作，只是不在视图中显示罢了。

修改数据的操作本质为：当对视图数据进行修改数据操作时，就相当于对基本表数据进行修改数据操作。

3.3，删除数据

通过视图删除基本表数据时，可以使用DELETE语句完成数据的删除操作。

DELETE FROM view_name [WHERE 条件表达式];

为了更好的理解视图数据的删除操作，示例如下：

在上述视图中进行删除数据操作，视图中符合条件的数据记录和其与之对应的基表的数据记录都会删除。

删除数据的操作本质为：当对视图数据进行删除操作时，就相当于对基本表数据进行删除操作。

3.4，扩展知识

在进行视图数据操作时，如果遇到如下几种情况，操作可能会失败。

1，所操作的视图定义在多个数据表上

2，未满足视图基本表对字段的约束条件

3，在定义的SELECT语句后的字段列表中使用了数学表达式或者聚合函数。

4，在定义的SELECT语句中使用了DISTINCT，UNION，TOP，GROUP BY或HAVING子句

4，视图检查条件

在创建视图的语法格式中，WITH CHECK OPTION子句用于视图数据操作时进行条件检查，分为级联检查和非级联检查。

4.1，级联检查

在创建视图时，使用CASCADED进行级联检查，系统默认，操作数据时满足所有有关视图和表定义的条件，如不满足，则操作数据失败。

为了更好的理解视图的级联检查操作，示例如下：

操作说明：首先依赖student数据表创建满足成绩大于92的学生信息的视图view_1，无检查条件；再依赖于view_1视图创建满足成绩低于96的学生信息的视图view_2，存在检查条件CASCADED；然后在对视图view_2添加符合成绩在92到96之间的数据记录，数据记录添加成功；最后在往视图view_2添加不符合成绩在92到96之间的数据记录，数据记录添加失败，系统报错。

因此，在创建视图时，若使用CASCADED进行级联检查，操作数据要满足所有有关视图和表定义的条件，如不满足，则操作数据失败。

4.2，非级联检查

在创建视图时，使用LOCAL进行非级联检查，操作数据时只需要满足该视图本身定义的条件，如不满足，则操作数据失败。

为了更好的理解视图的非级联检查操作，示例如下：

操作说明：首先依赖student数据表创建满足成绩大于92的学生信息的视图view_1，无检查条件；再依赖于view_1视图创建满足成绩低于96的学生信息的视图view_3，存在检查条件LOCAL；然后在对视图view_3添加满足成绩小于96，但不大于92的数据记录，数据记录添加成功；最后在往视图view_2添加不满足成绩小于96的数据记录，数据记录添加失败，系统报错。

因此，在创建视图时，若使用LOCAL进行非级联检查，操作数据时只需要满足该视图本身定义的条件，如不满足，则操作数据失败。