Mysql经典入门练习题

在网上找了些SQL练习题做，但是网上很多题目和答案要么比较老旧了，要么不适用于MySQL，要么代码风格不好，所以我进行了适当修改加工。现在将改进版的题目和答案分享一下。

我使用的是MYSQL 5.5，Win环境，使用的编程环境是MySQL Workbench。

注：要是有错误或者改进优化建议，请给我发邮件，万分感谢：qiuyuling98@163.com。

原贴地址： https://blog.csdn.net/qaz13177_58_/article/details/5575711/ （原贴使用的是MySQL 5.0）

建库/表

首先是建库和建表（都是简单的DDL语句和DML语句，没有用上索引主键之类的）：

 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


# 建库
DROP DATABASE IF EXISTS practice;
CREATE DATABASE practice;
USE practice;

# 建表
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),
	dept VARCHAR(10) NOT NULL
);

INSERT INTO student (sno,sname,ssex,sbirthday,class) 
VALUES (108 ,'曾华','男' ,'1977-09-01',95033),
	(105 ,'匡明','男' ,'1975-10-02',95031),
    (107 ,'王丽','女' ,'1976-01-23',95033),
    (101 ,'李军','男' ,'1976-02-20',95033),
    (109 ,'王芳','女' ,'1975-02-10',95031),
    (103 ,'陆君','男' ,'1974-06-03',95031);

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

INSERT INTO score(sno,cno,degree)
VALUES (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),
    (108,'3-105',78),
    (101,'6-166',85),
    (107,'6-106',79),
    (108,'6-166',81);

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

题目一览

1、查询student表中的所有记录的sname、ssex和class列。 2、查询教师所有的单位即不重复的dept列。 3、查询student表的所有记录。 4、查询score表中成绩在60到80之间的所有记录。 5、查询score表中成绩为85，86或88的记录。 6、查询student表中“95031”班或性别为“女”的同学记录。 7、以class降序查询student表的所有记录。 8、以cno升序、degree降序查询score表的所有记录。 9、查询“95031”班的学生人数。 10、查询score表中的最高分的学生学号和课程号。 11、查询‘3-105’号课程的平均分。 12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。 13、查询最低分大于70，最高分小于90的sno列。 14、查询所有学生的sname、cno和degree列。 15、查询所有学生的sno、cname和degree列。 16、查询所有学生的sname、cname和degree列。 17、查询“95033”班所选课程的平均分。 18、假设使用如下命令建立了一个grade表：

1
2
3
4
5
6
7
8


CREATE TABLE grade(
    low INT(3),
    upp INT(3),
    rank CHAR(1)
);

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

现查询所有同学的sno、cno和rank列。 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。 21、查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列。 22、查询“张旭“教师任课的学生成绩。 23、查询选修某课程的同学人数多于5人的教师姓名。 24、查询95033班和95031班全体学生的成绩记录。 25、查询存在有85分以上成绩的课程cno. 26、查询出“计算机系“教师所教课程的成绩表。 27、查询“计算机系”中与“电子工程系“教师不同职称的教师的tname和prof。 28、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序。 29、查询所有教师和同学的name、sex和birthday. 30、查询所有“女”教师和“女”同学的name、sex和birthday. 31、查询成绩比该课程平均成绩低的同学的成绩表。 32、查询所有任课教师的tname和dept. 33、查询所有未讲课的教师的tname和dept. 34、查询至少有2名男生的班号。 35、查询student表中不姓“王”的同学记录。 36、查询student表中每个学生的姓名和年龄。 37、查询student表中最大和最小的sbirthday日期值。 38、以班号和年龄从大到小的顺序查询student表中的全部记录。 39、查询“男”教师及其所上的课程。 40、查询最高分同学的sno、cno和degree列。 41、查询和“李军”同性别的所有同学的sname. 42、查询和“李军”同性别并同班的同学sname. 43、查询所有选修“计算机导论”课程的“男”同学的成绩表

emmmm~是不是被吓到了，其实都是非常非常简单基础的题，但是也不要眼高手低，请一定自己练习后再看以下“参考答案”。

参考答

  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
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265


