【MySQL基础】MySQL复合查询全面解析：从基础到高级应用

-- 部门表 CREATE TABLE departments ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL, location VARCHAR(50) NOT NULL, established_date DATE, budget DECIMAL(12,2) ); -- 员工表 CREATE TABLE employees ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(50) NOT NULL, dept_id INT, salary DECIMAL(10,2) NOT NULL, hire_date DATE NOT NULL, manager_id INT, email VARCHAR(100), CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id), CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id) ); -- 项目表 CREATE TABLE projects ( project_id INT PRIMARY KEY AUTO_INCREMENT, project_name VARCHAR(100) NOT NULL, budget DECIMAL(12,2), start_date DATE, end_date DATE, dept_id INT, status ENUM('Planning', 'In Progress', 'Completed', 'On Hold') DEFAULT 'Planning', CONSTRAINT fk_project_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ); -- 员工项目关联表 CREATE TABLE emp_projects ( emp_id INT, project_id INT, role VARCHAR(50), join_date DATE, hours_allocated INT, PRIMARY KEY (emp_id, project_id), CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id), CONSTRAINT fk_project FOREIGN KEY (project_id) REFERENCES projects(project_id) );

2.2 示例数据填充

-- 部门数据 INSERT INTO departments VALUES (1, '技术研发部', '北京总部', '2015-06-01', 2000000.00), (2, '市场营销部', '上海分公司', '2016-03-15', 1500000.00), (3, '人力资源部', '广州办事处', '2017-01-10', 800000.00), (4, '财务部', '北京总部', '2015-06-01', 1200000.00); -- 员工数据 INSERT INTO employees VALUES (1, '张伟', 1, 25000.00, '2016-03-10', NULL, '[email protected]'), (2, '李娜', 1, 18000.00, '2017-05-15', 1, '[email protected]'), (3, '王芳', 2, 22000.00, '2016-11-20', NULL, '[email protected]'), (4, '赵刚', 2, 16000.00, '2018-02-28', 3, '[email protected]'), (5, '钱强', 3, 19000.00, '2017-08-05', NULL, '[email protected]'), (6, '孙丽', 3, 14000.00, '2019-06-15', 5, '[email protected]'), (7, '周明', 4, 21000.00, '2016-07-22', NULL, '[email protected]'); -- 项目数据 INSERT INTO projects VALUES (1, '新一代电商平台开发', 800000.00, '2023-01-10', '2023-09-30', 1, 'In Progress'), (2, '全球市场推广计划', 500000.00, '2023-02-15', '2023-08-15', 2, 'In Progress'), (3, '员工技能提升计划', 200000.00, '2023-03-01', '2023-12-31', 3, 'Planning'), (4, '财务系统云迁移', 350000.00, '2023-04-01', NULL, 4, 'In Progress'), (5, '移动端应用优化', 300000.00, '2023-05-15', '2023-11-30', 1, 'Planning'); -- 员工项目关联 INSERT INTO emp_projects VALUES (1, 1, '技术负责人', '2023-01-05', 30), (2, 1, '开发工程师', '2023-01-10', 40), (1, 5, '架构师', '2023-05-10', 20), (3, 2, '市场总监', '2023-02-10', 25), (4, 2, '市场专员', '2023-02-15', 35), (5, 3, '培训经理', '2023-03-01', 30), (6, 3, '培训助理', '2023-03-05', 20), (7, 4, '项目经理', '2023-04-01', 40);

三、子查询深度解析

3.1 子查询分类与语法

3.1.1 按子查询位置分类

HAVING子句子查询

SELECT dept_id, AVG(salary) as avg_salary FROM employees GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

SELECT子句子查询

SELECT emp_name, salary, (SELECT AVG(salary) FROM employees) as company_avg FROM employees;

FROM子句子查询（派生表）

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

WHERE子句子查询

SELECT emp_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

3.1.2 按子查询相关性分类

相关子查询

SELECT e1.emp_name, e1.salary FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id);

