重新学习 MySQL 的基础知识，本章内容为视图。
认识视图
视图是一个虚拟表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据，但视图并不是数据库真实存储的数据表。
在讲解视图的作用之前，我们先举一个例子，在此使用的数据与 重学MySQL之查询练习 中的数据一致。
查看 student、teacher、score 三张表的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
| mysql> SELECT * FROM student;
+
| s_no | s_name | s_gender | s_birthday | class |
+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+
9 rows in set (0.00 sec)
mysql> SELECT * FROM course;
+
| c_no | c_name | t_no |
+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+
4 rows in set (0.34 sec)
mysql> SELECT * FROM score;
+
| s_no | c_no | grade |
+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+
9 rows in set (0.05 sec)
|
现在需要查询 每门课程中高于该门课程平均分的学生的成绩记录
这种查询比较简单，可以用简单的子查询来解决
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| mysql>
mysql> SELECT c_no, AVG(grade) FROM score GROUP BY c_no;
+
| c_no | AVG(grade) |
+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+
3 rows in set (0.00 sec)
mysql> SELECT * FROM score AS s1 WHERE grade>=(SELECT AVG(grade) FROM score AS s2 GROUP BY c_no HAVING s1.c_no=s2.c_no);
+
| s_no | c_no | grade |
+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
+
4 rows in set (0.00 sec)
|
在上面的查询过程中，因为都是在一张表上操作，所以使用了别名的操作，因为之前是求高于每门课程平均分的学生成绩，假如现在还需要查询低于每门课程平均分的学生成绩，或者高于每门课程平均分的男\女同学的成绩，或者 ……
可见对于这类需求，每次都需要求出每门课程的平均分，而这一数据并不存在与表中，那么为了简化操作，是否有另一种方式来简化这种查询呢？
有的。视图的目的就显现出来了，之前说，视图其实是一张虚拟表，它不是真正数据表，其结构和数据是建立在对数据中真实表的查询基础上的。视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。要注意的是，视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
有了上述例子的铺垫，下面来详细介绍视图。
创建视图
语法格式如下
1
| CREATE VIEW <视图名> AS <SELECT语句>;
|
在上述例子的基础上，我们可以创建一个基于查询每门课程平均分的视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| mysql>
mysql> CREATE VIEW avg_score AS SELECT c_no, AVG(grade) AS avg_grade FROM score GROUP BY c_no;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> SELECT * FROM avg_score;
+
| c_no | avg_grade |
+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+
3 rows in set (0.00 sec)
|
有了视图过后，就可以简化我们之前的查询操作
1
2
3
4
5
6
7
8
9
10
| mysql> SELECT * FROM score, avg_score WHERE score.c_no=avg_score.c_no AND grade>=avg_grade;
+
| s_no | c_no | grade | c_no | avg_grade |
+
| 103 | 3-105 | 92 | 3-105 | 85.3333 |
| 103 | 3-245 | 86 | 3-245 | 76.3333 |
| 103 | 6-166 | 85 | 6-166 | 81.6667 |
| 105 | 3-105 | 88 | 3-105 | 85.3333 |
+
4 rows in set (0.00 sec)
|
修改视图
语法格式如下：
1
| ALTER VIEW <视图名> AS <SELECT语句>
|
还是拿之前的例子，比如现在我们需要为视图 avg_score 做一个简单的修改，查询出课程号，课程名和平均成绩
1
2
3
4
5
6
7
8
9
10
11
12
| mysql> ALTER VIEW avg_score AS SELECT course.c_no, c_name, AVG(grade) AS avg_grade FROM score, course WHERE score.c_no=course.c_no GROUP BY c_no;
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM avg_score;
+
| c_no | c_name | avg_grade |
+
| 3-105 | 计算机导论 | 85.3333 |
| 3-245 | 操作系统 | 76.3333 |
| 6-166 | 数字电路 | 81.6667 |
+
3 rows in set (0.00 sec)
|
删除视图
语法格式如下：
1
| DROP VIEW <视图名1> [ , <视图名2> …]
|
删除之前创建的视图
1
2
| mysql> DROP VIEW avg_score;
Query OK, 0 rows affected (0.00 sec)
|
Copyright Notice: All articles in this blog are licensed under CC BY-NC-ND 4.0 unless stating additionally.
