MySQL入门(六)分组查询

本文数据集下载链接:https://pan.baidu.com/s/1sVcSXfVZimc6ruyMr4Gr3g ,提取码:7gfa

一、分组函数简介

GROUP BY 语句根据一个或多个列对结果集进行分组,在分组的列上我们可以使用 COUNT, SUM, AVG等函数。

功能:用于统计,又称为聚合函数或统计函数或组函数

分类sum求和,avg平均值,max最大值,min最小值,count计算个数

特点

  1. SUM, AVG一般处理数值型, MAX, MIN, COUNT可以处理任何类型
  2. 是否忽略NULL值:所有分组函数都忽略NULL
  3. 可以和DISTINCT搭配实现去重运算
  4. COUNT函数的专门介绍

1. 简单使用

1
2
3
4
5
6
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和, ROUND(AVG(salary), 2) 平均, MAX(salary) 最高, MIN(salary) 最低, COUNT(salary) FROM employees;

2. 参数类型

以下为无意义但不报错的使用方式:

1
2
SELECT SUM(last_name), AVG(last_name) 
FROM employees;

同理还有对日期求和等,也是无意义的。以下使用方式是合理的:

1
2
SELECT MAX(last_name), MIN(last_name) 
FROM employees;

这是因为可以按照首字母排序,于是也有最大值和最小值,同理对日期求最大最小值也是可以的

也可以使用COUNT语句对其他类型求和:

1
2
3
SELECT COUNT(commission_pct), COUNT(last_name) 
FROM employees;
# 返回35和107

两者不同是因为COUNT返回 不为NULL的个数

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

3. COUNT函数详解

统计总行数

1
2
SELECT COUNT(*) 
FROM employees;

每一行中只要有不为NULL的计数器就加一,同理也可以使用以下方式统计总行数:

1
2
SELECT COUNT(1) 
FROM employees;

事实上,COUNT()中的常量值可以取任何值获得同样的效果,但就效率而言

  • 在MYISAM存储引擎下,COUNT(*)的效率高
  • 在INNODB存储引擎下,COUNT(*)的效率和COUNT(1)差不多,但比COUNT(字段)效率高

因此一般使用COUNT(*)来统计行数

注意:和分组函数一同查询的字段有限制(要求是group by后的字段)

二、分组查询

  1. 筛选条件可分为两类:分组前筛选和分组后筛选

    | | 数据源 | 位置 | 关键字 |
    | ——————— | ——————— | —————————— | ————— |
    | 分组前筛选 | 原始表 | GROUP BY子句的前面 | WHERE |
    | 分组后筛选 | 分组后的结果集 | GROUP BY子句的后面 | HAVING |

  2. GROUP BY子句支持单个字段分组、多个字段分组(多个字段之间用逗号隔开,没有顺序要求)

  3. 也可以添加排序(放在整个分组查询最后)

注:

  1. 分组函数做条件肯定是放在HAVING子句中
  2. 能用分组前筛选的优先使用分组前筛选

1. GROUP BY语法

1
2
3
4
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

2. 具体示例

查询每个部门的平均工资

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

查询每个工种有奖金的员工的最高工资

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

根据上一题查询结果筛选最高工资>12000

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

查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资

1
2
3
4
5
SELECT MIN(salary), manager_id 
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000

按表达式(函数)分组:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些

① 查询每个长度的员工个数

1
2
3
SELECT COUNT(*), LENGTH(last_name) len_name 
FROM employees
GROUP BY LENGTH(last_name);

② 添加筛选条件

1
2
3
4
SELECT COUNT(*), LENGTH(last_name) len_name 
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5;

按多个字段分组

案例:查询每个部门每个工种的员工的平均工资

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

添加排序

案例:查询部门编号不为NULL的每个工种的员工的平均工资,并按工资高低显示

1
2
3
4
5
SELECT AVG(salary), department_id, job_id 
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id, job_id
ORDER BY AVG(salary) DESC;
  • 版权声明: 本博客所有文章除特别声明外,著作权归作者所有。转载请注明出处!

扫一扫,分享到微信

微信分享二维码
  • Copyrights © 2020-2021 chenk
  • 由 帅气的CK本尊 强力驱动
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信