子查询（Subquery）是在一个 SQL 查询中嵌套另一个查询。子查询可以用来在主查询中使用其他查询的结果，子查询通常用来在 SELECT、INSERT、UPDATE、DELETE 等语句中进行复杂的数据操作。子查询可以出现在 SELECT、FROM、WHERE 等子句中。
基本语法
子查询的基本语法如下：
SELECT column1, column2, ...
FROM table_name
WHERE column_name OPERATOR (SELECT column1, column2, ... FROM table_name WHERE condition);
示例数据库和表结构
假设我们有一个公司数据库，其中有两个表：employees 和 departments。
-- 创建数据库
CREATE DATABASE company;
-- 选择数据库
USE company;
-- 创建表 departments
CREATE TABLE departments (
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL
);
-- 创建表 employees
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
emp_position VARCHAR(100),
emp_salary DECIMAL(10, 2),
hire_date DATE,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- 插入示例数据到 departments 表
INSERT INTO departments (dept_name)
VALUES
('HR'),
('Engineering'),
('Marketing');
-- 插入示例数据到 employees 表
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date, dept_id)
VALUES
('John Doe', 'Manager', 75000.00, '2023-10-01', 1),
('Jane Smith', 'Developer', 60000.00, '2023-09-01', 2),
('Alice Johnson', 'Analyst', 50000.00, '2023-08-01', 1),
('Bob Brown', 'Developer', 70000.00, '2023-07-01', 2),
('Charlie Brown', 'Tester', 55000.00, '2023-11-01', 3),
('Emma White', 'Developer', 60000.00, '2023-08-20', 2);
使用子查询的示例
1. 在 WHERE 子句中使用子查询
获取薪水高于所有工程师的员工名单：
SELECT emp_name, emp_salary
FROM employees
WHERE emp_salary > (SELECT MAX(emp_salary) FROM employees WHERE emp_position = 'Developer');
结果：
emp_name | emp_salary
---------|------------
John Doe | 75000.00
2. 在 SELECT 子句中使用子查询
获取每个员工所在部门的名称：
SELECT emp_name, emp_position,
(SELECT dept_name FROM departments WHERE departments.dept_id = employees.dept_id) AS dept_name
FROM employees;
结果：
emp_name | emp_position | dept_name
--------------|--------------|-----------
John Doe | Manager | HR
Jane Smith | Developer | Engineering
Alice Johnson | Analyst | HR
Bob Brown | Developer | Engineering
Charlie Brown | Tester | Marketing
Emma White | Developer | Engineering
3. 在 FROM 子句中使用子查询
获取每个部门的平均薪水，并列出部门名称和平均薪水：
SELECT dept_name, avg_salary
FROM (
SELECT dept_id, AVG(emp_salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS subquery
JOIN departments ON subquery.dept_id = departments.dept_id;
结果：
dept_name | avg_salary
------------|------------
HR | 62500.00
Engineering | 63333.33
Marketing | 55000.00
4. 相关子查询
获取薪水高于所在部门平均薪水的员工名单：
SELECT emp_name, emp_salary, dept_id
FROM employees e1
WHERE emp_salary > (SELECT AVG(emp_salary)
FROM employees e2
WHERE e1.dept_id = e2.dept_id);
结果：
emp_name | emp_salary | dept_id
---------|------------|--------
John Doe | 75000.00 | 1
Bob Brown| 70000.00 | 2
5. 在 HAVING 子句中使用子查询
获取部门中有员工薪水超过60000的部门名称：
SELECT dept_name
FROM departments
WHERE dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING MAX(emp_salary) > 60000
);
结果：
dept_name
----------
HR
Engineering
子查询与 EXISTS
6. 使用 EXISTS 子查询
获取至少有一个员工的部门名称：
SELECT dept_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.dept_id = d.dept_id
);
结果：
dept_name
----------
HR
Engineering
Marketing
子查询与 IN
7. 使用 IN 子查询
获取在所有部门中薪水最高的员工名单：
SELECT emp_name, emp_salary
FROM employees
WHERE emp_salary IN (
SELECT MAX(emp_salary)
FROM employees
GROUP BY dept_id
);
结果：
emp_name | emp_salary
---------|------------
John Doe | 75000.00
Bob Brown| 70000.00
Charlie Brown | 55000.00
小结
子查询是构建复杂 SQL 查询的重要技术。通过在主查询中嵌套其他查询，您可以实现多种数据操作，如过滤、聚合、排序等。子查询可以出现在 SELECT、FROM、WHERE、HAVING 等子句中，并且可以与 EXISTS 和 IN 等关键字结合使用，以实现更复杂的查询逻辑。上述示例展示了如何在不同情况下使用子查询，这对于数据分析和操作非常有用。