# 1、 查询student表中的所有记录的sname、ssex和class列
SELECT sname, ssex, class FROM student;
# 2、 查询教师所有的单位即不重复的dept列
SELECT DISTINCT dept FROM teacher;
# 3、 查询student表的所有记录
SELECT * FROM student;
# 4、 查询score表中成绩在60到80之间的所有记录
SELECT * FROM score
WHERE degree BETWEEN 60 AND 80;
# 5、 查询score表中成绩为85，86或88的记录
SELECT * FROM score
WHERE degree IN (85, 86, 88);
# 6、 查询student表中“95031”班或性别为“女”的同学记录
SELECT * FROM student
WHERE class = '95031' OR ssex = '女';
# 7、 以class降序查询student表的所有记录
SELECT * FROM student
ORDER BY class DESC;
# 8、 以cno升序、degree降序查询score表的所有记录
SELECT * FROM score
ORDER BY cno, degree DESC;
# 9、 查询“95031”班的学生人数
SELECT COUNT(*)
FROM student
WHERE class = '95031';
# 10、查询score表中的最高分的学生学号和课程号
SELECT sno, cno
FROM score
WHERE degree = (
	SELECT MAX(degree)
    FROM score
);
# 或
SELECT sno, cno 
FROM score
ORDER BY degree DESC
LIMIT 1;
# 11、查询‘3-105’号课程的平均分
SELECT AVG(degree)
FROM score
WHERE cno = '3-105';
# 12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT cno, AVG(degree)
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(sno) >= 5
# 13、查询最低分大于70，最高分小于90的sno列
SELECT sno
FROM score
GROUP BY sno
HAVING MIN(degree) > 70
AND MAX(degree) < 90;
# 14、查询所有学生的sname、cno和degree列
SELECT s.sname, sc.cno, sc.degree
FROM student s
LEFT JOIN score sc
ON s.sno = sc.sno;
# 15、查询所有学生的sno、cname和degree列
SELECT sc.sno, c.cname, sc.degree
FROM course c
LEFT JOIN score sc
ON c.cno = sc.cno;
# 16、查询所有学生的sname、cname和degree列
SELECT sname, cname, degree
FROM student s
LEFT JOIN score sc
ON s.sno = sc.sno
LEFT JOIN course c
ON c.cno = sc.cno;
# 17、查询“95033”班所选课程的平均分
SELECT AVG(degree)
FROM student s
JOIN score sc
ON s.sno = sc.sno
WHERE s.class = '95033';
# 18、结合grade表查询所有同学的sno、cno和rank列
SELECT s.sno, s.cno, g.rank
FROM score s
JOIN grade g
ON s.degree BETWEEN g.low AND g.upp;
# 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
SELECT * 
FROM score
WHERE cno = '3-105'
AND degree > (
    SELECT degree
    FROM score
    WHERE sno = '109' AND cno = '3-105'
);
## 此题有多种解法，比如可以使用自连接，我个人倾向使用子查询,简单易懂……
SELECT A.*
FROM score A
WHERE A.cno = '3-105' AND A.degree > ALL(
    SELECT degree
    FROM score B
    WHERE B.sno = '109' AND B.cno = '3-105'
);
# 20、查询score中选学一门以上课程的同学中分数为非（score中所有degree的）最高分成绩的记录(这题没啥意义，可以改成查询非本人成绩最高分的记录)
SELECT *
FROM score s
WHERE degree < (
    SELECT MAX(degree)
    FROM score
)
GROUP BY sno
HAVING COUNT(sno) > 1
ORDER BY degree;
# 21、查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列
SELECT sno, sname, sbirthday
FROM student
WHERE YEAR(sbirthday) = (
    SELECT YEAR(sbirthday)
    FROM student
    WHERE sno = '108'
);
# 22、查询“张旭“教师任课的学生成绩
SELECT sc.*
FROM score sc
JOIN course c
ON c.cno = sc.cno
JOIN teacher t
ON c.tno = t.tno
WHERE t.tname = '张旭';
# 23、查询选修某课程的同学人数多于5人的教师姓名
SELECT cname, tname
FROM course c
JOIN teacher t
ON c.tno = t.tno
JOIN score sc
ON c.cno = sc.cno
GROUP BY sc.cno
HAVING COUNT(sc.cno) > 5;
# 24、查询95033班和95031班全体学生的成绩记录
SELECT *
FROM score
WHERE cno IN(
    SELECT cno
	FROM student
	WHERE class IN('95033', '95031')
);
# 25、查询存在有85分以上成绩的课程cno
SELECT DISTINCT cno
FROM score
WHERE degree > 85;
# 26、查询出“计算机系“教师所教课程的成绩表
SELECT sc.*
FROM score sc
WHERE cno IN(
	SELECT c.cno
    FROM course c
    JOIN teacher t
    ON c.tno = t.tno
    WHERE t.dept = '计算机系'
);
# 27、查询“计算机系”中与“电子工程系“教师不同职称的教师的tname和prof
SELECT tname,prof
FROM teacher
WHERE dept = '计算机系'
AND prof NOT IN (
	SELECT prof
    FROM teacher
    WHERE dept='电子工程系'
);
# 28、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序
SELECT sc.cno, sc.sno, sc.degree
FROM score sc
JOIN course c
ON sc.cno = c.cno
WHERE c.cno = '3-105'
AND sc.degree > ANY(SELECT degree FROM score WHERE cno = '3-245')
ORDER BY sc.degree;
# 29、查询所有教师和同学的name、sex和birthday
SELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM student
UNION
SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacher;
# 30、查询所有“女”教师和“女”同学的name、sex和birthday
SELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM student WHERE ssex = '女'
UNION
SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacher WHERE tsex = '女';
# 31、查询成绩比该课程平均成绩低的同学的成绩表
SELECT sc.*
FROM score sc
JOIN (
    SELECT cno, AVG(degree) AS avg_degree
	FROM score
	GROUP BY cno
)AS d
ON sc.cno = d.cno
AND sc.degree < d.avg_degree;
# 32、查询所有任课教师的tname和dept
SELECT tname, dept
FROM teacher t
JOIN course c
ON t.tno = c.tno;
# 33、查询所有未讲课的教师的tname和dept
SELECT tname, dept
FROM teacher t
LEFT JOIN course c
ON t.tno = c.tno
WHERE c.tno IS NULL;
# 34、查询至少有2名男生的班号
SELECT class
FROM student
WHERE ssex = '男'
GROUP BY class
HAVING COUNT(sno) >= 2;
# 35、查询student表中不姓“王”的同学记录
SELECT *
FROM student
WHERE sname NOT LIKE '王%';
# 36、查询student表中每个学生的姓名和年龄
SELECT sname, YEAR(NOW()) - YEAR(sbirthday) AS '年龄'
FROM student;
# 37、查询student表中最大和最小的sbirthday日期值
SELECT sname, sbirthday AS '出生日期' FROM student WHERE sbirthday = (SELECT MIN(sbirthday) FROM student)
UNION
SELECT sname, sbirthday AS '出生日期' FROM student WHERE sbirthday = (SELECT MAX(sbirthday) FROM student);
# 38、以班号和年龄从大到小的顺序查询student表中的全部记录
SELECT *, (YEAR(NOW()) - YEAR(sbirthday))  AS age
FROM student
ORDER BY class DESC, age DESC;
# 39、查询“男”教师及其所上的课程
SELECT tname, cname
FROM teacher t
JOIN course c
ON t.tno = c.tno
WHERE tsex = '男';
# 40、查询最高分同学的sno、cno和degree列
SELECT sno, cno, degree
FROM score
WHERE degree = (
	SELECT MAX(degree)
    FROM score
);
# 41、查询和“李军”同性别的所有同学的sname
SELECT sname
FROM student
WHERE ssex = (
    SELECT ssex
    FROM student
    WHERE sname = '李军'
);
# 42、查询和“李军”同性别并同班的同学sname
SELECT sname
FROM student
WHERE ssex = (
    SELECT ssex
    FROM student
    WHERE sname = '李军'
)
AND class = (
    SELECT class
    FROM student
    WHERE sname = '李军'
);
# 43、查询所有选修“计算机导论”课程的“男”同学的成绩表 
SELECT sc.*
FROM score sc
JOIN course c
ON c.cno = sc.cno
JOIN student s
ON s.sno = sc.sno
WHERE s.ssex = '男'
AND c.cname = '计算机导论';

文章目录

建库/表

题目一览

参考答