根据上一篇文章建立的表，我们来做一些多表练习：
没建立表的可以
点击此链接
去建立练习用的表：
目录
12.查询被”Tom”和”Jerry”教的课程的最高分和最低分
15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
28.查询课程名称为”java”，且分数低于60分的学生姓名和分数
1.查询“1”号学生的姓名和各科成绩：
进行student表和scores表的id相连接，course表和scores表的id相连接
SELECT
s.id sid,
s.`name` sname,
c.`name` cname,
sc.score
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
s.id = 1;
2.查询各个学科的平均成绩和最高成绩：
SELECT
c.id,
c.`name`,
AVG( sc.score ),
max( sc.score )
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
c.id,
c.`name`;
3.查询所有姓张的同学的各科成绩：
SELECT
s.id,
s.`name`,
c.`name` cname,
sc.score
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
s.`name` LIKE '张%';
4.查询每个同学的最高成绩和科目名称
SELECT
t.id,
t.NAME,
c.id,
c.NAME,
r.score
FROM
(
SELECT
s.id,
s.NAME,(
SELECT
max( score )
FROM
scores r
WHERE
r.s_id = s.id
) score
FROM
student s
) t
LEFT JOIN scores r ON r.s_id = t.id
AND r.score = t.score
LEFT JOIN course c ON r.c_id = c.id;
5.查询每个课程的最高分的学生信息
SELECT
*
FROM
student s
WHERE
id IN (
SELECT DISTINCT
r.s_id
FROM
(
SELECT
c.id,
c.NAME,
max( score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
GROUP BY
c.id,
c.NAME
) t
LEFT JOIN scores r ON r.c_id = t.id
AND t.score = r.score
);
6.查询名字中含有’张’或’李’字的学生的信息和各科成绩。
SELECT
s.id,
s.NAME sname,
sc.score,
c.NAME
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON sc.c_id = c.id
WHERE
s.NAME LIKE '%张%'
OR s.NAME LIKE '%李%';
7.查询平均成绩大于70的同学的信息。（子查询）
SELECT
*
FROM
student
WHERE
id IN (
SELECT
sc.s_id
FROM
scores sc
GROUP BY
sc.s_id
HAVING
avg( sc.score ) >= 70
);
8.将学生按照总分数进行排名。（从高到低）
SELECT
s.id,
s.NAME,
sum( sc.score ) score
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
GROUP BY
s.id,
s.NAME
ORDER BY
score DESC,
s.id ASC;
9.查询数学成绩的最高分、最低分、平均分。
SELECT
c.NAME,
max( sc.score ),
min( sc.score ),
avg( sc.score )
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
WHERE
c.NAME = '数学';
10.将各科目按照平均分排序。
SELECT
c.id,
c.NAME,
avg( sc.score ) score
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
c.id,
c.NAME
ORDER BY
score DESC;
11.查询老师的信息和他所带的科目的平均分
SELECT
t.id,
t.NAME,
c.id cid,
c.NAME cname,
avg( r.score )
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores r ON r.c_id = c.id
GROUP BY
t.id,
t.NAME,
c.id,
c.NAME;
12.查询被”Tom”和”Jerry”教的课程的最高分和最低分
SELECT
t.id,
t.NAME,
c.id cid,
c.NAME cname,
max( r.score ),
min( r.score )
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores r ON r.c_id = c.id
GROUP BY
t.id,
t.NAME,
c.id,
c.NAME
HAVING
t.NAME IN ( 'Tom', 'Jerry' );
13.查询每个学生的最好成绩的科目名称（子查询）
SELECT
t.id,
t.sname,
r.c_id,
c.NAME,
t.score
FROM
(
SELECT
s.id,
s.NAME sname,
max( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
GROUP BY
s.id,
s.NAME
) t
LEFT JOIN scores r ON r.s_id = t.id
AND r.score = t.score
LEFT JOIN course c ON r.c_id = c.id;
14.查询所有学生的课程及分数
SELECT
s.id,
s.NAME,
c.id,
c.NAME,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON c.id = r.c_id;
15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT
s.*,
r.*
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
WHERE
r.c_id = 1
AND r.score > 60
16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
SELECT
s.id,
s.NAME,
t.score
FROM
student s
LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id
WHERE
t.score >= 70;
17.查询有不及格课程的学生信息
SELECT
*
FROM
student s
WHERE
id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );
18.查询每门课程有成绩的学生人数
SELECT
c.id,
c.NAME,
count(*)
FROM
course c
LEFT JOIN scores r ON c.id = r.c_id
GROUP BY
c.id,
c.NAME;
19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT
c.id,
c.NAME,
avg( score ) score
FROM
course c
LEFT JOIN scores r ON c.id = r.c_id
GROUP BY
c.id,
c.NAME
ORDER BY
score DESC,
c.id ASC;
20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s.id,
s.NAME sname,
avg( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
GROUP BY
s.id,
s.NAME
HAVING
score > 65;
21.查询有且仅有一门课程成绩在80分以上的学生信息
SELECT
s.id,
s.NAME,
s.gender
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
WHERE
r.score > 80
GROUP BY
s.id,
s.NAME,
s.gender
HAVING
count(*) = 1;
22.查询出只有三门课程的学生的学号和姓名
SELECT
s.id,
s.NAME,
s.gender
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME,
s.gender
HAVING
count(*) = 3;
23.查询有不及格课程的课程信息
SELECT
*
FROM
course c
WHERE
id IN (
SELECT
r.c_id
FROM
scores r
GROUP BY
r.c_id
HAVING
min( r.score ) < 60
);
24.查询至少选择4门课程的学生信息
SELECT
s.id,
s.NAME
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
HAVING
count(*) >= 4;
25.查询没有选全所有课程的同学的信息
SELECT
*
FROM
student
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
count(*) != 5
);
26.查询选全所有课程的同学的信息
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
HAVING
number = ( SELECT count(*) FROM course );
27.查询各学生都选了多少门课
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME;
28.查询课程名称为”java”，且分数低于60分的学生姓名和分数
SELECT
s.id,
s.NAME,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON r.c_id = c.id
WHERE
c.NAME = 'java'
AND r.score < 60;
29.查询学过”Tony”老师授课的同学的信息
SELECT
s.id,
s.NAME
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.NAME = 'Tom';
30.查询没学过”Tony”老师授课的学生信息
SELECT
*
FROM
student
WHERE
id NOT IN (
SELECT DISTINCT
s.id
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.NAME = 'Tom'
)
版权声明：本文为weixin_49627122原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。