---

SQLite 详细教程：全面掌握数据库操作

数据库是现代软件应用的核心组成部分，用于存储、管理和检索数据。在众多数据库系统中，SQLite 因其独特的特性——轻量、无服务器、零配置、文件存储——而在嵌入式设备、移动应用、桌面软件以及小型网站等场景中广受欢迎。本教程将带您深入了解 SQLite，从基础概念到高级操作，助您全面掌握这个强大的数据库引擎。

第一部分：初识 SQLite

1.1 什么是 SQLite？

SQLite 是一个 C 语言库，实现了自给自足的、高可靠的、全功能的 SQL 数据库引擎。它的主要特点包括：

• 无服务器 (Serverless)：与 MySQL、PostgreSQL 等需要独立数据库服务器进程的系统不同，SQLite 数据库直接读取和写入普通磁盘文件。整个数据库就是一个文件。
• 零配置 (Zero Configuration)：无需安装、配置、启动或停止任何服务器进程。第一次使用时创建数据库文件即可。
• 事务性 (Transactional)：完全支持 ACID（原子性、一致性、隔离性、持久性）事务，确保数据操作的可靠性。
• 轻量级 (Lightweight)：代码库小，资源占用低。
• 可移植 (Portable)：数据库文件可以在不同架构的机器之间自由复制和使用。
• 兼容性 (Compatibility)：支持大部分标准 SQL92 语法，易于从其他 SQL 数据库迁移或进行概念上的理解。

1.2 为什么选择 SQLite？

• 简单易用：特别适合小型项目、原型开发或需要嵌入数据库的应用。
• 无需部署复杂环境：开发者可以直接在本地创建和使用数据库。
• 适合离线应用：数据存储在本地文件，即使没有网络连接也能正常工作。
• 学习曲线平缓：SQL 语法是通用的，掌握 SQLite 也为学习其他数据库打下基础。

第二部分：环境准备与基础操作

2.1 安装 SQLite

SQLite 本身是一个库，但为了方便交互，我们通常使用其提供的命令行工具 sqlite3。

• Windows：访问 SQLite 官网 (https://www.sqlite.org/download.html) 下载预编译的二进制文件（通常是 sqlite-tools-win64-x64-xxxxxxx.zip 或类似名称），解压后将包含 sqlite3.exe。将其所在目录添加到系统 PATH 环境变量，或直接在该目录下打开命令行使用。
• macOS：macOS 通常预装了 SQLite。打开终端输入 sqlite3 --version 检查。如果未安装，可以使用 Homebrew 安装：brew install sqlite3。
• Linux：大多数 Linux 发行版仓库中都提供了 SQLite。使用包管理器安装，例如 Debian/Ubuntu: sudo apt-get install sqlite3 libsqlite3-dev；Fedora/CentOS: sudo yum install sqlite sqlite-devel。

2.2 启动 SQLite 命令行工具

打开终端或命令行，输入 sqlite3 命令。

• 创建或打开数据库文件：输入 sqlite3 your_database_name.db。如果 your_database_name.db 文件不存在，SQLite 会创建一个；如果存在，则打开该文件。例如：
  bash
sqlite3 mydata.db
• 只启动工具，稍后创建/打开：直接输入 sqlite3。进入交互模式后，可以使用 .open your_database_name.db 命令来打开或创建数据库文件。

2.3 SQLite 命令行常用命令

在 sqlite3> 提示符下，以点开头的命令是 SQLite 命令行工具自身的命令，而不是 SQL 语句。

• .help：显示所有命令行命令。
• .databases：列出当前打开的所有数据库及其文件名。
• .tables：列出当前数据库中的所有表。
• .schema [table_name]：显示指定表的 CREATE 语句，如果省略表名则显示所有表的 schema。
• .quit 或 .exit：退出命令行工具。
• .mode MODE：设置输出模式（如 column、list、html、csv 等）。
• .headers on/off：开启/关闭输出列头。
• .read filename：执行指定文件中的 SQL 语句或命令行命令。

示例：

“`sqlite
sqlite> .open mydata.db
sqlite> .databases
main: /path/to/your/mydata.db
sqlite> .tables

No tables yet

sqlite> .quit
“`

第三部分：核心 SQL 操作 – CRUD

CRUD 是指创建 (Create)、读取 (Read)、更新 (Update) 和删除 (Delete)，是数据库操作中最基本也是最重要的四项。

3.1 创建表 (CREATE TABLE)

在存储数据之前，需要定义表的结构，包括表名、列名、数据类型和约束。

SQLite 数据类型亲和性 (Affinity)：SQLite 采用动态类型系统，数据类型不像其他数据库那样严格。存储值的数据类型取决于其值本身，而不是列的声明类型。但是，列声明的类型会影响其“类型亲和性”，指导 SQLite 如何处理存储的值。SQLite 支持五种核心存储类：NULL, INTEGER, REAL (浮点数), TEXT (字符串), BLOB (二进制数据)。声明类型名称只影响亲和性，主要有以下几种：

• INTEGER：亲和于 INTEGER。
• TEXT：亲和于 TEXT。
• BLOB：亲和于 BLOB (没有亲和性，直接存储)。
• REAL：亲和于 REAL。
• NUMERIC：亲和于 NUMERIC (可以包含 INTEGER, REAL, TEXT)。

尽管类型是动态的，但为了代码清晰和与其他数据库兼容，建议在 CREATE TABLE 语句中声明具体的类型名称，如 INTEGER, TEXT, REAL, BLOB。SQLite 会根据这些名称赋予相应的亲和性。

语法：

sql
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
columnN datatype [constraints]
);

