Inno's Blog HomeArchivesTagsCategoriesGalleryAbout
重学MySQL之查询练习
|MySQL

重新学习 MySQL 的基础知识，查询是数据库中常用的操作，本章主要是通过练习来实践查询操作。

准备数据

建数据表

创建数据库

1
CREATE DATABASE select_test;

切换数据库

1
USE select_test;

创建学生表

1
2
3
4
5
6
7
CREATE TABLE student (
    s_no VARCHAR(20) PRIMARY KEY COMMENT '学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT '学生姓名',
    s_gender VARCHAR(10) NOT NULL COMMENT '学生性别',
    s_birthday DATETIME COMMENT '学生生日',
    class VARCHAR(20) COMMENT '学生班级'
);

创建教师表

1
2
3
4
5
6
7
8
CREATE TABLE teacher (
    t_no VARCHAR(20) PRIMARY KEY COMMENT '教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT '教师名称',
    t_gender VARCHAR(10) NOT NULL COMMENT '教师性别',
    t_birthday DATETIME COMMENT '教师生日',
    title VARCHAR(20) NOT NULL COMMENT '教师职称',
    department VARCHAR(20) NOT NULL COMMENT '教师部门'
);

创建课程表

1
2
3
4
5
6
CREATE TABLE course (
    c_no VARCHAR(20) PRIMARY KEY COMMENT '课程编号',
    c_name VARCHAR(20) NOT NULL COMMENT '课程名字',
    t_no VARCHAR(20) NOT NULL COMMENT '教师编号',
    FOREIGN KEY(t_no) REFERENCES teacher(t_no)
);

创建成绩表

1
2
3
4
5
6
7
8
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT '学生编号',
    c_no VARCHAR(20) NOT NULL COMMENT '课程编号',
    grade DECIMAL COMMENT '分数',
    FOREIGN KEY(s_no) REFERENCES student(s_no),
    FOREIGN KEY(c_no) REFERENCES course(c_no),
    PRIMARY KEY(s_no, c_no)
);

添加数据

添加学生表数据

1
2
3
4
5
6
7
8
9
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

添加教师表数据

1
2
3
4
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

添加课程表数据

1
2
3
4
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

添加成绩表数据

1
2
3
4
5
6
7
8
9
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

基本查询练习

1. 查询 student 表的所有记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

2. 查询 student 表中的 s_name、s_gender 和 class 字段的所有记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT s_name, s_gender, class FROM student;
+--------+----------+-------+
| s_name | s_gender | class |
+--------+----------+-------+
| 曾华   | 男       | 95033 |
| 匡明   | 男       | 95031 |
| 王丽   | 女       | 95033 |
| 李军   | 男       | 95033 |
| 王芳   | 女       | 95031 |
| 陆军   | 男       | 95031 |
| 王尼玛 | 男       | 95033 |
| 张全蛋 | 男       | 95031 |
| 赵铁柱 | 男       | 95031 |
+--------+----------+-------+
9 rows in set (0.00 sec)

3. 查询 teacher 表中不重复的 department 列

1
2
3
4
5
6
7
8
mysql> SELECT DISTINCT(department) FROM teacher;
+------------+
| department |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+
2 rows in set (0.00 sec)

4. 查询 score 表中成绩在60-80之间的所有记录（区间查询和运算符查询）

区间查询：BETWEEN a AND b, 查询区间为 [a, b]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> -- 运算符查询
mysql> SELECT * FROM score WHERE grade>=60 AND grade<=80;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 105  | 3-245 |    75 |
| 105  | 6-166 |    79 |
| 109  | 3-105 |    76 |
| 109  | 3-245 |    68 |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> -- 区间查询
mysql> SELECT * FROM score WHERE grade BETWEEN 60 AND 80;
mysql> -- 结果同上

5. 查询 score 表中成绩为 85, 86 或 88 的记录

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM score WHERE grade IN (85, 86, 88);
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-245 |    86 |
| 103  | 6-166 |    85 |
| 105  | 3-105 |    88 |
+------+-------+-------+
3 rows in set (0.00 sec)

