MySQL 查询进阶：WHERE、ORDER BY、GROUP BY

查询是数据库最常用的操作，掌握进阶技巧能让你的 SQL 功力大增！

先准备数据：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer VARCHAR(50),
    product VARCHAR(100),
    category VARCHAR(20),
    price DECIMAL(10,2),
    quantity INT,
    order_date DATE,
    city VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO orders (customer, product, category, price, quantity, order_date, city) VALUES
('张三', '笔记本电脑', '电子产品', 5999.00, 1, '2026-01-15', '北京'),
('李四', '手机', '电子产品', 3999.00, 2, '2026-01-20', '上海'),
('王五', '《MySQL入门》', '图书', 59.00, 3, '2026-02-01', '北京'),
('张三', '鼠标', '电子产品', 99.00, 5, '2026-02-10', '北京'),
('赵六', '手机', '电子产品', 3999.00, 1, '2026-02-15', '广州'),
('李四', '《SQL进阶》', '图书', 79.00, 2, '2026-03-01', '上海'),
('王五', '键盘', '电子产品', 299.00, 1, '2026-03-10', '北京'),
('张三', '显示器', '电子产品', 1999.00, 1, '2026-03-20', '北京');

一、WHERE 条件详解

比较运算符

-- 大于
SELECT * FROM orders WHERE price > 1000;

-- 不等于
SELECT * FROM orders WHERE category != '图书';

-- 范围
SELECT * FROM orders WHERE price BETWEEN 50 AND 500;

逻辑运算 AND / OR / NOT

-- AND：同时满足
SELECT * FROM orders 
WHERE category = '电子产品' AND price > 1000;

-- OR：满足任一
SELECT * FROM orders 
WHERE city = '北京' OR city = '上海';

-- 组合（注意括号！）
SELECT * FROM orders 
WHERE (city = '北京' OR city = '上海') 
  AND price > 1000;

IN 和 NOT IN

SELECT * FROM orders WHERE city IN ('北京', '广州');
SELECT * FROM orders WHERE category NOT IN ('图书');

LIKE 模糊匹配

-- % 匹配任意多个字符
SELECT * FROM orders WHERE product LIKE '%手机%';
SELECT * FROM orders WHERE customer LIKE '张%';

-- _ 匹配单个字符
SELECT * FROM orders WHERE customer LIKE '张_';

IS NULL 处理空值

SELECT * FROM orders WHERE product IS NULL;
-- 注意：不能用 = NULL，必须用 IS NULL

二、ORDER BY 排序

基本排序

-- 单字段升序
SELECT * FROM orders ORDER BY price;

-- 单字段降序
SELECT * FROM orders ORDER BY price DESC;

-- 多字段排序
SELECT * FROM orders 
ORDER BY category ASC, price DESC;

基于表达式的排序

-- 按总价排序
SELECT *, price * quantity AS total 
FROM orders 
ORDER BY total DESC;

三、GROUP BY 分组聚合

常用聚合函数

基础分组

-- 每个城市有多少订单
SELECT city, COUNT(*) AS order_count
FROM orders
GROUP BY city;

-- 每个品类的销售总额
SELECT category, SUM(price * quantity) AS total_sales
FROM orders
GROUP BY category;

-- 每个客户的平均消费
SELECT customer, AVG(price * quantity) AS avg_spend
FROM orders
GROUP BY customer;

GROUP BY + 多字段

SELECT city, category, COUNT(*) AS cnt
FROM orders
GROUP BY city, category;

四、HAVING 过滤分组

WHERE 过滤行，HAVING 过滤分组

-- 找出总消费超过 5000 的客户
SELECT customer, SUM(price * quantity) AS total
FROM orders
GROUP BY customer
HAVING total > 5000;

-- 找出订单数大于等于2的城市
SELECT city, COUNT(*) AS cnt
FROM orders
GROUP BY city
HAVING cnt >= 2;

-- WHERE + GROUP BY + HAVING 完整组合
SELECT customer, SUM(price * quantity) AS total
FROM orders
WHERE category = '电子产品'       -- 先过滤行
GROUP BY customer
HAVING total > 3000               -- 再过滤分组
ORDER BY total DESC;              -- 最后排序

五、DISTINCT 去重

-- 查看有哪些城市
SELECT DISTINCT city FROM orders;

-- 查看有哪些品类
SELECT DISTINCT category FROM orders;

-- 组合去重
SELECT DISTINCT city, category FROM orders;

六、综合实战

-- 1. 各品类销量排名
SELECT category, SUM(quantity) AS total_qty,
       SUM(price * quantity) AS total_revenue
FROM orders
GROUP BY category
ORDER BY total_revenue DESC;

-- 2. 北京客户最爱买什么
SELECT category, COUNT(*) AS cnt
FROM orders
WHERE city = '北京'
GROUP BY category
ORDER BY cnt DESC;

-- 3. 月度销售趋势
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*) AS orders,
       SUM(price * quantity) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

-- 4. 找出回头客（多次消费的客户）
SELECT customer, COUNT(*) AS times,
       SUM(price * quantity) AS total
FROM orders
GROUP BY customer
HAVING times > 1
ORDER BY total DESC;

七、SQL 语句的执行顺序

理解执行顺序是写好 SQL 的关键：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

SELECT customer, SUM(price * quantity) AS total  -- 5. 选择列
FROM orders                                        -- 1. 确定表
WHERE category = '电子产品'                       -- 2. 过滤行
GROUP BY customer                                  -- 3. 分组
HAVING total > 3000                                -- 4. 过滤分组
ORDER BY total DESC                                -- 6. 排序
LIMIT 5;                                           -- 7. 限制条数

本篇小结

√ 掌握了 WHERE 的各种条件写法
√ 学会了 ORDER BY 排序技巧
√ 理解了 GROUP BY 分组聚合的强大功能
√ 知道了 HAVING 和 WHERE 的区别
√ 记住了 SQL 执行顺序

下一篇我们将学习 JOIN 多表联合查询！