非相关子查询

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '北京总部');

3.2 子查询操作符详解

比较运算符子查询

SELECT emp_name, salary FROM employees WHERE salary >= (SELECT MAX(salary) * 0.8 FROM employees);

EXISTS操作符

SELECT d.dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM projects p WHERE p.dept_id = d.dept_id AND p.status = 'In Progress');

NOT IN操作符

SELECT emp_name FROM employees WHERE emp_id NOT IN (SELECT DISTINCT emp_id FROM emp_projects);

IN操作符

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 1000000);

3.3 子查询性能优化

限制子查询返回的列数

-- 只选择必要的列 SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments); -- 而不是 SELECT *

使用EXISTS替代IN

-- 当子查询结果集大时更高效 SELECT d.dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM projects p WHERE p.dept_id = d.dept_id);

使用JOIN替代子查询

-- 不推荐 SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '北京总部'); -- 推荐 SELECT e.emp_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE d.location = '北京总部';

四、连接查询全面讲解

4.1 连接类型详解

4.1.1 内连接(INNER JOIN)

-- 基本内连接 SELECT e.emp_name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id; -- 带条件的内连接 SELECT e.emp_name, p.project_name, ep.role FROM employees e INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id INNER JOIN projects p ON ep.project_id = p.project_id WHERE p.status = 'In Progress';

4.1.2 外连接(OUTER JOIN)

全外连接(FULL OUTER JOIN) - MySQL通过UNION实现

-- 查询所有员工和所有部门的组合 SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id UNION SELECT e.emp_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id WHERE e.emp_id IS NULL;

右外连接(RIGHT JOIN)

-- 查询所有员工及其部门(包括没有部门的员工) SELECT e.emp_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;

左外连接(LEFT JOIN)

-- 查询所有部门及其员工(包括没有员工的部门) SELECT d.dept_name, e.emp_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id;

4.1.3 交叉连接(CROSS JOIN)

-- 生成员工和项目的所有可能组合 SELECT e.emp_name, p.project_name FROM employees e CROSS JOIN projects p;

4.1.4 自连接(SELF JOIN)

-- 查询员工及其经理信息 SELECT e.emp_name AS employee, m.emp_name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id;

4.2 连接查询优化策略

下面关于索引和视图的知识后面还会详细讲解

使用STRAIGHT_JOIN强制连接顺序

SELECT STRAIGHT_JOIN d.dept_name, COUNT(e.emp_id) as emp_count FROM departments d JOIN employees e ON d.dept_id = e.dept_id GROUP BY d.dept_id;

选择适当的连接顺序

-- 小表驱动大表原则 SELECT /*+ JOIN_ORDER(d, e) */ d.dept_name, e.emp_name FROM departments d -- 假设部门表比员工表小 JOIN employees e ON d.dept_id = e.dept_id;

确保连接条件有索引

ALTER TABLE employees ADD INDEX idx_dept_id (dept_id); ALTER TABLE emp_projects ADD INDEX idx_emp_id (emp_id); ALTER TABLE emp_projects ADD INDEX idx_project_id (project_id);

五、UNION查询高级应用

5.1 UNION基础用法

-- 合并员工和部门名称 SELECT emp_name AS name, 'Employee' AS type FROM employees UNION SELECT dept_name, 'Department' FROM departments ORDER BY type, name;

5.2 UNION ALL与UNION的区别

-- UNION会去重，UNION ALL不会 SELECT dept_id FROM employees WHERE salary > 20000 UNION SELECT dept_id FROM departments WHERE budget > 1500000; -- 使用UNION ALL提高性能(当确定不需要去重时) SELECT emp_name FROM employees WHERE dept_id = 1 UNION ALL SELECT emp_name FROM employees WHERE salary > 18000;

5.3 复杂UNION查询示例

-- 按类型统计人数和预算 SELECT 'Department' AS category, COUNT(*) AS count, SUM(budget) AS total_budget FROM departments UNION SELECT 'Employee' AS category, COUNT(*) AS count, SUM(salary) AS total_salary FROM employees UNION SELECT 'Project' AS category, COUNT(*) AS count, SUM(budget) AS total_budget FROM projects;

