当前位置：首页 > news >正文

MySQL中的视图

news 来源：原创 2024/5/20 11:39:09

在实际开发中，有时候为了保障数据的安全性和提高查询效率，希望创建一个只包含指定字段数据的虚拟表给用户使用，此时可以使用视图。视图在数据库中的作用类似于窗户，用户通过这个窗口只能看到指定的数据。接下来对视图进行详细讲解。

视图的概述

视图是一种虚拟存在的表，并不在数据库中实际存在，它的数据依赖真实存在的数据表。通过视图不仅可以看到其依赖数据表中的数据，还可以像操作数据表一样，对数据表中的数据进行添加、修改和删除。与直接操作数据表相比，视图具有以下优点。

1.简化查询语句

视图不仅可以简化用户对数据的理解，也可以简化对数据的操作。例如，日常开发需要经常使用一个比较复杂的语句进行查询，此时就可以将该查询语句定义为视图，从而避免大量重复且复杂的操作。

2.安全性

数据库授权命令可以将每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定列上。通过视图，可以更加方便地进行权限控制，使特定用户只能查询和修改指定的数据，而无法查看和修改数据库中的其他数据。

3.逻辑数据独立性

视图可以帮助用户屏蔽数据表结构变化带来的影响，例如，数据表增加字段不会影响基于该数据表查询出数据的视图。

视图管理

视图管理包括创建、查看、修改和删除视图。下面分别对视图的这几种视图管理进行讲解。

创建视图

在MySQL中，可以使用CREATE VIEW语句创建视图。创建视图的基本语法格式如下。

CREATE [OR REPLACE] VIEW 视图名 [(字段列表)] AS select_statement

关于上述语法格式的具体介绍如下。

[OR REPLACE]：可选参数，表示若数据库中已经存在这个名称的视图就替换原有的视图，若不存在则创建视图。
视图名：表示要创建的视图名称，该名称在数据库中必须是唯一的，不能与其他数据表或视图同名。
select statement:指一个完整的SELECT语句，表示从某个数据表或视图中查出满足条件的记录，将这些记录导入视图中。一般将SELECT语句所涉及的数据表称为视图的基本表。

视图的基本表可以是一张数据表，也可以是多张数据表。下面分别以视图的基本表为单表和多表这两种情况，通过案例演示如何创建视图。

1.基于单表创建视图。

例如，公司想要组建一个开发小组，开发一个资源管理系统，供各部门上传共享资源。该系统需要根据员工工号empno、员工姓名ename、职位job和部门编号deptno进行账户管理和权限授予。如果将操作员工表的权限直接交给该开发小组，会造成部分敏感信息泄露。此时数据库管理员可以将员工工号empno、员工姓名ename、职位job和部门编号deptmo查询出来创建视图view_emp，供该开发小组使用。具体SQL语句及执行结果如下所示。

先声明一下，这个是引用之前创建的员工表emp，看过前面的文章的应该都有印象。

mysql> CREATE VIEW view_emp AS SELECT empno,ename,job,deptno FROM emp;
Query OK, 0 rows affected (0.01 sec)

由上述SQL语句执行结果可以看出，CREATE VIEW语句成功执行。默认情况下，创建的视图中的字段名称和基于查询的数据表的字段名称是一样的。下面使用SELECT语句查看view_emp视图，查询语句及执行结果如下。

mysql> SELECT * FROM view_emp;
+-------+-----------+-----------+--------+
| empno | ename     | job       | deptno |
+-------+-----------+-----------+--------+
|   911 | 王五      | 分析员    |     20 |
|   935 | 陈十一    | 经理      |     10 |
|   936 | 吴九      | 销售      |     30 |
|   951 | 郑十      | 销售      |     30 |
|   952 | 周八      | 销售      |     30 |
|   969 | 萧二      | 保洁      |     30 |
|   985 | 刘一      | 董事长    |     10 |
|   991 | 赵六      | 分析员    |     20 |
|   994 | 孙七      | 销售      |     30 |
+-------+-----------+-----------+--------+
9 rows in set (0.00 sec)

从执行结果可以看出，创建的视图view_emp的字段名称和数据表emp的字段名称是一样的。

视图的字段名称可以使用基本表的字段名称，但也可以根据实际的需求自定义视图字段的名称。例如，数据库管理员觉得将数据表的真实字段名称在视图中暴露不太安全，想要创建一个新的视图view_emp2给开发小组使用。视图view_emp2中包含的字段和视图view_emp相同，但视图view_emp2中的字段名称和员工表中的字段名称不一致，具体创建语句及执行结果如下。

mysql> CREATE VIEW view_emp2(e_no,e_name,e_job,e_dep) AS SELECT empno,ename,job,deptno FROM emp;
Query OK, 0 rows affected (0.00 sec)

