Loading... # 一篇文章带你掌握SQLite3基本用法 SQLite3是一种轻量级的嵌入式数据库,广泛应用于移动设备、嵌入式系统和小型应用中。它不需要独立的服务器进程,数据存储在一个单一的磁盘文件中,极大地方便了应用的部署和管理。本文将带你全面掌握SQLite3的基本用法,包括数据库的创建、表的操作、数据的增删改查以及索引和事务的使用。 ![](https://www.8kiz.cn/usr/uploads/2024/07/2884321702.png) ## **一、SQLite3简介** SQLite3是一个C语言库,提供了一个独立、无服务器、零配置、事务性的SQL数据库引擎。其设计目标是嵌入式环境,但也非常适用于小型应用程序和开发测试环境。 ## **二、SQLite3的安装** 在大多数现代操作系统中,SQLite3都已经预装。如果没有预装,可以通过包管理器安装: - **Ubuntu/Debian**: ```bash sudo apt-get install sqlite3 ``` - **macOS**: ```bash brew install sqlite ``` - **Windows**: 从SQLite官方网站下载预编译的二进制文件。 ## **三、SQLite3基本操作** ### **3.1 创建数据库** SQLite3数据库是一个文件,创建数据库实际上是创建一个文件。 ```bash sqlite3 mydatabase.db ``` 上面的命令会创建一个名为 `mydatabase.db`的数据库文件,并启动SQLite3交互式命令行工具。 ### **3.2 创建表** 使用SQL语句创建表。下面的示例创建一个名为 `users`的表,包含 `id`、`name`和 `email`三个字段。 ```sql CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE ); ``` ### **3.3 插入数据** 使用 `INSERT`语句向表中插入数据。 ```sql INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'); ``` ### **3.4 查询数据** 使用 `SELECT`语句查询数据。 ```sql SELECT * FROM users; ``` 可以使用 `WHERE`子句进行条件查询。 ```sql SELECT * FROM users WHERE name = 'Alice'; ``` ### **3.5 更新数据** 使用 `UPDATE`语句更新数据。 ```sql UPDATE users SET email = 'alice_new@example.com' WHERE name = 'Alice'; ``` ### **3.6 删除数据** 使用 `DELETE`语句删除数据。 ```sql DELETE FROM users WHERE name = 'Bob'; ``` ### **3.7 创建索引** 索引可以加快查询速度。使用 `CREATE INDEX`语句创建索引。 ```sql CREATE INDEX idx_users_name ON users (name); ``` ### **3.8 使用事务** 事务确保一组操作要么全部成功,要么全部失败,保持数据的一致性。使用 `BEGIN TRANSACTION`开始事务,`COMMIT`提交事务,`ROLLBACK`回滚事务。 ```sql 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`回滚事务。 ```sql 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`语句创建触发器。 ```sql 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`语句创建视图。 ```sql CREATE VIEW user_emails AS SELECT name, email FROM users; ``` ### **4.3 参数化查询** 在应用程序中使用SQLite3时,参数化查询可以防止SQL注入攻击。 ```python 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`命令导出数据库内容。 ```bash sqlite3 mydatabase.db sqlite> .import data.csv users sqlite> .dump > backup.sql ``` ## **五、实际案例** ### **5.1 项目背景** 假设我们正在开发一个简单的图书管理系统,需要存储图书信息和借阅记录。 ### **5.2 数据库设计** 我们需要两个表:`books`和 `borrow_records`。 ```sql 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 操作示例** #### **插入图书数据** ```sql INSERT INTO books (title, author) VALUES ('1984', 'George Orwell'); INSERT INTO books (title, author) VALUES ('To Kill a Mockingbird', 'Harper Lee'); ``` #### **借阅图书** ```sql 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; ``` #### **归还图书** ```sql 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的基本用法,并能够在实际项目中灵活应用。 **分析说明表** | 操作 | 示例代码 | 说明 | | ---------- | ------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------ | | 创建数据库 | `sqlite3 mydatabase.db` | 创建一个新的SQLite3数据库文件 | | 创建表 | `CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE);` | 创建一个包含三个字段的用户表 | | 插入数据 | `INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');` | 向用户表中插入一条记录 | | 查询数据 | `SELECT * FROM users;` | 查询用户表中的所有记录 | | 更新数据 | `UPDATE users SET email = 'alice_new@example.com' WHERE name = 'Alice';` | 更新用户表中Alice的电子邮件地址 | | 删除数据 | `DELETE FROM users WHERE name = 'Bob';` | 删除用户表中名为Bob的记录 | | 创建索引 | `CREATE INDEX idx_users_name ON users (name);` | 创建一个基于用户名的索引 | | 使用事务 | `BEGIN TRANSACTION; INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com'); COMMIT;` | 使用事务插入数据 | | 创建触发器 | `CREATE TRIGGER user_update_trigger AFTER UPDATE ON users BEGIN INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'UPDATE'); END;` | 创建一个在用户表更新时触发的触发器 | | 创建视图 | `CREATE VIEW user_emails AS SELECT name, email FROM users;` | 创建一个显示用户姓名和电子邮件的视图 | | 参数化查询 | `cursor.execute("SELECT * FROM users WHERE name = ?", (name,))` | 使用参数化查询防 | 止SQL注入 | | 导入数据 | `.import data.csv users` | 从CSV文件导入数据到用户表 | | 导出数据 | `.dump > backup.sql` | 导出数据库内容到SQL文件 | 通过本文的介绍,希望您能深入理解SQLite3的基本用法,并在实际开发中灵活运用这些知识,提升数据管理效率。 最后修改:2024 年 07 月 05 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 如果觉得我的文章对你有用,请随意赞赏