操作方法
先来介绍一下我们的表 1.学生表student（s_id，name，sex，score） 2.班级表 class （c_id,c_name） 3.学生-班级 student_class(s_id,c_id)
#查询每个班级的学生人数。 SELECT c_name,count(*) FROM student_class,class WHERE student_class.c_id=class.c_id GROUP BY c_name;
#查询每个班的男女人数,以及总人数 SELECT c_name,sum(case when sex='女' then 1 ELSE 0 END) as'女',sum(case when sex='男' then 1 ELSE 0 END) as'男' ,count(*) as total FROM student,student_class,class WHERE student.s_id=student_class.s_id and student_class.c_id=class.c_id GROUP BY c_name;
#查询每个班级的平均分 按照平均分高低排序 SELECT c_name,avg(score)as avg_s FROM student,student_class,class WHERE student.s_id=student_class.s_id and student_class.c_id=class.c_id GROUP BY c_name ORDER BY avg_s DESC;
#查询 一班中 分数大于90分的学生信息 select * from student where score> 90 and s_id in( select s_id from student_class where c_id in(select c_id from class where c_name='一班' ));
#查询每个班级的女生人数和平均分 select c.c_name,count(s.s_id) as '女生人数',avg(s.score) as '平均分' from class c inner join student_class sc on sc.c_id=c.c_id inner join student s on s.s_id=sc.s_id where s.sex= '女' group by c.c_name ORDER BY avg(s.score) desc;