SQL 与 SQLite 基础

一、为什么学习数据库？

CSV 很适合交换数据，但当你要按学号查询、关联学生与成绩、拒绝无效数据并保证一组修改一起成功时，数据库会更合适。SQL 是描述查询和修改的语言；SQLite 是可以直接嵌入程序、常用单文件保存的关系数据库。

本篇用虚构学生和成绩数据练习，不需要搭建服务器。先修知识为基本 Python；目标是能设计两张表、完成增删改查和 JOIN，并用参数绑定和事务保护数据。

二、表、行、列、主键与外键

概念	含义	例子
表	同类记录的集合	students
行	一条记录	学号 1 的学生
列	字段与其约束	name、team
主键	唯一标识一条记录	students.id
外键	指向另一张表的关联键	scores.student_id
NULL	缺失或未知	未提交成绩

不要把学生姓名当唯一标识；同名很常见。学生信息与课程成绩分表，可以避免每增加一门课就新增 score_1/score_2 等列。成绩表中 (student_id, subject) 组成联合主键，表示每人每门课最多一条成绩；若需要补考历史，应进一步引入考试批次。

三、准备与运行方式

Python 标准库通常包含 sqlite3，不需要安装名为 sqlite3 的第三方包：

python -c "import sqlite3; print(sqlite3.sqlite_version)"

sqlite3.connect(":memory:") 创建内存数据库，连接关闭后数据消失；sqlite3.connect("school.db") 使用当前工作目录下的文件。正式修改文件前先做备份；本教程完整实验使用内存数据库，可重复运行而不会覆盖既有数据。

四、创建表与约束

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  team TEXT NOT NULL CHECK(team IN ('A', 'B'))
);
CREATE TABLE scores (
  student_id INTEGER NOT NULL REFERENCES students(id),
  subject TEXT NOT NULL,
  score REAL NOT NULL CHECK(score BETWEEN 0 AND 100),
  PRIMARY KEY(student_id, subject)
);

• NOT NULL 拒绝缺失；CHECK 限制取值；PRIMARY KEY 保证标识唯一。
• SQLite 常见存储类别有 NULL、INTEGER、REAL、TEXT、BLOB。普通表采用类型亲和性，不能因为声明了整数就假定所有输入都会被严格拒绝；更严格的模型可进一步了解 STRICT 表及其版本条件。
• 每条连接应明确执行 PRAGMA foreign_keys = ON，不能依赖其他连接已经启用。
• 日期可约定存为 ISO 文本，例如 2026-09-01。数据库不会自动替你定义时区或业务日期语义。

