高级查询一.1

高级查询一.1
一、分组查询
#分组查询
select id,AVG(grade) from score group by id;
#多列分组查询
select id,grade,AVG(grade) from score group by id,grade;
#查询补考的学生(多个)
select sid,AVG(grade) from score group by sid having COUNT(sid)>1;
二、子查询
#查询年龄比姓‘zhao’还大的人
select username from info where age>(select age from info where username=‘zhao’);
#查询成绩为80分的人
select i.username,s.grade from info i inner join score s on s.sid=i.id where s.grade=80;
select username from info where id=(select sid from score where grade=80);
三、in exists 子查询
#in子查询，可以返回多条记录
select username from info where id in(select sid from score where grade>=80);
#not in子查询
select username from info where id not in(select sid from score where grade>=80);
#exists查出来或者不出来都有结果
select id,grade from score where exists(select grade from score where grade>95) limit 0,5;
select id,grade from score where not exists(select grade from score where grade>95) limit 0,5;