网站导航
> 技术文档 > 【MySQL基础】MySQL复合查询全面解析：从基础到高级应用

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

网友: 技术文档 2025-07-30 11:52:15

MySQL学习：

https://blog.csdn.net/2301_80220607/category_12971838.html?spm=1001.2014.3001.5482

前言：

前面学习了表的增删查改之后，今天我们重点来讲解一下有关查询的复杂问题——复合查询

目录

一、复合查询基础概念

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 进阶学习建议

一、复合查询基础概念

1.1 什么是复合查询

复合查询是指将多个简单查询通过特定的SQL语法组合起来，形成一个功能更加强大的查询语句。与简单查询相比，复合查询能够：

  • 处理更复杂的数据关系

  • 减少应用程序中的数据处理逻辑

  • 提高数据检索效率（当正确使用时）

  • 实现跨表的数据关联和分析

1.2 复合查询的主要类型

MySQL中常见的复合查询包括：

  1. 子查询（Subqueries）

  2. 连接查询（JOIN Operations）

  3. 联合查询（UNION Queries）

  4. 派生表（Derived Tables）

  5. 公用表表达式（Common Table Expressions，CTE）

二、示例数据库结构详解

在进行讲解我们的查询之前，我们先看一下名为需要用到的表，以及往表里添加几组示例数据，以方便我们查询后看到查询的效果

2.1 完整的表结构设计

-- 部门表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, \'zhangwei@company.com\'),(2, \'李娜\', 1, 18000.00, \'2017-05-15\', 1, \'lina@company.com\'),(3, \'王芳\', 2, 22000.00, \'2016-11-20\', NULL, \'wangfang@company.com\'),(4, \'赵刚\', 2, 16000.00, \'2018-02-28\', 3, \'zhaogang@company.com\'),(5, \'钱强\', 3, 19000.00, \'2017-08-05\', NULL, \'qianqiang@company.com\'),(6, \'孙丽\', 3, 14000.00, \'2019-06-15\', 5, \'sunli@company.com\'),(7, \'周明\', 4, 21000.00, \'2016-07-22\', NULL, \'zhouming@company.com\');-- 项目数据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 按子查询位置分类

  1. WHERE子句子查询

    SELECT emp_name, salaryFROM employeesWHERE salary > (SELECT AVG(salary) FROM employees);

  2. FROM子句子查询（派生表）

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

  3. SELECT子句子查询

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

  4. HAVING子句子查询

    SELECT dept_id, AVG(salary) as avg_salaryFROM employeesGROUP BY dept_idHAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

3.1.2 按子查询相关性分类

  1. 非相关子查询

    SELECT emp_nameFROM employeesWHERE dept_id IN (SELECT dept_id FROM departments WHERE location = \'北京总部\');

  2. 相关子查询

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

3.2 子查询操作符详解

  1. IN操作符

    SELECT emp_nameFROM employeesWHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 1000000);

  2. NOT IN操作符

    SELECT emp_nameFROM employeesWHERE emp_id NOT IN (SELECT DISTINCT emp_id FROM emp_projects);

  3. EXISTS操作符

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

  4. 比较运算符子查询

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

3.3 子查询性能优化

  1. 使用JOIN替代子查询

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

  2. 使用EXISTS替代IN

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

  3. 限制子查询返回的列数

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

四、连接查询全面讲解

4.1 连接类型详解

4.1.1 内连接(INNER JOIN)
-- 基本内连接SELECT e.emp_name, d.dept_nameFROM employees eINNER JOIN departments d ON e.dept_id = d.dept_id;-- 带条件的内连接SELECT e.emp_name, p.project_name, ep.roleFROM employees eINNER JOIN emp_projects ep ON e.emp_id = ep.emp_idINNER JOIN projects p ON ep.project_id = p.project_idWHERE p.status = \'In Progress\';
4.1.2 外连接(OUTER JOIN)

  1. 左外连接(LEFT JOIN)

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

  2. 右外连接(RIGHT JOIN)

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

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

    -- 查询所有员工和所有部门的组合SELECT e.emp_name, d.dept_nameFROM employees eLEFT JOIN departments d ON e.dept_id = d.dept_idUNIONSELECT e.emp_name, d.dept_nameFROM employees eRIGHT JOIN departments d ON e.dept_id = d.dept_idWHERE e.emp_id IS NULL;

4.1.3 交叉连接(CROSS JOIN)
-- 生成员工和项目的所有可能组合SELECT e.emp_name, p.project_nameFROM employees eCROSS JOIN projects p;

