15.group by 和having

group by：

select * from emp;
-- 统计各个部门的平均工资
select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用
select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组
select deptno,avg(sal) from emp group by deptno order by deptno desc;

-- 统计各个岗位的平均工资
select  job,lower(job),avg(sal) from emp group by job;

having：

-- 统计各个部门的平均工资，只显示平均工资2000以上的  - 分组以后进行二次筛选 having
select deptno,avg(sal) from emp group by deptno;
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000 ; 
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 >2000 order by deptno desc;

-- 统计各个岗位的平均工资，除了MANAGER
-- 方法1：
SELECT JOB,AVG(SAL) FROM EMP WHERE JOB!='MANAGER' GROUP BY JOB ORDER BY AVG(SAL) DESC; 
-- 方法2：
SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB !='MANAGER' ORDER BY AVG(SAL);

-- 注意：WHERE 和 HAVING 的区别：WHERE是分组前进行过滤的，HAVING是分组后进行过滤的