创建和管理 SQLite 数据库

介绍

在这个实验中，你将学习使用 sqlite3 命令行工具操作 SQLite 数据库的基础知识。SQLite 是一种轻量级的、基于文件的数据库系统，非常适合初学者。你将创建数据库、创建表、插入数据、查询数据、添加列、更新数据，最后删除数据库文件。让我们开始吧！

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/link_db("Connect To Database") 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/append_col("Add New Column") sqlite/SQLiteGroup -.-> sqlite/end_db("Close Database Connection") subgraph Lab Skills sqlite/init_db -.-> lab-552337{{"创建和管理 SQLite 数据库"}} sqlite/link_db -.-> lab-552337{{"创建和管理 SQLite 数据库"}} sqlite/make_table -.-> lab-552337{{"创建和管理 SQLite 数据库"}} sqlite/add_rows -.-> lab-552337{{"创建和管理 SQLite 数据库"}} sqlite/get_all -.-> lab-552337{{"创建和管理 SQLite 数据库"}} sqlite/query_where -.-> lab-552337{{"创建和管理 SQLite 数据库"}} sqlite/append_col -.-> lab-552337{{"创建和管理 SQLite 数据库"}} sqlite/end_db -.-> lab-552337{{"创建和管理 SQLite 数据库"}} end

创建 SQLite 数据库并连接

在这一步中，你将创建一个新的 SQLite 数据库文件，并使用 sqlite3 命令行工具连接到它。

在 LabEx VM 环境中打开一个终端。默认情况下，你应该在 /home/labex/project 目录下。通过运行以下命令确认：
```
pwd
```
输出应该是：
```
/home/labex/project
```
如果你不在这个目录下，通过运行以下命令导航到它：
```
cd /home/labex/project
```
创建一个名为 mydatabase.db 的新 SQLite 数据库，并使用 sqlite3 命令连接到它：
```
sqlite3 mydatabase.db
```
如果 mydatabase.db 文件不存在，此命令会创建它并打开一个连接。你将看到 SQLite 提示符：
```
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>
```

sqlite3 命令用于创建和连接 SQLite 数据库。如果指定的数据库文件不存在，SQLite 将创建它。现在你可以在 SQLite 环境中执行 SQL 命令了。

创建 `users` 表

在这一步中，你将在 mydatabase.db 数据库中创建一个名为 users 的表。

确保你仍然连接到上一步中的 mydatabase.db 数据库。如果不是，使用以下命令重新连接：
```
sqlite3 mydatabase.db
```
使用以下 SQL 命令创建一个名为 users 的表：
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);
```
此命令定义了 users 表的结构。id 列是一个整数，并且是主键（primary key），确保每个用户都有一个唯一的标识符。name 列是一个文本字段，不能为空（NOT NULL），age 列是一个整数。

CREATE TABLE 命令是一个基本的 SQL 命令，用于定义数据库中表的结构。PRIMARY KEY 约束确保 id 列包含每行的唯一值，NOT NULL 约束确保 name 列不能为空。

向 `users` 表中插入数据

现在你已经创建了 users 表，你可以向其中插入数据了。这涉及到向表中添加行（记录），每一行代表一个用户及其对应的信息。

确保你仍然连接到 mydatabase.db 数据库。如果不是，使用以下命令重新连接：
```
sqlite3 mydatabase.db
```

使用 INSERT INTO 命令将以下记录插入到 users 表中：

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 22);

这些命令向 users 表添加了三个新行。每一行都包含一个 id、一个 name 和一个 age。

INSERT INTO 命令用于向表中添加新行。VALUES 子句中指定的值被插入到表名后括号中列出的相应列中。

从 `users` 表中查询数据

在这一步中，你将学习如何使用 SELECT 命令从 users 表中查询数据。这允许你从数据库中检索特定的信息。

确保你仍然连接到 mydatabase.db 数据库。如果不是，使用以下命令重新连接：
```
sqlite3 mydatabase.db
```
使用以下 SQL 命令从 users 表中检索所有数据：
```
SELECT * FROM users;
```
此命令选择 users 表中所有行的所有列（*）。输出将显示你在上一步中插入的数据。你应该看到类似这样的内容：
```
1|Alice|25
2|Bob|30
3|Charlie|22
```

SELECT 命令是从数据库中检索数据的强大工具。* 通配符指定应选择所有列。你还可以通过列出它们的名称（用逗号分隔）来选择特定的列（例如，SELECT name, age FROM users;）。

添加新列并更新数据

在这一步中，你将向 users 表添加一个名为 email 的新列，然后使用电子邮件地址更新现有记录。

确保你仍然连接到 mydatabase.db 数据库。如果不是，使用以下命令重新连接：
```
sqlite3 mydatabase.db
```
使用 ALTER TABLE 命令向 users 表添加一个名为 email 的新列：
```
ALTER TABLE users ADD COLUMN email TEXT;
```
此命令向 users 表添加一个类型为 TEXT 的名为 email 的新列。

使用 UPDATE 命令更新每个用户的 email 列：

UPDATE users SET email = '[email protected]' WHERE id = 1;
UPDATE users SET email = '[email protected]' WHERE id = 2;
UPDATE users SET email = '[email protected]' WHERE id = 3;

这些命令使用相应的电子邮件地址更新每个用户的 email 列。

通过查询 users 表来检查更新后的数据：

SELECT * FROM users;

1|Alice|25|[email protected]
2|Bob|30|[email protected]
3|Charlie|22|[email protected]

ALTER TABLE 命令用于修改现有表的结构。在本例中，我们添加了一个新列。UPDATE 命令用于修改表中的现有数据。SET 子句指定要更新的列和新值。WHERE 子句基于条件指定要更新的行。

退出 SQLite 并删除数据库文件

在这一步中，你将退出 SQLite 界面，然后删除数据库文件。由于 SQLite 将所有数据存储在单个文件中，因此删除数据库就像使用 rm 命令删除文件一样简单。请注意，此操作是永久性的！

退出 SQLite 提示符：
```
.quit
```
确保你在 /home/labex/project 目录中。使用以下命令确认：
```
pwd
```
输出应为：
```
/home/labex/project
```
如果不是，请使用以下命令导航到该目录：
```
cd /home/labex/project
```
使用 rm 命令删除 mydatabase.db 文件：
```
rm mydatabase.db
```
如果删除成功，则不会有任何输出。
通过再次列出目录内容来确认该文件已被删除：
```
ls
```
你应该不再在输出中看到 mydatabase.db，从而确认该文件已被删除。

.quit 命令退出 SQLite shell（SQLite 命令行界面）并返回到 Linux 终端。rm 命令是一个标准的 Linux 命令，用于删除文件。在本例中，它永久删除 mydatabase.db 文件，该文件包含 SQLite 数据库的所有数据和结构。

总结

在这个实验中，你学习了使用 sqlite3 命令行工具创建和管理 SQLite 数据库的基本技能。你创建了一个数据库，创建了一个表，插入了数据，查询了数据，添加了一个新列，更新了数据，最后删除了数据库文件。这些步骤为使用 SQLite 数据库提供了坚实的基础。