mysql复杂查询统计总成绩_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;