MySQL 经典练习题(第一套)

经典的 MySQL 练习题。

这个练习题感觉挺经典的,我人工筛选了一些我认为有练习价值的,特别简单的没有收录进来。

创建表的代码

需要自己先创建一个数据库。然后再创建表。

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
CREATE TABLE STUDENT
(
SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)
);

CREATE TABLE COURSE
(
CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL
);

CREATE TABLE SCORE
(
SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL
);

CREATE TABLE TEACHER
(
TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL,
PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL
);

INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (108, '曾华'
, '男', '1977-09-01', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (105, '匡明'
, '男', '1975-10-02', 95031);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (107, '王丽'
, '女', '1976-01-23', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (101, '李军'
, '男', '1976-02-20', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (109, '王芳'
, '女', '1975-02-10', 95031);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (103, '陆君'
, '男', '1974-06-03', 95031);

INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('3-105', '计算机导论', 825);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('3-245', '操作系统', 804);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('6-166', '数据电路', 856);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('9-888', '高等数学', 100);

INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (103, '3-245', 86);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (105, '3-245', 75);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (109, '3-245', 68);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (103, '3-105', 92);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (105, '3-105', 88);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (109, '3-105', 76);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (101, '3-105', 64);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (107, '3-105', 91);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (101, '6-166', 85);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (107, '6-106', 79);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '3-105', 78);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '6-166', 81);

INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (804, '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (856, '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (825, '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (831, '刘冰', '女', '1977-08-14', '助教', '电子工程系');

练习题

查询Score表中成绩在60到80之间的所有记录

1
2
3
SELECT *
FROM score
WHERE dergee > 60 and degree < 80;

1
2
3
SELECT *
FROM score
WHERE dergee BETWEEN 60 AND 80;

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

1
2
3
SELECT *
FROM score
WHERE degree = 85 or degree = 86 or degree = 88;

1
2
3
SELECT *
FROM score
WHERE degree IN (85, 86, 88);

以 cno 升序、degree降序查询 score 表的所有记录

1
2
3
SELECT *
FROM score
ORDER BY cno ASC, degree DESC;

注意 ORDER BY 子句中的顺序,交换顺序之后是不一样的。首先根据第一个字段排序,如果第一个字段相同,再根据第二个字段排序。

查询“95031”班的学生人数。

1
2
3
SELECT COUNT(*)
FROM STUDENT
WHERE class = '95031';

MySQL 中 COUNT() 函数的使用。

查询Score表中的最高分的学生学号和课程号

1
2
3
4
SELECT sno, cno
FROM score
ORDER BY degree DESC
LIMIT 1 OFFSET 0; -- 可以简写为 LIMIT 1;

下面的写法也可以,但是效率不高。

1
2
3
4
5
6
SELECT sno, cno
FROM score
WHERE degree = (
SELECT max(degree)
FROM score
);

查询‘3-105’号课程的平均分。

1
2
3
SELECT AVG(degree)
FROM score
WHERE cno = '3-105';

MySQL 的 AVG 函数的使用。

查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

1
2
3
4
5
SELECT  cno, AVG(degree)
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(*) > 5;

查询最低分大于70,最高分小于90的Sno列。

本题乍一看不太好理解,可以翻译为:查询所有课程成绩最低分大于70分,最高分小于90分的学生的学号。

题意暗含了要根据 sno 分组的意思。

1
2
3
4
SELECT sno
FROM score
GROUP BY sno
HAVING min(degree) > 70 AND max(degree) < 90;

查询所有学生的Sname、Cno和Degree列。

1
2
3
SELECT sname, cno, degree
FROM student, score
WHERE student.sno = score.sno;

查询所有学生的Sno、Cname和Degree列。

1
2
3
SELECT score.sno, cno, degree
FROM student, score
WHERE student.sno = score.sno;

查询所有学生的Sname、Cname和Degree列。

1
2
3
4
SELECT a.sname, b.cname, c.degree
FROM student a
JOIN (course b, score c)
ON a.sno = c.sno AND b.cno = c.cno;

查询“95033”班所选课程的平均分。

1
2
3
4
5
6
SELECT AVG(degree)
FROM score
WHERE sno IN
(SELECT sno
FROM student
WHERE class = '95033');

查询所有同学的Sno、Cno和rank列。

首先,假设使用如下命令建立了一个grade表:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE grade (
low NUMERIC (3, 0),
upp NUMERIC (3),
rank CHAR (1)
);

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

答案:

1
2
3
4
SELECT a.sno, a.cno, b.rank
FROM score a, grade b
WHERE a.degree BETWEEN b.low AND b.upp
ORDER BY rank;

1
2
3
4
SELECT a.Sno, a.Cno, b.rank
FROM score a
JOIN grade b ON a.degree >= b.low AND a.degree <= b.upp
ORDER BY rank;

其中使用 BETWEEN ... AND 来进行表的连接。

查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

1
2
3
4
5
6
7
SELECT *
FROM score
WHERE cno = '3-105'
AND degree >
(SELECT degree
FROM score
WHERE sno = '109' AND cno = '3-105');

查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

这道题目有歧义,请参考本文末尾,我写了一大段用来解释这道题。

查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

1
2
3
4
5
6
SELECT sno, sname, sbirthday
FROM student
WHERE YEAR(sbirthday) =
(SELECT YEAR(sbirthday)
FROM student
WHERE sno = '108');

涉及到函数 YEAR() 的使用。

查询“张旭“教师任课的学生成绩。

1
2
3
4
5
6
7
8
SELECT *
FROM score
WHERE cno =
(SELECT cno
FROM course
JOIN teacher
ON course.tno = teacher.tno
AND tname = '张旭');

查询选修某课程的同学人数多于5人的教师姓名。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT tname
FROM teacher
WHERE tno IN
(SELECT tno
FROM course
WHERE cno IN
(SELECT cno
FROM score
GROUP BY cno
HAVING count(sno) > 5));

-- 或

SELECT tname
FROM teacher
WHERE tno IN
(SELECT c.tno
FROM course c,score s
WHERE c.cno = s.cno
GROUP BY c.tno
HAVING COUNT(c.tno) > 5);

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

1
2
3
SELECT *
FROM student
WHERE CLASS IN ('95033', '95031');

查询存在有85分以上成绩的课程Cno。

1
2
3
4
SELECT cno
FROM score
GROUP BY cno
HAVING MAX(degree) > 85;

查询出“计算机系“教师所教课程的成绩表。

1
2
3
4
5
6
7
SELECT *
FROM score
WHERE cno IN
(SELECT cno
FROM course, teacher
WHERE depart = '计算机系'
AND course.tno = teacher.tno);

查询所有教师和同学的name、sex和birthday。

1
2
3
4
5
SELECT tname name, tsex sex, tbirthday birthday
FROM teacher
UNION
SELECT sname name, ssex sex, sbirthday birthday
FROM student;

其中使用到了集合运算 UNION

查询所有“女”教师和“女”同学的name、sex和birthday。

1
2
3
4
5
6
7
SELECT tname name, tsex sex, tbirthday birthday
FROM teacher
WHERE tsex = '女'
UNION
SELECT sname name, ssex sex, sbirthday birthday
FROM student
WHERE ssex = '女';

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

1
2
3
4
5
6
SELECT a.*
FROM score a
WHERE degree <
(SELECT AVG(degree)
FROM score b
WHERE b.cno = a.cno);

查询所有任课教师的Tname和Depart。

1
2
3
4
5
SELECT tname, depart
FROM teacher a
WHERE tno IN
(SELECT DISTINCT tno
FROM course);

查询至少有2名男生的班号。

1
2
3
4
5
SELECT CLASS
FROM student
WHERE ssex = '男'
GROUP BY CLASS
HAVING count(ssex) > 1;

查询Student表中不姓“王”的同学记录。

1
2
3
SELECT *
FROM student
WHERE sname NOT LIKE "王%";

查询Student表中每个学生的姓名和年龄。

1
2
SELECT sname, YEAR(NOW()) - YEAR(sbirthday)
FROM student;

使用到了 NOW() 函数。

对查询score中选学一门以上课程的同学中分数为非最高分成绩的记录的辨析

假设有下面这样一张表,表名为 score

sno cno degree
103 3-245 86
105 3-245 75
109 3-245 68
103 3-105 92
105 3-105 88
109 3-105 76
101 3-105 64
107 3-105 91
101 6-166 85
108 3-105 79
108 6-166 81

考虑下面这个 SQL 语句

1
2
3
SELECT *
FROM score
GROUP BY sno;

你觉得这句 SQL 语句会返回什么?或许我们是希望返回按照 sno 分组之后的每个组中的所有记录。

实际上,这个语句只会返回每个分组中排名最靠前的一条记录(有可能是按照原表中数据的顺序排列的)。

假设我们想要查询 score 中选学一门以上课程的同学中分数为非最高分成绩的记录

我们可以能会写出这样的 SQL 语句:

1
2
3
4
SELECT *
FROM score
GROUP BY sno
HAVING COUNT(cno) > 1 AND degree != MAX(degree);

HAVING degree != MAX(degree) 会对每个分组中的每一条记录进行判断吗?

实际上并不会,只会对每个分组中排名最靠前的一条记录进行判断。所以这个 SQL 并不会正确地返回期望的答案。其实 SQL 标准是不允许有这种存在含混不清的语句存在的,可以参考关于 MySQL 的 ONLY_FULL_GROUP_BY 的相关内容。

因为本题的题目的表述有歧义,可能有两种解读:

其一种是查询 score 中选学一门以上课程的同学中,分数不是其自己所有课程成绩的最高分的记录。

另一种是查询 score 中选学一门以上课程的同学中,其课程分数不是该课程所有学生成绩最高分的记录。

针对第一种理解方式的答案:

1
2
3
4
5
6
SELECT a.*
FROM score a
JOIN (SELECT sno, MAX(degree) AS max_degree
FROM score
GROUP BY sno) b
ON a.sno = b.sno AND a.degree != b.max_degree;

这个写法没有显式地体现出“选学一门以上课程”这个条件,是因为如果一个人只选了一门课程,那么这门课程的成绩在子查询中一定是 max_degree,所以在外层查询一定会被过滤掉。

针对第二种理解方式的答案:

1
2
3
4
5
6
SELECT a.*
FROM score a
JOIN (SELECT cno, MAX(degree) AS max_degree
FROM score
GROUP BY cno) b
ON a.cno = b.cno AND a.degree != b.max_degree;

针对于第一种理解方式,这还有一个错误的答案,供参考,用来防止踩坑:

1
2
3
4
5
6
7
8
9
-- 这个不行,因为一个学生的不是最大成绩的记录,
-- 有可能和别的学生的最大成绩相同,所以不能使用 NOT IN
SELECT *
FROM score
GROUP BY sno, cno
HAVING degree
NOT IN (SELECT MAX(degree)
FROM score
GROUP BY sno);

题目来源

经过一番搜索,发现这套题出自《SQL Server 2000应用系统开发教程》。

豆瓣链接:SQL Server 2000应用系统开发教程 - 2005版

上边那个是2005版的,没有图书封面图片。还有一个2008版的链接,有图书封面图片:SQL Server 2000应用系统开发教程 - 2008版

Google Books 可以查看这本书的电子版的部分章节:SQL Server 2000应用系统开发教程 - Google Books

如何找到的(用了什么搜索技巧)

使用谷歌搜索 曾华 匡明 王丽 教材,就直接找到 Google Books 提供的信息了。

参考资料

  1. 题目来源:sql 经典练习题 · CyC2018/CS-Notes
  2. 还有另外一套题:sql语句多表查询(学生表/课程表/教师表/成绩表 )
  3. 【SQL练习】经典SQL练习题