SQLite 教程：入门、使用与实战技巧
SQLite 是一款轻量级的、自包含的、无服务器的、零配置的事务性 SQL 数据库引擎。它被广泛应用于嵌入式设备、移动应用、桌面应用，甚至是 Web 应用的后端数据存储。SQLite 库可以直接嵌入到你的应用中，无需单独的数据库服务器进程，使得部署和维护非常方便。
本文将带你从零开始，深入了解 SQLite 的各个方面，包括安装、基本操作、高级特性以及实战技巧，帮助你掌握 SQLite 的使用方法，并能够将其应用到实际项目中。
一、 SQLite 入门
1.1 什么是 SQLite？
SQLite 不是一个独立的数据库服务器进程，而是一个 C 库，它实现了独立的、零配置的、事务性的 SQL 数据库引擎。这意味着：
轻量级： SQLite 的核心库非常小巧，通常只有几百 KB，非常适合资源有限的设备。
自包含： SQLite 数据库存储在一个单一的文件中，方便备份和迁移。
无服务器： 不需要单独的数据库服务器进程，应用可以直接访问数据库文件。
零配置： 安装 SQLite 非常简单，不需要复杂的配置过程。
事务性： SQLite 支持 ACID 事务，保证数据的一致性和可靠性。
1.2 SQLite 的应用场景
SQLite 因其轻量级和易用性，被广泛应用于各种场景：
嵌入式系统： 手机、路由器、智能家居设备等资源受限的设备。
移动应用： Android 和 iOS 应用，用于存储本地数据。
桌面应用： 软件的配置信息、缓存数据、小型数据库等。
网站开发： 作为小型网站的后端数据库，或者用于开发原型。
测试环境： 作为开发环境中的数据库替代方案，方便快速搭建和测试。
1.3 安装 SQLite
SQLite 不需要复杂的安装过程，只需要下载相应的预编译库或通过包管理器安装即可。
Windows:
访问 SQLite 官方网站 (https://www.sqlite.org/download.html) 下载预编译的二进制文件。
将下载的压缩包解压到你喜欢的目录。
将解压后的目录添加到系统的 PATH 环境变量中，以便在命令行中使用 sqlite3 命令。
macOS:
macOS 通常已经预装了 SQLite。你可以在终端中输入 sqlite3 --version 来检查是否安装了 SQLite。如果没有安装，可以使用 Homebrew 安装： brew install sqlite3
Linux:
可以使用包管理器安装 SQLite，例如：
Debian/Ubuntu: sudo apt-get install sqlite3
Fedora/CentOS/RHEL: sudo yum install sqlite
Python:
Python 的标准库 sqlite3 模块提供了 SQLite 的支持，无需额外安装。
二、 SQLite 基本操作
2.1 连接到数据库
使用 sqlite3 命令行工具连接到 SQLite 数据库：
bash
sqlite3 mydatabase.db
如果 mydatabase.db 文件不存在，SQLite 会自动创建一个。
在 Python 中，可以使用 sqlite3 模块连接到数据库：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
“`
2.2 创建表
使用 CREATE TABLE 语句创建表。例如，创建一个名为 users 的表，包含 id、name 和 email 三个字段：
sql
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE
);
INTEGER PRIMARY KEY AUTOINCREMENT: id 字段是整数类型，作为主键，并自动递增。
TEXT NOT NULL: name 字段是文本类型，不能为空。
TEXT UNIQUE: email 字段是文本类型，必须唯一。
在 Python 中创建表：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
cursor.execute(”’
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE
)
”’)
conn.commit()
conn.close()
“`
2.3 插入数据
使用 INSERT INTO 语句插入数据。例如，向 users 表中插入一条数据：
sql
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
在 Python 中插入数据：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
cursor.execute(“INSERT INTO users (name, email) VALUES (?, ?)”, (‘Alice’, ‘[email protected]’))
conn.commit()
conn.close()
“`
使用参数化查询可以有效防止 SQL 注入攻击。
2.4 查询数据
使用 SELECT 语句查询数据。例如，查询 users 表中的所有数据：
sql
SELECT * FROM users;
在 Python 中查询数据：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
cursor.execute(“SELECT * FROM users”)
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()
“`
2.5 更新数据
使用 UPDATE 语句更新数据。例如，将 id 为 1 的用户的 name 修改为 ‘Bob’：
sql
UPDATE users SET name = 'Bob' WHERE id = 1;
在 Python 中更新数据：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
cursor.execute(“UPDATE users SET name = ? WHERE id = ?”, (‘Bob’, 1))
conn.commit()
conn.close()
“`
2.6 删除数据
使用 DELETE FROM 语句删除数据。例如，删除 id 为 1 的用户：
sql
DELETE FROM users WHERE id = 1;
在 Python 中删除数据：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
cursor.execute(“DELETE FROM users WHERE id = ?”, (1,))
conn.commit()
conn.close()
“`
2.7 其他常用 SQL 语句
ALTER TABLE: 修改表的结构，例如添加、删除或修改列。
DROP TABLE: 删除表。
CREATE INDEX: 创建索引，提高查询效率。
DROP INDEX: 删除索引。
三、 SQLite 高级特性
3.1 事务
SQLite 支持 ACID 事务，保证数据的一致性和可靠性。事务可以将多个操作组合成一个原子操作，要么全部成功，要么全部失败。
sql
BEGIN TRANSACTION;
-- 执行多个 SQL 语句
INSERT INTO orders (user_id, product_id) VALUES (1, 101);
UPDATE products SET stock = stock - 1 WHERE id = 101;
COMMIT TRANSACTION;
在 Python 中使用事务：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
try:
cursor.execute(“BEGIN TRANSACTION”)
cursor.execute(“INSERT INTO orders (user_id, product_id) VALUES (?, ?)”, (1, 101))
cursor.execute(“UPDATE products SET stock = stock – 1 WHERE id = ?”, (101,))
conn.commit()
except sqlite3.Error as e:
conn.rollback()
print(f”Transaction failed: {e}”)
finally:
conn.close()
“`
如果事务执行过程中发生错误，可以调用 conn.rollback() 回滚事务，撤销所有操作。
3.2 索引
索引可以提高查询效率，特别是对于大型表。可以为经常用于查询的字段创建索引。
sql
CREATE INDEX idx_email ON users (email);
在 Python 中创建索引：
“`python
import sqlite3
conn = sqlite3.connect(‘mydatabase.db’)
cursor = conn.cursor()
cursor.execute(“CREATE INDEX idx_email ON users (email)”)
conn.commit()
conn.close()
“`
创建索引会增加写入的开销，因此应该谨慎使用索引，只为必要的字段创建索引。
3.3 连接 (JOIN)
连接可以将多个表的数据组合在一起，进行更复杂的查询。
sql
SELECT orders.id, users.name, products.name
FROM orders
JOIN users ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id;
3.4 子查询
子查询可以在 WHERE 子句中使用，用于过滤数据。
sql
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE product_id = 101);
3.5 视图 (VIEW)
视图是一个虚拟表，它基于一个或多个表的查询结果。视图可以简化复杂的查询，并提供数据安全性。
“`sql
CREATE VIEW user_orders AS
SELECT orders.id, users.name, products.name
FROM orders
JOIN users ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id;
SELECT * FROM user_orders;
“`
3.6 触发器 (TRIGGER)
触发器是在特定事件发生时自动执行的 SQL 语句。例如，可以在插入或更新数据时自动执行触发器，用于数据验证或审计。
sql
CREATE TRIGGER check_stock
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
SELECT RAISE(ABORT, 'Stock is insufficient')
WHERE (SELECT stock FROM products WHERE id = NEW.product_id) < 1;
END;
四、 SQLite 实战技巧
4.1 优化查询性能
使用索引： 为经常用于查询的字段创建索引。
避免 SELECT *： 只选择需要的字段，减少数据传输量。
使用 EXPLAIN QUERY PLAN： 分析查询计划，找到性能瓶颈。
避免在 WHERE 子句中使用函数： 在 WHERE 子句中使用函数会阻止索引的使用。
使用连接代替子查询： 连接通常比子查询更有效率。
4.2 数据备份和恢复
备份： 直接复制 SQLite 数据库文件即可。
恢复： 将备份的文件复制回原来的位置即可。
使用 VACUUM 命令： 整理数据库文件，释放未使用的空间。 VACUUM 命令会对整个数据库进行重建，所以执行时间会比较长，并且需要占用额外的磁盘空间。
4.3 处理并发
SQLite 对并发的支持有限，在高并发环境下可能会出现性能问题。可以考虑以下方法：
减少锁冲突： 优化查询语句，减少锁的持有时间。
使用 WAL 模式： WAL (Write-Ahead Logging) 模式可以提高并发性能，但可能会增加数据损坏的风险。
使用连接池： 维护一组数据库连接，减少连接建立的开销。
考虑使用更强大的数据库： 如果需要处理高并发，可以考虑使用 PostgreSQL 或 MySQL 等数据库。
4.4 安全性
防止 SQL 注入攻击： 使用参数化查询，避免直接拼接 SQL 语句。
限制数据库文件的访问权限： 只允许授权用户访问数据库文件。
加密数据库文件： 可以使用 SQLCipher 等工具加密数据库文件。
五、 总结
本文详细介绍了 SQLite 的入门知识、基本操作、高级特性以及实战技巧。通过学习本文，你应该能够掌握 SQLite 的基本使用方法，并能够将其应用到实际项目中。SQLite 是一款非常强大的轻量级数据库引擎，在许多场景下都能发挥重要作用。记住，实践是最好的老师，多动手尝试，才能真正掌握 SQLite 的使用技巧。 祝你在 SQLite 的学习和应用中取得成功！