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

需要保存一批结构清晰、彼此有关联的数据时，关系型数据库通常是可靠的选择。SQL（结构化查询语言）用于定义和操作这类数据库：你可以用它创建表、写入记录、筛选结果，也可以通过约束和事务减少数据错误。

本文以 SQLite 为例，从建表和插入数据开始，逐步介绍常用查询、更新与删除、表连接、聚合、事务和安全的参数化写法。示例采用 SQLite 支持的语法，适合本地练习、小型应用和原型开发。PostgreSQL、MySQL 等数据库的基本概念相通，但数据类型、函数、自动编号、分页写法和管理方式并不完全相同，实际使用时应查阅对应数据库的文档。

一、SQL 数据库适合解决什么问题

关系型数据库擅长管理有明确结构、需要一致性和关联查询的数据。例如：订单与商品、学生与课程、用户与地址、库存与出入库记录。数据可以按主题拆分成多张表，再通过键关联。数据库负责保存数据、执行查询，并在约束和事务的帮助下维护数据规则。

如果数据主要是大量彼此独立的文件、结构经常变化，或需要跨大量机器处理海量日志，关系型数据库未必是唯一合适的工具。不过，不能只因数据“很大”或“格式不固定”就排除 SQL：许多关系型数据库支持扩展数据类型、全文检索和分区等能力，是否合适应根据查询方式、一致性要求、规模和运维条件判断。

二、准备 SQLite 环境

SQLite 是嵌入式关系型数据库。它通常把数据库保存在一个文件中，不需要单独启动数据库服务器，适合学习、桌面软件、移动应用和轻量级服务。它也支持 SQL、事务、索引和约束，但并不等同于一台可供大量客户端并发写入的数据库服务器。

可以使用系统提供的 sqlite3 命令行工具，也可以用 Python 自带的 sqlite3 模块运行后文示例。若要使用命令行，先创建或打开数据库文件：

sqlite3 shop.db

进入交互界面后，可执行 SQL 语句。每条语句通常以分号结束。以下示例围绕商品和订单展开；在命令行中可以逐段运行。SQLite 默认不会为每个连接自动启用外键检查，因此示例会显式打开该功能。

PRAGMA foreign_keys = ON;

这项设置作用于当前连接，不是对数据库文件永久生效的全局开关。应用程序每次创建连接时都应按需要设置。下面的 Python 示例也会显式开启外键检查。

三、表、行、列和键

关系型数据库把数据组织为表。表由列（字段）和行（记录）组成；每一列描述一种属性，例如商品名称、价格或库存数量。主键用于唯一标识一行，外键则用于表达表与表之间的关联。

设计表时，先明确每列代表什么、允许什么值，以及一条记录怎样被唯一识别。尽量让每列只保存一种含义：例如把收件人姓名和联系电话分别存储，而不是拼成一个字符串。合理拆分能让筛选、校验和更新更直接。

四、创建表并定义约束

下面创建两张表：products 保存商品，orders 保存订单。订单通过 product_id 引用商品主键。

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

CREATE TABLE orders (
    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(id)
);

这里使用整数“分”保存价格，避免使用二进制浮点数直接表示金额时出现精度误差。真实业务还需考虑币种、税费和舍入规则；不同场景也可能采用数据库的定点小数类型。

• INTEGER、TEXT 是 SQLite 常见的数据类型名称。SQLite 的类型系统与 PostgreSQL、MySQL 等数据库有所不同；其他数据库可能需要使用 VARCHAR、DECIMAL、TIMESTAMP 等类型。
• PRIMARY KEY 声明主键。此处的 INTEGER PRIMARY KEY 在 SQLite 中会与行标识机制关联；若在其他数据库中需要自动生成主键，应使用该数据库对应的语法，不能直接假定各库都支持同一种写法。
• NOT NULL 禁止该列取空值，DEFAULT 提供默认值，CHECK 用于限制列值或行值，FOREIGN KEY 则定义引用关系。

约束能拦截违反规则的数据，但不能代替完整的业务逻辑。例如，CHECK (stock >= 0) 可以阻止库存为负，却不能单独解决两个并发请求同时扣减库存的问题。涉及多步变更时，还需要事务和适当的并发控制。

五、插入数据：INSERT

插入记录时，建议明确写出列名。这样即使表结构后来增加了列，已有插入语句也更容易理解，不会因为列顺序变化而误写数据。

INSERT INTO products (name, price_cents, stock)
VALUES ('机械键盘', 39900, 12);

INSERT INTO products (name, price_cents, stock)
VALUES
    ('无线鼠标', 12900, 30),
    ('显示器支架', 25900, 8);

