SQLite 教程：从入门到实践 – wiki基地

作者： /

SQLite 教程：从入门到实践

引言

在数据管理的世界中，数据库扮演着至关重要的角色。当我们谈论数据库时，通常会想到大型的客户端-服务器（Client-Server）架构数据库，如 MySQL、PostgreSQL 或 SQL Server。然而，还有一类特殊而强大的数据库——嵌入式数据库，其中最著名的莫过于 SQLite。

SQLite 是一个开源的、无需独立服务器进程的、独立的、零配置的事务性 SQL 数据库引擎。这意味着它不需要一个单独的服务器进程来运行，所有数据都存储在一个单一的文件中，可以直接由应用程序访问。它的轻量级、高性能和易用性使其成为许多应用场景的理想选择。

为什么选择 SQLite？

  • 零配置 (Zero-configuration): 不需要安装、配置或管理数据库服务器。
  • 无服务器 (Serverless): 数据直接存储在文件系统中，应用程序直接读写数据库文件。
  • 轻量级 (Lightweight): 库文件通常只有几百KB，内存占用极低。
  • 跨平台 (Cross-platform): 几乎支持所有操作系统，包括 Windows、Linux、macOS、iOS 和 Android。
  • 高可靠性 (High Reliability): 严格遵循 ACID (原子性、一致性、隔离性、持久性) 事务特性。
  • 易于集成 (Easy Integration): 可以轻松嵌入到各种编程语言和应用程序中。
  • 公有领域 (Public Domain): 完全免费使用，无需担心许可问题。

适用场景：

  • 移动应用程序 (Android, iOS)
  • 桌面应用程序
  • Web 浏览器 (如 Chrome, Firefox)
  • 嵌入式设备
  • 物联网 (IoT)
  • 开发和测试环境
  • 小型网站或个人项目

本教程将带领你从 SQLite 的基础概念开始，逐步深入到 SQL 语句的实际操作，并探讨如何在编程语言中应用 SQLite，最终掌握其进阶特性和最佳实践。

第一部分：SQLite 基础

1.1 安装与设置

