MySQL DQL 高频查询速查表

DQL（Data Query Language，数据查询语言）主要指 SELECT。本文以 MySQL 8.0 的用户、商品和订单业务为例，覆盖列表、搜索、关联、聚合、分页和窗口函数等日常高频查询。

1. 基础查询

1.1 查询指定字段

SELECT id, username, email
FROM users;

业务代码中优先明确字段，不要习惯性使用 SELECT *：

• 减少不必要的数据传输。
• 表新增字段后不会意外改变接口输出。
• 字段来源和返回结构更清楚。

1.2 字段和表别名

SELECT
  u.id AS user_id,
  u.username AS user_name
FROM users AS u;

1.3 去重

SELECT DISTINCT status
FROM orders;

多个字段一起出现时，DISTINCT 对整个字段组合去重：

SELECT DISTINCT user_id, status
FROM orders;

2. WHERE 条件查询

2.1 比较和逻辑条件

SELECT id, name, price, stock
FROM products
WHERE status = 1
  AND price >= 100
  AND stock > 0;

常用比较运算符：

=  <>  !=  >  >=  <  <=

AND 优先级高于 OR，混用时使用括号：

SELECT id, username, status
FROM users
WHERE deleted_at IS NULL
  AND (status = 1 OR email = 'admin@example.com');

2.2 IN 和 NOT IN

SELECT id, order_no, status
FROM orders
WHERE status IN ('paid', 'shipped');

NOT IN 列表或子查询中存在 NULL 时可能导致结果不符合直觉。排除关联数据时通常优先使用 NOT EXISTS。

2.3 BETWEEN

SELECT id, name, price
FROM products
WHERE price BETWEEN 100 AND 500;

BETWEEN 包含两个边界。时间范围更推荐左闭右开：

WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at < '2026-09-01 00:00:00'

2.4 NULL 判断

SELECT id, username
FROM users
WHERE phone IS NULL;

SELECT id, username
FROM users
WHERE phone IS NOT NULL;

不能写 phone = NULL。NULL 表示未知值，必须使用 IS NULL 或 IS NOT NULL。

2.5 EXISTS

查询至少有一个已支付订单的用户：

SELECT u.id, u.username
FROM users AS u
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
    AND o.status = 'paid'
);

只关心“有没有”时，EXISTS 比先计算完整数量更符合语义。

查询从未下单的用户：

SELECT u.id, u.username
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
);

3. 中文搜索和排序规则

3.1 前缀和包含查询

-- 以“机械”开头，有机会使用普通索引
SELECT id, name
FROM products
WHERE name LIKE '机械%';

-- 任意位置包含“机械”，通常不能用普通索引快速定位
SELECT id, name
FROM products
WHERE name LIKE '%机械%';

• % 匹配任意长度字符。
• _ 匹配一个字符。
• 转义用户输入中的 % 和 _，否则它们会被当作通配符。

3.2 大小写是否敏感

字符串比较行为由字段排序规则决定。默认 utf8mb4_0900_ai_ci 不区分英文大小写：

SELECT id, email
FROM users
WHERE email = 'USER@EXAMPLE.COM';

临时强制严格比较：

SELECT id, code
FROM invite_codes
WHERE code COLLATE utf8mb4_bin = 'AbC123';

高频严格比较字段应在 DDL 中直接选择合适排序规则，而不是每次查询临时转换。

3.3 中文排序和拼音排序

普通排序：

SELECT id, username
FROM users
ORDER BY username;

测试中文语言排序规则：

SELECT id, username
FROM users
ORDER BY username COLLATE utf8mb4_zh_0900_as_cs;

产品明确要求拼音顺序，尤其要处理多音字或业务自定义顺序时，推荐维护独立的拼音排序字段：

SELECT id, username
FROM users
ORDER BY username_pinyin, id;

3.4 中文全文搜索

普通 B-Tree 索引不能解决 LIKE '%关键词%' 的大规模中文全文搜索。可以评估 MySQL FULLTEXT 和 ngram 分词器：

ALTER TABLE articles
ADD FULLTEXT INDEX ft_articles_title_content (title, content)
WITH PARSER ngram;

SELECT
  id,
  title,
  MATCH(title, content) AGAINST('数据库优化') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('数据库优化')
ORDER BY score DESC;

实际效果受分词参数、停用词和数据规模影响。需要同义词、拼音、纠错和复杂相关度时，应评估专业搜索引擎。

4. 排序和分页

4.1 多字段稳定排序

SELECT id, order_no, created_at
FROM orders
ORDER BY created_at DESC, id DESC;

增加唯一或近似唯一的 id 作为最后一个排序条件，可以让相同时间的数据保持稳定顺序。

4.2 LIMIT 分页

