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

本文将深入探讨 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.pswp.cn/bicheng/83209.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

现代 CSS 高阶技巧：实现平滑内凹圆角的工程化实践

现代 CSS 高阶技巧：实现平滑内凹圆角的工程化实践

通过 数学计算 CSS mask 复合遮罩 实现的真正几何内凹效果： 背景是一张图片，用来证明中间的凹陷是透明的。 完整代码： app.js import FormPage from "./pages/formPage"; import "./App.css"; const App () > {re…
阅读更多...
Qt不同布局添加不同控件

Qt不同布局添加不同控件

对于这种 不同布局添加不同控件 的情况，可以采用以下几种简化方法： 方法 1：使用 std::pair 或 std::tuple 配对（C++17 推荐） for (auto [layout, widget] : {std::pair{m_layoutMistakeCalibrate,
阅读更多...
MySQL 事务解析

MySQL 事务解析

1. 事务简介 事务（Transaction） 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。 经典案例&#xff1…
阅读更多...
PyTorch中 torch.utils.data.DataLoader 的详细解析和读取点云数据示例

PyTorch中 torch.utils.data.DataLoader 的详细解析和读取点云数据示例

一、DataLoader 是什么？ torch.utils.data.DataLoader 是 PyTorch 中用于加载数据的核心接口，它支持： 批量读取（batch）数据打乱（shuffle）多线程并行加载（num_workers）自…
阅读更多...
在MDK中自动部署LVGL，在stm32f407ZGT6移植LVGL-8.4，运行demo，显示label

在MDK中自动部署LVGL，在stm32f407ZGT6移植LVGL-8.4，运行demo，显示label

在MDK中自动部署LVGL，在stm32f407ZGT6移植LVGL-8.4 一、硬件平台二、实现功能三、移植步骤1、下载LVGL-8.42、MDK中安装LVGL-8.43、配置RTE4、配置头文件 lv_conf_cmsis.h5、配置lv_port_disp_template 四、添加心跳相关文件1、在STM32CubeMX中配置TIM7的参数2、使能…
阅读更多...
德思特新闻 | 德思特与es:saar正式建立合作伙伴关系

德思特新闻 | 德思特与es:saar正式建立合作伙伴关系

德思特新闻 2025年5月9日，德思特科技有限公司（以下简称“德思特”）与德国嵌入式系统专家es:saar GmbH正式达成合作伙伴关系。此次合作旨在将 es:saar 的先进嵌入式开发与测试工具引入中国及亚太市场，助力本地客户提升产品开发效率…
阅读更多...
fork函数小解

fork函数小解

学了好久终于搞懂fork函数的一些作用 1. fork函数作用：用于创建新的子进程 这是fork最根本的功能，在父进程里创建新的子进程、 但是创建新的子进程之后呢？ 子进程和父进程的关系是什么样的？ 为什么fork得到的子进程返回值为0&am…
阅读更多...
opencv(C++) 变换图像与形态学操作

opencv(C++) 变换图像与形态学操作

文章目录 使用腐蚀和膨胀图像形态滤波器实现案例使用形态学滤波器对图像进行开运算和闭运算实现案例在灰度图像上应用形态学操作算子形态学梯度（Morphological Gradient）黑帽变换（Black-hat Transform）使用分水岭算法进行图像分割使用 MSER 提取显著区域MSER 检测与可视化使…
阅读更多...
测试工程师学LangChain之promptTemplate 实战笔记

测试工程师学LangChain之promptTemplate 实战笔记

一、引言：大模型时代的测试自动化革命 2025 年，随着大模型（如 DeepSeek）在自动化测试领域的广泛应用，Prompt 编写已成为测试工程师的核心技能之一。 为什么？ 大模型输出的质量 90% 取决于输入的 PromptLangChain 的 PromptTemplate 提供了参数化 Prompt 的标准化方案Ope…
阅读更多...
CP2K 软件介绍与使用指南

CP2K 软件介绍与使用指南

CP2K 软件介绍与使用指南 一、CP2K简介 CP2K是一款开源的量子化学和固态物理模拟软件包，主要用于原子尺度模拟，特别擅长以下领域： 第一性原理计算：基于密度泛函理论(DFT)的电子结构计算分子动力学(MD)：包括从头算分…
阅读更多...
npm、pnpm、yarn使用以及区别

npm、pnpm、yarn使用以及区别

npm 使用 安装包&#xff1a;在项目目录下&#xff0c;npm install <包名> 用于本地安装包到 node_modules 目录&#xff0c;并添加到 package.json 的 dependencies 中&#xff1b;npm install -g <包名> 用于全局安装&#xff0c;适用于命令行工具等。初始化项目…
阅读更多...
2025年北京市职工职业技能大赛第六届信息通信行业网络安全技能大赛复赛CTF部分WP-哥斯拉流量分析

2025年北京市职工职业技能大赛第六届信息通信行业网络安全技能大赛复赛CTF部分WP-哥斯拉流量分析

2025年北京市职工职业技能大赛第六届信息通信行业网络安全技能大赛复赛CTF部分WP-哥斯拉流量分析 一、流量分析 题目没有任何提示，附件gzl.pcap 解题哥斯拉流量300多KB包很多，没啥经验只能挨个看回来之后又狠狠得撸了一把哥斯拉流量分析我这里用的是哥斯拉4.0.1 测试链接…
阅读更多...
GitLab 18.0 正式发布，15.0 将不再受技术支持，须升级【六】

GitLab 18.0 正式发布，15.0 将不再受技术支持，须升级【六】

GitLab 是一个全球知名的一体化 DevOps 平台&#xff0c;很多人都通过私有化部署 GitLab 来进行源代码托管。极狐GitLab 是 GitLab 在中国的发行版&#xff0c;专门为中国程序员服务。可以一键式部署极狐GitLab。 学习极狐GitLab 的相关资料&#xff1a; 极狐GitLab 官网极狐…
阅读更多...
React 项目中封装 Excel 导入导出组件：技术分享与实践

React 项目中封装 Excel 导入导出组件：技术分享与实践

文章目录 前言一、为什么需要封装 Excel 组件&#xff1f;二、技术选型三、核心实现1. 安装依赖2. 封装Excel导出3. 封装导入组件 &#xff08;UploadExcel&#xff09; 总结 前言 在 React 项目中&#xff0c;处理 Excel 文件的导入和导出是常见的业务需求。无论是导出报表数…
阅读更多...
RustDesk 搭建自建服务器并设置服务自启动

RustDesk 搭建自建服务器并设置服务自启动

目录 0. 介绍 1. 事前准备 1.1 有公网 ip 的云服务器一台 1.2 服务端部署包 1.3 客户端安装包 2. 部署 2.1 服务器环境准备 2.2 上传服务端部署包 2.3 运行 pm2 3. 客户端使用 3.1 安装 3.2 配置 3.2.1 解锁网络设置 3.2.2 ID / 中级服务器 3.3 启动效果 > …
阅读更多...
基于Qt封装数据库基本增删改查操作，支持多线程，并实现SQLite数据库单例访问

基于Qt封装数据库基本增删改查操作，支持多线程，并实现SQLite数据库单例访问

抽出来的&#xff0c;直接用就行 头文件CPP文件使用示例 头文件 #ifndef DATABASECOMMON_H #define DATABASECOMMON_H/** 单例封装SQLite通用操作&#xff0c;支持多线程调用&#xff1b;可扩展兼容其他数据库&#xff0c;照着SysRunDatabase写&#xff0c;并且重载openDataba…
阅读更多...
AI笔记 - 网络模型 - mobileNet

AI笔记 - 网络模型 - mobileNet

网络模型 mobileNet mobileNet V1网络结构深度可分离卷积空间可分![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/aff06377feac40b787cfc882be7c6e5d.png) 参考 mobileNet V1 网络结构 MobileNetV1可以理解为VGG中的标准卷积层换成深度可分离卷积 可分离卷积主要有…
阅读更多...
第十五篇：MySQL 高级实战项目：构建高可用、可观测、性能优化一体化数据库平台

第十五篇：MySQL 高级实战项目：构建高可用、可观测、性能优化一体化数据库平台

本篇聚焦于如何基于 MySQL 构建一个真正面向生产环境的数据库平台&#xff0c;集成高可用、可观测与性能调优三大核心能力&#xff0c;助力稳定、可扩展的系统运行。 一、项目背景与目标 在实际生产环境中&#xff0c;数据库系统需要应对以下挑战&#xff1a; 业务高速增长带来…
阅读更多...
华为OD机试真题——文件目录大小（2025 A卷：100分）Java/python/JavaScript/C++/C语言/GO六种语言最佳实现

华为OD机试真题——文件目录大小（2025 A卷：100分）Java/python/JavaScript/C++/C语言/GO六种语言最佳实现

2025 A卷 100分 题型 本文涵盖详细的问题分析、解题思路、代码实现、代码详解、测试用例以及综合分析； 并提供Java、python、JavaScript、C++、C语言、GO六种语言的最佳实现方式！ 2025华为OD真题目录+全流程解析/备考攻略/经验分享 华为OD机试真题《文件目录大小》： 目录 题…
阅读更多...
qwen 2.5 并行计算机制：依靠 PyTorch 和 Transformers 库的分布式能力

qwen 2.5 并行计算机制：依靠 PyTorch 和 Transformers 库的分布式能力

qwen 2.5 并行计算机制：依靠 PyTorch 和 Transformers 库的分布式能力 完整可运行代码： import torch import torch.nn.functional as F from transformers
阅读更多...
最新文章

Copyright @ 2022~2023