文章目录
6、分组函数
-
功能:用作统计使用,又叫聚合函数或统计函数或组函数
-
分类:sum 求和、avg 平均、max 最大值、min 最小值、count 计算个数
-
特点:
- sum、avg一般用于处理数值函数
max、min、count可以处理任何类型 - 以上分组函数忽略null值
- 可以和distinct 搭配实现去重运算
- count函数的单独介绍
一般使用 count(*)用作统计行数 - 和分组函数一同查询的字段要求是 group by 后的字段
- sum、avg一般用于处理数值函数
6.1、用法 SUM ,MAX …
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;
#平均值保存后2位
SELECT SUM(salary) 和, ROUND(AVG(salary),2) 平均 , MAX(salary) 最大 , MIN(salary) 最小 , COUNT(salary) 个数
FROM employees;
6.2、参数支持哪些类型
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(last_name) FROM employees;
6.3、是否忽略null值(不会计算 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;
#不会计算 null
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;
6.4、和distinct搭配(去重)
SELECT SUM(DISTINCT salary) , SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary) ,COUNT(salary) FROM employees;
6.5、count函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
#都是可以统计个数
SELECT COUNT(1) FROM employees;
SELECT COUNT(2) FROM employees;
SELECT COUNT('a') FROM employees;
-
效率
MYISAM存储引擎下,COUNT(*) 的效率高 INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些
6.6、和分组函数一同查询的字段有限制
SELECT AVG(salary) ,employee_id FROM employees;
6.7、练习题
#1、查询公司员工工资的最大值,最小值、平均值、总和
SELECT MAX(salary) 最大值 , MIN(salary) 最小值 ,AVG(salary) 平均值,SUM(salary) 总和
FROM employees;
#2、查询员工表中的最大入职时间和最小入职时间和相差的天数(diffrence)
SELECT DATEDIFF(MAX(hiredate) , MIN(hiredate)) DIFFERNCE
FROM employees;
#3、查询部门编号为90的员工个数
SELECT COUNT(*) 编号为90的员工个数
FROM employees
WHERE department_id = 90;