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

GG Bond1

发布于 2025-07-17 17:18:04

14400

代码可运行

文章被收录于专栏：C/C++葵花宝典C/C++葵花宝典

运行总次数：0

代码可运行

MySQL学习：

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

前言：

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

一、复合查询基础概念

1.1 什么是复合查询

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

处理更复杂的数据关系
减少应用程序中的数据处理逻辑
提高数据检索效率（当正确使用时）
实现跨表的数据关联和分析

1.2 复合查询的主要类型

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

子查询（Subqueries）
连接查询（JOIN Operations）
联合查询（UNION Queries）
派生表（Derived Tables）
公用表表达式（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 按子查询位置分类

WHERE子句子查询

SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) 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;

SELECT子句子查询

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

HAVING子句子查询

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

3.1.2 按子查询相关性分类

非相关子查询

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

相关子查询

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

3.2 子查询操作符详解

IN操作符

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

NOT IN操作符

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

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

比较运算符子查询

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

3.3 子查询性能优化

使用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 = '北京总部';

使用EXISTS替代IN

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

限制子查询返回的列数

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

四、连接查询全面讲解

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)

左外连接(LEFT JOIN)

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

右外连接(RIGHT JOIN)

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

全外连接(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;

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 连接查询优化策略

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

确保连接条件有索引

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

选择适当的连接顺序

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

使用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;

五、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 复合查询性能优化

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;

索引优化建议

为所有连接条件创建索引
为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;

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

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-06-23，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度