Mysql_3 分组函数、单行处理函数、group by 和 having

14、分组函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值

记住：所有的分组函数都是对“某一组”数据进行操作的。

    找出工资总和？
		select sum(sal) from emp;
	找出最高工资？
		select max(sal) from emp;
	找出最低工资？
		select min(sal) from emp;
	找出平均工资？
		select avg(sal) from emp;
	找出总人数？
		select count(*) from emp;
		select count(ename) from emp;

分组函数一共5个。
分组函数还有另一个名字：多行处理函数。
多行处理函数的特点：输入多行，最终输出的结果是1行。

**分组函数自动忽略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);
//ERROR 1111 (HY000): Invalid use of group function		

思考以上的错误信息：无效的使用了分组函数？
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。
因为group by是在where执行之后才会执行的。

select		    5
	       ..			
from			1
	       ..
where			2
		   ..
group by		3
		   ..
having		    4
		   ..
order by		6
		   ..

count()和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; 
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 |
+-------+---------+

15、单行处理函数
输入一行，输出一行，就叫单行处理函数。

计算每个员工的年薪？

select ename,(sal+comm)*12 as yearsal from emp;

重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。这里的意思就是SMITH的工资是800，800+null等于null。
解决办法：使用ifnull函数：

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

ifnull() 空处理函数？
ifnull(可能为NULL的数据,被当做什么处理) ： 属于单行处理函数。

   select ename,ifnull(comm,0) as comm from emp;

16、group by 和 having
案例：找出每个工作岗位的最高薪资

select max(sal),job from emp group by job;
    +----------+-----------+
	| max(sal) | job       |
	+----------+-----------+
	|  3000.00 | ANALYST   |
	|  1300.00 | CLERK     |
	|  2975.00 | MANAGER   |
	|  5000.00 | PRESIDENT |
	|  1600.00 | SALESMAN  |
	+----------+-----------+

注意⚠️：
分组函数一般都会和group by联合使用，这也是为什么它会被称为分组函数的原因。
并且任何一个分组函数（count sum avg min max）都是在group by 语句执行结束后才会执行的。
当一条sql语句没有group by 的话，整张表的数据都会自成一组。

select ename,max(sal),job from emp group by job;
// ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

字面意思理解是sql_model=only_full_group_by限制了，导致在以往MYSQL版本中能正常查询的SQL，在5.7后不能用了。

以上在Oracle数据库当中也会报错。语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

每个工作岗位的平均薪资？

select job,avg(sal) from emp group by job;
		+-----------+-------------+
		| job       | avg(sal)    |
		+-----------+-------------+
		| ANALYST   | 3000.000000 |
		| CLERK     | 1037.500000 |
		| MANAGER   | 2758.333333 |
		| PRESIDENT | 5000.000000 |
		| SALESMAN  | 1400.000000 |
		+-----------+-------------+
	

多个字段能不能联合起来一块分组？
案例：找出每个部门不同工作岗位的最高薪资。

        select 
			deptno,job,max(sal)
		from
			emp
		group by
			deptno,job;

找出每个部门的最高薪资，要求显示薪资大于2900的数据。

        第一步：找出每个部门的最高薪资
 		select max(sal),deptno from emp group by deptno;
		+----------+--------+
		| max(sal) | deptno |
		+----------+--------+
		|  5000.00 |     10 |
		|  3000.00 |     20 |
		|  2850.00 |     30 |
		+----------+--------+

		第二步：找出薪资大于2900
		select max(sal),deptno from emp group by deptno having max(sal) > 2900; // 这种方式效率低。
		+----------+--------+
		| max(sal) | deptno |
		+----------+--------+
		|  5000.00 |     10 |
		|  3000.00 |     20 |
		+----------+--------+

		select max(sal),deptno from emp where sal > 2900 group by deptno;  // 效率较高，建议能够使用where过滤的尽量使用where。
		+----------+--------+
		| max(sal) | deptno |
		+----------+--------+
		|  5000.00 |     10 |
		|  3000.00 |     20 |
		+----------+--------+

找出每个部门的平均薪资，要求显示薪资大于2000的数据。

    第一步：找出每个部门的平均薪资
	select deptno,avg(sal) from emp group by deptno;
	+--------+-------------+
	| deptno | avg(sal)    |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+

	第二步：要求显示薪资大于2000的数据
	select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;	
	+--------+-------------+
	| deptno | avg(sal)    |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	+--------+-------------+

	where后面不能使用分组函数：
	select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno;	// 错误了。
	这种情况只能使用having过滤。

17、总结一个完整的DQL语句怎么写？

    select		    5
		..
	from			1	
		..
	where			2
		..
	group by		3
		..
	having		    4
		..
	order by		6
		..
	

以上语句的执行顺序
1.首先执行where语句过滤原始数据
2.执行group by进行分组
3.执行having对分组数据进行操作
4.执行select选出数据
5.执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。