SQL数据库开发入门：从表结构设计到基础查询

关系型数据库常用于保存结构明确、需要可靠查询和更新的数据，例如订单、用户、库存、账务记录和业务配置。SQL 是操作这类数据库的主要语言。学会写查询只是入门的一部分：字段类型是否合适、数据完整性由谁保证、查询是否安全，以及并发更新时会发生什么，都会影响系统能否长期稳定运行。

本文以 PostgreSQL 为例，介绍从设计表结构到编写基础查询的实践方法。示例中的 SQL 可以在 PostgreSQL 中执行；应用代码使用 Python 的 psycopg 3 演示参数化查询。其他数据库在自增列、日期函数、分页语法和占位符形式上可能不同，迁移时应以对应数据库文档为准。

一、SQL 数据库适合做什么

SQL 数据库适合数据之间存在明确关系、需要事务、约束和灵活查询的场景。比如，一个订单关联一个客户，订单又包含多条商品明细；数据库可以通过外键确保订单指向真实客户，并在事务中保证订单与明细一起成功保存或一起回滚。

• 适合：电商订单、预约、财务流水、内容管理、企业业务系统等结构化数据，以及需要按多个条件筛选、排序和汇总的场景。
• 需要评估：超大规模日志、复杂图关系、全文检索或海量非结构化文档。SQL 数据库可能仍能承担部分工作，但也可能需要专用搜索、分析或对象存储系统配合。
• 不是自动解决方案：使用数据库并不会自动让查询足够快，也不会替代备份、权限管理、数据迁移和容量规划。

选择数据库时，还应考虑团队经验、部署方式、备份恢复要求、读写负载和可接受的运维成本。以下例子使用 PostgreSQL，并不表示所有项目都必须选它。

二、先把数据关系想清楚

建表前，先明确实体、实体之间的关系，以及业务规则。假设要保存客户、商品、订单和订单明细：

• 一个客户可以有多个订单；每个订单属于一个客户。
• 一个订单可以包含多个商品；同一种商品也可以出现在多个订单中，因此订单和商品之间是多对多关系。
• 订单明细表用来表示这层关系，同时记录购买数量和下单时的商品单价。

订单明细需要保存下单时的价格快照。若只在查询时读取商品当前售价，商品涨价后，历史订单金额也会随之变化，这通常不符合业务预期。数据是否重复并非唯一判断标准：适度保存业务快照，可以让历史事实保持稳定。

还需要提前回答一些具体问题：邮箱是否允许重复？未填写的电话号码是未知，还是确实没有？商品下架后，历史订单明细是否仍需保留？这些选择会影响唯一约束、空值策略和删除规则。不要只根据字段名称猜测业务含义。

三、创建一组可运行的表

下面的脚本适用于 PostgreSQL 10 及以上版本，使用 identity 列生成主键。可以在新建的测试数据库中通过 psql 执行。生产环境不应未经评估就直接重建已有表。

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,
    CHECK (length(trim(full_name)) > 0)
);

