第08章_聚合函数

1. 聚合函数介绍

什么是聚合函数
聚合函数作用于一组数据，并对一组数据返回一个值

• 聚合函数类型
• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()

1.1 AVG和SUM函数

可以对数值型数据使用AVG 和 SUM 函数

#1.1 AVG/ SUM
SELECT AVG(salary),SUM(salary),AVG(salary)*107
FROM employees;     
#如下的操作没有意义
SELECT SUM(last_name),AVG(last_name)
FROM employees;

 

1.2 MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

#1.2 MAX/MIN:二者适用于数值类型，字符串类型，日期时间类型
SELECT MAX(salary),TRUNCATE(MIN(salary),0),MAX(hire_date)
FROM employees;

SELECT MAX(last_name)
FROM employees;

SELECT MIN(last_name)
FROM employees;

1.3 COUNT函数

• COUNT(*)返回表中记录总数，适用于任意数据类型。

  #1.3 COUNT :
  SELECT *FROM employees;
  #作用：计算指定的字段在查询结构中出现的个数(不能出现空值)
  SELECT COUNT(employee_id),COUNT(salary),COUNT(2*salary),COUNT(1)
  FROM employees;
  #如果计算表中有多少条记录
  #方式1：COUNT(*)
  #方式2：COUNT(1)
  #方式3: COUNT(具体的字段)：不一定对，那个字段中不为空的个数
   SELECT COUNT(commission_pct)
   FROM  employees;

  问题：用count( * )，count(1)，count(列名)谁好呢?
  其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

  Innodb引擎的表用count(* ),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名）

• 问题：能不能使用count(列名)替换count( * )?
  不要使用 count(列名)来替代 count(* ) ， count(* ) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
  说明：count(* )会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
   

2. GROUP BY

2.1 基本使用 

#查询各个部门的平均工资,最高工资
SELECT department_id,AVG(salary),MAX(salary)
FROM employees
GROUP BY  department_id;
 
#查询job_id 的平均工资
SELECT  job_id,AVG(salary)
FROM employees
GROUP BY  job_id;



#错误的！
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY  department_id;

#结论1：select出现的函数字段必须出现在group by 中
#        而group by 中出现的字段可以不在select 中       

#结论2；group by 声明在，from 之后，where 之后，order by 之前，limit 之前

#结论3：MYSQL 使用WITH ROLLUP，不能同时进行使用，order 无法对组和整体进行排序

 2.2 使用多个列分组

#查询各个departmen_id,job_id的工资
SELECT  department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

 2.2 使用with rollup

#这里with rollup 计算的是整体的平均
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;


#说明：with rollup 和 order by 不能同时进行使用，			
SELECT department_id,AVG(salary) a
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY  a ;

3. HAVING

3.1 基本使用

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

   练习：各个部门中最高工资比10000高的部门信息
   
   #错误的！
   SELECT  department_id,MAX(salary)
   FROM employees
   WHERE  MAX(salary)>10000
   GROUP BY department_id;
   
   #要求1:如果过滤条件使用了聚合函数，必须使用HAVING来替换WHERE
   #要求2：HAVING 必须使用在ORDER BY 的后面
   #要求3：HAVING 必须和GROUP BY 一起使用，不能单独使用
   SELECT  department_id,MAX(salary)
   FROM employees
   GROUP BY department_id
   HAVING  MAX(salary)>10000;

   3.2 WHERE和HAVING的对比

#结论：当过滤条件有聚合函数时，声明在HAVING
#      当过滤条件没有聚合函数时，建议使用WHERE

#where 和having的区别：
#1.having的使用范围更加广
#2.过滤条件没有聚合函数时，建议使用WHER，运行效率高

 4. SELECT的执行过程

4.1 查询的结构

#4.1 
# SQL92的语法
 SELECT ..... (存在符合函数)
 FROM .......
 WHERE 多表的连接条件 AND 过滤条件不包含聚合函数
 GROUP BY ....
 HAVING  过滤条件包含聚合函数
 ORDER BY ....(ASC,DESC)
 LIMIT .....;
 
#SQL99的语法
 SELECT ..... (存在符合函数)
 FROM .... (LEFT/RIGHT)JOIN ... ON 多表的连接条件
 (LEFT/RIGHT)JOIN ... ON 多表的连接条件
 WHERE 多表的连接条件 AND 过滤条件不包含聚合函数
 GROUP BY ....
 HAVING  过滤条件包含聚合函数
 ORDER BY ....(ASC,DESC)
 LIMIT .....;
 

4.2 SELECT执行顺序

4.2SQL的执行过程
#FROM  ...,...---->ON-->(LEFT/RIGHT JOIN) --->WHERE ->GROUP BY-->HAVING->
#  SELECT->DISTINCT-> 
#  ORDER BY ->LIMIT

4.3 SQL 的执行原理

SELECT ...
FROM  ...
(LEFT/RIGHT) JOIN ...on
(LEFT/RIGHT) JOIN ...on
WHERE .....
GROUP BY ...
HAVING  ....
ORDER BY  ..... ASC/DESC
LIMIT .....;

课后练习

#第08章_聚合函数练习题
【题目】
SELECT * FROM employees;
SELECT * FROM departments;
#1.where子句可否使用组函数进行过滤? 
   NO
#2.查询公司员工工资的最大值，最小值，平均值，总和
   SELECT  MAX(salary),MIN(salary),AVG(salary),SUM(salary)
   FROM    employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
      SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
      FROM  employees
      GROUP BY  job_id;
#4.选择具有各个job_id的员工人数
      SELECT job_id,COUNT(*)
      FROM   employees
      GROUP BY  job_id;
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
     SELECT  MAX(salary)-MIN(salary) DIFFERENCE
     FROM   employees; 
# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
     SELECT  manager_id,MIN(salary)
     FROM    employees
     WHERE   manager_id IS NOT NULL
     GROUP BY  manager_id
     HAVING   MIN(salary)>=6000;
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
    SELECT   d.department_name,location_id,COUNT(employee_id),
    AVG(salary)
    FROM  employees e RIGHT JOIN  departments d
    ON  e.`department_id`=d.`department_id`
   
    GROUP BY  d.department_id,d.location_id
    ORDER BY   AVG(salary) DESC;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
    SELECT d.department_name,job_id,MIN(salary)
    FROM  employees e RIGHT JOIN departments d
    ON   e.`department_id`=d.`department_id`
    GROUP BY department_name,job_id;