轻量级SQLite全解析：从建库到视图的核心操作指南

摘要：SQLite作为一款轻量级的嵌入式关系数据库，以其零配置、文件存储、支持标准SQL等特性，在各类应用中广泛使用，像移动应用、桌面程序和物联网设备等场景都有它的身影。

SQLite作为一款轻量级的嵌入式关系数据库，以其零配置、文件存储、支持标准SQL等特性，在各类应用中广泛使用，像移动应用、桌面程序和物联网设备等场景都有它的身影。

在SQLite中，创建数据库非常简单，因为SQLite是基于文件的数据库，当你首次执行连接操作并指定一个不存在的数据库文件名时，数据库就会被自动创建。

在命令行中，使用 sqlite3 命令加上数据库文件名即可创建并进入该数据库的交互环境。

sqlite3 example.db

上述命令会创建一个名为 example.db 的SQLite数据库，如果该数据库已存在，则直接打开。

进入数据库交互环境后，你可以执行各种SQL语句。为了更清晰地查看输出结果，我们可以设置输出模式，比如以列的形式展示数据：

.mode column.header on

.mode column 命令设置输出模式为列模式，使数据以整齐的列格式显示；

.header on 命令则开启表头显示，让每列的字段名也显示出来，方便我们阅读查询结果。

创建数据表是定义数据结构的关键步骤，使用 CREATE TABLE 语句来完成。

CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER,email TEXT,gender TEXT CHECK (gender IN ('Male', 'Female', 'Other')), -- 新增性别字段并设置检查约束created_at TIMESTAMP DEFAULT (datetime('now')), -- 记录创建时间updated_at TIMESTAMP DEFAULT (datetime('now')), -- 记录更新时间phone_number TEXT UNIQUE CHECK(length(phone_number) >= 10) -- 新增电话号码字段，设置唯一约束且长度至少为10);

在这里，我们创建了一个名为 users 的数据表。

IF NOT EXISTS 子句确保只有在表不存在时才会创建，避免重复创建导致的错误。

id 字段被定义为整数类型，作为主键并且会自动递增；

name 字段为文本类型且不能为空；

age 字段用于存储年龄，是整数类型；

email 字段则用来存储用户的邮箱地址。

新增的 gender 字段用于记录性别，并通过 CHECK 约束限定其取值范围为 Male 、 Female 或 Other 。 created_at 和 updated_at 字段使用 TIMESTAMP 类型，默认值为当前时间，分别用于记录数据的创建时间和更新时间。

新添加的 phone_number 字段用于存储电话号码，设置了 UNIQUE 约束确保号码唯一，同时通过 CHECK 约束要求号码长度至少为10。

插入数据使用 INSERT INTO 语句，有两种常见的方式：插入单条数据和批量插入多条数据。

1、插入单条数据

INSERT INTO users (name, age, email, gender, phone_number)VALUES ('Alice', 25, 'alice@example.com', 'Female', '13800138000');

这条语句将一条新的用户记录插入到 users 表中，指定了 name 、 age 、 email 、 gender 和 phone_number 字段的值。由于 created_at 和 updated_at 有默认值，所以不需要显式插入。

2、批量插入多条数据

INSERT INTO users (name, age, email, gender, phone_number) VALUES('Bob', 30, 'bob@example.com', 'Male', '13900139000'),('Charlie', 35, 'charlie@example.com', 'Male', '13600136000');

通过在 VALUES 关键字后使用逗号分隔的多个值列表，可以一次性插入多条记录，大大提高了插入效率。

查询数据是数据库使用中最频繁的操作之一，使用 SELECT 语句来实现。

1、查询所有数据

SELECT * FROM users;

这条语句会返回 users 表中的所有列和所有行的数据， * 代表所有列。

结合前面设置的 .mode column 和 .header on ，查询结果会以更易读的形式展示。

2、根据条件查询数据

SELECT * FROM users WHERE age > 30 AND gender = 'Male' AND email LIKE '%example.com';