6. 查询 student 表中 ‘95031’ 班或性别为 ‘女’ 的所有记录

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM student WHERE class='95031' OR s_gender='女';
+------+--------+----------+---------------------+-------+
| s_no | s_name | s_gender | s_birthday          | class |
+------+--------+----------+---------------------+-------+
| 102  | 匡明   | 男       | 1975-10-02 00:00:00 | 95031 |
| 103  | 王丽   | 女       | 1976-01-23 00:00:00 | 95033 |
| 105  | 王芳   | 女       | 1975-02-10 00:00:00 | 95031 |
| 106  | 陆军   | 男       | 1974-06-03 00:00:00 | 95031 |
| 108  | 张全蛋 | 男       | 1975-02-10 00:00:00 | 95031 |
| 109  | 赵铁柱 | 男       | 1974-06-03 00:00:00 | 95031 |
+------+--------+----------+---------------------+-------+
6 rows in set (0.00 sec)

7. 以 class 降序的方式查询 student 表的所有记录

ORDER By 对字段进行排序：默认升序（ASC），降序为 DESC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT * FROM student ORDER BY class DESC;
+------+--------+----------+---------------------+-------+
| s_no | s_name | s_gender | s_birthday          | class |
+------+--------+----------+---------------------+-------+
| 101  | 曾华   | 男       | 1977-09-01 00:00:00 | 95033 |
| 103  | 王丽   | 女       | 1976-01-23 00:00:00 | 95033 |
| 104  | 李军   | 男       | 1976-02-20 00:00:00 | 95033 |
| 107  | 王尼玛 | 男       | 1976-02-20 00:00:00 | 95033 |
| 102  | 匡明   | 男       | 1975-10-02 00:00:00 | 95031 |
| 105  | 王芳   | 女       | 1975-02-10 00:00:00 | 95031 |
| 106  | 陆军   | 男       | 1974-06-03 00:00:00 | 95031 |
| 108  | 张全蛋 | 男       | 1975-02-10 00:00:00 | 95031 |
| 109  | 赵铁柱 | 男       | 1974-06-03 00:00:00 | 95031 |
+------+--------+----------+---------------------+-------+
9 rows in set (0.00 sec)

8. 以 c_no 升序、grade 降序查询 score 表的所有记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT * FROM score ORDER BY c_no ASC, grade DESC;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-105 |    92 |
| 105  | 3-105 |    88 |
| 109  | 3-105 |    76 |
| 103  | 3-245 |    86 |
| 105  | 3-245 |    75 |
| 109  | 3-245 |    68 |
| 103  | 6-166 |    85 |
| 109  | 6-166 |    81 |
| 105  | 6-166 |    79 |
+------+-------+-------+
9 rows in set (0.00 sec)

9. 查询 “95031” 班的学生人数

使用 COUNT() 函数进行统计

1
SELECT COUNT(*) FROM student WHERE class="95031";

10. 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）

LIMIT 关键字用法：LIMIT 起始位置, 数量

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT s_no, c_no FROM score WHERE grade=(SELECT MAX(grade) FROM score);
+------+-------+
| s_no | c_no  |
+------+-------+
| 103  | 3-105 |
+------+-------+
1 row in set (0.00 sec)

mysql> -- 也可以使用降序排序，取第一个的方法，但对于有多个相同最大值的情况不能确保数量正确
mysql> SELECT s_no, c_no FROM score ORDER BY grade DESC LIMIT 0, 1;
mysql> -- 结果同上

11. 查询每门课的平均成绩

AVG() 函数可以计算平均值; GROUP BY 可以按列分组

1
2
3
4
5
6
7
8
9
10
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)

12. 查询 score 表中至少有 2 名学生选修的, 并且以 3 开头的课程的平均分

模糊查询可以使用 LIKE 关键字，”%“ 是一个通配符，3% 用来匹配以 3 开头的课程
在 GROUP BY 使用条件查询需要使用 HAVING 关键字而非 WHERE

1
2
3
4
5
6
7
8
mysql> SELECT c_no, AVG(grade), COUNT(c_no) FROM score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+-------+------------+-------------+
| c_no  | AVG(grade) | COUNT(c_no) |
+-------+------------+-------------+
| 3-105 |    85.3333 |           3 |
| 3-245 |    76.3333 |           3 |
+-------+------------+-------------+
2 rows in set (0.00 sec)

13. 查询分数大于 70 但是小于 90 的 s_no 列:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT s_no, grade FROM score WHERE grade > 70 AND grade < 90;
+------+-------+
| s_no | grade |
+------+-------+
| 103  |    86 |
| 103  |    85 |
| 105  |    88 |
| 105  |    75 |
| 105  |    79 |
| 109  |    76 |
| 109  |    81 |
+------+-------+
7 rows in set (0.04 sec)

mysql> SELECT s_no, grade FROM score WHERE grade BETWEEN 71 AND 89;
mysql> -- 结果同上

