第15章、视图

标签： MySQL是怎样使用的新版

创建视图

我们前边唠叨过如何书写连接查询语句，比方说下边这个语句：

  1. mysql> 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 = '男';
  2. +----------+-----------+--------------------------+-----------------------------+-------+
  3. | number   | name      | major                    | subject                     | score |
  4. +----------+-----------+--------------------------+-----------------------------+-------+
  5. | 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理              |    78 |
  6. | 20180101 | 杜子腾    | 计算机科学与工程         | 论萨达姆的战争准备          |    88 |
  7. | 20180103 | 范统      | 软件工程                 | 母猪的产后护理              |    59 |
  8. | 20180103 | 范统      | 软件工程                 | 论萨达姆的战争准备          |    61 |
  9. +----------+-----------+--------------------------+-----------------------------+-------+
  10. 4 rows in set (0.00 sec)
  12. mysql>

我们查询出了一些男学生的基本信息和成绩信息，如果下次还想得到这些信息，我们就不得不把这个又臭又长的查询语句再敲一遍，不过设计MySQL的大叔们很贴心的为我们提供了一个称之为视图(英文名VIEW)的东东来帮助我们以很容易的方式来复用这些查询语句。

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

  1. CREATE VIEW 视图名 AS 查询语句

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

  1. mysql> CREATE VIEW male_student_view 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 = '男';
  2. Query OK, 0 rows affected (0.02 sec)
  4. mysql>

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

使用视图

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

我们平时怎么从真实表中查询信息，就可以怎么从视图中查询信息，比如这么写：

  1. mysql> SELECT * FROM male_student_view;
  2. +----------+-----------+--------------------------+-----------------------------+-------+
  3. | number   | name      | major                    | subject                     | score |
  4. +----------+-----------+--------------------------+-----------------------------+-------+
  5. | 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理              |    78 |
  6. | 20180101 | 杜子腾    | 计算机科学与工程         | 论萨达姆的战争准备          |    88 |
  7. | 20180103 | 范统      | 软件工程                 | 母猪的产后护理              |    59 |
  8. | 20180103 | 范统      | 软件工程                 | 论萨达姆的战争准备          |    61 |
  9. +----------+-----------+--------------------------+-----------------------------+-------+
  10. 4 rows in set (0.00 sec)
  12. mysql>

这个查询语句的查询列表是*，这也就意味着male_student_view视图所代表的查询语句的结果集将作为本次查询的结果集。从这个例子中我们也可以看到，我们不再需要使用那条又臭又长的连接查询语句了，只需要从它对应的视图中查询即可。

当然，更复杂的一些查询语句也可以作用于视图，比方说这个语句：

  1. mysql> SELECT subject, AVG(score) FROM male_student_view WHERE score > 60 GROUP BY subject HAVING AVG(score) > 75 LIMIT 1;
  2. +-----------------------+------------+
  3. | subject               | AVG(score) |
  4. +-----------------------+------------+
  5. | 母猪的产后护理        |    78.0000 |
  6. +-----------------------+------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. 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;

当然视图还可以参与一些更复杂的查询，比如子查询、连接查询什么的。有一点比较有趣的是，在书写查询语句时，视图还可以和真实表一起使用，比如这样：

  1. mysql> SELECT * FROM male_student_view WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
  2. +----------+-----------+--------------------------+-----------------------------+-------+
  3. | number   | name      | major                    | subject                     | score |
  4. +----------+-----------+--------------------------+-----------------------------+-------+
  5. | 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理              |    78 |
  6. | 20180101 | 杜子腾    | 计算机科学与工程         | 论萨达姆的战争准备          |    88 |
  7. +----------+-----------+--------------------------+-----------------------------+-------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

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

利用视图来创建新视图

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

  1. mysql> CREATE VIEW by_view AS SELECT number, name, score FROM male_student_view;
  2. Query OK, 0 rows affected (0.02 sec)
  4. mysql>

我们使用一下这个依赖另一个视图而生成的新视图：

  1. mysql> SELECT * FROM by_view;
  2. +----------+-----------+-------+
  3. | number   | name      | score |
  4. +----------+-----------+-------+
  5. | 20180101 | 杜子腾    |    78 |
  6. | 20180101 | 杜子腾    |    88 |
  7. | 20180103 | 范统      |    59 |
  8. | 20180103 | 范统      |    61 |
  9. +----------+-----------+-------+
  10. 4 rows in set (0.00 sec)
  12. mysql>