CREATE TABLE products (
    product_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    price       NUMERIC(12, 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),
    status       TEXT NOT NULL DEFAULT 'new'
                 CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
    ordered_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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(12, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

字段类型和约束各有用途

• BIGINT 用于整数标识符。identity 列由数据库生成，但它不是“无间断编号”保证：事务回滚或并发分配都可能造成编号空缺，不应把主键当作连续的业务序号。
• TEXT 用于文本。PostgreSQL 中 TEXT 不设固定长度上限；具体数据库、字符编码、存储和部署环境仍有容量限制。数据库字段长度也不等于业务层的输入校验策略。
• NUMERIC(12, 2) 适合需要十进制精确计算的金额。它表示总精度最多 12 位、小数部分最多 2 位，不是无限精度。插入超出范围的值可能报错；输入值的小数位数超出定义时，数据库可能按规则舍入，因此应明确业务允许的精度与舍入方式。
• TIMESTAMPTZ 用于带时区语义的时间点。PostgreSQL 会将其转换为内部时间点并按会话时区显示，不保留输入中的原始时区名称。若需要记录用户选择的时区名称，应另存时区标识。
• NOT NULL、UNIQUE、CHECK 和外键约束让数据库直接拒绝不满足条件的数据。应用层也应提供友好校验，但不能把完整性完全寄托在前端或单个应用实例上。

主键用于唯一标识记录。订单明细使用复合主键 (order_id, product_id)，表示同一商品在一个订单中最多一行。若业务允许同商品拆成多行，例如区分批次或折扣，应设计单独的明细主键，并重新确定唯一规则。

外键默认阻止删除仍被引用的父记录。这个例子没有设置级联删除，是为了避免删除客户或订单时意外连带删除历史数据。是否使用 ON DELETE CASCADE，应基于业务规则决定，而不是为了让删除语句“更容易成功”。

四、插入、读取、更新和删除

写入数据时，始终列出目标字段，不依赖表当前的字段顺序。显式插入有助于代码审查，也能减少表结构变化导致的意外。

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', '林晓');

INSERT INTO products (sku, name, price)
VALUES
    ('TEA-001', '乌龙茶', 28.50),
    ('CUP-002', '陶瓷杯', 45.00);

SELECT customer_id, email, full_name, created_at
FROM customers
WHERE email = '[email protected]';

查询使用 SELECT。除非确实需要所有列，尽量明确列名，而不是长期依赖 SELECT *。明确列名更容易发现接口返回内容的变化，也避免无意传输敏感或体积较大的字段。

使用 UPDATE 修改记录时，通常应带上明确的筛选条件：

UPDATE products
SET price = 30.00
WHERE sku = 'TEA-001';

DELETE FROM products
WHERE sku = 'OLD-009';

在执行更新或删除前，可以先用相同条件执行 SELECT 检查目标行。在生产环境中，尤其要确认 WHERE 条件是否充分：省略条件可能会更新或删除整张表的记录。数据库客户端的自动提交设置不同，提交前也要弄清操作是否已经生效。

NULL 不是普通值

NULL 表示缺失或未知，不等于数字 0、空字符串，也不等于另一个 NULL。因此，不能用 = NULL 查找空值，而应写 IS NULL 或 IS NOT NULL：

SELECT customer_id, full_name
FROM customers
WHERE full_name IS NOT NULL;

如果业务上必填，应使用 NOT NULL；如果允许未知，则在应用逻辑中明确处理空值。把“未知”错误地替换成空字符串，容易让统计和过滤出现歧义。

五、过滤、排序与分页

WHERE 过滤行，ORDER BY 排序。对于要稳定翻页的查询，排序条件应尽量包含唯一键作为最后一列；否则多行排序值相同，分页结果可能在不同请求间出现重复或遗漏。

SELECT product_id, sku, name, price
FROM products
WHERE active = TRUE
  AND price >= 20.00
ORDER BY price DESC, product_id ASC
LIMIT 20 OFFSET 0;

PostgreSQL 的 LIMIT 和 OFFSET 用于限制结果行数和跳过前面的行。没有 ORDER BY 时，数据库不承诺结果顺序。偏移量很大时，数据库仍可能需要扫描并丢弃大量行，性能会变差。列表翻页到深页时，可评估“游标式”或键集分页，例如记录上一页最后一条的排序键，再查询其后的记录；具体条件需与排序方向和唯一键保持一致。

分页大小应由应用限制在合理范围内，不能因为客户端传来一个很大的数字就无限制地返回数据。SQL 参数可以绑定值，但不同驱动、数据库和查询接口对可参数化位置的支持有差异，应查阅具体驱动文档。排序列名、表名这类 SQL 标识符，通常不能当作普通值参数绑定；需要动态选择排序字段时，应从固定的允许列表映射到预先写好的 SQL 片段。

六、用 JOIN 读取相关数据

将多张表关联起来，不必把重复信息复制到同一张表。下面查询订单、客户和订单明细的商品信息。连接条件要明确写出，否则容易形成笛卡尔积，让结果行数远超预期。

SELECT
    o.order_id,
    o.ordered_at,
    c.full_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
JOIN order_items AS oi
  ON oi.order_id = o.order_id
JOIN products AS p
  ON p.product_id = oi.product_id
WHERE o.status = 'paid'
ORDER BY o.ordered_at DESC, o.order_id DESC;

JOIN（也称内连接）只返回两边都匹配的行。若要保留左表中没有关联记录的行，例如列出所有客户并附带可能存在的订单，可以使用 LEFT JOIN。需要注意，若对右表字段的筛选条件放在 WHERE 中，可能会过滤掉右表为空的行，从而改变左连接的效果。是否将条件放在 ON 还是 WHERE，取决于希望保留哪些记录。

七、聚合与分组

数据库可以在服务端进行统计，避免把全部明细取到应用中再求和。以下查询计算每张已付款订单的金额：

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

GROUP BY 按字段分组，SUM、COUNT、AVG、MIN 和 MAX 等聚合函数对每组计算结果。WHERE 在分组前过滤行；HAVING 在聚合后过滤组。这个区别很重要：将聚合条件写进 WHERE 通常是不合法的，或不能表达预期逻辑。

COUNT(*) 统计行数；COUNT(column) 不统计该列为 NULL 的行。关联查询中，连接关系也会影响计数：一条订单连接多条明细后会展开成多行。统计订单数量时，应按订单表的粒度设计查询，必要时使用 COUNT(DISTINCT o.order_id)，并确认这种去重符合业务口径。

八、索引：优化查询，也增加维护成本

主键和唯一约束通常会由 PostgreSQL 自动建立索引。外键列并不会因为声明外键就自动获得索引；若经常按订单查明细，或者删除、更新父表时需要检查大量子表记录，可评估为外键列建立索引：

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

CREATE INDEX idx_order_items_product
ON order_items (product_id);

索引可以帮助特定查询更快定位数据，但会占用磁盘空间，并增加插入、更新和删除的维护开销。复合索引的列顺序很重要：通常要结合真实的筛选条件、排序条件和数据分布来判断；不能只因为查询中出现某个字段，就盲目加索引。

对性能有疑问时，先在有代表性的数据量和真实查询上查看执行计划，例如使用 PostgreSQL 的 EXPLAIN。在评估可能实际执行查询、产生额外负载的分析选项时，应先了解其副作用。数据量很小的开发数据库上跑得快，不代表生产环境同样快。

九、参数化查询与 SQL 注入

如果把用户输入直接拼接进 SQL 字符串，输入可能改变 SQL 的语法结构。参数化查询让驱动把值作为数据传递，而不是把它当成 SQL 代码解释。下面的示例使用 psycopg 3：

import psycopg

conninfo = "postgresql://app_user:replace_me@localhost:5432/shop"

with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        email = "[email protected]"
        cur.execute(
            "SELECT customer_id, full_name "
            "FROM customers "
            "WHERE email = %s",
            (email,),
        )
        rows = cur.fetchall()

print(rows)

运行前应安装 psycopg 3，并准备好 PostgreSQL 数据库与连接账号；示例中的连接信息需要替换为实际配置。连接凭据不要硬编码进公开代码仓库，生产环境可使用受控的环境配置或密钥管理方案。不同 Python 数据库驱动的占位符格式可能不同，不应把一个驱动的写法照搬到另一个驱动。

参数化查询有助于防止输入值被解释为 SQL 语法，但它不是所有安全问题的通用解决方案。它不会自动解决越权访问、过度暴露数据、弱口令、数据库权限过宽、错误信息泄露或业务逻辑漏洞；应用仍需进行身份验证、授权和必要的输入校验。

尤其要注意，普通值参数通常只能放在值的位置，不能替代表名、列名、关键字或排序方向。例如，不能简单地把列名作为一个值参数传入 ORDER BY 并期待它变成标识符。如果排序选项来自用户，应将外部选项映射到固定白名单：

sort_columns = {
    "price": "price",
    "name": "name",
}

requested_sort = "price"
column = sort_columns.get(requested_sort, "product_id")

sql = f"""
    SELECT product_id, name, price
    FROM products
    ORDER BY {column} ASC
    LIMIT %s
"""

cur.execute(sql, (20,))

上例中通过格式化字符串插入的列名不是未经检查的用户原文，而是从固定映射中取出的受控内容；行数仍使用参数绑定。如果要让用户选择升序或降序，也应只允许固定的 ASC、DESC 选项，而不是直接拼接任意输入。更复杂的动态 SQL 可使用驱动提供的标识符组合接口，并遵循其文档。

十、事务：让相关操作一起成功或失败

例如，创建订单时需要插入订单行和多条明细。如果订单创建成功而明细插入失败，留下不完整订单可能违反业务规则。可以用事务将相关修改作为一个整体：

BEGIN;

INSERT INTO orders (customer_id, status)
VALUES (1, 'new')
RETURNING order_id;

-- 应用取得上一步返回的 order_id 后，
-- 再插入该订单的明细。
-- 如果任一步骤失败，应回滚整个事务。

COMMIT;

在应用代码中，使用数据库驱动提供的事务上下文通常更方便：所有语句成功后提交；出现异常时回滚。事务应尽量短，避免在事务中执行耗时的网络请求或等待人工操作。不同数据库的隔离级别、锁行为和并发控制细节并不相同；遇到库存扣减、重复提交或并发更新等问题，需要结合具体数据库和业务流程设计，并处理可能出现的冲突或重试。

事务也不是“自动防止所有并发问题”的开关。比如先查询库存、再在应用中判断、最后更新的写法，两个并发请求可能同时读到旧库存。更可靠的方案可能是用带条件的原子更新，或在事务中使用适当的锁与隔离策略；设计前应明确允许的并发行为，并测试异常和重试路径。

十一、常见错误与检查清单

• 忘记唯一约束：在应用中先查“邮箱是否已存在”，再插入，无法阻止并发请求同时通过检查。真正必须唯一的规则应由数据库约束兜底，并在应用中友好处理冲突。
• 把空值当普通值：用 = NULL 查询不会得到预期结果；应使用 IS NULL。还要分清未知、空字符串和零值的业务含义。
• 遗漏关联条件：多表查询应逐一核对连接条件和预期行数。结果突然变多时，先检查连接是否漏写，或关联字段是否并非唯一。
• 无排序地分页：没有明确排序，结果顺序不受保证；排序字段不唯一时，还应添加稳定的唯一键。
• 直接拼接用户输入：普通值应使用参数化查询；动态标识符要采用白名单或驱动提供的安全组合接口。
• 索引越多越好：索引会增加写入成本和存储占用，应根据查询计划和实际负载评估，而不是见字段就加。
• 把数据库错误藏起来：应用可以向用户返回适当的错误信息，但应记录必要的诊断信息，同时避免在公开响应中泄露密码、连接串或内部结构。
• 直接改生产结构：结构变更应使用可审查、可追踪的迁移脚本，评估锁表、数据回填、回滚方式和兼容窗口，并先在测试环境验证。

十二、从小项目开始的实践顺序

练习时可以先在本地 PostgreSQL 数据库中创建本文的表，再按以下顺序完成一个小功能：

1. 插入客户和商品，确认主键、唯一约束和检查约束是否按预期生效。
2. 创建订单及明细，分别尝试引用不存在的客户、插入负数价格或零数量，观察数据库如何拒绝非法数据。
3. 查询订单明细并关联客户和商品，核对金额计算和返回行数。
4. 按状态统计订单，练习 WHERE、GROUP BY 与 HAVING 的区别。
5. 把查询接入应用，使用驱动支持的参数化接口，并确认异常情况下事务会回滚。
6. 准备一定规模的测试数据，再观察执行计划，按实际查询决定是否需要索引。

掌握基础 SQL 的关键，不是记住尽可能多的语法，而是建立正确的边界意识：数据规则要尽可能由数据库约束表达；查询要说清楚筛选、关联和排序条件；外部输入要作为数据安全传入；性能判断要基于执行计划和真实负载；涉及多步修改时要明确事务范围。把这些原则落实到小项目中，数据库开发就不只是“写得出 SQL”，而是能够构建可靠、可维护的数据操作。