-- 第 3 页，每页 20 条，offset = (3 - 1) * 20
SELECT id, order_no, total_amount, created_at
FROM orders
ORDER BY id DESC
LIMIT 20 OFFSET 40;

MySQL 也支持 LIMIT 40, 20，其中第一个数字是偏移量。LIMIT 20 OFFSET 40 更易读。

4.3 游标分页

深分页会扫描并丢弃大量前置记录。连续向后翻页时，可以用上一页最后一条记录的 ID：

SELECT id, order_no, total_amount, created_at
FROM orders
WHERE id < 10000
ORDER BY id DESC
LIMIT 20;

如果按时间和 ID 共同排序，游标条件也要匹配排序规则：

SELECT id, order_no, created_at
FROM orders
WHERE created_at < '2026-08-08 10:00:00'
   OR (created_at = '2026-08-08 10:00:00' AND id < 10000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

5. 聚合和分组

5.1 高频聚合函数

SELECT
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount,
  AVG(total_amount) AS average_amount,
  MAX(total_amount) AS maximum_amount,
  MIN(total_amount) AS minimum_amount
FROM orders
WHERE status = 'paid';

• COUNT(*) 统计行数。
• COUNT(column) 不统计该字段为 NULL 的行。
• 没有匹配行时，SUM() 可能返回 NULL。

SELECT COALESCE(SUM(total_amount), 0) AS total_amount
FROM orders
WHERE status = 'cancelled';

5.2 GROUP BY 和 HAVING

统计每位用户的已支付订单：

SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING COUNT(*) >= 2
ORDER BY total_amount DESC;

• WHERE 在分组前过滤原始行。
• HAVING 在分组后过滤聚合结果。
• 能放在 WHERE 的条件优先放在 WHERE，减少参与分组的数据。

5.3 按天和按月统计

SELECT
  DATE(created_at) AS order_date,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount
FROM orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at < '2026-09-01 00:00:00'
GROUP BY DATE(created_at)
ORDER BY order_date;

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS order_month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount
FROM orders
WHERE created_at >= '2026-01-01 00:00:00'
  AND created_at < '2027-01-01 00:00:00'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY order_month;

过滤条件不要写成 DATE(created_at) = ...，对索引字段套函数通常不利于范围索引定位。

6. 多表关联

6.1 INNER JOIN

只返回能够匹配用户的订单：

SELECT
  o.id,
  o.order_no,
  o.total_amount,
  u.username
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'paid';

6.2 LEFT JOIN

返回所有用户，包括没有订单的用户：

SELECT
  u.id,
  u.username,
  COUNT(o.id) AS paid_order_count
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
  AND o.status = 'paid'
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.username;

订单状态放在 ON 中，才能保留没有已支付订单的用户。如果写在 WHERE o.status = 'paid'，o 为 NULL 的行会被过滤，效果接近内连接。

6.3 一对多关联导致重复行

一个订单有多条明细时，关联后订单会出现多行：

SELECT o.id, o.order_no, oi.product_name
FROM orders AS o
INNER JOIN order_items AS oi ON oi.order_id = o.id;

如果只需要订单列表，不要用 DISTINCT 掩盖不必要的关联，应先确认是否真的需要明细字段。需要汇总时可以分组：

SELECT
  o.id,
  o.order_no,
  COUNT(oi.id) AS item_count,
  SUM(oi.quantity) AS total_quantity
FROM orders AS o
INNER JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY o.id, o.order_no;

7. 子查询、CTE 和窗口函数

7.1 标量子查询

SELECT
  u.id,
  u.username,
  (
    SELECT COUNT(*)
    FROM orders AS o
    WHERE o.user_id = u.id
  ) AS order_count
FROM users AS u;

数据量较大时，相关子查询可能重复执行，应对比 JOIN + GROUP BY 的执行计划。

7.2 CTE

WITH paid_orders AS (
  SELECT user_id, total_amount
  FROM orders
  WHERE status = 'paid'
)
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount
FROM paid_orders
GROUP BY user_id;

CTE 可以让复杂查询更易读，但不代表一定更快，仍要查看执行计划。

7.3 每组最新一条

查询每位用户最新订单：

SELECT id, user_id, order_no, total_amount, created_at
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, id DESC
    ) AS row_num
  FROM orders AS o
) AS ranked_orders
WHERE row_num = 1;

7.4 排名

SELECT
  id,
  name,
  sales_count,
  DENSE_RANK() OVER (ORDER BY sales_count DESC) AS sales_rank
FROM products;

• ROW_NUMBER()：每行编号不同。
• RANK()：并列名次后会跳号。
• DENSE_RANK()：并列名次后不跳号。

7.5 累计值

SELECT
  DATE(created_at) AS order_date,
  SUM(total_amount) AS daily_amount,
  SUM(SUM(total_amount)) OVER (
    ORDER BY DATE(created_at)
  ) AS accumulated_amount
