mysql练习题

本文最后更新于:1 个月前

数据

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
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`s_score` int(3) NULL DEFAULT NULL,
PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;

题目

答案

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
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
1.
SELECT student.*,b.s_score as '课程1',c.s_score as '课程2' FROM student
INNER JOIN score b ON student.s_id=b.s_id AND b.c_id='01'
INNER JOIN score c ON student.s_id=c.s_id AND c.c_id='02'
WHERE b.s_score>c.s_score

2.
SELECT student.*,b.s_score as '课程1',c.s_score as '课程2' FROM student
INNER JOIN score b ON b.s_id=student.s_id AND b.c_id='01'
INNER JOIN score c ON c.s_id=student.s_id AND c.c_id='02'
WHERE b.s_score<c.s_score

3.
SELECT student.s_id,student.s_name,ROUND(AVG(score.s_score),2) AS avg_score
FROM student
INNER JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_score >= 60

4.
SELECT student.s_id,student.s_name,ROUND(AVG(IFNULL(score.s_score,0)),2) AS avg_score
FROM student
LEFT JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_score < 60
-----或者-----
SELECT student.s_id,student.s_name,ROUND(AVG(score.s_score),2) AS avg_score
FROM student
LEFT JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_score < 60 OR avg_score IS NULL

5.
SELECT student.s_id,student.s_name,COUNT(score.c_id) as '选课总数',SUM(score.s_score) as '总成绩'
from student
LEFT JOIN score ON student.s_id=score.s_id
GROUP BY student.s_id,student.s_name

6.
SELECT COUNT(teacher.t_name) FROM teacher WHERE teacher.t_name LIKE '李%'

7.
SELECT student.*,teacher.t_name,course.c_name FROM student
LEFT JOIN score on score.s_id = student.s_id
LEFT JOIN course on course.c_id = score.c_id
LEFT JOIN teacher ON teacher.t_id = course.t_id
WHERE teacher.t_name = '张三'

8.
SELECT * FROM student
WHERE student.s_id not in(
# 学生学过张三的课(上题得出)
SELECT student.s_id FROM student
LEFT JOIN score on score.s_id = student.s_id
LEFT JOIN course on course.c_id = score.c_id
LEFT JOIN teacher ON teacher.t_id = course.t_id
WHERE teacher.t_name = '张三'
)

9.
SELECT student.*,s1.s_score AS '01分数',s2.s_score AS '02分数'
FROM student
LEFT JOIN score s1 ON s1.s_id = student.s_id
LEFT JOIN score s2 ON s2.s_id = student.s_id
WHERE s1.c_id = '01' AND s2.c_id = '02'

10.
SELECT student.*,s1.s_score AS '01分数',s2.s_score AS '02分数'
FROM student
INNER JOIN score s1 ON s1.s_id = student.s_id AND s1.c_id = '01'
LEFT JOIN score s2 ON s2.s_id = student.s_id AND s2.c_id = '02'
WHERE s2.s_score IS NULL

11.
SELECT * FROM student
WHERE student.s_id IN (
SELECT score.s_id FROM score GROUP BY score.s_id HAVING COUNT(score.s_score)<3
)

12.
SELECT * FROM student WHERE student.s_id IN (
SELECT DISTINCT score.s_id FROM score WHERE score.c_id IN(
SELECT score.c_id FROM score WHERE score.s_id='01'
)
)

13.
SELECT * FROM student WHERE student.s_id IN (
SELECT score.s_id FROM score WHERE score.c_id IN(
SELECT score.c_id FROM score WHERE score.s_id='01'
)AND score.s_id != '01' GROUP BY score.s_id HAVING COUNT(score.c_id)>=3
)

14.
SELECT * FROM student
WHERE student.s_id NOT IN(
SELECT score.s_id FROM score
INNER JOIN course ON course.c_id = score.c_id
INNER JOIN teacher ON teacher.t_id = course.t_id
WHERE teacher.t_name = '张三'
)

