尚拙
Search K

Appearance

Node系列 · 数据库：单表查询 ​

SELECT 是 SQL 里最常用也最容易写错的语句。本章从语法骨架出发，覆盖 WHERE 过滤、ORDER BY 排序、LIMIT 分页、聚合函数——单表查询的所有核心场景。

一、SELECT 完整语法骨架 ​

sql
SELECT [DISTINCT] 列[, 列2, ...]
FROM 表名
[WHERE 过滤条件]
[GROUP BY 分组列]
[HAVING 分组过滤]
[ORDER BY 排序列 [ASC | DESC]]
[LIMIT 偏移, 行数];

最小可执行形式：SELECT 1;（返回常数）。

二、基本查询 ​

2.1 查询所有列 ​

sql
SELECT * FROM `student`;

WARNING

生产项目避免 SELECT *：

  • 列数变化会破坏上层代码（ORM 映射、JSON 序列化）
  • 无用字段浪费网络和内存
  • 失去覆盖索引优化机会

2.2 查询指定列 ​

sql
SELECT `id`, `stuno`, `name` FROM `student`;

2.3 列别名（AS） ​

sql
SELECT
  `id`        AS `学号ID`,
  `stuno`     AS `学号`,
  `name`      AS `姓名`
FROM `student`;

别名让结果集更易读，也方便应用层取值。

2.4 去重（DISTINCT） ​

sql
-- 查询所有不同的班级 ID
SELECT DISTINCT `class_id` FROM `student`;

TIP

DISTINCT 作用于所有选中的列，不是单个列。SELECT DISTINCT a, b FROM t 表示 a+b 组合去重。

三、WHERE 过滤 ​

WHERE 是查询的核心，决定返回哪些行。

3.1 比较运算符 ​

运算符含义示例
=等于WHERE sex = b'1'
<> 或 !=不等于WHERE class_id <> 3
> < >= <=大小比较WHERE age >= 18
BETWEEN ... AND ...范围（含端点）WHERE age BETWEEN 18 AND 30
IN (...)在列表中WHERE class_id IN (1, 3, 5)
NOT IN (...)不在列表中WHERE class_id NOT IN (2, 4)
IS NULL为空WHERE phone IS NULL
IS NOT NULL非空WHERE phone IS NOT NULL
LIKE模糊匹配WHERE name LIKE '张%'

WARNING

NULL 不能用 = 比较——必须用 IS NULL 或 IS NOT NULL。= NULL 永远返回 NULL（不是 true）。

3.2 逻辑运算符 ​

sql
-- AND：全部满足
SELECT * FROM `student` WHERE `sex` = b'1' AND `class_id` = 3;

-- OR：任一满足
SELECT * FROM `student` WHERE `class_id` = 1 OR `class_id` = 2;

-- NOT：取反
SELECT * FROM `student` WHERE NOT `class_id` = 3;

AND 优先级高于 OR，复杂的条件加括号更清晰：

sql
WHERE (`class_id` = 1 OR `class_id` = 2) AND `sex` = b'1';

3.3 模糊查询 LIKE ​

sql
-- % 匹配任意长度字符（含 0 个）
WHERE `name` LIKE '张%'        -- 张三、张三丰
WHERE `name` LIKE '%三%'       -- 包含"三"
WHERE `name` LIKE '%张'        -- 以"张"结尾

-- _ 匹配单个字符
WHERE `name` LIKE '张_'        -- 张三、张四（不含张三丰）
WHERE `name` LIKE '张__'       -- 张三丰（三个字）

WARNING

LIKE '%xxx%' 全表扫描，无法走索引。数据量大时考虑：

  • 全文索引（FULLTEXT INDEX）
  • 引入 Elasticsearch 做搜索
  • 反范式存储"是否包含某关键词"标记

四、ORDER BY 排序 ​

sql
-- 单列升序（默认）
SELECT * FROM `student` ORDER BY `id`;

-- 单列降序
SELECT * FROM `student` ORDER BY `created_at` DESC;

-- 多列排序：先按 class_id 升序，同 class_id 内按 score 降序
SELECT * FROM `student`
ORDER BY `class_id` ASC, `score` DESC;

TIP

