常用SQL（二）

--排序
select * from emp order by job;

--分组
select job,max(sal) from emp group by job;

--having
select job,max(sal) from emp group by job having max(sal)>=2000;
--按年龄段列举出班上最高分数超过80的年龄段
select age,max(score) from student group by age having max(score)>80;

--列举班上最大的男生,（用到子查询)
select name from student where age in (select max(age) from student where sex=1) and sex=1;

--查询出 所有工种中 平均工资 最高的
select job,avg(sal) from emp group by job having avg(sal)=
(
   select max(avg_sal) from
      (select job,avg(sal) avg_sal from emp group by job)
);