SQL数据库入门：从建表到查询的基础操作

当应用需要保存用户、订单、库存或业务记录时，数据不仅要能写入，还要能被可靠地查找、更新和关联。SQL 数据库正适合这类结构化数据：表描述数据形状，SQL 用来定义表和操作记录，数据库负责持久化并处理约束、并发和事务。

本文以 SQLite 作为示例数据库，从建表开始，逐步完成插入、查询、更新、删除、关联查询和事务操作。示例可以在 SQLite 命令行或支持 SQLite 的图形工具中运行。SQLite 是轻量级的嵌入式数据库，不需要单独启动数据库服务，适合学习、原型、小型应用和本地存储；多用户并发写入、集中式服务、权限管理等需求，通常应评估 PostgreSQL、MySQL 等服务型数据库。

一、先认识关系型数据库

关系型数据库把数据组织在表中。表由列和行组成：列定义字段及其数据类型，行表示一条记录。比如用户表可以有用户编号、姓名和邮箱等列；每个用户对应一行。

表之间可以通过键建立关系。主键用于唯一标识一行；外键用于引用另一张表中的记录。数据库中的约束则可以限制数据，例如要求邮箱不能重复、价格不能为负数。

SQL 是操作关系型数据库的语言。常见语句大致分为几类：

• CREATE、ALTER、DROP：定义或修改表结构。
• INSERT、UPDATE、DELETE：写入、修改、删除记录。
• SELECT：读取记录。
• BEGIN、COMMIT、ROLLBACK：管理事务。

SQL 的核心语法在数据库之间有不少共通之处，但数据类型、自动编号、日期处理、分页和冲突处理等功能可能存在差异。下面的 SQL 以 SQLite 为准；换到其他数据库时，应核对对应产品的语法和行为。

二、准备 SQLite 环境

如果本机已安装 SQLite 命令行工具，可以在终端运行以下命令创建或打开数据库文件：

sqlite3 shop.db

命令执行后进入 SQLite 提示符。若提示找不到命令，需要先安装 SQLite，或使用带有 SQLite 支持的数据库客户端。也可以用 Python 标准库中的 sqlite3 模块访问数据库，无需额外安装 Python 数据库驱动。

SQLite 的外键约束需要在每个数据库连接中启用。开始操作前执行：

PRAGMA foreign_keys = ON;

这条设置针对当前连接，不应假设所有客户端或程序连接都已自动开启。后面的外键示例因此能够在同一连接中进行约束检查。

三、建表：设计字段和约束

先创建一个简单的商品表。INTEGER PRIMARY KEY 在 SQLite 中可作为行的整数主键；NOT NULL 要求字段必须提供非空值，UNIQUE 要求字段值不能重复，CHECK 则验证字段满足指定条件。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    price      NUMERIC NOT NULL CHECK (price >= 0),
    stock      INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

这里的 DEFAULT 0 表示插入时若未提供库存值，数据库使用 0。需要区分“没有提供字段”和“显式提供 NULL”：前者可以触发默认值，后者通常会违反 NOT NULL。

SQLite 的类型系统与一些服务型数据库不同。声明列类型会影响数据的存储和比较方式，但并非所有类型声明都意味着严格限制输入类型。较新版本的 SQLite 支持通过 STRICT 表进行更严格的类型检查，但可用类型和规则有所限制；部署时应确认 SQLite 版本。初学时应明确字段用途，并在应用和数据库约束两侧验证数据。

再创建订单表。订单必须属于一个商品，商品编号通过外键引用商品表的主键：

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    status      TEXT NOT NULL DEFAULT 'pending',
    created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CURRENT_TIMESTAMP 在 SQLite 中可用于生成当前 UTC 时间的文本值。实际项目要统一时间存储规则，例如统一存 UTC、在展示时转换时区，并明确精度要求。日期时间的存储与函数在不同数据库间并不完全一致。

外键让数据库拒绝引用不存在商品的订单。但它不会自动保证库存足够，也不会自动扣减库存；这些业务规则需要通过事务中的 SQL 操作或应用逻辑实现。外键启用与否也会影响约束是否实际生效，因此不能只看到建表语句里写了外键就认为检查一定开启。

