SQL查询
所用的三张表如图:
1、查询学生的平均成绩>80分的的相关信息;
select * from student where sid in (select sid from xuanke group by sid having AVG(score)>80);
分析:having与where的区别:where用在group by之前,并且where中不能使用聚合函数:
以下为错:select sid from xuanke group by sid where AVG(score)>80
having写在groupby之后,表示对分组后的结果再过滤;正确的为select sid from xuanke group by sid having AVG(score)>80
,
2、查询选课数>5的学生信息;
select * from student where sid in ( select sid from xuanke group by sid having COUNT(cid)>5);