Structured Query Language

SQL
数据库语言

全栈地位

SQL 是与关系型数据库交互的标准语言——如果把数据库比作电子表格，SQL 就是操作这个表格的"公式语言"。

无论 PostgreSQL、MySQL 还是 SQLite，SQL 语法基本通用。理解 SQL 是操作数据的基础。

TOPIC数据库 → 表 → SQL

STATUS数据基础

idtitlestatus

1SQL 入门指南published

2安全防护实战draft

3认证中间件详解published

author_id INTEGER

created_at TIMESTAMP

views INTEGER DEFAULT 0

FOREIGN KEY

PRIMARY KEY

▎

Database & Table

数据库
与表

CORE CONCEPT

数据库 → 表 → 行/列

结构化存储的基础单元

数据库包含多张表，每张表存储一类数据。表之间可以通过外键（Foreign Key）建立关联——这是关系型数据库的核心设计思想。

Database blog_db

├── Table users

├── id INTEGER PRIMARY KEY

├── email VARCHAR(200) UNIQUE

└── created_at TIMESTAMP

├── Table posts

├── id SERIAL PRIMARY KEY

├── title VARCHAR(200) NOT NULL

├── content TEXT NOT NULL

├── status VARCHAR(20) DEFAULT 'draft'

├── author_id INTEGER NOT NULL

└── FOREIGN KEY (author_id) REFERENCES users(id)

└── Table ...

一个数据库包含多张表，每张表存储一类数据。表中的每一列定义字段的类型，每一行是一条具体的记录。表格的每一行是一条记录，每一列是一个字段。

CREATE & Types

创建表
与类型

DDL

CREATE TABLE 定义表结构

数据类型决定存储方式

CREATE TABLE 是 SQL 的 DDL（数据定义语言），用于定义表的结构。一旦创建完成，数据类型就确定了——每种类型决定了数据如何存储和查询。

schema/posts.sql

CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    content     TEXT NOT NULL,
    slug        VARCHAR(200) UNIQUE NOT NULL,
    status      VARCHAR(20) DEFAULT 0,
    views       INTEGER DEFAULT 0,
    author_id   INTEGER NOT NULL,
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW(),

    FOREIGN KEY (author_id) REFERENCES users(id)
);

类型

说明

示例

INTEGER

整数

SERIAL

自增整数

1, 2, 3, ...

VARCHAR(n)

可变长字符串（最长 n）

'hello'

TEXT

不限长度的文本

长文章内容

BOOLEAN

布尔值

TRUE / FALSE

TIMESTAMP

日期时间

'2025-03-15 10:30:00'

FLOAT

浮点数

3.14

JSONB

JSON 数据

{"key": "value"}

Key Insight

设计
类比

ANALOGY

数据库 ≈ 电子表格

SQL ≈ 公式语言

理解 SQL 不需要背语法——只需要理解一张表的结构和数据类型的含义。剩下的就是查文档。

把数据库比作一个电子表格——每张表是一个 Sheet，每列是一个字段定义，每行是一条数据记录。SQL 就是操作这些表格的"公式语言"：查询、筛选、排序、计算都通过 SQL 完成。

EXCEL → SQL

Excel

一个 Sheet 就是一张表，列头定义字段名，每行是一条数据

SQL

CREATE TABLE 定义表结构，INSERT 添加行，SELECT 查询行

Excel

单元格设置"数字格式"或"日期格式"限制输入类型

SQL

列定义 INTEGER、VARCHAR、TIMESTAMP 等类型约束数据格式

Excel

VLOOKUP 从另一个 Sheet 关联数据

SQL

FOREIGN KEY + JOIN 实现表间关联查询

思维模型几乎完全一致——区别在于 SQL 的表达力远超电子表格，支持复杂的多表关联、嵌套查询和聚合计算。

CRUD Operations

增删
改查

CRUD

CRUD 是对数据库最基础的四种操作——创建、读取、更新、删除。几乎所有后端 API 本质上都是 CRUD 的不同封装。

理解了 SQL 层的 CRUD，就能理解为什么 RESTful API 的设计是 GET/POST/PUT/DELETE。

TOPICINSERT → SELECT → UPDATE → DELETE

STATUS核心操作

idtitlestatus

1TS 入门指南+ INSERT

2Vue 3 实战published

3草稿条目- DELETE

4~ 更新中...* UPDATE

INSERT INTO

SELECT FROM

DELETE FROM

UPDATE SET

CREATE

插入
数据

INSERT

向表中添加新记录

单条、批量、带返回值

INSERT INTO ... VALUES 是添加数据的标准语法。支持单条插入、批量插入，以及通过 RETURNING 子句返回新插入的记录。

crud/insert.sql

