特点	说明
无需服务器	不需要安装和配置独立的数据库服务器进程，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表的idsubject 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_ageFROM studentsGROUP 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而非idclass_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 BEGININSERT 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 sqlite3conn = 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 sqlite3conn = 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 sqlite3conn = 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 sqlite3conn = 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 sqlite3try: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 sqlite3def 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 NULLAND 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, timedeltaDB_PATH = 'library.db'class LibraryManager:def __init__(self, db_path):self.conn = sqlite3.connect(db_path)self.conn.row_factory = sqlite3.Rowself.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 resultsdef 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集成和性能优化。

学习数据库最重要的不是记住所有语法，而是理解"如何用结构化的方式组织和查询数据"。建议你多动手实践，在实际项目中运用所学知识，这是巩固知识的最佳方式。

资讯详情

目录

第一章 初识数据库与SQLite

1.2 关系型数据库

1.3.1 SQLite的核心特点