第04章06节-查询DQL-聚合函数&分组查询

聚合函数&分组查询

一、分组函数（聚集函数）

分组函数的执行原则：先分组，然后对每一组数据执行分组函数。如果没有分组语句group by的话，整张表的数据自成一组。
分组函数包括五个：

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

分组函数属于多行处理函数。多个输入，一个输出。

1.1 max

找出员工的最高薪资

1
select max(sal) from emp;

1.2 min

找出员工的最低工资

1
select min(sal) from emp;

1.3 avg

计算员工的平均薪资

1
select avg(sal) from emp;

1.4 sum

计算员工的工资和

1
select sum(sal) from emp;

计算员工的津贴之和

1
select sum(comm) from emp;

[!TIP]
重点：所有的分组函数都是自动忽略NULL的。

1.5 count

统计员工人数

1
2
3
select count(ename) from emp;
select count(*) from emp;
select count(1) from emp;

count(*)和count(1)的效果一样，统计该组中总记录行数。
count(ename)统计的是这个ename字段中不为NULL个数总和。
例如：count(comm) 结果是 4，而不是14

1
select count(comm) from emp;

统计岗位数量

1
select count(distinct job) from emp;

[!TIP]
总结：

  1. COUNT(*) 用于计算表中行的总数，而 COUNT(某个字段) 用于计算某个特定字段中非 NULL 的值的数量。

  2. count(*)和count(1)的效果一样，统计该组中总记录行数。

1.6 分组函数组合使用

1
select count(*) as Count,sum(sal) as Sum,min(sal) as Min ,max(sal) as Max,avg(sal) as Avg from emp;

1.7 分组函数注意事项

==分组函数不能直接使用在where子句当中==

1
select ename,job from emp where sal > avg(sal);

这个会报错的
原因：分组的行为是在where执行之后才开始的。

二、分组查询

2.1 group by

按照某个字段分组，或者按照某些字段联合分组。

注意：group by的执行是在where之后执行。
语法：
group by 字段
group by 字段1,字段2,字段3....

找出每个岗位的平均薪资

1
select job, avg(sal) from emp group by job;

找出每个部门最高工资

1
select deptno,max(sal) from emp group by deptno;

找出每个部门不同岗位的平均薪资

1
select deptno,job,avg(sal) from emp group by deptno,job;

当select语句中有group by的话，select后面只能跟分组函数或参加分组的字段

1
select ename,deptno,avg(sal) from emp group by deptno; // 这个SQL执行后会报错。
image.png

2.2 having

having写在group by的后面，而且不能单独使用。当你对分组之后的数据不满意，可以继续通过having对分组之后的数据进行过滤。
where的过滤是在分组前进行过滤。
使用原则：尽量在where中过滤，实在不行，再使用having。越早过滤效率越高。

[!CAUTION]

WHERE子句中是不能使用聚集函数作为表达式的，聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句

找出除20部分之外，其它部门的平均薪资。

1
2
select deptno,avg(sal) from emp where deptno<>20 group by deptno; // 建议
select deptno,avg(sal) from emp group by deptno having deptno <> 20; // 不建议

查询每个部门平均薪资，找出平均薪资高于2000的。

1
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

三、组内排序

3.1 GROUP_CONCAT

GROUP_CONCAT 是 MySQL 中的一个聚合函数，用于将分组中的多个值连接成一个字符串。它在进行数据汇总时特别有用，比如将某一列的多个值连接成一个以逗号分隔的字符串。

3.1.1 基本语法

1
2
3
4
SELECT GROUP_CONCAT(column_name)
FROM table_name
[WHERE condition]
[GROUP BY column_name2];

3.1.2 示例

假设有一个名为 students 的表，结构和数据如下：

id name course
1 Alice Math
2 Bob Science
3 Alice History
4 Bob Math
5 Alice Science

我们想要得到每个学生所选课程的一个列表。可以使用 GROUP_CONCAT 来实现：

1
2
3
SELECT name, GROUP_CONCAT(course) AS courses
FROM students
GROUP BY name;
结果
name courses
Alice Math,History,Science
Bob Science,Math

3.1.3 可选参数

