十六、MySQL 视图详解

文章目录

前置知识：

一、数据库开发与实战专栏导学及数据库基础概念入门
二、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.userWHEREUSER='用户名';

参数说明如下：

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

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

mysql>SELECT Select_priv,Create_view_priv FROM mysql.userWHEREUSER='root';+-------------+------------------+| Select_priv | Create_view_priv |+-------------+------------------+| Y           | Y                |+-------------+------------------+1rowinset(0.00 sec)

3.2 创建视图

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

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

举例1：

mysql>CREATEVIEW empvu80 ASSELECT employee_id, last_name, salary FROM employees WHERE department_id=80;
Query OK,0rows affected (0.00 sec)

mysql>SELECT*FROM empvu80 LIMIT5;+-------------+-----------+----------+| 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|+-------------+-----------+----------+5rowsinset(0.00 sec)

查询视图：

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

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

mysql>SELECT*FROM emp_year_salary LIMIT3;+---------+-------------+| ename   | year_salary |+---------+-------------+| King    |288000.00|| Kochhar |204000.00|| De Haan |204000.00|+---------+-------------+3rowsinset(0.00 sec)

举例3：

mysql>CREATEVIEW empvu801 ASSELECT employee_id e_id, last_name e_name, salary e_salary FROM employees;
Query OK,0rows affected (0.00 sec)

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

举例4：多表。

mysql>CREATEVIEW 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,0rows affected (0.00 sec)

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

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

mysql>CREATEVIEW 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,0rows affected (0.00 sec)

mysql>SELECT*FROM vu_emp_dept1 LIMIT3;+------------------------+| emp_info               |+------------------------+| Whalen(Administration)|| Hartstein(Marketing)|| Fay(Marketing)|+------------------------+3rowsinset(0.00 sec)

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

mysql>CREATEVIEW test ASSELECT last_name,salary FROM empvu80;
Query OK,0rows affected (0.00 sec)

mysql>SELECT*FROM test LIMIT3;+-----------+----------+| last_name | salary   |+-----------+----------+| Russell   |14000.00|| Partners  |13500.00|| Errazuriz |12000.00|+-----------+----------+3rowsinset(0.00 sec)

3.3 查看视图

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

mysql>SHOWTABLES;

语法2：查看视图的结构

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

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

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等） SHOWTABLESTATUSLIKE'视图名称'\G;
mysql>SHOWTABLESTATUSLIKE'test'\G;***************************1.row***************************
           Name: test
         Engine: NULL
        Version: NULL
     Row_format: NULLRows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULLAuto_increment: NULL
    Create_time: 2023-04-0516:08:13
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULLComment: VIEW1rowinset(0.00 sec)

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

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

SHOWCREATEVIEW 视图名称;
mysql>SHOWCREATEVIEW 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|+------------+---------+1rowinset(0.00 sec)

mysql>CREATEVIEW test20230405 ASSELECT first_name,salary FROM employees WHERE employee_id =143;
Query OK,0rows affected (0.00 sec)

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

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

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

举例：DELETE 操作：

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

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

mysql>DELETEFROM test20230405;
Query OK,1row 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 操作；

CREATEALGORITHM=TEMPTABLEVIEW book_view1 ASSELECT*FROM tb_book;

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

CREATEVIEW book_view1(a_sort)ASSELECT(SELECT name FROM tb_book);

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

CREATEVIEW book_view1(a_sort,a_book)ASSELECT sort,books,FROM tb_book GROUPBY id;CREATEVIEW book_view1(a_sort,a_book)ASSELECT sort,books,COUNT(name)FROM tb_book;

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

CREATEVIEW book_view1 ASSELECT*FROM book_view2;

8、常量视图。

CREATEVIEW book_view1 ASSELECT'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||+-------------+-------------+------+-----+---------+-------+3rowsinset(0.00 sec)

mysql>CREATEORREPLACEVIEW 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,0rows affected,2warnings(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||+---------------+-------------+------+-----+---------+-------+4rowsinset(0.00 sec)

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

ALTERVIEW 视图名称 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||+---------------+-------------+------+-----+---------+-------+4rowsinset(0.00 sec)

mysql>ALTERVIEW empvu80 ASSELECT employee_id, last_name, salary FROM employees WHERE department_id=80;
Query OK,0rows 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||+-------------+-------------+------+-----+---------+-------+3rowsinset(0.00 sec)

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

DROPVIEWIFEXISTS 视图名称;-- 示例: 
mysql>DROPVIEW empvu80;
Query OK,0rows affected (0.01 sec)

mysql>DROPVIEW empvu80;
ERROR 1051(42S02): Unknown table'mysql_study.empvu80'
mysql>DROPVIEWIFEXISTS empvu80;
Query OK,0rows 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 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。实际项目中，如果视图过多，会导致数据库维护成本的问题。所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。