一文轻松掌握SQLite3基本用法(收藏版)

目录

• 一、SQLite3简介
• 二、SQLite3的安装
• 三、SQLite3基本操作
• 3.1 创建数据库
• 3.2 创建表
• 3.3 插入数据
• 3.4 查询数据
• 3.5 更新数据
• 3.6 删除数据
• 3.7 创建索引
• 3.8 使用事务
• 四、SQLite3高级功能
• 4.1 触发器
• 4.2 视图
• 4.3 参数化查询
• 4.4 导入和导出数据
• 五、实际案例
• 5.1 项目背景
• 5.2 数据库设计
• 5.3 操作示例
• 插入图书数据
• 借阅图书
• 归还图书

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如python Tcl、C#、php、Java等，还有ODBC接口，同样比起mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2021年已经接近有21个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

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

一、SQLite3简介

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

二、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');
-- 假设此操作失败
UpythonPDATE 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 SEBcaouCjlT return_date = '2023-07-15' WHERE book_id = 1 AND return_date IS NULL;
COMMIT;

到此这篇关于一篇文章带你掌握SQLite3基本用法的文章就介绍到这了,更多相关SQLite3用法内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！