mysql --聚合函数的学习

聚合函数
1. 常见的聚合函数
        1.1 AVG /SUM：只适用于数值类型的字段（或变量）
        1.2 MAX/MIN：适用于数值类型、字符串类型、时间日期类型的字段（或变量）
        1.3 COUNT 
        1.3.1 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的）
        #如果计算表中有多少条记录，如何实现？
        #方式1：COUNT(*)
        #方式2：COUNT(1)
        #方式3：COUNT(具体字段) : 不一定对！
        1.3.2 注意：计算指定字段出现的个数时，是不计算null值的。
        SELECT count(commission_pct)
        FROM employees;

        SELECT commission_pct
        FROM employees
        WHERE commission_pct IS NOT NULL;
        1.3.3 公式：avg = sum / count 
        公司平均奖金率
        SELECT avg(ifnull(commission_pct,0))
        FROM employees;

2. GROUP BY 的使用 
        查询部门的平均工资
        SELECT department_id,avg(salary)
        FROM employees
        GROUP BY department_id 
        
        SELECT job_id,department_id,AVG(salary)
    FROM employees
    GROUP BY job_id,department_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中GROUP BY中使用WITH ROLLUP
        SELECT department_id,avg(salary) avg_sal
        FROM employees
        GROUP BY department_id with ROLLUP
        #需求： 查询各个部门的平均工资，按照平均工资的升序排列
        SELECT department_id,avg(salary) avg_sal
        FROM employees
        GROUP BY department_id 
        ORDER BY avg_sal ASC
        
3. HAVING 的使用，作用是用来过滤数据的
要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
要求2：HAVING 必须声明在 GROUP BY 的后面。
        #练习查询各个部门中最高工资比10000高的部门 
        SELECT department_id, max(salary) max_sal
        FROM employees
        GROUP BY department_id
        HAVING max_sal > 10000
要求3： 开发中，我们使用having 的前提是SQL 中使用了group by 
        #练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
        方法1:
        SELECT department_id, max(salary) max_salary
        FROM employees
        WHERE department_id in (10,20,30,40)
        GROUP BY department_id
        having max_salary > 10000
        
        方法2:
        SELECT department_id, max(salary) max_sal
        FROM employees
        GROUP BY department_id
        HAVING max_sal > 10000 and department_id in (10,20,30,40);
#结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
#当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

/*
  WHERE 与 HAVING 的对比
1. 从适用范围上来讲，HAVING的适用范围更广。 
2. 如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING
*/

#4. SQL底层执行原理
#4.1 SELECT 语句的完整结构
/*

#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 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....


*/

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


#1.where子句可否使用组函数进行过滤? 
no
#2.查询公司员工工资的最大值，最小值，平均值，总和
select max(salary) max_sal, min(salary) min_sal, avg(salary) avg_sal, sum(salary) sum_sal
FROM employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
select job_id,max(salary) max_sal, min(salary) min_sal, avg(salary) avg_sal, sum(salary) sum_sal
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 
GROUP BY manager_id
HAVING min(salary) > 6000 and manager_id is not NULL;
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT department_name, location_id , count(employee_id),avg(salary) avg_sal
FROM employees RIGHT JOIN departments
ON employees.`department_id`=departments.`department_id`
GROUP BY department_name, location_id
ORDER BY avg_sal DESC;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT 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