十六、MySQL 视图详解

Amo Xiang

已于 2023-04-06 00:08:41 修改

阅读量1.9k

点赞数 12

分类专栏： MySQL8.0从菜鸟到大牛文章标签： mysql 大数据数据库

于 2023-04-05 20:14:58 首次发布

本文链接：https://blog.csdn.net/xw1680/article/details/129976117

版权

MySQL8.0从菜鸟到大牛专栏收录该内容

25 篇文章 87 订阅

订阅专栏

文章目录

一、常见的数据库对象
二、视图概述
三、视图常用操作
四、总结
- 4.1 视图优点
- 4.2 视图不足

前置知识：

一、数据库开发与实战专栏导学及数据库基础概念入门
 二、MySQL 介绍及 MySQL 安装与配置
 三、MySQL 数据库的基本操作
 四、MySQL 存储引擎及数据类型
 五、数据导入与基本的 SELECT 语句
 六、MySQL 数据库练习题1(包含前5章练习题目及答案)
七、MySQL 多表查询详解(附练习题及答案----超详细)
八、MySQL 常用函数汇总(1)
九、MySQL 常用函数汇总(2)
十、MySQL 聚合函数、分组查询及过滤分组
 十一、子查询详解
 十二、创建和管理表
 十三、表数据的增、删、改操作
 十四、MySQL 约束详解
 十五、Python 操作 MySQL 数据库------非ORM

视图是从一个或多个表中导出的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用查看整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。本文将介绍视图的概念和作用，视图定义的原则和创建视图的方法，以及查看视图、修改视图、更新视图和删除视图的方法等。本章知识架构及重难点如下：

一、常见的数据库对象

