SQLite 子查询技术 - 提升 SQL 数据检索能力

介绍

在这个实验中，你将探索 SQLite 子查询技术，以增强你的数据检索和过滤能力。你将学习如何在 WHERE 子句中使用子查询，将它们嵌入到 SELECT 语句中，并构建相关子查询（correlated subqueries）。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/build_index("Create Single Index") subgraph Lab Skills sqlite/make_table -.-> lab-552555{{"SQLite 子查询技术"}} sqlite/get_all -.-> lab-552555{{"SQLite 子查询技术"}} sqlite/query_where -.-> lab-552555{{"SQLite 子查询技术"}} sqlite/build_index -.-> lab-552555{{"SQLite 子查询技术"}} end

创建表并插入数据

在第一步中，你将创建两个表，departments（部门表）和 employees（员工表），并将一些示例数据插入到其中。这将提供你在后续步骤中练习使用子查询所需的数据。

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

首先，连接到名为 company.db 的 SQLite 数据库。如果数据库不存在，SQLite 将为你创建它。运行以下命令：

sqlite3 company.db

此命令打开 SQLite 命令行工具并连接到 company.db 数据库。你将看到 sqlite> 提示符。

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

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT,
    location TEXT
);

此命令创建一个名为 departments 的表，其中包含三列：department_id（部门 ID）、department_name（部门名称）和 location（地点）。department_id 列是此表的主键（primary key）。

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

INSERT INTO departments (department_name, location) VALUES
('Sales', 'New York'),
('Marketing', 'Los Angeles'),
('Engineering', 'San Francisco');

此命令将三行数据插入到 departments 表中，表示三个不同的部门及其地点。

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

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

此命令创建一个名为 employees 的表，其中包含三列：employee_id（员工 ID）、employee_name（员工姓名）和 department_id（部门 ID）。department_id 列是一个外键（foreign key），它引用 departments 表中的 department_id 列。

最后，将一些示例数据插入到 employees 表中：

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

此命令将四行数据插入到 employees 表中，表示四个不同的员工及其部门 ID。

在 WHERE 子句中使用子查询

在这一步中，你将学习如何在 WHERE 子句中使用子查询，以根据另一个查询的输出来过滤结果。

子查询是嵌套在另一个 SQL 语句中的 SELECT 语句。在本例中，你将使用子查询从 departments 表中选择 department_id（部门 ID）值，然后使用这些值来过滤对 employees 表的查询结果。

让我们找到所有在位于‘New York’（纽约）的部门工作的员工。为此，你首先需要找到‘New York’部门的 department_id，然后找到所有具有该 department_id 的员工。

在 sqlite> 提示符中输入以下 SQL 命令：

SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

此命令从 employees 表中选择 employee_name（员工姓名），其中 department_id 在子查询返回的 department_id 值列表中。子查询从 departments 表中选择 department_id，其中 location 是‘New York’。

执行命令后，你应该看到以下输出：

Alice
Charlie

此输出显示了在 Sales（销售）部门工作的员工姓名，该部门位于 New York。

在 SELECT 子句中嵌入子查询

在这一步中，你将学习如何在 SQL 语句的 SELECT 子句中嵌入子查询，以检索相关数据。

在 SELECT 子句中嵌入子查询允许你为外部查询中的每一行检索单个值。此值通常是计算值或来自另一个表的相关值。

让我们检索每个员工的姓名以及他们所在部门的名称。在 sqlite> 提示符中输入以下 SQL 命令：

SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name FROM employees;

此命令从 employees 表中选择 employee_name（员工姓名），并且还包括一个子查询，该子查询从 departments 表中检索 department_name（部门名称）。子查询使用 employees 表中的 department_id（部门 ID）来匹配正确的部门。子查询的结果别名为 department_name。

执行命令后，你应该看到以下输出：

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

此输出显示了每个员工的姓名以及他们对应部门的名称。

构建相关子查询

在这一步中，你将学习如何构建相关子查询。相关子查询是指引用外部查询中的列的子查询。这意味着子查询对于外部查询的每一行执行一次。

与简单子查询（执行一次，其结果被外部查询使用）不同，相关子查询依赖于外部查询的值。当你需要将子查询中的值与外部查询当前行中的值进行比较时，可以使用它们。

让我们找到所有在与员工姓名位于同一城市的部门工作的员工。为了实现这一点，我们首先将员工姓名更新为城市名称。

UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Alice';
UPDATE employees SET employee_name = 'Los Angeles' WHERE employee_name = 'Bob';
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Charlie';
UPDATE employees SET employee_name = 'San Francisco' WHERE employee_name = 'David';

这些命令将 employees 表中的 employee_name（员工姓名）列更新为城市名称。

现在，让我们编写相关子查询：

SELECT e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location = e.employee_name);

此命令从 employees 表（别名为 e）中选择 employee_name，其中 department_id 在子查询返回的 department_id 值列表中。子查询从 departments 表（别名为 d）中选择 department_id，其中 location 与外部查询中的 employee_name 匹配。

执行命令后，你应该看到以下输出：

New York
Los Angeles
San Francisco

此输出显示了在位于同一城市的部门工作的员工姓名（现在是城市名称）。

使用 JOIN 评估子查询效率

在这一步中，你将学习如何评估子查询的效率，并探索使用 JOIN 操作进行优化的替代方法。

虽然子查询功能强大，但有时会导致性能瓶颈，尤其是在处理大型数据集时。在许多情况下，你可以使用 JOIN 操作重写子查询，这可能更有效。

让我们使用 JOIN 重写上一步中的相关子查询：

SELECT e.employee_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location = e.employee_name;

此命令从 employees 表（别名为 e）中选择 employee_name，并使用 department_id 列将其与 departments 表（别名为 d）连接。然后，WHERE 子句过滤结果，仅包括 departments 表中的 location 与 employees 表中的 employee_name 匹配的行。

要验证结果，请执行该命令。你应该看到与上一步相同的输出：

New York
Los Angeles
San Francisco

要评估效率，你通常会在更改前后使用 EXPLAIN QUERY PLAN。但是，由于 LabEx 环境的限制，我们无法完全演示 EXPLAIN QUERY PLAN 命令。关键在于，JOIN 操作通常比相关子查询更有效，尤其是在处理较大的数据集时。

最后，退出 sqlite3 shell：

.exit

这将使你返回到 bash 提示符。

总结

在这个实验中，你学习了如何使用 SQLite 子查询来增强你的数据检索和过滤能力。你练习了在 WHERE 子句中使用子查询，将它们嵌入到 SELECT 语句中，以及构建相关子查询。你还学习了如何使用 JOIN 操作重写子查询以获得更高的效率。这些技术为你提供了在 SQLite 中处理数据的强大工具。