示例：创建一个用户表

sqlite
sqlite> CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
age INTEGER DEFAULT 18,
email TEXT
);

• id INTEGER PRIMARY KEY AUTOINCREMENT: id 列是整数类型，设为主键 (PRIMARY KEY)，并自动递增 (AUTOINCREMENT)。在 SQLite 中，INTEGER PRIMARY KEY 会成为表的别名，查找速度极快。
• name TEXT NOT NULL UNIQUE: name 列是文本类型，不能为空 (NOT NULL)，且值必须唯一 (UNIQUE)。
• age INTEGER DEFAULT 18: age 列是整数类型，如果插入数据时未指定 age，则默认值为 18。
• email TEXT: email 列是文本类型，没有其他约束。

3.2 插入数据 (INSERT INTO)

向表中添加新行。

语法 1：指定列插入

sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

语法 2：插入所有列（值的顺序必须与表中列的定义顺序一致）

sql
INSERT INTO table_name VALUES (value1, value2, ...);

示例：向 users 表插入数据

sqlite
sqlite> INSERT INTO users (name, age, email) VALUES ('Alice', 30, '[email protected]');
sqlite> INSERT INTO users (name, email) VALUES ('Bob', '[email protected]'); -- age 将使用默认值 18
sqlite> INSERT INTO users VALUES (NULL, 'Charlie', 25, '[email protected]'); -- id 是 AUTOINCREMENT，指定 NULL 或省略都会自动生成

3.3 查询数据 (SELECT)

从表中检索数据。这是最常用的 SQL 操作。

基本语法：

sql
SELECT column1, column2, ... FROM table_name;
SELECT * FROM table_name; -- 选择所有列

过滤数据 (WHERE 子句)： 根据指定条件筛选行。

sql
SELECT column1, ... FROM table_name WHERE condition;

常用的条件运算符： =, <>, >, <, >=, <=, BETWEEN, LIKE, IN, IS NULL, IS NOT NULL, AND, OR, NOT

排序数据 (ORDER BY 子句)： 按照一个或多个列排序结果。

sql
SELECT column1, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...; -- ASC 升序 (默认), DESC 降序

限制结果数量 (LIMIT 子句)： 限制返回的行数，常与 ORDER BY 一起使用。

sql
SELECT column1, ... FROM table_name LIMIT count [OFFSET offset]; -- count 是返回的最大行数，offset 是跳过的行数

示例：查询 users 表数据