四、插入记录：指定列名更可靠

使用 INSERT INTO 添加记录。建议显式写出列名，不要依赖表中列的排列顺序：

INSERT INTO products (name, price, stock)
VALUES ('机械键盘', 399.00, 20);

INSERT INTO products (name, price, stock)
VALUES ('USB-C 转接器', 59.90, 50);

INSERT INTO products (name, price)
VALUES ('笔记本支架', 129.00);

第三条语句没有提供 stock，因此采用默认值 0。字符串通常用单引号括起；如果字符串内部包含单引号，需要按 SQL 规则转义，例如 'user''s item'。不要把未处理的用户输入直接拼接进 SQL 字符串，后文会介绍参数化查询。

可以一次插入多行：

INSERT INTO products (name, price, stock)
VALUES
    ('显示器支架', 199.00, 12),
    ('无线鼠标', 89.00, 30);

若再次执行相同的插入语句，可能因为唯一约束而失败，或者形成重复记录，取决于表结构。自动编号主键适合标识记录，但并不意味着每次插入都能安全重试。重复提交、网络重试等场景应设计明确的幂等策略，例如使用业务唯一键和数据库约束。

五、查询记录：SELECT、WHERE 与排序

最简单的查询是读取整张表：

SELECT product_id, name, price, stock
FROM products;

SELECT * 可以读取所有列，适合临时探索数据；在应用代码和长期维护的查询中，通常明确列名更稳妥。这样可以减少不必要的数据传输，也不容易因表结构新增列而改变程序预期。

使用 WHERE 筛选行：

SELECT name, price
FROM products
WHERE price < 150;

多个条件可以用 AND、OR 组合。需要混合使用时，使用括号明确优先级：

SELECT product_id, name, stock
FROM products
WHERE stock > 0
  AND (price < 100 OR name = '机械键盘');

SQL 中判断空值要用 IS NULL 或 IS NOT NULL，不能写 = NULL。NULL 表示未知或缺失，不等同于数字 0、空字符串或“否”。涉及 NULL 的比较结果可能是未知，因此筛选时要显式考虑空值。

ORDER BY 指定排序。没有排序条件时，数据库不保证结果顺序：

SELECT name, price
FROM products
ORDER BY price DESC, name ASC;

DESC 表示降序，ASC 表示升序。需要稳定分页时，排序条件应尽量包含唯一字段作为最后的排序键，否则价格相同的记录之间没有确定顺序。

SQLite 支持用 LIMIT 控制返回行数：

SELECT product_id, name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

LIMIT 和分页写法并非所有数据库都相同。大偏移量分页可能需要数据库扫描并跳过大量行；数据量较大时，可考虑基于上一页最后一条记录的游标式分页。具体方案取决于排序字段、索引和数据库产品。

六、聚合：统计、分组与过滤组

聚合函数可对多行数据计算一个结果。常用函数包括 COUNT、SUM、AVG、MIN 和 MAX：

SELECT
    COUNT(*) AS product_count,
    AVG(price) AS average_price,
    MIN(price) AS cheapest_price,
    MAX(price) AS highest_price
FROM products;

COUNT(*) 统计行数；COUNT(column) 统计该列非 NULL 的值。SQLite 中 AVG 等聚合函数会忽略 NULL。读结果时应考虑空表和 NULL 对统计结果的影响。

使用 GROUP BY 按字段分组。例如统计各商品状态下的订单数量：

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

HAVING 用于筛选分组后的结果，WHERE 则先筛选原始行。下面只保留订单数量至少为两笔的状态：

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
HAVING COUNT(*) >= 2;

七、修改和删除：先确认范围

用 UPDATE 修改已有记录：

UPDATE products
SET price = 379.00
WHERE product_id = 1;

执行前应确认 WHERE 条件能选中预期记录。遗漏 WHERE 会更新整张表，而不是报错。可以先用相同条件运行 SELECT 检查目标范围，再执行更新：

