SQLite是世界上最广泛部署的数据库引擎，以其轻量、零配置和嵌入式特性著称。本教程将带你从基础到高级全面掌握SQLite的使用技巧，特别强化了数据库连接和命令行操作部分。
一、安装与配置
Windows安装
- 访问 SQLite官网
- 下载
sqlite-tools-win32-*.zip - 解压后添加目录到系统PATH
- 命令行输入
sqlite3验证安装
Linux/macOS安装
# Debian/Ubuntu
sudo apt install sqlite3
# macOS (需先安装Homebrew)
brew install sqlite二、数据库连接与基本操作
创建并连接数据库
# 创建或打开数据库（不存在则创建）
sqlite3 mydatabase.db成功连接后，提示符变为：
sqlite>实时操作示例
-- 创建用户表
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 插入数据
INSERT INTO users (name, email) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com');
-- 查询数据
SELECT * FROM users;
-- 退出SQLite命令行
.quit连接到现有数据库
# 连接到已存在的数据库
sqlite3 existing_db.db内存数据库（临时数据库）
# 创建内存中的临时数据库（关闭后消失）
sqlite3 :memory:三、SQLite命令行工具高级用法
常用点命令
| 命令 | 说明 |
|---|---|
.open test.db | 打开/创建数据库 |
.tables | 显示所有表 |
.schema users | 显示表结构 |
.mode column | 列模式显示 |
.headers on | 显示列名 |
.show | 显示当前配置 |
.quit | 退出命令行 |
.help | 显示帮助信息 |
数据导入导出实战
导出数据到CSV：
sqlite3 mydatabase.db
.headers on
.mode csv
.output users.csv
SELECT * FROM users;
.output stdout从CSV导入数据：
-- 创建临时表
CREATE TABLE temp_import(
name TEXT,
email TEXT
);
-- 导入数据
.mode csv
.import /path/to/new_users.csv temp_import
-- 插入到主表
INSERT INTO users (name, email)
SELECT name, email FROM temp_import;
-- 清理临时表
DROP TABLE temp_import;备份与恢复
完整备份：
sqlite3 mydatabase.db ".backup 'backup.db'"增量备份（使用WAL模式）：
# 启用WAL模式
sqlite3 mydatabase.db "PRAGMA journal_mode=WAL;"
# 创建备份
cp mydatabase.db backup.db
cp mydatabase.db-wal backup.db-wal
cp mydatabase.db-shm backup.db-shm四、Python集成实战
import sqlite3
from datetime import datetime
# 连接数据库（不存在则创建）
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
# 创建表
cursor.execute('''CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
user_id INTEGER,
product TEXT,
amount REAL,
order_date TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id))''')
# 插入订单数据
user_id = 1
orders = [
(user_id, 'Laptop', 1200.50, datetime.now()),
(user_id, 'Mouse', 25.99, datetime.now())
]
cursor.executemany("INSERT INTO orders (user_id, product, amount, order_date) VALUES (?, ?, ?, ?)", orders)
# 复杂查询：用户及其订单
cursor.execute('''SELECT users.name, orders.product, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id''')
for row in cursor.fetchall():
print(f"{row} purchased {row} for ${row:.2f}")
# 提交并关闭连接
conn.commit()
conn.close()五、高级特性与优化
事务处理
BEGIN TRANSACTION;
-- 转账操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交事务
COMMIT;
-- 出错时回滚
-- ROLLBACK;性能优化技巧
- 索引优化：
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(order_date);- WAL模式：
PRAGMA journal_mode=WAL;-- 启用写前日志- 缓存调整：
PRAGMA cache_size = -2000;-- 设置2000页缓存(约3.2MB)- 查询优化：
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';全文搜索
-- 创建虚拟表
CREATE VIRTUAL TABLE docs USING fts5(title, content);
-- 插入数据
INSERT INTO docs VALUES
('SQLite Guide', 'Comprehensive guide to SQLite database'),
('Python Tutorial', 'Learn Python programming language');
-- 全文搜索
SELECT * FROM docs WHERE docs MATCH 'guide database';六、图形化管理工具推荐
- DB Browser for SQLite：
- 开源可视化工具
- 下载地址：https://sqlitebrowser.org/
- SQLiteStudio：
- 跨平台多功能管理器
- 下载地址：https://sqlitestudio.pl/
七、常见问题解决方案
数据库锁定问题
# 查看锁定进程
lsof mydatabase.db
# 安全解锁
cp mydatabase.db unlocked.db
sqlite3 unlocked.db "PRAGMA wal_checkpoint;"数据库修复
# 导出数据
sqlite3 corrupt.db .dump > recovery.sql
# 重建数据库
sqlite3 new.db < recovery.sql跨数据库操作
-- 附加另一个数据库
ATTACH DATABASE 'another.db' AS other;
-- 跨数据库查询
SELECT * FROM main.users
UNION ALL
SELECT * FROM other.users;
-- 分离数据库
DETACH DATABASE other;八、实际应用场景
移动应用数据存储
// Android示例
SQLiteDatabase db = openOrCreateDatabase("app_data.db", MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)");
ContentValues values = new ContentValues();
values.put("key", "theme");
values.put("value", "dark");
db.insert("settings", null, values);网站数据分析
# 日志分析示例
import sqlite3
conn = sqlite3.connect('weblog.db')
conn.execute('''CREATE TABLE IF NOT EXISTS visits
(id INTEGER PRIMARY KEY,
ip TEXT,
url TEXT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)''')
# 模拟插入访问记录
conn.execute("INSERT INTO visits (ip, url) VALUES (?, ?)",
('192.168.1.1', '/homepage'))
conn.commit()学习资源推荐
- SQLite官方文档 - 最权威的参考指南
- SQLite Tutorial - 交互式学习平台
- SQLite Fiddle - 在线SQLite测试环境
- SQLite Viewer - 在线查看SQLite文件
graph TD
A[启动SQLite] --> B{数据库存在？}
B -->|是| C[打开数据库]
B -->|否| D[创建新数据库]
C --> E[执行SQL操作]
D --> E
E --> F[保存更改]
F --> G[退出SQLite]通过本增强版教程，你不仅掌握了SQLite的核心操作，还学习了实际开发中的高级技巧。SQLite的轻量级特性使其成为嵌入式系统、移动应用和小型网站的完美选择，立即开始你的SQLite项目实践吧！