由于 id 是主键且没有在语句中指定，SQLite 会为其生成值。orders 表中的 created_at 和 status 也可以使用默认值：

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

如果显式插入不存在的 product_id，在当前连接启用外键检查的前提下，SQLite 会拒绝这条记录。不要把“数据库拒绝无效值”的行为寄托在应用界面上：其他程序、脚本或接口也可能写入同一数据库。

六、查询数据：SELECT

1. 选择列与筛选记录

SELECT 用于读取数据。下面读取商品名称和价格，并用 WHERE 筛选库存大于零的商品：

SELECT name, price_cents
FROM products
WHERE stock > 0;

开发时常见的 SELECT * 会返回表中所有列：

SELECT *
FROM products;

它适合快速检查数据，但不宜无条件用于长期维护的应用查询。列过多会增加传输和读取成本，表结构变化也可能影响调用方。正式查询通常应列出实际需要的字段。

2. 比较、范围和组合条件

WHERE 支持比较运算符，如 =、<>、>、>=。多个条件可以用 AND、OR 组合；需要时用括号明确优先级。

SELECT id, name, price_cents, stock
FROM products
WHERE price_cents >= 10000
  AND stock > 0;

SELECT id, name
FROM products
WHERE stock = 0 OR price_cents > 50000;

范围查询可用 BETWEEN。在 SQL 中，BETWEEN a AND b 通常包含两端边界：

SELECT name, price_cents
FROM products
WHERE price_cents BETWEEN 10000 AND 30000;

筛选一组值可以用 IN：

SELECT id, name
FROM products
WHERE id IN (1, 3, 5);

3. 空值、去重和模式匹配

SQL 的 NULL 表示缺失或未知值，它不等同于数字 0、空字符串或布尔值“否”。与 NULL 比较时，不要写 column = NULL，而要使用 IS NULL 或 IS NOT NULL。

SELECT id, name
FROM products
WHERE name IS NOT NULL;

DISTINCT 用于去掉结果中的重复行：

SELECT DISTINCT status
FROM orders;

LIKE 可以做简单的字符串模式匹配：% 表示任意长度的字符，_ 表示单个字符。

SELECT id, name
FROM products
WHERE name LIKE '%鼠标%';

具体数据库对大小写、字符集和排序规则的处理可能不同。若把用户输入直接放进 LIKE 模式，即便使用参数化查询，输入中的 % 和 _ 仍会作为通配符；这属于匹配语义问题，不是 SQL 注入。若产品要求按字面搜索，需要转义这些通配符，并在 SQL 中使用相应的 ESCAPE 规则。

4. 排序与限制返回数量

ORDER BY 设置结果顺序；ASC 为升序，DESC 为降序。若查询依赖某种稳定顺序，应明确指定排序条件，因为数据库没有义务按插入顺序返回结果。

SELECT id, name, price_cents
FROM products
ORDER BY price_cents DESC, id ASC
LIMIT 10;

SQLite 支持 LIMIT，但分页语法在数据库之间存在差异。使用偏移分页时要提供确定的排序条件；如果排序列有重复值，可再加唯一键作为次级排序条件，否则翻页时记录可能出现重复或遗漏。对于不断增长的大表，基于上一页最后一条记录的“游标分页”通常比很大的偏移量更适合，但写法取决于排序字段。

七、修改和删除数据

UPDATE 修改已有记录。执行前先确认筛选条件；如果漏掉 WHERE，语句会修改表中的全部记录。

UPDATE products
SET price_cents = 38900
WHERE id = 1;

以下语句会调整指定商品的库存。把计算放在数据库语句中，可避免先读出旧值、再写回时覆盖期间发生的变化；不过是否满足业务并发要求，还要结合事务、隔离级别和数据库行为判断。

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

执行后应检查影响行数：如果是零行，可能是商品不存在，也可能是库存不足。不要仅凭“语句没有报错”就认定业务操作成功。

DELETE 删除记录，同样要谨慎使用 WHERE：

DELETE FROM orders
WHERE id = 7;

删除被其他表引用的商品时，外键约束可能会拒绝操作。是否采用级联删除、软删除或禁止删除，应依据业务规则明确设计；不要为了让删除语句“成功”而随意关闭外键检查。

八、关联查询：JOIN

订单表只存储商品编号，没有重复保存商品名称和单价。查询时可以使用 JOIN 将两张表按键关联。显式写出连接条件，比依赖不直观的隐式关联更清楚。

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

JOIN（也称 INNER JOIN）只返回连接条件匹配的行。若希望保留左表中的所有行，即使右表没有匹配记录，可以使用 LEFT JOIN；结果中没有匹配的右表字段会是 NULL。

SELECT p.id, p.name, o.id AS order_id
FROM products AS p
LEFT JOIN orders AS o
    ON o.product_id = p.id