14. 查询所有的学生 s_name , c_no, grade 列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT s_name, c_no, grade FROM student, score WHERE student.s_no=score.s_no;
+--------+-------+-------+
| s_name | c_no  | grade |
+--------+-------+-------+
| 王丽   | 3-105 |    92 |
| 王丽   | 3-245 |    86 |
| 王丽   | 6-166 |    85 |
| 王芳   | 3-105 |    88 |
| 王芳   | 3-245 |    75 |
| 王芳   | 6-166 |    79 |
| 赵铁柱 | 3-105 |    76 |
| 赵铁柱 | 3-245 |    68 |
| 赵铁柱 | 6-166 |    81 |
+--------+-------+-------+
9 rows in set (0.04 sec)

15. 查询所有学生的 s_no, c_name, grade 列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT s_no, c_name, grade FROM course, score WHERE course.c_no=score.c_no;
+------+------------+-------+
| s_no | c_name     | grade |
+------+------------+-------+
| 103  | 计算机导论 |    92 |
| 103  | 操作系统   |    86 |
| 103  | 数字电路   |    85 |
| 105  | 计算机导论 |    88 |
| 105  | 操作系统   |    75 |
| 105  | 数字电路   |    79 |
| 109  | 计算机导论 |    76 |
| 109  | 操作系统   |    68 |
| 109  | 数字电路   |    81 |
+------+------------+-------+
9 rows in set (0.07 sec)

16. 查询所有的学生 s_name , c_name, grade 列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT s_name, c_name, grade FROM student, course, score WHERE student.s_no=score.s_no AND course.c_no=score.c_no;
+--------+------------+-------+
| s_name | c_name     | grade |
+--------+------------+-------+
| 王丽   | 计算机导论 |    92 |
| 王丽   | 操作系统   |    86 |
| 王丽   | 数字电路   |    85 |
| 王芳   | 计算机导论 |    88 |
| 王芳   | 操作系统   |    75 |
| 王芳   | 数字电路   |    79 |
| 赵铁柱 | 计算机导论 |    76 |
| 赵铁柱 | 操作系统   |    68 |
| 赵铁柱 | 数字电路   |    81 |
+--------+------------+-------+
9 rows in set (0.00 sec)

17. 查询班级是’95031’班学生每门课的平均分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> -- 1. 先试着查询 95031 班的学生成绩
mysql> SELECT c_no, grade FROM student, score WHERE student.s_no=score.s_no AND class='95031';

mysql> -- 2. 然后按课程号 c_no 分组，再求平均分
mysql> SELECT c_no, AVG(grade) FROM student, score WHERE student.s_no=score.s_no AND class='95031' GROUP BY c_no;
+-------+------------+
| c_no  | AVG(grade) |
+-------+------------+
| 3-105 |    82.0000 |
| 3-245 |    71.5000 |
| 6-166 |    80.0000 |
+-------+------------+
3 rows in set (0.00 sec)

mysql> -- 也可以使用子查询加分组的方式
mysql> SELECT c_no, AVG(grade) FROM score WHERE s_no IN (SELECT s_no FROM student WHERE class='95031') GROUP BY c_no;
mysql> -- 结果同上

18. 查询选修 ‘3-105’ 课程且成绩高于 ‘109’ 号同学的 ‘3-105’ 课程成绩的所有同学的记录

1
2
3
4
5
6
7
8
9
10
11
12
mysql> -- 1. 先试着查绚选修了 3-105 课程的 109 号同学的分数
mysql> SELECT grade FROM student, score WHERE student.s_no=score.s_no AND c_no='3-105' AND score.s_no='109';

mysql> -- 2. 再查询成绩高于 109 号同学的所有同学记录
mysql> SELECT * FROM student, score WHERE student.s_no=score.s_no AND c_no='3-105' AND grade > (SELECT grade FROM student, score WHERE student.s_no=score.s_no AND c_no='3-105' AND score.s_no='109');
+------+--------+----------+---------------------+-------+------+-------+-------+
| s_no | s_name | s_gender | s_birthday          | class | s_no | c_no  | grade |
+------+--------+----------+---------------------+-------+------+-------+-------+
| 103  | 王丽   | 女       | 1976-01-23 00:00:00 | 95033 | 103  | 3-105 |    92 |
| 105  | 王芳   | 女       | 1975-02-10 00:00:00 | 95031 | 105  | 3-105 |    88 |
+------+--------+----------+---------------------+-------+------+-------+-------+
2 rows in set (0.00 sec)