FROM orders
WHERE status = 'paid'
GROUP BY DATE(created_at)
ORDER BY order_date;

8. 条件表达式和空值处理

8.1 CASE WHEN

SELECT
  order_no,
  total_amount,
  CASE
    WHEN total_amount >= 1000 THEN '大额订单'
    WHEN total_amount >= 100 THEN '普通订单'
    ELSE '小额订单'
  END AS amount_level
FROM orders;

8.2 COALESCE

返回第一个非 NULL 值：

SELECT
  id,
  username,
  COALESCE(phone, '未填写') AS phone
FROM users;

8.3 NULLIF

两个值相等时返回 NULL，可以避免除零：

SELECT
  paid_order_count / NULLIF(total_order_count, 0) AS paid_rate
FROM user_statistics;

9. 高频业务查询

9.1 查询最新一条记录

SELECT id, order_no, total_amount, created_at
FROM orders
WHERE user_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 1;

9.2 判断记录是否存在

SELECT EXISTS (
  SELECT 1
  FROM users
  WHERE email = ?
) AS user_exists;

9.3 查询重复数据

SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

真正需要禁止重复的数据应建立唯一索引，不能只依赖查询检查。

9.4 列表和总数

列表：

SELECT id, username, email, created_at
FROM users
WHERE status = 1
  AND deleted_at IS NULL
ORDER BY id DESC
LIMIT ? OFFSET ?;

总数：

SELECT COUNT(*) AS total
FROM users
WHERE status = 1
  AND deleted_at IS NULL;

两条 SQL 的筛选条件要保持一致。高并发下它们可能不是同一时刻的快照，普通列表通常可以接受。

10. EXPLAIN 和性能检查

EXPLAIN
SELECT id, order_no, total_amount
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;

MySQL 8.0.18+ 可以查看实际执行信息：

EXPLAIN ANALYZE
SELECT id, order_no, total_amount
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN ANALYZE 会真正执行查询，生产环境使用前要评估查询成本。

初学阶段重点关注：

• key：实际使用的索引。
• type：访问方式，const、ref、range 通常优于 ALL。
• rows：预计检查行数。
• Extra：例如 Using filesort、Using temporary。

常见问题：

-- 对索引字段使用函数
WHERE DATE(created_at) = '2026-08-08'

-- 前置通配符
WHERE name LIKE '%键盘%'

-- 字段和参数类型不一致，可能发生隐式转换
WHERE order_no = 202608080001

-- 联合索引没有从最左字段开始
WHERE created_at >= '2026-08-01'

这些写法不代表索引一定失效，最终由优化器决定，但应使用 EXPLAIN 验证。

11. Node.js 参数化查询

import type { RowDataPacket } from "mysql2";
import { pool } from "./database";

interface ProductRow extends RowDataPacket {
  id: number;
  name: string;
  price: string;
  stock: number;
}

async function searchProducts(keyword: string): Promise<ProductRow[]> {
  const escapedKeyword = keyword
    .replaceAll("\\", "\\\\")
    .replaceAll("%", "\\%")
    .replaceAll("_", "\\_");

  const [rows] = await pool.execute<ProductRow[]>(
    `SELECT id, name, price, stock
     FROM products
     WHERE name LIKE ? ESCAPE '\\\\'
       AND status = 1
     ORDER BY id DESC
     LIMIT 20`,
    [`%${escapedKeyword}%`],
  );

  return rows;
}

• 参数化查询防止输入改变 SQL 结构。
• % 和 _ 即使作为参数传入，仍然是 LIKE 通配符；需要按字面量搜索时必须额外转义。
• MySQL DECIMAL 常由驱动作为字符串返回，以避免 JavaScript 浮点精度问题。
• 确保 Node.js 连接与库表使用兼容的 utf8mb4 字符集。

12. 一页式 DQL 模板

-- 按主键查询
SELECT id, username, email
FROM users
WHERE id = ? AND deleted_at IS NULL
LIMIT 1;

-- 条件列表、排序和分页
SELECT id, name, price, stock
FROM products
WHERE status = ?
  AND name LIKE ?
ORDER BY id DESC
LIMIT ? OFFSET ?;

-- 统计
SELECT COUNT(*) AS total, COALESCE(SUM(total_amount), 0) AS amount
FROM orders
WHERE status = ?;

-- 分组
SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS amount
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING COUNT(*) >= ?;

-- 关联
SELECT o.order_no, o.total_amount, u.username
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.status = ?;

-- 是否存在
SELECT EXISTS (
  SELECT 1 FROM users WHERE email = ?
) AS user_exists;

-- 最新一条
SELECT id, order_no, created_at
FROM orders
WHERE user_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 1;