3 SQL语句----分组函数

1 分组函数(多行处理函数)

分组函数:
	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子句当中。why????
				怎么解释？
					因为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;
			+-------+---------+
			| 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);

2 单行处理函数

什么是单行处理函数？
		输入一行，输出一行。
	
	计算每个员工的年薪？
		select ename,(sal+comm)*12 as yearsal from emp;
		重点：所有数据库都是这样规定的，只要有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;
		+--------+---------+
		| ename  | comm    |
		+--------+---------+
		| SMITH  |    0.00 |
		| ALLEN  |  300.00 |
		| WARD   |  500.00 |
		| JONES  |    0.00 |
		| MARTIN | 1400.00 |
		| BLAKE  |    0.00 |
		| CLARK  |    0.00 |
		| SCOTT  |    0.00 |
		| KING   |    0.00 |
		| TURNER |    0.00 |
		| ADAMS  |    0.00 |
		| JAMES  |    0.00 |
		| FORD   |    0.00 |
		| MILLER |    0.00 |
		+--------+---------+

3 group by 和 having

group by ： 按照某个字段或者某些字段进行分组。
	having : 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 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)    |
		+-----------+-------------+
		| 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过滤。

4 总结

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

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