mysql--视图（view）

原创 大孩子 我们都是小青蛙 2018-07-02
社会我蛙哥，人狠话也多
视图
我们之前唠叨过连接表的查询，比方说下边这个：

image_1chb82o2b15c81t4416291a52cffp.png-123.5kB
我们查询出了一些男学生的基本信息和成绩信息，如果下次还想得到这些信息，我们就不得不把这个又臭又长的查询语句再敲一遍，所以MySQL提供了视图(英文名VIEW)来帮助我们用很容易的方式去复用这些查询语句。

创建视图
一个视图可以理解为一个查询语句的别名，创建视图的语句如下：

CREATE VIEW 视图名 AS 查询语句
比如我们想根据上边那个又臭又长的查询语句来创建一个视图可以这么写：

mysql> CREATE VIEW male_student_info AS SELECT s1.number, s1.name, s1.major, s2.subject, s2.score FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.sex = ‘男’;
Query OK, 0 rows affected (0.02 sec)

mysql>
这样，这个名称为male_student_info的视图就代表了那一串又臭又长的查询语句了。

使用视图
视图也被称为虚拟表，因为我们可以对视图进行一些类似表的增删改查操作，只不过我们对视图的相关操作都会被映射到那个又臭又长的查询语句对应的底层的表上。那一串又臭又长的查询语句的查询列表可以被当作视图的虚拟列，比方说male_student_info这个视图对应的查询语句中的查询列表是number、name、major、subject、score，它们也是male_student_info视图的虚拟列。

比如我们可以使用平常的查询语句从视图中查询我们需要的信息可以这么写：

image_1chb8adng129m1qlo15pt1qm8fp1p.png-96.6kB
这里我们的查询列表是*，这也就意味着male_student_info所代表的查询语句的结果集将作为整个查询的结果集返回。从这个例子中我们也可以看到，我们不再需要使用那句又臭又长的连接查询语句了，只需要从它对应的视图中查询即可。

除此之外，我们在真实表中使用的那些查询语句都可以被用到视图这个虚拟表中，比方说这个查询语句：

image_1chb8bn1811ee8csfen1b0f6bl26.png-58.2kB
我们再次强调一遍，视图其实就是某个查询的别名，而不是某个查询的结果集，换句话说就是，创建视图的时候并不会把那个又臭又长的查询语句的结果集维护在硬盘或者内存里！在对视图进行查询时，MySQL服务器将会帮助我们把对视图的查询语句转换为对底层表的查询语句然后再执行，所以上边这个查询其实会被转换成下边这个查询语句去执行：

SELECT subject, AVG(score) FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.sex = ‘男’ AND score > 60 GROUP BY subject HAVING AVG(score) > 75;
只不过这个转换的过程我们并不能看到，所以主观上认为硬盘或内存里真的维护了一个视图对应的表而已～ 更复杂的一些查询语句，比如子查询、连接查询什么的，都可以被用到视图上，我们这里就不举例子了。

有一点比较有趣的是，在查询时，视图可以和表一起使用，包括子查询和连接查询，比如这样：

image_1chb8cv3e16hv2g91s2e16261l1f2j.png-89.5kB
所以在使用层面，我们完全可以把视图当作一个表去使用，但是它的实现原理却是在执行语句时转换为对底层表的操作。使用视图的好处也是显而易见的，我们可以复用某个查询语句，从而简化了查询操作，避免了每次查询时都要写一遍又臭又长的语句；对视图的操作更加直观，而不用考虑它底层的查询细节。

利用视图来创建新视图
我们前边说视图是某个查询语句的别名，其实这个查询语句不仅可以从普通的表中查询数据，也可以从另一个视图中查询数据，只要是个合法的查询语句就好了。比方说我们利用male_student_info视图来创建另一个新视图可以这么写：

mysql> CREATE VIEW by_view AS SELECT number, name, score FROM male_student_info;
Query OK, 0 rows affected (0.02 sec)

mysql>
我们查询一下这个从另一个视图中生成的视图：

image_1chb8hku4lko1iqa1t0bn8l1a3g.png-48.1kB
这种利用其他的视图来生成的新视图也被称为嵌套视图，在对某个嵌套视图执行查询时，查询语句会先被转换成对它依赖的视图的查询，再转换成对底层表的查询。