0.35>-- 插入一条记录
INSERT INTO posts (title, content, slug, author_id)
VALUES (0, 1, 2, 1);

0.35>-- 插入多条记录
INSERT INTO posts (title, content, slug, author_id) VALUES
  (3, 4, 5, 1),
  (6, 7, 8, 1);

0.35>-- 插入并返回新记录
INSERT INTO posts (title, content, slug, author_id)
VALUES (9, 10, 11, 1)
RETURNING id, title, created_at;

READ

查询
数据

SELECT

最常用的 SQL 操作

条件、模糊、NULL 判断

SELECT 是 SQL 中使用最频繁的语句。从"查全部"到"精确筛选"，掌握 WHERE 子句的各种写法是核心。

crud/select.sql

0.35>-- 查询所有列 / 指定列
SELECT * FROM posts;
SELECT id, title, status, created_at FROM posts;

0.35>-- 条件查询
SELECT * FROM posts WHERE status = 0;
SELECT * FROM posts WHERE views > 100;
SELECT * FROM posts WHERE author_id = 1 AND status = 1;
SELECT * FROM posts WHERE status = 2 OR status = 3;

0.35>-- 模糊查询
SELECT * FROM posts WHERE title LIKE 4;
SELECT * FROM posts WHERE title LIKE 5;

0.35>-- NULL 判断
SELECT * FROM posts WHERE deleted_at IS NULL;
SELECT * FROM posts WHERE deleted_at IS NOT NULL;

=等于

> < >= <=比较

AND / OR组合

LIKE '%...'模糊

IS NULL空值

IS NOT NULL非空

IN (...)枚举

BETWEEN范围

UPDATE

更新
数据

CAUTION

永远不要忘记 WHERE

没有 WHERE = 更新全表

UPDATE ... SET ... WHERE 修改符合条件的记录。忘记写 WHERE 子句会更新整张表——这是最常见的 SQL 事故之一。

安全习惯：写 UPDATE 语句时，先写 WHERE 条件，再写 SET 内容。或者先用 SELECT 验证 WHERE 条件命中了哪些记录，再执行 UPDATE。

crud/update.sql

0.35>-- 更新指定记录
UPDATE posts
SET title = 0, updated_at = NOW()
WHERE id = 42;

0.35>-- 批量更新
UPDATE posts
SET status = 1
WHERE status = 2 AND created_at < 3;

0.35>-- 更新并返回
UPDATE posts
SET views = views + 1
WHERE id = 42
RETURNING id, views;

DELETE

删除
数据

DANGER ZONE

DELETE 不可逆

TRUNCATE 更快但不可回滚

DELETE FROM ... WHERE 删除符合条件的记录。同样，忘记 WHERE 会清空整张表。

crud/delete.sql

0.35>-- 删除指定记录
DELETE FROM posts WHERE id = 42;

0.35>-- 条件删除
DELETE FROM posts
WHERE status = 0 AND created_at < 1;

0.35>-- 清空整张表（慎用）
DELETE FROM posts;
0.35>-- 或更快但不可回滚
TRUNCATE TABLE posts;

DELETE

逐行删除，可以加 WHERE 条件，可以被回滚（在事务中），速度较慢。

TRUNCATE

直接清空整张表，不能加 WHERE，不可回滚，速度极快。适合清空大表。

Advanced Queries

排序
聚合
关联

ORDER · GROUP · JOIN

CRUD 只是最基础的读写。真正的数据价值在于排序展示、统计分析、跨表关联——这三项能力把 SQL 从"存取工具"提升为"数据引擎"。

ORDER BY 控制展示顺序，GROUP BY 做分类统计，JOIN 把分散在多张表的数据拼成完整的业务视图。

TOPICORDER BY → GROUP BY → JOIN

STATUS进阶查询

posts

↑

1 TS 入门

2 Vue 实战

3 API 设计

4 部署指南

↓

users

1 张三

2 李四

JOIN

COUNT(*) → 4AVG(views) → 128GROUP BY status

Sort & Page

排序
与分页

ORDER BY

ASC 升序 / DESC 降序

LIMIT + OFFSET 分页

ORDER BY 控制结果排序方向，LIMIT 限制返回条数，OFFSET 跳过指定数量——三者组合就是分页的完整实现。

query/sort_page.sql

0.35>-- 按创建时间降序（最新在前）
SELECT * FROM posts ORDER BY created_at DESC;

0.35>-- 多字段排序
SELECT * FROM posts ORDER BY status ASC, views DESC;

0.35>-- 第 1 页，每页 20 条
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

0.35>-- 第 2 页
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

0.35>-- 分页公式：OFFSET = (page - 1) * limit

OFFSET = (page - 1) × limit— 第 3 页每页 20 条 → OFFSET = 40

Aggregation

