MySQL--常见的分组函数

1.功能

用作统计使用，又称为聚合函数或统计函数或组函数。

2.分类

sum求和、avg平均值、max最大值、min最小值、count计算个数。

1.简单使用

 求和：

SELECT SUM(salary) FROM employees;

平均值：

SELECT AVG(salary) FROM employees;

最小值：

SELECT MIN(salary) FROM employees;

最大值：

SELECT MAX(salary) FROM employees;

计算个数：

SELECT COUNT (salary) FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;

3.特点

1.sum、avg一般用于处理数值型；max、min、count可以处理任何类型

 SELECT SUM(last_name),AVG(last_name) FROM employees;

SELECT sum(hiredate),AVG(hiredate) FROM employees;

SELECT SUM(last_name),AVG(last_name) FROM employees;

SELECT SUM(hiredate),AVG(hiredate) FROM employees;

SELECT MAX(last_name),MIN(last_name) FROM employees;

SELECT MAX(hiredate),MIN(hiredate) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

SELECT COUNT(last_name) FROM employees;

2.以上分组函数都忽略null值

 SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;

SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

SELECT commission_pct FROM employees;

3.可以和DISTINCT(去重)搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary) COUNT(salary) FROM employees;

4.COUNT(统计个数)函数

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

效率：

MYISAM存储引擎下，COUNT(*)的效率高

INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要搞一些，原因是要进行判断，是否为null

一般使用COUNT(*)统计行数

5.和分组函数一同查询的字段要求是group by后的字段