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

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

Lomu • • 未分类 • 阅读 114

文章标题：

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
上一篇 2025 年 8 月 6 日
下一篇 2025 年 8 月 6 日

相关推荐

  • 无需付费自动获取最新版datagrip激活码，权威破解教程 DataGrip激活码

    无需付费自动获取最新版datagrip激活码，权威破解教程

    免责声明：下文提及的 DataGrip 破解补丁与激活码均源自网络公开资源，仅供个人学习研究，禁止商业用途。若出现版权争议，请第一时间联系作者删除。条件允许时，强烈建议购买官方正版！ 先放一张实测截图，DataGrip 2025.2.1 已顺利激活至 2099 年，效果如图，稳！ 下面用图文方式手把手演示最新版 DataGrip 的完整激活流程。 嫌折腾？直…

    2025 年 10 月 18 日
    5300
  • 🚀 2025年最新IDEA激活码分享：永久破解IDEA终极教程（附破解补丁） IDEA破解教程

    🚀 2025年最新IDEA激活码分享：永久破解IDEA终极教程（附破解补丁）

    💻 教程简介 本教程适用于JetBrains全家桶（IDEA、PyCharm、DataGrip、Goland等），手把手教你永久激活到2099年！先看最新IDEA版本破解成功截图👇 📥 准备工作 下载IDEA安装包 访问官网 https://www.jetbrains.com/idea/download/ 下载最新版IDEA 安装过程超简单：1. 设置安装路…

    2025 年 6 月 9 日
    1.2K00
  • 2025最新PyCharm永久激活码及破解教程（亲测有效2099年）🔥 PyCharm破解教程

    2025最新PyCharm永久激活码及破解教程（亲测有效2099年）🔥

    本教程适用于JetBrains全家桶（IDEA、PyCharm、DataGrip、Goland等）所有产品！💯 先给大家看看最新PyCharm版本破解成功的实锤截图👇 有效期直接拉到2099年，简直不要太爽！ 下面我就手把手教大家如何轻松激活PyCharm，这个方法通杀所有版本，无论你是： 🖥️ Windows用户 🍎 Mac用户 🐧 Linux用户 统统适…

    2025 年 5 月 13 日
    1.3K00

  • 2025年最新PyCharm激活码与永久破解教程（支持2099年）

    本方法适用于JetBrains全家桶，包括PyCharm、IDEA、DataGrip、Goland等开发工具！ 先给大家展示最新PyCharm版本成功破解的截图，可以看到已经完美激活到2099年！ 下面我将用详细的图文教程，手把手教你如何将PyCharm激活至2099年。 这个方法不仅适用于最新版本，也兼容所有旧版PyCharm！ Windows/Mac/L…

    PyCharm激活码 2025 年 8 月 5 日
    18000
  • 2025最新PyCharm永久激活破解教程（亲测有效，支持2099年）🔥 PyCharm破解教程

    2025最新PyCharm永久激活破解教程（亲测有效，支持2099年）🔥

    本教程适用于Jetbrains全家桶，包括IDEA、PyCharm、DataGrip、Goland等所有产品！💯 先给大家看看最新版PyCharm成功破解到2099年的效果图，是不是很给力？😎 下面我就手把手教你如何永久激活PyCharm，这个方法同样适用于旧版本哦～ ✨ 支持所有系统：- Windows用户看过来- Mac用户别着急- Linux用户也有份…

    2025 年 5 月 19 日
    55700

发表回复

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

联系我们

400-800-8888

在线咨询： QQ交谈

邮件：admin@example.com

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

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