19. 查询成绩高于选修了 ‘3-105’ 课程且学号为 ‘109’ 的同学的成绩的所有记录

相比于 T18，该问题不限制课程号，只要成绩大于109号同学的3-105课程成绩即可，并且查询的只是 score 表

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM score WHERE grade > (SELECT grade FROM student, score WHERE student.s_no=score.s_no AND c_no='3-105' AND score.s_no='109');
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-105 |    92 |
| 103  | 3-245 |    86 |
| 103  | 6-166 |    85 |
| 105  | 3-105 |    88 |
| 105  | 6-166 |    79 |
| 109  | 6-166 |    81 |
+------+-------+-------+
6 rows in set (0.00 sec)

20. 查询与学号为 101 和 108 的同学同年出生的所有学生的 s_no、s_name 和 s_birthday

因为要匹配同年出生，所以要对取出生日的年份，使用 YEAR() 函数

1
2
3
4
5
6
7
8
9
10
mysql> SELECT s_no, s_name, s_birthday FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN ('101', '108'));
+------+--------+---------------------+
| s_no | s_name | s_birthday          |
+------+--------+---------------------+
| 101  | 曾华   | 1977-09-01 00:00:00 |
| 102  | 匡明   | 1975-10-02 00:00:00 |
| 105  | 王芳   | 1975-02-10 00:00:00 |
| 108  | 张全蛋 | 1975-02-10 00:00:00 |
+------+--------+---------------------+
4 rows in set (0.00 sec)

21. 查询任课教师为 ‘张旭’ 的学生的成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> SELECT s_no, score.c_no, grade FROM teacher, course, score WHERE teacher.t_no=course.t_no AND course.c_no=score.c_no AND t_name='张旭';
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 6-166 |    85 |
| 105  | 6-166 |    79 |
| 109  | 6-166 |    81 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> -- 也可以使用多层嵌套子查询的方式
mysql> -- 1. 先找到 '张旭' 老师的教师号
mysql> SELECT t_no FROM teacher WHERE t_name='张旭';

mysql> -- 2. 再找到 '张旭' 老师所教授的课程号
mysql> SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_name='张旭');

mysql> -- 3. 再从 score 表中找到对应课程号的成绩
mysql> SELECT s_no, c_no, grade FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_name='张旭'));
mysql> -- 结果同上

22. 查询选修人数大于 2 人的课程的教师姓名

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
mysql> -- 只需查询教师名字，但为了直观还是多输出了一些信息
mysql> SELECT course.c_no, c_name, t_name, COUNT(course.c_no) FROM teacher, course, score WHERE teacher.t_no=course.t_no AND course.c_no=score.c_no GROUP BY score.c_no HAVING COUNT(course.c_no) > 2;
+-------+------------+--------+--------------------+
| c_no  | c_name     | t_name | COUNT(course.c_no) |
+-------+------------+--------+--------------------+
| 3-105 | 计算机导论 | 王萍   |                  3 |
| 3-245 | 操作系统   | 李诚   |                  3 |
| 6-166 | 数字电路   | 张旭   |                  3 |
+-------+------------+--------+--------------------+
3 rows in set (0.00 sec)

mysql> -- 也可以使用多层嵌套子查询的方式
mysql> -- 1. 先按课程分组，查询选修人数大于 2 的课程编号
mysql> SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 2;

mysql> -- 2. 再通过课程表，利用课程编号查询出对应的教师编号
mysql> SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 2);
mysql> -- 3. 再通过教师表，利用教师编号查询出对应的教师名称
mysql> SELECT t_name FROM teacher WHERE t_no IN (SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 2));
+--------+
| t_name |
+--------+
| 李诚   |
| 王萍   |
| 张旭   |
+--------+
3 rows in set (0.00 sec)

23. 查询 95033 班和 95031 班全体学生的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT * FROM student WHERE class IN ('95033', '95031');
+------+--------+----------+---------------------+-------+
| 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)

24. 查询存在 85 分以上成绩的课程 c_no

1
2
3
4
5
6
7
8
9
mysql> SELECT c_no FROM score WHERE grade > 85;
+-------+
| c_no  |
+-------+
| 3-105 |
| 3-245 |
| 3-105 |
+-------+
3 rows in set (0.00 sec)

