掌握MySQL高级查询，轻松挖掘数据价值

原创已于 2025-06-27 11:23:16 修改 · 681 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2025-05-23 11:27:24 首次发布

一、引言

在数据驱动的时代，高效地从数据库中提取信息至关重要。MySQL 作为流行的开源数据库管理系统，其高级查询功能为我们深入挖掘数据价值提供了强大的工具。本文将深入探讨 MySQL 高级查询的各个方面，助力你在数据处理上更上一层楼。

二、排序与分页

排序（ORDER BY）

ORDER BY 是实现结果集排序的关键子句。它支持按单个或多个字段排序，还能指定升序（ASC，默认）或降序（DESC）。

单字段排序：比如在 employees 表中，要按员工薪资降序排列，语句为：

SELECT * FROM employees ORDER BY salary DESC;

这样能快速找出高薪员工。

多字段排序：当有多个排序需求时，多字段排序就派上用场。例如，先按部门升序，再按薪资降序排列员工信息，可使用：
```
SELECT * FROM employees ORDER BY department ASC, salary DESC;
```
在这种情况下，MySQL 先按 department 字段排序，相同部门内再按 salary 字段排序。
分页（LIMIT）

对于大量数据的查询，分页必不可少，既能减少单次传输的数据量，又能提升查询性能和用户体验。LIMIT 子句用于限制返回的记录数，还能指定偏移量（从哪条记录开始）。
基本分页：若想获取 products 表中前 10 条产品记录，使用：
```
SELECT * FROM products LIMIT 10;
```
带偏移量分页：若要获取第 11 - 20 条记录（即偏移量为 10 ），则是
```
SELECT * FROM products LIMIT 10, 10;
```
在实际应用中，常用于分页展示数据，如网站的商品列表分页

三、聚合与分组

聚合函数

聚合函数用于对一组数据进行计算，返回单个结果值。常见的聚合函数有：

COUNT()：用于统计记录数量。比如统计 orders 表中的订单总数，使用：
```
SELECT COUNT(*) FROM orders;
```
若想统计不同客户的数量，可使用：

SELECT COUNT(DISTINCT customer_id) FROM orders;

SUM()：计算数值字段的总和。在统计订单总金额时，以下语句就很有用：
```
SELECT SUM(total_amount) FROM orders;
```
AVG()：求平均值。例如计算员工的平均薪资，使用：
```
SELECT AVG(salary) FROM employees;
```
MAX() 和 MIN()：分别获取字段的最大值和最小值。查找产品的最高和最低价格，语句为：

SELECT MAX(price), MIN(price) FROM products;

分组（GROUP BY）

GROUP BY 子句将数据按指定字段分组，以便对每个组分别应用聚合函数。例如，想知道每个部门的员工平均薪资，可使用：

SELECT department, AVG(salary) FROM employees GROUP BY department;

MySQL 会先按 department 字段分组，再分别计算每个部门的平均薪资。

分组结果筛选（HAVING）

HAVING 用于对分组后的结果进行筛选，与 WHERE 不同，WHERE 是在分组前对行进行筛选，而 HAVING 是在分组并计算聚合函数后筛选。比如，要筛选出平均薪资大于 8000 的部门，语句为：

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 8000;

四、子查询

子查询是嵌套在其他查询中的查询，能解决复杂的查询需求。

单行子查询

返回单个值，常用于比较运算。例如，查询薪资高于公司平均薪资的员工，可先通过子查询算出平均薪资，再在主查询中筛选，语句为：

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

多行子查询

返回多个值，常与 IN、ANY、ALL 等操作符配合。比如，查询与特定部门（如销售部）员工薪资相同的其他部门员工，使用：

SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department = 'Sales') AND department != 'Sales';

五、窗口函数

窗口函数用于在结果集的 “窗口” 内进行计算，不改变结果集的行数和分组。语法为 函数名() OVER (PARTITION BY 列 ORDER BY 列) 。

排名函数：RANK()、DENSE_RANK()、ROW_NUMBER() 等。比如使用 RANK() 计算每个部门内员工薪资排名，使用：
```
SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank 
FROM employees;
```
聚合窗口函数：像 SUM()、AVG() 等聚合函数也可作为窗口函数。例如，计算每个员工薪资占所在部门薪资总和的比例，使用
```
SELECT employee_id, department_id, salary, salary / SUM(salary) OVER (PARTITION BY department_id) AS salary_ratio 
FROM employees;
```
六、集合运算