对象	描述
表(TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束 (CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图(VIEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引(INDEX)	用于提高查询性能，相当于书的目录
存储过程 (PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数 (FUNCTION)	用于完成一次特定的计算，具有一个返回值
触发器 (TRIGGER)	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

二、视图概述

视图是由数据库中的一个或多个表导出的虚拟表，方便用户对数据的操作。本小节将详细讲解视图的概念及作用。
在这里插入图片描述

2.1 为什么要使用视图?

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

2.2 视图的理解

视图是一个 虚拟表，是从数据库中的一个或多个表中导出来的表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而没有存放视图中的数据，占用很少的内存空间，它是 SQL 中的一个重要概念。

视图建立在已有表的基础上，视图赖以建立的这些表称为 基表。

视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为 存储起来的 SELECT 语句。

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

2.3 视图的作用

对所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过视图修改数据时的限制也很少。视图的作用可归纳为如下几点。

1、简单性。 看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。可以将经常使用的查询定义为视图，这样用户就不必为以后的操作每次指定全部的条件。

2、安全性。 视图的安全性可以防止未授权用户查看特定的行或列，使有权限用户只能看到表中特定行的方法如下。

在表中增加一个标志用户名的列
建立视图，使用户只能看到标有自己用户名的行
把视图授权给其他用户

3、逻辑数据独立性。 视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。

如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序不动。
如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。
如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序不动。
如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而使数据库不动。

三、视图常用操作

创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一个表中，也可以建立在多个表中。本小节主要讲解视图的一些常用操作。

3.1 查看创建视图的权限

创建视图需要具有 CREATE VIEW 的权限，同时应该具有查询涉及的列的 SELECT 权限。可以使用 SELECT 语句来查询这些权限信息，查询语法如下：

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE USER='用户名';

参数说明如下：

Select_priv 表示用户是否具有 SELECT 权限，Y 表示拥有 SELECT 权限，N 表示没有。
Create_view_priv 表示用户是否具有 CREATE VIEW 权限。
mysql.user 表示 MySQL 数据库下面的 user 表。
用户名 表示要查询是否拥有权限的用户，该参数需要用单引号引起来。

查询 MySQL 中 root 用户是否具有创建视图的权限：

mysql> SELECT Select_priv,Create_view_priv FROM mysql.user WHERE USER='root';
+-------------+------------------+
| Select_priv | Create_view_priv |
+-------------+------------------+
| Y           | Y                |
+-------------+------------------+
1 row in set (0.00 sec)

3.2 创建视图

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

CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名称 [(字段列表)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
-- ALGORITHM: 可选参数，表示视图选择的算法
-- 视图名: 表示要创建的视图名称
-- 字段列表: 可选参数，指定视图中各个属性的名词，默认情况下与SELECT语句中查询的属性相同
-- SELECT语句：一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中
-- WITH CHECK OPTION: 可选参数，表示更新视图时要保证在该视图的权限范围之内

举例1：

mysql> CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id=80;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM empvu80 LIMIT 5;
+-------------+-----------+----------+
| employee_id | last_name | salary   |
+-------------+-----------+----------+
|         145 | Russell   | 14000.00 |
|         146 | Partners  | 13500.00 |
|         147 | Errazuriz | 12000.00 |
|         148 | Cambrault | 11000.00 |
|         149 | Zlotkey   | 10500.00 |
+-------------+-----------+----------+
5 rows in set (0.00 sec)

查询视图：

举例2：视图中的字段在基表中可能没有对应的字段

CREATE VIEW emp_year_salary(ename,year_salary) AS SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) FROM employees;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM emp_year_salary LIMIT 3;
+---------+-------------+
| ename   | year_salary |
+---------+-------------+
| King    |   288000.00 |
| Kochhar |   204000.00 |
| De Haan |   204000.00 |
+---------+-------------+
3 rows in set (0.00 sec)

举例3：

mysql> CREATE VIEW empvu801 AS SELECT employee_id e_id, last_name e_name, salary e_salary FROM employees;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM empvu801 LIMIT 3;
+------+---------+----------+
| e_id | e_name  | e_salary |
+------+---------+----------+
|  100 | King    | 24000.00 |
|  101 | Kochhar | 17000.00 |
|  102 | De Haan | 17000.00 |
+------+---------+----------+
3 rows in set (0.00 sec)

举例4：多表。

mysql> CREATE VIEW vu_emp_dept
    -> AS
    -> SELECT e.employee_id,e.department_id,d.department_name
    -> FROM employees e JOIN departments d
    -> ON e.`department_id` = d.`department_id`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM vu_emp_dept LIMIT 3;
+-------------+---------------+-----------------+
| employee_id | department_id | department_name |
+-------------+---------------+-----------------+
|         200 |            10 | Administration  |
|         201 |            20 | Marketing       |
|         202 |            20 | Marketing       |
+-------------+---------------+-----------------+
3 rows in set (0.00 sec)

举例5：利用视图对数据进行格式化。

mysql> CREATE VIEW vu_emp_dept1
    -> AS
    -> SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
    -> FROM employees e JOIN departments d
    -> ON e.`department_id` = d.`department_id`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM vu_emp_dept1 LIMIT 3;
+------------------------+
| emp_info               |
+------------------------+
| Whalen(Administration) |
| Hartstein(Marketing)   |
| Fay(Marketing)         |
+------------------------+
3 rows in set (0.00 sec)

当我们创建好一张视图之后，还可以在它的基础上继续创建视图。举例6：

mysql> CREATE VIEW test AS SELECT last_name,salary FROM empvu80;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test LIMIT 3;
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| Russell   | 14000.00 |
| Partners  | 13500.00 |
| Errazuriz | 12000.00 |
+-----------+----------+
3 rows in set (0.00 sec)

3.3 查看视图

语法1：查看数据库的表对象、视图对象

mysql> SHOW TABLES;

语法2：查看视图的结构

-- DESC / DESCRIBE 视图名称;
mysql> DESC test;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| last_name | varchar(25) | NO   |     | NULL    |       |
| salary    | double(8,2) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

语法3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等） 
SHOW TABLE STATUS LIKE '视图名称'\G;
mysql> SHOW TABLE STATUS LIKE 'test'\G;
*************************** 1. row ***************************
           Name: test
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2023-04-05 16:08:13
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

ERROR:
No query specified
-- 执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表

语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;
mysql> SHOW CREATE VIEW test\G;

3.4 更新视图的数据

3.4.1 一般情况

MySQL 支持使用 INSERT、UPDATE 和 DELETE 语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。举例：UPDATE 操作

mysql> SELECT first_name,salary FROM employees WHERE employee_id = 143;
+------------+---------+
| first_name | salary  |
+------------+---------+
| Randall    | 2600.00 |
+------------+---------+
1 row in set (0.00 sec)

mysql> CREATE VIEW test20230405 AS SELECT first_name,salary FROM employees WHERE employee_id = 143;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test20230405;
+------------+---------+
| first_name | salary  |
+------------+---------+
| Randall    | 2600.00 |
+------------+---------+
1 row in set (0.00 sec)

mysql> UPDATE employees SET salary=2700.00 WHERE employee_id = 143;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test20230405;
+------------+---------+
| first_name | salary  |
+------------+---------+
| Randall    | 2700.00 |
+------------+---------+
1 row in set (0.00 sec)

举例：DELETE 操作：

mysql> SELECT first_name,salary FROM employees WHERE employee_id = 143;
+------------+---------+
| first_name | salary  |
+------------+---------+
| Randall    | 2600.00 |
+------------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test20230405;
+------------+---------+
| first_name | salary  |
+------------+---------+
| Randall    | 2700.00 |
+------------+---------+
1 row in set (0.00 sec)

mysql> DELETE FROM test20230405;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT first_name,salary FROM employees WHERE employee_id = 143;
Empty set (0.00 sec)

3.4.2 不可更新的视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

1、在定义视图的时候指定了 ALGORITHM = TEMPTABLE，视图将不支持 INSERT 和 DELETE 操作；

CREATE ALGORITHM=TEMPTABLE VIEW book_view1 AS SELECT * FROM tb_book;

2、视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持 INSERT 操作；
3、在定义视图的 SELECT 语句中使用了 JOIN 联合查询，视图将不支持 INSERT 和 DELETE 操作；
4、在定义视图的 SELECT 语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也不支持 UPDATE 使用了数学表达式、子查询的字段值；

 CREATE VIEW book_view1(a_sort)  AS SELECT (SELECT name FROM tb_book);

5、在定义视图的 SELECT 语句后的字段列表中使用 DISTINCT、聚合函数、GROUP BY、HAVING、UNION 等，视图将不支持 INSERT、UPDATE、DELETE；

CREATE VIEW book_view1(a_sort,a_book) AS SELECT sort,books, FROM tb_book GROUP BY id;
CREATE VIEW book_view1(a_sort,a_book) AS SELECT sort,books, COUNT(name) FROM tb_book;

6、在定义视图的 SELECT 语句中包含了子查询，而子查询中引用了 FROM 后面的表，视图将不支持 INSERT、UPDATE、DELETE；
7、视图定义基于一个 不可更新视图 ；

CREATE VIEW book_view1 AS SELECT * FROM book_view2;

8、常量视图。

CREATE VIEW book_view1 AS SELECT 'Aric' as a_book;

上面的几种情况其实就是一种情况，规则就是，视图的数据和基本表的数据不一样了。

虽然可以更新视图中数据，但是有很多限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，可能会造成数据更新失败。

3.5 修改、删除视图

方式1：使用 CREATE OR REPLACE VIEW 子句修改视图：

mysql> desc empvu80;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int         | NO   |     | 0       |       |
| last_name   | varchar(25) | NO   |     | NULL    |       |
| salary      | double(8,2) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)
    -> AS
    -> SELECT
    -> employee_id, first_name || ' ' || last_name, salary, department_id
    -> FROM employees
    -> WHERE department_id = 80;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> desc empvu80;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id_number     | int         | NO   |     | 0       |       |
| name          | int         | YES  |     | NULL    |       |
| sal           | double(8,2) | YES  |     | NULL    |       |
| department_id | int         | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

说明：CREATE VIEW 子句中各列的别名应和子查询中各列相对应。方式2：ALTER VIEW 修改视图的语法是：

ALTER VIEW 视图名称 AS 查询语句
mysql> desc empvu80;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id_number     | int         | NO   |     | 0       |       |
| name          | int         | YES  |     | NULL    |       |
| sal           | double(8,2) | YES  |     | NULL    |       |
| department_id | int         | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id=80;
Query OK, 0 rows affected (0.00 sec)

mysql> desc empvu80;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int         | NO   |     | 0       |       |
| last_name   | varchar(25) | NO   |     | NULL    |       |
| salary      | double(8,2) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除视图： 删除视图只是删除视图的定义，并不会删除基表的数据。删除视图的语法是：

DROP VIEW IF EXISTS 视图名称;
-- 示例: 
mysql> DROP VIEW empvu80;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP VIEW empvu80;
ERROR 1051 (42S02): Unknown table 'mysql_study.empvu80'
mysql> DROP VIEW IF EXISTS empvu80;
Query OK, 0 rows affected, 1 warning (0.00 sec)

说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。

四、总结

4.1 视图优点

1、操作简单。 将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

2、减少数据冗余。 视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3、数据安全。 MySQL 将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性 。视图相当于在用户和实际的数据表之间加了一层虚拟表。
请添加图片描述
同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4、适应灵活多变的需求。 当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

5、能够分解复杂的查询逻辑。 数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

小结：视图存在主要出于两个原因：首先是安全原因，视图可以隐藏一些数据，例如，它可以用视图显示员工信息表中的姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是它可使复杂的查询易于理解和使用。

4.2 视图不足

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。 特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。实际项目中，如果视图过多，会导致数据库维护成本的问题。所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。

Amo Xiang

关注

12
点赞
踩
30

收藏

觉得还不错? 一键收藏
打赏
4
评论
十六、MySQL 视图详解

本章对MySQL数据库中视图的概念和作用进行了详细讲解，并且讲解了创建视图、查看视图、修改视图、更新视图和删除视图的方法。创建视图和修改视图是本章的重点内容，并且需要在计算机上实际操作。读者在创建视图和修改视图后，一定要查看视图的结构，以确保创建和修改的操作正确。更新视图是本章的一个难点，因为实际中存在一些造成视图不能更新的因素，希望读者在练习中认真分析。
复制链接

扫一扫