重学MySQL之视图

重新学习 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)
文章作者: Inno Fang
文章链接: https://innofang.github.io/2020/04/02/重学MySQL之视图/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-ND 4.0 许可协议。转载请注明来自 Inno's Blog