这条语句通过 WHERE 子句设置条件，仅返回 age 大于30且 gender 为 Male 并且邮箱地址包含 example.com 的用户记录，实现了有条件的数据筛选。

这里使用了 LIKE 操作符进行模糊查询。

3、排序查询结果

SELECT * FROM users ORDER BY age DESC, name ASC;

这条语句使用 ORDER BY 子句对查询结果进行排序， age DESC 表示按照 age 字段降序排列， name ASC 表示在年龄相同的情况下按照 name 字段升序排列，这样可以更细致地查看数据顺序。

4、聚合查询

SELECT COUNT(*) AS total_users, AVG(age) AS average_ageFROM users;

这条语句使用了聚合函数 COUNT 和 AVG ，分别统计用户总数和平均年龄，并使用 AS 关键字为结果列指定别名，使结果更易理解。

五、数据更新操作

更新数据使用 UPDATE 语句，可对表中的现有数据进行修改。

UPDATE usersSET age = 31,email = 'new_bob@example.com',updated_at = datetime('now'),phone_number = '13700137000'WHERE name = 'Bob';

这条语句将 users 表中 name 为 Bob 的记录的 age 字段更新为31， email 字段更新为 new_bob@example.com ，同时更新 updated_at 字段为当前时间， phone_number 字段更新为新的号码。

通过 WHERE 子句指定更新的条件，确保只修改符合条件的数据。

六、数据删除操作

删除数据使用 DELETE FROM 语句。

DELETE FROM users WHERE name = 'Charlie';

这条语句会删除 users 表中 name 为 Charlie 的记录，同样通过 WHERE 子句指定删除的条件。

如果不使用 WHERE 子句，将会删除表中的所有数据，所以在使用时需要格外小心。

七、事务处理

事务处理确保一组数据库操作要么全部成功执行，要么全部回滚，以保证数据的完整性和一致性。

BEGIN;INSERT INTO users (name, age, email, gender, phone_number)VALUES ('TestUser', 20, 'test@example.com', 'Other', '13400134000');UPDATE users SET age = 21 WHERE name = 'TestUser';-- 模拟错误，尝试插入不满足约束的值-- INSERT INTO users (name, age, email, gender, phone_number) VALUES ('AnotherUser', 22, 'another@example.com', 'InvalidGender', '123');SAVEPOINT savepoint1; -- 设置保存点UPDATE users SET email = 'updated_test@example.com' WHERE name = 'TestUser';ROLLBACK TO SAVEPOINT savepoint1; -- 回滚到保存点-- 继续其他操作INSERT INTO users (name, age, email, gender, phone_number)VALUES ('FinalUser', 23, 'final@example.com', 'Male', '13500135000');COMMIT;

在这段命令中， BEGIN 语句开始一个事务，随后的插入和更新操作都属于这个事务。

设置了保存点 savepoint1 ，在执行部分更新操作后回滚到保存点，然后继续进行其他插入操作。

如果在事务执行过程中没有发生错误， COMMIT 语句会提交事务，将所有操作永久保存到数据库中。如果发生错误，可以使用 ROLLBACK 语句回滚事务，撤销所有未提交的操作。

这里模拟了一个可能导致错误的插入操作（插入不满足 gender 检查约束或 phone_number 约束的值），实际执行时会因为违反约束而报错，此时事务可以回滚以保持数据的一致性。

索引可以加快数据的查询速度，对于经常用于查询条件的字段，可以创建索引。

CREATE INDEX idx_name ON users (name);CREATE INDEX idx_email ON users (email);

上述语句创建了一个名为 idx_name 的索引，基于 users 表的 name 字段，还创建了名为 idx_email 的索引基于 email 字段。

当查询涉及到 name 或 email 字段时，使用这些索引可以显著提高查询效率。

视图是一个虚拟表，它基于查询结果，可以简化复杂的查询操作。

CREATE VIEW young_users_view ASSELECT name, ageFROM usersWHERE age

这条创建了一个名为 young_users_view 的视图，该视图从 users 表中选取年龄小于30的用户的 name 和 age 字段。

通过查询视图可以快速获取相关数据：