-- 查询所有学生的学号、姓名、选课数、总成绩
select 学号, 姓名 from student;
select 学号, count(课程号) 选课号, max(成绩) 总成绩 from score group by 学号;
select a.学号, a.姓名, count(b.课程号) as 选课数, sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;
-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select 学号, avg(成绩) 平均成绩 from score group by 学号 HAVING avg(成绩) > 85;
select a.学号, a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
HAVING avg(成绩) > 85;
-- 查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号, a.姓名, c.课程号, c.课程名称
from student a
left join score b on a.学号 = b.学号
left join course c on b.课程号 = c.`课程号`
-- 查询出每门课程的及格人数和不及格人数
-- 方式一
select 课程号, count(学号) from score where 成绩 < 60 GROUP BY 课程号;
select 课程号, count(学号) from score where 成绩 >= 60 GROUP BY 课程号;
-- 方式二
select 课程号,
sum(CASE WHEN 成绩>=60 THEN 1
ELSE 0
END) as 及格人数,
sum(CASE WHEN 成绩<60 THEN 1
ELSE 0
END) as 不及格人数
from score
group by 课程号;
-- 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名|
select a.学号, a.姓名 from student a join
score b on a.学号 = b.学号
where b.课程号='0003' AND b.成绩 > 80
# 多表联查
-- 检索"0001"课程分数小于60，按分数降序排列的学生信息
select 学号 from score where 课程号='0001' AND 成绩 < 60 ORDER BY 成绩 desc;
-- 查询不同老师所教不同课程平均分从高到低显示
select 课程号, avg(成绩) 平均分 from score GROUP BY 课程号 ORDER BY 平均分 desc;
select a.教师号, a.教师姓名, avg(c.成绩)
from teacher as a
inner join course as b on a.教师号= b.教师号
inner join score c on b.课程号= c.课程号
group by a.教师姓名
order by avg(c.成绩) desc;
-- 查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT
姓名, 成绩
FROM student s join score sc on s.`学号` = sc.`学号`
join course co on sc.`课程号` = co.`课程号`
WHERE 成绩 < 60 AND 课程名称='数学';
-- 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select 姓名,课程名称,成绩
from student a join score b on a.`学号` = b.`学号`
join course c on b.课程号 = c.`课程号`
where b.成绩 > 70;
-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select 姓名, a.学号, AVG(成绩) 平均成绩 FROM score a join student b on a.学号 = b.学号
WHERE 成绩 < 60 group by a.学号 HAVING count(`课程号`) >= 2;
-- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct a.`学号`, a.`成绩` , a.`课程号`
from score as a inner join score as b on a.`学号` = b.`学号`
where a.成绩 =b.成绩 and a.课程号 != b.课程号;
-- 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
select 学号 from score where 课程号='0001';
select 学号 from score where 课程号='0002';
select a.学号 from (
select 学号 from score where 课程号='0001'
) a join (
select 学号 from score where 课程号='0002'
) b on a.学号 = b.学号;
-- 查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
select 学号
from score s
join course c on s.`课程号` = c.`课程号`
join teacher t on t.`教师号` = c.`教师号`
WHERE t.`教师姓名` = '孟扎扎';
-- 查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名（与上题类似，"没学过"用not in来实现)
-- 排除学过的
-- 查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）
-- 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.学号,avg(a.成绩 ),
max(case when b.课程名称 = '数学' then a.成绩 else null end ) as '数学',
max(case when b.课程名称 = '语文' then a.成绩 else null end ) as '语文',
max(case when b.课程名称 = '英语' then a.成绩 else null end ) as '英语'
from score as a
inner join course as b on a.课程号 =b.课程号
group by a.学号 ;
