SQL数据库学习路线：先掌握哪些核心知识

学 SQL，最容易走偏的两种方式，一种是背很多语法却不知道数据应该怎样组织；另一种是只会写查询，遇到并发、数据完整性或性能问题时无从下手。更稳妥的路线，是先理解关系数据库如何描述数据，再从简单查询逐步学到数据修改、表关系、事务、索引和安全。

本文以 PostgreSQL 为例，代码可以在 PostgreSQL 中执行。SQL 标准规定了许多基础语法，但不同数据库在数据类型、分页、日期函数、自动生成主键、JSON、冲突处理等方面存在差异。学习时应先掌握通用概念，再针对实际使用的数据库核对文档，而不是把一种数据库的语法当成所有数据库的统一写法。

一、先建立正确预期：SQL 管理的是有结构的数据

关系数据库把数据组织成表。表由行和列组成：行表示一条记录，列表示记录具有的属性。SQL 是用来定义表结构、读取和修改数据，以及控制访问和事务的一种语言。

SQL 通常按用途分为几类：定义表、约束等结构的 DDL；读取数据的查询语句；插入、更新、删除数据的 DML；以及事务控制语句。不同资料对这些分类的边界有时略有不同，不必先背分类，重点是理解每类语句会对数据库产生什么影响。

数据库表不是电子表格的随意集合。列有数据类型，行应能被可靠地区分，表与表之间也应通过明确的键关联。数据库可以帮助应用守住一些规则，例如订单必须属于已有客户、商品价格不能为负；如果所有规则都只写在应用界面上，其他导入脚本或服务仍可能写入不符合预期的数据。

二、第一阶段：认识表、数据类型和约束

先学会阅读表定义，并理解主键、外键、唯一约束、非空约束和检查约束。下面创建一组简化的客户、商品和订单表示例。请在测试数据库中执行；重复运行时，表已存在会报错，可以先删除示例表，或者换一套表名。

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT NOT NULL,
    price      NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    active     BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
    order_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    ordered_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status      TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled'))
);

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL REFERENCES orders(order_id),
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

PRIMARY KEY 为表中的行提供唯一标识，并且不能为 NULL。外键声明的是引用关系；默认情况下，删除被引用的客户或商品可能因为外键限制而失败。是否使用 ON DELETE CASCADE 等级联行为，需要根据业务语义决定，不能为了让删除“顺利通过”就随意级联。

订单明细里保存 unit_price，是为了记录下单时的价格。若查询历史订单时总是关联商品表的当前价格，商品调价后，旧订单金额也会跟着改变。数据是否需要保留历史快照，要由业务含义决定。

NUMERIC(10, 2) 表示精确小数，适合需要十进制精确表示的金额类数据；它的精度和可用范围受定义约束。浮点类型用于近似数值计算，不应不加判断地替代金额字段。日期时间也要先确认业务需要的是日期、当地时间还是带时区的时间点；示例使用 TIMESTAMPTZ，并不意味着数据库可以替应用自动决定所有时区语义。

注意，NULL 不等于空字符串，也不等于数字零。它表示缺失或未知。NOT NULL 只能禁止空值，不能验证文本内容是否合理。约束适合表达数据库必须长期成立的规则，但复杂的跨多行业务条件通常需要额外设计，不能假定一个简单的 CHECK 就能安全覆盖任意跨表逻辑。

三、第二阶段：掌握查询的基本骨架

把查询拆成几个问题来写：要哪些列、从哪些表取、行要满足什么条件、如何排序，以及是否要限制返回数量。先显式列出需要的列，不要把 SELECT * 当作生产代码默认写法。表结构变化后，通配符会让结果列意外变化，也会读取用不到的数据。

SELECT customer_id, email, full_name
FROM customers
WHERE customer_id > 0
ORDER BY customer_id
LIMIT 20;

WHERE 对行进行筛选；ORDER BY 指定排序；LIMIT 限制返回行数。没有排序条件时，数据库不保证结果按某个固定顺序返回。即使一条查询今天看起来按主键排列，也不应依赖未写出的排序顺序。

