LabEx
登录免费加入

在 SQLite 中构建表

SQLiteBeginner
立即练习

介绍

在这个实验中，你将学习在 SQLite 中创建和管理表的基本技能。SQLite 是一种轻量级且无服务器的数据库引擎，非常适合小型应用程序。你将探索 SQLite 数据类型，创建带有主键的表，应用诸如 NOT NULL 和 UNIQUE 等约束，使用 .schema 命令检查表结构，并安全地删除表。通过 LabEx VM 环境中的逐步练习，你将获得使用 sqlite3 命令行工具构建和管理数据库表的实践经验。这个实验是为初学者设计的，提供了一条清晰的路径来理解 SQLite 表管理。

连接到 SQLite 并创建 contacts 表

在第一步中，你将连接到 SQLite 数据库并创建你的第一个表，名为 contacts。SQLite 是一种轻量级的数据库引擎，它将数据存储在单个文件中。你将使用 sqlite3 命令行工具与数据库进行交互。

  1. 在 LabEx VM 环境中打开一个终端。确保你在默认工作目录 /home/labex/project 中。

  2. 使用 sqlite3 工具连接到名为 test.db 的 SQLite 数据库。如果数据库文件尚不存在，此命令将创建它。

    sqlite3 test.db

    此命令会打开 SQLite shell，你将看到类似 sqlite> 的提示符，表明你已进入 SQLite 环境。

  3. 创建一个名为 contacts 的表，其中包含 id、name 和 phone 列。在 sqlite> 提示符下输入以下 SQL 命令：

    CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone TEXT
);

    此命令创建一个名为 contacts 的表。id 列是一个整数，并被指定为 PRIMARY KEY（主键），这意味着它将唯一地标识表中的每一行。name 和 phone 列都是 TEXT 类型，可以存储任何字符串数据。

    让我们分解一下这个命令：

    • CREATE TABLE contacts：这告诉 SQLite 创建一个名为 contacts 的新表。
    • id INTEGER PRIMARY KEY：这定义了一个名为 id 的列，其数据类型为 INTEGER（整数）。PRIMARY KEY（主键）约束确保此列中的每个值都是唯一的，并用作每一行的主要标识符。
    • name TEXT：这定义了一个名为 name 的列，其数据类型为 TEXT（文本），可以存储任何字符串数据。
    • phone TEXT：这定义了一个名为 phone 的列，其数据类型为 TEXT（文本），可以存储任何字符串数据。

将数据插入到 contacts 表中

现在你已经创建了 contacts 表，让我们向其中插入一些数据。你将使用 INSERT INTO 语句向表中添加新行。

  1. 将一个新联系人插入到 contacts 表中。在 sqlite> 提示符下运行此命令：

    INSERT INTO contacts (name, phone) VALUES ('John Doe', '123-456-7890');

    此命令将一个新行插入到 contacts 表中，其中姓名是 'John Doe'，电话号码是 '123-456-7890'。id 列将自动分配一个唯一值，因为它是一个主键（primary key）。

    让我们分解一下这个命令：

    • INSERT INTO contacts：这告诉 SQLite 将数据插入到 contacts 表中。
    • (name, phone)：这指定了你要将数据插入到哪些列中。
    • VALUES ('John Doe', '123-456-7890')：这指定了要插入到相应列中的值。

  2. 将另一个联系人插入到 contacts 表中：

    INSERT INTO contacts (name, phone) VALUES ('Jane Smith', '987-654-3210');

    此命令将另一个行插入到 contacts 表中，其中姓名是 'Jane Smith'，电话号码是 '987-654-3210'。

从 contacts 表中检索数据

现在你已经将数据插入到 contacts 表中，让我们检索它。你将使用 SELECT 语句查询表并显示数据。

  1. 从 contacts 表中检索所有数据。在 sqlite> 提示符下运行此命令：

    SELECT * FROM contacts;

    此命令选择 contacts 表中的所有列（*）并显示结果。

    预期输出：

    1|John Doe|123-456-7890
2|Jane Smith|987-654-3210

    输出显示了你插入的两个联系人，以及它们自动分配的 id 值。

    让我们分解一下这个命令：

    • SELECT *：这告诉 SQLite 从表中选择所有列。
    • FROM contacts：这指定了你要从中检索数据的表。

  2. 仅从 contacts 表中检索 name 和 phone 列：

    SELECT name, phone FROM contacts;

    此命令仅选择 contacts 表中的 name 和 phone 列并显示结果。

    预期输出：

    John Doe|123-456-7890