15.
SELECT student.s_id,student.s_name,AVG(score.s_score) AS AVG_SCORE FROM student
INNER JOIN score on student.s_id = score.s_id
WHERE student.s_id in (
SELECT score.s_id FROM score WHERE score.s_score<60
)
GROUP BY student.s_id

16.
SELECT student.*,score.s_score AS 01_score FROM student
INNER JOIN score ON student.s_id = score.s_id AND score.s_score<60
WHERE score.c_id = '01'
ORDER BY 01_score DESC

17.
SELECT
student.*,
01_score.s_score AS 01_score,
02_score.s_score AS 02_score,
03_score.s_score AS 03_score,
AVG( sc.s_score ) AS AVG_score
FROM
student
LEFT JOIN score 01_score ON student.s_id = 01_score.s_id
AND 01_score.c_id = '01'
LEFT JOIN score 02_score ON student.s_id = 02_score.s_id
AND 02_score.c_id = '02'
LEFT JOIN score 03_score ON student.s_id = 03_score.s_id
AND 03_score.c_id = '03'
LEFT JOIN score sc ON student.s_id = sc.s_id
GROUP BY
student.s_id
ORDER BY
AVG_score DESC

18.
SELECT
score.c_id,
course.c_name,
MAX( score.s_score ) AS max_sc,
MIN( score.s_score ) AS min_sc,
AVG( score.s_score ) AS avg_sc
FROM
score
INNER JOIN course ON course.c_id = score.c_id
GROUP BY
score.c_id

19.
SET @scrank = 0;
SELECT
student.*,
score.c_id,
score.s_score,
@scrank := @scrank + 1 AS sc_rank
FROM
student
INNER JOIN score ON score.s_id = student.s_id
ORDER BY
score.c_id,
score.s_score DESC

20.
SET @scrank = 0;
SELECT
student.*,
SUM( score.s_score ) AS sum_sc,
@scrank := @scrank + 1 AS sc_rank
FROM
student
INNER JOIN score ON score.s_id = student.s_id
GROUP BY
student.s_id
ORDER BY
sum_sc DESC

21.
SELECT
teacher.*,
course.c_name,
AVG( score.s_score ) AS avg_sc
FROM
teacher
LEFT JOIN course ON course.t_id = teacher.t_id
LEFT JOIN score ON score.c_id = course.c_id
GROUP BY
teacher.t_id,
course.c_name
ORDER BY
avg_sc DESC

22.
(SELECT st.*,sc.c_id,sc.s_score FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '01' ORDER BY sc.s_score DESC LIMIT 1,2)
UNION ALL
(SELECT st.*,sc.c_id,sc.s_score FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '02' ORDER BY sc.s_score DESC LIMIT 1,2)
UNION ALL
(SELECT st.*,sc.c_id,sc.s_score FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '03' ORDER BY sc.s_score DESC LIMIT 1,2)

23.
SELECT c.c_id,c.c_name
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=100 AND sc.s_score>80)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "100-85"
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=85 AND sc.s_score>70)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "85-70"
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=70 AND sc.s_score>60)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "70-60"
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=60 AND sc.s_score>=0)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "60-0"
FROM course c ORDER BY c.c_id

24.
SELECT
student.s_id,
student.s_name,
ROUND(( CASE WHEN AVG( score.s_score ) IS NULL THEN 0 ELSE AVG( score.s_score ) END ), 2 ) AS avg_sc
FROM
student
INNER JOIN score ON score.s_id = student.s_id
GROUP BY
score.s_id
ORDER BY
avg_sc DESC

25.
SELECT
sc1.*
FROM
score sc1
WHERE
( SELECT COUNT(*) FROM score sc2 WHERE sc1.c_id = sc2.c_id AND sc1.s_score < sc2.s_score )< 3
ORDER BY
sc1.c_id,
sc1.s_score DESC

