聚合或聚集、分组函数对一组数据进行汇总的函数输入一组数据的集合输出单个值1. 常见的聚合函数作用作用于一组数据并对一组数据返回一个值语法聚合函数不能嵌套调用比如不能出现类似“AVG(SUM(字段名称))”形式的调用1.1 AVG和SUM函数对象数值型数据作用AVG平均值SUM总和mysqlSELECT-AVG(salary)平均工资,-SUM(salary)总工资-FROM-employees;------------------------|平均工资|总工资|------------------------|6461.682243|691400.00|------------------------1rowinset(0.01sec)注意如果应用于字符串等类型不会报错但是结果没有意义1.2 MIN和MAX函数对象任意数据类型数值、字符串、时间日期……作用MIN最小值MAX最大值数值即最大、最小的数值mysqlSELECT-MAX(salary)最高工资-,MIN(salary)最低工资-FROM-employees;--------------------|最高工资|最低工资|--------------------|24000.00|2100.00|--------------------1rowinset(0.00sec)字符串按字典序mysqlSELECT-MAX(last_name)-FROM-employees;----------------|MAX(last_name)|----------------|Zlotkey|----------------1rowinset(0.00sec)1.3 COUNT函数对象任意数据类型作用返回表中记录总数COUNT(*)返回表中记录总数适用于任意数据类型mysqlSELECTCOUNT(*)FROMemployees;----------|COUNT(*)|----------|107|----------1rowinset(0.00sec)COUNT(expr)返回expr不为空的记录总数mysqlSELECT-COUNT(commission_pct)-FROM-employees;-----------------------|COUNT(commission_pct)|-----------------------|35|-----------------------1rowinset(0.00sec)QAQAVG(xxx) 等于 SUM(xxx) / COUNT(xxx) 吗A等于AVG()和SUM()也会过滤NULLQcount(*)count(1)count(列名)用谁好A1对于MyISAM引擎的表是没有区别的引擎内部有一计数器在维护着行数A2但若是Innodb引擎的表用count(*),count(1)直接读行数复杂度是O(n)O(n)O(n)因为innodb真的要去数一遍但好于具体的count(列名)Q能不能使用count(列名)替换count(*)?A不要使用count(列名)来替代count(*)count(*)是 SQL92 定义的标准统计行数的语法跟数据库无关跟 NULL 和非 NULL 无关count(*)会统计值为 NULL 的行而 count(列名)不会统计此列为 NULL 值的行练习查询公司中的平均奖金率mysqlSELECT-SUM(commission_pct)/COUNT(IFNULL(commission_pct,0))平均奖金率,-AVG(IFNULL(commission_pct,0))平均奖金率-FROM-employees;------------------------|平均奖金率|平均奖金率|------------------------|0.072897|0.072897|------------------------1rowinset(0.00sec)注意AVG()会把NULL去掉但平均奖金率应该把0也算进去2. GROUP BYgroup 组2.1 基本使用可以使用GROUP BY子句将表中的数据分成若干组mysqlSELECT-department_id,-AVG(salary)-FROM-employees-GROUPBY-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|------------------------------12rowsinset(0.00sec)2.2 使用多个列分组mysqlSELECT-department_id,-job_id,-SUM(salary)-FROM-employees-GROUPBY-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|8300.00|------------------------------------------20rowsinset(0.00sec)注意SELECT 中出现的非组函数的字段必须声明在GROUP BY 中GROUP BY 子句中声明的字段可以不出现在SELECT 中GROUP BY声明在 FROM 、WHERE 后面ORDER BY、LIMIT 前面2.3 GROUP BY中使用WITH ROLLUP使用 WITH ROLLUP 关键字之后在所有查询出的分组记录之后增加一条记录该记录计算查询出的所有记录的总和即统计记录数量mysqlSELECT-department_id,-AVG(salary)-FROM-employees-WHERE-department_id80-GROUPBY-department_idWITH ROLLUP;------------------------------|department_id|AVG(salary)|------------------------------|90|19333.333333||100|8600.000000||110|10150.000000||NULL|11809.090909|# 多的一行------------------------------# 算的平均所以这里是总的平均值4rowsinset(0.00sec)[!注意]当使用ROLLUP时不能同时使用ORDER BY子句进行结果排序即ROLLUP和ORDER BY是互相排斥的3. HAVING注意WHERE不能使用聚合函数所以过滤条件中出现聚合函数就得用HAVING过滤分组HAVING子句行已经被分组使用聚合函数满足HAVING 子句中条件的分组将被显示HAVING 不能单独使用必须要跟 GROUP BY 一起使用且HAVING 必须声明在 GROUP BY 后面mysqlSELECT-department_id,-MAX(salary)-FROM-employees-GROUPBY-department_id-HAVING-MAX(salary)10000;------------------------------|department_id|MAX(salary)|------------------------------|20|13000.00||30|11000.00||80|14000.00||90|24000.00||100|12000.00||110|12000.00|------------------------------6rowsinset(0.00sec)练习查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息息方法一用WHEREmysqlSELECT-department_id,-MAX(salary)-FROM-employees-WHERE-department_idIN(10,20,30,40)-GROUPBY-department_id-HAVING-MAX(salary)10000;------------------------------|department_id|MAX(salary)|------------------------------|20|13000.00||30|11000.00|------------------------------2rowsinset(0.00sec)方法二用HAVINGmysqlSELECT-department_id,-MAX(salary)-FROM-employees-GROUPBY-department_id-HAVING-MAX(salary)10000-ANDdepartment_idIN(10,20,30,40);------------------------------|department_id|MAX(salary)|------------------------------|20|13000.00||30|11000.00|------------------------------2rowsinset(0.00sec)推荐使用方式一方式一执行效率高于方式二[!结论]当过滤条件中有聚合函数时则此过滤条件必须声明HAVING中当过滤条件中没有聚合函数时则此过滤条件在WHERE或HAVING 中都可以但是建议声明在WHERE中[!WHERE和HAVING的对比]HAVING的适用范围更广若没有聚合函数WHERE的执行效率比HAVING高(从下面的执行原理中可见一斑)4. SQL 底层执行原理4.1 SELECT 语句的完整结构SQL92语法SELECT...,...,...(存在聚合函数FROM...,...,...WHERE多表的连接条件AND不包含聚合函数的过滤条件GROUPBY...,...HAVING包含聚合函数的过滤条件ORDERBY...,...(ASC/DESC)LIMIT...,...SQL99语法SELECT ...,...,...(存在聚合函数 FROM ...(LEFT/RIGHT)JOIN...ON 多表的连接条件 WHERE 不包含聚合函数的过滤条件 GROUP BY ...,... HAVING 包含聚合函数的过滤条件 ORDER BY ...,...(ASC/DESC) LIMIT ...,...4.2 SELECT执行顺序FROM - ON - (LEFT/RIGHT)JOIN - WHERE - GROUP BY - HAVING - SELECT 的字段 - DISTINCT - ORDER BY - LIMIT在 SELECT 语句执行这些步骤的时候每个步骤都会产生一个虚拟表然后将这个虚拟表传入下一个步骤中作为输入这些步骤隐含在 SQL 的执行过程中对于我们来说是不可见的4.3 SQL 的执行原理SQL 是声明式语言我们写的是“要什么”数据库引擎负责“怎么实现”。其底层执行遵循固定的逻辑顺序≠ 书写顺序该顺序决定了查询如何逐步生成结果阶段关键字/操作作用输出虚拟表1. FROM JOINFROMJOIN含CROSS JOIN,INNER JOIN,LEFT JOIN等构建初始数据集• 多表时先做笛卡尔积CROSS JOIN→ 得vt1-1• 再通过ON条件筛选 →vt1-2• 若有外连接LEFT/RIGHT/FULL添加外部行 →vt1-3vt1最终原始数据集2. WHEREWHERE对vt1进行行级过滤条件筛选vt23. GROUP BYGROUP BY在vt2上按指定列分组vt3分组后中间表4. HAVINGHAVING对vt3的分组结果进行过滤聚合函数可用vt45. SELECTSELECT提取指定字段可含表达式、别名vt5-16. DISTINCTDISTINCT去除重复行在vt5-1上操作vt5-27. ORDER BYORDER BY按指定字段排序稳定排序vt68. LIMIT / OFFSETLIMIT [n] [OFFSET m]截取前 n 行或跳过 m 行后取 n 行vt7最终结果集虚拟表命名说明vt1-xFROMJOIN 阶段的子步骤vt1vt7各主阶段输出的逻辑中间结果补充并非所有阶段都存在若无GROUP BY则跳过GROUP BY和HAVING若无DISTINCT则跳过该步若无ORDER BY或LIMIT对应阶段省略关键字顺序 ≠ 执行顺序书写顺序SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT为什么理解执行顺序很重要解释为何WHERE中不能用SELECT别名此时尚未执行 SELECT理解HAVING可用聚合函数而WHERE不行分组未完成优化查询避免在WHERE中使用函数导致索引失效因 WHERE 早于 SELECT