ORDER BY 的字段要么是索引列，要么接受 filesort 性能开销。生产大表分页查询用"延迟关联"或"游标分页"避免深翻页。

五、LIMIT 分页 ​

sql
-- 前 10 条
SELECT * FROM `student` LIMIT 10;

-- 第 11-20 条（跳过 10 条取 10 条）
SELECT * FROM `student` LIMIT 10, 10;

等价写法：

sql
SELECT * FROM `student` LIMIT 10 OFFSET 10;

深翻页性能问题 ​

sql
-- ❌ 越翻越慢：offset 100000 时扫 100010 行
SELECT * FROM `student` LIMIT 100000, 10;

WARNING

深翻页是性能大坑。LIMIT 100000, 10 会扫描前 100010 行但只返回 10 行——资源浪费。生产方案：

  • 游标分页（推荐）：WHERE id > last_id LIMIT 10，索引直接定位
  • 记住最大 id：业务上让用户"上一页 / 下一页"而不是任意跳页
sql
-- ✅ 游标分页：每次只取当前 id 之后的 10 条
SELECT * FROM `student` WHERE `id` > 1000 ORDER BY `id` LIMIT 10;

六、聚合函数 ​

聚合函数对一组行做计算，返回单值：

函数含义示例
COUNT(*)总行数SELECT COUNT(*) FROM student;
COUNT(col)col 非空的行数SELECT COUNT(phone) FROM student;
SUM(col)求和SELECT SUM(score) FROM score;
AVG(col)平均SELECT AVG(score) FROM score;
MAX(col)最大SELECT MAX(score) FROM score;
MIN(col)最小SELECT MIN(score) FROM score;

TIP

COUNT(*) 和 COUNT(col) 不同：

  • COUNT(*) 统计所有行（包括 NULL 列）
  • COUNT(col) 统计 col 非 NULL 的行

七、GROUP BY 分组 ​

按列分组，配合聚合函数用：

sql
-- 每个班级的学生人数
SELECT `class_id`, COUNT(*) AS `count`
FROM `student`
GROUP BY `class_id`;

HAVING 过滤分组结果 ​

sql
-- 找出学生人数超过 30 的班级
SELECT `class_id`, COUNT(*) AS `count`
FROM `student`
GROUP BY `class_id`
HAVING `count` > 30
ORDER BY `count` DESC;
子句作用对象何时用
WHERE行（原始数据）分组前过滤
HAVING组（聚合后）分组后过滤

WARNING

HAVING 的字段必须是聚合函数或 GROUP BY 字段。HAVING name = '张三' 会报错或产生不可预期结果。

八、Node 端查询实战 ​

javascript
const mysql = require('mysql2/promise');

const pool = mysql.createPool({ /* config */ });

// 查询第 11-20 条学生
const [rows] = await pool.execute(
  'SELECT id, stuno, name, class_id FROM student WHERE class_id = ? ORDER BY id LIMIT ?, ?',
  [3, 10, 10]
);
console.log(rows);
// [{ id: 11, stuno: '...', name: '...', class_id: 3 }, ...]

// 聚合：每个班人数
const [stats] = await pool.execute(
  'SELECT class_id, COUNT(*) AS count FROM student GROUP BY class_id HAVING count > ?',
  [30]
);

九、最佳实践 ​

场景推荐
列选择永远列名而非 *
WHERE 索引高频过滤字段建索引
模糊查询前缀匹配 (LIKE 'xxx%') 走索引；%xxx% 不走
排序小数据集 ORDER BY；大数据集游标分页
分页浅页 LIMIT N；深页游标分页（WHERE id > ?）
聚合 + 过滤WHERE 过滤行 → GROUP BY → HAVING 过滤组
NULL 判断IS NULL / IS NOT NULL，不是 = NULL

十、小结 ​

  • SELECT 完整语法：SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
  • WHERE 过滤原始行；HAVING 过滤聚合后的组
  • 比较运算：= <> BETWEEN IN IS NULL LIKE
  • 逻辑运算：AND OR NOT，复杂条件加括号
  • 排序默认升序；LIMIT N, M 跳 N 行取 M 行
  • 深翻页用游标分页（WHERE id > ?）替代 offset
  • 聚合函数：COUNT / SUM / AVG / MAX / MIN