重学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 joinjoin
  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 外键。如果设置了的话,personcardId 字段值为 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