25. 查出所有 ‘计算机系’ 教师所教课程的成绩表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT s_no, score.c_no, grade FROM teacher, course, score WHERE teacher.t_no=course.t_no AND course.c_no=score.c_no AND department='计算机系';
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-245 |    86 |
| 105  | 3-245 |    75 |
| 109  | 3-245 |    68 |
| 103  | 3-105 |    92 |
| 105  | 3-105 |    88 |
| 109  | 3-105 |    76 |
+------+-------+-------+
6 rows in set (0.00 sec)

mysql> -- 也可以使用多层嵌套子查询的方式
mysql> -- 1. 通过教师表，先找出 '计算机系' 老师的教师编号
mysql> SELECT t_no FROM teacher WHERE department='计算机系';

mysql> -- 2. 通过课程表，再找出对应老师所教授课程的课程编号
mysql> SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE department='计算机系');

mysql> -- 2. 通过成绩表，找出对应课程的成绩
mysql> SELECT * FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE department='计算机系'));
mysql> -- 结果同上

26. 查询 ‘计算机系’ 与 ‘电子工程系’ 中不同职称的教师的所有记录 (职称相同的不输出)

换句话说，题目大意就是，找出两个系中职称互补的教师，职称相交的不要
电子工程系中已有的职称，计算机系中不能有人有，反过来计算机系中有的职称，电子工程系不能有人有
两个系的职称是互补的，互补的输出需要将查询结果进行联合，使用 UNION 关键字联合两个查询的结果

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM teacher WHERE department='计算机系' AND title NOT IN (SELECT title FROM teacher WHERE department='电子工程系')
    -> UNION
    -> SELECT * FROM teacher WHERE department='电子工程系' AND title NOT IN (SELECT title FROM teacher WHERE department='计算机系');
+------+--------+----------+---------------------+--------+------------+
| t_no | t_name | t_gender | t_birthday          | title  | department |
+------+--------+----------+---------------------+--------+------------+
| 804  | 李诚   | 男       | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 856  | 张旭   | 男       | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+------+--------+----------+---------------------+--------+------------+
2 rows in set (0.01 sec)

27. 查询在 ‘3-105’ 课程中考得至少比其中一些 ‘3-245’ 课程好的同学的 c_no, s_no 和 grade, 并且按照 grade 从高到低次序排序

只要 ‘3-105’ 课程的学生成绩比其中至少一个 ‘3-245’ 课程的学生成绩好即符合要求，要表达 任意一个 的目的，可以使用 ANY() 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT * FROM score WHERE c_no='3-105' AND grade>ANY(SELECT grade FROM score WHERE c_no='3-245') ORDER BY grade DESC;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-105 |    92 |
| 105  | 3-105 |    88 |
| 109  | 3-105 |    76 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> -- 也可以让选修 3-105 的同学成绩与选修 3-245 的同学的最低成绩作比较
mysql> SELECT * FROM score WHERE c_no='3-105' AND grade>(SELECT MIN(grade) FROM score WHERE c_no='3-245') ORDER BY grade DESC;
mysql> -- 结果同上

28. 查询在 ‘3-105’ 课程中考得比 ‘3-245’ 课程中都好的同学的 c_no, s_no 和 grade

相比于 T27，本题要表达的是 都要好 的概念，因此需要使用 ALL() 函数

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM score WHERE c_no='3-105' AND grade>ALL(SELECT grade FROM score WHERE c_no='3-245');
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-105 |    92 |
| 105  | 3-105 |    88 |
+------+-------+-------+
2 rows in set (0.00 sec)

mysql> -- 也可以让选修 3-105 的同学成绩与选修 3-245 的同学的最高成绩成绩作比较
mysql> SELECT * FROM score WHERE c_no='3-105' AND grade>(SELECT MAX(grade) FROM score WHERE c_no='3-245');
mysql> -- 结果同上

29. 查询所有教师和同学的 name、gender、birthday

因为 t_name 和 s_name 都表示姓名，并且在联合查询的结果中，会发现只有 t_name、t_gender、t_birthday，所以为了统一，可以使用别名，利用 AS 关键字即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SELECT t_name AS name, t_gender AS gender, t_birthday AS birthday FROM teacher
    -> UNION
    -> SELECT s_name, s_gender, s_birthday FROM student;
