mysql视图(View)

引言

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

基本概念

场面描述说明

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

mysql> DESC department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(4)      | NO   | PRI | NULL    |       |
| d_name   | varchar(20) | NO   | UNI    | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

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

mysql> DESC worker;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| num         | int(10)     | NO   | PRI | NULL    |       |
| d_id        | int(4)      | YES  |MUL     | NULL    |       |
| name        | varchar(20) | NO   |     | NULL    |       |
| sex         | varchar(4)  | NO   |     | NULL    |       |
| birthday    | datetime    | YES  |     | NULL    |       |
| homeaddress | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

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

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

技巧：如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据。

MySQL 的视图不支持输入参数的功能，因此交互性上还有欠缺。但对于变化不是很大的操作，使用视图可以很大程度上简化用户的操作。

视图并不同于数据表，它们的区别在于以下几点：

视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
视图的建立和删除只影响视图本身，不影响对应的基本表。

视图的优点：

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有如下优点：

1) 定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。

例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

2) 简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3) 提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4) 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5) 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

6) 重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

要注意区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

使用视图的时候，还应该注意以下几点：

创建视图需要足够的访问权限。
创建视图的数目没有限制。
视图可以嵌套，即从其他视图中检索数据的查询来创建视图。
视图不能索引，也不能有关联的触发器、默认值或规则。
视图可以和表一起使用。
视图不包含数据，所以每次使用视图时，都必须执行查询中所需的任何一个检索操作。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图，可能会发现系统运行性能下降得十分严重。因此，在部署大量视图应用时，应该进行系统测试。

提示：ORDER BY 子句可以用在视图中，但若该视图检索数据的 SELECT 语句中也含有 ORDER BY 子句，则该视图中的 ORDER BY 子句将被覆盖。

Mysql视图(View)操作演示

department