26.
SELECT score.c_id,course.c_name,COUNT(score.s_id) AS stu_num FROM score,course
WHERE course.c_id = score.c_id
GROUP BY score.c_id

27.
SELECT student.s_id,student.s_name
FROM student
WHERE student.s_id IN (
SELECT score.s_id FROM score
GROUP BY score.s_id
HAVING COUNT(score.s_id) =2
)

28.
SELECT student.s_sex,COUNT(student.s_sex) FROM student GROUP BY student.s_sex

29.
SELECT student.* FROM student WHERE student.s_name LIKE '%风%'

30.
# 为了重名,加一条学生信息
# INSERT INTO `practice_test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('09', '李云', '1992-01-02', '女')
# sql语句如下
SELECT student.s_name,COUNT(*) FROM student GROUP BY student.s_name HAVING COUNT(*)>1

31.
SELECT student.* FROM student WHERE student.s_birth LIKE '1990%'

32.
# 为了有平均分相同的情况,在分数表(score)中插入两条数据,
# s_id c_id s_score
# 08 04 69
# 09 04 68
SELECT score.c_id,AVG(score.s_score) AS avg_sc FROM score GROUP BY score.c_id ORDER BY avg_sc DESC,c_id

33.
SELECT student.s_id,student.s_name,AVG(score.s_score) AS avg_sc FROM student
INNER JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_sc >=85

34.
SELECT student.s_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score<60 and course.c_name='数学'

35.
SELECT student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id

36.
SELECT student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score>70

37.
SELECT student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score<60

38.
SELECT student.s_id,student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score>=80 AND score.c_id = '01'

39.
SELECT course.c_name,COUNT(score.s_score) AS st_num FROM course
LEFT JOIN score ON score.c_id=course.c_id
GROUP BY course.c_id

40.
SELECT student.*,score.s_score,teacher.t_name FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
INNER JOIN teacher ON teacher.t_id = course.t_id
WHERE teacher.t_name = '张三'
ORDER BY score.s_score DESC
LIMIT 0,1

41.
SELECT st.s_id,st.s_name,sc.c_id,sc.s_score
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
WHERE(
SELECT COUNT( * )
FROM student st2
LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
LEFT JOIN course c2 ON c2.c_id = sc2.c_id
WHERE sc.s_score = sc2.s_score
AND c.c_id <> c2.c_id
)>1

42.
SELECT a.* FROM score AS a
LEFT JOIN score as b
ON a.c_id = b.c_id AND a.s_score < b.s_score # 列出同一门课内所有分数比较的情况
GROUP BY a.c_id, a.s_id
HAVING COUNT(b.c_id)<=1 #他只比1个低,或者比0个低(即他就是第一)
ORDER BY a.c_id;

43.
SELECT score.c_id,COUNT(score.s_id) AS st_num FROM score
GROUP BY score.c_id
HAVING st_num>5

44.
SELECT sc.s_id,COUNT(sc.s_score) count_sc FROM score sc
GROUP BY sc.s_id
HAVING count_sc>=2

45.
SELECT student.* FROM student
WHERE student.s_id IN(
SELECT sc.s_id FROM score sc
GROUP BY sc.s_id
HAVING COUNT(sc.s_score)>2
)

46.
select *, TIMESTAMPDIFF(YEAR,student.s_birth,NOW()) as 年龄 from Student

47.
SELECT student.*,WEEK(student.s_birth),WEEK(NOW()) FROM student
WHERE WEEK(student.s_birth)=WEEK(NOW())

48.
SELECT student.*,WEEK(student.s_birth),WEEK(NOW()) FROM student
WHERE WEEK(student.s_birth)=WEEK(NOW())+1

49.
SELECT student.*,MONTH(student.s_birth),MONTH(NOW()) FROM student
WHERE MONTH(student.s_birth)=MONTH(NOW())

50.
SELECT student.*,MONTH(student.s_birth),MONTH(NOW()) FROM student
WHERE MONTH(student.s_birth)=MONTH(NOW())+1

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!