本文提供了一系列关于MySQL多表查询的练习题目，涵盖了基础的JOIN操作到复杂的子查询，如：查询所有课程及教师，学生性别分布，物理满分学生，平均分高于80的同学，课程选修情况，姓李老师的数量，未选修特定老师课程的学生，成绩对比，课程选修次数，平均成绩高于85的学生，生物不及格学生，李平老师课程的最高平均分学生，最佳成绩课程，相同成绩不同课程，未学过李平课程的学生，选修指定学生课程的学生，任课最多的老师对应学生最高分课程等。通过这些题目，读者可以深入理解和掌握MySQL的多表查询技巧。
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id=teacher.tid;2、查询学生表中男女生各有多少人
SELECT
gender,
count(sid)
FROM
student
GROUP BY
gender;3、查询物理成绩等于100的学生的姓名
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
score.student_id
FROM
score
LEFT JOIN course ON score.course_id=course.cid
WHERE
score.num= 100AND course.cname= "物理");4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
t1.sname,
t2.avg_num
FROM
student t1
INNER JOIN (
SELECT
student_id,
avg(num) avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num)> 80) t2 ON t1.sid=t2.student_id;5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
SELECT
sid,
sname,
t1.count_course,
t1.sum_num
FROM
student
LEFT JOIN (
SELECT
student_id,
count(course_id) count_course,
sum(num) sum_num
FROM
score
GROUP BY
student_id
) t1 ON student.sid=t1.student_id;6、 查询姓李老师的个数
SELECT
count(tid)
FROM
teacher
WHERE
tname LIKE'李%';7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)
SELECT
sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id=(
SELECT
tid
FROM
teacher
WHERE
tname= '李平')
)
);8、 查询物理课程的分数比生物课程的分数高的学生的学号
SELECT
t1.student_id
FROM
(
SELECT
student_id,
num
FROM
score
LEFT JOIN course ON score.course_id=course.cid
WHERE
cname= '物理') t1
LEFT JOIN (
SELECT
student_id,
num
FROM
score
LEFT JOIN course ON score.course_id=course.cid
WHERE
cname= '生物') t2 ON t1.student_id=t2.student_id
WHERE
t1.num>t2.num;9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ('物理', '体育')
)
GROUP BY
student_id
HAVING
count(student_id)= 1);10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表，然后对学生进行分组，统计课程数目>=2)#方式一：
SELECT
t1.sname,
t2.caption
FROM
(
SELECT
class_id,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
num< 60GROUP BY
student_id
HAVING
count(course_id)> 1)
) t1
INNER JOIN (SELECT cid, caption FROMclass) t2 ON t1.class_id =t2.cid;#方式二：
SELECT
student.sname,class.caption
FROM
student
INNER JOIN (
SELECT
student_id
FROM
score
WHERE
num< 60GROUP BY
student_id
HAVING
count(course_id)>= 2) AS t1
INNER JOINclass ON student.sid =t1.student_id
AND student.class_id= class.cid;11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
count(course_id)=(
SELECT
count(cid) AS course_total
FROM
course
)
);12、查询李平老师教的课程的所有成绩记录#方式一：
SELECT*FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id=(
SELECT
tid
FROM
teacher
WHERE
tname= '李平')
);#方式二：
SELECT*FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id=teacher.tid
WHERE
teacher.tname= '李平');13、查询全部学生都选修了的课程号和课程名(先统计出有课程的学生总数，再以课程分组统计出有多少学生学，如果有课程跟学生总数相等，就说明这就是都选修的课程)
SELECT
cid,
cname
FROM
course
WHERE
cid IN (
SELECT
course_id
FROM
score
GROUP BY
course_id
HAVING
count(student_id)=(
SELECT
count(DISTINCT student_id)
FROM
score
)
)14、查询每门课程被选修的次数
SELECT
course_id,
count(student_id)
FROM
score
GROUP BY
course_id;15、查询只选修了一门课程的学生学号和姓名
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
count(course_id)= 1);16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
SELECT DISTINCT
num
FROM
score
ORDER BY
num DESC;17、查询平均成绩大于85的学生姓名和平均成绩
SELECT
sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num)> 85) t1 ON student.sid=t1.student_id;18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
sname,
t1.num
FROM
student
INNER JOIN (
SELECT
student_id,
num
FROM
score
WHERE
course_id=(
SELECT
cid
FROM
course
WHERE
cname= '生物')
HAVING
num< 60) t1 ON student.sid=t1.student_id;19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT
sname
FROM
student
WHERE
sid=(
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
teacher
INNER JOIN course ON teacher.tid=course.teacher_id
WHERE
teacher.tname= '李平')
GROUP BY
student_id
ORDER BY
avg(num) DESC
LIMIT1);20、查询每门课程成绩最好的课程id、学生姓名和分数
SELECT
course_id,
sname,
num
FROM
student
INNER JOIN (
SELECT
t1.course_id,
student_id,
num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) AS max_num
FROM
score
GROUP BY
course_id
) t1 ON score.course_id=t1.course_id
WHERE
score.num=t1.max_num
) t2 ON student.sid=t2.student_id;21、查询不同课程但成绩相同的课程号、学生号、成绩
SELECT DISTINCT
s1.course_id,
s2.course_id,
s1.student_id,
s2.student_id,
s1.num,
s2.num
FROM
score AS s1,
score AS s2
WHERE
s1.num=s2.num
AND s1.course_id>s2.course_id
order by s1.course_id;22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称
SELECT
student.sname,
course.cname
FROM
student
INNER JOIN (
SELECT
student_id,
course_id
FROM
score
WHERE
student_id IN (
SELECT
sid
FROM
student
WHERE
sid NOT IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id=teacher.tid
WHERE
tname= '李平')
)
)
) t1 ON student.sid=t1.student_id
INNER JOIN course ON course.cid=t1.course_id;23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course_id
FROM
score
WHERE
student_id= 2)
)
AND sid!= 2;24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数
SELECT
t1.course_id,
student.sname,
num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) AS max_num
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id=(
SELECT
teacher_id
FROM
course
GROUP BY
teacher_id
ORDER BY
count(teacher_id) DESC
LIMIT1)
)
GROUP BY
course_id
) t1 ON score.course_id=t1.course_id
INNER JOIN student ON score.student_id=student.sid
WHERE
score.course_id=t1.course_id
AND num=t1.max_num;
参考答案
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