ORDER BY p.id, o.id;

不要忘记写连接条件。若把两张表简单地放在 FROM 中而没有适当关联，可能产生笛卡尔积：每一行都与另一张表的每一行组合，结果数量会迅速膨胀。多表查询还要确认关联键是否唯一；一对多连接会让左表的同一条记录在结果中出现多次，这是关系本身造成的，不一定是重复数据。

九、统计和分组：聚合函数

常见聚合函数包括 COUNT、SUM、AVG、MIN 和 MAX。GROUP BY 按指定列分组，HAVING 则筛选聚合后的分组。

SELECT
    status,
    COUNT(*) AS order_count,
    SUM(quantity) AS total_quantity
FROM orders
GROUP BY status
HAVING COUNT(*) >= 1
ORDER BY order_count DESC;

WHERE 在分组前筛选行，HAVING 在分组后筛选组。写统计查询时，应检查选择列表中的非聚合列是否都符合数据库对分组查询的要求；不同数据库对此可能有不同的宽松程度。依赖宽松行为可能让查询在换库后失败，或返回不确定的列值。

还要留意 COUNT(*) 与 COUNT(column) 的差异：前者统计行数，后者不统计该列为 NULL 的行。使用 LEFT JOIN 统计关联记录时，计数方式也会影响结果；应根据要统计的对象选择表达式，并用小型数据集验证边界情况。

十、索引：让常用查询更高效

索引是数据库为某些列维护的额外查找结构，常用于加速过滤、排序和连接。代价是占用存储空间，并让插入、更新和删除需要维护索引。索引不是越多越好，应结合实际查询和执行计划选择。

CREATE INDEX idx_orders_product_id
ON orders(product_id);

SQLite 为主键和唯一约束创建相应索引结构；外键引用列本身不会因为声明外键就必然自动获得适合查询的索引，因此经常用于连接或检查的子表外键列通常值得评估是否建索引。具体索引设计应以查询模式和数据规模为依据。

可以用 EXPLAIN QUERY PLAN 查看 SQLite 对查询的计划：

EXPLAIN QUERY PLAN
SELECT id, quantity
FROM orders
WHERE product_id = 1;

执行计划用于分析数据库可能采用的访问方式，不是性能保证。数据量、统计信息、数据库版本和硬件都会影响结果。小表即使没有索引，扫描全表也可能更简单、更快。

十一、事务：把多步操作作为一个整体

事务用于把相关操作组织为一个整体：成功时提交，失败时回滚。比如创建订单时，既要插入订单，也要扣减库存；只完成其中一步会留下不一致的数据。

BEGIN;

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

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

COMMIT;

上面的演示还不够安全：若库存不足，UPDATE 可能影响零行，但前面的订单仍可能被提交。实际程序必须检查更新行数；若更新未成功，应回滚整个事务。更可靠的做法是由应用代码统一处理异常和提交状态：

import sqlite3

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

try:
    with con:
        cur = con.execute(
            "UPDATE products "
            "SET stock = stock - ? "
            "WHERE id = ? AND stock >= ?",
            (2, 1, 2),
        )
        if cur.rowcount != 1:
            raise ValueError("商品不存在或库存不足")

        con.execute(
            "INSERT INTO orders (product_id, quantity) VALUES (?, ?)",
            (1, 2),
        )
finally:
    con.close()

在 Python 的 sqlite3 中，连接的上下文管理器会在代码块正常结束时提交事务，在抛出异常时回滚事务；它本身不会替你关闭连接，因此示例使用 finally 关闭连接。连接默认的事务行为可能受 Python 版本及连接参数影响，生产代码应查阅所用版本的文档，并明确管理连接和事务。

十二、参数化查询：避免把输入拼进 SQL

来自用户、请求或文件的数据不能直接拼接进 SQL 字符串。错误做法如下：

user_name = input("请输入商品名：")
sql = "SELECT id, name FROM products WHERE name = '" + user_name + "'"
# 不要这样执行

如果输入中包含引号或 SQL 语法，拼接后的语句可能被改变；这会导致 SQL 注入。正确做法是使用数据库驱动提供的参数占位符，把 SQL 结构与数据分开传递：

import sqlite3

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

name = "机械键盘"
rows = con.execute(
    "SELECT id, name, price_cents FROM products WHERE name = ?",
    (name,),
).fetchall()

for row in rows:
    print(row)

con.close()

SQLite 的 Python 驱动使用 ? 作为位置参数占位符，也支持命名参数。元组只有一个元素时必须保留末尾逗号，即 (name,)。参数值应单独传给执行方法；不要先把值格式化进 SQL 字符串再执行。

