当前位置： 首页 > news >正文

MySQL高级查询技巧：分组、聚合、子查询与分页【MySQL系列】

news 2025/6/8 4:08:13

本文将深入探讨 MySQL 高级查询技巧，重点讲解 GROUP BY、HAVING、各种聚合函数、子查询以及分页查询（LIMIT 语法）的使用。文章内容涵盖实际应用中最常见的报表需求和分页实现技巧，适合有一定 SQL 基础的开发者进一步提升技能。

一、前置知识回顾

在进入高级部分之前，我们先简要回顾一些 SQL 查询的基本组成部分，便于后续内容的理解：

SELECT [字段列表]
FROM [表名]
WHERE [条件]
GROUP BY [分组字段]
HAVING [聚合条件]
ORDER BY [排序字段]
LIMIT [偏移量, 行数]

二、GROUP BY 分组查询

2.1 基本语法

GROUP BY 用于将查询结果按某个或某些字段进行分组。配合聚合函数（如 COUNT()、SUM()、AVG() 等）使用，可以实现对每个分组的统计。

示例：统计每个部门的员工数量

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

2.2 多字段分组

示例：统计每个部门中每个职位的员工数量

SELECT department_id, job_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id, job_id;

多字段分组适用于需要“交叉”维度分析的场景，比如不同区域+不同产品的销售统计。

三、聚合函数详解

聚合函数用于对一组数据进行计算，常用于 GROUP BY 分组后。

函数说明
COUNT()统计数量
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
GROUP_CONCAT()将组内字段连接为字符串

3.1 COUNT()

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

3.2 SUM()

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

3.3 GROUP_CONCAT()

SELECT department_id, GROUP_CONCAT(first_name) AS employee_names
FROM employees
GROUP BY department_id;

GROUP_CONCAT() 在报表中经常用于“拼接多个名称为一列”，如列出参与某个项目的所有人名。

四、HAVING：对分组后的结果进行过滤

4.1 区别 WHERE 与 HAVING

  • WHERE 是对 原始数据 进行筛选
  • HAVING 是对 分组后的结果 进行筛选

4.2 示例：只显示员工数大于5的部门

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

4.3 使用别名

虽然在 SELECT 中定义了别名 employee_count，但在 HAVING 中引用聚合函数更安全。

HAVING COUNT(*) > 5 -- 推荐
-- HAVING employee_count > 5 -- 有些版本不支持

五、子查询的多种用法

子查询是指嵌套在主查询内部的 SELECT 查询。可以出现在 SELECT、FROM、WHERE 等多个位置。

5.1 SELECT 中的子查询

示例：查询每位员工的平均工资差值

SELECT employee_id, salary,salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

5.2 WHERE 中的子查询

示例：查询工资高于公司平均值的员工

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

5.3 FROM 中的子查询（内联视图）

用于将子查询临时当作一个“表”来使用。

SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id
) AS dept_avg
WHERE avg_salary > 10000;

5.4 IN/NOT IN 子查询

示例：查找至少有一位员工的部门

SELECT department_id, department_name
FROM departments
WHERE department_id IN (SELECT DISTINCT department_id FROM employees
);

六、分页查询（LIMIT）详解

在构建分页接口或展示数据列表时，LIMIT 是非常关键的 SQL 工具。

6.1 LIMIT 基本用法

SELECT * FROM employees
LIMIT 10; -- 取前10条

6.2 LIMIT + OFFSET 用法

SELECT * FROM employees
LIMIT 10 OFFSET 20; -- 从第21条开始，取10条

等价写法：

SELECT * FROM employees
LIMIT 20, 10;

6.3 用于分页接口的实现

-- page = 3, pageSize = 10
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 20, 10;

分页核心逻辑：LIMIT (page - 1) * pageSize, pageSize

七、常见报表需求实践

以下为结合 GROUP BY、聚合函数、子查询与分页的常见报表查询场景。

7.1 部门月度工资支出报表

SELECT department_id, DATE_FORMAT(hire_date, '%Y-%m') AS month,SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, month
ORDER BY department_id, month;

7.2 Top N 查询（工资最高的前3名员工）

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

