练习题和参考解
（1）查询“001”课程比“002”课程成绩低的所有学生的学号、001学科成绩、002学科成绩
1
SELECT
2
s1.StudentNo,
3
s1.score AS '001',
4
s2.score AS '002'
5
FROM
6
score s1,
7
(
8
SELECT
9
*
10
FROM
11
score s
12
WHERE
13
s.CourseNo = 2
14
) s2
15
WHERE
16
s1.CourseNo = 1
17
AND
18
s1.StudentNo = s2.StudentNo
19
AND
20
s1.score < s2.score
21
ORDER BY s1.StudentNo
1
SELECT
2
s1.StudentNo,
3
s1.score AS '001',
4
s2.score AS '002'
5
FROM
6
score s1,
7
score s2
8
WHERE
9
s1.CourseNo = 001
10
AND
11
s2.CourseNo = 002
12
AND
13
s1.StudentNo = s2.StudentNo
14
AND
15
s1.score < s2.score
16
ORDER BY s1.StudentNo
（2）查询平均成绩大于60分的同学的学号和平均成绩
1
SELECT
2
s1.StudentNo,
3
AVG(s1.score)
4
FROM
5
score s1
6
GROUP BY s1.StudentNo
7
HAVING AVG(s1.score)>60
（3）查询所有同学的学号、姓名、选课数、总成绩
1
SELECT
2
s1.StudentNo,
3
stu1.name,
4
COUNT(*),
5
SUM(s1.score)
6
FROM
7
score s1,
8
student stu1
9
WHERE
10
s1.StudentNo = stu1.StudentNo
11
GROUP BY s1.StudentNo
（4）查询姓“李”的老师的个数
1
SELECT
2
COUNT(*)
3
FROM
4
teacher t1
5
WHERE
6
t1.name like '李%'
（5）查询没学过“叶平”老师课的同学的学号、姓名
1
SELECT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
student stu1
6
WHERE
7
stu1.StudentNo NOT IN
8
(
9
SELECT DISTINCT
10
s1.StudentNo
11
FROM
12
score s1,
13
course c1,
14
teacher t1
15
WHERE
16
s1.courseNo = c1.CourseNo
17
AND
18
c1.teacherNo = t1.teacherNo
19
AND
20
t1.name = '叶平'
21
)
（6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
1
-- 这个算法比普通的要有想法
2
SELECT
3
s1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1
8
WHERE
9
s1.StudentNo = stu1.StudentNo
10
AND
11
s1.CourseNo IN (1, 2)
12
GROUP BY s1.StudentNo
13
HAVING COUNT(*) = 2
1
SELECT
2
s1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.CourseNo = 1
11
AND
12
s1.StudentNo IN
13
(
14
SELECT
15
s2.StudentNo
16
FROM
17
score s2
18
WHERE
19
s2.CourseNo = 2
20
)
（7）查询学过“叶平”老师所教的所有课的同学的学号、姓名
1
-- 利用主键值不相同，它们的和一定各不相同，叶平老师的课程的主键值和如果与学生所学的叶平老师的课程的主键值和相等，那么说明学了叶平老师所有课程
2
SELECT
3
stu1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1,
8
course c1,
9
teacher t1
10
WHERE
11
s1.StudentNo = stu1.StudentNo
12
AND
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.StudentNo
19
HAVING SUM(s1.CourseNo)=
20
(
21
SELECT
22
SUM(c2.CourseNo)
23
FROM
24
course c2,
25
teacher t2
26
WHERE
27
c2.teacherNo = t2.teacherNo
28
AND
29
t2.name = '叶平'
30
)
1
-- 如果学生学习叶平老师的课程数量，与叶平老师所教学课程的数量相同，那么说明该同学学了叶平老师的所有课程
2
SELECT
3
stu1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1,
8
course c1,
9
teacher t1
10
WHERE
11
s1.StudentNo = stu1.StudentNo
12
AND
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.StudentNo
19
HAVING COUNT(*) =
20
(
21
SELECT
22
COUNT(*)
23
FROM
24
course c2,
25
teacher t2
26
WHERE
27
c2.teacherNo = t2.teacherNo
28
AND
29
t2.name = '叶平'
30
)
（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
1
SELECT
2
stu1.studentNo,
3
stu1.name
4
FROM
5
score s1,
6
(
7
SELECT
8
s2.StudentNo,
9
s2.score
10
FROM
11
score s2
12
WHERE
13
s2.CourseNo = 1
14
) t2,
15
student stu1
16
WHERE
17
s1.CourseNo = 2
18
AND
19
s1.StudentNo = t2.StudentNo
20
AND
21
s1.score < t2.score
22
AND
23
s1.StudentNo = stu1.studentNo
（9）查询有课程成绩小于60分的同学的学号、姓名
1
SELECT DISTINCT
2
s1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.studentNo
9
AND
10
s1.score < 60
（10）查询没有学全所有课的同学的学号、姓名
1
SELECT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
GROUP BY s1.StudentNo
10
HAVING COUNT(*) <
11
(
12
SELECT
13
COUNT(*)
14
FROM
15
course c1
16
)
（11）查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
1
SELECT DISTINCT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.CourseNo IN
11
(
12
SELECT
13
s2.CourseNo
14
FROM
15
score s2
16
WHERE
17
s2.StudentNo = 1
18
)
（12）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名（和11题撞脸，排除1号同学就可以了）
1
SELECT DISTINCT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.StudentNo != 1
11
AND
12
s1.CourseNo IN
13
(
14
SELECT
15
s2.CourseNo
16
FROM
17
score s2
18
WHERE
19
s2.StudentNo = 1
20
)
（13）把“score”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
1
-- 涉及将两表联合，将本表某字段的值按条件设置为另个表的某个字段的值 （参考链接：MySQL:把一个表中的数据按键值更新(update)到另一个表）
2
UPDATE
3
score s,
4
(
5
SELECT
6
s1.CourseNo as courseNo,
7
AVG(s1.score) as avgScore
8
FROM
9
score s1,
10
course c1,
11
teacher t1
12
WHERE
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.CourseNo
19
) as t
20
SET
21
s.score = t.avgScore
22
WHERE
23
s.CourseNo = t.courseNo
（14）查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
1
SELECT
2
stu.studentNo,
3
stu.name
4
FROM
5
score s,
6
student stu
7
WHERE
8
s.StudentNo != 2
9
AND
10
s.StudentNo = stu.studentNo
11
GROUP BY s.StudentNo
12
HAVING SUM(s.CourseNo)=
13
(
14
SELECT
15
SUM(s1.CourseNo)
16
FROM
17
score s1
18
WHERE
19
s1.StudentNo = 2
20
)
（15）删除学习“叶平”老师课的SC表记录
1
DELETE FROM
2
score s
3
WHERE
4
s.CourseNo IN
5
(
6
SELECT
7
c.CourseNo
8
FROM
9
course c,
10
teacher t
11
WHERE
12
c.teacherNo = t.teacherNo
13
AND
14
t.name = '叶平'
15
)
（16）向SC表中插入一些记录，这些记录要求符合以下条件：1、没有上过编号“002”课程的同学学号；2、插入“002”号课程的平均成绩
1
-- 本题采用插入子查询的方式，三个字段中后两个字段为常量（基本格式：INSERT INTO R(A1, A2 ... ,An) 子查询）
2
INSERT INTO
3
score(StudentNo, CourseNo, score)
4
(
5
SELECT
6
stu.studentNo,
7
2,
8
(SELECT AVG(s3.score) FROM score s3 WHERE s3.CourseNo = 2)
9
FROM
10
student stu
11
WHERE
12
stu.studentNo
13
NOT IN
14
(
15
SELECT
16
s2.StudentNo
17
FROM
18
score s2
19
WHERE
20
s2.CourseNo = 2
21
)
22
)
（17）按学号由低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分
1
-- 用了个极蠢的办法，虽然很瓜很绕但是也算是温习了下相关子查询、CASE WHEN、EXISTS了，另外对自己也有所启发，就留下了
2
-- 然后做到这里的时候感慨，随着练习总是越来越熟练的，尽管自己写得很绕，但以往是根本想不到用什么CASE WHEN、EXISTS之类的，也算是成长吧
3
SELECT
4
stu.studentNo,
5
CASE WHEN EXISTS (SELECT * FROM score s1 WHERE s1.CourseNo = 1 AND s1.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 1 AND s.studentNo = stu.studentNo) ELSE NULL END AS "语文",
6
CASE WHEN EXISTS (SELECT * FROM score s2 WHERE s2.CourseNo = 2 AND s2.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 2 AND s.studentNo = stu.studentNo) ELSE NULL END AS "数学",
7
CASE WHEN EXISTS (SELECT * FROM score s3 WHERE s3.CourseNo = 3 AND s3.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 3 AND s.studentNo = stu.studentNo) ELSE NULL END AS "英语",
8
t1.validateCount AS '有效科目数',
9
t2.validateAVG AS '有效平均分'
10
FROM
11
student stu,
12
(SELECT s.studentNo, COUNT(*) AS validateCount FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t1,
13
(SELECT s.studentNo, AVG(s.score) AS validateAVG FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t2
14
WHERE
15
stu.studentNo = t1.studentNo
16
AND
17
stu.studentNo = t2.studentNo
18
ORDER BY stu.StudentNo
1
-- 另外，参考博客中博主给出的答案如下，比我的就简洁多了，
2
-- 其次，他在这里的有效课程数和有效平均分是针对学生所有的成绩，而并非此处的仅仅三科
3
-- 因为题意也不是很清楚，也就作罢，正好算是两种形式吧
4
SELECT
5
s.StudentNo,
6
(SELECT s1.score FROM score s1 WHERE s1.CourseNo=1 AND s1.StudentNo = s.StudentNo) AS "语文",
7
(SELECT s2.score FROM score s2 WHERE s2.CourseNo=2 AND s2.StudentNo = s.StudentNo) AS "数学",
8
(SELECT s3.score FROM score s3 WHERE s3.CourseNo=3 AND s3.StudentNo = s.StudentNo) AS "英语",
9
COUNT(s.CourseNo) AS "有效课程数",
10
AVG(s.score) AS "有效平均分"
11
FROM
12
score s
13
GROUP BY s.StudentNo
14
ORDER BY s.StudentNo
（18）查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
1
SELECT
2
s.CourseNo,
3
MAX(s.score),
4
MIN(s.score)
5
FROM
6
score s
7
GROUP BY
8
s.CourseNo
（19）按各科平均成绩从低到高和及格率的百分数从高到低顺序；
1
-- 看了下上次在Github上写的，不得不说，practice makes perfect
2
SELECT
3
s.CourseNo,
4
c.name,
5
AVG(s.score) AS '平均分',
6
SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END) AS '及格数',
7
COUNT(*) AS '总数',
8
SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '及格率'
9
FROM
10
score s,
11
course c
12
WHERE
13
s.CourseNo = c.courseNo
14
GROUP BY s.CourseNo
15
ORDER BY AVG(s.score), SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*) DESC
还不完全，参考原博主，加isnull
（20）查询不同老师所教不同课程平均分从高到低显示
1
SELECT
2
c1.name,
3
t1.name,
4
AVG(s1.score)
5
FROM
6
score s1,
7
course c1,
8
teacher t1
9
WHERE
10
s1.CourseNo = c1.courseNo
11
AND
12
c1.teacherNo = t1.teacherNo
13
GROUP BY s1.CourseNo
14
ORDER BY AVG(s1.score) DESC
（21）统计列印各科成绩，各分数段人数：课程ID，课程名称，(100-85)，(85-70，(70-60)，( 低于60)
1
SELECT
2
c.courseNo AS '课程ID',
3
c.name AS '课程名称',
4
SUM(CASE WHEN s.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '(100-85)',
5
SUM(CASE WHEN s.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '(85-70)',
6
SUM(CASE WHEN s.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '(70-60)',
7
SUM(CASE WHEN s.score < 60 THEN 1 ELSE 0 END) AS '(低于60)'
8
FROM
9
course c,
10
score s
11
WHERE
12
c.courseNo = s.CourseNo
13
GROUP BY c.courseNo
（22）查询各科成绩前三名的记录（不考虑成绩并列情况）
1
SELECT
2
*
3
FROM
4
score s
5
WHERE
6
(
7
SELECT
8
COUNT(*)
9
FROM
10
score s1
11
WHERE
12
s1.CourseNo = s.CourseNo
13
AND
14
s1.score > s.score
15
) < 3
16
ORDER BY s.CourseNo
（23）查询每门课程被选修的学生数
1
SELECT
2
c.name AS '课程',
3
COUNT(s.StudentNo) AS '选修学生数'
4
FROM
5
course c LEFT JOIN score s ON c.courseNo = s.CourseNo
6
GROUP BY c.courseNo
（24）查询出只选修了一门课程的全部学生的学号和姓名
1
SELECT
2
stu1.StudentNo AS '学号',
3
stu1.name AS '姓名'
4
FROM
5
(SELECT StudentNo, COUNT(CourseNo) AS amount FROM score GROUP BY StudentNo) t1,
6
student stu1
7
WHERE
8
t1.StudentNo = stu1.studentNo
9
AND
10
t1.amount = 1
1
SELECT
2
stu1.studentNo AS '学号',
3
stu1.name AS '姓名'
4
FROM
5
score s,
6
student stu1
7
WHERE
8
s.StudentNo = stu1.studentNo
9
GROUP BY s.StudentNo
10
HAVING COUNT(s.CourseNo) = 1
（25）查询男生、女生的人数
1
SELECT
2
s.sex AS '性别',
3
COUNT(*) AS '人数'
4
FROM
5
student s
6
GROUP BY s.sex
（26）查询同名同姓学生名单，并统计同名人数
1
SELECT
2
s.name AS '姓名',
3
COUNT(*) AS '学生数'
4
FROM
5
student s
6
GROUP BY s.name
（27）查询1991年出生的学生名单
1
SELECT
2
s.name AS '姓名'
3
FROM
4
student s
5
WHERE
6
YEAR(CURDATE()) - s.age = 1991
（28）查询每门课程的平均成绩，结果按平均成绩升序排列
1
#未考虑到课程无人选修的情况
2
SELECT
3
c.name AS '课程名称',
4
AVG(s.score) AS '平均成绩'
5
FROM
6
score s,
7
course c
8
WHERE
9
s.CourseNo = c.courseNo
10
GROUP BY c.courseNo
11
ORDER BY AVG(s.score)
12
13
14
15
#如果某课程无人选修，其平均成绩显示为null
16
SELECT
17
c.name AS '课程名称',
18
AVG(s.score) AS '平均成绩'
19
FROM
20
course c LEFT JOIN score s ON c.courseNo = s.CourseNo
21
GROUP BY c.courseNo
22
ORDER BY AVG(s.score)
（29）查询平均成绩大于85的所有学生的学号、姓名和平均成绩
1
SELECT
2
stu.studentNo AS '学号',
3
stu.name AS '姓名',
4
AVG(s.score) AS '平均成绩'
5
FROM
6
score s,
7
student stu
8
WHERE
9
s.StudentNo = stu.studentNo
10
GROUP BY s.StudentNo
11
HAVING AVG(s.score) > 85
（30）查询课程名称为“数学”，且分数低于60的学生姓名和分数
1
SELECT
2
stu.name AS '姓名',
3
s.score AS '数学成绩'
4
FROM
5
score s,
6
course c,
7
student stu
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.StudentNo = stu.studentNo
12
AND
13
c.name = '数学'
14
AND
15
s.score < 60
（31）查询所有学生的选课情况
1
SELECT
2
stu.name AS '姓名',
3
c.name AS '选课'
4
FROM
5
score s,
6
course c,
7
student stu
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.StudentNo = stu.studentNo
12
ORDER BY stu.name
（32）查询任何一门课程成绩在70分以上的姓名、课程名称和分数
1
SELECT
2
stu.name AS '姓名',
3
c.name AS '课程名称',
4
s.score AS '分数'
5
FROM
6
score s,
7
student stu,
8
course c
9
WHERE
10
s.StudentNo = stu.studentNo
11
AND
12
s.CourseNo = c.courseNo
13
AND
14
s.score > 70
（33）查询不及格的课程，并按课程号从大到小排列
1
#包含不及格记录的课程
2
SELECT DISTINCT
3
c.courseNo AS '课程号',
4
c.name AS '课程名称'
5
FROM
6
score s,
7
course c
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.score < 60
12
13
#不及格的课程的选修记录
14
SELECT
15
stu.name AS '姓名',
16
c.name AS '课程名称',
17
s.score AS '分数'
18
FROM
19
score s,
20
student stu,
21
course c
22
WHERE
23
s.StudentNo = stu.studentNo
24
AND
25
s.CourseNo = c.courseNo
26
AND
27
s.score < 60
28
ORDER BY c.courseNo DESC
（34）查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
1
SELECT
2
stu.studentNo AS '学号',
3
stu.name AS '姓名'
4
FROM
5
score s,
6
student stu
7
WHERE
8
s.StudentNo = stu.studentNo
9
AND
10
s.CourseNo = 3
11
AND
12
s.score > 80
（35）求选了课程的学生人数
1
#method-1
2
SELECT
3
COUNT(DISTINCT s.StudentNo) AS '选了课程的学生人数'
4
FROM
5
score s
6
7
#method-2
8
SELECT
9
COUNT(*) AS '选了课程的学生人数'
10
FROM
11
(SELECT * FROM score s GROUP BY s.StudentNo) t
（36）查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩
1
SELECT
2
stu.name AS '学生姓名',
3
s.score AS '成绩'
4
FROM
5
score s,
6
student stu,
7
course c,
8
teacher t
9
WHERE
10
s.StudentNo = stu.studentNo
11
AND
12
s.CourseNo = c.courseNo
13
AND
14
c.teacherNo = t.teacherNo
15
AND
16
t.name = '杨艳'
17
ORDER BY s.score DESC
18
LIMIT 0, 1
（37）查询各个课程及相应的选修人数
1
SELECT
2
c.name AS '课程名称',
3
COUNT(*) AS '选修人数'
4
FROM
5
score s,
6
course c
7
WHERE
8
s.CourseNo = c.courseNo
9
GROUP BY s.CourseNo
（38）查询不同课程但成绩相同的学生的学号、课程号、学生成绩
1
#method-1
2
SELECT
3
s.StudentNo AS '学号',
4
s.CourseNo AS '课程号',
5
s.score AS '成绩'
6
FROM
7
score s
8
WHERE
9
(SELECT COUNT(*) FROM score s1 WHERE s1.score = s.score AND s1.CourseNo <> s.COurseNo) > 0
10
ORDER BY s.score DESC, s.StudentNo, s.CourseNo
11
12
#method-2
13
SELECT DISTINCT
14
s1.StudentNo AS '学号',
15
s1.CourseNo AS '课程号',
16
s1.score AS '成绩'
17
FROM
18
score s1,
19
score s2
20
WHERE
21
s1.score = s2.score
22
AND
23
s1.CourseNo <> s2.CourseNo
24
ORDER BY s1.score DESC, s1.StudentNo, s1.CourseNo
（39）查询每门课程成绩最好的前两名
1
SELECT
2
s.CourseNo AS '课程号',
3
s.StudentNo AS '学号',
4
s.score AS '分数'
5
练习题和参考解
（1）查询“001”课程比“002”课程成绩低的所有学生的学号、001学科成绩、002学科成绩
1
SELECT
2
s1.StudentNo,
3
s1.score AS '001',
4
s2.score AS '002'
5
FROM
6
score s1,
7
(
8
SELECT
9
*
10
FROM
11
score s
12
WHERE
13
s.CourseNo = 2
14
) s2
15
WHERE
16
s1.CourseNo = 1
17
AND
18
s1.StudentNo = s2.StudentNo
19
AND
20
s1.score < s2.score
21
ORDER BY s1.StudentNo
1
SELECT
2
s1.StudentNo,
3
s1.score AS '001',
4
s2.score AS '002'
5
FROM
6
score s1,
7
score s2
8
WHERE
9
s1.CourseNo = 001
10
AND
11
s2.CourseNo = 002
12
AND
13
s1.StudentNo = s2.StudentNo
14
AND
15
s1.score < s2.score
16
ORDER BY s1.StudentNo
（2）查询平均成绩大于60分的同学的学号和平均成绩
1
SELECT
2
s1.StudentNo,
3
AVG(s1.score)
4
FROM
5
score s1
6
GROUP BY s1.StudentNo
7
HAVING AVG(s1.score)>60
（3）查询所有同学的学号、姓名、选课数、总成绩
1
SELECT
2
s1.StudentNo,
3
stu1.name,
4
COUNT(*),
5
SUM(s1.score)
6
FROM
7
score s1,
8
student stu1
9
WHERE
10
s1.StudentNo = stu1.StudentNo
11
GROUP BY s1.StudentNo
（4）查询姓“李”的老师的个数
1
SELECT
2
COUNT(*)
3
FROM
4
teacher t1
5
WHERE
6
t1.name like '李%'
（5）查询没学过“叶平”老师课的同学的学号、姓名
1
SELECT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
student stu1
6
WHERE
7
stu1.StudentNo NOT IN
8
(
9
SELECT DISTINCT
10
s1.StudentNo
11
FROM
12
score s1,
13
course c1,
14
teacher t1
15
WHERE
16
s1.courseNo = c1.CourseNo
17
AND
18
c1.teacherNo = t1.teacherNo
19
AND
20
t1.name = '叶平'
21
)
（6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
1
-- 这个算法比普通的要有想法
2
SELECT
3
s1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1
8
WHERE
9
s1.StudentNo = stu1.StudentNo
10
AND
11
s1.CourseNo IN (1, 2)
12
GROUP BY s1.StudentNo
13
HAVING COUNT(*) = 2
1
SELECT
2
s1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.CourseNo = 1
11
AND
12
s1.StudentNo IN
13
(
14
SELECT
15
s2.StudentNo
16
FROM
17
score s2
18
WHERE
19
s2.CourseNo = 2
20
)
（7）查询学过“叶平”老师所教的所有课的同学的学号、姓名
1
-- 利用主键值不相同，它们的和一定各不相同，叶平老师的课程的主键值和如果与学生所学的叶平老师的课程的主键值和相等，那么说明学了叶平老师所有课程
2
SELECT
3
stu1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1,
8
course c1,
9
teacher t1
10
WHERE
11
s1.StudentNo = stu1.StudentNo
12
AND
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.StudentNo
19
HAVING SUM(s1.CourseNo)=
20
(
21
SELECT
22
SUM(c2.CourseNo)
23
FROM
24
course c2,
25
teacher t2
26
WHERE
27
c2.teacherNo = t2.teacherNo
28
AND
29
t2.name = '叶平'
30
)
1
-- 如果学生学习叶平老师的课程数量，与叶平老师所教学课程的数量相同，那么说明该同学学了叶平老师的所有课程
2
SELECT
3
stu1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1,
8
course c1,
9
teacher t1
10
WHERE
11
s1.StudentNo = stu1.StudentNo
12
AND
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.StudentNo
19
HAVING COUNT(*) =
20
(
21
SELECT
22
COUNT(*)
23
FROM
24
course c2,
25
teacher t2
26
WHERE
27
c2.teacherNo = t2.teacherNo
28
AND
29
t2.name = '叶平'
30
)
（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
1
SELECT
2
stu1.studentNo,
3
stu1.name
4
FROM
5
score s1,
6
(
7
SELECT
8
s2.StudentNo,
9
s2.score
10
FROM
11
score s2
12
WHERE
13
s2.CourseNo = 1
14
) t2,
15
student stu1
16
WHERE
17
s1.CourseNo = 2
18
AND
19
s1.StudentNo = t2.StudentNo
20
AND
21
s1.score < t2.score
22
AND
23
s1.StudentNo = stu1.studentNo
（9）查询有课程成绩小于60分的同学的学号、姓名
1
SELECT DISTINCT
2
s1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.studentNo
9
AND
10
s1.score < 60
（10）查询没有学全所有课的同学的学号、姓名
1
SELECT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
GROUP BY s1.StudentNo
10
HAVING COUNT(*) <
11
(
12
SELECT
13
COUNT(*)
14
FROM
15
course c1
16
)
（11）查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
1
SELECT DISTINCT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.CourseNo IN
11
(
12
SELECT
13
s2.CourseNo
14
FROM
15
score s2
16
WHERE
17
s2.StudentNo = 1
18
)
（12）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名（和11题撞脸，排除1号同学就可以了）
1
SELECT DISTINCT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.StudentNo != 1
11
AND
12
s1.CourseNo IN
13
(
14
SELECT
15
s2.CourseNo
16
FROM
17
score s2
18
WHERE
19
s2.StudentNo = 1
20
)
（13）把“score”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
1
-- 涉及将两表联合，将本表某字段的值按条件设置为另个表的某个字段的值 （参考链接：MySQL:把一个表中的数据按键值更新(update)到另一个表）
2
UPDATE
3
score s,
4
(
5
SELECT
6
s1.CourseNo as courseNo,
7
AVG(s1.score) as avgScore
8
FROM
9
score s1,
10
course c1,
11
teacher t1
12
WHERE
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.CourseNo
19
) as t
20
SET
21
s.score = t.avgScore
22
WHERE
23
s.CourseNo = t.courseNo
（14）查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
1
SELECT
2
stu.studentNo,
3
stu.name
4
FROM
5
score s,
6
student stu
7
WHERE
8
s.StudentNo != 2
9
AND
10
s.StudentNo = stu.studentNo
11
GROUP BY s.StudentNo
12
HAVING SUM(s.CourseNo)=
13
(
14
SELECT
15
SUM(s1.CourseNo)
16
FROM
17
score s1
18
WHERE
19
s1.StudentNo = 2
20
)
（15）删除学习“叶平”老师课的SC表记录
1
DELETE FROM
2
score s
3
WHERE
4
s.CourseNo IN
5
(
6
SELECT
7
c.CourseNo
8
FROM
9
course c,
10
teacher t
11
WHERE
12
c.teacherNo = t.teacherNo
13
AND
14
t.name = '叶平'
15
)
（16）向SC表中插入一些记录，这些记录要求符合以下条件：1、没有上过编号“002”课程的同学学号；2、插入“002”号课程的平均成绩
1
-- 本题采用插入子查询的方式，三个字段中后两个字段为常量（基本格式：INSERT INTO R(A1, A2 ... ,An) 子查询）
2
INSERT INTO
3
score(StudentNo, CourseNo, score)
4
(
5
SELECT
6
stu.studentNo,
7
2,
8
(SELECT AVG(s3.score) FROM score s3 WHERE s3.CourseNo = 2)
9
FROM
10
student stu
11
WHERE
12
stu.studentNo
13
NOT IN
14
(
15
SELECT
16
s2.StudentNo
17
FROM
18
score s2
19
WHERE
20
s2.CourseNo = 2
21
)
22
)
（17）按学号由低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分
1
-- 用了个极蠢的办法，虽然很瓜很绕但是也算是温习了下相关子查询、CASE WHEN、EXISTS了，另外对自己也有所启发，就留下了
2
-- 然后做到这里的时候感慨，随着练习总是越来越熟练的，尽管自己写得很绕，但以往是根本想不到用什么CASE WHEN、EXISTS之类的，也算是成长吧
3
SELECT
4
stu.studentNo,
5
CASE WHEN EXISTS (SELECT * FROM score s1 WHERE s1.CourseNo = 1 AND s1.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 1 AND s.studentNo = stu.studentNo) ELSE NULL END AS "语文",
6
CASE WHEN EXISTS (SELECT * FROM score s2 WHERE s2.CourseNo = 2 AND s2.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 2 AND s.studentNo = stu.studentNo) ELSE NULL END AS "数学",
7
CASE WHEN EXISTS (SELECT * FROM score s3 WHERE s3.CourseNo = 3 AND s3.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 3 AND s.studentNo = stu.studentNo) ELSE NULL END AS "英语",
8
t1.validateCount AS '有效科目数',
9
t2.validateAVG AS '有效平均分'
10
FROM
11
student stu,
12
(SELECT s.studentNo, COUNT(*) AS validateCount FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t1,
13
(SELECT s.studentNo, AVG(s.score) AS validateAVG FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t2
14
WHERE
15
stu.studentNo = t1.studentNo
16
AND
17
stu.studentNo = t2.studentNo
18
ORDER BY stu.StudentNo
1
-- 另外，参考博客中博主给出的答案如下，比我的就简洁多了，
2
-- 其次，他在这里的有效课程数和有效平均分是针对学生所有的成绩，而并非此处的仅仅三科
3
-- 因为题意也不是很清楚，也就作罢，正好算是两种形式吧
4
SELECT
5
s.StudentNo,
6
(SELECT s1.score FROM score s1 WHERE s1.CourseNo=1 AND s1.StudentNo = s.StudentNo) AS "语文",
7
(SELECT s2.score FROM score s2 WHERE s2.CourseNo=2 AND s2.StudentNo = s.StudentNo) AS "数学",
8
(SELECT s3.score FROM score s3 WHERE s3.CourseNo=3 AND s3.StudentNo = s.StudentNo) AS "英语",
9
COUNT(s.CourseNo) AS "有效课程数",
10
AVG(s.score) AS "有效平均分"
11
FROM
12
score s
13
GROUP BY s.StudentNo
14
ORDER BY s.StudentNo
（18）查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
1
SELECT
2
s.CourseNo,
3
MAX(s.score),
4
MIN(s.score)
5
FROM
6
score s
7
GROUP BY
8
s.CourseNo
（19）按各科平均成绩从低到高和及格率的百分数从高到低顺序；
1
-- 看了下上次在Github上写的，不得不说，practice makes perfect
2
SELECT
3
s.CourseNo,
4
c.name,
5
AVG(s.score) AS '平均分',
6
SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END) AS '及格数',
7
COUNT(*) AS '总数',
8
SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '及格率'
9
FROM
10
score s,
11
course c
12
WHERE
13
s.CourseNo = c.courseNo
14
GROUP BY s.CourseNo
15
ORDER BY AVG(s.score), SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*) DESC
还不完全，参考原博主，加isnull
（20）查询不同老师所教不同课程平均分从高到低显示
1
SELECT
2
c1.name,
3
t1.name,
4
AVG(s1.score)
5
FROM
6
score s1,
7
course c1,
8
teacher t1
9
WHERE
10
s1.CourseNo = c1.courseNo
11
AND
12
c1.teacherNo = t1.teacherNo
13
GROUP BY s1.CourseNo
14
ORDER BY AVG(s1.score) DESC
（21）统计列印各科成绩，各分数段人数：课程ID，课程名称，(100-85)，(85-70，(70-60)，( 低于60)
1
SELECT
2
c.courseNo AS '课程ID',
3
c.name AS '课程名称',
4
SUM(CASE WHEN s.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '(100-85)',
5
SUM(CASE WHEN s.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '(85-70)',
6
SUM(CASE WHEN s.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '(70-60)',
7
SUM(CASE WHEN s.score < 60 THEN 1 ELSE 0 END) AS '(低于60)'
8
FROM
9
course c,
10
score s
11
WHERE
12
c.courseNo = s.CourseNo
13
GROUP BY c.courseNo
（22）查询各科成绩前三名的记录（不考虑成绩并列情况）
1
SELECT
2
*
3
FROM
4
score s
5
WHERE
6
(
7
SELECT
8
COUNT(*)
9
FROM
10
score s1
11
WHERE
12
s1.CourseNo = s.CourseNo
13
AND
14
s1.score > s.score
15
) < 3
16
ORDER BY s.CourseNo
（23）查询每门课程被选修的学生数
1
SELECT
2
c.name AS '课程',
3
COUNT(s.StudentNo) AS '选修学生数'
4
FROM
5
course c LEFT JOIN score s ON c.courseNo = s.CourseNo
6
GROUP BY c.courseNo
（24）查询出只选修了一门课程的全部学生的学号和姓名
1
SELECT
2
stu1.StudentNo AS '学号',
3
stu1.name AS '姓名'
4
FROM
5
(SELECT StudentNo, COUNT(CourseNo) AS amount FROM score GROUP BY StudentNo) t1,
6
student stu1
7
WHERE
8
t1.StudentNo = stu1.studentNo
9
AND
10
t1.amount = 1
1
SELECT
2
stu1.studentNo AS '学号',
3
stu1.name AS '姓名'
4
FROM
5
score s,
6
student stu1
7
WHERE
8
s.StudentNo = stu1.studentNo
9
GROUP BY s.StudentNo
10
HAVING COUNT(s.CourseNo) = 1
（25）查询男生、女生的人数
1
SELECT
2
s.sex AS '性别',
3
COUNT(*) AS '人数'
4
FROM
5
student s
6
GROUP BY s.sex
（26）查询同名同姓学生名单，并统计同名人数
1
SELECT
2
s.name AS '姓名',
3
COUNT(*) AS '学生数'
4
FROM
5
student s
6
GROUP BY s.name
（27）查询1991年出生的学生名单
1
SELECT
2
s.name AS '姓名'
3
FROM
4
student s
5
WHERE
6
YEAR(CURDATE()) - s.age = 1991
（28）查询每门课程的平均成绩，结果按平均成绩升序排列
1
#未考虑到课程无人选修的情况
2
SELECT
3
c.name AS '课程名称',
4
AVG(s.score) AS '平均成绩'
5
FROM
6
score s,
7
course c
8
WHERE
9
s.CourseNo = c.courseNo
10
GROUP BY c.courseNo
11
ORDER BY AVG(s.score)
12
13
14
15
#如果某课程无人选修，其平均成绩显示为null
16
SELECT
17
c.name AS '课程名称',
18
AVG(s.score) AS '平均成绩'
19
FROM
20
course c LEFT JOIN score s ON c.courseNo = s.CourseNo
21
GROUP BY c.courseNo
22
ORDER BY AVG(s.score)
（29）查询平均成绩大于85的所有学生的学号、姓名和平均成绩
1
SELECT
2
stu.studentNo AS '学号',
3
stu.name AS '姓名',
4
AVG(s.score) AS '平均成绩'
5
FROM
6
score s,
7
student stu
8
WHERE
9
s.StudentNo = stu.studentNo
10
GROUP BY s.StudentNo
11
HAVING AVG(s.score) > 85
（30）查询课程名称为“数学”，且分数低于60的学生姓名和分数
1
SELECT
2
stu.name AS '姓名',
3
s.score AS '数学成绩'
4
FROM
5
score s,
6
course c,
7
student stu
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.StudentNo = stu.studentNo
12
AND
13
c.name = '数学'
14
AND
15
s.score < 60
（31）查询所有学生的选课情况
1
SELECT
2
stu.name AS '姓名',
3
c.name AS '选课'
4
FROM
5
score s,
6
course c,
7
student stu
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.StudentNo = stu.studentNo
12
ORDER BY stu.name
（32）查询任何一门课程成绩在70分以上的姓名、课程名称和分数
1
SELECT
2
stu.name AS '姓名',
3
c.name AS '课程名称',
4
s.score AS '分数'
5
FROM
6
score s,
7
student stu,
8
course c
9
WHERE
10
s.StudentNo = stu.studentNo
11
AND
12
s.CourseNo = c.courseNo
13
AND
14
s.score > 70
（33）查询不及格的课程，并按课程号从大到小排列
1
#包含不及格记录的课程
2
SELECT DISTINCT
3
c.courseNo AS '课程号',
4
c.name AS '课程名称'
5
FROM
6
score s,
7
course c
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.score < 60
12
13
#不及格的课程的选修记录
14
SELECT
15
stu.name AS '姓名',
16
c.name AS '课程名称',
17
s.score AS '分数'
18
FROM
19
score s,
20
student stu,
21
course c
22
WHERE
23
s.StudentNo = stu.studentNo
24
AND
25
s.CourseNo = c.courseNo
26
AND
27
s.score < 60
28
ORDER BY c.courseNo DESC
（34）查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
1
SELECT
2
stu.studentNo AS '学号',
3
stu.name AS '姓名'
4
FROM
5
score s,
6
student stu
7
WHERE
8
s.StudentNo = stu.studentNo
9
AND
10
s.CourseNo = 3
11
AND
12
s.score > 80
（35）求选了课程的学生人数
1
#method-1
2
SELECT
3
COUNT(DISTINCT s.StudentNo) AS '选了课程的学生人数'
4
FROM
5
score s
6
7
#method-2
8
SELECT
9
COUNT(*) AS '选了课程的学生人数'
10
FROM
11
(SELECT * FROM score s GROUP BY s.StudentNo) t
（36）查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩
1
SELECT
2
stu.name AS '学生姓名',
3
s.score AS '成绩'
4
FROM
5
score s,
6
student stu,
7
course c,
8
teacher t
9
WHERE
10
s.StudentNo = stu.studentNo
11
AND
12
s.CourseNo = c.courseNo
13
AND
14
c.teacherNo = t.teacherNo
15
AND
16
t.name = '杨艳'
17
ORDER BY s.score DESC
18
LIMIT 0, 1
（37）查询各个课程及相应的选修人数
1
SELECT
2
c.name AS '课程名称',
3
COUNT(*) AS '选修人数'
4
FROM
5
score s,
6
course c
7
WHERE
8
s.CourseNo = c.courseNo
9
GROUP BY s.CourseNo
（38）查询不同课程但成绩相同的学生的学号、课程号、学生成绩
1
#method-1
2
SELECT
3
s.StudentNo AS '学号',
4
s.CourseNo AS '课程号',
5
s.score AS '成绩'
6
FROM
7
score s
8
WHERE
9
(SELECT COUNT(*) FROM score s1 WHERE s1.score = s.score AND s1.CourseNo <> s.COurseNo) > 0
10
ORDER BY s.score DESC, s.StudentNo, s.CourseNo
11
12
#method-2
13
SELECT DISTINCT
14
s1.StudentNo AS '学号',
15
s1.CourseNo AS '课程号',
16
s1.score AS '成绩'
17
FROM
18
score s1,
19
score s2
20
WHERE
21
s1.score = s2.score
22
AND
23
s1.CourseNo <> s2.CourseNo
24
ORDER BY s1.score DESC, s1.StudentNo, s1.CourseNo
（39）查询每门课程成绩最好的前两名
1
SELECT
2
s.CourseNo AS '课程号',
3
s.StudentNo AS '学号',
4
s.score AS '分数'
5
FROM
6
score s
7
WHERE
8
(SELECT COUNT(*) FROM score s1 WHERE s1.CourseNo = s.CourseNo AND s1.score > s.score) < 2
9
ORDER BY s.CourseNo
FROM
6
score s
7
WHERE
8
(SELECT COUNT(*) FROM score s1 WHERE s1.CourseNo = s.CourseNo AND s1.score > s.score) < 2
9
ORDER BY s.CourseNo
扫一扫
1896
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