SQLite 通常无需“安装”。它是一个库文件，可以直接集成到你的应用程序中。但为了方便管理和测试，我们可以安装一个命令行工具 sqlite3。

  • Windows:
    1. 访问 SQLite 官网下载页面 (https://www.sqlite.org/download.html)。
    2. 在 “Precompiled Binaries for Windows” 部分，下载 sqlite-tools-win32-x86-...zip。
    3. 解压到一个你喜欢的目录（例如 C:\sqlite）。
    4. 将该目录添加到系统环境变量 Path 中，这样就可以在任何地方运行 sqlite3 命令。
    5. 打开命令行（CMD 或 PowerShell），输入 sqlite3，如果看到 sqlite> 提示符，则表示安装成功。
  • macOS:
    macOS 系统通常预装了 sqlite3。打开终端，输入 sqlite3 即可。如果需要更新版本，可以通过 Homebrew 安装：
    bash
    brew install sqlite
  • Linux (Ubuntu/Debian):
    在大多数 Linux 发行版中，可以通过包管理器安装：
    bash
    sudo apt update
    sudo apt install sqlite3
    对于 CentOS/RHEL：
    bash
    sudo yum install sqlite

开始一个 SQLite 会话：

在命令行中输入 sqlite3，然后按回车。
bash
sqlite3
你将进入 SQLite 命令行界面：
SQLite version 3.X.X YYYY-MM-DD HH:MM:SS
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
如果你想直接打开或创建一个数据库文件，可以在 sqlite3 命令后面加上文件名：
bash
sqlite3 mydatabase.db
如果 mydatabase.db 不存在，SQLite 会自动创建它；如果存在，则会打开它。

常用 .command (点命令):

在 sqlite> 提示符下，输入以 . 开头的命令来管理数据库环境，而不是执行 SQL 语句。

  • .help: 显示所有点命令。
  • .quit 或 .exit: 退出 SQLite 命令行。
  • .databases: 列出当前连接的所有数据库。
  • .tables: 列出当前数据库中的所有表。
  • .schema [TABLE_NAME]: 显示表结构。
  • .mode MODE: 设置输出模式 (如 csv, column, html, json)。
  • .headers ON/OFF: 显示/隐藏列标题。
  • .dump [TABLE_NAME]: 导出数据库或指定表的 SQL 语句。

1.2 核心概念

  • 数据库文件 (.db, .sqlite, .sqlite3):
    SQLite 数据库的核心是一个单一的文件。这个文件包含了所有的表、索引、视图等数据库对象以及所有的数据。你可以随意命名这个文件，但通常使用 .db、.sqlite 或 .sqlite3 作为扩展名。
  • 表 (Table):
    表是关系型数据库中存储数据的基本结构。它由行 (Row) 和列 (Column) 组成。
  • 列 (Column):
    列定义了表中存储的数据类型和属性。每列都有一个名称和数据类型。

  • 数据类型 (Data Types):
    SQLite 采用的是“弱类型”系统，这意味着你可以在任何列中存储任何类型的数据，尽管你为列声明了特定类型。然而，SQLite 会使用“类型亲和性 (Type Affinity)”来指导数据的存储。
    SQLite 有五种主要存储类型：

    • NULL: 值是 NULL。
    • INTEGER: 带符号的整数，根据值的大小存储为 1, 2, 3, 4, 6 或 8 字节。
    • REAL: 浮点数，存储为 8 字节 IEEE 浮点数。
    • TEXT: 文本字符串，使用数据库编码 (UTF-8, UTF-16BE 或 UTF-16LE) 存储。
    • BLOB: 二进制大对象，完全按输入存储（例如图片、文件）。

    尽管如此，我们仍然会在 CREATE TABLE 语句中声明数据类型，这有助于提高可读性并暗示数据的预期用途。常用的声明类型包括：INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8, CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NATIVE CHARACTER(255), NCHAR(255), TEXT, CLOB, BLOB, REAL, DOUBLE, DOUBLE PRECISION, FLOAT, NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME。

  • 主键 (Primary Key):
    主键是表中一列或一组列，其值能唯一标识表中的每一行。主键列的值必须唯一且不能为 NULL。
    通常，我们会将主键设置为 INTEGER PRIMARY KEY AUTOINCREMENT，这会创建一个自动递增的整数 ID。

  • 外键 (Foreign Key):
    外键是用于建立两个表之间关系的列。它指向另一个表的主键，确保数据的一致性和完整性（参照完整性）。

第二部分：SQL 语句实战

本部分我们将通过实际的 SQL 语句来操作 SQLite 数据库。请在 sqlite3 mydatabase.db 命令下进行操作。

2.1 创建数据库与表

在 SQLite 中，打开一个不存在的文件就相当于创建了一个新数据库。

创建表 (CREATE TABLE):

我们来创建一个 users 表和一个 products 表。

“`sql
— 创建 users 表
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT UNIQUE,
age INTEGER,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

— 创建 products 表
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER DEFAULT 0
);

— 创建 orders 表，演示外键
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
``
**解释：**
*INTEGER PRIMARY KEY AUTOINCREMENT: 定义一个自动递增的主键id。
*TEXT NOT NULL UNIQUE:username列是文本类型，不能为空，且值必须唯一。
*DATETIME DEFAULT CURRENT_TIMESTAMP:created_at列是日期时间类型，默认值为当前时间戳。
*FOREIGN KEY (…) REFERENCES …(…)`: 定义外键约束。

你可以使用 .tables 查看所有表，使用 .schema users 查看 users 表的结构。

2.2 插入数据 (INSERT INTO)

向表中添加新行。

“`sql
— 向 users 表插入单行数据
INSERT INTO users (username, email, age) VALUES (‘alice’, ‘[email protected]’, 30);
INSERT INTO users (username, email, age) VALUES (‘bob’, ‘[email protected]’, 25);
INSERT INTO users (username, email) VALUES (‘charlie’, ‘[email protected]’); — age 未指定，则为 NULL

— 插入多行数据
INSERT INTO products (name, price, stock) VALUES
(‘Laptop’, 1200.00, 50),
(‘Mouse’, 25.50, 200),
(‘Keyboard’, 75.00, 100);

— 向 orders 表插入数据
— 假设 alice 的 id 是 1，bob 的 id 是 2
— 假设 Laptop 的 id 是 1，Mouse 的 id 是 2
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 1, 1); — alice 购买了 1 台 Laptop
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 2, 2); — alice 购买了 2 个 Mouse
INSERT INTO orders (user_id, product_id, quantity) VALUES (2, 3, 1); — bob 购买了 1 个 Keyboard
“`

2.3 查询数据 (SELECT)

从表中检索数据。

基本查询：

“`sql
— 查询 users 表所有列所有行
SELECT * FROM users;

— 查询 products 表的 name 和 price 列
SELECT name, price FROM products;

— 给列起别名
SELECT name AS product_name, price AS current_price FROM products;
“`

条件过滤 (WHERE):

“`sql
— 查询 age 大于 28 的用户
SELECT * FROM users WHERE age > 28;

— 查询 username 为 ‘bob’ 的用户
SELECT * FROM users WHERE username = ‘bob’;

— 查询价格在 50 到 100 之间的产品 (包含边界)
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

— 查询 stock 大于 0 且 price 小于 100 的产品
SELECT * FROM products WHERE stock > 0 AND price < 100;

— 查询 username 是 ‘alice’ 或 ‘charlie’ 的用户
SELECT * FROM users WHERE username IN (‘alice’, ‘charlie’);

— 查询 email 以 ‘@example.com’ 结尾的用户
SELECT * FROM users WHERE email LIKE ‘%@example.com’;

— 查询 age 不为 NULL 的用户
SELECT * FROM users WHERE age IS NOT NULL;
“`

排序 (ORDER BY):

“`sql
— 按 age 升序排序
SELECT * FROM users ORDER BY age ASC;

— 按 price 降序排序
SELECT * FROM products ORDER BY price DESC;

— 按多个列排序，先按 stock 降序，再按 price 升序
SELECT * FROM products ORDER BY stock DESC, price ASC;
“`

限制与偏移 (LIMIT 和 OFFSET):

用于实现分页查询。

“`sql
— 查询前 2 条产品数据
SELECT * FROM products LIMIT 2;

— 查询第 3 条及之后的产品数据 (跳过前 2 条，取 1 条)
SELECT * FROM products LIMIT 1 OFFSET 2;
— 或者等价地
SELECT * FROM products LIMIT 2, 1; — LIMIT offset, count
“`

聚合函数 (COUNT, SUM, AVG, MIN, MAX):

用于对一组值进行计算，返回单个汇总值。

“`sql
— 统计 users 表中有多少行
SELECT COUNT(*) FROM users;

— 统计 age 不为 NULL 的用户数量
SELECT COUNT(age) FROM users;

— 计算所有产品的总库存
SELECT SUM(stock) FROM products;

— 计算产品的平均价格
SELECT AVG(price) FROM products;

— 查找最贵产品的价格
SELECT MAX(price) FROM products;

— 查找最便宜产品的价格
SELECT MIN(price) FROM products;
“`

分组 (GROUP BY) 和 过滤分组 (HAVING):

GROUP BY 用于将具有相同值的行分组。HAVING 用于过滤分组后的结果。

“`sql
— 统计每个 age 段的用户数量
SELECT age, COUNT(*) FROM users GROUP BY age;

— 统计每个 age 段的用户数量，并且只显示用户数量大于 1 的 age 段
SELECT age, COUNT() FROM users GROUP BY age HAVING COUNT() > 1;

— 统计每个用户的订单数量 (通过 JOIN 连接 users 和 orders 表)
SELECT u.username, COUNT(o.order_id) AS total_orders
FROM users AS u
JOIN orders AS o ON u.id = o.user_id
GROUP BY u.username;
“`

联结 (JOIN):

用于组合来自两个或更多表的行。

“`sql
— 查找所有订单以及对应的用户信息和产品信息
SELECT
o.order_id,
u.username,
p.name AS product_name,
o.quantity,
o.order_date
FROM orders AS o
JOIN users AS u ON o.user_id = u.id
JOIN products AS p ON o.product_id = p.id;

— 左联结 (LEFT JOIN): 即使右表没有匹配，也返回左表的所有行
SELECT u.username, o.order_id
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id;
“`

2.4 更新数据 (UPDATE)

修改表中现有数据。务必使用 WHERE 子句，否则将更新所有行。

“`sql
— 更新 id 为 1 的用户的 age
UPDATE users SET age = 31 WHERE id = 1;

— 将所有价格大于 100 的产品库存增加 10
UPDATE products SET stock = stock + 10 WHERE price > 100;

— 同时更新多个列
UPDATE users SET email = ‘[email protected]’, age = 32 WHERE username = ‘alice’;
“`

2.5 删除数据 (DELETE FROM 和 DROP TABLE)

从表中删除行或删除整个表。务必使用 WHERE 子句。

“`sql
— 删除 id 为 3 的用户
DELETE FROM users WHERE id = 3;

— 删除所有 stock 为 0 的产品
DELETE FROM products WHERE stock = 0;

— ⚠️ 慎用！删除 products 表中的所有数据，但不删除表结构
DELETE FROM products;

— ⚠️ 慎用！删除整个 products 表及其所有数据和结构
DROP TABLE products;
“`

2.6 修改表结构 (ALTER TABLE)

SQLite 对 ALTER TABLE 的支持相对有限，但常见的操作如添加列、重命名表和重命名列是支持的。

“`sql
— 添加一个新列 description 到 products 表
ALTER TABLE products ADD COLUMN description TEXT;

— 重命名 products 表为 new_products
ALTER TABLE products RENAME TO new_products;

— SQLite 3.25.0 及以上版本支持重命名列
— ALTER TABLE users RENAME COLUMN email TO contact_email;
“`

第三部分：SQLite 进阶与实践

3.1 索引 (Indexes)

索引是一种特殊查找表，数据库搜索引擎可以更快地检索数据。它类似于一本书的目录。
虽然 SQLite 的主键会自动创建索引，但对于频繁查询的非主键列，创建索引可以显著提高查询速度。

“`sql
— 为 users 表的 email 列创建索引
CREATE INDEX idx_users_email ON users (email);

— 创建一个复合索引 (多个列)
CREATE INDEX idx_products_price_stock ON products (price, stock DESC);

— 删除索引
DROP INDEX idx_users_email;
``
**注意：** 索引会增加写入（INSERT, UPDATE, DELETE）操作的开销，因为每次数据变动时索引也需要更新。因此，只应为那些频繁用于WHERE子句、JOIN条件或ORDER BY` 子句的列创建索引。

3.2 视图 (Views)

视图是虚拟的表。它不存储任何数据，而是基于一个 SELECT 语句的结果集。视图可以简化复杂的查询，提供数据抽象和安全控制。

“`sql
— 创建一个视图，显示所有用户的用户名和电子邮件
CREATE VIEW user_contacts AS
SELECT username, email FROM users;

— 查询视图，就像查询普通表一样
SELECT * FROM user_contacts WHERE username LIKE ‘a%’;

— 删除视图
DROP VIEW user_contacts;
“`

3.3 事务 (Transactions)

事务是一组 SQL 语句，它们被视为一个单一的逻辑工作单元。事务要么全部成功提交 (COMMIT)，要么全部失败回滚 (ROLLBACK)。这保证了数据库的 ACID 特性。

“`sql
— 开启一个事务
BEGIN TRANSACTION;
— 或 BEGIN; 或 BEGIN DEFERRED; 或 BEGIN IMMEDIATE; 或 BEGIN EXCLUSIVE;

— 在事务中执行多个操作
INSERT INTO products (name, price, stock) VALUES (‘Monitor’, 300.00, 70);
UPDATE users SET age = age + 1 WHERE id = 1;

— 如果所有操作都成功，则提交事务
COMMIT;

— 如果发生错误，则回滚事务，撤销所有操作
— ROLLBACK;
``
SQLite 默认每个独立语句都是一个隐式事务。使用BEGIN TRANSACTION` 可以明确控制事务边界。

3.4 与编程语言结合 (以 Python 为例)

SQLite 与各种编程语言都有良好的集成。这里以 Python 为例，展示如何使用 sqlite3 模块。

Python 示例 (sqlite_example.py):

“`python
import sqlite3

1. 连接到数据库 (如果文件不存在则创建)

conn = sqlite3.connect(‘example.db’)
cursor = conn.cursor()

2. 创建表

try:
cursor.execute(”’
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
position TEXT,
hire_date DATE DEFAULT CURRENT_DATE
)
”’)
conn.commit() # 提交事务
print(“Table ’employees’ created successfully (or already exists).”)
except sqlite3.Error as e:
print(f”Error creating table: {e}”)

3. 插入数据

try:
cursor.execute(“INSERT INTO employees (name, position) VALUES (?, ?)”, (‘Alice Smith’, ‘Software Engineer’))
cursor.execute(“INSERT INTO employees (name, position) VALUES (?, ?)”, (‘Bob Johnson’, ‘Project Manager’))
conn.commit()
print(“Data inserted successfully.”)
except sqlite3.Error as e:
print(f”Error inserting data: {e}”)
conn.rollback() # 出现错误时回滚

4. 查询数据

print(“\nAll employees:”)
cursor.execute(“SELECT * FROM employees”)
rows = cursor.fetchall()
for row in rows:
print(row)

print(“\nEmployees hired after 2024-01-01:”)
cursor.execute(“SELECT name, position FROM employees WHERE hire_date > ‘2024-01-01′”)
for row in cursor.fetchall():
print(row)

5. 更新数据

try:
cursor.execute(“UPDATE employees SET position = ? WHERE name = ?”, (‘Senior Engineer’, ‘Alice Smith’))
conn.commit()
print(“\nData updated successfully.”)
except sqlite3.Error as e:
print(f”Error updating data: {e}”)
conn.rollback()

6. 删除数据

try:
cursor.execute(“DELETE FROM employees WHERE name = ?”, (‘Bob Johnson’,))
conn.commit()
print(“\nData deleted successfully.”)
except sqlite3.Error as e:
print(f”Error deleting data: {e}”)
conn.rollback()

print(“\nRemaining employees:”)
cursor.execute(“SELECT * FROM employees”)
for row in cursor.fetchall():
print(row)

7. 关闭连接

conn.close()
print(“\nDatabase connection closed.”)
**运行 Python 脚本：**bash
python sqlite_example.py
``
**解释：**
*sqlite3.connect(‘example.db’): 连接到数据库。如果文件不存在，会创建一个。
*conn.cursor(): 创建一个游标对象，用于执行 SQL 命令。
*cursor.execute(…): 执行 SQL 语句。?是占位符，用于安全地传递参数，防止 SQL 注入。
*conn.commit(): 提交当前事务，使更改永久生效。
*conn.rollback(): 回滚当前事务，撤销所有更改。
*cursor.fetchall(): 获取所有查询结果。
*conn.close()`: 关闭数据库连接。

第四部分：最佳实践与注意事项

何时选择 SQLite

  • 应用程序需要嵌入式数据库： 数据与应用程序打包在一起，无需单独安装数据库服务器。
  • 并发写入需求低： SQLite 在高并发写入场景下性能不如客户端-服务器数据库，因为它通常只允许一个写入操作同时进行。但并发读取性能很好。
  • 数据量不是海量： 数据库文件大小通常在几 GB 到几十 GB 范围内性能最佳。
  • 开发和测试： 易于设置，方便快速迭代。

数据备份

由于 SQLite 数据库是一个单一文件，备份非常简单：直接复制 .db 文件即可。
在数据库正在写入时进行备份可能会导致文件损坏，最佳实践是在数据库不活跃时备份，或者使用 SQLite 的在线备份 API。

性能优化技巧

  1. 合理使用索引： 为 WHERE、JOIN、ORDER BY 子句中频繁使用的列创建索引。
  2. 避免 SELECT *： 只选择你需要的数据列，减少 I/O 和内存消耗。
  3. 使用 VACUUM 命令： 当数据被删除时，数据库文件的大小并不会自动减小。VACUUM 命令可以重组数据库，回收未使用的空间并减小文件大小，同时可能提高性能。
    sql
    VACUUM;
  4. 调整 PRAGMA 设置：
    • PRAGMA journal_mode = WAL;: 默认是 DELETE，WAL (Write-Ahead Logging) 模式可以显著提高并发读取性能，因为读取者不会阻塞写入者。
    • PRAGMA synchronous = NORMAL;: 降低写入操作的同步级别，提高写入速度，但有小概率数据丢失风险。FULL (默认) 最安全，OFF 最快但风险最大。
    • PRAGMA temp_store = MEMORY;: 将临时表存储在内存中，提高复杂查询性能。
      sql
      PRAGMA journal_mode = WAL;
      PRAGMA synchronous = NORMAL;
      PRAGMA temp_store = MEMORY;
      这些 PRAGMA 命令需要在每个数据库连接会话开始时设置。

总结

本教程详细介绍了 SQLite 数据库。我们从其独特的优势和适用场景入手，学习了 sqlite3 命令行工具的安装与使用。接着，我们深入探讨了 SQLite 的核心概念，包括数据类型、表结构、主键和外键。

通过大量的 SQL 语句实战，我们掌握了数据的创建、插入、查询、更新和删除，以及表结构的修改。最后，我们了解了索引、视图、事务等进阶特性，并以 Python 为例展示了 SQLite 在编程语言中的应用，以及一些重要的最佳实践和性能优化技巧。

SQLite 以其轻量、高效、零配置的特性，在嵌入式系统、桌面应用和小型项目中大放异彩。掌握 SQLite，无疑将为你的开发工具箱增添一把利器。现在，你已经具备了从入门到实践 SQLite 的基础知识和操作能力，鼓励你继续探索更复杂的场景和功能！

滚动至顶部