+--------+--------+---------------------+
| name   | gender | birthday            |
+--------+--------+---------------------+
| 李诚   | 男     | 1958-12-02 00:00:00 |
| 王萍   | 女     | 1972-05-05 00:00:00 |
| 刘冰   | 女     | 1977-08-14 00:00:00 |
| 张旭   | 男     | 1969-03-12 00:00:00 |
| 曾华   | 男     | 1977-09-01 00:00:00 |
| 匡明   | 男     | 1975-10-02 00:00:00 |
| 王丽   | 女     | 1976-01-23 00:00:00 |
| 李军   | 男     | 1976-02-20 00:00:00 |
| 王芳   | 女     | 1975-02-10 00:00:00 |
| 陆军   | 男     | 1974-06-03 00:00:00 |
| 王尼玛 | 男     | 1976-02-20 00:00:00 |
| 张全蛋 | 男     | 1975-02-10 00:00:00 |
| 赵铁柱 | 男     | 1974-06-03 00:00:00 |
+--------+--------+---------------------+
13 rows in set (0.00 sec)

30. 查询所有 ‘女’ 教师和 ‘女’ 学生的 name、gender 和 birthday

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT t_name AS name, t_gender AS gender, t_birthday AS birthday FROM teacher WHERE t_gender='女'
    -> UNION
    -> SELECT s_name, s_gender, s_birthday FROM student WHERE s_gender='女';
+------+--------+---------------------+
| name | gender | birthday            |
+------+--------+---------------------+
| 王萍 | 女     | 1972-05-05 00:00:00 |
| 刘冰 | 女     | 1977-08-14 00:00:00 |
| 王丽 | 女     | 1976-01-23 00:00:00 |
| 王芳 | 女     | 1975-02-10 00:00:00 |
+------+--------+---------------------+
4 rows in set (0.00 sec)

31. 查询成绩比该课程平均成绩低的同学的成绩表

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM score AS a WHERE grade<(SELECT AVG(grade) FROM score AS b WHERE a.c_no=b.c_no);
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 105  | 3-245 |    75 |
| 105  | 6-166 |    79 |
| 109  | 3-105 |    76 |
| 109  | 3-245 |    68 |
| 109  | 6-166 |    81 |
+------+-------+-------+
5 rows in set (0.00 sec)

32. 查询所有任课教师的 t_name 和 department

要注意是任课教师，所以需要结合课程表来查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT t_name, department FROM teacher, course WHERE teacher.t_no=course.t_no;
+--------+------------+
| t_name | department |
+--------+------------+
| 李诚   | 计算机系   |
| 王萍   | 计算机系   |
| 刘冰   | 电子工程系 |
| 张旭   | 电子工程系 |
+--------+------------+
4 rows in set (0.00 sec)

mysql> -- 也可以用子查询的方式
mysql> SELECT t_name, department FROM teacher WHERE t_no IN (SELECT t_no FROM course);
mysql> -- 结果同上

33. 查出至少有2名男生的班号

1
2
3
4
5
6
7
8
mysql> SELECT class FROM student WHERE s_gender='男' GROUP BY class HAVING COUNT(*)>1;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set (0.00 sec)

34. 查询 student 表中不姓 ‘王’ 的同学的记录

模糊查询可以使用 LIKE 关键字，如果要对模糊查询结果取反可以使用 NOT LIKE
也可以对复杂字符串匹配使用正则表达式，使用关键字 REGEXP

不姓 '王' 的同学 的正则表达式：^[^王].+，第一个 ^ 表示要匹配的字符串的开头，[] 内的 ^ 表示剔除方括号内的内容， ^[^王] 表示第字符串开头不为王，
. 表示任意任意字符，.+ 表示匹配任意字符一次或多次，因为中文名字不会只有姓氏，还至少包含一个名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT * FROM student WHERE s_name NOT LIKE '王%';
+------+--------+----------+---------------------+-------+
| 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 |
| 104  | 李军   | 男       | 1976-02-20 00:00:00 | 95033 |
| 106  | 陆军   | 男       | 1974-06-03 00:00:00 | 95031 |
| 108  | 张全蛋 | 男       | 1975-02-10 00:00:00 | 95031 |
| 109  | 赵铁柱 | 男       | 1974-06-03 00:00:00 | 95031 |
+------+--------+----------+---------------------+-------+
6 rows in set (0.00 sec)

mysql> -- 也可以使用正则表达式来查询不姓王的同学
mysql> SELECT * FROM student WHERE s_name REGEXP '^[^王].+';
mysql> -- 结果同上

35. 查询 student 中每个学生的姓名和年龄

年龄 = 当前时间 - 出生年份，当前时间可以通过 NOW() 函数获取，年份可以通过 YEAR() 函数获取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;
+--------+------+
| s_name | age  |
+--------+------+
| 曾华   |   43 |
| 匡明   |   45 |
| 王丽   |   44 |
| 李军   |   44 |
| 王芳   |   45 |
| 陆军   |   46 |
| 王尼玛 |   44 |
| 张全蛋 |   45 |
| 赵铁柱 |   46 |
+--------+------+
9 rows in set (0.00 sec)

