MySQL分组函数与分组查询

所用数据库——链接地址

一、分组函数

1. 分类

• count：取得记录数
• sum：求和
• avg：取平均
• max：取最大数
• min：取最小数

2. 概述

• 分组函数也叫多行处理函数（输入多行，输出一行）
• 分组函数自动忽略null空值，不需要手动添加 where 条件排除空值
• 分组函数可以互相组合起来使用
• 分组函数一般和group by联合使用
• 可以实现与distinct组合使用（distinct只能出现在所有字段的最前面，多个字段则表示联合去重，是对查询结果集的去重，不改变表数据）
• 分组函数不可以直接使用在where语句后面：
  因为任何一个分组函数都是在group by语句执行结束之后才会执行，而group by都是在 where 执行结束之后执行

二、count

• count(*)表示取得所有记录，不忽略null，即会取得null记录；不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）
• 采用count(字段名称)，忽略null，即不会取得null记录；表示统计字段中不为null的数据总数量
• 取得sc表所有记录

SELECT COUNT(*) FROM sc

• 取得成绩不为null的记录数

SELECT COUNT(grade) FROM sc

• 效率分析
  MyISAM存储引擎下，count()的效率高
  InnoDB存储引擎下，count()和count(grade)的效率差不多

三、sum

• 取得某一列的和，null会被忽略
• 使用 MySQL 的 sum() 函数时，如果根据查询条件没有得到数据，那么 sum() 之后的结果会是 null，而不是0；
  如果根据查询条件得到了多条数据，但是这些数据中的求和字段，有的有值，有的为null，此时 sum() 也会得到正确的结果。总之，为了保证使用上的正确和严谨，以后在使用 sum() 函数的时候，应该使用 ifnull(sum(columnName), 0)
• 取得所有成绩和

SELECT SUM(grade) FROM sc

四、avg

• 取某一列的平均值
• 可以结合round()函数查询指定数据百分比
• 取平均成绩

SELECT AVG(grade) FROM sc

• 查询成绩大于70分的百分比

SELECT ROUND(AVG(grade > 70),2) FROM sc

五、max

• 取得某一列的最大值
• 查询最大成绩

SELECT MAX(grade) FROM sc

六、min

• 取得某一列的最小值
• 查询最小成绩

SELECT MIN(grade) FROM sc

七、组合使用

SELECT COUNT(*),SUM(grade),AVG(grade),MAX(grade),MIN(grade) FROM sc

八、分组查询

• 主要使用group by和having
• 在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段，MySQL中没有意义，但在Oracle中则报错
• 如果想对分组数据再进行过滤需要使用having子句
• 分组函数的执行顺序：
  根据条件查询数据
  分组
  采用having过滤，取得正确的数据
• 按学号分组查询平均成绩

SELECT AVG(grade) FROM sc GROUP BY sno

• 按学号分组查询平均成绩大于70的平均成绩

SELECT AVG(grade) FROM sc GROUP BY sno HAVING AVG(grade) > 70

引、select语句总结

1. 一个完整的select语句格式：

select 字段
from 表名
where …….
group by ………
having …….(为了过滤分组后的数据而存在的，即不可以单独的出现)
order by ………

2. 语句执行顺序

1. 首先执行where语句过滤原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序

• 原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的