MySQL复习（三）分组函数，分组查询

分组函数

•     sum 求和
•     max 最大值
•     min 最小值
•     avg 平均值
•     count 计数

    特点：
    1、以上五个分组函数都忽略null值，除了count(*)
    2、sum和avg一般用于处理数值型
        max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用，用于统计去重后的结果
    4、count的参数可以支持：
        字段、*、常量值，一般放1

       建议使用 count(*)

分组查询

语法:
SELECT 分组函数,列(要求出现在group by 后面)
FROM 表
WHERE 筛选条件
GROUP BY 分组的列表
ORDER BY 子句

特点：

• 可以按单个字段分组
• 和分组函数一同查询的字段最好是分组后的字段
• 分组筛选
  •  针对的表            位置                      关键字
  • 分组前筛选：     原始表                   group by的前面        where
  • 分组后筛选：     分组后的结果集     group by的后面        having
• 可以按多个字段分组，字段之间用逗号隔开
• 可以支持排序
• having后可以支持别名

# 引入:查询每个部门的平均工资
# 结果:6461.682243
SELECT AVG(salary) FROM employees;
# 简单的分组查询
# 案例1: 查询每个工种的最高工资
# 结果:8300.00		AC_ACCOUNT (结果集)
SELECT MAX(salary),job_id FROM employees GROUP BY job_id
# 查询每个位置上的部门个数
# 结果:
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id

添加筛选条件

# 案例1: 查询邮箱中包含a字符的,每个部门的平均工资。
SELECT AVG(salary),department_id FROM employees  WHERE email LIKE '%a%' GROUP BY department_id
# 案例2: 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct is not null GROUP BY manager_id

添加复杂的筛选条件

# 案例1:查询那个部门的员工个数>2
# ①查询每个部门的员工个数
SELECT COUNT(*),department_id FROM employees GROUP BY department_id
# ②根据①的结果筛选,查询哪个部门的员工个数>2
#也就是说执行完group by 的结果后再进行筛选,查询哪个部门的员工个数>2
#这时就需要另外一个关键词,HAVING,放在GROUP BY 以后
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2
# 案例2:查询每个工种有奖金的员工的最高工资>12000
SELECT MAX(salary),job_id FROM employees WHERE commission_pct is not null GROUP BY job_id HAVING MAX(salary) >12000
# 案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资。
SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary) > 5000

GROUP BY 支持表达式和函数,也支持别名,但不通用,比如orcal 不支持GROUP BY 和HAVING 后面放别名

# 案例4:按员工姓名长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5
SELECT COUNT(*) c ,LENGTH(last_name) l FROM employees GROUP BY l HAVING c>5


# 按多个字段分组
# 案例4:查询每个部门每个工种的员工的平均工资。
SELECT  AVG( salary),department_id,job_id FROM employees GROUP BY department_id,job_id

# 添加排序
# 案例5:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示。
SELECT  AVG( salary) a,department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY  a desc