sqlite
sqlite> SELECT * FROM users;
sqlite> SELECT name, age FROM users WHERE age > 20; -- 查询年龄大于 20 的用户姓名和年龄
sqlite> SELECT * FROM users WHERE name LIKE 'A%'; -- 查询姓名以 A 开头的用户
sqlite> SELECT * FROM users WHERE age BETWEEN 20 AND 30 ORDER BY age DESC; -- 查询年龄在 20 到 30 之间，按年龄降序排列
sqlite> SELECT name FROM users LIMIT 1; -- 只查询一个用户的姓名
sqlite> SELECT name FROM users ORDER BY id LIMIT 1 OFFSET 1; -- 查询第二个用户的姓名 (跳过1个)

3.4 更新数据 (UPDATE)

修改表中现有行的数据。

语法：

sql
UPDATE table_name SET column1 = new_value1, column2 = new_value2, ... WHERE condition;

注意： 如果省略 WHERE 子句，UPDATE 将修改表中的所有行！

示例：更新 users 表数据

sqlite
sqlite> UPDATE users SET age = 31 WHERE name = 'Alice'; -- 将 Alice 的年龄更新为 31
sqlite> UPDATE users SET email = '[email protected]', age = 19 WHERE name = 'Bob'; -- 更新 Bob 的邮箱和年龄
-- sqlite> UPDATE users SET age = age + 1; -- 将所有用户的年龄加 1 (危险操作，无 WHERE)

3.5 删除数据 (DELETE FROM)

从表中删除现有行。

语法：

sql
DELETE FROM table_name WHERE condition;

注意： 如果省略 WHERE 子句，DELETE 将删除表中的所有行！

示例：删除 users 表数据

sqlite
sqlite> DELETE FROM users WHERE name = 'Charlie'; -- 删除名为 Charlie 的用户
sqlite> DELETE FROM users WHERE age < 18; -- 删除年龄小于 18 的用户
-- sqlite> DELETE FROM users; -- 删除表中所有行 (危险操作)

3.6 删除表 (DROP TABLE)

彻底删除数据库中的一张表及其所有数据和结构。

语法：

sql
DROP TABLE table_name;

示例：

sqlite
sqlite> DROP TABLE users; -- 删除 users 表

第四部分：进阶数据库操作

4.1 约束 (Constraints)

约束用于强制执行数据完整性规则，防止无效数据进入表。我们在 CREATE TABLE 中已经看到一些。

• NOT NULL：列的值不能为 NULL。
• UNIQUE：列的所有值必须唯一。
• PRIMARY KEY：唯一标识表中的每一行。通常是 NOT NULL 和 UNIQUE 的组合。一个表只能有一个主键。
• FOREIGN KEY：确保一个表中的数据的存在依赖于另一个表中的数据（建立表之间的关联）。
• DEFAULT：为列提供默认值。
• CHECK：确保列中的值满足特定条件。

示例：创建带有外键和 Check 约束的订单表

假设我们还有一个 orders 表，需要关联到 users 表。

sqlite
sqlite> CREATE TABLE orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
order_date TEXT NOT NULL,
total_amount REAL CHECK (total_amount > 0), -- 检查金额大于0
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE -- 外键约束，关联 users 表的 id 列，当 users 表中对应的用户被删除时，相关的订单也随之删除
);

• FOREIGN KEY (user_id) REFERENCES users(id)：orders 表的 user_id 列是外键，引用 users 表的 id 列。
• ON DELETE CASCADE: 是外键约束的动作。CASCADE 意味着当引用的 users 表中的行被删除时，orders 表中所有引用了该行的行也会被自动删除。其他选项包括 NO ACTION (默认，如果存在引用则删除失败), RESTRICT (同 NO ACTION), SET NULL (将外键列设为 NULL), SET DEFAULT (将外键列设为默认值)。
• CHECK (total_amount > 0): 确保 total_amount 列的值始终大于 0。

4.2 连接查询 (JOIN)

连接查询用于根据两个或多个表之间的相关列，将这些表中的行组合起来。