7.3 每个部门工资最高的员工（相关子查询）

SELECT *
FROM employees e
WHERE salary = (SELECT MAX(salary)FROM employeesWHERE department_id = e.department_id
);

八、分页优化技巧

分页性能在大数据量下成为瓶颈，以下是常见优化方法。

8.1 使用覆盖索引加速分页

-- 仅查询主键或索引字段
SELECT employee_id
FROM employees
ORDER BY hire_date DESC
LIMIT 100000, 10;

8.2 延迟关联分页

-- 第一步：查主键
SELECT employee_id
FROM employees
ORDER BY hire_date DESC
LIMIT 100000, 10;-- 第二步：再查详情
SELECT * 
FROM employees
WHERE employee_id IN (…);

8.3 使用 ID 游标分页（适合不断增长的主键）

-- 假设上次最后一条ID是 120
SELECT * FROM employees
WHERE employee_id > 120
ORDER BY employee_id
LIMIT 10;

九、总结

技巧应用场景
GROUP BY数据分组统计、分类汇总
聚合函数报表、指标计算（如总数、平均值等）
HAVING分组结果过滤
子查询复杂条件过滤、嵌套数据分析
LIMIT分页列表、Top N 取值
分页优化大数据分页响应慢时的优化方案

实战建议：

  1. 分组前过滤用 WHERE，分组后过滤用 HAVING
  2. 复杂统计尽量使用子查询或视图，保持主查询简洁
  3. 分页查询在大数据场景下需优化 LIMIT 的性能
  4. GROUP_CONCAT 适合小量数据展示，不宜用于大表

本项目适用于后台管理系统、电商用户中心、SaaS 用户模块等场景，特别适合开发者进行实战演练与面试准备。

一、项目背景与需求概述

我们将构建一个基础版的用户管理系统，具备以下业务功能：

  • 用户注册与登录
  • 用户角色与权限分配
  • 日志记录与用户状态追踪
  • 多条件用户查询与分页

涉及的核心业务对象包括：用户、角色、权限、日志等。

二、数据库建模与表结构设计

2.1 实体关系图（ER图）简要说明

  • 一位用户可以拥有多个角色（多对多）
  • 一个角色可以拥有多个权限（多对多）
  • 用户与登录日志是一对多关系

2.2 用户表（users）

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,password VARCHAR(100) NOT NULL,email VARCHAR(100),status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用',created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.3 角色表（roles）

CREATE TABLE roles (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,description VARCHAR(255)
);

2.4 权限表（permissions）

CREATE TABLE permissions (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view'
);

2.5 用户-角色关联表（user_role）

CREATE TABLE user_role (user_id INT,role_id INT,PRIMARY KEY (user_id, role_id),FOREIGN KEY (user_id) REFERENCES users(id),FOREIGN KEY (role_id) REFERENCES roles(id)
);

2.6 角色-权限关联表（role_permission）

CREATE TABLE role_permission (role_id INT,permission_id INT,PRIMARY KEY (role_id, permission_id),FOREIGN KEY (role_id) REFERENCES roles(id),FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

2.7 登录日志表（login_logs）

CREATE TABLE login_logs (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,ip_address VARCHAR(45),login_time DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
);

三、数据初始化脚本

3.1 插入初始角色与权限

INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户');INSERT INTO permissions(name, code) VALUES
('查看用户', 'user:view'),
('新增用户', 'user:create'),
('删除用户', 'user:delete');-- 分配权限给角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 拥有全部权限
(2, 1);                 -- user 仅能查看用户

3.2 插入测试用户

INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 为管理员
(2, 2); -- bob 为普通用户

四、典型查询场景实现

4.1 查询所有启用用户及其角色

SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

4.2 查询某用户拥有的所有权限

SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';

4.3 查询最近7天登录日志

SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;

4.4 用户分页查询（带关键字搜索）

SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;

五、事务控制与一致性保障

在角色授权或用户注册等业务流程中，可以使用事务来确保数据完整性。

5.1 注册用户 + 分配默认角色（事务）

START TRANSACTION;INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色COMMIT;

5.2 授权失败时回滚

START TRANSACTION;-- 假设某权限不存在导致失败
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);-- 失败时回滚
ROLLBACK;

