SQLite 表连接

介绍

在这个实验中，你将学习如何使用表连接技术在 SQLite 中合并来自多个表的数据。我们将涵盖 INNER JOIN（内连接）、LEFT JOIN（左连接）、连接多个表以及过滤连接结果。在本实验结束时，你将能够使用 SQLite 有效地检索和合并数据。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite 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") subgraph Lab Skills sqlite/init_db -.-> lab-552556{{"SQLite 表连接"}} sqlite/make_table -.-> lab-552556{{"SQLite 表连接"}} sqlite/add_rows -.-> lab-552556{{"SQLite 表连接"}} sqlite/get_all -.-> lab-552556{{"SQLite 表连接"}} sqlite/query_where -.-> lab-552556{{"SQLite 表连接"}} end

创建并填充表

在此步骤中，你将创建两个表，employees（员工）和 departments（部门），并使用示例数据填充它们。这些表将在后续步骤中使用，以演示表连接技术。

首先，通过在终端中运行以下命令来打开 SQLite shell：

sqlite3 /home/labex/project/company.db

此命令打开 SQLite shell 并连接到名为 company.db 的数据库文件。如果该文件不存在，SQLite 将创建它。

现在，使用以下 SQL 命令创建 employees 表：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

此命令创建一个名为 employees 的表，其中包含三列：id、name 和 department_id。id 列是主键，将唯一标识每个员工。

接下来，使用以下 SQL 命令创建 departments 表：

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

此命令创建一个名为 departments 的表，其中包含两列：id 和 name。id 列是主键，将唯一标识每个部门。

现在，将一些示例数据插入到 employees 表中：

INSERT INTO employees (name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', NULL);

此命令将五行插入到 employees 表中。每一行代表一个员工及其对应的部门 ID。

最后，将示例数据插入到 departments 表中：

INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Engineering');

此命令将三行插入到 departments 表中。每一行代表一个部门及其名称。

要验证表是否已正确创建和填充，你可以运行以下 SQL 命令：

SELECT * FROM employees;

预期输出：

1|Alice|1
2|Bob|2
3|Charlie|1
4|David|3
5|Eve|

以及：

SELECT * FROM departments;

预期输出：

1|Sales
2|Marketing
3|Engineering

执行 `INNER JOIN`（内连接）查询

在此步骤中，你将学习如何在 SQLite 中使用 INNER JOIN（内连接）子句。当基于指定的条件在两个表中都存在匹配项时，INNER JOIN 将返回行。

INNER JOIN 的基本语法是：

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

在我们的例子中，我们想要检索员工姓名以及他们所在的部门名称。为此，请运行以下 SQL 命令：

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

此命令基于 employees 表中的 department_id 列与 departments 表中的 id 列匹配来连接 employees 和 departments 表。然后，它从 employees 表中选择员工的姓名，并从 departments 表中选择部门的名称。

预期输出：

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

此输出显示了员工的姓名及其对应的部门名称。请注意，Eve 不包含在结果中，因为她的 department_id 为 NULL，并且没有匹配的部门。INNER JOIN 仅返回匹配的行。

使用 `LEFT JOIN`（左连接）处理可选数据

在此步骤中，你将学习如何在 SQLite 中使用 LEFT JOIN（左连接）。LEFT JOIN（或 LEFT OUTER JOIN，左外连接）返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配项，则右表的列将返回 NULL 值。当你想要从一个表中检索所有记录，并在存在的情况下包含来自另一个表的相关数据时，这非常有用。

LEFT JOIN 的基本语法是：

SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

要检索所有员工及其部门名称，即使某个员工没有分配部门，请运行以下 SQL 命令：

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

此命令基于 employees 表中的 department_id 列与 departments 表中的 id 列匹配来连接 employees 和 departments 表。它从 employees 表中选择员工的姓名，并从 departments 表中选择部门的名称。因为它是一个 LEFT JOIN，所以将列出所有员工。

预期输出：

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering
Eve|

请注意，Eve 已被列出，但部门名称为空（NULL），因为她的 department_id 为 NULL，并且没有匹配的部门。这演示了 LEFT JOIN 如何包含左表（employees）中的所有行，即使右表（departments）中没有匹配项。

连接多个表

在此步骤中，你将学习如何在 SQLite 中连接多个表。连接两个以上的表涉及使用多个 JOIN 子句组合来自多个相关表的数据。

首先，让我们添加一个名为 locations 的新表来存储部门位置。运行以下 SQL 命令以创建 locations 表：

CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    department_id INTEGER,
    city TEXT
);

此命令创建一个名为 locations 的表，其中包含三列：id、department_id 和 city。

接下来，将一些示例数据插入到 locations 表中：

INSERT INTO locations (department_id, city) VALUES
(1, 'New York'),
(2, 'Los Angeles'),
(3, 'Chicago');

现在，让我们连接所有三个表以检索员工姓名、部门名称和部门位置。运行以下 SQL 命令：

SELECT employees.name, departments.name, locations.city
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id;

此查询首先基于 employees.department_id = departments.id 连接 employees 和 departments。然后，它基于 departments.id = locations.department_id 将结果与 locations 表连接。这会将员工链接到他们的部门，然后再链接到这些部门的位置。

预期输出：

Alice|Sales|New York
Bob|Marketing|Los Angeles
Charlie|Sales|New York
David|Engineering|Chicago

这显示了每个员工的姓名、他们部门的名称以及他们部门所在的城市。Eve 不包括在内，因为她没有分配部门。

过滤连接结果

在此步骤中，你将学习如何在 SQLite 中使用 WHERE 子句过滤连接表的结果。过滤允许你在连接操作后仅检索满足特定条件的行。

要检索在销售（Sales）部门工作的员工姓名，请运行以下 SQL 命令：

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'Sales';

此查询连接 employees 和 departments 表，然后过滤结果，仅包含部门名称为 'Sales' 的员工。

预期输出：

Alice
Charlie

这表明 Alice 和 Charlie 在销售（Sales）部门工作。

现在，让我们检索在位于纽约（New York）的部门工作的员工姓名。运行以下 SQL 命令：

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id
WHERE locations.city = 'New York';

此查询连接所有三个表，然后过滤结果，仅包含部门位置在纽约（New York）的员工。

预期输出：

Alice
Charlie

同样，Alice 和 Charlie 是位于纽约（New York）的部门中仅有的员工。

总结

在这个实验中，你学习了如何使用表连接技术在 SQLite 中组合来自多个表的数据。你学习了 INNER JOIN，它在两个表中都存在匹配项时返回行，以及 LEFT JOIN，它从左侧表返回所有行，并从右侧表返回匹配的行。你还学习了如何连接多个表，以及如何使用 WHERE 子句过滤连接后的结果。这些技能将使你能够使用 SQLite 有效地检索和组合数据。

介绍

Skills Graph

创建并填充表

执行 INNER JOIN（内连接）查询

使用 LEFT JOIN（左连接）处理可选数据

连接多个表

过滤连接结果

总结

执行 `INNER JOIN`（内连接）查询

使用 `LEFT JOIN`（左连接）处理可选数据