SELECT product_id, name, price
FROM products
WHERE product_id = 1;

删除记录使用 DELETE：

DELETE FROM products
WHERE product_id = 3;

同样，省略 WHERE 会删除表中所有行。删除商品时，如果已有订单引用它，启用外键后默认行为通常会阻止删除；这是为了避免留下无效引用。业务上需要保留订单历史时，可采用软删除标记、归档策略，或为外键明确设计级联行为。使用级联删除前，必须确认它不会连带删除仍需保留的数据。

不要把 DELETE FROM 表名 与 DROP TABLE 表名 混为一谈：前者删除表里的行，后者删除表结构及其中数据，影响范围完全不同。

八、关联查询：JOIN 与外键

订单表只存商品编号，商品名称和价格仍在商品表中。通过连接查询，可以把两张表中有关联的行组合起来：

SELECT
    orders.order_id,
    products.name AS product_name,
    orders.quantity,
    orders.status,
    orders.created_at
FROM orders
JOIN products
    ON orders.product_id = products.product_id
ORDER BY orders.order_id;

JOIN 默认指内连接（INNER JOIN）：只有连接条件两侧都匹配的行会出现在结果中。若要保留左表全部记录，即使右表没有匹配项，也可以使用 LEFT JOIN。在有外键约束的表中，合法订单应当有对应商品，但其他没有外键保护的数据仍可能出现不匹配。

表较多时，用别名让 SQL 更简洁：

SELECT
    o.order_id,
    p.name AS product_name,
    o.quantity
FROM orders AS o
JOIN products AS p
    ON o.product_id = p.product_id;

注意连接条件要写对。若遗漏 ON 条件，或者条件错误地匹配了无关字段，可能生成大量组合行，结果数目远超预期。排查时可以先单独查询两张表，再检查关联字段和主外键关系。

九、索引：加速查询，也有成本

索引帮助数据库更快定位符合条件的行。主键通常已经有相应索引；对于经常按订单状态筛选或按创建时间排序的场景，可以评估增加索引：

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

多列索引的列顺序会影响可用的查询方式。上面的索引对以 status 为前导条件的查询更有帮助，但是否能优化某条 SQL，还要结合数据库的查询计划、数据分布和工作负载判断。SQLite 可以用 EXPLAIN QUERY PLAN 查看查询计划：

EXPLAIN QUERY PLAN
SELECT order_id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

索引并非越多越好。它会占用存储空间，并增加插入、更新、删除时的维护成本。应针对常见且有实际性能问题的查询设计索引，而不是给每一列都建一个索引。索引也不能替代合理的查询条件和数据建模。

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

事务适用于必须保持一致的一组操作。例如创建订单时，需要插入订单并扣减库存。如果订单创建成功、库存却没有相应变化，数据就不一致。事务可以将相关操作作为一个整体提交，出错时回滚：

BEGIN;

INSERT INTO orders (product_id, quantity, status)
VALUES (1, 2, 'pending');

UPDATE products
SET stock = stock - 2
WHERE product_id = 1
  AND stock >= 2;

COMMIT;

这个示例展示了事务边界，但直接执行仍需检查更新是否影响了一行。如果库存不足，更新会影响零行，而前面的订单插入仍然存在；应用必须检测更新行数，并在库存不足时执行 ROLLBACK，而不是提交。较可靠的业务流程是先开启事务、插入订单、执行带库存条件的更新、检查受影响行数；任一步失败都回滚，全部成功才提交。

事务提供原子性等保证，但具体隔离级别、锁行为和并发能力因数据库而异。SQLite 支持事务，但它是嵌入式数据库，写入并发特性与服务型数据库不同。高并发写入场景应通过实际负载测试确认是否合适。

十一、应用程序中使用参数化查询

在程序里执行 SQL 时，用户输入应作为参数传递，而不是拼进 SQL 字符串。以 Python 的标准库 sqlite3 为例：

import sqlite3

connection = sqlite3.connect("shop.db")
connection.execute("PRAGMA foreign_keys = ON")

name = "便携键盘"
price = 249.00
stock = 15