CREATE TABLE `department` (
  `d_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门号',
  `d_name` varchar(20) NOT NULL COMMENT '部门名称',
  `function` varchar(50) DEFAULT NULL COMMENT '功能',
  `address` varchar(50) DEFAULT NULL COMMENT '办公地址',
  PRIMARY KEY (`d_id`,`d_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', '总裁办', '批改奏折', '华北');
INSERT INTO `department` VALUES ('2', '财务部', '出账', '华南');
INSERT INTO `department` VALUES ('3', '技术部', '干技术', '华中');
INSERT INTO `department` VALUES ('4', '设计部', '干设计的', '华东');

worker

CREATE TABLE `worker` (
  `num` int(11) NOT NULL,
  `d_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `homeaddress` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('222', '4', '张三', '男', '1993-06-03 09:23:24', '北京朝阳群众');
INSERT INTO `worker` VALUES ('333', '3', '李四', '男', '1990-07-03 09:23:24', '湖南长沙实名');
INSERT INTO `worker` VALUES ('444', '2', '王五', '女', '1998-01-13 09:23:24', '上海开发大道');
INSERT INTO `worker` VALUES ('555', '1', '赵六', '男', '1995-01-03 09:23:24', '深圳长江一号');
INSERT INTO `worker` VALUES ('666', '2', '小明', '男', '1999-07-07 09:23:24', '天津市武清区');
INSERT INTO `worker` VALUES ('777', '3', '小红', '女', '1992-04-23 09:23:24', '河北承德市');

创建视图基本语法

可以使用 CREATE VIEW 语句来创建视图。语法格式如下：

CREATE VIEW <视图名> AS <SELECT语句>

语法说明如下。

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。

SELECT 语句不能引用系统或用户变量。

SELECT 语句不能包含 FROM 子句中的子查询。

SELECT 语句不能引用预处理语句参数。

视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。

视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。

WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件。

创建单表视图

【实例 1】在 worker 表中，创建 view_worker_info 视图

CREATE VIEW view_worker_info AS SELECT * FROM worker;

查看视图

mysql> select * from view_worker_info;
+-----+------+------+-----+---------------------+--------------+
| num | d_id | name | sex | birthday            | homeaddress  |
+-----+------+------+-----+---------------------+--------------+
| 222 |    4 | 张三 | 男  | 1993-06-03 09:23:24 | 北京朝阳群众 |
| 333 |    3 | 李四 | 男  | 1990-07-03 09:23:24 | 湖南长沙实名 |
| 444 |    2 | 王五 | 女  | 1998-01-13 09:23:24 | 上海开发大道 |
| 555 |    1 | 赵六 | 男  | 1995-01-03 09:23:24 | 深圳长江一号 |
| 666 |    2 | 小明 | 男  | 1999-07-07 09:23:24 | 天津市武清区 |
| 777 |    3 | 小红 | 女  | 1992-04-23 09:23:24 | 河北承德市   |
+-----+------+------+-----+---------------------+--------------+
6 rows in set

【实例 2】在 worker 表中，根据某些字段，创建 view_worker_attr 视图

CREATE VIEW view_worker_attr (s_num,s_name,s_homeaddress) AS SELECT num,name,homeaddress FROM worker;

查看视图

mysql> select * from view_worker_attr;
+-------+--------+---------------+
| s_num | s_name | s_homeaddress |
+-------+--------+---------------+
|   222 | 张三   | 北京朝阳群众  |
|   333 | 李四   | 湖南长沙实名  |
|   444 | 王五   | 上海开发大道  |
|   555 | 赵六   | 深圳长江一号  |
|   666 | 小明   | 天津市武清区  |
|   777 | 小红   | 河北承德市    |
+-------+--------+---------------+
6 rows in set

创建多表视图

创建基于 department 和 worker 的联合视图

CREATE VIEW view_info (
	s_num,
	s_name,
	d_id,
	s_dname,
	s_homeaddress,
	s_address
) AS SELECT
	num,
	`name`,
	worker.d_id,
	d_name,
	homeaddress,
	address
FROM
	worker,
	department
WHERE
	worker.d_id = department.d_id;

查看视图

mysql> select * from view_info;
+-------+--------+------+---------+---------------+-----------+
| s_num | s_name | d_id | s_dname | s_homeaddress | s_address |
+-------+--------+------+---------+---------------+-----------+
|   222 | 张三   |    4 | 设计部  | 北京朝阳群众  | 华东      |
|   333 | 李四   |    3 | 技术部  | 湖南长沙实名  | 华中      |
|   444 | 王五   |    2 | 财务部  | 上海开发大道  | 华南      |
|   555 | 赵六   |    1 | 总裁办  | 深圳长江一号  | 华北      |
|   666 | 小明   |    2 | 财务部  | 天津市武清区  | 华南      |
|   777 | 小红   |    3 | 技术部  | 河北承德市    | 华中      |
+-------+--------+------+---------+---------------+-----------+
6 rows in set

查看视图基本语法

查看视图的字段信息与查看数据表的字段信息一样，都是使用 DESCRIBE 关键字来查看的。具体语法如下：

DESCRIBE 视图名;

或简写成：

DESC 视图名;

测试

查看联合视图 view_info 的视图结构

mysql> DESCRIBE view_info;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| s_num         | int(11)     | NO   |     | NULL    |       |
| s_name        | varchar(20) | NO   |     | NULL    |       |
| d_id          | int(11)     | NO   |     | NULL    |       |
| s_dname       | varchar(20) | NO   |     | NULL    |       |
| s_homeaddress | varchar(50) | YES  |     | NULL    |       |
| s_address     | varchar(50) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
6 rows in set

查看视图表的详细信息

在 MySQL 中，SHOW CREATE VIEW 语句可以查看视图的详细定义。其语法如下所示：

SHOW CREATE VIEW 视图名;

通过上面的语句，还可以查看创建视图的语句。创建视图的语句可以作为修改或者重新创建视图的参考，方便用户操作。

测试

mysql> SHOW CREATE VIEW view_info \G;
*************************** 1. row ***************************
                View: view_info
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`offline`@`%` SQL SECURITY DEFINER VIEW `view_info` AS select `worker`.`num` AS `s_num`,`worker`.`name` AS `s_name`,`worker`.`d_id` AS `d_id`,`department`.`d_name` AS `s_dname`,`worker`.`homeaddress` AS `s_homeaddress`,`department`.`address` AS `s_address` from (`worker` join `department`) where (`worker`.`d_id` = `department`.`d_id`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.04 sec)

ERROR: 
No query specified

上述 SQL 语句以\G结尾，这样能使显示结果格式化。如果不使用\G，显示的结果会比较混乱

拓展阅读

所有视图的定义都是存储在 information_schema 数据库下的 views 表中，也可以在这个表中查看所有视图的详细信息，SQL 语句如下：

SELECT * FROM information_schema.views;

不过，通常情况下都是使用 SHOW CREATE VIEW 语句。

修改视图基本语法

可以使用 ALTER VIEW 语句来对已有的视图进行修改。语法格式如下：

ALTER VIEW <视图名> AS <SELECT语句>

语法说明如下：

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
<SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。

修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

修改视图内容

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。

注意：对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义。

某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。

还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
位于选择列表中的子查询。
FROM 子句中的不可更新视图或包含多个表。
WHERE 子句中的子查询，引用 FROM 子句中的表。
ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

测试

案例1

修改视图表的表结构

ALTER VIEW view_worker_attr AS SELECT num,name FROM worker;

查看

mysql> select * from view_worker_attr;
+-----+--------+
| num | name   |
+-----+--------+
| 222 | 张三   |
| 333 | 李四   |
| 444 | 王五   |
| 555 | 赵六   |
| 666 | 小明   |
| 777 | 小红   |
+-----+--------+
6 rows in set (0.02 sec)

案例2

使用 UPDATE 语句更新视图 view_worker_attr

UPDATE view_worker_attr SET name="666张三666" WHERE num=222;

查看

mysql> select * from view_worker_attr;
+-----+--------------+
| num | name         |
+-----+--------------+
| 222 | 666张三666   |
| 333 | 李四         |
| 444 | 王五         |
| 555 | 赵六         |
| 666 | 小明         |
| 777 | 小红         |
+-----+--------------+
6 rows in set (0.04 sec)

mysql> select * from worker;
+-----+------+--------------+-----+---------------------+--------------------+
| num | d_id | name         | sex | birthday            | homeaddress        |
+-----+------+--------------+-----+---------------------+--------------------+
| 222 |    4 | 666张三666   | 男  | 1993-06-03 09:23:24 | 北京朝阳群众       |
| 333 |    3 | 李四         | 男  | 1990-07-03 09:23:24 | 湖南长沙实名       |
| 444 |    2 | 王五         | 女  | 1998-01-13 09:23:24 | 上海开发大道       |
| 555 |    1 | 赵六         | 男  | 1995-01-03 09:23:24 | 深圳长江一号       |
| 666 |    2 | 小明         | 男  | 1999-07-07 09:23:24 | 天津市武清区       |
| 777 |    3 | 小红         | 女  | 1992-04-23 09:23:24 | 河北承德市         |
+-----+------+--------------+-----+---------------------+--------------------+
6 rows in set (0.02 sec)

删除视图基本语法

可以使用 DROP VIEW 语句来删除视图。语法格式如下：

DROP VIEW <视图名1> [ , <视图名2> …]

其中：<视图名>指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

测试

删除 view_worker_attr 视图

mysql> DROP VIEW view_worker_attr;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from view_worker_attr;
ERROR 1146 (42S02): Table 'view_db.view_worker_attr' doesn't exist

李斌

此平台用作学习交流日常分享

mysql视图(View)

引言

基本概念

场面描述说明

视图的优点：

1) 定制用户数据，聚焦特定的数据

2) 简化数据操作

3) 提高数据的安全性

4) 共享所需数据

5) 更改数据格式

6) 重用 SQL 语句

Mysql视图(View)操作演示

创建视图基本语法

创建单表视图

创建多表视图

查看视图基本语法

测试

查看视图表的详细信息

测试

拓展阅读

修改视图基本语法

修改视图内容

测试

案例1

案例2

删除视图基本语法

测试