2020-08-04MySQL复杂查询练习

查询出所有学生的成绩，取3到5条记录
统计出所有学生的总成绩
统计出所有学生的平均成绩
统计出所有学生的最高成绩
统计出所有学生的最低成绩
统计出成绩表中共有多少条记录
统计每个学生的总成绩
统计每个学生的平均成绩
统计每个学生的最高成绩
统计每个学生的最低成绩
统计每门课及格的学生数
统计每门课都及格的学生编号
查询出名字叫周梅每门课的成绩
查询出李四教的所有学生
查询出李四老师教的课程的平均成绩
查询出每个老师教的课程的平均成绩
查询出每个老师教的学生人数
查询出选课人数不低于2人的课程和老师姓名
查询出总分最高的课程及老师姓名
查询平均成绩最高的学生姓名

答：

select * from sc limit 2,3 ;

select sum(score) from sc ;

select avg(score) from sc ;

select max(score) from sc ;

select min(score) from sc ;

select count(*) from sc ;

select sid,sum(score)
from sc group by sid ;

select sid,avg(score)
from sc group by sid ;

select sid,max(score)
from sc group by sid ;

select sid,min(score)
from sc group by sid ;

select cid,count(*)
from sc where score >= 60 group by cid ;

select sid
from sc group by sid having min(score) >= 60 ;

select s.sname,c.cname,s2.score from student s
    inner join sc s2 on s.sid = s2.sid join course c on s2.cid = c.cid where s.sname = '周梅' ;

select s.sname
from student s
    inner join sc s2 on s.sid = s2.sid
    join course c on s2.cid = c.cid
    join teacher t on c.tid = t.tid where t.tname = '李四' ;

select avg(score) from sc s
    inner join course c on s.cid = c.cid
    join teacher t on c.tid = t.tid
where tname = '李四' ;

select t.tname, avg(s2.score)from student s
    join sc s2 on s.sid = s2.sid
    join course c on s2.cid = c.cid
    join teacher t on c.tid = t.tid group by t.tid ;

select t.tname, count(*)from student s
    join sc s2 on s.sid = s2.sid
    join course c on s2.cid = c.cid
    join teacher t on c.tid = t.tid group by t.tid ;

select t.tname, count(*) from student s
    join sc s2 on s.sid = s2.sid
    join course c on s2.cid = c.cid
    join teacher t on c.tid = t.tid group by t.tid having count(*) >= 2 ;

select c.cname,t.tname from sc a
    inner join course c on a.cid = c.cid
    inner join teacher t on c.tid = t.tid
group by c.cid order by sum(a.score) desc limit 1;

select s.sname from student s
    inner join sc a on s.sid = a.sid
group by s.sid order by avg(a.score) desc limit 1;