练习条件表达式时，熟悉比较运算、AND、OR、IN、BETWEEN、LIKE，并适当使用括号表达组合条件。尤其注意 AND 与 OR 混用时的优先级，不要依赖记忆或让读者猜测意图。

SELECT product_id, name, price
FROM products
WHERE active = TRUE
  AND (price < 50 OR name LIKE 'Book%')
ORDER BY price ASC, product_id ASC;

排序最好附上足以确定先后顺序的次级列。例如多个商品价格相同时，加上唯一键作为次级排序。分页也依赖稳定排序：LIMIT 20 OFFSET 40 在数据量大时可能需要跳过很多行，而且数据持续变化时，不同页之间可能出现重复或遗漏。大表的连续翻页可进一步学习基于排序键的游标式分页，但它要求排序键选择合理，也不适合所有随机跳页场景。

四、第三阶段：插入、更新和删除要有边界

写数据语句必须明确目标列和筛选条件。下面插入一名客户，并使用 PostgreSQL 的 RETURNING 返回生成的主键；RETURNING 是 PostgreSQL 支持的扩展语法，其他数据库可能使用不同方式获取新记录标识。

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Lee')
RETURNING customer_id, email;

批量插入可以一次写多组值。执行前要确认唯一约束冲突时应当失败、更新还是忽略；这属于业务规则，不应为了消除报错而盲目忽略错误。

INSERT INTO products (name, price)
VALUES
    ('Notebook', 4.50),
    ('Pen', 1.20);

更新和删除尤其容易造成大范围影响。执行前可先用同一条件写 SELECT 检查命中记录，再执行修改；生产操作还应考虑事务、权限、备份与审计。

SELECT order_id, status
FROM orders
WHERE status = 'pending';

UPDATE orders
SET status = 'cancelled'
WHERE order_id = 42
  AND status = 'pending';

没有 WHERE 的 UPDATE 会更新整张表，没有 WHERE 的 DELETE 会删除整张表中的行。不要把数据库客户端的“自动提交”状态当成安全网；若条件错了，已经提交的修改可能无法简单撤销。必要时在事务内操作，并在提交前核验结果。

五、第四阶段：连接表，理解一对多和多对多

关系型数据库的关键能力之一，是用键把分散在不同表里的事实组合起来。客户与订单通常是一对多关系；订单与商品通常是多对多关系，中间通过订单明细表表达。学习连接时，先弄清每张表的粒度：客户表一行代表一个客户，订单表一行代表一张订单，订单明细表一行代表某订单中的一种商品。

SELECT o.order_id,
       o.ordered_at,
       c.email,
       o.status
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.ordered_at DESC, o.order_id DESC;

JOIN（即内连接）只保留两边能匹配上的行。若要找出尚未产生订单的客户，可使用左连接，并把右表的空值作为“没有匹配记录”的标记：

SELECT c.customer_id, c.email
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

一个常见错误，是对左连接后的右表条件直接写进 WHERE，意外排除右表为空的行。若需要保留左表全部记录，右表过滤条件通常应放在 ON 中，并根据目标结果验证语义。

另一个常见错误是连接条件遗漏或不完整，导致行数成倍增长。连接订单与明细时，使用订单主键；如果是复合键，必须按业务所需的全部键列匹配。发现结果重复时，不要第一时间加 DISTINCT：先检查连接关系和表的粒度，重复可能意味着查询设计错误，而不是显示问题。

六、第五阶段：聚合、分组与 NULL 处理

聚合函数把多行汇总为统计结果，常见的有 COUNT、SUM、AVG、MIN 和 MAX。GROUP BY 用于定义汇总粒度，HAVING 用于筛选聚合后的组。

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

可以把 WHERE 理解为聚合前筛选行，把 HAVING 理解为聚合后筛选组。很多查询引擎对未分组列有严格要求：选择列表中的普通列通常必须出现在 GROUP BY 中，或被聚合。不要在分组查询中混入一个含义不明确的列。

