SQLite 临时存储 - 创建、使用和自动清理

介绍

在这个实验中，我们将探索 SQLite 临时存储，重点是创建和使用临时表。临时表提供了一种机制，用于在数据库连接中存储中间结果或仅在短时间内需要的数据。

本实验将指导你使用 CREATE TEMP TABLE 语句创建一个名为 temp_employees 的临时表。你将了解临时表的作用域、生命周期和命名约定，理解它们与常规表的区别。后续步骤将涉及使用数据填充临时表、查询数据，以及确认在关闭数据库连接时自动清理临时表。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") sqlite/SQLiteGroup -.-> sqlite/end_db("Close Database Connection") subgraph Lab Skills sqlite/make_table -.-> lab-552557{{"SQLite 临时存储"}} sqlite/add_rows -.-> lab-552557{{"SQLite 临时存储"}} sqlite/get_all -.-> lab-552557{{"SQLite 临时存储"}} sqlite/query_where -.-> lab-552557{{"SQLite 临时存储"}} sqlite/sort_data -.-> lab-552557{{"SQLite 临时存储"}} sqlite/verify_table -.-> lab-552557{{"SQLite 临时存储"}} sqlite/end_db -.-> lab-552557{{"SQLite 临时存储"}} end

创建临时表

在此步骤中，你将学习如何在 SQLite 中创建临时表。临时表对于在复杂查询期间存储中间结果或保存仅在短时间内需要的数据非常有用。当数据库连接关闭时，它们会自动删除。

首先，在 LabEx VM 中打开你的终端。你的默认路径是 /home/labex/project。

现在，让我们创建一个名为 employees.db 的 SQLite 数据库。运行以下命令来创建数据库文件并打开 SQLite 命令行工具：

sqlite3 employees.db

你将看到一个提示，表明你现在位于 SQLite shell 中：

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

要创建临时表，你需要使用 CREATE TEMP TABLE 语句。语法与创建常规表类似：

CREATE TEMP TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

让我们创建一个名为 temp_employees 的临时表来存储员工信息。在 sqlite> 提示符下输入以下 SQL 命令，然后按 Enter 键：

CREATE TEMP TABLE temp_employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    department TEXT
);

此命令创建一个名为 temp_employees 的临时表，其中包含三列：id（一个自动递增的整数主键）、name（文本）和 department（文本）。

CREATE TEMP TABLE temp_employees：此 SQL 语句创建一个名为 temp_employees 的临时表。TEMP 关键字指定此表是临时的，并且在数据库连接关闭时将自动删除。

id INTEGER PRIMARY KEY AUTOINCREMENT：这定义了一个名为 id 的列作为整数，将其设置为主键，并将其配置为在每次插入新行时自动递增。

name TEXT：这定义了一个名为 name 的列作为文本字段，用于存储员工姓名。

department TEXT：这定义了一个名为 department 的列作为文本字段，用于存储每个员工所属的部门。

将数据插入临时表

现在你已经创建了 temp_employees 表，让我们向其中添加一些数据。我们将向表中插入几个员工记录。

你可以使用 INSERT INTO 语句将数据插入临时表，就像使用常规表一样。

通过在 sqlite> 提示符下逐个运行以下命令，将以下员工记录插入到 temp_employees 表中：

INSERT INTO temp_employees (name, department) VALUES ('Alice', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Bob', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Charlie', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('David', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Eve', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Frank', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('Grace', 'HR');
INSERT INTO temp_employees (name, department) VALUES ('Henry', 'Sales');

这些命令将 8 行添加到 temp_employees 表中。

INSERT INTO temp_employees (name, department)：这指定你正在将数据插入到 temp_employees 表的 name 和 department 列中。

VALUES ('Alice', 'Sales')：这提供了要为每个记录插入的值。

要确认数据已正确添加，请运行以下命令以查看表中的所有记录：

SELECT * FROM temp_employees;

预期输出：

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

此输出显示每个记录的 id、name 和 department。SELECT * 命令检索指定表中的所有列。

从临时表查询数据

在此步骤中，你将学习如何使用带有不同子句的 SELECT 语句从临时表中检索数据。查询数据是使用数据库的核心技能，因为它允许你查看和分析存储的信息。

在 sqlite> 提示符下，让我们首先检索 temp_employees 表中的所有数据。运行以下命令：

SELECT * FROM temp_employees;

预期输出：

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

SELECT * 中的 * 表示“所有列”，因此该命令显示 temp_employees 表中的每个列和行。

接下来，使用带有 WHERE 子句的条件来过滤数据。要仅查看“Sales”部门的员工记录，请运行：

SELECT * FROM temp_employees WHERE department = 'Sales';

预期输出：

1|Alice|Sales
4|David|Sales
8|Henry|Sales

WHERE 子句将结果缩小到与条件匹配的行，在本例中，department 为“Sales”。

SELECT * FROM temp_employees 选择 temp_employees 表中的所有列。
WHERE department = 'Sales' 过滤结果，仅包括 department 列等于“Sales”的行。

现在，使用 ORDER BY 子句按姓名对数据进行排序：

SELECT * FROM temp_employees ORDER BY name;

预期输出：

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

ORDER BY 子句根据指定的列（在本例中为 name 列）对结果进行排序。

最后，使用 LIMIT 子句限制结果的数量。要仅查看前 3 条记录，请运行：

SELECT * FROM temp_employees LIMIT 3;

预期输出：

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering

LIMIT 子句限制查询返回的行数。

确认临时表的自动清理

在此步骤中，你将确认当数据库连接关闭时，临时表 temp_employees 会自动删除。这是临时表的一个关键特性。

首先，通过键入以下内容退出 SQLite shell（SQLite 命令行界面）：

.exit

这将关闭与 employees.db 数据库的连接。

现在，通过运行以下命令重新打开 SQLite shell：

sqlite3 employees.db

尝试查询 temp_employees 表：

SELECT * FROM temp_employees;

你应该会看到一条错误消息，指示该表不存在：

Error: no such table: temp_employees

这证实了先前的数据库连接关闭时，临时表已自动删除。

SELECT * FROM temp_employees：此 SQL 语句尝试从 temp_employees 表中选择所有列和行。

Error: no such table: temp_employees：此错误消息表明 temp_employees 表在数据库中不存在，确认它在数据库连接关闭时已自动删除。

总结

在这个实验中，你学习了如何在 SQLite 中创建和使用临时表。你创建了一个名为 temp_employees 的临时表，向其中插入了数据，使用各种 SELECT 语句查询了数据，并确认了当数据库连接关闭时，临时表会自动删除。这些实践练习全面介绍了临时表，为你提供了使用 SQLite 有效处理临时数据的基本知识。