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练习题