常用的连接类型：

• INNER JOIN：只返回在两个表中都存在匹配关系的行。
• LEFT JOIN (或 LEFT OUTER JOIN)：返回左表中的所有行，即使在右表中没有匹配的行。右表中没有匹配的行时，结果集中对应右表列的值为 NULL。

语法：

sql
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column; -- INNER JOIN 语法

sql
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column; -- LEFT JOIN 语法

示例：查询用户及其订单信息

“`sqlite
sqlite> — 先插入一些订单数据
sqlite> INSERT INTO orders (user_id, order_date, total_amount) VALUES (1, ‘2023-10-26’, 150.00); — 假设 Alice 的 id 是 1
sqlite> INSERT INTO orders (user_id, order_date, total_amount) VALUES (1, ‘2023-10-27’, 220.50);
sqlite> INSERT INTO orders (user_id, order_date, total_amount) VALUES (2, ‘2023-10-26’, 88.00); — 假设 Bob 的 id 是 2
sqlite>
sqlite> — 使用 INNER JOIN 查询有订单的用户及其订单详情
sqlite> SELECT users.name, orders.order_id, orders.order_date, orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

sqlite> — 使用 LEFT JOIN 查询所有用户及其订单详情 (包括没有订单的用户)
sqlite> SELECT users.name, orders.order_id, orders.order_date, orders.total_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
“`

4.3 聚合函数与分组 (Aggregate Functions & GROUP BY)

聚合函数对一组值执行计算，并返回单个值。常用的聚合函数包括 COUNT(), SUM(), AVG(), MIN(), MAX()。GROUP BY 子句用于将结果集按照一个或多个列进行分组。

语法：

sql
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1, ...
HAVING group_condition -- HAVING 用于过滤分组
ORDER BY column1, ...;

示例：统计用户订单数量和总金额

“`sqlite
sqlite> SELECT users.name, COUNT(orders.order_id) AS order_count, SUM(orders.total_amount) AS total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id — 使用 LEFT JOIN 包含没有订单的用户
GROUP BY users.name
ORDER BY total_spent DESC; — 按总消费金额降序排列

sqlite> — 统计总金额大于 200 的用户
sqlite> SELECT users.name, SUM(orders.total_amount) AS total_spent
FROM users
INNER JOIN orders ON users.id = orders.user_id — INNER JOIN 即可，因为要统计有订单的用户
GROUP BY users.name
HAVING SUM(orders.total_amount) > 200; — 使用 HAVING 过滤分组后的结果
“`

4.4 索引 (Indexes)

索引是数据库中用于提高查询速度的一种数据结构。它类似于书的目录，可以帮助数据库系统更快地找到特定的行，而无需扫描整个表。

语法：

sql
CREATE INDEX index_name ON table_name (column1, column2, ...);

优点： 显著提高 SELECT 查询和 WHERE 子句的性能，尤其是对于大型表。
缺点： 占用额外的存储空间；会降低 INSERT, UPDATE, DELETE 操作的性能，因为每次数据修改时也需要更新索引。
何时使用： 对经常用于查询条件 (WHERE) 或连接 (JOIN) 的列创建索引。主键列会自动创建索引。

示例：为 users 表的 email 列创建索引

sqlite
sqlite> CREATE INDEX idx_user_email ON users (email);

4.5 事务 (Transactions)

事务是一组数据库操作，这些操作要么全部成功 (COMMIT)，要么全部失败 (ROLLBACK)，不会只成功一部分。这确保了数据库的 ACID 特性，尤其是在涉及多个步骤且必须保持一致性的操作中（如银行转账）。

语法：

sql
BEGIN TRANSACTION; -- 或 BEGIN;
-- 执行一系列 SQL 语句
-- ...
COMMIT; -- 如果所有操作都成功，提交事务，永久保存更改
-- 或
ROLLBACK; -- 如果发生错误或决定取消，回滚事务，撤销所有更改

示例：模拟转账操作

假设有一个 accounts 表 (account_id INTEGER PRIMARY KEY, balance REAL)。

