MySQL聚合函数及其应用

聚合函数是数据库操作中不可或缺的工具，能够对数据集进行高效统计分析。这类函数接收多行数据作为输入，经过计算后返回单个汇总值，为数据统计提供了强大支持。 1. 常见的聚合函数 作用： 对数据集进行处理并返回计算结果 语法： 聚合函数不能嵌套使用 禁止出现类似"AVG(SUM(字段名称))"的嵌套调用 1.1 AVG和SUM函数 适用对象：数值类型数据 功能说明：AVG计算平均值，SUM计算总和 mysql> SELECT -> AVG(salary) '平均工资', -> SUM(salary) '总工资' -> FROM -> employees;+-------------+-----------+| 平均工资 | 总工资 |+-------------+-----------+| 6461.682243 | 691400.00 |+-------------+-----------+1 row in set (0.01 sec) 注意：若应用于非数值类型数据，虽不会报错但计算结果无实际意义 1.2 MIN和MAX函数 适用对象：多种数据类型（数值、字符串、日期等） 功能说明：MIN查找最小值，MAX查找最大值 数值类型将返回最大最小值 mysql> SELECT -> MAX(salary) '最高工资' -> ,MIN(salary) '最低工资' -> FROM -> employees;+----------+----------+| 最高工资 | 最低工资 |+----------+----------+| 24000.00 | 2100.00 |+----------+----------+1 row in set (0.00 sec) 字符串类型按字典序比较 mysql> SELECT -> MAX(last_name) -> FROM -> employees;+----------------+| MAX(last_name) |+----------------+| Zlotkey |+----------------+1 row in set (0.00 sec) 1.3 COUNT函数 适用对象：任意数据类型 功能说明：统计表中记录数量 COUNT(*)返回总记录数，适用于所有数据类型 mysql> SELECT COUNT(*) FROM employees;+----------+| COUNT(*) |+----------+| 107 |+----------+1 row in set (0.00 sec) COUNT(expr) 仅统计expr非空的记录数 mysql> SELECT -> COUNT(commission_pct) -> FROM -> employees;+-----------------------+| COUNT(commission_pct) |+-----------------------+| 35 |+-----------------------+1 row in set (0.00 sec) 常见问题解答： 问：AVG(xxx) 是否等于 SUM(xxx) / COUNT(xxx)？ 答：相等，AVG()和SUM()都会自动过滤NULL值 问：count(*)，count(1)，count(列名)如何选择？ 答1：MyISAM引擎下三者无区别，引擎内部维护行数计数器 答2：InnoDB引擎建议使用count(*)或count(1)，其性能优于count(列名) 问：能否用count(列名)替代count(*)? 答：不建议替换 count(*)是SQL92标准语法，统计所有行数包括NULL值 count(列名)会忽略该列为NULL的记录 实践练习： 计算公司平均奖金率 mysql> SELECT -> SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)) '平均奖金率', -> AVG(IFNULL(commission_pct,0)) '平均奖金率' -> FROM -> employees;+------------+------------+| 平均奖金率 | 平均奖金率 |+------------+------------+| 0.072897 | 0.072897 |+------------+------------+1 row in set (0.00 sec) 注意：AVG()会排除NULL值，但计算奖金率时需将0计入 2. GROUP BY 数据分组操作 2.1 基本用法 使用GROUP BY子句对数据进行分组统计 mysql> SELECT -> department_id, -> AVG( salary ) -> FROM -> employees -> GROUP BY -> department_id;+---------------+---------------+| department_id | AVG( salary ) |+---------------+---------------+| NULL | 7000.000000 || 10 | 4400.000000 || 20 | 9500.000000 || 30 | 4150.000000 || 40 | 6500.000000 || 50 | 3475.555556 || 60 | 5760.000000 || 70 | 10000.000000 || 80 | 8955.882353 || 90 | 19333.333333 || 100 | 8600.000000 || 110 | 10150.000000 |+---------------+---------------+12 rows in set (0.00 sec) 2.2 多列分组 mysql> SELECT -> department_id, -> job_id, -> SUM( salary ) -> FROM -> employees -> GROUP BY -> department_id, -> job_id;+---------------+------------+---------------+| department_id | job_id | SUM( salary ) |+---------------+------------+---------------+| 90 | AD_PRES | 24000.00 || 90 | AD_VP | 34000.00 || 60 | IT_PROG | 28800.00 || 100 | FI_MGR | 12000.00 || 100 | FI_ACCOUNT | 39600.00 || 30 | PU_MAN | 11000.00 || 30 | PU_CLERK | 13900.00 || 50 | ST_MAN | 36400.00 || 50 | ST_CLERK | 55700.00 || 80 | SA_MAN | 61000.00 || 80 | SA_REP | 243500.00 || NULL | SA_REP | 7000.00 || 50 | SH_CLERK | 64300.00 || 10 | AD_ASST | 4400.00 || 20 | MK_MAN | 13000.00 || 20 | MK_REP | 6000.00 || 40 | HR_REP | 6500.00 || 70 | PR_REP | 10000.00 || 110 | AC_MGR | 12000.00 || 110 | AC_ACCOUNT