4.1.4 自连接(SELF JOIN)
-- 查询员工及其经理信息SELECT e.emp_name AS employee, m.emp_name AS managerFROM employees eLEFT JOIN employees m ON e.manager_id = m.emp_id;

4.2 连接查询优化策略

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

  1. 确保连接条件有索引

    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);

  2. 选择适当的连接顺序

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

  3. 使用STRAIGHT_JOIN强制连接顺序

    SELECT STRAIGHT_JOIN d.dept_name, COUNT(e.emp_id) as emp_countFROM departments dJOIN employees e ON d.dept_id = e.dept_idGROUP BY d.dept_id;

五、UNION查询高级应用

5.1 UNION基础用法

-- 合并员工和部门名称SELECT emp_name AS name, \'Employee\' AS type FROM employeesUNIONSELECT dept_name, \'Department\' FROM departmentsORDER BY type, name;

5.2 UNION ALL与UNION的区别

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

5.3 复杂UNION查询示例

-- 按类型统计人数和预算SELECT \'Department\' AS category, COUNT(*) AS count, SUM(budget) AS total_budgetFROM departmentsUNIONSELECT \'Employee\' AS category, COUNT(*) AS count, SUM(salary) AS total_salaryFROM employeesUNIONSELECT \'Project\' AS category, COUNT(*) AS count, SUM(budget) AS total_budgetFROM projects;

六、派生表与CTE高级用法

6.1 派生表(MySQL 5.7+)

-- 计算各部门薪资统计信息SELECT d.dept_name, stats.emp_count, stats.avg_salary, stats.max_salaryFROM departments dJOIN ( 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.salaryFROM employees eJOIN 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, levelFROM org_hierarchyORDER 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_rateFROM dept_statsORDER 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_salaryFROM employees eJOIN departments d ON e.dept_id = d.dept_idJOIN dept_avg_salary das ON e.dept_id = das.dept_idJOIN project_employees pe ON e.emp_id = pe.emp_idWHERE e.salary > das.avg_salaryORDER BY e.dept_id, e.salary DESC;

八、性能优化与最佳实践

8.1 复合查询性能优化

  1. EXPLAIN分析工具

    EXPLAIN SELECT e.emp_name, d.dept_nameFROM employees eJOIN departments d ON e.dept_id = d.dept_idWHERE e.salary > 15000;

  2. 索引优化建议

    • 为所有连接条件创建索引

    • 为WHERE子句中的条件列创建索引

    • 考虑复合索引的顺序

  3. 查询重写技巧

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

8.2 复合查询最佳实践

  1. 保持查询简洁：避免过度复杂的嵌套

  2. 合理使用注释：解释复杂查询的逻辑

  3. 分步构建查询：先测试子查询再组合

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

    CREATE VIEW dept_project_stats ASSELECT d.dept_id, d.dept_name, COUNT(DISTINCT e.emp_id) as emp_count, COUNT(DISTINCT p.project_id) as project_countFROM departments dLEFT JOIN employees e ON d.dept_id = e.dept_idLEFT JOIN projects p ON d.dept_id = p.dept_idGROUP BY d.dept_id, d.dept_name;

九、常见问题与解决方案

9.1 性能问题排查

问题：复合查询执行缓慢

解决方案：

  1. 使用EXPLAIN分析执行计划

  2. 检查是否使用了适当的索引

  3. 考虑将复杂查询拆分为多个简单查询

  4. 评估是否可以使用临时表存储中间结果

9.2 结果不符合预期

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

解决方案：

  1. 检查连接条件是否正确

  2. 确认使用正确的JOIN类型(INNER/LEFT/RIGHT)

  3. 验证WHERE条件逻辑

  4. 检查NULL值的处理方式

9.3 语法错误处理

常见错误：

  • 子查询返回多行但使用了比较运算符

  • 在GROUP BY或HAVING中引用了不存在的列

  • UNION查询的列数或类型不匹配

解决方案：

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

十、总结与进阶学习建议

10.1 复合查询核心要点总结

  1. 子查询适合解决分步查询问题，但要注意性能

  2. 连接查询是处理表关系的强大工具

  3. UNION提供了垂直合并结果集的能力

  4. CTE提高了复杂查询的可读性和可维护性

10.2 进阶学习建议

  1. 深入学习执行计划：掌握EXPLAIN输出解读

  2. 了解查询优化器原理：学习MySQL如何优化查询

  3. 研究分区表查询：大数据量下的查询优化

  4. 学习窗口函数：MySQL 8.0+的高级分析功能

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

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

网络标签：

相关问题