SQLite 教程（完整中文版）

SQLite 是一个轻量级、零配置的嵌入式关系型数据库，广泛用于移动应用、桌面软件和嵌入式系统。本教程从零基础开始，逐步带你掌握 SQLite 的核心概念和实用操作。

---

一、SQLite 简介

特点

• 零配置：无需安装服务器，直接使用 .db 文件
• 单文件数据库：所有数据存储在一个文件中
• 跨平台：支持 Windows、macOS、Linux、iOS、Android
• 支持标准 SQL：兼容大部分 SQL 语法
• 事务支持：ACID 完整性
• 小巧高效：核心库 < 500KB

---

二、安装与工具

1. 安装 SQLite

# Ubuntu/Debian
sudo apt install sqlite3

# macOS (使用 Homebrew)
brew install sqlite

# Windows
# 下载 https://www.sqlite.org/download.html 中的 sqlite-tools-win32-*.zip
# 解压后将 sqlite3.exe 加入 PATH

2. 推荐 GUI 工具

• DB Browser for SQLite（免费）：https://sqlitebrowser.org
• DBeaver（免费）
• Navicat（付费）

---

三、基本操作（命令行）

1. 创建/打开数据库

sqlite3 mydatabase.db

若文件不存在会自动创建

2. 查看数据库信息

.databases      -- 查看当前数据库
.tables         -- 查看所有表
.schema         -- 查看建表语句
.headers on     -- 显示列名
.mode column    -- 列式显示

---

四、SQL 基础操作（CRUD）

1. 创建表

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. 插入数据

INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);
INSERT INTO users (name, email, age) VALUES 
    ('李四', 'lisi@example.com', 30),
    ('王五', 'wangwu@example.com', 22);

3. 查询数据

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT name, age FROM users WHERE age > 25;

-- 模糊查询
SELECT * FROM users WHERE name LIKE '张%';

-- 排序
SELECT * FROM users ORDER BY age DESC;

-- 分页
SELECT * FROM users LIMIT 2 OFFSET 1;

4. 更新数据

UPDATE users SET age = 26 WHERE name = '张三';

5. 删除数据

DELETE FROM users WHERE id = 1;

6. 删除表

DROP TABLE users;

---

五、数据类型

类型	说明
INTEGER	整数（1, 2, 4, 8 字节自动选择）
TEXT	文本字符串（UTF-8）
REAL	浮点数
BLOB	二进制数据（如图片）
NULL	空值

SQLite 是弱类型，允许将字符串存入 INTEGER 列（不推荐）

---

六、常用函数

-- 字符串函数
LENGTH(name), UPPER(name), LOWER(name), SUBSTR(name, 1, 3)

-- 数学函数
ROUND(price, 2), ABS(-10), RANDOM()

-- 日期时间
DATETIME('now'), DATE('now'), STRFTIME('%Y-%m', created_at)

-- 聚合函数
COUNT(*), SUM(age), AVG(age), MAX(age), MIN(age)

---

七、分组与连接

1. 分组统计

SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age 
HAVING count > 1;

2. 表连接

-- 创建第二张表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    product TEXT,
    amount REAL,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

-- 内连接
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

---

八、索引与性能优化

1. 创建索引

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);

2. 查看执行计划

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@b.com';

3. 删除索引

DROP INDEX idx_users_email;

---

九、事务处理

BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('事务测试1');
INSERT INTO users (name) VALUES ('事务测试2');
COMMIT;  -- 或 ROLLBACK;

---

十、在编程中使用 SQLite

Python 示例（使用内置 sqlite3）

import sqlite3

# 连接数据库
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
)
''')

# 插入数据
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ('笔记本', 5999))
conn.commit()

# 查询
cursor.execute("SELECT * FROM products WHERE price > ?", (5000,))
for row in cursor.fetchall():
    print(row)

# 关闭连接
conn.close()

安全做法：参数化查询防 SQL 注入

cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

---

十一、备份与恢复

命令行备份

sqlite3 mydatabase.db ".backup backup.db"

使用 .dump 导出 SQL

sqlite3 mydatabase.db ".dump" > backup.sql

恢复

sqlite3 new.db < backup.sql

---

十二、常见面试题

1. SQLite 适合什么场景？

• 单机应用、移动端、配置存储、日志系统
• 不适合高并发服务器（支持 ~100 QPS）

1. 主键用 INTEGER PRIMARY KEY 有什么特殊？

• 自动创建 ROWID 别名
• AUTOINCREMENT 确保递增（有性能开销）

1. 如何处理大文本或二进制数据？

• 使用 BLOB 类型
• Python 中用 buffer() 或 bytes

---

十三、完整示例：学生成绩管理系统

-- 创建数据库
sqlite3 school.db

-- 创建表
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT,
    class TEXT
);

CREATE TABLE scores (
    id INTEGER PRIMARY KEY,
    student_id INTEGER,
    subject TEXT,
    score INTEGER,
    FOREIGN KEY(student_id) REFERENCES students(id)
);

-- 插入数据
INSERT INTO students (name, class) VALUES ('张三', '高一1班');
INSERT INTO scores (student_id, subject, score) VALUES (1, '数学', 95);

-- 查询平均分
SELECT s.name, AVG(c.score) as avg_score
FROM students s
LEFT JOIN scores c ON s.id = c.student_id
GROUP BY s.id;

---

学习资源推荐

1. 官方文档：https://www.sqlite.org/docs.html
2. SQLite 教程：https://www.sqlitetutorial.net
3. DB Browser：https://sqlitebrowser.org
4. Python 官方文档：https://docs.python.org/3/library/sqlite3.html

---

现在开始练习！
打开终端，输入 sqlite3 test.db，创建一个表，插入几条数据，然后查询试试吧！

需要我生成一个完整的 可运行的 Python + SQLite 项目模板 吗？可以回复 “要” 获取。