MySQL— —03聚合查询

MySQL— —聚合查询

1、聚合查询

有时候，我们并不需要返回实际表中的数据，只是对数据进行总结。那么MySQL就为我们提供了一些聚合函数。常用的聚合函数有：MAX（）、MIN（）、COUNT（）、SUM（）、AVG（）

函数	作用
AVG（）	返回某列的平均值
COUNT（）	返回某列的行数
MAX（）	返回某列的最大值
MIN（）	返回某列的最小值
SUM（）	返回某列值的和
注意：

• COUNT()函数统计表中包含的记录行的总数，或根据查询结果返回列中包含的数据行数。其使用方法有两种：
  ①COUNT(*)，计算表中总的行数，不管某列是否有数值或空值
  ②COUNT(字段名)，计算指定列下总的行数，计算时将忽略空值的行
• SUM()是一个求总和的函数，返回指定列值的总和。sum()函数在计算时，忽略列值为null的行。
• AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
• MAX()函数返回指定列中的最大值

MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，返回包括字符类型的最大值。在堆字符类型数据进行比较时，按照字符的ASCII码值大小进行比较，从a~z，a的ASCII码最小，z的最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相等或字符结束为止。例如，'b’与’t’比较时，'t’为最大值；"bcd"与"bca"比较时候，"bcd"为最大值。

• MIN()函数与MAX（）函数类似，不仅适用于查找数据类型，也可应用于字符类型。

2、分组查询（group by）

GROUP BY 关键字通常和聚合函数一起使用
查询每个部门的人数

select deptid,count(1) from emp group by deptid;

统计每个部门的男女人数

select deptid,empsex,count(1) from emp group by deptid,empsex;

分组后再排序

select deptid,empsex,count(1) from emp group by deptid,empsex order by deptid desc, 
count(1) desc;

3、使用HAVING过滤分组

GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示
统计每个部门的男女人数只要大于等于3的信息

select deptid,empsex,count(1) from emp group by deptid,empsex having count(1)>=3 order 
by count(1) desc, deptid desc;

4、LIMIT

select返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回第一行或前几行，可以使用LIMIT关键字。

拓展（面试）：count（1）与count（*）区别？

按照性能排序： count(*)≈count(1)>count(主键字段)>count(字段)

• count()是一个聚合函数，函数的参数不仅可以是字段名也可以是任意的表达式，作用是统计符合查询条件的记录中不为NULL的记录有多少个。
  ①假设count()函数的参数是字段名，如下：

  select count(emp_name) from emp;
  

  该语句统计的是emp表中，emp_name字段不为NULL的记录有多少个

  ②假设count()函数的参数是数字1这种表达式：

  select count(1) from emp;
  

  这条语句是统计emp表中，1这个表达式不为NULL的记录有多少个。【1这个表达式就是单纯数字，因此它永远都不会是NULL，所以上面这条语句就类似于统计emp表中有多少条记录】
  
  在通过count函数统计有多少条记录时候，MySQL的server层会维护一个叫做count的变量。server层会循环向InnoDB读取一条记录，如果count函数指定的参数不为NULL，那么就会将count变量加1，直到符合查询的全部记录被读取完，就退出循环。最后将count变量的值发送给客户端。
  
  InnoDB是通过B+树来保持记录的，根据索引的类型分为聚簇索引和二级索引（非聚簇索引），聚簇索引的叶子节点存放的是实际数据，而非聚簇索引的叶子节点存放的是主键值，而不是实际数据。
  

  以下面的这条语句为例：

  -- id为主键
  select count(id) from emp;
  

  如果表里只有主键索引，没有二级索引时，那么InnoDB循环遍历聚簇索引，将读取到的记录返回给server层，然后读取记录中的id值，就会判断id值是否为null，如果不为null，就将count变量加1。

• 但是，如果表里有二级索引时，InnoDB循环遍历的对象就不是聚簇索引，而是二级索引。

• 这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以生成的二级索引树将比聚簇索引树小，这样遍历二级索引的成本就会比遍历聚簇索引的I/O成本小，因此【优化器】优先选择是二级索引。

count(1)执行过程如下：
select count(1) from emp;
如果表里面只有主键，没有索引时。那么InnoDB循环遍历聚簇索引（主键索引），将读取到的记录返回给server层，但是不会读取记录中的任何字段的值【因为是常数，不是字段，所以不需要读取记录中的字段值】。