sqlite
sqlite> BEGIN TRANSACTION;
sqlite> -- 从账户 1 扣除 100
sqlite> UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
sqlite> -- 假设这里发生了错误，比如账户 2 不存在
sqlite> -- UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; -- 如果 account_id = 2 不存在，这条语句可能不会报错，但在实际应用中，可能在编程语言层面捕获异常
sqlite> -- 为了演示回滚，我们假设手动发现错误或模拟错误
sqlite> SELECT * FROM accounts WHERE account_id = 2; -- 检查账户 2 是否存在，如果不存在...
sqlite> ROLLBACK; -- 回滚事务，账户 1 的余额不会被扣除

如果一切顺利：

sqlite
sqlite> BEGIN TRANSACTION;
sqlite> UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
sqlite> UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
sqlite> COMMIT; -- 提交事务，两个账户的余额都发生改变

在 SQLite 命令行工具中，默认情况下每条 SQL 语句都是一个独立的事务（称为自动提交模式）。使用 BEGIN TRANSACTION 可以开启手动事务模式。

第五部分：其他实用技巧与工具

5.1 导入/导出数据

• 导入 CSV 数据：
  sqlite
sqlite> .mode csv -- 设置模式为 csv
sqlite> .import path/to/your/file.csv table_name -- 将 csv 文件导入到指定表
• 导出数据到 CSV：
  sqlite
sqlite> .mode csv
sqlite> .headers on -- 如果需要列头
sqlite> .output path/to/your/output.csv -- 将输出重定向到文件
sqlite> SELECT * FROM table_name; -- 执行查询
sqlite> .output stdout -- 将输出重定向回标准输出
• 导出整个数据库结构和数据 (SQL 脚本)：
  bash
sqlite3 your_database_name.db .dump > backup.sql
  这个命令会在终端中执行，而不是在 sqlite3> 提示符下。它生成一个包含 CREATE TABLE 和 INSERT 语句的 SQL 文件，可以用来恢复数据库。
• 从 SQL 文件恢复数据库：
  bash
sqlite3 new_database_name.db < backup.sql
  这将创建一个新的数据库或覆盖现有数据库，并执行 backup.sql 中的所有语句。

5.2 使用 GUI 工具

虽然命令行工具强大，但对于查看数据和管理 schema，GUI 工具更为直观。流行的 SQLite GUI 工具包括：

• DB Browser for SQLite (免费，跨平台)
• SQLiteStudio (免费，跨平台)
• DataGrip (商业，JetBrains出品，功能强大)

这些工具通常提供图形界面来执行查询、创建/修改表、导入/导出数据等。

5.3 在编程语言中使用 SQLite

SQLite 的另一个巨大优势在于其易于集成到各种编程语言中。大多数主流语言都有成熟的 SQLite 驱动或库：

• Python：内置 sqlite3 模块。
• Java：JDBC 驱动 (如 Xerial SQLite JDBC Driver)。
• Node.js：sqlite3 npm 包。
• PHP：PDO_SQLite 扩展。
• C#/ .NET：System.Data.SQLite 或 Microsoft.Data.Sqlite。

通过这些库，您可以在应用程序中无缝地创建、访问和管理 SQLite 数据库。

总结

本教程详细介绍了 SQLite 的基础概念、环境准备、核心 CRUD 操作、进阶的约束、连接、聚合、索引和事务，以及一些实用的命令行技巧和 GUI 工具。

SQLite 是一个功能完备且易于使用的数据库引擎，尤其适用于不需要独立数据库服务器的场景。掌握 SQLite 不仅能帮助您在轻量级应用中高效地存储和管理数据，还能加深您对关系型数据库和 SQL 语言的理解，为学习其他更大型数据库系统打下坚实基础。

数据库的学习重在实践。建议您按照本教程的内容，亲手操作 SQLite 命令行工具，创建表、插入数据、执行各种查询，并尝试更复杂的连接和分组。随着实践的深入，您将能更自信地使用 SQLite 解决实际问题。