SQL 查询：SELECT 与聚合

第 2 章 · SELECT、过滤、排序与聚合查询

本章目标：掌握 SELECT 投影与别名；WHERE 条件（比较、逻辑、IN、BETWEEN、LIKE、NULL）；ORDER BY 与 LIMIT 分页；聚合函数 COUNT/SUM/AVG/MIN/MAX；GROUP BY 分组与 HAVING 过滤分组；常用字符串/日期/数值函数；在 shop_db 样本表上完成运营报表 SQL；对照 spring-boot-web 分页与 python-database ch02 平行结构。

学时建议：4～5 小时（含 2.5 小时跟练）

前置：完成 java-database ch01；MySQL 容器 shop-db-mysql 已运行。

2.1 场景说明：运营要看「已上架商品统计」

运营同学通过虚构后台 https://api.example.com/admin 查看数据，底层即对 shop_db 的 SQL。本章先在 mysql 客户端手工建练习用样本表并插入数据（ch05 会改为规范 DDL + 外键）。

典型需求：

全站字段回顾：products.slug UNIQUE、is_published、price 整数分；users.email、password_hash。

2.2 准备样本数据（本章专用）

在 mysql 客户端执行（教学简化表，无完整外键）：

USE shop_db;

DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  slug VARCHAR(64) NOT NULL UNIQUE,
  title VARCHAR(200) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price INT NOT NULL COMMENT '单位：分',
  is_published TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  total_amount INT NOT NULL DEFAULT 0 COMMENT '分',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_lines (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  unit_price INT NOT NULL COMMENT '下单时单价，分'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO products (slug, title, category, price, is_published) VALUES
('java-concurrency', 'Java 并发实战', 'books', 4599, 1),
('spring-boot-guide', 'Spring Boot 3 指南', 'books', 5299, 1),
('kotlin-handbook', 'Kotlin 入门', 'books', 3999, 0),
('usb-c-cable', 'USB-C 数据线 1m', 'accessories', 1999, 1),
('mech-keyboard', '机械键盘 K87', 'accessories', 39900, 1),
('shop-mug', '贤紫优选马克杯', 'accessories', 5900, 1);

INSERT INTO users (email, password_hash) VALUES
('user-demo@example.com', '$2a$12$demo_hash_not_real'),
('ops@example.com', '$2a$12$ops_hash_not_real');

INSERT INTO orders (user_id, status, total_amount, created_at) VALUES
(1, 'paid', 9898, '2026-08-01 10:00:00'),
(1, 'pending', 1999, '2026-08-15 14:30:00'),
(2, 'paid', 45800, '2026-08-18 09:00:00');

INSERT INTO order_lines (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 4599),
(1, 2, 1, 5299),
(2, 4, 1, 1999),
(3, 5, 1, 39900),
(3, 6, 1, 5900);

SELECT COUNT(*) AS product_count FROM products;

2.3 SELECT 基础与列别名

-- 查询所有列（生产避免 SELECT *，明确列名利于索引与 JPA）
SELECT id, slug, title, price, is_published
FROM products;

-- 列别名 AS（AS 可省略）
SELECT
  slug,
  title,
  price AS price_cents,
  price / 100.0 AS price_yuan
FROM products
WHERE is_published = 1;

-- 去重
SELECT DISTINCT category FROM products;

-- 常量列
SELECT slug, 'CNY' AS currency, price FROM products LIMIT 3;

对照 spring-boot-web：@Query("SELECT p.slug, p.title, p.price FROM Product p WHERE p.isPublished = true") 等价于指定列 SELECT。

2.4 WHERE 条件表达式

-- 比较
SELECT slug, price FROM products WHERE price >= 3000;

-- 逻辑 AND OR NOT
SELECT slug, title FROM products
WHERE is_published = 1 AND category = 'books';

SELECT slug FROM products
WHERE category = 'books' OR category = 'accessories';

-- IN / NOT IN
SELECT slug FROM products
WHERE category IN ('books', 'accessories');

-- BETWEEN（含边界）
SELECT slug, price FROM products
WHERE price BETWEEN 2000 AND 6000;

-- LIKE 模糊（% 任意长度，_ 单字符）
SELECT slug, title FROM products
WHERE title LIKE '%实战%';

-- NULL 必须用 IS NULL / IS NOT NULL
SELECT slug FROM products WHERE slug IS NOT NULL;

布尔字段：MySQL 用 TINYINT(1)，is_published = 1 表真。与 spring-boot-web Boolean isPublished 对应。

常见 API 映射（虚构 GET /api/v1/products?category=books&min_price=3000）：

SELECT slug, title, price, category
FROM products
WHERE is_published = 1
  AND category = 'books'
  AND price >= 3000
ORDER BY price ASC;

2.5 ORDER BY 与 LIMIT 分页

-- 单字段排序
SELECT slug, price FROM products
WHERE is_published = 1
ORDER BY price DESC;

-- 多字段：先 category 升序，再 price 降序
SELECT slug, category, price FROM products
ORDER BY category ASC, price DESC;

-- 分页：第 1 页，每页 2 条（OFFSET 从 0 开始）
SELECT slug, title, price FROM products
WHERE is_published = 1
ORDER BY id ASC
LIMIT 2 OFFSET 0;

-- 第 2 页
SELECT slug, title, price FROM products
WHERE is_published = 1
ORDER BY id ASC
LIMIT 2 OFFSET 2;

性能提示：大 OFFSET 深分页慢（需扫描跳过行）；生产可用 keyset pagination（WHERE id > ? ORDER BY id LIMIT n，spring-boot-web Pageable 选修）。

2.6 聚合函数

-- 全表聚合（无 GROUP BY 时 SELECT 只能有聚合列或常量）
SELECT
  COUNT(*) AS total_products,
  COUNT(CASE WHEN is_published = 1 THEN 1 END) AS published_count,
  SUM(price) AS sum_price_cents,
  AVG(price) AS avg_price_cents,
  MIN(price) AS min_price,
  MAX(price) AS max_price
FROM products;

-- 忽略 NULL：COUNT(col) 不统计 NULL 行
SELECT COUNT(slug), COUNT(category) FROM products;