MySQL-分组查询与分组函数

雨点打透心脏的1/2处 2023-10-08 21:41 131阅读 0赞

分组查询与分组函数

  • 分组查询
      • 1.简单的分组
      • 2.可以实现分组前的筛选
      • 3.分组后筛选
      • 4.添加排序
      • 5.按多个字段分组
  • 分组函数
      • 1.简单的使用
      • 2.参数支持哪些类型
      • 3.是否忽略null
      • 4.和distinct搭配
      • 5.count函数的详细介绍
      • 6.count()和count(1)效率比较
      • 7.和分组函数一同查询的字段有限制

分组查询

特点:

  • 和分组函数一同查询的字段必须是group by后出现的字段

  • 筛选分为两类：分组前筛选和分组后筛选

    • 分组前筛选 原始表 group by前 where
    • 分组后筛选 group by后的结果集 group by后 having
  • 分组可以按单个字段也可以按多个字段
  • 可以搭配着排序使用

使用方法:
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

可能的疑问:
问题1：分组函数做筛选能不能放在where后面
答：不能
问题2：where——group by——having
答：一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

1.简单的分组

案例1：查询每个工种的员工平均工资

  1. SELECT AVG(salary),job_id
  2. FROM employees
  3. GROUP BY job_id;

案例2：查询每个位置的部门个数

  1. SELECT COUNT(*),location_id
  2. FROM departments
  3. GROUP BY location_id;

2.可以实现分组前的筛选

案例1：查询邮箱中包含a字符的 每个部门的最高工资

  1. SELECT MAX(salary),department_id
  2. FROM employees
  3. WHERE email LIKE '%a%'
  4. GROUP BY department_id;

案例2：查询有奖金的每个领导手下员工的平均工资

  1. SELECT AVG(salary),manager_id
  2. FROM employees
  3. WHERE commission_pct IS NOT NULL
  4. GROUP BY manager_id;

3.分组后筛选

案例：查询哪个部门的员工个数>5

  1. #1.查询每个部门的员工个数
  2. SELECT COUNT(*),department_id
  3. FROM employees
  4. GROUP BY department_id;
  5. #2.筛选刚才①结果
  6. SELECT COUNT(*),department_id
  7. FROM employees
  8. GROUP BY department_id
  9. HAVING COUNT(*)>5;

案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

  1. SELECT job_id,MAX(salary)
  2. FROM employees
  3. WHERE commission_pct IS NOT NULL
  4. GROUP BY job_id
  5. HAVING MAX(salary)>12000;

案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

  1. # 拆分思想
  2. SELECT MIN(salary),manager_id 
  3. FROM employees
  4. WHERE manager_id>102
  5. GROUP BY manager_id
  6. HAVING MIN(salary)>5000;

4.添加排序

案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

  1. SELECT job_id,MAX(salary) m
  2. FROM employees
  3. WHERE commission_pct IS NOT NULL
  4. GROUP BY job_id
  5. HAVING m>6000
  6. ORDER BY m ;

5.按多个字段分组

案例：查询每个工种每个部门的最低工资,并按最低工资降序

  1. SELECT MIN(salary),job_id,department_id
  2. FROM employees
  3. GROUP BY department_id,job_id
  4. ORDER BY MIN(salary) DESC;

分组函数

功能: 用作统计使用，又称为聚合函数或统计函数或组函数

特点:

  • sum、avg一般用于处理数值型，max、min、count可以处理任何类型
  • 以上分组函数都忽略null值
  • 可以和distinct搭配实现去重的运算
  • count函数的单独介绍，一般使用count(*)用作统计行数
  • 和分组函数一同查询的字段要求是group by后的字段

1.简单的使用

  1. SELECT SUM(salary) FROM employees;
  2. SELECT AVG(salary) FROM employees;
  3. SELECT MIN(salary) FROM employees;
  4. SELECT MAX(salary) FROM employees;
  5. SELECT COUNT(salary) FROM employees;
  7. SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
  8. FROM employees;
  10. SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
  11. FROM employees;

2.参数支持哪些类型

  1. SELECT SUM(last_name) ,AVG(last_name) FROM employees;
  2. SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
  3. SELECT MAX(last_name),MIN(last_name) FROM employees;
  4. SELECT MAX(hiredate),MIN(hiredate) FROM employees;
  5. SELECT COUNT(commission_pct) FROM employees;
  6. SELECT COUNT(last_name) FROM employees;

3.是否忽略null

  1. SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
  3. SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
  5. SELECT COUNT(commission_pct) FROM employees;
  6. SELECT commission_pct FROM employees;

4.和distinct搭配

  1. SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
  2. SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

5.count函数的详细介绍

  1. SELECT COUNT(salary) FROM employees;
  2. SELECT COUNT(*) FROM employees;
  3. SELECT COUNT(1) FROM employees;

6.count()和count(1)效率比较

效率：
MYISAM存储引擎下 ，COUNT()的效率高
INNODB存储引擎下，COUNT()和COUNT(1)的效率差不多，比COUNT(字段)要高一些

7.和分组函数一同查询的字段有限制

  1. SELECT AVG(salary),employee_id  FROM employees;

发表评论

表情：
评论列表 （有 0 条评论，131人围观）

还没有评论，来说两句吧...

相关阅读