SQLite 代码详细讲解与示例

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

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

---

目录

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

---

创建数据库

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

sqlite3 mydatabase.db

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

---

创建表

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

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 语句向表中插入数据。

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 语句从表中检索数据。

查询所有用户：

SELECT * FROM users;

指定字段查询：

SELECT name, email FROM users;

条件查询：

SELECT * FROM users WHERE age > 30;

排序查询：

SELECT * FROM users ORDER BY age DESC;

分页查询：

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

---

更新数据

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

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

解释：

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

---

删除数据

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

DELETE FROM users WHERE id = 3;

解释：

• 删除 id 为 3 的用户。

注意：

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

---

事务处理

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

示例：

BEGIN TRANSACTION;

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

COMMIT;

解释：

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

---

索引与视图

创建索引：

索引可以提高查询速度。

CREATE INDEX idx_users_email ON users(email);

解释：

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

创建视图：

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

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

解释：

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

使用视图：

SELECT * FROM v_users_over_30;

---

准备语句（Prepared Statements）

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

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

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 示例：

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。
• 善于使用事务和索引，提升数据库的性能和安全性。