在对这种依赖其他的视图而生成的新视图进行查询时，查询语句会先被转换成对它依赖的视图的查询，再转换成对底层表的查询。

创建视图时指定自定义列名

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

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

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

  1. mysql> SELECT no, n, m FROM student_info_view;
  2. +----------+-----------+--------------------------+
  3. | no       | n         | m                        |
  4. +----------+-----------+--------------------------+
  5. | 20180101 | 杜子腾    | 计算机科学与工程         |
  6. | 20180102 | 杜琦燕    | 计算机科学与工程         |
  7. | 20180103 | 范统      | 软件工程                 |
  8. | 20180104 | 史珍香    | 软件工程                 |
  9. | 20180105 | 范剑      | 飞行器设计               |
  10. | 20180106 | 朱逸群    | 电子信息                 |
  11. +----------+-----------+--------------------------+
  12. 6 rows in set (0.00 sec)
  14. mysql>

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

  1. mysql> SELECT number, name, major FROM student_info_view;
  2. ERROR 1054 (42S22): Unknown column 'number' in 'field list'
  3. mysql>

查看和删除视图

查看有哪些视图

我们创建视图时默认是将其放在当前数据库下的，如果我们想查看当前数据库中有哪些视图的话，其实和查看有哪些表的命令是一样的：

  1. mysql> SHOW TABLES;
  2. +---------------------+
  3. | Tables_in_xiaohaizi |
  4. +---------------------+
  5. | by_view             |
  6. | first_table         |
  7. | male_student_view   |
  8. | second_table        |
  9. | student_info        |
  10. | student_info_view   |
  11. | student_score       |
  12. | t                   |
  13. | t1                  |
  14. | t2                  |
  15. | t3                  |
  16. | zero_table          |
  17. +---------------------+
  18. 12 rows in set (0.00 sec)
  20. mysql>

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

查看视图的定义

视图是一张虚拟表，用来查看视图结构的语句和用来查看表结构的语句比较类似，是这样的：

  1. SHOW CREATE VIEW 视图名;

比如我们来查看一下student_info_view视图的结构可以这样写：

  1. mysql> SHOW CREATE VIEW student_info_view\G
  2. *************************** 1. row ***************************
  3.                 View: student_info_view
  4.          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`
  5. character_set_client: utf8
  6. collation_connection: utf8_general_ci
  7. 1 row in set (0.00 sec)
  9. mysql>

小贴士： 注意到我们查询出来的视图结构中多了很多信息，比方说ALGORITHM=UNDEFINED、DEFINER=`root`@`localhost`、SQL SECURITY DEFINER等等等等，这些信息我们目前不关心，大家主动跳过就好了，等各位羽翼丰满了之后可以到MySQL文档中查看这些信息都代表啥意思。

可更新的视图

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

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

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

  1. mysql> SELECT name FROM student_info WHERE number = 20180101;
  2. +------+
  3. | name |
  4. +------+
  5. | 111  |
  6. +------+
  7. 1 row in set (0.00 sec)
  9. mysql>

名称的确被更改成功了！

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

  • 聚集函数（比如SUM(), MIN(), MAX(), COUNT()等等）
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION 或者 UNION ALL
  • 某些子查询
  • 某些连接查询
  • 等等等等

我们这里对这些限制条件并不准备展开来说，因为这会引入更多复杂的东西，对于作为小白的我们来说，一般我们只在查询语句里使用视图，而不在INSERT、DELETE、UPDATE语句里使用视图！这里介绍对可更新的视图只是为了语法的完整性，并不是建议大家在实际使用过程中使用此功能。

删除视图

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

  1. DROP VIEW 视图名

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

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

然后再查看当前数据库中的表和视图：

  1. mysql> SHOW TABLES;
  2. +---------------------+
  3. | Tables_in_xiaohaizi |
  4. +---------------------+
  5. | first_table         |
  6. | male_student_view   |
  7. | second_table        |
  8. | student_info        |
  9. | student_info_view   |
  10. | student_score       |
  11. | t                   |
  12. | t1                  |
  13. | t2                  |
  14. | t3                  |
  15. | zero_table          |
  16. +---------------------+
  17. 11 rows in set (0.00 sec)
  19. mysql>

这个by_view视图就不见了！