SQLite零基础学习教程
SQLite 零基础学习教程
从入门到精通—数据库基础、SQL语法、高级特性与编程集成
DuMate
2026年8月
本教程面向完全没有数据库经验的初学者，从"什么是数据库"讲起，逐步深入SQLite的核心知识体系。教程分为十章加附录，涵盖数据库基础概念、SQL语法、表设计、索引、事务、高级特性、Python编程集成及实战项目。每章配有可运行的代码示例，建议边学边练。
学习建议：本教程按由浅入深的顺序编排，建议按章节顺序学习。每学完一个知识点，请在SQLite命令行中实际运行示例代码，动手实践是掌握SQL的关键。
目录
9.1 查看查询计划（EXPLAIN QUERY PLAN） 37
第一章 初识数据库与SQLite
本章介绍数据库的基本概念、SQLite的特点，以及如何在你的电脑上安装和使用SQLite。读完本章，你将理解数据库的作用，并能在命令行中运行SQLite。
1.1 什么是数据库
数据库（Database）是一个有组织地存储数据的容器。你可以把它想象成一个电子文件柜——里面的文件按一定规则摆放，方便查找和管理。
在日常生活中，我们经常接触类似数据库的概念：通讯录就是一个小型数据库（每条记录包含姓名、电话、邮箱），Excel表格也是一种简单的数据库（每列是一个字段，每行是一条记录）。
专业数据库管理系统（DBMS）在这些基础上提供了更强大的功能：支持多用户并发访问、保证数据安全、自动备份数据、高效检索大量数据等。
1.2 关系型数据库
数据库有多种类型，最常见的是关系型数据库（Relational Database）。它的核心思想是用"表"来组织数据，表与表之间通过"关系"关联。
关系型数据库的基本概念：
- 表（Table）：数据的存储结构，类似Excel的一个工作表，每张表存放一类数据
- 行（Row）：表中的一条记录，代表一个实体。例如学生表中的一行代表一个学生
- 列（Column）：表中的一个字段，代表实体的一个属性。例如学生表中的"姓名"列
- 主键（Primary Key）：唯一标识一行数据的列，类似身份证号
- 外键（Foreign Key）：引用其他表主键的列，用于建立表与表之间的关联
举例：一个学校系统可能有"学生表"和"班级表"。学生表中有"班级ID"列引用班级表的主键，这就建立了学生和班级的关联关系。这种设计就是"关系型"的含义。
1.3 认识SQLite
SQLite是世界上使用最广泛的数据库引擎，几乎每个智能手机、每台电脑上都内置了SQLite。它的名字中的"Lite"代表轻量级。
1.3.1 SQLite的核心特点
SQLite核心特点一览
特点 | 说明 |
无需服务器 | 不需要安装和配置独立的数据库服务器进程，SQLite直接读写本地文件 |
零配置 | 下载后即可使用，无需任何配置 |
单文件存储 | 整个数据库就是一个文件（.db/.sqlite），方便备份和迁移 |
跨平台 | 数据库文件可以在不同操作系统之间直接复制使用 |
开源免费 | SQLite是公有领域的开源软件，完全免费，可用于任何目的 |
嵌入式 | SQLite被嵌入到应用程序内部运行，不需要单独的连接过程 |
标准SQL支持 | 支持大部分SQL92标准语法 |
1.3.2 SQLite与其他数据库对比
市面上常见的数据库分两大类：嵌入式数据库（如SQLite）和客户端/服务器数据库（如MySQL、PostgreSQL、Oracle）。下面是它们的对比：
SQLite与其他主流数据库对比
对比项 | SQLite | MySQL | PostgreSQL |
架构 | 嵌入式（文件级） | 客户端/服务器 | 客户端/服务器 |
安装 | 下载即用 | 需安装服务器 | 需安装服务器 |
配置 | 零配置 | 需配置 | 需配置 |
并发 | 单写多读 | 多用户并发 | 多用户并发 |
数据量 | 适合中小型（TB级以下） | 适合大型 | 适合大型 |
部署成本 | 极低 | 中等 | 中等 |
适用场景 | App、嵌入式、小型网站、测试 | 中大型Web应用 | 复杂查询、数据分析 |
选择建议：如果你是个人学习、开发桌面App、做数据分析、搭建小型网站或做单元测试，SQLite是最佳选择。如果是多人协作的大型Web系统，MySQL或PostgreSQL更合适。
1.4 安装SQLite
SQLite的安装非常简单。不同平台的安装方式如下：
1.4.1 Windows安装
- 访问SQLite官网下载页面：https://www.sqlite.org/download.html
- 找到"Precompiled Binaries for Windows"区域
- 下载sqlite-tools-win32-*.zip压缩包
- 解压到任意目录，例如C:\sqlite
- 将该目录添加到系统PATH环境变量中
打开命令提示符或PowerShell，输入以下命令验证安装：
sqlite3 --version
如果显示版本号（如3.46.x），说明安装成功。
1.4.2 macOS安装
macOS通常已预装SQLite。如果没有，可通过Homebrew安装：
brew install sqlite3
1.4.3 Linux安装
大多数Linux发行版也预装了SQLite。如果没有：
# Ubuntu/Debian sudo apt-get install sqlite3 # CentOS/RHEL sudo yum install sqlite
1.5 第一个SQLite操作
安装完成后，让我们来执行第一个SQLite操作。打开命令行，输入以下命令创建并打开一个数据库文件：
sqlite3 mydb.db
这会创建一个名为mydb.db的数据库文件（如果不存在则新建），并进入SQLite交互式命令行界面，你会看到sqlite>提示符。
在SQLite命令行中，输入以下命令创建第一张表：
-- 创建一张 greetings 表 CREATE TABLE greetings ( id INTEGER PRIMARY KEY, message TEXT ); -- 插入一条数据 INSERT INTO greetings (message) VALUES ('Hello, SQLite!'); -- 查询数据 SELECT * FROM greetings;
你会看到输出：1|Hello, SQLite!
恭喜！你刚刚完成了数据库操作中最核心的三件事：建表、插入数据、查询数据。后面的章节将深入讲解每个操作。
1.5.1 SQLite常用命令行指令
在SQLite交互式命令行中，以点(.)开头的命令是SQLite特有的管理命令：
SQLite常用点命令
命令 | 作用 |
.help | 显示帮助信息 |
.tables | 列出当前数据库中的所有表 |
.schema | 显示所有表的创建语句（表结构） |
.schema 表名 | 显示指定表的创建语句 |
.headers on | 查询结果中显示列名 |
.mode column | 以对齐的列格式显示查询结果 |
.open 文件路径 | 打开（或创建）指定路径的数据库文件 |
.databases | 列出当前打开的数据库 |
.dump | 导出整个数据库为SQL文本 |
.quit | 退出SQLite命令行 |
实用技巧：输入 .headers on 和 .mode column 后再执行查询，输出的数据会以表格形式对齐显示，可读性大幅提升。建议每次进入SQLite后先执行这两条命令。
第二章 SQL基础语法与CRUD操作
本章是整个教程的核心。你将学习SQL语言的基础语法，掌握对数据的增（Create）、查（Read）、改（Update）、删（Delete）四大操作，即CRUD。
2.1 SQL语言简介
SQL（Structured Query Language，结构化查询语言）是与数据库沟通的标准语言。无论你使用SQLite、MySQL还是PostgreSQL，SQL的基本语法都是通用的。
SQL语句按功能分为以下几类：
SQL语句分类
分类 | 全称 | 作用 | 常用语句 |
DDL | 数据定义语言 | 定义和修改数据库结构 | CREATE, ALTER, DROP |
DML | 数据操作语言 | 操作表中的数据 | INSERT, UPDATE, DELETE |
DQL | 数据查询语言 | 查询数据 | SELECT |
DCL | 数据控制语言 | 控制访问权限 | GRANT, REVOKE |
在SQLite中，最常用的是DDL（建表）、DML（增删改）和DQL（查询）。本章先讲DDL和DML，下一章深入DQL。
2.2 创建数据库与表
2.2.1 创建（打开）数据库
在SQLite中，创建数据库就是打开一个文件。如果文件不存在，SQLite会自动创建：
-- 在命令行中执行 sqlite3 school.db -- 或者在SQLite交互界面中 .open school.db
注意：SQLite没有CREATE DATABASE语句。一个文件就是一个数据库。
2.2.2 创建表（CREATE TABLE）
CREATE TABLE语句用于创建新表。基本语法：
CREATE TABLE 表名 ( 列名1 数据类型 [约束], 列名2 数据类型 [约束], ... );
下面创建一张学生表，包含学号、姓名、年龄和班级：
CREATE TABLE students ( id INTEGER PRIMARY KEY, -- 学号，主键 name TEXT NOT NULL, -- 姓名，不能为空 age INTEGER, -- 年龄 class TEXT DEFAULT '1班' -- 班级，默认值为'1班' );
代码解析：
- INTEGER和TEXT是数据类型，分别表示整数和文本
- PRIMARY KEY表示该列是主键，值唯一且自动递增
- NOT NULL表示该列不允许为空值
- DEFAULT设置默认值，插入数据时如果不指定该列，就使用默认值
2.2.3 SQLite数据类型
SQLite采用动态类型系统，数据类型比其他数据库更灵活。它有5种基本存储类型：
SQLite基本数据类型
存储类型 | 说明 | 示例 |
INTEGER | 有符号整数（1/2/4/6/8字节） | 1, 42, -100 |
REAL | 浮点数（8字节IEEE浮点） | 3.14, -0.5 |
TEXT | 文本字符串（UTF-8/UTF-16/UTF-16BE编码） | '张三', 'hello' |
BLOB | 二进制数据，按输入原样存储 | 图片、音频等二进制数据 |
NULL | 空值，表示没有数据 | NULL |
SQLite还支持类型亲和性（Type Affinity）机制。在创建表时声明TEXT、INTEGER、REAL、NUMERIC、NONE等类型名时，SQLite会根据亲和性规则自动转换。这意味着即使你声明列为VARCHAR(255)，SQLite也会把它当作TEXT处理。
最佳实践：虽然SQLite的类型系统很灵活，但建议在创建表时始终明确声明数据类型，这样代码更清晰，也更容易迁移到其他数据库。
2.2.4 修改表结构（ALTER TABLE）
SQLite的ALTER TABLE功能有限，支持添加列和重命名表：
-- 添加新列 ALTER TABLE students ADD COLUMN email TEXT; -- 重命名表 ALTER TABLE students RENAME TO pupils; -- 重命名回students ALTER TABLE pupils RENAME TO students;
注意：SQLite不支持直接删除列或修改列类型。如果需要这些操作，通常的做法是：创建新表 -> 复制数据 -> 删除旧表 -> 重命名新表。
2.2.5 删除表（DROP TABLE）
-- 删除表（谨慎操作！数据会全部丢失） DROP TABLE students; -- 如果表存在才删除（更安全的写法） DROP TABLE IF EXISTS students;
安全提示：DROP TABLE会永久删除表和其中所有数据，无法撤销。始终先确认再执行，重要数据操作前建议备份。
2.3 插入数据（INSERT）
INSERT语句用于向表中添加新数据。基本语法：
-- 指定列名插入（推荐写法，安全且清晰） INSERT INTO students (name, age, class) VALUES ('张三', 18, '计算机1班'); -- 省略列名插入（需要按顺序提供所有列的值） INSERT INTO students VALUES (NULL, '李四', 19, '计算机2班'); -- 批量插入多条数据 INSERT INTO students (name, age, class) VALUES ('王五', 20, '计算机1班'), ('赵六', 21, '计算机2班'), ('孙七', 19, '计算机1班');
代码解析：
- 主键id设为INTEGER PRIMARY KEY时，插入NULL会自动分配递增的编号
- 推荐使用指定列名的写法，即使表结构变化也不容易出错
- 批量插入用逗号分隔多组VALUES，效率比逐条插入高得多
2.4 查询数据（SELECT）
SELECT是SQL中最重要、最常用的语句。基础语法：
SELECT 列名1, 列名2, ... FROM 表名;
-- 查询所有列 SELECT * FROM students; -- 查询指定列 SELECT name, age FROM students; -- 使用AS给列起别名 SELECT name AS 姓名, age AS 年龄 FROM students; -- 去除重复值 SELECT DISTINCT class FROM students;
SELECT的强大之处在于可以配合各种子句进行灵活查询，这将在第三章详细讲解。
2.5 更新数据（UPDATE）
UPDATE语句用于修改已有数据。基本语法：
UPDATE 表名 SET 列名1 = 新值1, 列名2 = 新值2 WHERE 条件;
-- 更新单个字段 UPDATE students SET age = 22 WHERE name = '张三'; -- 更新多个字段 UPDATE students SET age = 20, class = '计算机3班' WHERE name = '李四'; -- 更新所有行（慎用！） UPDATE students SET class = '未分班';
危险操作：如果省略WHERE子句，UPDATE会更新表中所有行。务必先确认WHERE条件正确再执行。安全做法是先SELECT查看要更新的数据，确认无误后再执行UPDATE。
2.6 删除数据（DELETE）
DELETE语句用于删除数据。基本语法：
DELETE FROM 表名 WHERE 条件;
-- 删除指定行 DELETE FROM students WHERE name = '赵六'; -- 删除所有数据（保留表结构） DELETE FROM students; -- 更高效的清空方式（不记录日志，速度快） DELETE FROM students;
DELETE与DROP的区别：DELETE删除表中的数据但保留表结构，DROP直接删除整个表（包括结构和数据）。DELETE和UPDATE一样，不写WHERE会操作所有行。
2.7 实操练习
请在SQLite命令行中完成以下练习：
- 创建一个名为shop.db的数据库文件
- 创建products表，包含id（主键）、name（商品名）、price（价格）、stock（库存）四个字段
- 插入5条商品数据（如苹果3.5元库存100、笔记本15元库存50等）
- 查询所有商品信息
- 将苹果的价格更新为4.0元
- 删除库存为0的商品
第三章 查询进阶
本章深入讲解SELECT语句的各种高级用法，这是SQL最强大的部分。掌握本章内容后，你就能从海量数据中精准提取所需信息。
3.1 条件过滤（WHERE）
WHERE子句用于筛选满足条件的行。它支持多种比较运算符和逻辑运算符：
WHERE常用运算符
运算符 | 含义 | 示例 |
= | 等于 | WHERE age = 20 |
!= 或 <> | 不等于 | WHERE age != 20 |
>, <, >=, <= | 大于/小于/大于等于/小于等于 | WHERE age >= 18 |
BETWEEN...AND | 在指定范围内 | WHERE age BETWEEN 18 AND 25 |
IN | 在指定值列表中 | WHERE class IN ('1班', '2班') |
LIKE | 模糊匹配 | WHERE name LIKE '张%' |
IS NULL | 判断是否为空值 | WHERE email IS NULL |
AND | 与（两个条件都满足） | WHERE age > 18 AND class = '1班' |
OR | 或（满足任一条件） | WHERE age < 18 OR age > 25 |
NOT | 非（取反） | WHERE NOT class = '1班' |
-- 查询年龄大于等于20岁的学生 SELECT * FROM students WHERE age >= 20; -- 查询1班中年龄在18到22之间的学生 SELECT * FROM students WHERE class = '1班' AND age BETWEEN 18 AND 22; -- 查询姓"张"的学生 SELECT * FROM students WHERE name LIKE '张%'; -- 查询没有邮箱的学生 SELECT * FROM students WHERE email IS NULL;
LIKE通配符说明：百分号(%)匹配任意数量字符，下划线(_)匹配单个字符。例如：
- '张%' 匹配以"张"开头的所有字符串
- '%三' 匹配以"三"结尾的所有字符串
- '%三%' 匹配包含"三"的所有字符串
- '张_' 匹配"张"开头且后面恰好一个字符的字符串（如"张三"但不匹配"张三丰"）
3.2 排序（ORDER BY）
ORDER BY用于对查询结果排序，默认升序（ASC）：
-- 按年龄升序排列 SELECT * FROM students ORDER BY age; -- 按年龄降序排列 SELECT * FROM students ORDER BY age DESC; -- 多列排序：先按班级升序，同班内按年龄降序 SELECT * FROM students ORDER BY class ASC, age DESC;
3.3 限制结果数量（LIMIT与OFFSET）
LIMIT限制返回的行数，OFFSET指定跳过前几行，常用于分页：
-- 只返回前3条记录 SELECT * FROM students LIMIT 3; -- 跳过前2条，返回接下来的3条（分页：第2页，每页3条） SELECT * FROM students LIMIT 3 OFFSET 2; -- 结合排序和限制：年龄最大的3个学生 SELECT * FROM students ORDER BY age DESC LIMIT 3;
3.4 聚合函数
聚合函数对一组值进行计算，返回单个结果值：
常用聚合函数
函数 | 作用 | 示例 |
COUNT() | 统计行数 | SELECT COUNT(*) FROM students |
SUM() | 求和 | SELECT SUM(age) FROM students |
AVG() | 求平均值 | SELECT AVG(age) FROM students |
MAX() | 求最大值 | SELECT MAX(age) FROM students |
MIN() | 求最小值 | SELECT MIN(age) FROM students |
-- 统计学生总数 SELECT COUNT(*) AS 总人数 FROM students; -- 计算平均年龄 SELECT AVG(age) AS 平均年龄 FROM students; -- 查询最大年龄和最小年龄 SELECT MAX(age) AS 最大年龄, MIN(age) AS 最小年龄 FROM students; -- COUNT(列名)不计算NULL值 SELECT COUNT(email) AS 有邮箱人数 FROM students;
3.5 分组（GROUP BY与HAVING）
GROUP BY按指定列分组，常与聚合函数配合使用。HAVING用于过滤分组后的结果（类似WHERE但作用于分组）：
-- 按班级分组，统计每个班级的人数 SELECT class, COUNT(*) AS 人数 FROM students GROUP BY class; -- 按班级分组，计算每班平均年龄 SELECT class, AVG(age) AS 平均年龄 FROM students GROUP BY class; -- 查询人数超过2的班级 SELECT class, COUNT(*) AS 人数 FROM students GROUP BY class HAVING COUNT(*) > 2;
WHERE和HAVING的区别：WHERE在分组前过滤行，HAVING在分组后过滤组。可以同时使用：
SELECT class, COUNT(*) AS 人数 FROM students WHERE age >= 18 -- 先过滤掉未满18岁的 GROUP BY class -- 再按班级分组 HAVING COUNT(*) >= 2; -- 最后过滤掉人数少于2的班级
3.6 连接查询（JOIN）
JOIN用于将多张表的数据按关联条件组合在一起。这是关系型数据库最强大的功能之一。
为了演示JOIN，我们先创建一张成绩表：
CREATE TABLE scores ( id INTEGER PRIMARY KEY, student_id INTEGER, -- 关联students表的id subject TEXT, -- 科目 score REAL -- 分数 ); INSERT INTO scores (student_id, subject, score) VALUES (1, '数学', 95.5), (1, '英语', 88.0), (2, '数学', 78.0), (2, '英语', 92.5), (3, '数学', 85.0);
3.6.1 INNER JOIN（内连接）
INNER JOIN只返回两张表中都匹配的行：
-- 查询每个学生的姓名和各科成绩 SELECT students.name, scores.subject, scores.score FROM students INNER JOIN scores ON students.id = scores.student_id; -- 使用表别名简化写法 SELECT s.name, sc.subject, sc.score FROM students s INNER JOIN scores sc ON s.id = sc.student_id;
3.6.2 LEFT JOIN（左连接）
LEFT JOIN返回左表的所有行，即使右表中没有匹配。右表无匹配时返回NULL：
-- 查询所有学生及其成绩（包括没有成绩的学生） SELECT s.name, sc.subject, sc.score FROM students s LEFT JOIN scores sc ON s.id = sc.student_id;
INNER JOIN与LEFT JOIN的区别：如果有一个学生没有任何成绩记录，INNER JOIN不会显示该学生，LEFT JOIN会显示该学生但成绩字段为NULL。
3.7 子查询
子查询是嵌套在另一个查询中的查询，可以出现在WHERE、SELECT、FROM等子句中：
-- 查询年龄大于平均年龄的学生 SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students); -- 查询有成绩记录的学生 SELECT * FROM students WHERE id IN (SELECT DISTINCT student_id FROM scores); -- 子查询作为临时表 SELECT class, avg_age FROM ( SELECT class, AVG(age) AS avg_age FROM students GROUP BY class ) WHERE avg_age > 20;
3.8 SQL子句执行顺序
理解SQL各子句的执行顺序对编写复杂查询很重要：
- FROM（确定数据来源表）
- JOIN（连接表）
- WHERE（过滤行）
- GROUP BY（分组）
- HAVING（过滤分组）
- SELECT（选择列，计算聚合）
- DISTINCT（去重）
- ORDER BY（排序）
- LIMIT/OFFSET（限制结果）
理解执行顺序的意义：WHERE在SELECT之前执行，所以WHERE中不能用SELECT里定义的别名。HAVING在GROUP BY之后执行，所以HAVING中可以用聚合函数。
第四章 表设计与约束
好的表设计是数据库性能和数据质量的基础。本章学习如何设计合理的表结构，以及如何用约束保证数据的完整性和一致性。
4.1 主键（PRIMARY KEY）
主键是表中唯一标识每一行的列或列组合。主键的值必须唯一且不为空。
-- 单列主键 CREATE TABLE users ( id INTEGER PRIMARY KEY, -- 自动递增主键 name TEXT NOT NULL ); -- 显式使用AUTOINCREMENT（保证ID只增不减） CREATE TABLE users2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL ); -- 复合主键（多列组合作为主键） CREATE TABLE course_selection ( student_id INTEGER, course_id INTEGER, selected_at TEXT, PRIMARY KEY (student_id, course_id) );
INTEGER PRIMARY KEY的特殊行为：在SQLite中，声明为INTEGER PRIMARY KEY的列会自动成为ROWID的别名，插入NULL时自动分配一个递增的整数值。
4.2 外键（FOREIGN KEY）
外键用于建立表与表之间的引用关系，保证关联数据的一致性。
CREATE TABLE courses ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, credit INTEGER ); CREATE TABLE enrollments ( id INTEGER PRIMARY KEY, student_id INTEGER, course_id INTEGER, enrolled_date TEXT, FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id) );
外键约束的行为：当删除或更新被引用的行时，可以指定如何处理外键：
外键删除/更新策略
策略 | 说明 |
RESTRICT（默认） | 禁止删除被引用的行 |
CASCADE | 级联删除/更新引用方的行 |
SET NULL | 将引用方的值设为NULL |
NO ACTION | 与RESTRICT相同，延迟到事务提交时检查 |
CREATE TABLE enrollments2 ( id INTEGER PRIMARY KEY, student_id INTEGER, course_id INTEGER, FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE, FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL );
重要提示：SQLite默认不启用外键约束检查。需要每次连接数据库后执行 PRAGMA foreign_keys = ON; 才会生效。
4.3 其他约束
4.3.1 NOT NULL约束
NOT NULL确保列不能存储NULL值：
CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL, -- 用户名不能为空 email TEXT NOT NULL UNIQUE -- 邮箱不能为空且唯一 );
4.3.2 UNIQUE约束
UNIQUE确保列中的所有值都不重复：
CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT UNIQUE, -- 用户名唯一 email TEXT UNIQUE -- 邮箱唯一 ); -- 多列组合唯一 CREATE TABLE user_login ( id INTEGER PRIMARY KEY, user_id INTEGER, login_device TEXT, UNIQUE(user_id, login_device) -- 同一用户同一设备只能登录一次 );
4.3.3 DEFAULT约束
DEFAULT为列设置默认值，插入数据时如果不指定该列就使用默认值：
CREATE TABLE articles ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, content TEXT, status TEXT DEFAULT 'draft', -- 默认状态为草稿 created_at TEXT DEFAULT (datetime('now', 'localtime')) -- 默认为当前时间 );
4.3.4 CHECK约束
CHECK确保列的值满足指定条件：
CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL CHECK(price >= 0), -- 价格不能为负 stock INTEGER CHECK(stock >= 0), -- 库存不能为负 category TEXT CHECK(category IN ('食品', '电子', '服装', '图书')) -- 限定分类 );
4.4 数据库设计原则
好的数据库设计需要遵循一些基本原则：
4.4.1 数据库范式
范式是数据库设计的规范，目的是减少数据冗余和异常。最重要的三个范式：
数据库三大范式
范式 | 要求 | 通俗理解 |
第一范式(1NF) | 每个列都是不可分割的原子值 | 一个列不要存多个值 |
第二范式(2NF) | 在1NF基础上，非主键列完全依赖主键 | 不要把不相关的数据放一张表 |
第三范式(3NF) | 在2NF基础上，非主键列之间没有传递依赖 | 非主键列只依赖主键，不依赖其他非主键列 |
举例说明反例和正例：
-- 反例：违反第三范式（班级信息冗余存储） CREATE TABLE bad_students ( id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER, class_name TEXT, -- 冗余：class_name依赖class_id而非id class_teacher TEXT -- 冗余：同上 ); -- 正例：拆分成两张表（消除传递依赖） CREATE TABLE classes ( id INTEGER PRIMARY KEY, name TEXT, teacher TEXT ); CREATE TABLE good_students ( id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER, FOREIGN KEY (class_id) REFERENCES classes(id) );
4.4.2 命名规范建议
- 表名用复数或单数均可，但全文保持一致（如全部用单数：student, course, score）
- 列名用小写字母加下划线，如created_at、user_id
- 外键列名通常为"关联表名单数_id"，如student_id、course_id
- 布尔类型列以is_或has_开头，如is_active、has_permission
- 时间列以_at结尾，如created_at、updated_at、deleted_at
第五章 索引
索引是数据库性能优化最重要的工具。理解索引的原理和使用场景，能让你的查询速度提升数十倍甚至上百倍。
5.1 什么是索引
索引类似于书的目录：没有目录时，找某个内容需要翻遍全书；有了目录，可以直接定位到对应页码。数据库索引也是这个原理——它是一种数据结构（SQLite使用B-tree），让数据库不必扫描整张表就能快速找到数据。
索引的代价：索引虽然能加速查询，但会占用额外存储空间，并且在INSERT、UPDATE、DELETE时需要同步更新索引，因此会降低写入速度。
5.2 创建与删除索引
-- 在name列上创建单列索引 CREATE INDEX idx_students_name ON students(name); -- 在多列上创建复合索引 CREATE INDEX idx_students_class_age ON students(class, age); -- 创建唯一索引（同时保证唯一性和加速查询） CREATE UNIQUE INDEX idx_students_email ON students(email); -- 删除索引 DROP INDEX idx_students_name;
SQLite的CREATE INDEX语法选项：
-- 创建表达式索引（对表达式的结果建索引） CREATE INDEX idx_lower_name ON students(LOWER(name)); -- 创建部分索引（只对满足条件的行建索引） CREATE INDEX idx_active_users ON users(name) WHERE status = 'active';
5.3 何时使用索引
适合创建索引的场景：
- 经常出现在WHERE条件中的列
- 经常用于JOIN连接条件的列（通常是外键）
- 经常用于ORDER BY排序的列
- 经常用于GROUP BY分组的列
- 值选择性高的列（不同值多，重复少）
不适合创建索引的场景：
- 数据量很小的表（几百条以下，全表扫描更快）
- 频繁更新的列（索引维护成本高）
- 值选择性低的列（如性别只有男女两个值）
- 很少用于查询条件的列
5.4 复合索引与最左前缀原则
复合索引是在多个列上创建的索引。它的使用遵循"最左前缀原则"：查询条件必须从索引的最左列开始才能使用索引。
-- 复合索引：(class, age, name) CREATE INDEX idx_multi ON students(class, age, name); -- 能使用索引的查询： SELECT * FROM students WHERE class = '1班'; -- 用到了class SELECT * FROM students WHERE class = '1班' AND age = 20; -- 用到了class, age SELECT * FROM students WHERE class = '1班' AND age = 20 AND name LIKE '张%'; -- 全部用到 -- 不能（有效）使用索引的查询： SELECT * FROM students WHERE age = 20; -- 跳过了class SELECT * FROM students WHERE name = '张三'; -- 跳过了class和age SELECT * FROM students WHERE age = 20 AND name = '张三'; -- 跳过了class
设计复合索引时，把选择性最高（不同值最多）的列放在最左边，这样能过滤掉更多数据，索引效率更高。
第六章 视图、触发器与事务
本章介绍三个重要的数据库高级特性：视图、触发器和事务。它们能让数据库更安全、更自动化、更可靠。
6.1 视图（VIEW）
视图是基于一条SELECT语句的虚拟表。它不存储实际数据，只存储查询定义。使用视图可以简化复杂查询、控制数据访问权限。
-- 创建视图：显示学生姓名和各科成绩 CREATE VIEW student_scores AS SELECT s.name, sc.subject, sc.score FROM students s INNER JOIN scores sc ON s.id = sc.student_id; -- 像查询普通表一样查询视图 SELECT * FROM student_scores; SELECT * FROM student_scores WHERE score >= 90; -- 创建视图：每个学生的平均分 CREATE VIEW student_avg AS SELECT s.name, AVG(sc.score) AS avg_score FROM students s LEFT JOIN scores sc ON s.id = sc.student_id GROUP BY s.id; -- 查询视图 SELECT * FROM student_avg ORDER BY avg_score DESC; -- 删除视图 DROP VIEW IF EXISTS student_scores;
视图的使用场景：
- 简化常用复杂查询：把多表JOIN封装成视图，使用时像查表一样简单
- 数据安全：只暴露部分列给特定用户
- 数据抽象：底层表结构变化时，视图可以屏蔽变化
6.2 触发器（TRIGGER）
触发器是在特定事件（INSERT/UPDATE/DELETE）发生时自动执行的SQL代码。常用于数据审计、自动更新关联数据等。
-- 创建审计表 CREATE TABLE audit_log ( id INTEGER PRIMARY KEY, action TEXT, table_name TEXT, record_id INTEGER, old_value TEXT, new_value TEXT, created_at TEXT DEFAULT (datetime('now', 'localtime')) ); -- 创建触发器：当学生信息被更新时，自动记录到审计表 CREATE TRIGGER trg_student_update AFTER UPDATE ON students FOR EACH ROW BEGIN INSERT INTO audit_log (action, table_name, record_id, old_value, new_value) VALUES ('UPDATE', 'students', NEW.id, OLD.name, NEW.name); END; -- 测试触发器 UPDATE students SET name = '张三丰' WHERE id = 1; -- 查看审计记录 SELECT * FROM audit_log; -- 删除触发器 DROP TRIGGER IF EXISTS trg_student_update;
触发器的关键概念：
- 触发时机：BEFORE（操作前执行）或AFTER（操作后执行）
- 触发事件：INSERT、UPDATE、DELETE
- OLD关键字：引用操作前的旧数据（UPDATE和DELETE时可用）
- NEW关键字：引用操作后的新数据（INSERT和UPDATE时可用）
- FOR EACH ROW：对受影响的每一行执行一次触发器
6.3 事务（TRANSACTION）
事务是一组操作的逻辑单元，要么全部成功，要么全部失败。事务保证数据库在异常情况下数据不会损坏。
6.3.1 ACID特性
事务ACID特性
特性 | 全称 | 含义 |
原子性 | Atomicity | 事务中的操作要么全部执行，要么全部不执行 |
一致性 | Consistency | 事务执行前后，数据库从一个一致状态变为另一个一致状态 |
隔离性 | Isolation | 多个事务并发执行时互不干扰 |
持久性 | Durability | 事务提交后，修改永久保存，即使系统崩溃也不会丢失 |
6.3.2 事务的基本使用
-- 开始事务 BEGIN TRANSACTION; -- 执行多条SQL语句 UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'; -- 如果都正确，提交事务 COMMIT; -- 如果出错，回滚事务（撤销所有操作） -- ROLLBACK;
事务的经典场景——银行转账：从Alice转100元给Bob，需要两步操作（减Alice余额、加Bob余额）。如果第一步成功但第二步失败，没有事务的话钱就"消失"了。用事务可以保证要么两步都成功，要么都不执行。
-- 完整的转账事务示例 BEGIN TRANSACTION; -- 检查Alice余额是否足够 -- （在应用代码中判断） UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'; -- 假设这里发生错误，可以回滚 -- ROLLBACK; UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'; COMMIT; -- 转账完成
SQLite的事务特点：SQLite默认每个SQL语句自动作为一个事务执行（自动提交模式）。使用BEGIN TRANSACTION可以手动控制事务。手动事务中，所有操作在COMMIT前都不会真正写入磁盘，这也能大幅提升批量插入性能。
6.3.3 事务隔离级别
SQLite使用BEGIN语句开启事务，支持以下事务类型：
-- DEFERRED（默认）：延迟获取锁，直到第一次读写 BEGIN DEFERRED TRANSACTION; -- IMMEDIATE：立即获取保留锁，允许其他连接读但不允许写 BEGIN IMMEDIATE TRANSACTION; -- EXCLUSIVE：立即获取独占锁，其他连接不能读写 BEGIN EXCLUSIVE TRANSACTION;
第七章 SQLite高级特性
本章介绍SQLite的一些独特功能，这些功能让SQLite在小巧的体积中也拥有强大的能力。
7.1 SQLite常用内置函数
7.1.1 字符串函数
-- 字符串长度 SELECT LENGTH('Hello'); -- 返回 5 -- 大小写转换 SELECT UPPER('hello'); -- 返回 HELLO SELECT LOWER('HELLO'); -- 返回 hello -- 字符串拼接 SELECT 'Hello' || ' ' || 'World'; -- 返回 Hello World -- 截取子串 SELECT SUBSTR('Hello World', 1, 5); -- 返回 Hello -- 替换 SELECT REPLACE('Hello World', 'World', 'SQLite'); -- 返回 Hello SQLite -- 去除首尾空格 SELECT TRIM(' Hello '); -- 返回 Hello
7.1.2 数值函数
-- 四舍五入 SELECT ROUND(3.14159, 2); -- 返回 3.14 -- 绝对值 SELECT ABS(-42); -- 返回 42 -- 取整 SELECT CAST(3.7 AS INTEGER); -- 返回 3（截断小数部分） SELECT ROUND(3.7); -- 返回 4.0 -- 随机数 SELECT RANDOM(); -- 返回随机整数
7.1.3 日期时间函数
-- 当前日期时间 SELECT datetime('now'); -- UTC时间 SELECT datetime('now', 'localtime'); -- 本地时间 SELECT date('now'); -- 当前日期 SELECT time('now', 'localtime'); -- 当前时间 -- 日期计算 SELECT date('now', '+1 day'); -- 明天 SELECT date('now', '-1 month'); -- 上个月今天 SELECT date('now', '+1 year'); -- 明年今天 -- 日期格式化 SELECT strftime('%Y-%m-%d %H:%M', 'now', 'localtime'); -- 2026-08-14 15:00 -- 计算两个日期差 SELECT julianday('2026-12-31') - julianday('now'); -- 距年底天数
strftime格式化符号
符号 | 含义 | 示例输出 |
%Y | 四位年份 | 2026 |
%m | 月份（01-12） | 08 |
%d | 日期（01-31） | 14 |
%H | 小时（00-23） | 15 |
%M | 分钟（00-59） | 30 |
%S | 秒（00-59） | 00 |
%w | 星期（0-6，0=周日） | 4 |
%j | 一年中第几天（001-366） | 226 |
7.2 全文搜索（FTS）
SQLite内置FTS5模块，提供全文搜索功能。LIKE模糊查询在数据量大时性能很差，FTS可以高效地进行文本搜索。
-- 创建FTS5虚拟表 CREATE VIRTUAL TABLE articles_fts USING fts5( title, content, tokenize='unicode61' -- 支持中文等Unicode分词 ); -- 插入数据 INSERT INTO articles_fts (title, content) VALUES ('SQLite入门', 'SQLite是一个轻量级的嵌入式数据库引擎'), ('Python教程', 'Python是一门流行的编程语言'), ('数据库优化', '索引和事务是数据库优化的重要手段'); -- 全文搜索（MATCH） SELECT * FROM articles_fts WHERE articles_fts MATCH '数据库'; -- 搜索多个词（空格表示AND） SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite 轻量'; -- 搜索标题中的关键词 SELECT * FROM articles_fts WHERE title MATCH '入门'; -- 排序按相关度 SELECT title, rank FROM articles_fts WHERE articles_fts MATCH '数据库' ORDER BY rank;
FTS5在SQLite 3.9.0及以上版本可用。如果你的SQLite版本较旧，可能需要使用FTS4或FTS3。可以用 SELECT sqlite_version(); 查看版本。
7.3 JSON支持
SQLite从3.9.0开始提供JSON1扩展，3.38.0起内置JSON函数。可以直接在SQLite中存储和查询JSON数据。
-- 创建带JSON数据的表 CREATE TABLE user_profiles ( id INTEGER PRIMARY KEY, name TEXT, data TEXT -- 存储JSON字符串 ); -- 插入JSON数据 INSERT INTO user_profiles (name, data) VALUES ('张三', '{"age":25, "city":"北京", "hobbies":["编程","音乐"]}'), ('李四', '{"age":30, "city":"上海", "hobbies":["阅读","旅行"]}'); -- 提取JSON字段 SELECT name, json_extract(data, '$.age') AS age, json_extract(data, '$.city') AS city FROM user_profiles; -- 查询JSON数组中包含某个值 SELECT name FROM user_profiles WHERE data LIKE '%编程%'; -- 修改JSON数据 UPDATE user_profiles SET data = json_set(data, '$.age', 26) WHERE name = '张三'; -- 创建JSON对象 SELECT json_object('name', '王五', 'age', 28);
7.4 导入导出数据
7.4.1 导出数据库
-- 在SQLite命令行中导出整个数据库为SQL脚本 sqlite3 school.db .dump > backup.sql -- 导出单张表 sqlite3 school.db .dump students > students_backup.sql
7.4.2 从SQL脚本导入
-- 从SQL脚本恢复数据库 sqlite3 new_school.db < backup.sql
7.4.3 导入CSV数据
-- 在SQLite命令行中 .mode csv .import data.csv my_table -- 注意：CSV的第一行会成为数据导入，如果第一行是表头需要手动处理
第八章 编程集成——Python与SQLite
SQLite最大的优势之一是可以直接嵌入编程语言中使用。Python标准库自带sqlite3模块，无需安装任何额外依赖。本章教你如何在Python中操作SQLite数据库。
8.1 Python sqlite3模块入门
Python内置了sqlite3模块，使用前只需import即可：
import sqlite3 # 连接到数据库（文件不存在会自动创建） conn = sqlite3.connect('example.db') # 也可以使用内存数据库（临时数据库，关闭连接后消失） # 适合测试和临时数据处理 conn = sqlite3.connect(':memory:') # 创建游标对象（用于执行SQL语句） cursor = conn.cursor() # 创建表 cursor.execute(''' CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER ) ''') # 提交事务（保存更改） conn.commit() # 关闭连接 conn.close()
重要：使用connect()连接数据库后，所有修改操作（INSERT/UPDATE/DELETE）都需要调用conn.commit()才会真正保存。如果忘记commit，关闭连接后修改会丢失。
8.2 增删改查（CRUD）操作
8.2.1 插入数据
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor() # 插入单条数据 cursor.execute( 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)', ('张三', 'zhangsan@example.com', 25) ) # 获取插入的ID print(f'插入的ID: {cursor.lastrowid}') # 批量插入 cursor.executemany( 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)', [ ('李四', 'lisi@example.com', 30), ('王五', 'wangwu@example.com', 28), ('赵六', 'zhaoliu@example.com', 35), ] ) conn.commit() print(f'共插入 {cursor.rowcount} 条记录') conn.close()
安全提醒：永远使用参数化查询（?占位符）来插入用户输入的数据，绝不要用字符串拼接的方式构造SQL语句。字符串拼接会导致SQL注入漏洞，这是最常见的安全问题之一。
8.2.2 查询数据
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor() # 查询所有数据 cursor.execute('SELECT * FROM users') rows = cursor.fetchall() # 获取所有结果 for row in rows: print(row) # 查询单条 cursor.execute('SELECT * FROM users WHERE name = ?', ('张三',)) row = cursor.fetchone() # 获取一条结果 print(row) # 查询前N条 cursor.execute('SELECT * FROM users ORDER BY age DESC') rows = cursor.fetchmany(3) # 获取3条结果 for row in rows: print(row) # 使用参数化查询 cursor.execute('SELECT * FROM users WHERE age > ? AND age < ?', (25, 35)) for row in cursor.fetchall(): print(row) conn.close()
8.2.3 更新和删除
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor() # 更新数据 cursor.execute( 'UPDATE users SET age = ? WHERE name = ?', (26, '张三') ) print(f'更新了 {cursor.rowcount} 条记录') # 删除数据 cursor.execute('DELETE FROM users WHERE name = ?', ('赵六',)) print(f'删除了 {cursor.rowcount} 条记录') conn.commit() conn.close()
8.3 使用上下文管理器
Python的with语句可以自动管理资源的打开和关闭，还可以自动处理事务：
import sqlite3 # 使用上下文管理器管理连接 with sqlite3.connect('example.db') as conn: cursor = conn.cursor() cursor.execute('SELECT * FROM users') for row in cursor.fetchall(): print(row) # with块结束时自动commit（如果无异常） # 如果发生异常则自动rollback # 连接不需要手动关闭，但建议显式关闭
8.4 获取结果为字典
默认情况下，查询结果返回元组。通过设置row_factory可以让结果以字典形式返回，更方便使用：
import sqlite3 conn = sqlite3.connect('example.db') conn.row_factory = sqlite3.Row # 设置为Row工厂 cursor = conn.cursor() cursor.execute('SELECT * FROM users') for row in cursor.fetchall(): # 可以通过列名访问 print(row['name'], row['email'], row['age']) # 也可以通过索引访问 print(row[0], row[1], row[2]) conn.close()
8.5 异常处理
import sqlite3 try: conn = sqlite3.connect('example.db') cursor = conn.cursor() cursor.execute('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', ('测试', 'zhangsan@example.com', 20)) # 重复邮箱会报错 conn.commit() except sqlite3.IntegrityError as e: print(f'数据完整性错误: {e}') conn.rollback() except sqlite3.Error as e: print(f'数据库错误: {e}') conn.rollback() finally: conn.close()
8.6 事务处理最佳实践
import sqlite3 def transfer_money(conn, from_user, to_user, amount): try: cursor = conn.cursor() # 开始事务 cursor.execute('BEGIN TRANSACTION') # 扣款 cursor.execute('UPDATE accounts SET balance = balance - ? WHERE name = ?', (amount, from_user)) if cursor.rowcount == 0: raise ValueError(f'用户 {from_user} 不存在') # 加款 cursor.execute('UPDATE accounts SET balance = balance + ? WHERE name = ?', (amount, to_user)) if cursor.rowcount == 0: raise ValueError(f'用户 {to_user} 不存在') conn.commit() print(f'成功转账 {amount} 元: {from_user} -> {to_user}') except Exception as e: conn.rollback() print(f'转账失败: {e}，已回滚') # 注意：以上代码演示事务模式，实际使用时需正确缩进
第九章 性能优化
当数据量增大时，数据库性能变得至关重要。本章介绍SQLite的性能分析工具和优化技巧。
9.1 查看查询计划（EXPLAIN QUERY PLAN）
EXPLAIN QUERY PLAN是分析查询性能的核心工具。它告诉你SQLite如何执行查询，是否使用了索引：
-- 查看查询是否使用索引 EXPLAIN QUERY PLAN SELECT * FROM students WHERE name = '张三'; -- 输出示例（未使用索引）： -- SCAN TABLE students -- （SCAN表示全表扫描，性能差） -- 输出示例（使用了索引）： -- SEARCH TABLE students USING INDEX idx_students_name (name=?) -- （SEARCH表示使用了索引，性能好）
关键字解读：
- SCAN TABLE：全表扫描，遍历每一行（慢）
- SEARCH TABLE USING INDEX：使用索引查找（快）
- SEARCH TABLE USING COVERING INDEX：使用覆盖索引，连表数据都不用查（最快）
- USE TEMP B-TREE FOR ORDER BY：需要临时排序（可能需要优化）
9.2 PRAGMA设置
PRAGMA是SQLite特有的配置命令，可以调整数据库的行为和性能：
-- 查看当前PRAGMA值 PRAGMA journal_mode; -- 查看日志模式 PRAGMA cache_size; -- 查看缓存大小 PRAGMA page_size; -- 查看页大小 -- 设置日志模式为WAL（提高并发性能） PRAGMA journal_mode = WAL; -- 增大缓存（单位KB，负数表示KB，正数表示页数） PRAGMA cache_size = -64000; -- 64MB缓存 -- 启用外键约束 PRAGMA foreign_keys = ON; -- 设置同步模式（0=关闭, 1=普通, 2=完全同步） PRAGMA synchronous = NORMAL; -- 获取数据库信息 PRAGMA table_info(students); -- 查看表结构 PRAGMA index_list(students); -- 查看表的索引 PRAGMA database_list; -- 查看已连接的数据库
常用PRAGMA设置
PRAGMA | 推荐值 | 说明 |
journal_mode | WAL | Write-Ahead Logging，提高读写并发性能 |
synchronous | NORMAL | 兼顾性能和安全，比默认FULL快很多 |
cache_size | -64000 | 64MB缓存，比默认值大很多 |
foreign_keys | ON | 启用外键约束检查 |
temp_store | MEMORY | 临时表存储在内存中 |
9.3 批量操作优化
批量插入大量数据时，使用事务可以大幅提升性能（几十倍甚至上百倍）：
-- 方法1：逐条插入（慢！每条都是独立事务） INSERT INTO big_table (name) VALUES ('row1'); INSERT INTO big_table (name) VALUES ('row2'); INSERT INTO big_table (name) VALUES ('row3'); -- 插入10000条约需数分钟 -- 方法2：用事务包裹批量插入（快！） BEGIN TRANSACTION; INSERT INTO big_table (name) VALUES ('row1'); INSERT INTO big_table (name) VALUES ('row2'); INSERT INTO big_table (name) VALUES ('row3'); -- ...更多插入 COMMIT; -- 插入10000条约需几秒 -- 方法3：一条INSERT多行（更快） INSERT INTO big_table (name) VALUES ('row1'), ('row2'), ('row3'), ...; -- 但单条SQL有长度限制（默认SQLITE_MAX_SQL_LENGTH=1000000）
9.4 其他优化技巧
- 只查询需要的列，避免SELECT *
- 在经常查询的列上创建索引
- 使用LIMIT限制结果数量，特别是只需要少量数据时
- 避免在索引列上使用函数（如WHERE LOWER(name)='abc'会导致索引失效）
- 大量数据导入前先禁用索引，导入后再重建
- 使用PRAGMA optimize在数据库关闭时自动优化
- 定期运行ANALYZE更新统计信息，帮助查询优化器做出更好的决策
- 使用INSTEAD OF触发器代替直接更新视图
第十章 实战项目：图书管理系统
本章通过一个完整的图书管理系统项目，将前面学到的所有知识串联起来。项目包含数据库设计、数据操作和Python集成。
10.1 需求分析
我们要构建一个简单的图书管理系统，功能包括：
- 管理图书信息（书名、作者、ISBN、分类、库存数量）
- 管理读者信息（姓名、借书证号、联系方式）
- 记录借阅信息（谁借了什么书、借出时间、归还时间）
- 查询功能（按书名/作者搜索、查看借阅记录、查看逾期未还）
10.2 数据库设计
-- 创建数据库 .open library.db -- 启用外键约束 PRAGMA foreign_keys = ON; -- 图书分类表 CREATE TABLE categories ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE ); -- 图书表 CREATE TABLE books ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, isbn TEXT UNIQUE, category_id INTEGER, stock INTEGER DEFAULT 1 CHECK(stock >= 0), created_at TEXT DEFAULT (datetime('now', 'localtime')), FOREIGN KEY (category_id) REFERENCES categories(id) ); -- 读者表 CREATE TABLE readers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, phone TEXT, email TEXT, created_at TEXT DEFAULT (datetime('now', 'localtime')) ); -- 借阅记录表 CREATE TABLE borrow_records ( id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, reader_id INTEGER NOT NULL, borrow_date TEXT DEFAULT (datetime('now', 'localtime')), return_date TEXT, -- NULL表示未归还 due_date TEXT NOT NULL, -- 应还日期 FOREIGN KEY (book_id) REFERENCES books(id), FOREIGN KEY (reader_id) REFERENCES readers(id) ); -- 创建索引 CREATE INDEX idx_books_title ON books(title); CREATE INDEX idx_books_author ON books(author); CREATE INDEX idx_borrow_book ON borrow_records(book_id); CREATE INDEX idx_borrow_reader ON borrow_records(reader_id);
设计要点解析：
- 分类单独建表（第三范式），避免冗余
- 图书表通过外键关联分类表
- 借阅记录表是多对多关系的中间表（一个读者可借多本书，一本书可被多个读者借）
- return_date为NULL表示尚未归还，归还后更新为实际归还日期
- stock用CHECK约束确保不为负数
- 为常用查询字段创建索引
10.3 初始化数据
-- 插入分类 INSERT INTO categories (name) VALUES ('文学'), ('科技'), ('历史'), ('艺术'); -- 插入图书 INSERT INTO books (title, author, isbn, category_id, stock) VALUES ('SQLite权威指南', '克雷格', '978-7-111-12345-1', 2, 3), ('Python编程', 'Eric', '978-7-111-12346-2', 2, 5), ('红楼梦', '曹雪芹', '978-7-101-12347-3', 1, 2), ('人类简史', '赫拉利', '978-7-508-12348-4', 3, 4); -- 插入读者 INSERT INTO readers (name, phone, email) VALUES ('张三', '13800000001', 'zhangsan@test.com'), ('李四', '13800000002', 'lisi@test.com'), ('王五', '13800000003', 'wangwu@test.com'); -- 插入借阅记录 INSERT INTO borrow_records (book_id, reader_id, due_date) VALUES (1, 1, date('now', '+30 day')), (2, 2, date('now', '+30 day')), (3, 1, date('now', '+30 day'));
10.4 常用查询
-- 1. 按书名搜索图书 SELECT b.title, b.author, c.name AS category, b.stock FROM books b LEFT JOIN categories c ON b.category_id = c.id WHERE b.title LIKE '%SQLite%'; -- 2. 查看所有借阅记录 SELECT r.name AS reader, b.title AS book, br.borrow_date, br.due_date, br.return_date FROM borrow_records br INNER JOIN readers r ON br.reader_id = r.id INNER JOIN books b ON br.book_id = b.id; -- 3. 查看逾期未还的图书 SELECT r.name AS reader, b.title AS book, br.borrow_date, br.due_date FROM borrow_records br INNER JOIN readers r ON br.reader_id = r.id INNER JOIN books b ON br.book_id = b.id WHERE br.return_date IS NULL AND br.due_date < date('now'); -- 4. 统计每个分类的图书数量 SELECT c.name AS category, COUNT(*) AS count FROM books b INNER JOIN categories c ON b.category_id = c.id GROUP BY c.name; -- 5. 查看每个读者借阅的图书数量 SELECT r.name, COUNT(br.id) AS borrowed_count FROM readers r LEFT JOIN borrow_records br ON r.id = br.reader_id GROUP BY r.id;
10.5 Python完整实现
以下是使用Python实现的图书管理系统核心代码：
import sqlite3 from datetime import datetime, timedelta DB_PATH = 'library.db' class LibraryManager: def __init__(self, db_path): self.conn = sqlite3.connect(db_path) self.conn.row_factory = sqlite3.Row self.conn.execute('PRAGMA foreign_keys = ON') self.cursor = self.conn.cursor() def add_book(self, title, author, isbn, category_id, stock=1): try: self.cursor.execute( 'INSERT INTO books (title, author, isbn, category_id, stock) ' 'VALUES (?, ?, ?, ?, ?)', (title, author, isbn, category_id, stock) ) self.conn.commit() print(f'图书《{title}》添加成功，ID: {self.cursor.lastrowid}') except sqlite3.IntegrityError as e: print(f'添加失败: {e}') def search_books(self, keyword): self.cursor.execute( 'SELECT b.*, c.name AS category_name FROM books b ' 'LEFT JOIN categories c ON b.category_id = c.id ' 'WHERE b.title LIKE ? OR b.author LIKE ?', (f'%{keyword}%', f'%{keyword}%') ) results = self.cursor.fetchall() if not results: print('未找到匹配的图书') return [] for book in results: status = f'库存{book["stock"]}本' if book['stock'] > 0 else '已借完' print(f'[{book["id"]}] 《{book["title"]}》 - {book["author"]} ({book["category_name"]}) - {status}') return results def borrow_book(self, reader_id, book_id, days=30): try: self.cursor.execute('BEGIN TRANSACTION') # 检查库存 self.cursor.execute('SELECT stock, title FROM books WHERE id = ?', (book_id,)) book = self.cursor.fetchone() if not book: raise ValueError('图书不存在') if book['stock'] <= 0: raise ValueError('库存不足') # 创建借阅记录 due_date = (datetime.now() + timedelta(days=days)).strftime('%Y-%m-%d') self.cursor.execute( 'INSERT INTO borrow_records (book_id, reader_id, due_date) ' 'VALUES (?, ?, ?)', (book_id, reader_id, due_date) ) # 减少库存 self.cursor.execute( 'UPDATE books SET stock = stock - 1 WHERE id = ?', (book_id,) ) self.conn.commit() print(f'借阅成功：《{book["title"]}》应于 {due_date} 前归还') except Exception as e: self.conn.rollback() print(f'借阅失败: {e}') def return_book(self, reader_id, book_id): try: self.cursor.execute('BEGIN TRANSACTION') # 查找未归还的借阅记录 self.cursor.execute( 'SELECT id FROM borrow_records ' 'WHERE book_id = ? AND reader_id = ? AND return_date IS NULL', (book_id, reader_id) ) record = self.cursor.fetchone() if not record: raise ValueError('未找到借阅记录') # 更新归还日期 self.cursor.execute( 'UPDATE borrow_records SET return_date = datetime("now", "localtime") ' 'WHERE id = ?', (record['id'],) ) # 增加库存 self.cursor.execute( 'UPDATE books SET stock = stock + 1 WHERE id = ?', (book_id,) ) self.conn.commit() print('归还成功') except Exception as e: self.conn.rollback() print(f'归还失败: {e}') def list_overdue(self): self.cursor.execute( 'SELECT r.name AS reader, b.title AS book, br.due_date ' 'FROM borrow_records br ' 'INNER JOIN readers r ON br.reader_id = r.id ' 'INNER JOIN books b ON br.book_id = b.id ' 'WHERE br.return_date IS NULL AND br.due_date < date("now")' ) results = self.cursor.fetchall() if not results: print('没有逾期未还的图书') for row in results: print(f'{row["reader"]} 借阅的《{row["book"]}》已逾期（应还日期: {row["due_date"]}）') def close(self): self.conn.close() # 使用示例 if __name__ == '__main__': lib = LibraryManager(DB_PATH) # 搜索图书 lib.search_books('Python') # 借书 lib.borrow_book(reader_id=1, book_id=2) # 查看逾期 lib.list_overdue() lib.close()
这个项目涵盖了前面学到的所有知识点：建表、约束、外键、索引、JOIN查询、子查询、事务、Python集成、异常处理等。建议你在此基础上扩展更多功能，如读者注册、图书分类管理、借阅统计报表等。
附录 常见问题FAQ与踩坑指南
A.1 常见问题FAQ
Q1: SQLite最大能存多少数据？
SQLite的数据库文件最大支持281TB，实际限制取决于文件系统和磁盘空间。对于大多数应用场景，SQLite的性能完全足够。单个表的行数没有硬性限制。
Q2: SQLite支持多用户并发吗？
SQLite支持多用户读取，但同一时间只允许一个写入操作。对于读多写少的场景（如网站内容管理），SQLite完全胜任。对于高并发写入场景，建议使用MySQL或PostgreSQL。使用WAL模式可以提高并发读写的性能。
Q3: 如何选择SQLite和MySQL？
选择SQLite：个人项目、桌面应用、移动App、嵌入式设备、测试环境、小型网站、数据分析。选择MySQL：多用户高并发Web应用、大型企业系统、需要网络远程访问的场景。
Q4: SQLite数据库文件可以直接复制使用吗？
可以。SQLite数据库就是一个文件，直接复制即可在任何平台使用。但确保复制时没有程序正在写入数据库，否则可能得到不完整的副本。安全做法是先执行VACUUM INTO命令或使用备份API。
Q5: 为什么我的外键约束不生效？
SQLite默认不启用外键约束。需要在每次连接数据库后执行PRAGMA foreign_keys = ON;才会检查外键约束。这是初学者最常遇到的"坑"之一。
A.2 常见踩坑指南
坑1：忘记提交事务
问题：执行了INSERT/UPDATE/DELETE后数据没保存。
原因：SQLite默认自动提交，但在手动BEGIN TRANSACTION后，必须COMMIT才会保存。
解决方案：确保在修改操作后调用conn.commit()，或使用with语句自动管理。
坑2：SQL注入漏洞
问题：用字符串拼接构造SQL语句，用户输入特殊字符导致SQL注入。
原因：如 cursor.execute(f"SELECT * FROM users WHERE name='{user_input}'")，用户输入 ' OR '1'='1 就能获取所有用户数据。
解决方案：始终使用参数化查询，即用?占位符：cursor.execute('SELECT * FROM users WHERE name=?', (user_input,))。
坑3：LIKE查询不区分大小写的问题
问题：在SQLite中LIKE默认不区分ASCII字母大小写（如LIKE 'a%'能匹配'Apple'），但区分Unicode大小写。
解决方案：如果需要精确的大小写匹配，使用GLOB代替LIKE（GLOB区分大小写），或者使用LOWER()/UPPER()函数转换后比较。
坑4：DELETE没有WHERE条件
问题：DELETE FROM students;清空了整张表。
解决方案：执行DELETE或UPDATE前，先用SELECT查看要操作的数据范围。养成写WHERE条件的习惯。如需清空表，DELETE FROM table比DROP TABLE安全（保留表结构）。
坑5：数据库文件被锁定
问题：出现"database is locked"错误。
原因：多个进程/连接同时尝试写入SQLite数据库。
解决方案：使用WAL模式（PRAGMA journal_mode = WAL），设置忙等待超时（PRAGMA busy_timeout = 5000），或减少长时间运行的事务。
A.3 学习资源推荐
SQLite学习资源
资源 | 类型 | 说明 |
https://www.sqlite.org/docs.html | 官方文档 | SQLite权威文档，包含语法参考和教程 |
https://www.sqlitetutorial.net/ | 在线教程 | 互动式SQLite教程，含在线练习 |
https://www.sqlite.org/lang.html | SQL语法参考 | 完整的SQL语句语法说明 |
https://www.sqlite.org/cli.html | 命令行工具文档 | SQLite命令行工具详细使用说明 |
恭喜你完成了这份SQLite学习教程！通过十章的学习，你已经从零基础掌握了SQLite的完整知识体系：数据库基础概念、SQL语法、表设计、索引、视图、触发器、事务、高级特性、Python集成和性能优化。
学习数据库最重要的不是记住所有语法，而是理解"如何用结构化的方式组织和查询数据"。建议你多动手实践，在实际项目中运用所学知识，这是巩固知识的最佳方式。
浙公网安备 33010602011771号