由上述SQL语句执行结果可以看出，CREATE VIEW语句执行成功。下面使用SELECT语句查看一下。

mysql> SELECT * FROM view_emp2;
+------+-----------+-----------+-------+
| e_no | e_name    | e_job     | e_dep |
+------+-----------+-----------+-------+
|  911 | 王五      | 分析员    |    20 |
|  935 | 陈十一    | 经理      |    10 |
|  936 | 吴九      | 销售      |    30 |
|  951 | 郑十      | 销售      |    30 |
|  952 | 周八      | 销售      |    30 |
|  969 | 萧二      | 保洁      |    30 |
|  985 | 刘一      | 董事长    |    10 |
|  991 | 赵六      | 分析员    |    20 |
|  994 | 孙七      | 销售      |    30 |
+------+-----------+-----------+-------+
9 rows in set (0.00 sec)

从执行结果可以看出，虽然 view_emp和view_emp2两个视图中的字段名称不同，但是数据却是相同的，这是因为这两个视图引用的是同一个数据表中的数据。在实际开发中，用户可以根据自己的需要，通过视图获取基本表中需要的数据，这样既能满足用户的需求，也不需要破坏基本表原来的结构，从而保证了基本表中数据的安全性。

2.基于多表创建视图。

创建视图。在MySQL中，除了可以在单表上创建视图，还可以在两个或两个以上的数据表上创建视图

例如，开发小组开发资源管理系统时，需要使用公司ems数据库信息。中员工编号empno、员工姓名ename、职位job、部门编号deptno和部门名称dname的信息。

下面根据需求创建视图view_emp3,具体SQL语句及执行结果如下。

mysql> CREATE VIEW view_emp3(e_no,e_name,e_job,e_dep,e_deptname) AS SELECT e.empno,e.ename,e.job,e.deptno,d.dname FRO
M emp e LEFT JOIN dept d ON e.deptno=d.deptno;
Query OK, 0 rows affected (0.01 sec)

由上述SQL语句执行结果可以看出，视图view_emp3成功创建。下面使用sELECT语句查看view_emp3t视图，查询语句及执行结果如下。

mysql> SELECT * FROM view_emp3;
+------+-----------+-----------+-------+------------+
| e_no | e_name    | e_job     | e_dep | e_deptname |
+------+-----------+-----------+-------+------------+
|  911 | 王五      | 分析员    |    20 | 研究院     |
|  935 | 陈十一    | 经理      |    10 | 裁决室     |
|  936 | 吴九      | 销售      |    30 | 销售部     |
|  951 | 郑十      | 销售      |    30 | 销售部     |
|  952 | 周八      | 销售      |    30 | 销售部     |
|  969 | 萧二      | 保洁      |    30 | 销售部     |
|  985 | 刘一      | 董事长    |    10 | 裁决室     |
|  991 | 赵六      | 分析员    |    20 | 研究院     |
|  994 | 孙七      | 销售      |    30 | 销售部     |
+------+-----------+-----------+-------+------------+
9 rows in set (0.01 sec)

在上述执行结果中，视图view_emp3中的字段名称和数据表emp及数据表dept中的字段名称不一致，但是字段值和数据表中的数据是一致的。

查看视图

查询视图的字段信息

创建好了视图，我们就能去查看我们所创建的视图，怎么查看呢？当然也是有查看视图的语句了：

DESCRIBE 视图名;或者简写为 DESC视图名;

这个和查看普通的数据表是一样的查询语句。

上面创建了三个视图，我们来使用DESC语句来查看一下：

mysql> DESC view_emp3;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| e_no       | int         | NO   |     | NULL    |       |
| e_name     | varchar(20) | NO   |     | NULL    |       |
| e_job      | varchar(20) | NO   |     | NULL    |       |
| e_dep      | int         | YES  |     | NULL    |       |
| e_deptname | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

对这个表格我就不在详细解释了，相信大家都已经熟悉了。

查看视图的状态信息

在MySQL中，我们可以使用SHOW TABLE STATUS语句来查看表的状态信息，其基本语法格式如下：

SHOW TABLE STATUS LIKE '视图名';

在上述语法格式中，LIKE表示后面匹配的是字符串，'视图名'表示创建的视图的名称，注意要用引号包裹。

下面来用此语法查看view_emp3的视图信息：

mysql> SHOW TABLE STATUS LIKE 'view_emp3' \G;
*************************** 1. row ***************************Name: view_emp3Engine: NULLVersion: NULLRow_format: NULLRows: NULLAvg_row_length: NULLData_length: NULL
Max_data_length: NULLIndex_length: NULLData_free: NULLAuto_increment: NULLCreate_time: 2024-03-03 15:51:44Update_time: NULLCheck_time: NULLCollation: NULLChecksum: NULLCreate_options: NULLComment: VIEW
1 row in set (0.00 sec)

