mysql学习-- 聚合函数,group by理解与使用

聚合函数

定义

作用一组数据，并对数据返回一个值。
例如，查询公司每个部门的最高工资
解决办法，
（1）select 函数 from 表 where 部门
这只能获取一条数据（或一个部门的最高工资）
（2）分析上述问题，实际上是将公司所有员工这个整体按部门分组 ，然后对每一组求最高工资，每一组就是一条数据然后打印出来
聚合函数就是为了解决上述问题（对数据分组并对每组数据操作），
sql 语句：

select 部门id,max(薪资字段) from 表名称 group by 部门

解释，

• 获取最高工资是通过max()函数，对所有工资取最大值，
• group by xx ,就是根据xx分组，
• 合起来就是对每一组进行获取最大值，然后会返回每一组的数据。
• 总的来说，通过group by进行分组，通过max（）等聚合函数，对每组数据进行操作，来解决分组操作问题

常用的聚合函数

1. avg() ,一组数据的平均数据， sum() ,一组数据的总和
   以上连个只适用于数值类型的字段

2. max()，一组数据的最大值，min()一组数据的最小值
   注意，max(name), 字符串的最大值是有意义的，字符串可以比较大小，日期也是

3. count() ,计算指定字段在查询结果中出现的个数（不包含null），
   计算表中有多少记录？ count(*) ,count(1) 可以， count(具体字段) 由于不包含null不准确

• count(1)，count(),count(字段) 的效率问题？
  如果使用的是MyISAM存储引擎，则三者效率相同， O（1）
  如果使用的是InnoDB存储引擎，则三者效率 count()=count >count(字段)

group by的使用

使用单个列进行分组

group by 字段
例如,根据部门ID分组
```
select prod_id , salary from Employee group by prod_id
```

根据多个列进行分组

group by 字段1，字段2
例如，根据部门和工种进行分组

select 部门，工种 ，salary from Employee group by 部门，工种

• 注意,

  • group by 部门，工种 与group by 工种 , 部门 结果相同 ，相当于54 与 45的区别、
    例如，5个部门，4个工种，
    group by 部门，工种 所有记录为 54（5个部门，每个部门4个工种），
    group by 工种，部门 所有记录为45

  • 使用group by 是为了对一组数据进行操作，也就是说select 出现的字段，必须是一组数据共有的，例如根据部门分组 展示的字段 可以是部门或一组数据操作的结果。但不能是一组数据各不相同的字段
    例如员工名字，一组数据只展示1条数据，1组有n个员工，每个员工名字都不相同没办法展示。会报错。

  • group by 的位置
    select * from table where group by order by limit

  • with rollup 的使用
    解释，group by pod_id with rollup 在group by 字段后 加 with rollup,
    会每组计算完之后，将整体（整张表看作是一组）进行操作并将数据展示在最后一行
    注意，当使用with rollup 时不能使用 order by ，这两个会冲突，
    主要是将整体看作一组和按字段划分的组不能在一起排序，不能将公司成绩和个人成绩排序，公司成绩排第一，员工1排第二 ，员工2 排第三 这不符合常理。

having 的使用

作用

• 过滤数据，例如，不使用 having 我的得到的是根据字段划分后全部组的数据，使用having 后，我会得到 部分组的数据，有一部分数据被过滤了，体现在表记录中就是，没有使用having得到90条数据，使用后得到10条数据，每一条是对应的每一组的数据

要求

• 聚合函数的条件判断必须要使用，having,
• 注意 有时where 和 having 结果相同 ,但是where 效率更高

sql 语法

sql92语法：

  select ....,....,.... from .... ,....,.... where 多表连接条件and 不包含聚合函数的连接条件  
  group by ....,....,....
  having 包含聚合函数的连接条件
  order by ..... (ASC/DESC)
  limit .......

sql99语法：

select ....,.....,... from .....(left/right/full/inner) join 
...... on 多表连接条件
where 不包含聚合函数的过滤条件
group by ....,....,....
having 包含聚合函数的过滤条件
order by ...,...,... (ASC/DESC)
limit .....

sql语句的执行过程（进理解为主）：

from .....,.....,...
 -> on ......   
 -> left/join/full /inner  
  -> where
   -> group by 
   ->  having 
   ->  select 
   -> distinct 
   -> order by 
   -.>limit

• 知道上述执行过程可以知道一下：
  （1）前项引用，例如，在select 定义别名 可以在order by 中使用 ，不能在where中使用 。
  注意，不要被子查询混淆，例如select 别名，（子查询，整个可以使用别名,定义别名后必须使用别名）
  （2）where 与 having的执行效率问题
  主要是，where 会将数据过滤掉，不用进行分组，和聚合函数的操作
  having是分组完成之后，在过滤