sql Day02: 分组函数

一、count：计数
sum:求和
avg:平均数
max:最大值
min：最小值
分组函数一共五个。分组函数又叫多行处理函数。
所有的分组函数都是对某一组数据进行操作。
1.找出员工工资的总和：SELECT SUM(sal) FROM emp;

2.找出最高工资：SELECT MAX(sal) FROM emp;

3.找出平均工资：SELECT AVG(sal) FROM emp;

4.找出总人数：SELECT COUNT(ename) AS '人数' FROM emp;

5.分组函数自动忽略NULL。所以在有null参与计算是结果不会为null。
select count(comm) from emp;

6.单行处理函数（输入一行，输出一行）
计算每个员工的年薪：SELECT ename,(sal+comm)12 AS '年薪' FROM emp;（*错误）
SELECT ename,(sal+IFNULL(comm,0))*12 AS '年薪' FROM emp;（正确）


**注意：在数据库中，只要数学表达式有null出现，结果就为null。

ifnull()空处理函数：ifnull(可能为null的数据，被当做什么处理）
例如：SELECT ename,IFNULL(comm,0) FROM emp;把comm为Null的当成0处理。

7.找出工资高于平均工资的员工:
SELECT ename,sal FROM emp WHERE sal>AVG(sal);(错误）
//Invalid use of group function
原因：sql语句中，分组函数不能直接使用在where子句当中。
** 因为group by 是在where执行之后才执行的。（还没分组，就使用分组函数，当然错误）**。
第一步:找出平均工资
select avg(sal) from emp;
第二部:找出高于平均工资的员工
select ename,sal from emp where sal>2073.214286;
====>select ename,sal from emp where sal>(select avg(sal) from emp);//子查询

8.couunt()和count(某个字段的区别）:
SELECT COUNT() FROM emp;//总记录数，与字段无关

SELECT COUNT(comm) FROM emp;//字段中不为空的总数

couunt()：不是统计某个字段中数据的个数，而是统计总记录条数，与字段无关。
count(某字段):表示统计字段中不为Null的数据总数。
9.分组函数也能组合起来用：SELECT COUNT(),SUM(sal),AVG(sal),MAX(sal),MIN(sal) FROM emp;

10.分组查询：group by 和 having
1.group by： 是按照某个字段或者某些字段进行分组。
having：是对分组之后的数据进行再次过滤
例如：找出每个工作岗位的最高薪资：
SELECT 3
job,MAX(sal)
FROM ** 1
emp
*GROUP BY * 2
job;

**注意:分组函数（5个）一般都会和group by联合使用。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束以后才会执行。
当一条sql语句没有group by的话 整张表的数据会自成一组。
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order bzuy 6
..

where--->group by---->分组函数：分组函数不能写在where后，得先分组。
结论：当一条语句有group by的话，select后面只能跟参加分组的字段和分组函数，其他都不能跟。
11.每个个工作岗位的平均薪资：SELECT job ,AVG(sal) FROM emp GROUP BY job;

12.找出每个部门不同工作岗位的最高薪资：
select **
deptno,job,max(sal)
**from
emp
group
deptno,job;

13.having 找出每个部门的最高薪资，要求显示薪资大于2900的：
第一步：找出每个部门的最高薪资：SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;
第二步：找出大于2900的：SELECT deptno,MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>2900;
（效率低）
效率高：SELECT deptno,MAX(sal) FROM emp WHERE sal>2900 GROUP BY deptno;

14.找出每个部门的平均薪资，要求显示大于2000的。
第一步：找出每个部门的平均薪资：SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

第二步：要求显示大于2000的：SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;
//此时不能用where，因为where后面不能用分组函数。

总结：having必须和group by搭配使用，没有group by就不能用having。having效率低，慎用，一般可用where代替。
15.总结一个完整的DQL语句怎么写？
select 5
..
from ** 1
..
*where * 2
..
*group by * 3
..
**having 4
..
order by 6
..
16.关于查询结果集的去重
16.1例如：查询工作岗位（去重复）
SELECT DISTINCT job FROM emp;//distinct关键字去重复记录。

2.错误的：SELECT ename ,DISTINCT job form emp;
distinct前面不能有别的字段，distinct只能出现在所有字段的最前面。
2.1 distinct写在最前面，后面的字段联合去重（看出一个整体）。
例如：SELECT DISTINCT deptno,job FROM emp ORDER BY deptno;

案例：统计岗位的数量
SELECT COUNT( DISTINCT job) FROM emp;