测试表结构

 1 DROP TABLE IF EXISTS `t_emp`;
 2 CREATE TABLE `t_emp` (
 3   `empno` int(4) NOT NULL,
 4   `ename` varchar(20) DEFAULT NULL,
 5   `job` varchar(20) DEFAULT NULL,
 6   `mgr` int(4) DEFAULT NULL,
 7   `hiredate` date DEFAULT NULL,
 8   `sal` decimal(10,2) DEFAULT NULL,
 9   `comm` decimal(10,2) DEFAULT NULL,
10   `deptno` int(2) DEFAULT NULL,
11   PRIMARY KEY (`empno`)
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13 
14 DROP TABLE IF EXISTS `t_dept`;
15 CREATE TABLE `t_dept` (
16   `deptno` int(2) NOT NULL,
17   `dname` varchar(20) DEFAULT NULL,
18   `loc` varchar(20) DEFAULT NULL,
19   PRIMARY KEY (`deptno`)
20 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

聚合函数

SUM函数

SUM函数用于求和,只能用于数字类型,字符类型的统计结果为0,日期类型统计结果是毫秒数相加

1 SELECT SUM(ename), SUM(sal) FROM t_emp;

 

 注意SUM函数求和会排除NULL值

MAX函数

MAX函数用于获取非空值的最大值

1 SELECT MAX(comm) FROM t_emp;

 

MIN函数

MIN函数用于获取非空值的最小值

1 SELECT MIN(comm) FROM t_emp;

 

 AVG函数

AVG函数用于获得非空值的平均值,非数字数据统计结果为0

1 SELECT AVG(ename), AVG(sal+IFNULL(comm,0)) FROM t_emp;

 

COUNT函数

COUNT(*)用于获得主键条数,COUNT(列名)用于获得包含非空值的记录数.

1 SELECT COUNT(*), COUNT(comm) FROM t_emp;

 

 分组查询

默认情况下聚合函数是对全表范围内的数据做统计

GROUP BY子句的作用是通过一定的规则蒋一个数据集划分成若干个小的区域,然后针对每个小区域分进行数据聚合处理

1 SELECT deptno, AVG(sal) FROM t_emp GROUP BY deptno;

 

 对SELECT子句要求

查询语句中含有GROUP BY子句,那么SELECT子句中的内容就必须遵守规定:SELECT子句中可以包括聚合函数,或者GROUP BY子句的分组列,其余内容均不可以出现在SELECT子句中.

WITH ROLLUP

在group分组字段的基础上再进行统计数据

1 SELECT deptno, COUNT(*), AVG(sal), MAX(sal), MIN(sal) FROM t_emp GROUP BY deptno WITH ROLLUP;

 

 GROUP_CONCAT函数

GROUP_CONCAT函数可以把分组查询中的某个字段拼成一个字符串

1 SELECT deptno, GROUP_CONCAT(ename),COUNT(*) 
2 FROM t_emp 
3 WHERE sal >=2000
4 GROUP BY deptno;

 

 GROUP BY语句在查询语句的执行顺序

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

HAVING子句

因为WHERE子句先于GROUP BY,一旦WHERE中出现了聚合函数,数据库根本不知道按照什么范围计算聚合值,此时需要HAVING子句

1 SELECT deptno 
2 FROM t_emp
3 WHERE hiredate >= '1982-01-01'
4 GROUP BY deptno
5 HAVING COUNT(*) >=2
6 ORDER BY deptno;

 

HAVING子句的特殊用法

按照数字1分组,MySQL会根据SELECT子句中的列进行分组,HAVING子句也可以正常执行

1 SELECT deptno,COUNT(*) 
2 FROM t_emp
3 GROUP BY 1
4 HAVING deptno IN (10,20);