1. 程序员社区_IDEA激活码,IDEA破解教程,PyCharm激活码首页

MySQL复合查询：从基础到高阶的深度剖析

Lomu • • 未分类 • 阅读 12

文章标题：

MySQL复合查询：从基础到高级的透彻剖析

文章内容：MySQL知识研习：

前言：

完成表的增删改查学习后，今日聚焦查询的复杂情形——复合查询的相关内容

目录

一、复合查询基础概念

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_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 =

文章整理自互联网，只做测试使用。发布者：Lomu，转转请注明出处：https://www.it1024doc.com/13069.html

赞 (0)
LomuLomu
0
上一篇 16小时前
下一篇 14小时前

相关推荐

  • 🚀 2025年最新IDEA激活码 & 永久破解教程（支持JetBrains全家桶）💻 IDEA破解教程

    🚀 2025年最新IDEA激活码 & 永久破解教程（支持JetBrains全家桶）💻

    大家好！今天给大家带来一篇超详细的IDEA破解教程，适用于IntelliJ IDEA、PyCharm、DataGrip、GoLand等JetBrains全家桶产品！🔥 先上最新IDEA版本破解成功的截图，可以看到已经成功破解到2099年啦！🎉 📥 下载IDEA安装包 如果你已经下载了IDEA，可以跳过这一步哦！ 访问IDEA官网：https://www.je…

    2025 年 6 月 14 日
    1.6K00
  • Slurm HPC 集群安装 Slurm-web（python Flask开源项目） 未分类

    Slurm HPC 集群安装 Slurm-web（python Flask开源项目）

    slurm-web，也称为 slurm-wlm-web，是为 Slurm 工作负载管理器提供的一个 Web 界面。Slurm 是一个用于管理大型和小型 Linux 集群的开源、容错且高度可扩展的集群管理和作业调度系统。slurm-web 的主要功能是提供一个方便用户监控和管理 Slurm 集群的途径，而无需直接使用命令行工具。 Slurm-web 在 Slu…

    2025 年 1 月 14 日
    47700
  • 2024 GoLand最新激活码，GoLand永久免费激活码2024-12-30 更新 GoLand激活码

    2024 GoLand最新激活码，GoLand永久免费激活码2024-12-30 更新

    GoLand 2024最新激活码 以下是最新的GoLand激活码，更新时间：2024-12-30 🔑 激活码使用说明 1️⃣ 复制下方激活码 2️⃣ 打开 GoLand 软件 3️⃣ 在菜单栏中选择 Help -> Register 4️⃣ 选择 Activation Code 5️⃣ 粘贴激活码，点击 Activate ⚠️ 必看！必看！ 🔥 获取最新激活…

    2024 年 12 月 30 日
    42100

  • Python 潮流周刊#85：让 AI 帮你写出更好的代码（摘要）

    本周刊由 Python猫 出品，精心筛选国内外的 250+ 信息源，为你挑选最值得分享的文章、教程、开源项目、软件工具、播客和视频、热门话题等内容。愿景：帮助所有读者精进 Python 技术，并增长职业和副业的收入。 分享了 12 篇文章，12 个开源项目，1 则音视频，全文 2300 字。 以下是本期摘要： 🦄文章&教程 ① 如果一直要求 LLM “写出更…

    未分类 2025 年 1 月 12 日
    26900

  • ORM框架与数据库交互

    — title: ORM框架与数据库交互 date: 2024/12/22 updated: 2024/12/22 author: cmdragon excerpt: 对象关系映射（ORM）框架是连接数据库与编程语言的桥梁，它极大地简化了两者之间的交互。通过ORM，开发者能够以面向对象的方式处理数据库操作，避免了直接编写SQL语句的繁琐，从而提升开发效率…

    未分类 2024 年 12 月 27 日
    27600

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

联系我们

400-800-8888

在线咨询： QQ交谈

邮件：admin@example.com

工作时间：周一至周五，9:30-18:30，节假日休息

关注微信
原汁原味正版ChatGPT-Plus共享账号，完全稳定，无需翻墙！带售后！点击查看....