六、派生表与CTE高级用法

6.1 派生表(MySQL 5.7+)

-- 计算各部门薪资统计信息 SELECT d.dept_name, stats.emp_count, stats.avg_salary, stats.max_salary FROM departments d JOIN ( SELECT dept_id, COUNT(*) as emp_count, AVG(salary) as avg_salary, MAX(salary) as max_salary FROM employees GROUP BY dept_id ) stats ON d.dept_id = stats.dept_id;

6.2 公用表表达式(CTE, MySQL 8.0+)

6.2.1 基本CTE

-- 查询参与项目的员工信息 WITH project_emps AS ( SELECT DISTINCT emp_id FROM emp_projects ) SELECT e.emp_name, e.salary FROM employees e JOIN project_emps pe ON e.emp_id = pe.emp_id;

6.2.2 递归CTE

-- 组织结构层级查询 WITH RECURSIVE org_hierarchy AS ( -- 基础查询：找出所有没有经理的员工(顶层管理者) SELECT emp_id, emp_name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- 递归查询：找出每个员工的下属 SELECT e.emp_id, e.emp_name, e.manager_id, oh.level + 1 FROM employees e JOIN org_hierarchy oh ON e.manager_id = oh.emp_id ) SELECT emp_id, emp_name, level FROM org_hierarchy ORDER BY level, emp_name;

七、复合查询实战案例

7.1 多层级数据分析

-- 分析各部门项目参与情况 WITH dept_stats AS ( SELECT d.dept_id, d.dept_name, COUNT(DISTINCT e.emp_id) as total_emps, COUNT(DISTINCT ep.emp_id) as project_emps, COUNT(DISTINCT p.project_id) as project_count FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id LEFT JOIN projects p ON d.dept_id = p.dept_id GROUP BY d.dept_id, d.dept_name ) SELECT dept_name, total_emps, project_emps, project_count, CONCAT(ROUND(project_emps/total_emps*100, 2), '%') AS participation_rate FROM dept_stats ORDER BY participation_rate DESC;

7.2 复杂业务逻辑实现

-- 找出每个部门薪资高于部门平均且参与项目的员工 WITH dept_avg_salary AS ( SELECT dept_id, AVG(salary) as avg_salary FROM employees GROUP BY dept_id ), project_employees AS ( SELECT DISTINCT emp_id FROM emp_projects ) SELECT e.emp_name, e.salary, d.dept_name, das.avg_salary FROM employees e JOIN departments d ON e.dept_id = d.dept_id JOIN dept_avg_salary das ON e.dept_id = das.dept_id JOIN project_employees pe ON e.emp_id = pe.emp_id WHERE e.salary > das.avg_salary ORDER BY e.dept_id, e.salary DESC;

八、性能优化与最佳实践

8.1 复合查询性能优化

索引优化建议
- 为所有连接条件创建索引
- 为WHERE子句中的条件列创建索引
- 考虑复合索引的顺序

查询重写技巧

-- 不推荐：使用HAVING过滤分组前数据 SELECT dept_id, AVG(salary) as avg_salary FROM employees GROUP BY dept_id HAVING dept_id IN (1, 2, 3); -- 推荐：在WHERE子句中提前过滤 SELECT dept_id, AVG(salary) as avg_salary FROM employees WHERE dept_id IN (1, 2, 3) GROUP BY dept_id;

EXPLAIN分析工具

EXPLAIN SELECT e.emp_name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE e.salary > 15000;

8.2 复合查询最佳实践

保持查询简洁：避免过度复杂的嵌套
合理使用注释：解释复杂查询的逻辑
分步构建查询：先测试子查询再组合

考虑使用视图：对常用复杂查询创建视图

