一篇文章带你掌握SQLite3基本用法

SQLite3是一种轻量级的嵌入式数据库，广泛应用于移动设备、嵌入式系统和小型应用中。它不需要独立的服务器进程，数据存储在一个单一的磁盘文件中，极大地方便了应用的部署和管理。本文将带你全面掌握SQLite3的基本用法，包括数据库的创建、表的操作、数据的增删改查以及索引和事务的使用。

一、SQLite3简介

SQLite3是一个C语言库，提供了一个独立、无服务器、零配置、事务性的SQL数据库引擎。其设计目标是嵌入式环境，但也非常适用于小型应用程序和开发测试环境。

二、SQLite3的安装

在大多数现代操作系统中，SQLite3都已经预装。如果没有预装，可以通过包管理器安装：

• Ubuntu/Debian:

  sudo apt-get install sqlite3
  ​
  

• macOS:

  brew install sqlite
  ​
  

• Windows:

  从SQLite官方网站下载预编译的二进制文件。

三、SQLite3基本操作

3.1 创建数据库

SQLite3数据库是一个文件，创建数据库实际上是创建一个文件。

sqlite3 mydatabase.db
​

上面的命令会创建一个名为 mydatabase.db的数据库文件，并启动SQLite3交互式命令行工具。

3.2 创建表

使用SQL语句创建表。下面的示例创建一个名为 users的表，包含 id、name和 email三个字段。

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);
​

3.3 插入数据

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

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
​

3.4 查询数据

使用 SELECT语句查询数据。

SELECT * FROM users;
​

可以使用 WHERE子句进行条件查询。

SELECT * FROM users WHERE name = 'Alice';
​

3.5 更新数据

使用 UPDATE语句更新数据。

UPDATE users SET email = 'alice_new@example.com' WHERE name = 'Alice';
​

3.6 删除数据

使用 DELETE语句删除数据。

DELETE FROM users WHERE name = 'Bob';
​

3.7 创建索引

索引可以加快查询速度。使用 CREATE INDEX语句创建索引。

CREATE INDEX idx_users_name ON users (name);
​

3.8 使用事务

事务确保一组操作要么全部成功，要么全部失败，保持数据的一致性。使用 BEGIN TRANSACTION开始事务，COMMIT提交事务，ROLLBACK回滚事务。

BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');
UPDATE users SET email = 'alice_new@example.com' WHERE name = 'Alice';
COMMIT;
​

如果在事务中发生错误，可以使用 ROLLBACK回滚事务。

BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('David', 'david@example.com');
-- 假设此操作失败
UPDATE users SET email = 'error@example.com' WHERE name = 'Error';
ROLLBACK;
​

四、SQLite3高级功能

4.1 触发器

触发器是在特定事件发生时自动执行的SQL语句。使用 CREATE TRIGGER语句创建触发器。

CREATE TRIGGER user_update_trigger
AFTER UPDATE ON users
BEGIN
    INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'UPDATE');
END;
​

4.2 视图

视图是虚拟表，通过查询结果定义。使用 CREATE VIEW语句创建视图。

CREATE VIEW user_emails AS
SELECT name, email FROM users;
​

4.3 参数化查询

在应用程序中使用SQLite3时，参数化查询可以防止SQL注入攻击。

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

name = 'Alice'
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))
print(cursor.fetchall())

conn.close()
​

4.4 导入和导出数据

SQLite3支持导入和导出数据，可以使用 .import命令导入CSV文件，使用 .dump命令导出数据库内容。

sqlite3 mydatabase.db
sqlite> .import data.csv users
sqlite> .dump > backup.sql
​

五、实际案例

5.1 项目背景

假设我们正在开发一个简单的图书管理系统，需要存储图书信息和借阅记录。

5.2 数据库设计

我们需要两个表：books和 borrow_records。

CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    available INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE borrow_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    book_id INTEGER NOT NULL,
    borrower TEXT NOT NULL,
    borrow_date TEXT NOT NULL,
    return_date TEXT,
    FOREIGN KEY (book_id) REFERENCES books(id)
);
​

5.3 操作示例

插入图书数据

INSERT INTO books (title, author) VALUES ('1984', 'George Orwell');
INSERT INTO books (title, author) VALUES ('To Kill a Mockingbird', 'Harper Lee');
​

借阅图书

BEGIN TRANSACTION;
UPDATE books SET available = 0 WHERE id = 1;
INSERT INTO borrow_records (book_id, borrower, borrow_date) VALUES (1, 'John Doe', '2023-07-02');
COMMIT;
​

归还图书

BEGIN TRANSACTION;
UPDATE books SET available = 1 WHERE id = 1;
UPDATE borrow_records SET return_date = '2023-07-15' WHERE book_id = 1 AND return_date IS NULL;
COMMIT;
​

六、总结

SQLite3作为一种轻量级数据库，具有易用性、高效性和无需配置等优点，适用于各种小型应用和开发测试环境。通过本文的介绍，您应能掌握SQLite3的基本用法，并能够在实际项目中灵活应用。

分析说明表

止SQL注入 |
| 导入数据 | .import data.csv users | 从CSV文件导入数据到用户表 |
| 导出数据 | .dump > backup.sql | 导出数据库内容到SQL文件 |

通过本文的介绍，希望您能深入理解SQLite3的基本用法，并在实际开发中灵活运用这些知识，提升数据管理效率。