Jane Smith|987-654-3210

创建带有约束的 users 表

约束（constraints）是在表中强制执行数据完整性的规则。让我们向一个名为 users 的新表添加 NOT NULL 和 UNIQUE 约束。

  1. 创建一个名为 users 的表，并带有约束。在 sqlite> 提示符下运行此命令：

    CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);

    此命令创建一个名为 users 的表，并具有以下约束：

    • username 不能为 NULL 且必须是 UNIQUE（唯一的）。
    • email 不能为 NULL。

    让我们分解一下这个命令：

    • CREATE TABLE users：这告诉 SQLite 创建一个名为 users 的新表。
    • id INTEGER PRIMARY KEY：这定义了一个名为 id 的列，其数据类型为 INTEGER（整数）。PRIMARY KEY（主键）约束确保此列中的每个值都是唯一的，并用作每行的主要标识符。
    • username TEXT NOT NULL UNIQUE：这定义了一个名为 username 的列，其数据类型为 TEXT（文本）。NOT NULL 约束确保此列不能为空，UNIQUE 约束确保此列中的所有值都是不同的。
    • email TEXT NOT NULL：这定义了一个名为 email 的列，其数据类型为 TEXT（文本）。NOT NULL 约束确保此列不能为空。
    • age INTEGER：这定义了一个名为 age 的列，其数据类型为 INTEGER（整数）。

  2. 现在，让我们尝试插入一些违反这些约束的数据，看看它们是如何工作的。首先，尝试插入一个没有 username 的用户：

    INSERT INTO users (email, age) VALUES ('test@example.com', 25);

    你将看到一个错误，因为 username 列被定义为 NOT NULL。

    预期输出：

    Error: NOT NULL constraint failed: users.username

    此错误表明 SQLite 强制执行 username 上的 NOT NULL 约束，从而阻止插入。

  3. 接下来，尝试插入一个具有重复 username 的用户。首先，插入一个有效的用户：

    INSERT INTO users (username, email, age) VALUES ('johndoe', 'john.doe@example.com', 30);

    然后，尝试插入另一个具有相同 username 的用户：

    INSERT INTO users (username, email, age) VALUES ('johndoe', 'jane.doe@example.com', 25);

    你将看到一个错误，因为 username 列被定义为 UNIQUE。

    预期输出：

    Error: UNIQUE constraint failed: users.username

    此错误表明 SQLite 强制执行 username 上的 UNIQUE 约束，从而阻止重复的值。

使用 .schema 检查表结构

你可以使用 .schema 命令检查表的结构。此命令显示用于创建表的 SQL 语句，包括列名、数据类型和约束（constraints）。

  1. 检查 users 表的结构。在 sqlite> 提示符下运行此命令：

    .schema users

    预期输出：

    CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);

    输出显示了 users 表的 CREATE TABLE 语句，包括 username 列上的 NOT NULL 和 UNIQUE 约束。

    .schema 命令是一个有用的工具，可以帮助你理解表的结构并验证约束是否已正确定义。

  2. 退出 SQLite shell：

    .quit

    此命令将退出 SQLite shell 并返回到 Linux 终端。

    重要提示：在单击“Continue”按钮之前，请确保执行 .exit。否则，由于 sqlite 历史记录未被记录，因此无法验证该步骤。

删除 contacts 表

你可以使用 DROP TABLE 命令从数据库中删除表。此命令会永久删除该表及其所有数据，因此请谨慎使用。

  1. 连接到 test.db 数据库：

    sqlite3 test.db

  2. 删除 contacts 表。在 sqlite> 提示符下运行此命令：

    DROP TABLE contacts;

    此命令将从数据库中删除 contacts 表及其所有数据。

    警告：此操作不可逆。一旦你删除了一个表，你将无法恢复它或它的数据。

  3. 通过尝试从中选择来验证 contacts 表是否已被删除：

    SELECT * FROM contacts;

    你将看到一个错误，因为 contacts 表不再存在。

  4. 退出 SQLite shell：

    .quit

总结

在这个实验中，你已经学习了在 SQLite 中构建和管理表的基本技能。你探索了 SQLite 数据类型，创建了带有主键的表，应用了诸如 NOT NULL 和 UNIQUE 等约束（constraints），使用 .schema 命令检查了表结构，并安全地删除了表。这些技能对于使用 SQLite 数据库和构建数据驱动的应用程序至关重要。

相关 SQLite 课程
SQLite 初学者教程

SQLite 初学者教程

databasesqlite
SQLite 中级到高级

SQLite 中级到高级

databasesqlite