本文共 5736 字，大约阅读时间需要 19 分钟。

一、分组函数/聚合函数/多行处理函数

1、概念

分组函数一共5个。分组函数自动忽略NULL

分组函数还有另一个名字：多行处理函数。 多行处理函数的特点： 输入多行，最终输出的结果是1行 记住：所有的分组函数都是对“某一组”数据进行操作的。

2、说明

注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。

select count(*) from emp where xxx;符合条件的所记录总数。select count(comm) from emp;	    comm这个字段中不为空的元素总数。

注意：分组函数不能直接使用在where关键字后面。

select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function

思考以上的错误信息：无效的使用了分组函数？

原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。why??? 怎么解释？ 因为group by是在where执行之后才会执行的。

3、count

count(*)和count(具体的某个字段)，他们有什么区别？

count(*): 不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关） count(comm): 表示统计comm字段中不为NULL的数据总数量。 举例： 取得所有的员工数 Count(*)表示取得所有记录，忽略null，为null的值也会取得

mysql> select count(*) from emp;

±---------+

| count(*) | ±---------+ | 14 | ±---------+ 1 row in set (0.05 sec)

取得津贴不为null员工数

mysql> select count(comm) from emp;

±------------+

| count(comm) | ±------------+ | 4 | ±------------+ 1 row in set (0.00 sec) 采用count(字段名称)，不会取得为null的记录

4、sum：可以取得某一个列的和，null会被忽略

找出工资总和？

select sum(sal) from emp;

取得津贴的合计

select sum(comm) from emp where comm is not null;// 不需要额外添加这个过滤条件。sum函数自动忽略NULL。 取得薪水的合计（sal+comm) select sum(sal+comm) from emp;//错误的，原因在于comm字段null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0

select sum(sal+IFNULL(comm, 0)) from emp;

5、单行处理函数:

什么是单行处理函数？

输入一行，输出一行。 举例： 计算每个员工的年薪？ 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;

6、avg：取得某一列的平均值

举例：

取得平均薪水

select avg(sal) from emp;

找出工资高于平均工资的员工？

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..

正确的：

第一步：找出平均工资

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);

7、max：取得某个一列的最大值

举例：

找出最高薪水的员工

mysql> select ename,sal from emp where sal = (select max(sal) from emp);

取得最高薪水

mysql> select max(sal) from emp; 取得最晚入职得员工 mysql> select max(hiredate) from emp;

8、min：取得某个一列的最小值

举例：

取得最低薪水

mysql> select min(sal) from emp;

取得最早入职得员工（可以不使用str_to_date转换）

mysql> select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;

±---------------------------------------+

| min(str_to_date(hiredate, ‘%Y-%m-%d’)) | ±---------------------------------------+ | 1980-12-17 | ±---------------------------------------+ 1 row in set (0.04 sec)

9、组合聚合函数

可以将这些聚合函数都放到select中一起使用

mysql> 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 | ±---------±---------±------------±---------±---------+ 1 row in set (0.00 sec)

二、分组查询

分组查询主要涉及到两个子句，分别是：group by和having

1、group by：

按照某个字段或者某些字段进行分组。

举例： 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

mysql> select job,sum(sal) as sumSal from emp group by job;

案例：找出每个工作岗位的最高薪资。

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

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

select job,avg(sal) from emp group by job;

多个字段能不能联合起来一块分组？

案例：找出每个部门不同工作岗位的最高薪资。

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

注意：分组函数一般都会和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后面只能跟分组函数和参与分组的字段。

2、having :

having是对分组之后的数据进行再次过滤。

举例：

找出每个部门的最高薪资，要求显示薪资大于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。

找出每个部门的平均薪资，要求显示薪资大于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后面不能使用分组函数：

select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno; // 错误了。 这种情况只能使用having过滤。

3、select语句总结

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

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

一个完整的select语句格式如下

select 字段 from 表名 where ……. group by ……… having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现) order by ………

以上语句的执行顺序

首先执行where语句过滤原始数据 执行group by进行分组 执行having对分组数据进行操作 执行select出数据 执行order by排序

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

4.distinct:关于查询结果集的去重

举例：

mysql> select distinct job from emp; // distinct关键字去除重复记录。

±----------+

| job | ±----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | ±----------+

mysql> select ename,distinct job from emp;

以上的sql语句是错误的。 记住：distinct只能出现在所有字段的最前面。

mysql> select distinct deptno,job from emp;//去重deptno与job两相连相同的

±-------±----------+

| deptno | job | ±-------±----------+ | 20 | CLERK | | 30 | SALESMAN | | 20 | MANAGER | | 30 | MANAGER | | 10 | MANAGER | | 20 | ANALYST | | 10 | PRESIDENT | | 30 | CLERK | | 10 | CLERK | ±-------±----------+

案例：统计岗位的数量？

select count(distinct job) from emp;

±--------------------+

| count(distinct job) | ±--------------------+ | 5 | ±--------------------+

转载地址：http://kxuki.baihongyu.com/