上面显示的就是视图的信息。

查看视图的创建语句

在MySQL中，查看视图的创建语句和查看表的创建语句是一样的，只不过是把表的名字换为视图的名字：

SHOW CREATE view 视图名;

下面根据此语法查看view_emp3的视图创建信息：

mysql> SHOW CREATE VIEW view_emp3;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View      | Create View| character_set_client | collation_connection |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_emp3 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_emp3` (`e_no`,`e_name`,`e_job`,`e_dep`,`e_deptname`) AS select `e`.`empno` AS `empno`,`e`.`ename` AS `ename`,`e`.`job` AS `job`,`e`.`deptno` AS `deptno`,`d`.`dname` AS `dname` from (`emp` `e` left join `dept` `d` on((`e`.`deptno` = `d`.`deptno`))) | utf8                 | utf8_general_ci      |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

从上面的信息中我们可以查到视图的名称，创建语句、字符串编码等信息。

修改视图

视图的修改指的是修改数据库中存在的视图的定义，当视图的基本表中的字段发生变化时，需要对视图进行修改以确保查询的正确性。

在MySQL中，修改视图的方法有两种;

1.是使用CREATE OR REPLACE VIEW语句修改视图，其基本语法格式如下;

CREATE OR REPLACE VIEW 视图名 AS SELECT语句;

使用此语句时，要求被修改的视图要存在，不存在则会创建一个新的视图。

例如，我们想要修改view_emp3视图中的e_name字段名为e.na，并新增员工上级工号的字段。

先使用SQL语句查询一下这个视图的信息;

mysql> DESC view_emp3;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| e_no       | int         | NO   |     | NULL    |       |
| e_name     | varchar(20) | NO   |     | NULL    |       |
| e_job      | varchar(20) | NO   |     | NULL    |       |
| e_dep      | int         | YES  |     | NULL    |       |
| e_deptname | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

修改视图的语句：

mysql> CREATE OR REPLACE VIEW view_emp3(e_no,e_na,e_job,e_mgr,e_dep,e_deptname) AS SELECT e.e
mpno,e.ename,e.job,e.mgr,e.deptno,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;
Query OK, 0 rows affected (0.01 sec)

视图修改成功，那么查询结果为；

mysql> DESC view_emp3;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| e_no       | int         | NO   |     | NULL    |       |
| e_na       | varchar(20) | NO   |     | NULL    |       |
| e_job      | varchar(20) | NO   |     | NULL    |       |
| e_mgr      | int         | YES  |     | NULL    |       |
| e_dep      | int         | YES  |     | NULL    |       |
| e_deptname | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

从结果中可以看到字段e_name更改为了e_na，且多了一个e_mgr字段，说明视图成功修改。

使用SELECT语句查看视图中的信息：

mysql> SELECT * FROM view_emp3;
+------+-----------+-----------+-------+-------+------------+
| e_no | e_na      | e_job     | e_mgr | e_dep | e_deptname |
+------+-----------+-----------+-------+-------+------------+
|  911 | 王五      | 分析员    |  9866 |    20 | 研究院     |
|  935 | 陈十一    | 经理      |  9839 |    10 | 裁决室     |
|  936 | 吴九      | 销售      |  9698 |    30 | 销售部     |
|  951 | 郑十      | 销售      |  9698 |    30 | 销售部     |
|  952 | 周八      | 销售      |  9698 |    30 | 销售部     |
|  969 | 萧二      | 保洁      |  9698 |    30 | 销售部     |
|  985 | 刘一      | 董事长    |  NULL |    10 | 裁决室     |
|  991 | 赵六      | 分析员    |  9566 |    20 | 研究院     |
|  994 | 孙七      | 销售      |  9698 |    30 | 销售部     |
+------+-----------+-----------+-------+-------+------------+
9 rows in set (0.00 sec)

2.使用ALTER语句修改视图：

ALTER语句是MySQL另一种修改视图的方法，其基本语法格式如下：

ALTER VIEW 视图名 AS SELECT语句;

下面通过案例修改视图：

例如，我们觉得e_mgr字段无用，想要删除，具体语句及执行结果如下;

mysql> ALTER VIEW view_emp3(e_no,e_na,e_job,e_dep,e_deptname) AS SELECT e.empno,e.ename,e.job,e.deptno,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;
Query OK, 0 rows affected (0.01 sec)

修改语句执行成功，接下来使用DESC语句查看视图内容是否成功更改：

mysql> DESC view_emp3;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| e_no       | int         | NO   |     | NULL    |       |
| e_na       | varchar(20) | NO   |     | NULL    |       |
| e_job      | varchar(20) | NO   |     | NULL    |       |
| e_dep      | int         | YES  |     | NULL    |       |
| e_deptname | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

从上述语句中可以看到视图内容修改成功。

再使用SELECT语句查看视图数据;

mysql> SELECT * FROM view_emp3;
+------+-----------+-----------+-------+------------+
| e_no | e_na      | e_job     | e_dep | e_deptname |
+------+-----------+-----------+-------+------------+
|  911 | 王五      | 分析员    |    20 | 研究院     |
|  935 | 陈十一    | 经理      |    10 | 裁决室     |
|  936 | 吴九      | 销售      |    30 | 销售部     |
|  951 | 郑十      | 销售      |    30 | 销售部     |
|  952 | 周八      | 销售      |    30 | 销售部     |
|  969 | 萧二      | 保洁      |    30 | 销售部     |
|  985 | 刘一      | 董事长    |    10 | 裁决室     |
|  991 | 赵六      | 分析员    |    20 | 研究院     |
|  994 | 孙七      | 销售      |    30 | 销售部     |
+------+-----------+-----------+-------+------------+
9 rows in set (0.00 sec)

从结果可以看到视图中不在有e_mgr字段的数据，说明我们修改成功了。

删除视图

当我们不使用视图时，可以将其删除。注意删除视图只会删除我们创建的视图，不会删除基本表中的数据。

删除语句如下：

DROP VIEW 视图名;

例如：我们不使用view_emp3这个视图了，那就把这个视图删除了,具体语句及执行结果如下：

mysql> DROP VIEW view_emp3;
Query OK, 0 rows affected (0.01 sec)

从结果得出，语句执行成功。下面使用SELECT语句查看视图是否被删除;

mysql> SELECT * FROM view_emp3;
ERROR 1146 (42S02): Table 'ems.view_emp3' doesn't exist

结果显示Table 'ems.view_emp3' doesn't exist，这表明视图已经不存在了，说明我们成功删除了视图。

视图的数据操作

视图操作数据就是通过视图来查询、添加、修改和删除基本表中的数据。因为视图是一个虚拟表，不真是保存数据，所以通过操作数据修改的数据也就是在基本表上操作。

1.添加数据

添加数据之前先根据dept部门表创建一个视图表：

mysql> CREATE VIEW view_dept(d_id,d_name) AS SELECT * FROM dept;
Query OK, 0 rows affected (0.01 sec)

查看部门表现有数据：

mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     20 | 研究院    |
|     10 | 裁决室    |
|     30 | 销售部    |
+--------+-----------+
3 rows in set (0.00 sec)

通过视图向数据表中添加数据的方式与直接向数据表中添加数据的格式一样，具体语句及执行结果如下:

mysql> INSERT INTO view_dept VALUES(40,'运营部');
Query OK, 1 row affected (0.01 sec)

插入语句执行成功，下面使用SELECT语句查看dept表中的数据：

mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     20 | 研究院    |
|     10 | 裁决室    |
|     40 | 运营部    |
|     30 | 销售部    |
+--------+-----------+
4 rows in set (0.00 sec)

从执行结果可以看出数据表中新增了一条数据，说明通过视图成功的向基本表中添加了一条数据。

2.修改数据

通过视图修改基本表的数据可以使用UPDATE语句。

例如，感觉裁决室这个名字太那啥了，改名为总裁办，具体语句及执行结果如下：

mysql> UPDATE view_dept SET d_name='总裁办' WHERE d_name='裁决室';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

UPDATE语句执行成功。下面使用SELECT语句查询数据表dept中的数据：


mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     10 | 总裁办    |
|     20 | 研究院    |
|     40 | 运营部    |
|     30 | 销售部    |
+--------+-----------+
4 rows in set (0.00 sec)

从结果可以看出语句修改成功。

3.删除数据

通过视图删除基本表的数据可以使用DELETE语句。

例如，想要删除运营部，具体语句及执行结果如下：

mysql>  DELETE FROM view_dept WHERE d_name='运营部';
Query OK, 1 row affected (0.00 sec)

从结果可以看出DELETE语句执行成功。使用SELECT语句查看基本表dept中的数据：

mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     10 | 总裁办    |
|     20 | 研究院    |
|     30 | 销售部    |
+--------+-----------+
3 rows in set (0.00 sec)

结果显示运营部已经不存在了，说明数据删除成功。

视图之后我会再简单提一下MySQL中的事务，但内容不会太多，也都是些简单的内容，大家可以放心观看，视图这篇文章代码只是看着长，其实不难理解，相信大家认真阅读都能理解的。