MYSQL笔记_聚合函数

注意：聚合函数只返回一个结果

1, 常见的聚合函数

    AVG() / SUM()平均值 / 总和

        SUM不计算NULL
        只适用于数值类型，

        例如：

        AVG(last_name)——>无意义操作    

---

    MAX / MIN最大 / 最小

        适用于数值类型，字符串类型，日期类型
 

        所以：

        MAX(last_name)——>有意义

---

    COUNT计算指定字段在查询结果中出现的个数,有多少行

        SELECT COUNT(employee_id),
        COUNT(1)，

        COUNT(*)——>有意义，相当于增加一个常量，行数不变
        FROM employees

    总结：

        A，COUNT(1),COUNT(*)——>一定对
              COUNT(具体字段)——>不一定对，因为COUNT不计算NULL

        B，AVG(salary) = SUM(salary)/COUNT(salary)

        C，应用：查询平均salary        

错误：SELECT AVG(salary)——>AVG没有计算工资为NULL的员工
           FROM employees;        

正确：SELECT SUM(salary)/COUNT(IFNULL(salary,0))

           或者

           AVG(IFNULL(salary,0))
           FROM employees;

---

 2, GROUP BY

        用法：调查各个office_id的平均工资

        SELECT office_id,AVG(salary)
        FROM employees
        GROUP BY office_id

        用法：查询各个office_id，job_title的平均工资

        SELECT office_id,AVG(salary),job_title
        FROM employees
        GROUP BY office_id,job_title
        注意：SELECT中出现的非函数组字段必须分组

---

 3, HAVING过滤分组后的数据

        a，过滤条件中使用了聚合函数，必须使用HAVING
        b，和groupby联结使用
        用法：查询各个office_id最高的salary，筛选大于80000的

        SELECT office_id,MAX(salary)
        FROM employees
        GROUP BY office_id
        HAVING MAX(salary) > 80000

        用法：查询office_id=1,2,3的最高的salary，筛选大于80000的

        SELECT office_id,MAX(salary)
        FROM employees
        WHERE office_id IN(1,2,3)效率更高
        GROUP BY office_id
        HAVING MAX(salary) > 80000 #AND office_id IN(1,2,3)

---

练习

1，查询各个office_id的salary最大值，最小值，平均值，总和

SELECT office_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY office_id

---

2,查询所有office的地址，office_id，员工数量和平均工资，按平均工资降序排序

SELECT E.'office_id',O.'address',E.'salary',SUM(1),AVG(1)
FROM employees AS E JOIN offices AS O
ON E.'office_id' = O.'office_id'
GROUP BY E.'office_id'
ORDER BY AVG (E.'salary') DESC