分组函数 - 《Mysql》

单行处理函数
- group by 和 having
多字段分组查询

记住：所有的分组函数都是对“某一组”数据进行操作的。
分组函数一共5个。分组函数还有另一个名字：多行处理函数。
多行处理函数的特点：输入多行，最终输出一行。
分组函数自动忽然NULL。
select count (comm) from emp;
+——————-+
| count(comm) |
+——————-+
| 4 |
+——————-+
select sum(comm) from emp;
+—————-+
| sum(comm) |
+—————-+
| 2200.00 |
+—————-+
select sum(comm) from emp where comm is not null;//不需要额外添加这个过滤条件。sum函数自动忽略NULL.
找出工资高于平均工资的员工
select avg(sal) from emp;
+——————-+
| avg(sal) |
+——————-+
| 2073.214286 |
+——————-+
select ename,sal from emp where sal > avg(sal);
思考以上的错误信息：无效的使用了分组函数？
原因：sql语句当中有一个语法规则，分组函数不可直接使用在where子句当中。why？？？
因为group by是在where执行之后才会执行的。
select 5
。。
from 1
。。
where 2
。。
group by 3
。。
having 4
。。
order by 6
。。
count （计数）
select count(*) from emp;
select count(ename) from emp;

sun（求和）
找出工资总和？
select sun(sal) from emp;

avg（平均值）
找出平均工资？
select avg(sal) from emp;

max （最大值）
找出最高工资？
select max(sal) from emp;

min （最小值）
select min(sal) from emp;
cout()和count(具体的某个字段)，他们有什么区别？
count()：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count（comm）：表示统计comm字段中不为NULL的数据总数量。
分组函数也能组合起来用：
select count(),sum(sal),avg(sal),max(sal),min(sal) from emp;
+—————+—————+——————-+—————+—————+
| count() | sum(sal) | avg(sal) | max(sal) | min(sal) |
+—————+—————+——————-+—————+—————+
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
+—————+—————+——————-+—————+—————+
找出工资高于平均工资的员工？
第一步：找出平均工资
select avg(sal) from emp;
+——————-+
| avg(sal) |
+——————-+
| 2073.214286 |
+——————-+
第二步：找出高于平均工资的员工
select ename,sal from emp where sal > 2073.214286;
+———-+————-+
| ename | sal |
+———-+————-+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+———-+————-+
select ename,sal from emp where sal >(select avg(sal) from emp);
+———-+————-+
| ename | sal |
+———-+————-+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+———-+————-+

单行处理函数

 什么是单行处理函数<br />输入一行，输出一行。<br />计算每个员工的年薪？<br />select ename,(sal+comm)*12 as yearsal from emp;<br />使用ifnull函数：<br />select ename,(sal+ifnull(comm,0))*12 as yeaesal from emp;<br />重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。<br />ifnull() 空处理函数？<br />     ifnull（可能为null的数据，被当做什么处理）：属于单行处理函数<br />select ename,ifnull (comm,0) as comm from emp;<br />+--------+---------+<br />| ename  | comm    |<br />+--------+---------+<br />| SMITH  |    0.00 |<br />| ALLEN  |  300.00 |<br />| WARD   |  500.00 |<br />| JONES  |    0.00 |<br />| MARTIN | 1400.00 |<br />| BLAKE  |    0.00 |<br />| CLARK  |    0.00 |<br />| SCOTT  |    0.00 |<br />| KING   |    0.00 |<br />| TURNER |    0.00 |<br />| ADAMS  |    0.00 |<br />| JAMES  |    0.00 |<br />| FORD   |    0.00 |<br />| MILLER |    0.00 |<br />+--------+---------+

group by 和 having

group by ：按照某个字段或者某些字段进行分组。
having：having是对分组之后的数据进行再次过滤。
案例：找出每个工作岗位的最高薪资。
select max(sal),job from emp group by job;
+—————+—————-+
| max(sal) | job |
+—————+—————-+
| 1300.00 | CLERK |
| 1600.00 | SALESMAN |
| 2975.00 | MANAGER |
| 3000.00 | ANALYST |
| 5000.00 | PRESIDENT |
+—————+—————-+
注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因，并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
select ename,max(sal),job from emp group by job;
以上在mysql当中，查询结果是有的，但是结果没有意义，在oracle数据库当中会报错。语法错误。oracle的语法规则比mysql语法规则严谨。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段
每个工作岗位的平均薪资？
select job,avg(sal) from emp group by job;
+—————-+——————-+
| job | avg(sal) |
+—————-+——————-+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+—————-+——————-+

多字段分组查询

多个字段能不能联合起来一块分组？
案例：找出每个部门不同工作岗位的最高薪资。
select deptno,job,max(sal) from emp group by deptno,job;
+————+—————-+—————+
| deptno | job | max(sal) |
+————+—————-+—————+
| 20 | CLERK | 1100.00 |
| 30 | SALESMAN | 1600.00 |
| 20 | MANAGER | 2975.00 |
| 30 | MANAGER | 2850.00 |
| 10 | MANAGER | 2450.00 |
| 20 | ANALYST | 3000.00 |
| 10 | PRESIDENT | 5000.00 |
| 30 | CLERK | 950.00 |
| 10 | CLERK | 1300.00 |
+————+—————-+—————+
找出每个部门的最高薪资，要求薪资大于2900的数据。
第一步：找出每个部门的最高薪资
select max(sal),deptno from emp group by deptno;
+—————+————+
| max(sal) | deptno |
+—————+————+
| 3000.00 | 20 |
| 2850.00 | 30 |
| 5000.00 | 10 |
+—————+————+
第二步：找出薪资大于2900
select max(sal),deptno from emp group by deptno having max(sal) > 2900;//这种方式效率低
+—————+————+
| max(sal) | deptno |
+—————+————+
| 3000.00 | 20 |
| 5000.00 | 10 |
+—————+————+
select max(sal),deptno from emp where sal >2900 group by deptno;//效率较高，建议能使用where过滤的尽量使用where
| max(sal) | deptno |
+—————+————+
| 3000.00 | 20 |
| 5000.00 | 10 |
+—————+————+
找出每个部门的平均薪资，要求显示薪资大于2000的数据。
第一步：找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
+————+——————-+
| deptno | avg(sal) |
+————+——————-+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+————+——————-+
第二步：要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
+————+——————-+
| deptno | avg(sal) |
+————+——————-+
| 20 | 2175.000000 |
| 10 | 2916.666667 |
+————+——————-+
where后面不能使用分组函数
select deptno,avg(sal) from emp wher avg(sal) > 2000 group by deptno;//错误
总结一个完整的dql语句怎么写？
select 5
。。
from 1
。。
where 2
。。
group by 3
。。
having 4
。。
order by 6
。。