SQLite3

• https://sqlite.org
• https://github.com/sqlite/sqlite

参考 Supadata, Firebase

SQLite 必知必会

SQLite 是一个轻量级的关系型数据库管理系统，广泛应用于嵌入式系统、移动应用以及桌面软件等场景。它的特点是无服务器、零配置、跨平台，因此非常适合一些对性能要求较高的场景，尤其是对于小型应用或开发过程中进行快速原型开发时特别有用。

---

1. 基本概念与特点

• 轻量级：SQLite 不需要一个单独的数据库服务器，数据库是以单个文件形式存储在磁盘上。
• 零配置：SQLite 不需要复杂的配置，开箱即用。
• 事务支持：SQLite 完全支持 SQL 的事务，确保数据的一致性。
• 跨平台：SQLite 支持多种平台，如 Linux、Windows、macOS，甚至嵌入式系统。
• SQL 兼容性：SQLite 支持大部分标准的 SQL 语法，除了部分高级功能和优化（如存储过程、视图、函数等）外，基本 SQL 操作都能使用。

---

2. 基本操作

创建数据库与表

SQLite 数据库通常是一个单一的文件，通过 SQL 命令创建。

-- 创建数据库
sqlite3 mydatabase.db

-- 创建表
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

插入数据

-- 插入一条记录
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- 插入多条记录
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'),
                                        ('Charlie', 'charlie@example.com');

查询数据

-- 查询所有用户
SELECT * FROM users;

-- 条件查询
SELECT * FROM users WHERE name = 'Alice';

-- 排序查询
SELECT * FROM users ORDER BY name DESC;

更新数据

-- 更新用户的邮箱
UPDATE users SET email = 'newalice@example.com' WHERE name = 'Alice';

删除数据

-- 删除一条记录
DELETE FROM users WHERE name = 'Alice';

-- 删除所有记录
DELETE FROM users;

---

3. 索引和性能优化

• 创建索引：为提高查询性能，尤其是对大数据集的查询，常常需要创建索引。

CREATE INDEX idx_users_name ON users (name);

• 查询优化：尽量使用适当的索引来优化查询，避免全表扫描。

---

4. 事务管理

SQLite 完全支持事务，可以保证数据的完整性和一致性。

BEGIN TRANSACTION;

-- 执行多个操作
INSERT INTO users (name, email) VALUES ('David', 'david@example.com');
UPDATE users SET email = 'david_updated@example.com' WHERE name = 'David';

COMMIT;  -- 提交事务

• 回滚事务：如果在事务中出现错误，可以回滚所有操作。

ROLLBACK;  -- 回滚事务

---

5. 外键约束

SQLite 支持外键约束，但需要显式启用外键支持（默认情况下是禁用的）。

-- 启用外键支持
PRAGMA foreign_keys = ON;

-- 创建包含外键约束的表
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  product TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

---

6. SQL 特性支持

• 聚合函数：SQLite 支持常见的 SQL 聚合函数，如 COUNT(), SUM(), AVG(), MIN(), MAX() 等。

SELECT COUNT(*) FROM users;  -- 获取用户数量
SELECT AVG(age) FROM users;  -- 获取用户的平均年龄

• 子查询：SQLite 支持简单的子查询，用于更复杂的查询操作。

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE product = 'Laptop');

---

7. PRAGMA 命令

PRAGMA 是 SQLite 中的特殊命令，用于获取或设置数据库的配置参数。以下是一些常用的 PRAGMA 命令：

• 检查数据库的版本：

PRAGMA user_version;

• 启用或禁用外键约束：

PRAGMA foreign_keys = ON;  -- 启用
PRAGMA foreign_keys = OFF;  -- 禁用

• 查看数据库大小：

PRAGMA page_size;  -- 查看每页大小
PRAGMA page_count;  -- 查看数据库总页数

• 调整数据库性能：

PRAGMA cache_size = 2000;  -- 设置缓存大小
PRAGMA synchronous = OFF;  -- 设置同步模式（性能优化，但不保证数据安全）

---

8. 数据导入与导出

• 导入 CSV 文件：

.mode csv
.import mydata.csv users

• 导出为 SQL 脚本：

sqlite3 mydatabase.db .dump > backup.sql

• 导出为 CSV 文件：

.mode csv
.output users.csv
SELECT * FROM users;
.output stdout

---

9. SQLite 的限制

• 不支持并发写操作：SQLite 使用数据库文件锁定机制，因此它在并发写操作时会有限制。
• 不支持多表 JOIN 的一些高级特性，例如 FULL OUTER JOIN 和 RIGHT JOIN，SQLite 仅支持 INNER JOIN, LEFT JOIN 和 CROSS JOIN。
• 文件大小限制：虽然 SQLite 数据库文件的大小理论上可以达到几 TB，但实际操作中，由于文件锁机制，可能存在性能瓶颈。

---

10. 常用命令与工具

• SQLite 命令行工具： SQLite 提供了一个命令行工具，可以通过 sqlite3 命令进入数据库进行交互操作。

  sqlite3 mydatabase.db

• 数据库文件管理：通过操作系统工具进行备份、复制或移动数据库文件。

---

总结

SQLite 是一个轻量级的数据库系统，适用于单机应用、嵌入式系统和原型开发。你在使用 SQLite 时，主要要掌握以下几个方面：

• SQL 基础操作（创建表、增删查改）
• 事务管理与数据一致性
• 外键约束的使用
• 查询优化（索引）
• 数据导入导出

掌握了这些基础内容，你就能有效地使用 SQLite 进行大部分开发工作。如果你有更高级的需求（如并发写入、复杂查询等），可能需要考虑使用其他数据库系统。