UNION

UNION 用于合并两个或多个查询结果集，并自动去除重复行。要求参与合并的查询结果列数和数据类型一致。例如，合并 customers 表和 suppliers 表中的城市列表，使用：
```
SELECT city FROM customers UNION SELECT city FROM suppliers;
```
若想保留重复行，可使用 UNION ALL ，它不会进行去重操作，性能相对更高。
INTERSECT 与 EXCEPT

INTERSECT 返回两个查询结果集的交集（部分数据库支持），如查询既在订单表又在退货表中的产品 ID，使用：
```
SELECT product_id FROM orders INTERSECT SELECT product_id FROM returns;
```
EXCEPT 返回在第一个查询结果集中但不在第二个查询结果集中的记录（部分数据库支持），例如查询未退货的产品 ID，使用：
```
SELECT product_id FROM orders EXCEPT SELECT product_id FROM returns;
```
七、高级过滤技巧

NULL 值处理

使用 IS NULL 或 IS NOT NULL 来过滤包含 NULL 值的记录。比如，查询没有分配经理的员工，使用：
```
SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL; FROM employees WHERE manager_id IS NULL;
```
BETWEEN 操作符

用于查询某个范围内的值。例如，查询价格在 100 - 200 之间的产品，使用：
```
SELECT * FROM products WHERE price BETWEEN 100 AND 200;
```
IN 操作符

可替代多个 OR 条件。比如，查询部门 ID 为 1、2、3 的员工，使用：
```
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
```
LIKE 操作符的高级用法

结合通配符 %（匹配任意字符序列）和 _（匹配单个字符）进行模糊查询。如查询以 “A” 开头且长度为 5 的产品名称，使用：
```
SELECT * FROM products WHERE product_name LIKE 'A____';
```
八、实际案例与性能优化

实际案例

假设在电商系统中，要查询每个月销量最高的产品及其销量。可先按月份和产品分组统计销量，再使用窗口函数找出每个月销量最高的产品，具体语句为：

WITH MonthlySales AS (
    SELECT 
        MONTH(order_date) AS month,
        product_id,
        SUM(quantity) AS total_sales
    FROM orders 
    GROUP BY MONTH(order_date), product_id
)
SELECT 
    month,
    product_id,
    total_sales
FROM (
    SELECT 
        month,
        product_id,
        total_sales,
        RANK() OVER (PARTITION BY month ORDER BY total_sales DESC) AS sales_rank
    FROM MonthlySales
) RankedSales
WHERE sales_rank = 1;

性能优化
索引优化：为经常用于查询条件、排序和连接的字段创建索引，可显著提升查询性能。例如，在 orders 表的 customer_id 字段上创建索引，使用：

CREATE INDEX idx_customer_id ON orders(customer_id);

避免函数运算：尽量避免在 WHERE 子句中对字段进行函数运算，否则可能导致索引失效。比如，应避免 SELECT * FROM products WHERE YEAR(order_date) = 2023; ，可改为：

SELECT * FROM products WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

合理使用子查询与连接：子查询和连接都能实现复杂查询，但要根据实际情况选择更优方案。一般来说，简单的子查询可转化为连接操作，性能可能更好。

九、结语

MySQL 高级查询功能丰富多样，掌握这些技巧能让我们在数据处理和分析中更加得心应手。从基本的排序分页，到复杂的窗口函数和集合运算，每一项都有其独特的应用场景。在实际项目中，不断实践并结合性能优化策略，就能充分发挥 MySQL 的强大功能，为业务决策提供有力的数据支持。

希望通过本文的分享，你能对 MySQL 高级查询有更深入的理解和掌握，在数据的海洋中畅游自如，挖掘出更多有价值的信息。

掌握MySQL高级查询，轻松挖掘数据价值

一、引言

二、排序与分页

排序（ORDER BY）

分页（LIMIT）

三、聚合与分组

聚合函数

分组（GROUP BY）

分组结果筛选（HAVING）

四、子查询

单行子查询

多行子查询

相关子查询

五、窗口函数

六、集合运算

UNION

INTERSECT 与 EXCEPT

七、高级过滤技巧

NULL 值处理

BETWEEN 操作符

IN 操作符

LIKE 操作符的高级用法

八、实际案例与性能优化

实际案例

性能优化

九、结语