36. 查询 student 表中最大和最小的 s_birthday 的值

1
2
3
4
5
6
7
mysql> SELECT MAX(s_birthday), MIN(s_birthday) FROM student;
+---------------------+---------------------+
| MAX(s_birthday)     | MIN(s_birthday)     |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

37. 以班级号和年龄从大到小的顺序查询 student 表中的全部记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT * FROM student ORDER BY class DESC, s_birthday;
+------+--------+----------+---------------------+-------+
| s_no | s_name | s_gender | s_birthday          | class |
+------+--------+----------+---------------------+-------+
| 103  | 王丽   | 女       | 1976-01-23 00:00:00 | 95033 |
| 104  | 李军   | 男       | 1976-02-20 00:00:00 | 95033 |
| 107  | 王尼玛 | 男       | 1976-02-20 00:00:00 | 95033 |
| 101  | 曾华   | 男       | 1977-09-01 00:00:00 | 95033 |
| 106  | 陆军   | 男       | 1974-06-03 00:00:00 | 95031 |
| 109  | 赵铁柱 | 男       | 1974-06-03 00:00:00 | 95031 |
| 105  | 王芳   | 女       | 1975-02-10 00:00:00 | 95031 |
| 108  | 张全蛋 | 男       | 1975-02-10 00:00:00 | 95031 |
| 102  | 匡明   | 男       | 1975-10-02 00:00:00 | 95031 |
+------+--------+----------+---------------------+-------+
9 rows in set (0.00 sec)

38. 查询所有 ‘男’ 教师及其所教授的课程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SELECT * FROM teacher, course WHERE t_gender='男' AND teacher.t_no=course.t_no;
+------+--------+----------+---------------------+--------+------------+-------+----------+------+
| t_no | t_name | t_gender | t_birthday          | title  | department | c_no  | c_name   | t_no |
+------+--------+----------+---------------------+--------+------------+-------+----------+------+
| 804  | 李诚   | 男       | 1958-12-02 00:00:00 | 副教授 | 计算机系   | 3-245 | 操作系统 | 804  |
| 856  | 张旭   | 男       | 1969-03-12 00:00:00 | 讲师   | 电子工程系 | 6-166 | 数字电路 | 856  |
+------+--------+----------+---------------------+--------+------------+-------+----------+------+
2 rows in set (0.00 sec)

mysql> -- 或者使用子查询的方式
mysql> SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_gender='男');
+-------+----------+------+
| c_no  | c_name   | t_no |
+-------+----------+------+
| 3-245 | 操作系统 | 804  |
| 6-166 | 数字电路 | 856  |
+-------+----------+------+
2 rows in set (0.00 sec)

39. 查询最高分同学的 s_no、c_no 和 grade

1
2
3
4
5
6
7
mysql> SELECT student.s_no, c_no, grade FROM student, score WHERE student.s_no=score.s_no AND grade=(SELECT MAX(grade) FROM score);
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-105 |    92 |
+------+-------+-------+
1 row in set (0.00 sec)

40. 查询和 ‘李军’ 同性别的所有同学的 s_name

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT s_name FROM student WHERE s_gender=(SELECT s_gender FROM student WHERE s_name='李军');
+--------+
| s_name |
+--------+
| 曾华   |
| 匡明   |
| 李军   |
| 陆军   |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
+--------+
7 rows in set (0.00 sec)

41. 查询和 ‘李军’ 同性别并且同班的所有同学的 s_name

1
2
3
4
5
6
7
8
9
mysql> SELECT s_name FROM student WHERE s_gender=(SELECT s_gender FROM student WHERE s_name='李军') AND class=(SELECT class FROM student WHERE s_name='李军');
+--------+
| s_name |
+--------+
| 曾华   |
| 李军   |
| 王尼玛 |
+--------+
3 rows in set (0.00 sec)

42. 查询所有选修 ‘计算机导论’ 课程的 ‘男’ 同学的成绩表

1
2
3
4
5
6
7
mysql>  SELECT * FROM score WHERE c_no=(SELECT c_no FROM course WHERE c_name='计算机导论' ) AND s_no IN (SELECT s_no FROM student WHERE s_gender='男');
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 109  | 3-105 |    76 |
+------+-------+-------+
1 row in set (0.00 sec)