聚合
函数

GROUP BY

COUNT / SUM / AVG / MAX / MIN

HAVING 过滤分组结果

聚合函数对一组值计算后返回单个结果。GROUP BY 按字段分组，HAVING 对分组后的结果做过滤（WHERE 过滤的是原始行）。

query/aggregate.sql

0.35>-- 计数
SELECT COUNT(*) FROM posts;
SELECT COUNT(*) FROM posts WHERE status = 0;

0.35>-- 求和 / 平均值
SELECT SUM(views) FROM posts;
SELECT AVG(views) FROM posts WHERE status = 1;

0.35>-- 分组统计
SELECT status, COUNT(*) as count
FROM posts
GROUP BY status;

0.35>-- 分组过滤
SELECT author_id, COUNT(*) as post_count
FROM posts
GROUP BY author_id
HAVING COUNT(*) > 5;

WHERE

过滤原始行，在分组前执行

HAVING

过滤分组结果，在分组后执行

JOIN

表
关联

RELATIONS

INNER JOIN / LEFT JOIN

多对多需要中间表

表之间通过外键建立关联。JOIN 把分散在多张表的数据拼成一个完整的业务视图。

一对多一个作者 → 多篇文章

多对多文章 ↔ 标签（需中间表）

一对一用户 → 个人资料

query/join.sql

0.35>-- 内连接
SELECT posts.title, users.name as author_name
FROM posts
INNER JOIN users ON posts.author_id = users.id;

0.35>-- 左连接
SELECT posts.title, comments.content
FROM posts
LEFT JOIN comments ON comments.post_id = posts.id;

0.35>-- 多表连接
SELECT p.title, u.name as author, t.name as tag
FROM posts p
INNER JOIN users u ON p.author_id = u.id
INNER JOIN post_tags pt ON pt.post_id = p.id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE p.status = 0;

多对多中间表

创建中间表

post_tags 表同时引用 posts 和 tags 的主键，复合主键确保不重复关联。ON DELETE CASCADE 确保删除文章时自动清理关联。

查询方式

查文章的标签：tags JOIN post_tags WHERE post_id = ?。查标签下的文章：posts JOIN post_tags WHERE tag_id = ?。方向相反，结构对称。

Index · Transaction · Tricks

索引
事务
技巧

性能与安全

索引让查询从"翻遍全书"变成"查目录直达"，事务保证一组操作要么全部成功、要么全部回滚。子查询和 CASE 表达式是处理复杂数据的利器。

这三项能力把 SQL 从"能查数据"提升到"安全高效地管理数据"。

TOPICINDEX → TRANSACTION → SUBQUERY

STATUS进阶能力

INDEX

ts-intro → p.1

vue3 → p.2

api → p.3

deploy → p.4

→ 直接定位，O(log n)

→

TABLE

1 TS 入门 ...

2 Vue ...

3 API 设计 ...

4 部署 ...

5 安全 ...

6 日志 ...

扫描中...

逐行扫描，O(n)

ACID

AtomicityConsistencyIsolationDurability

Index

索引

ANALOGY

索引 ≈ 书籍目录

O(n) → O(log n)

索引类似书籍的目录——没有目录时，找某个内容需要翻遍整本书（全表扫描）；有目录后，直接翻到对应页码（索引查找）。

ddl/index.sql

0.35>-- 在 slug 列创建唯一索引
CREATE UNIQUE INDEX idx_posts_slug ON posts(slug);

0.35>-- 在 status 列创建普通索引
CREATE INDEX idx_posts_status ON posts(status);

0.35>-- 在 author_id + status 上创建复合索引
CREATE INDEX idx_posts_author_status ON posts(author_id, status);

何时需要索引：

✓WHERE 条件中频繁使用的列

✓JOIN 关联的列（外键）

✓ORDER BY 排序的列

✗数据量小（几百条）

✗频繁写入、极少查询的列

Transaction

事务

ACID

全部成功或全部回滚

BEGIN → COMMIT / ROLLBACK

事务确保一组操作"要么全部成功，要么全部回滚"——比如转账：扣款和入账必须同时成功，不能只扣款不入账。

txn/transfer.sql

BEGIN;  0.35>-- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

0.35>-- 如果上面两条都成功
COMMIT;  0.35>-- 提交

0.35>-- 如果任何一条失败
ROLLBACK;  0.35>-- 回滚（撤销所有修改）

事务的 ACID 特性：

AAtomicity全部成功或全部回滚

CConsistency数据始终满足约束条件

IIsolation并发事务互不干扰

DDurability提交后数据不会丢失

Advanced

子查询
与技巧

SUBQUERY

子查询 / EXISTS

CASE / DISTINCT / IN

子查询Subquery