connection.execute(
    "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
    (name, price, stock),
)

rows = connection.execute(
    "SELECT product_id, name, price FROM products WHERE price < ?",
    (300,),
).fetchall()

for row in rows:
    print(row)

connection.commit()
connection.close()

SQLite 的问号占位符代表参数位置，参数由驱动单独绑定。不要自己在参数值外面添加引号，也不要把参数值通过字符串拼接塞进 SQL。占位符风格取决于数据库驱动，不同语言和数据库可能使用问号、命名参数或其他形式；应按驱动文档使用。

参数化查询的安全用途是把数据值与 SQL 语法分开，防止数据被当作 SQL 代码解释，从而避免常见的 SQL 注入问题。它不等同于完整的应用安全方案：仍需进行权限控制、输入校验、错误处理和敏感数据保护。

参数通常只能绑定数据值，不能直接替代表名、列名或排序方向。例如，不能期望把 ORDER BY ? 中的参数值当作列名使用。若表名、列名或排序方向必须动态变化，应从程序内预先定义的允许列表中选择，再组合 SQL；不能将任意用户输入直接拼成标识符。参数化查询也不会自动判断业务输入是否合理，例如价格是否为负数仍应由业务规则和数据库约束共同处理。

十二、常见错误与排查方法

• 更新或删除范围过大：检查是否漏写 WHERE，执行前用相同条件运行 SELECT。重要操作可在事务中执行，确认结果后再提交。
• 把 NULL 当作普通值比较：使用 IS NULL 和 IS NOT NULL，不要写 = NULL。
• 误以为查询结果默认有序：需要稳定顺序时显式写 ORDER BY，分页时加入能打破并列的排序键。
• 使用字符串拼接 SQL：改用驱动提供的参数绑定。参数绑定值，不绑定表名或列名。
• 忽略外键开关：SQLite 连接需要启用外键约束；确认应用的每个连接都执行了相应设置。
• 字段类型和输入不匹配：不要假设所有数据库都会像严格类型系统一样自动拒绝不合适的值；为关键数据使用约束，并在应用层校验。
• 不检查更新影响行数：库存扣减、状态转换等操作应验证实际影响的行数，避免把“没有更新成功”误当成成功。
• 滥用索引或过早优化：先确认查询瓶颈，再查看执行计划和实际数据量；索引会带来写入和存储成本。
• 混淆数据库方言：不同产品在自动编号、日期函数、分页、冲突处理和类型规则方面可能不同。迁移时逐条验证，不要假定 SQLite 示例可原样用于其他数据库。

十三、从示例走向实际项目

掌握 SQL 基础后，实际建模还要回答几个问题：哪些字段允许为空，哪些值必须唯一，记录删除后是否需要保留历史，金额精度如何表示，时间按什么时区保存，数据增长后哪些查询需要索引。这些没有脱离业务的通用答案，应结合数据含义和访问模式决定。

金额字段尤其需要谨慎。不同数据库对浮点数和定点数的处理不同；浮点数可能存在二进制表示误差。涉及账务时，应根据数据库能力和业务精度要求选择合适的定点类型，或以最小货币单位整数保存，并明确币种和舍入规则。不要仅因为示例使用了 NUMERIC，就推断所有数据库都以相同方式存储和计算金额。

备份与恢复也不能省略。数据库文件存在本地，并不等于已经有备份。正式使用时应制定备份频率、保留周期和恢复演练方案；对服务型数据库，还应了解其权限、日志、监控和故障恢复能力。

总结

SQL 的基本工作流可以概括为：根据业务设计表和约束，用 INSERT 写入数据，用 SELECT 筛选和关联数据，用 UPDATE 与 DELETE 谨慎修改记录，用事务保证一组操作的一致性，再根据真实查询需求评估索引。

入门阶段最值得养成的习惯是：明确查询范围、显式列出字段、使用参数化查询、理解 NULL 和外键、在关键业务操作中检查影响行数，并记住数据库方言和参数规则并非处处相同。沿着这些原则练习，便可以从几张简单的表开始，逐步构建可靠的数据操作流程。