COUNT(*) 统计行数；COUNT(column) 不计入该列为 NULL 的行。大多数聚合函数会忽略空值，但具体行为应查阅函数文档。判断空值要用 IS NULL 或 IS NOT NULL，不能写 = NULL，因为空值比较遵循 SQL 的三值逻辑：条件可能为真、假或未知。

SELECT customer_id,
       COUNT(*) AS item_rows,
       SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY customer_id;

上面的示例故意不能直接执行：order_items 没有 customer_id 列。要按客户汇总，必须通过订单表连接；这也是检查字段归属和查询粒度的好习惯。可运行版本如下：

SELECT o.customer_id,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.customer_id
ORDER BY revenue DESC;

金额统计还要明确退款、税费、币种、取消订单等业务口径。SQL 能算出表达式，不代表表达式自动符合业务定义。金额使用精确小数类型时，也应确认字段精度足以覆盖实际范围。

七、第六阶段：事务与并发，不只学习单条语句

事务把一组数据库操作作为一个整体处理。典型过程是开始事务、执行多条语句、检查结果后提交；发生错误时回滚。以下为 PostgreSQL 的示例：

BEGIN;

UPDATE products
SET price = price + 1.00
WHERE product_id = 1;

SELECT product_id, price
FROM products
WHERE product_id = 1;

ROLLBACK;

这里最后执行 ROLLBACK，因此改价不会保留。真实业务中，核验通过后可执行 COMMIT。事务能帮助保证一组操作共同提交或共同撤销，但并不意味着任何并发情况下都自然符合业务预期。并发更新、重复请求、锁等待和死锁都需要理解。

ACID 是理解事务的常用框架：原子性、一致性、隔离性和持久性。隔离级别影响并发事务能观察到什么现象；不同数据库的实现和默认值可能不同。不要仅凭“用了事务”就假设并发安全，也不要把应用层读取、判断、再写入的多步流程当成不可被其他事务插入的原子操作。

涉及账户余额、库存或名额时，通常需要在数据库侧设计条件更新、行锁、唯一约束或合适的隔离策略，并处理冲突重试。具体方案取决于并发量、业务一致性要求和数据库能力；可以从 PostgreSQL 的事务与锁文档，以及实际压测开始，而不是照搬一个孤立 SQL 片段。

八、第七阶段：索引与查询计划

索引帮助数据库更快定位数据，但会占用空间，也会增加插入、更新和删除的维护成本。索引不是越多越好。常见的起点是为主键和常用关联键建立索引，并根据真实查询筛选、排序与连接方式，评估是否需要其他索引。

CREATE INDEX idx_orders_customer_ordered_at
ON orders (customer_id, ordered_at DESC);

复合索引的列顺序会影响可用查询模式。上例通常更适合以客户编号筛选，并按下单时间访问的查询；不能据此推断它对任意只筛选 ordered_at 的查询同样有效。是否命中索引，还取决于数据分布、查询写法、统计信息和优化器判断。

在 PostgreSQL 中，可以用 EXPLAIN 查看计划，用 EXPLAIN ANALYZE 实际执行并报告运行信息：

EXPLAIN
SELECT order_id, ordered_at
FROM orders
WHERE customer_id = 10
ORDER BY ordered_at DESC
LIMIT 20;

EXPLAIN ANALYZE 会真正执行查询。对普通查询来说这很常见，但若分析的是有副作用的语句，或查询本身代价很高，必须先了解影响并选择安全环境。计划里的顺序扫描也不必然是错误：表很小，或者要读取大部分行时，顺序扫描可能更合适。优化应基于代表性数据、实际耗时和执行计划，而不是看到“没有用索引”就立刻加索引。

九、SQL 参数化与安全边界

应用程序构造 SQL 时，应优先使用驱动提供的参数绑定接口。参数化能把用户提供的值作为数据传递，而不是拼接成 SQL 语法，从而降低 SQL 注入风险。参数占位符的写法由数据库驱动决定，不存在所有语言、所有驱动通用的一种占位符。

以下是 Python 使用 psycopg（psycopg 3）的示例，假定应用已经配置好数据库连接。注意占位符写作 %s，参数值单独传入，不能先用字符串格式化把值拼进 SQL：

