SQLite 代码详细讲解与示例

SQLite 是一个轻量级的嵌入式关系型数据库，它不需要独立的服务器进程或系统来操作。SQLite 数据库被存储在一个单一的磁盘文件中，非常适合于嵌入式应用、测试和小型到中型的网站。

本文将详细讲解 SQLite 的基本操作，并提供相应的代码示例，帮助您快速上手。

目录

  1. 创建数据库
  2. 创建表
  3. 插入数据
  4. 查询数据
  5. 更新数据
  6. 删除数据
  7. 事务处理
  8. 索引与视图
  9. 准备语句（Prepared Statements）

创建数据库

SQLite 不需要显式地创建数据库，只需在连接数据库时指定数据库文件名，如果该文件不存在，SQLite 会自动创建。

1
sqlite3 mydatabase.db

上述命令将在当前目录下创建一个名为 mydatabase.db 的数据库文件，并进入 SQLite 命令行界面。

创建表

使用 CREATE TABLE 语句来创建表。下面创建一个名为 users 的表，包含用户的基本信息。

1
2
3
4
5
6
7
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER,
    registered_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

解释：

  • id：主键，自动递增。
  • name：用户姓名，不能为空。
  • email：电子邮件，唯一且不能为空。
  • age：用户年龄。
  • registered_at：注册时间，默认值为当前时间。

插入数据

使用 INSERT INTO 语句向表中插入数据。

1
2
3
INSERT INTO users (name, email, age) VALUES ('张三', '[email protected]', 28);
INSERT INTO users (name, email, age) VALUES ('李四', '[email protected]', 32);
INSERT INTO users (name, email) VALUES ('王五', '[email protected]');  -- age 留空

查询数据

使用 SELECT 语句从表中检索数据。

查询所有用户：

1
SELECT * FROM users;

指定字段查询：

1
SELECT name, email FROM users;

条件查询：

1
SELECT * FROM users WHERE age > 30;

排序查询：

1
SELECT * FROM users ORDER BY age DESC;

分页查询：

1
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 跳过前20条，取接下来的10条记录

更新数据

使用 UPDATE 语句修改表中的数据。

1
UPDATE users SET age = 29 WHERE name = '张三';

解释：

  • 将姓名为 '张三' 的用户年龄更新为 29。

删除数据

使用 DELETE 语句从表中删除数据。

1
DELETE FROM users WHERE id = 3;

解释：

  • 删除 id 为 3 的用户。

注意：

  • 如果不加 WHERE 条件，DELETE 将删除表中所有数据。

事务处理

事务用于确保一组 SQL 操作的原子性。使用 BEGIN TRANSACTION 开始事务，COMMIT 提交事务，ROLLBACK 回滚事务。

示例：

1
2
3
4
5
6
BEGIN TRANSACTION;

INSERT INTO users (name, email, age) VALUES ('赵六', '[email protected]', 25);
UPDATE users SET age = age + 1 WHERE name = '李四';

COMMIT;

解释：

  • 开始事务，插入一条新用户记录，并将 '李四' 的年龄加 1。
  • 如果其中任何一条语句失败，都可以使用 ROLLBACK 取消整个事务。

索引与视图

创建索引：

索引可以提高查询速度。

1
CREATE INDEX idx_users_email ON users(email);

解释：

  • 为 users 表的 email 字段创建索引。

创建视图：

视图是基于 SQL 查询的虚拟表。

1
2
CREATE VIEW v_users_over_30 AS
SELECT * FROM users WHERE age > 30;

解释：

  • 创建一个名为 v_users_over_30 的视图，包含年龄大于 30 的用户。

使用视图：

1
SELECT * FROM v_users_over_30;

准备语句（Prepared Statements）

准备语句用于执行类似的 SQL 语句多次，具有更高的性能和安全性，尤其是防止 SQL 注入。

示例（在命令行中使用）：

1
2
3
4
5
6
sqlite> PREPARE stmt FROM 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
sqlite> BIND PARAMETER 1 '孙七';
sqlite> BIND PARAMETER 2 '[email protected]';
sqlite> BIND PARAMETER 3 22;
sqlite> EXECUTE stmt;
sqlite> FINALIZE stmt;

注意：

  • 在实际开发中，准备语句通常在编程语言（如 Python、C 等）中使用。

在编程语言中使用 SQLite

下面以 Python 为例，展示如何在代码中操作 SQLite 数据库。

Python 示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import sqlite3

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

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

# 插入数据
cursor.execute('INSERT INTO products (name, price) VALUES (?, ?)', ('苹果', 3.5))
cursor.execute('INSERT INTO products (name, price) VALUES (?, ?)', ('香蕉', 2.8))

# 查询数据
cursor.execute('SELECT * FROM products')
rows = cursor.fetchall()
for row in rows:
    print(row)

# 提交事务并关闭连接
conn.commit()
conn.close()

解释：

  • 使用 sqlite3 模块连接数据库并执行 SQL 操作。
  • 使用 ? 占位符防止 SQL 注入。

总结

以上就是 SQLite 的基本操作和代码示例。通过这些内容，您应该能够创建和管理 SQLite 数据库中的表、数据和索引，并在应用程序中使用 SQLite。

建议：

  • 熟悉 SQL 的基本语法，有助于更好地使用 SQLite。
  • 善于使用事务和索引，提升数据库的性能和安全性。
https://withesse.co/post/sqlite-cheatsheet/
Author
zt
Posted on
May 27, 2025
Licensed under