CREATE VIEW dept_project_stats AS SELECT d.dept_id, d.dept_name, COUNT(DISTINCT e.emp_id) as emp_count, COUNT(DISTINCT p.project_id) as project_count FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id LEFT JOIN projects p ON d.dept_id = p.dept_id GROUP BY d.dept_id, d.dept_name;

九、常见问题与解决方案

9.1 性能问题排查

问题：复合查询执行缓慢

解决方案：

使用EXPLAIN分析执行计划
检查是否使用了适当的索引
考虑将复杂查询拆分为多个简单查询
评估是否可以使用临时表存储中间结果

9.2 结果不符合预期

问题：查询返回的行数多于或少于预期

解决方案：

检查连接条件是否正确
确认使用正确的JOIN类型(INNER/LEFT/RIGHT)
验证WHERE条件逻辑
检查NULL值的处理方式

9.3 语法错误处理

常见错误：

子查询返回多行但使用了比较运算符
在GROUP BY或HAVING中引用了不存在的列
UNION查询的列数或类型不匹配

解决方案：

-- 错误示例：子查询返回多行 SELECT emp_name FROM employees WHERE salary = (SELECT salary FROM employees WHERE dept_id = 1); -- 正确修改： SELECT emp_name FROM employees WHERE salary IN (SELECT salary FROM employees WHERE dept_id = 1);

十、总结与进阶学习建议

10.1 复合查询核心要点总结

子查询适合解决分步查询问题，但要注意性能
连接查询是处理表关系的强大工具
UNION提供了垂直合并结果集的能力
CTE提高了复杂查询的可读性和可维护性

10.2 进阶学习建议

深入学习执行计划：掌握EXPLAIN输出解读
了解查询优化器原理：学习MySQL如何优化查询
研究分区表查询：大数据量下的查询优化
学习窗口函数：MySQL 8.0+的高级分析功能

以上就是关于MySQL查询中的所有相关知识点，除了前面常用的外，后面的有些时候并不一定能用到，但都是有必要掌握的，由于篇幅原因，有些问题并不能全面刨析到，建议大家看到不理解的地方可以再去找一些教学视频看一下

感谢各位大佬观看，创作不易，还望各位大佬点赞支持！！！

一、复合查询基础概念

1.1 什么是复合查询

1.2 复合查询的主要类型

二、示例数据库结构详解

2.1 完整的表结构设计

2.2 示例数据填充

三、子查询深度解析

3.1 子查询分类与语法

3.1.1 按子查询位置分类

3.1.2 按子查询相关性分类

3.2 子查询操作符详解

3.3 子查询性能优化

四、连接查询全面讲解

4.1 连接类型详解

4.1.1 内连接(INNER JOIN)

4.1.2 外连接(OUTER JOIN)

4.1.3 交叉连接(CROSS JOIN)

4.1.4 自连接(SELF JOIN)

4.2 连接查询优化策略

五、UNION查询高级应用

5.1 UNION基础用法

5.2 UNION ALL与UNION的区别

5.3 复杂UNION查询示例

六、派生表与CTE高级用法

6.1 派生表(MySQL 5.7+)

6.2 公用表表达式(CTE, MySQL 8.0+)

6.2.1 基本CTE

6.2.2 递归CTE

七、复合查询实战案例

7.1 多层级数据分析

7.2 复杂业务逻辑实现

八、性能优化与最佳实践

8.1 复合查询性能优化

8.2 复合查询最佳实践

九、常见问题与解决方案

9.1 性能问题排查

9.2 结果不符合预期

9.3 语法错误处理

十、总结与进阶学习建议

10.1 复合查询核心要点总结

10.2 进阶学习建议

Read more

别再手动调优了！KingbaseES连接条件下推自动拯救慢 SQL

LangChain: 从 LLM 调用到 Agent 架构

Flutter 组件 short_uuids 适配鸿蒙 HarmonyOS 实战：唯一标识微缩技术，构建高性能短 ID 生成与分布式索引架构

【MySQL数据库基础】（四）MySQL 表的操作通关指南：创建 / 修改 / 删除一网打尽