rows = con.execute(
    "SELECT id, name FROM products "
    "WHERE price_cents >= :minimum "
    "ORDER BY price_cents",
    {"minimum": 10000},
).fetchall()

参数占位符用于数据值，不能用来替代表名、列名、关键字或排序方向。例如下面的写法不会把参数当作列名来解释：

column = "name"
# 不能这样让 ? 代表列名
# con.execute("SELECT ? FROM products", (column,))

若表名或排序字段必须由用户选择，应把输入映射到程序预先允许的标识符列表，再将合法选项写入 SQL 结构；不要把任意输入直接拼进标识符位置。

allowed_columns = {
    "name": "name",
    "price": "price_cents",
    "stock": "stock",
}

sort_key = "price"
column = allowed_columns.get(sort_key)

if column is None:
    raise ValueError("不支持的排序字段")

sql = f"SELECT id, name, price_cents FROM products ORDER BY {column}"
rows = con.execute(sql).fetchall()

这里的字符串插值只用于从程序控制的白名单中选出标识符；外部输入并未直接进入 SQL。参数化查询是防止 SQL 注入的重要手段，但不负责授权、访问控制、业务校验、输出编码或数据加密。应用还应按需要限制数据库文件和连接的访问权限，避免泄露敏感信息，并对写操作和错误进行审计与处理。

十三、常见错误与排查思路

• 把字符串当成数字，或把数字当成字符串。 使用与字段语义相符的值，并明确价格单位。SQLite 的类型处理与强类型数据库并不完全相同，不能据此假定其他数据库会自动完成相同的转换。
• 把 NULL 当作普通值比较。 使用 IS NULL、IS NOT NULL；理解聚合函数、连接和条件表达式对空值的影响。
• 更新或删除时遗漏 WHERE。 执行前先用相同条件运行 SELECT 检查目标行；重要变更可放在事务中，并检查影响行数。
• 忘记连接条件。 检查 JOIN ... ON 中的键是否正确，留意一对多关联导致的行数增加。
• 认为结果天然有序。 只有明确指定 ORDER BY 才能依赖排序；分页时尤其要使用稳定且足以区分记录的排序条件。
• 把输入拼进 SQL。 对数据使用参数化查询；动态表名和列名使用严格的白名单，而非普通值参数。
• 误以为声明外键就一定在所有连接中生效。 SQLite 需要对连接启用外键检查。还要确认应用实际使用的连接和数据库文件与测试时一致。
• 频繁创建索引却不验证效果。 索引会带来写入和存储成本。先识别慢查询，再查看执行计划并用接近真实的数据进行测试。
• 把事务写成只提交、不处理失败。 多步写操作应明确失败时的回滚路径。仅在程序中“先查库存再扣库存”，不能自动消除并发情况下的竞争。
• 期待不同数据库完全兼容。 SQL 核心语法相通，但自动编号、时间函数、分页、类型、冲突处理、事务细节和驱动占位符都可能不同。迁移时要逐项验证。

十四、SQLite 使用边界与日常维护

SQLite 不需要独立服务器，部署简单、读操作能力也适合许多应用。它的写入并发与客户端—服务器数据库不同：多个连接可以并发读取，但写入会受到锁和事务机制约束。是否适合高并发写入、多人远程访问或复杂权限管理，取决于应用形态和负载，不能仅凭数据库文件“小”就判断。

如果数据库放在网络文件系统上、多个进程频繁同时写入，或需要集中管理用户权限、远程连接和复杂的高可用方案，应认真评估部署方式，必要时采用 PostgreSQL、MySQL 等服务器型数据库。无论选择哪种产品，都要针对实际负载测试读写性能，并设计备份、恢复和升级流程。

数据库文件也需要备份。直接复制正在使用的文件在某些情况下可能无法得到一致的备份；SQLite 提供备份 API，命令行工具也有相应的备份能力。应按所用工具的文档实施备份，并实际演练恢复。没有验证过恢复过程的备份，不能视为已经具备可靠的恢复能力。

十五、从入门到可维护查询

日常写 SQL 可以按这样的顺序思考：先确定数据分别属于哪些表，再定义主键、外键和必要约束；写查询时先明确要返回的列和筛选条件，再决定是否需要连接、分组和排序；涉及输入时使用参数化查询；涉及多步写操作时使用事务并检查影响行数；遇到性能问题时再查看执行计划、评估索引和数据规模。

SQL 的基础语法并不复杂，难点往往在于数据规则是否定义清楚，以及边界情况是否经过验证。先用小数据集运行查询，检查空值、重复值、无匹配关联和失败操作的处理方式，再把语句放入应用代码。掌握这些基本习惯，就能从“写出能运行的 SQL”，逐步走向可靠、可维护的数据操作。