创建视图时指定自定义列名
我们前边说过视图的虚拟列其实是这个视图对应的查询语句的查询列表，我们也可以在创建列表的时候为这些虚拟列自定义列名，这些自定义列名写到视图名后边，用逗号,分隔就好了，不过需要注意的是，自定义列名一定要和查询列表中的查询对象一一对应。比如我们新创建一个自定义列名的视图：

mysql> CREATE VIEW student_info_view(no, n, m) AS SELECT number, name, major FROM student_info;
Query OK, 0 rows affected (0.02 sec)

mysql>
我们的自定义列名列表是no, n, m，分别对应查询列表中的number, name, major。有了自定义列名之后，我们之后对视图的查询语句都要基于这些自定义列名，比如我们可以这么查询：

image_1chb8pca923a1du41448v7uk776d.png-77.9kB
如果仍旧使用与视图对应的查询语句的查询列表中的列名就会报错，比如这样：

mysql> SELECT number, name, major FROM student_info_view;
ERROR 1054 (42S22): Unknown column ‘number’ in ‘field list’
mysql>
查看和删除视图
查看有哪些视图
我们想查看当前数据库中有哪些视图的话，其实和查看有哪些表的命令是一样的：

mysql> SHOW TABLES;
±--------------------+
| Tables_in_xiaohaizi |
±--------------------+
| by_view |
| first_table |
| male_student_info |
| second_table |
| student_info |
| student_info_view |
| student_score |
| t |
| t1 |
| t2 |
| t3 |
| zero_table |
±--------------------+
12 rows in set (0.00 sec)

mysql>
可以看到，我们创建的几个视图，包括by_view、male_student_info、student_info_view就都显示出来了。需要注意的是，因为视图是一张虚拟表，所以新创建的视图的名称不能和当前数据库中的其他视图或者表的名称冲突！

查看视图的定义
因为视图是一张虚拟表，所以用来查看表结构的语句都可以用来查看视图的结构，不过我们经常使用的查看视图定义语句是这个：

SHOW CREATE VIEW 视图名
我们来查看一下student_info_view视图的定义：

mysql> SHOW CREATE VIEW student_info_view\G
*************************** 1. row ***************************
View: student_info_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW student_info_view AS select student_info.number AS no,student_info.name AS n,student_info.major AS m from student_info
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>
删除视图
如果某个视图我们不想要了，可以使用这个语句来删除掉它：

DROP VIEW 视图名
比如我们把by_view视图删掉可以这么写：

mysql> DROP VIEW by_view;
Query OK, 0 rows affected (0.00 sec)

mysql>
然后再查看当前数据库中的表：

mysql> SHOW TABLES;
±--------------------+
| Tables_in_xiaohaizi |
±--------------------+
| first_table |
| male_student_info |
| second_table |
| student_info |
| student_info_view |
| student_score |
| t |
| t1 |
| t2 |
| t3 |
| zero_table |
±--------------------+
11 rows in set (0.00 sec)

mysql>
这个视图就不见了！

更新视图
我们前边进行的都是对视图的查询操作，其实也可以对视图进行更新，也就是在视图上执行INSERT、DELETE、UPDATE语句。对视图执行更新语句的本质上是对该视图对应的底层表进行更新。比方说视图student_info_view的底层表是student_info，所以如果我们对student_info_view执行更新语句就相当于对student_info表进行更新，比方说我们执行这个语句：

mysql> UPDATE student_info_view SET n = ‘111’ WHERE no = 20180101;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
我们再到student_info表中看一下这个学生的名称是否被改了：

mysql> SELECT name FROM student_info WHERE number = 20180101;
±-----+
| name |
±-----+
| 111 |
±-----+
1 row in set (0.00 sec)

mysql>
名称的确被更改成功了！

不过并不是可以在所有的视图上执行更新语句的，在生成视图的时候使用了下边这些语句的都不能进行更新：

分组

连接查询

子查询

组合查询

聚集函数

DISTINCT

查询列表上是非列名的表达式

虽然有这么多限制，但是需要我们注意的是，一般情况下，我们只在视图上执行查询操作而不进行更新操作！这里介绍对视图的更新只是为了语法的完整性，并不是建议大家在实际使用过程中使用对视图的更新功能。