43. 新建一张 rank 表，查询所有学生的 s_no、c_no和 level 列

新建一个 rank 表代表学生的成绩等级，并插入数据：

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE rank (
    low INT(3),
    upp INT(3),
    level char(1)
);

INSERT INTO rank VALUES (90, 100, 'A');
INSERT INTO rank VALUES (80, 89, 'B');
INSERT INTO rank VALUES (70, 79, 'C');
INSERT INTO rank VALUES (60, 69, 'D');
INSERT INTO rank VALUES (0, 59, 'E');

查询所有学生的 s_no、c_no和 level 列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT s_no, c_no, level FROM score, rank WHERE grade BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no  | level |
+------+-------+-------+
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+
9 rows in set (0.00 sec)

进阶查询练习 - 连接查询

SQL 共有四种连接查询

  1. 内连接 inner join 或 join
  2. 外连接
    1. 左连接 left join 或者 left outer join
    2. 右连接 right join 或者 right outer join
    3. 完全外连接 full join 或者 full outer join

数据准备

创建数据库

1
CREATE DATABASE join_select_test;

切换数据库

1
USE join_select_test;

创建 person 表

1
2
3
4
5
CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

创建 card 表

1
2
3
4
CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

添加数据

添加 person 表数据

1
INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);

添加 card 表数据

1
INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');

分析两张表发现，person 表并没有为 cardId 字段设置一个在 card 表中对应的 id 外键。如果设置了的话，person 中 cardId 字段值为 6 的记录就插不进去，因为该 cardId 值在 card 表中并对应的存在。

1. 内连接

要查询这两张表中有关系的数据，可以使用 JOIN 或者 INNER JOIN ( 内连接 ) 将它们连接在一起。内连接查询条件需要使用 ON 关键字

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
+------+------+--------+------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM person JOIN card on person.cardId = card.id;
mysql> -- 结果同上

可见内连接就是把两张表中有关联的数据连接在了一起，同样的效果也可以使用 WHERE 条件查询实现。

1
2
3
4
5
6
7
8
mysql> SELECT * FROM person, card WHERE person.cardId=card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
+------+------+--------+------+--------+
2 rows in set (0.00 sec)

2. 左外连接

表 A 左外连接表 B，那么表 A 中的列都会完整显示，而表 B 中对应的缺失部分会用 NULL 代替，可以使用 LEFT JOIN 或者 LEFT OUTER JOIN ( 左外连接 ) 将它们连接在一起

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
|    3 | 王五 |      6 | NULL | NULL   |
+------+------+--------+------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM person LEFT OUTER JOIN card on person.cardId = card.id;
mysql> -- 结果同上

左连接可以理解为，保证了左边表的完整性，而右边表为了配合左边表会将缺失值用 NULL 代替。

3. 右外连接

表 A 右外连接表 B，那么表 B 中的列都会完整显示，而表 A 中对应的缺失部分会用 NULL 代替，可以使用 RIGHT JOIN 或者 RIGHT OUTER JOIN ( 右外连接 ) 将它们连接在一起

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
| NULL | NULL |   NULL |    2 | 建行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 邮政卡 |
+------+------+--------+------+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM person RIGHT OUTER JOIN card on person.cardId = card.id;
mysql> -- 结果同上

与左外连接正好相反的理解，右外连接保证了右边表的完整性，而左边表为了配合右边表会将缺失值用 NULL 代替。

4. 全外连接

注意 MySQL 不支持全外连接关键字（FULL JOIN 或者 FULL OUTER JOIN），但是可以使用 UNION 来实现全外连接的效果

全外连接，就是保证两张表的数据完整性，而两张表对应部分的缺失值就会用 NULL 代替

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> -- 使用 UNION 把左外连接的结果和右外连接的结果联合，就可以得到全外连接的查询结果
mysql> SELECT * FROM person LEFT JOIN card on person.cardId = card.id
    -> UNION
    -> SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
|    3 | 王五 |      6 | NULL | NULL   |
| NULL | NULL |   NULL |    2 | 建行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 邮政卡 |
+------+------+--------+------+--------+
6 rows in set (0.00 sec)

为了便于理解，下面用文氏图来表示两张表四种连接查询的关系

根据文氏图的表达结果，还可以有另外几种情况，下面将所有情况统一列举

文章作者: Inno Fang
文章链接: https://innofang.github.io/2020/03/31/重学MySQL之查询练习/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-ND 4.0 许可协议。转载请注明来自 Inno's Blog！
MySQL