当前位置： 首页 > news >正文

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

news 2025/6/24 8:52:54

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

  2. 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;

  3. SELECT子句子查询

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

  4. 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 按子查询相关性分类

  1. 非相关子查询

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

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

3.2 子查询操作符详解

  1. IN操作符

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

  2. NOT IN操作符

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

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

  4. 比较运算符子查询

    SELECT emp_name, salary
FROM employees
WHERE 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_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = '北京总部';

  2. 使用EXISTS替代IN

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

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

    -- 只选择必要的列
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)

  1. 左外连接(LEFT JOIN)

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

  2. 右外连接(RIGHT JOIN)

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

  3. 全外连接(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 连接查询优化策略

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

  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_name
FROM 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_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_salaryFROM employeesGROUP 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 levelFROM employeesWHERE manager_id IS NULLUNION ALL-- 递归查询：找出每个员工的下属SELECT e.emp_id, e.emp_name, e.manager_id, oh.level + 1FROM employees eJOIN 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_countFROM departments dLEFT JOIN employees e ON d.dept_id = e.dept_idLEFT JOIN emp_projects ep ON e.emp_id = ep.emp_idLEFT JOIN projects p ON d.dept_id = p.dept_idGROUP 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_salaryFROM employeesGROUP BY dept_id
),
project_employees AS (SELECT DISTINCT emp_idFROM 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 复合查询性能优化

  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;

  2. 索引优化建议

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

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

    • 考虑复合索引的顺序

  3. 查询重写技巧

    -- 不推荐：使用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 复合查询最佳实践

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

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

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

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

    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 性能问题排查

问题：复合查询执行缓慢

解决方案：

  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 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 复合查询核心要点总结

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

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

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

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

10.2 进阶学习建议

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

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

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

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

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

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

http://www.lryc.cn/news/574144.html

相关文章：

  • 左神算法之给定一个数组arr，返回其中的数值的差值等于k的子数组有多少个
  • Flask学习笔记
  • Java数据结构第二十四期：探秘 AVL 树，当二叉搜索树学会 “自我调节”
  • 腾讯云 CodeBuddy 技术评估报告（2025年）：编码效率提升40%，复杂工程处理能力领先Cursor 35%​
  • 【Java开发日记】我们详细讲解一下 Java 中 new 一个对象的流程
  • Nginx与Tomcat：谁更适合你的服务器？
  • Python 商务数据分析—— NumPy 学习笔记Ⅱ
  • react gsap动画库使用详解之scroll滑动动画
  • 分布式系统 - 分布式锁及实现方案
  • 【开源工具】一键解决使用代理后无法访问浏览器网页问题 - 基于PyQt5的智能代理开关工具开发全攻略
  • 爬虫002-----urllib标准库
  • 企业主动风险管理破局供应链“黑天鹅”，善用期货
  • PDF-XSS
  • 游戏盾高效防范DDoS攻击的安全防护
  • 跨平台是.NET Framework4.5与.NET8的核心区别
  • 用ESP8266+MQTT构建完全属于自己的物联网平台
  • Life：Internship in OnSea Day 1
  • Excel工具箱WPS版 增强插件 文本处理、批量录入 数据对比 高级排序
  • vivado使用非自带的第三方编辑器
  • 采集MFC软件的数据方法记录
  • Redis Stream 消息队列详解及 PHP 实现
  • 光伏电站 “智慧大脑”：安科瑞 Acrel-1000DP 分布式监控系统技术解析
  • 单片机测ntc热敏电阻的几种方法（软件）
  • 【Android】Activity 的生命周期和启动模式
  • SAP将指定EXCEL工作SHEET的数据上传到内表
  • 基于 BERT 与语义角色标注的细粒度中文仇恨言论检测
  • 剑指offer40_数字序列中某一位的数字
  • 中国风国潮通用PPT模版
  • 【项目管理】项目管理资料文档模板（ZIP，PPT，WORD）
  • 榕壹云无人售货机管理系统：开源架构赋能私有化部署，打造智能零售技术解决方案