MySQL 复合查询全解析：从基础到多表关联与高级技巧
在 MySQL 中，复合查询是指对数据库中的数据进行多个操作或者对多个表进行联合查询，以获取更复杂、更丰富的查询结果。复合查询常见的操作包括 分组查询、联合查询、聚合查询、子查询 和 连接查询。本文将对这些内容进行详细解析，帮助你从基础到高级掌握 MySQL 中的复合查询技巧。
1. 复合查询基础
复合查询通常涉及多个表的数据处理、复杂的筛选条件、数据的分组、排序等操作。常用的 SQL 语句有：
- SELECT：用于从数据库中查询数据。
- JOIN：用于连接多个表的数据。
- WHERE：用于过滤查询结果。
- GROUP BY：用于按某些列对数据进行分组。
- HAVING：用于在分组后对数据进行过滤。
- ORDER BY：用于对结果进行排序。
1.1 SELECT 查询
SELECT 是 MySQL 查询的基本语法，能够选择性地查询数据库中的某些字段或者满足特定条件的数据。
SELECT column1, column2
FROM table_name
WHERE condition;
例如，查询一个 employees 表中所有职位为 “developer” 的员工姓名和薪资：
SELECT name, salary
FROM employees
WHERE position = 'developer';
2. 聚合查询与分组查询
聚合查询主要用于计算某些数据的总和、平均值、最大值、最小值等统计信息。常用的聚合函数包括：
- COUNT()：计算行数。
- SUM()：计算列的总和。
- AVG()：计算列的平均值。
- MAX()：返回列的最大值。
- MIN()：返回列的最小值。
2.1 GROUP BY：分组查询
GROUP BY 用于将查询结果按照某些列进行分组，并对每组数据执行聚合操作。
例如，查询每个部门的员工数量：
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
2.2 HAVING：过滤分组后的数据
HAVING 子句用于对分组后的数据进行过滤。它与 WHERE 子句类似，但是 WHERE 用于过滤原始数据，而 HAVING用于过滤分组后的数据。
例如，查询每个部门中员工数超过 10 的部门：
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
3. 多表连接查询
多表查询常用于获取来自不同表的相关数据，通常使用 JOIN 来进行连接。根据连接的方式不同，JOIN 可以分为几种类型：
- INNER JOIN（内连接）：返回两个表中匹配的行。
- LEFT JOIN（左连接）：返回左表中的所有行以及右表中匹配的行。
- RIGHT JOIN（右连接）：返回右表中的所有行以及左表中匹配的行。
- FULL JOIN（全连接）：返回左表和右表中所有的行，无论是否有匹配。
3.1 INNER JOIN（内连接）
INNER JOIN 返回两个表中匹配的行。通常用于从多个表中提取相关的数据。
例如，查询所有员工及其对应的部门名称：
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
3.2 LEFT JOIN（左连接）
LEFT JOIN 会返回左表中的所有行，以及右表中匹配的行。如果右表没有匹配的行，返回的结果中右表的字段将会是 NULL。
例如，查询所有员工及其对应的部门名称（包括那些没有部门的员工）：
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
3.3 RIGHT JOIN（右连接）
RIGHT JOIN 与 LEFT JOIN 相反，返回右表中的所有行，以及左表中匹配的行。如果左表没有匹配的行，返回的结果中左表的字段将会是 NULL。
例如，查询所有部门及其对应的员工名称（包括那些没有员工的部门）：
SELECT departments.name, employees.name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id;
3.4 FULL JOIN（全连接）
FULL JOIN 会返回两个表中的所有行，无论是否匹配。MySQL 不支持 FULL JOIN，但是可以通过 UNION 来实现。
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
4. 联合查询（UNION）
UNION 用于将两个或多个 SELECT 查询的结果组合到一起。UNION 会去除重复的行，而 UNION ALL 则不去除重复行。
4.1 使用 UNION 合并查询结果
例如，查询所有来自销售部门和技术部门的员工姓名：
SELECT name
FROM employees
WHERE department = 'Sales'
UNION
SELECT name
FROM employees
WHERE department = 'Tech';
4.2 使用 UNION ALL 合并查询结果
如果你希望保留重复的记录，可以使用 UNION ALL：
SELECT name
FROM employees
WHERE department = 'Sales'
UNION ALL
SELECT name
FROM employees
WHERE department = 'Tech';
5. 子查询
子查询是在一个查询中嵌套另一个查询。子查询可以出现在 SELECT、FROM、WHERE 或 HAVING 中。
5.1 WHERE 子查询
在 WHERE 子句中使用子查询来返回满足某些条件的数据。
例如，查询所有薪资大于部门平均薪资的员工：
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = 'Sales');
5.2 SELECT 子查询
在 SELECT 子句中使用子查询来返回动态计算的列。
例如，查询每个员工及其部门的平均薪资：
SELECT name, (SELECT AVG(salary) FROM employees WHERE department = employees.department) AS avg_salary
FROM employees;
5.3 IN 子查询
使用 IN 子查询来查询符合条件的数据。
例如，查询所有在 Sales 和 Tech 部门的员工：
SELECT name
FROM employees
WHERE department IN (SELECT name FROM departments WHERE location = 'New York');
6. 高级技巧
6.1 结合索引优化查询
使用索引可以显著提高查询效率，尤其是对于复杂的复合查询。通过在常用的查询条件字段上创建索引，可以加速查询的执行。
CREATE INDEX idx_department ON employees(department);
6.2 使用窗口函数（Window Functions）
MySQL 8.0 及以上版本支持窗口函数，可以在查询中进行更复杂的分析操作，如计算累积和、排名等。
例如，查询员工的薪资排名：
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
6.3 使用视图（View）
视图是一个虚拟表，可以封装复杂的查询逻辑，从而简化后续的查询。视图常用于复杂的多表查询。
CREATE VIEW employee_view AS
SELECT name, department, salary
FROM employees
WHERE salary > 50000;
之后你可以像查询普通表一样查询视图：
SELECT * FROM employee_view;
7. 小结
MySQL 中的复合查询是处理复杂数据查询的强大工具，包括分组查询、联合查询、连接查询、子查询等。通过掌握这些查询技巧，你能够高效地处理多表之间的关联、复杂的数据分析和报表生成等任务。
- 基本查询：使用
SELECT和WHERE。 - 聚合查询：使用
GROUP BY和聚合函数。 - 连接查询：使用
JOIN实现多表数据关联。 - 联合查询：使用
UNION合并多个查询结果。 - 子查询：在
WHERE、SELECT等子句中使用子查询。 - 高级技巧：如窗口函数、视图等可以进一步简化复杂查询。
通过不断实践和优化，能够充分发挥 MySQL 的查询能力，提升数据处理效率。
发表回复