六、索引优化与执行分析

6.1 建议加索引字段

  • users.username：用于登录验证、搜索
  • login_logs.user_id：日志查询
  • user_role.user_id / role_permission.role_id：JOIN 优化
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);

6.2 执行计划分析

EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;

可查看索引是否使用、JOIN 类型、Rows 扫描数量等。

更多推荐【MySQL完整系列】：MySQL数据库从0到拿捏系列

  1. MySQL数据库零基础入门教程：从安装配置到数据查询全掌握
    关键词：安装、登录、客户端、库表基础、简单查询

  2. MySQL数据表操作全指南：建表、修改、删除一步到位
    关键词：DDL语句、字段类型、主键/外键、约束、规范设计
    聚焦表结构的创建和维护，配合真实业务建表案例（如用户表、订单表）。

  3. MySQL增删改查基础教程：熟练掌握DML语句操作
    关键词：INSERT、UPDATE、DELETE、SELECT、WHERE、ORDER BY
    实战演练日常的数据库操作命令，重点讲解查询语句的条件与排序。

  4. MySQL高级查询技巧：分组、聚合、子查询与分页
    关键词：GROUP BY、HAVING、聚合函数、LIMIT、子查询
    向中级进阶，涵盖常见报表需求与分页列表的查询实现。

  5. MySQL多表查询详解：内连接、外连接、自连接通通搞懂
    关键词：JOIN、INNER JOIN、LEFT JOIN、UNION、自连接
    深度讲解表与表之间如何通过字段建立关联并进行数据整合。

  6. MySQL索引与性能优化入门：让查询提速的秘密武器
    关键词：索引原理、EXPLAIN、慢查询、查询优化
    开启性能优化之路，适合准备应对数据量增长或面试的人。

  7. MySQL事务与锁机制详解：确保数据一致性的关键
    关键词：事务四大特性、锁类型、死锁案例、隔离级别
    涉及电商、支付系统等对数据一致性要求高的业务场景。

  8. MySQL项目实战演练：搭建用户管理系统的完整数据库结构
    关键词：业务建模、表关系设计、数据初始化、查询场景
    以实战带动知识回顾，模拟真实业务项目，整合前面所学内容。

http://www.xdnf.cn/news/746281.html

相关文章：

  • 深入理解CSS常规流布局
  • 【系统架构设计师】第一章 计算机硬件 1.1 计算机硬件 - CPU - 校验码
  • Unity 模拟高度尺系统开发详解——实现拖动、范围限制、碰撞吸附与本地坐标轴选择
  • Linux基本指令/下
  • 信息安全之为什么引入公钥密码
  • Linux系统下安装配置 Nginx
  • AUTOSAR图解==＞AUTOSAR_EXP_AIADASAndVMC
  • 数组题解——最大子数组和​【LeetCode】
  • 机器学习算法04：SVC 算法(向量机分类)
  • Fastapi 学习使用
  • [GHCTF 2025]SQL???
  • 23种设计模式概览
  • ubuntu20.04.5--arm64版上使用node集成java
  • Ubuntu22.04通过命令行安装qt5
  • FPGA纯verilog实现MIPI-DSI视频编码输出，提供工程源码和技术支持
  • Redis7底层数据结构解析
  • [VMM]虚拟地址到物理地址的三级或四级页表查找过程详解
  • 4000万日订单背后，饿了么再掀即时零售的“效率革命”
  • win1011安装WinGet和Windows Terminal
  • CQF预备知识：一、微积分 -- 1.8 多变量函数：多元微积分详解
  • 离线安装 Python 包及其全部依赖
  • 谷歌Stitch：AI赋能UI设计，免费高效新利器
  • Vue2+Vuex通过数组动态生成store数据（分组模式）
  • 类FNAF游戏后续
  • Constraints and Triggers
  • 零基础一站式端游内存辅助编写教程（无密）
  • 进程间通信(信号量)
  • .net Avalonia 在centos部署
  • LeetCode 高频 SQL 50 题（基础版）之 【聚合函数】部分
  • 5.31 数学复习笔记 22