50. MySQL 高级查询

单表查询不够用了。真实业务里，数据分散在多张表中，需要 JOIN 关联；结果集太大，需要分页；还有排序、聚合统计等需求。本章把这些高级查询逐个拆解。

WHERE 条件

条件查询不只是等号：

// 范围
await pool.execute('SELECT * FROM products WHERE price BETWEEN ? AND ?', [10, 100]);

// IN 列表
await pool.execute('SELECT * FROM users WHERE status IN (?, ?)', ['active', 'vip']);

// LIKE 模糊查询（% 通配符）
await pool.execute('SELECT * FROM users WHERE name LIKE ?', ['%Alice%']);

// 多条件组合
await pool.execute(
  'SELECT * FROM orders WHERE status = ? AND created_at > ?',
  ['paid', '2024-01-01']
);

ORDER BY 排序

// 按价格升序
await pool.execute('SELECT * FROM products ORDER BY price ASC');

// 按创建时间倒序，最新的在前
await pool.execute('SELECT * FROM posts ORDER BY created_at DESC');

// 多字段排序：先按状态、再按时间
await pool.execute('SELECT * FROM tasks ORDER BY status ASC, deadline ASC');

LIMIT 分页

MySQL 用 LIMIT offset, count 做分页：

async function getPage(page = 1, size = 10) {
  const offset = (page - 1) * size;
  const [rows] = await pool.execute(
    'SELECT * FROM users ORDER BY id DESC LIMIT ?, ?',
    [offset, size]
  );
  return rows;
}

分页时通常还要查总条数：

const [[{ total }]] = await pool.execute('SELECT COUNT(*) AS total FROM users');

封装一个通用的分页响应：

async function paginate(table, page = 1, size = 10, orderBy = 'id DESC') {
  const offset = (page - 1) * size;
  const [rows] = await pool.execute(
    `SELECT * FROM ${table} ORDER BY ${orderBy} LIMIT ?, ?`,
    [offset, size]
  );
  const [[{ total }]] = await pool.execute(`SELECT COUNT(*) AS total FROM ${table}`);
  return { data: rows, pagination: { page, size, total } };
}

JOIN 多表关联

假设有两张表：users（用户）和 orders（订单），通过 user_id 关联。

INNER JOIN：只返回有匹配的记录

const [rows] = await pool.execute(`
  SELECT orders.id, orders.amount, users.name
  FROM orders
  INNER JOIN users ON orders.user_id = users.id
  WHERE orders.status = ?
`, ['paid']);

LEFT JOIN：返回左表全部，右表没匹配填 NULL

const [rows] = await pool.execute(`
  SELECT users.name, COUNT(orders.id) AS order_count
  FROM users
  LEFT JOIN orders ON users.id = orders.user_id
  GROUP BY users.id
`);

自关联：查用户的推荐人

SELECT u.name, r.name AS referrer_name
FROM users u
LEFT JOIN users r ON u.referrer_id = r.id

聚合函数

// 统计总金额
const [[{ total }]] = await pool.execute(
  'SELECT SUM(amount) AS total FROM orders WHERE status = ?',
  ['paid']
);

// 平均价格
const [[{ avg }]] = await pool.execute('SELECT AVG(price) AS avg FROM products');

// 分组统计每个用户的订单数
const [rows] = await pool.execute(`
  SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
  HAVING cnt >= 2
`);

HAVING 和 WHERE 的区别：WHERE 过滤原始行，HAVING 过滤聚合后的组。

子查询

// 查下过单的用户
const [rows] = await pool.execute(`
  SELECT * FROM users
  WHERE id IN (SELECT DISTINCT user_id FROM orders)
`);

子查询可读性好，但性能往往不如 JOIN。MySQL 8 以后可以用 CTE（公用表表达式）写得更清晰：

WITH active_users AS (
  SELECT DISTINCT user_id FROM orders
)
SELECT * FROM users WHERE id IN (SELECT user_id FROM active_users);

索引提示

查询慢的时候，用 EXPLAIN 看看执行计划：

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

如果 type 列是 ALL，说明在扫全表。给 email 加索引：

CREATE INDEX idx_email ON users(email);

索引不是越多越好，写操作（INSERT/UPDATE/DELETE）每次都要维护索引，会拖慢写入速度。只在频繁查询、很少修改的字段上加。