0.35>-- 查询阅读量高于平均值的文章
SELECT title, views
FROM posts
WHERE views > (SELECT AVG(views) FROM posts);

子查询先执行括号内的查询，再将结果作为外层查询的条件。适用于"需要动态计算基准值"的场景。

EXISTS

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p WHERE p.author_id = u.id
);

EXISTS 只检查"是否存在匹配"，不返回具体数据。比 IN 更高效，尤其当子查询结果集很大时。

CASE · DISTINCT · IN

0.35>-- 子查询：查询阅读量高于平均值的文章
SELECT title, views
FROM posts
WHERE views > (SELECT AVG(views) FROM posts);

0.35>-- EXISTS：查找有文章的用户
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p WHERE p.author_id = u.id
);

0.35>-- CASE 表达式
SELECT
    title,
    CASE
        WHEN views > 1000 THEN 0
        WHEN views > 100 THEN 1
        ELSE 2
    END as popularity
FROM posts;

0.35>-- DISTINCT：去重
SELECT DISTINCT author_id FROM posts;

0.35>-- IN：匹配多个值
SELECT * FROM posts WHERE status IN (3, 4);

CASE

类似 if-else，把数值映射为可读标签

DISTINCT

去除重复行，只保留唯一值

替代多个 OR，语法更简洁

Pitfalls & Glossary

常见
错误

与术语表

SQL 最危险的错误不是语法写错，而是逻辑正确但影响范围错误——比如忘记写 WHERE，一条语句就能毁掉整张表。

N+1 查询则是在代码层面"慢慢地杀死性能"。这些错误每个开发者都犯过，关键是建立防御习惯。

TOPICWHERE 遗漏 · SELECT * · N+1

STATUS避坑指南

✗ ERROR

UPDATE posts SET status = 'archived';

# 没有 WHERE !

# 65 条记录全部被更新

N+1 PROBLEM

1 query

× N ...

✓ FIXWHERE id = 42JOIN → 1 query

Pitfalls

三个
常见错误

TOP 3

每个都犯过

关键是建立防御习惯

8.1不加 WHERE 的 UPDATE/DELETE

-- ✗ 更新了所有记录（灾难性后果）

UPDATE posts SET status = 'archived';

-- ✓ 始终加 WHERE 条件

UPDATE posts SET status = 'archived'WHERE id = 42;

-- 建议：先用 SELECT 确认影响范围

SELECT * FROM posts WHERE status = 'draft'AND created_at < '2024-01-01';

# 确认后再改为 UPDATE/DELETE

8.2SELECT *

-- ✗ 查询所有列（浪费带宽，且表结构变化后可能出问题）

SELECT * FROM posts;

-- ✓ 只查询需要的列

SELECT id, title, status, created_at FROM posts;

8.3N+1 查询问题

# ✗ 循环中逐条查询（每次循环一条 SQL）

posts = db.execute("SELECT * FROM posts").fetchall()

for post in posts:

author = db.execute(f"SELECT * FROM users WHERE id = {post.author_id}").fetchone()

# 如果有 100 篇文章，就执行了 101 次查询

# ✓ 使用 JOIN 一次查询

results = db.execute("""

SELECT p.*, u.name as author_name

FROM posts p

JOIN users u ON p.author_id = u.id

""").fetchall()

Glossary

术语
对照表

REFERENCE

12 个核心术语

中文 → 英文 → 释义

掌握术语是阅读文档和交流的基础。以下 12 个术语覆盖了 SQL 章节的全部核心概念。

中文

英文

释义

表

Table

存储一类数据的结构（行和列）

行/记录

Row / Record

一条具体的数据

列/字段

Column / Field

数据的属性定义

主键

Primary Key

唯一标识一条记录的字段

外键

Foreign Key

引用另一张表主键的字段

索引

Index

加速查询的数据结构

事务

Transaction

一组原子操作（全部成功或全部回滚）

连接

JOIN

多表关联查询

聚合

Aggregate

对数据集进行统计计算

子查询

Subquery

嵌套在另一个查询中的查询

约束

Constraint

对数据的限制条件（NOT NULL, UNIQUE 等）

迁移

Migration

数据库结构的版本化变更

SQL数据库语言

数据库与表

创建表与类型

设计类比

增删改查

插入数据

查询数据

更新数据

删除数据

排序聚合关联

排序与分页

聚合函数

表关联

索引事务技巧

索引

事务

子查询与技巧

常见错误

三个常见错误

术语对照表

SQL
数据库语言

数据库
与表

创建表
与类型

设计
类比

增删
改查

插入
数据

查询
数据

更新
数据

删除
数据

排序
聚合
关联

排序
与分页

聚合
函数

表
关联

索引
事务
技巧

子查询
与技巧

常见
错误

三个
常见错误

术语
对照表