GROUP_CONCAT 函数还有一些可选参数可以用来改变其行为：

  1. **SEPARATOR**：指定分隔符，默认为逗号 ,。

    1
    2
    3
    SELECT name, GROUP_CONCAT(course SEPARATOR ' | ') AS courses
    FROM students
    GROUP BY name;

  2. **DISTINCT**：去重，防止重复值。

    1
    2
    3
    SELECT name, GROUP_CONCAT(DISTINCT course) AS courses
    FROM students
    GROUP BY name;

  3. **ORDER BY**：指定连接值的排序方式。

    1
    2
    3
    SELECT name, GROUP_CONCAT(course ORDER BY course ASC) AS courses
    FROM students
    GROUP BY name;

  4. **LIMIT**：限制结果的长度（不常用）。

3.1.4 综合示例

1
2
3
SELECT name, GROUP_CONCAT(DISTINCT course ORDER BY course ASC SEPARATOR ' | ') AS courses
FROM students
GROUP BY name;
结果
name courses
Alice History
Bob Math

通过以上方式，GROUP_CONCAT 可以灵活地将多行数据合并成一个字符串，满足各种需求。

3.2 SUBSTRING_INDEX

SUBSTRING_INDEX 是 MySQL 中用于截取字符串的函数，可以根据指定的分隔符将字符串拆分并返回特定部分。该函数非常适用于需要从字符串中提取特定子字符串的场景。

3.2.1 基本语法

1
SUBSTRING_INDEX(str, delim, count)
  • **str**：要处理的字符串。
  • **delim**：字符串中的分隔符。
  • **count**：指定要返回的部分。正数表示从左到右，负数表示从右到左。

3.2.2 示例

假设我们有一个表 users，其中有一列 email 存储用户的电子邮件地址：

id email
1 john.doe@example.com
2 jane.smith@test.com
3 alice@domain.org

我们想要从电子邮件地址中提取用户名（即 @ 符号前的部分）。可以使用 SUBSTRING_INDEX 实现：

1
2
SELECT email, SUBSTRING_INDEX(email, '@', 1) AS username
FROM users;
结果
email username
john.doe@example.com john.doe
jane.smith@test.com jane.smith
alice@domain.org alice

3.2.3 详细示例

  1. 从字符串中提取域名（@ 符号后的部分）：

    1
    2
    SELECT email, SUBSTRING_INDEX(email, '@', -1) AS domain
    FROM users;

    结果：

    email domain
    john.doe@example.com example.com
    jane.smith@test.com test.com
    alice@domain.org domain.org

  2. 从URL中提取协议（假设表中有一列 url）：

    1
    2
    SELECT url, SUBSTRING_INDEX(url, '://', 1) AS protocol
    FROM websites;

    如果 websites 表如下：

    id url
    1 https://www.example.com
    2 http://test.com
    3 ftp://files.domain.org

    结果：

    url protocol
    https://www.example.com https
    http://test.com http
    ftp://files.domain.org ftp

  3. 提取某个字符串中的第一个单词：

    1
    2
    SELECT sentence, SUBSTRING_INDEX(sentence, ' ', 1) AS first_word
    FROM phrases;

    如果 phrases 表如下：

    id sentence
    1 Hello world!
    2 OpenAI develops AI models
    3 SQL is fun

    结果：

    sentence first_word
    Hello world! Hello
    OpenAI develops AI models OpenAI
    SQL is fun SQL

3.2.4 使用注意事项

  • 如果 count 为正数，则从字符串左边开始计数。
  • 如果 count 为负数，则从字符串右边开始计数。
  • 如果 count 超出分隔符的数量，则返回整个字符串。

SUBSTRING_INDEX 是处理字符串的强大工具，通过合理设置分隔符和计数，可以方便地提取出所需的子字符串。

3.3 组内查询案例

案例：找出每个工作岗位的工资排名在前两名的。

1
2
3
# 找出每个工作岗位的工资排名在前两名的。
SELECT SUBSTRING_INDEX(GROUP_CONCAT(SAL ORDER BY SAL ASC ),',',2)  FROM EMP GROUP BY JOB;

substring_index函数的使用：

image.png

group_concat函数的使用：

image.png

学习了这两个函数之后，自己可以尝试写出来吗？

四、总结单表的DQL语句

select …5
from …1
where …2
group by …3
having …4
order by …6
重点掌握一个完整的DQL语句执行顺序。