本文将详细介绍 SQLite 中的子查询，并提供一些实际的测试数据和示例。本文将深入探讨子查询的概念、类型、用法，并通过具体的例子来展示其在实际应用中的强大功能。

准备测试数据

首先，让我们创建一些测试表和数据来演示子查询的使用。我们将创建一个简单的员工管理系统，包含员工、部门和项目三个表。

SQL
-- 创建部门表
CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL,
    location TEXT
);

-- 创建员工表
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    dept_id INTEGER,
    salary REAL,
    hire_date TEXT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 创建项目表
CREATE TABLE projects (
    project_id INTEGER PRIMARY KEY,
    project_name TEXT NOT NULL,
    start_date TEXT,
    end_date TEXT,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 插入部门数据
INSERT INTO departments (dept_id, dept_name, location) VALUES
(1, 'IT', 'New York'),
(2, 'HR', 'Los Angeles'),
(3, 'Finance', 'Chicago'),
(4, 'Marketing', 'San Francisco');

-- 插入员工数据
INSERT INTO employees (emp_id, first_name, last_name, dept_id, salary, hire_date) VALUES
(1, 'John', 'Doe', 1, 75000, '2020-01-15'),
(2, 'Jane', 'Smith', 2, 65000, '2019-05-20'),
(3, 'Mike', 'Johnson', 1, 80000, '2018-03-10'),
(4, 'Emily', 'Brown', 3, 72000, '2021-02-01'),
(5, 'David', 'Lee', 4, 68000, '2020-11-15'),
(6, 'Sarah', 'Wilson', 2, 62000, '2019-09-01'),
(7, 'Tom', 'Davis', 1, 78000, '2017-07-22'),
(8, 'Lisa', 'Anderson', 3, 70000, '2022-01-10');

-- 插入项目数据
INSERT INTO projects (project_id, project_name, start_date, end_date, dept_id) VALUES
(1, 'CRM Upgrade', '2022-01-01', '2022-06-30', 1),
(2, 'Employee Training Program', '2022-03-15', '2022-12-31', 2),
(3, 'Financial Reporting System', '2022-02-01', '2022-11-30', 3),
(4, 'Product Launch Campaign', '2022-04-01', '2022-09-30', 4),
(5, 'Network Infrastructure Upgrade', '2022-05-01', '2022-10-31', 1);

这些测试数据为我们提供了一个基础，可以用来演示各种子查询的使用场景。

什么是子查询？

子查询是嵌套在另一个 SQL 查询（外部查询）内部的查询。它可以出现在主查询的 SELECT、FROM、WHERE 或 HAVING 子句中，用于提供更复杂和精确的数据检索逻辑。

子查询的类型

1. 标量子查询：返回单个值。
2. 行子查询：返回单行多列。
3. 列子查询：返回单列多行。
4. 表子查询：返回多行多列。

子查询的用法

1. 在 WHERE 子句中使用子查询

示例：查找工资高于平均工资的员工

SQL
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

2. 在 FROM 子句中使用子查询

示例：计算每个部门的平均工资，并只显示高于公司平均工资的部门

SQL
SELECT dept_name, avg_salary
FROM (
    SELECT d.dept_name, AVG(e.salary) as avg_salary
    FROM departments d
    JOIN employees e ON d.dept_id = e.dept_id
    GROUP BY d.dept_id
) dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employees);

3. 在 SELECT 子句中使用子查询

示例：显示每个员工的工资与其所在部门平均工资的差额

SQL
SELECT 
    e.first_name, 
    e.last_name, 
    e.salary,
    e.salary - (
        SELECT AVG(salary) 
        FROM employees e2 
        WHERE e2.dept_id = e.dept_id
    ) AS salary_diff
FROM employees e;

4. 在 HAVING 子句中使用子查询

示例：找出平均工资高于公司平均工资的部门

SQL
SELECT d.dept_name, AVG(e.salary) as avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING avg_salary > (SELECT AVG(salary) FROM employees);

相关子查询 vs 非相关子查询

非相关子查询

非相关子查询是独立于外部查询的子查询，可以单独执行。

示例：查找工资高于 IT 部门平均工资的员工

SQL
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
    WHERE d.dept_name = 'IT'
);

相关子查询

相关子查询依赖于外部查询，不能单独执行。

示例：找出每个部门工资最高的员工

SQL
SELECT e1.first_name, e1.last_name, e1.salary, d.dept_name
FROM employees e1
JOIN departments d ON e1.dept_id = d.dept_id
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

子查询性能优化

1. 尽可能使用索引。
2. 优先考虑非相关子查询。
3. 对于 IN 子查询，考虑使用 EXISTS。
4. 避免在子查询中使用 * ，只选择需要的列。

子查询 vs JOIN

在某些情况下，子查询可以替换为 JOIN，反之亦然。选择使用哪种方法通常取决于具体的查询需求和性能考虑。

例如，以下两个查询产生相同的结果：

使用子查询：

SQL
SELECT e.first_name, e.last_name
FROM employees e
WHERE e.dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location = 'New York'
);

使用 JOIN：

SQL
SELECT DISTINCT e.first_name, e.last_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = 'New York';

最佳实践

1. 优先考虑可读性和可维护性。
2. 使用有意义的别名。
3. 适当使用注释解释复杂的子查询逻辑。
4. 定期检查查询性能，使用 EXPLAIN QUERY PLAN 分析执行计划。
5. 考虑创建视图来封装复杂的子查询。
6. 在处理大量数据时，考虑使用临时表或索引来优化性能。

结论

SQLite 中的子查询是一个强大的特性，可以大大增强数据库查询的灵活性和功能性。通过本文的示例和解释，我们探讨了子查询的各种类型和用法，以及如何在实际应用中有效地使用它们。

子查询可以帮助我们执行复杂的数据分析和检索任务，但同时也需要谨慎使用，以避免性能问题。通过理解子查询的工作原理，并结合适当的优化技巧，我们可以充分利用这一强大工具，编写出高效、灵活且可维护的 SQLite 查询。

随着对子查询的深入理解和实践，您将能够更好地处理复杂的数据关系，提高查询效率，并在 SQLite 数据库应用开发中游刃有余。记住，选择正确的查询方法（无论是子查询还是 JOIN）取决于具体的使用场景和性能需求。持续学习和实践将帮助您在 SQLite 开发中做出最佳选择。