五、插入数据：值用参数绑定

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.execute("CREATE TABLE students(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
with con:
    con.executemany("INSERT INTO students VALUES (?, ?)",
                    [(1, "Lin"), (2, "Chen")])
print(con.execute("SELECT name FROM students WHERE id = ?", (1,)).fetchall())
con.close()

输出 [('Lin',)]。单参数元组中的逗号不能省略。不要写 f"... WHERE name='{user_input}'"：输入中的引号可能破坏语法或改变语句含义。? 绑定的是值，不能拿来绑定表名；动态表名应来自你自己定义的允许列表。

六、查询、筛选、排序与分页

以下 SQL 使用第四节的双表结构，完整数据可从本篇下载脚本中获得：

SELECT student_id, score
FROM scores
WHERE subject = 'Python' AND score >= 80
ORDER BY score DESC, student_id ASC
LIMIT 10 OFFSET 0;

SQL 书写顺序不等于逻辑处理顺序。先根据 FROM/JOIN 确定数据，再筛选和分组，最后形成输出、排序和限制行数：

没有 ORDER BY 就不应依赖返回顺序；成绩相同时加学号作为第二排序键，使分页更稳定。大量数据的深 OFFSET 分页可能很慢，可以进一步学习基于游标的分页。

七、聚合、GROUP BY、HAVING 与 NULL

SELECT subject, COUNT(*) AS records, AVG(score) AS average_score
FROM scores
GROUP BY subject
HAVING COUNT(*) >= 2;

WHERE 筛选分组前的行，HAVING 筛选聚合后的组。COUNT(*) 统计行数；COUNT(score) 统计 score 非 NULL 的行；AVG(score) 忽略 NULL。没有有效成绩时平均值是 NULL，不是自动得到 0。

判断空值用 IS NULL 或 IS NOT NULL，不要写 = NULL。不要为了避免空值就把所有缺失成绩填成 0，否则会把“未参加”误算成“考了零分”。

八、JOIN：把两张表连接起来

SELECT s.id, s.name, x.score
FROM students AS s
LEFT JOIN scores AS x
  ON s.id = x.student_id AND x.subject = 'Python'
ORDER BY s.id;

INNER JOIN 只返回匹配行；LEFT JOIN 保留所有左表学生，没有 Python 成绩时右侧字段为 NULL。将 x.subject='Python' 放到 WHERE 中会排除未匹配行，这常常无意中改变了保留全部学生的目标。

完整实验中的学生为 Lin(A)、Chen(A)、Wang(B)、Zhao(B)，Python 成绩依次为 80、90、70、缺失：

组别	已有成绩人数	AVG(score)
A	2	85.0
B	1	70.0

这个结果不是“B 组所有学生平均 70 分”，而是“B 组有成绩的 1 人平均 70 分”。先说分母，再解释结论。

九、UPDATE、DELETE 与事务

UPDATE scores SET score = 88 WHERE student_id = 4 AND subject = 'Python';
DELETE FROM scores WHERE student_id = 4 AND subject = 'Python';

没有 WHERE 的 UPDATE/DELETE 作用于全表。修改前用同样的条件执行 SELECT 核对目标；UPDATE 不会自动插入不存在的记录。

事务把一组修改作为整体提交或回滚：

try:
    with con:
        con.execute("INSERT INTO scores VALUES (?, ?, ?)", (4, "Python", 88))
        con.execute("INSERT INTO scores VALUES (?, ?, ?)", (99, "Python", 80))
except sqlite3.IntegrityError:
    print("包含不存在的学生，整组写入回滚")

本例假设默认连接事务控制、表已建好、学生 4 存在而 99 不存在。异常必须传播出 with con 才能触发此上下文管理器的回滚；在块内吞掉异常可能提交前面已成功的操作。with con 管事务，不会替你关闭连接。

十、索引与查询计划

CREATE INDEX idx_scores_subject ON scores(subject);
EXPLAIN QUERY PLAN
SELECT student_id FROM scores WHERE subject = 'Python';

索引类似目录，可能减少读取，但占空间且增加写入维护成本。不要给每列都建立索引；查看查询计划并在代表性数据上测量。小表可能被优化器选择全表扫描，不代表索引“坏了”。联合主键已有相应索引，不要盲目重复创建。

SQLite 适合本地应用、教学和许多轻量服务；多用户密集并发写入、复杂服务运维时，需要重新评估部署与数据库选型。不要把它理解为可任意并发写入的共享文件。

十一、完整实验

下载 sqlite_demo.py，运行：

python sqlite_demo.py

team, graded students, average: [('A', 2, 85.0), ('B', 1, 70.0)]
invalid foreign key: transaction rolled back

脚本创建两张表，参数化插入、执行 LEFT JOIN 聚合，再故意触发外键错误，断言回滚后记录数未变。所有数据均为虚构。

十二、练习与参考思路

1. 查询没有 Python 成绩的学生。参考：LEFT JOIN 后检查 x.student_id IS NULL。
2. 插入同一学生同一科目的第二条成绩，解释异常。参考：联合主键冲突；应先定义覆盖还是保存历史。
3. 将 120 分插入成绩表，验证 CHECK 是否拒绝。
4. 把分组查询中的 COUNT(x.score) 改为 COUNT(*)，解释 B 组为何从 1 变成 2。
5. 小项目：设计设备与借用表，实现借出、归还和逾期查询；把日期字段、同一设备重复借出的规则写进 README。

完成标准： 查询结果可手工核对，代码不拼接用户输入，事务失败有证据，关闭连接，能说明 NULL 与 0 的区别。

十三、学习资料

• SQLite SQL 语言文档：查询语法的第一参考。
• Python sqlite3 文档：参数绑定、连接与事务行为。
• SQLite 外键文档：了解启用方式与约束。
• SQLite 查询计划：学习读取 EXPLAIN QUERY PLAN。