import psycopg

with psycopg.connect("dbname=app user=app_user") as conn:
    rows = conn.execute(
        "SELECT customer_id, email FROM customers WHERE email = %s",
        ("[email protected]",),
    ).fetchall()

    for row in rows:
        print(row)

实际部署时应通过受控配置提供连接信息，避免把密码写入源代码。连接参数的格式、凭据管理和 TLS 配置需按所用驱动及部署环境处理；示例连接串只用于说明调用方式。

参数绑定不是通用的 SQL 转义器，也不是授权系统。参数适用于值，例如比较条件中的邮箱地址；通常不能直接用它代替表名、列名或排序方向，因为这些内容属于 SQL 结构。若要动态选择排序列，应通过固定白名单映射到已知列名，而不是接受任意输入后拼接。

sort_columns = {
    "recent": "created_at",
    "name": "full_name",
}

sort_key = request.args.get("sort", "recent")
column = sort_columns.get(sort_key, "created_at")

sql = f"SELECT customer_id, full_name FROM customers ORDER BY {column}"

这段示例只展示白名单思路。column 必须来自程序内的固定映射，不能把原始请求值直接插入 SQL。若方向也由用户选择，同样应限制为固定的 ASC 或 DESC。另外，参数化不能代替最小权限、输入校验、访问控制和错误处理；数据库账号不应拥有业务根本不需要的权限。

查询结果中可能包含个人信息或机密内容，安全不仅是“防注入”。还要控制哪些用户能读取或修改哪些数据，避免在日志、异常信息和页面中泄露敏感字段。数据库的行级安全等功能是否采用，要结合权限模型和应用架构评估。

十、常见学习错误与排查顺序

• 不理解数据粒度就连接。先说清楚每张表一行代表什么，再判断连接后行数应增加、保持还是减少。
• 把空值当成普通值比较。用 IS NULL 检查空值，并留意聚合函数对空值的处理。
• 忽略结果顺序。需要稳定顺序就显式写 ORDER BY，分页时尤其如此。
• 修改语句没有经过核验。先用相同条件预览命中记录，再在事务或受控流程中执行更新与删除。
• 重复行就加 DISTINCT。先检查连接键、表粒度和一对多关系，去重可能掩盖根本错误。
• 过早优化或盲目加索引。先确认查询正确，再使用执行计划和真实数据观察瓶颈。
• 认为某数据库语法处处通用。分页、日期处理、自动生成键、冲突处理和 JSON 操作常有方言差异。
• 把参数化当作完整安全方案。参数保护的是按正确方式绑定的值；权限、动态标识符、敏感数据与业务校验仍需单独处理。

十一、建议的练习顺序

1. 创建客户、商品、订单和订单明细表，给主键、外键和必要约束写出理由。
2. 练习按条件查询、排序、限制返回行数，并确认没有显式排序时不要假定结果顺序。
3. 插入测试数据，再写更新与删除语句；每次先用查询检查目标范围。
4. 分别写内连接和左连接，回答“哪些客户没有订单”“每张订单有哪些商品”。
5. 练习按客户统计订单数、按商品统计销售数量，明确每个结果的统计粒度。
6. 在事务中模拟多步修改和回滚，再了解并发修改可能带来的问题。
7. 为真实查询查看执行计划，比较索引前后的表现，而不是为了完成练习盲目创建大量索引。
8. 在应用代码中使用参数绑定，并测试输入包含引号等特殊字符时查询是否仍按数据处理。

学习顺序不必被当作硬性门槛：实际项目可能很早就需要事务、权限或查询调优。但基础查询、关系建模、约束和安全习惯值得尽早掌握。完成上面的练习后，再学习窗口函数、公共表表达式、视图、存储过程、锁与隔离级别、备份恢复和数据库迁移，会更容易理解它们解决的具体问题。

判断是否真正掌握 SQL，不是看记住了多少关键字，而是能否解释查询结果的粒度、数据约束的意义、修改影响的范围，以及性能和安全方案的边界。能做到这些，换一款数据库时即使需要调整语法，也仍然拥有可迁移的核心能力。