MySQL復(fù)合查詢從基礎(chǔ)到高級(jí)應(yīng)用全面解析

更新時(shí)間：2025年12月18日 09:55:40 作者：GG Bond.?

本文詳細(xì)介紹了MySQL中的復(fù)合查詢,包括基礎(chǔ)概念、主要類型、示例數(shù)據(jù)庫(kù)結(jié)構(gòu)、子查詢、連接查詢、UNION查詢、派生表與CTE高級(jí)用法、實(shí)戰(zhàn)案例、性能優(yōu)化與最佳實(shí)踐以及常見(jiàn)問(wèn)題與解決方案,感興趣的朋友跟隨小編一起看看吧

一、復(fù)合查詢基礎(chǔ)概念

1.1 什么是復(fù)合查詢

復(fù)合查詢是指將多個(gè)簡(jiǎn)單查詢通過(guò)特定的SQL語(yǔ)法組合起來(lái)，形成一個(gè)功能更加強(qiáng)大的查詢語(yǔ)句。與簡(jiǎn)單查詢相比，復(fù)合查詢能夠：

處理更復(fù)雜的數(shù)據(jù)關(guān)系
減少應(yīng)用程序中的數(shù)據(jù)處理邏輯
提高數(shù)據(jù)檢索效率（當(dāng)正確使用時(shí)）
實(shí)現(xiàn)跨表的數(shù)據(jù)關(guān)聯(lián)和分析

1.2 復(fù)合查詢的主要類型

MySQL中常見(jiàn)的復(fù)合查詢包括：

子查詢（Subqueries）
連接查詢（JOIN Operations）
聯(lián)合查詢（UNION Queries）
派生表（Derived Tables）
公用表表達(dá)式（Common Table Expressions，CTE）

二、示例數(shù)據(jù)庫(kù)結(jié)構(gòu)詳解

在進(jìn)行講解我們的查詢之前，我們先看一下名為需要用到的表，以及往表里添加幾組示例數(shù)據(jù)，以方便我們查詢后看到查詢的效果

2.1 完整的表結(jié)構(gòu)設(shè)計(jì)

-- 部門(mén)表
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)
);
-- 項(xiàng)目表
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)
);
-- 員工項(xiàng)目關(guān)聯(lián)表
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 示例數(shù)據(jù)填充

-- 部門(mén)數(shù)據(jù)
INSERT INTO departments VALUES
(1, '技術(shù)研發(fā)部', '北京總部', '2015-06-01', 2000000.00),
(2, '市場(chǎng)營(yíng)銷部', '上海分公司', '2016-03-15', 1500000.00),
(3, '人力資源部', '廣州辦事處', '2017-01-10', 800000.00),
(4, '財(cái)務(wù)部', '北京總部', '2015-06-01', 1200000.00);
-- 員工數(shù)據(jù)
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, '錢(qián)強(qiáng)', 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');
-- 項(xiàng)目數(shù)據(jù)
INSERT INTO projects VALUES
(1, '新一代電商平臺(tái)開(kāi)發(fā)', 800000.00, '2023-01-10', '2023-09-30', 1, 'In Progress'),
(2, '全球市場(chǎng)推廣計(jì)劃', 500000.00, '2023-02-15', '2023-08-15', 2, 'In Progress'),
(3, '員工技能提升計(jì)劃', 200000.00, '2023-03-01', '2023-12-31', 3, 'Planning'),
(4, '財(cái)務(wù)系統(tǒng)云遷移', 350000.00, '2023-04-01', NULL, 4, 'In Progress'),
(5, '移動(dòng)端應(yīng)用優(yōu)化', 300000.00, '2023-05-15', '2023-11-30', 1, 'Planning');
-- 員工項(xiàng)目關(guān)聯(lián)
INSERT INTO emp_projects VALUES
(1, 1, '技術(shù)負(fù)責(zé)人', '2023-01-05', 30),
(2, 1, '開(kāi)發(fā)工程師', '2023-01-10', 40),
(1, 5, '架構(gòu)師', '2023-05-10', 20),
(3, 2, '市場(chǎng)總監(jiān)', '2023-02-10', 25),
(4, 2, '市場(chǎng)專員', '2023-02-15', 35),
(5, 3, '培訓(xùn)經(jīng)理', '2023-03-01', 30),
(6, 3, '培訓(xùn)助理', '2023-03-05', 20),
(7, 4, '項(xiàng)目經(jīng)理', '2023-04-01', 40);

三、子查詢深度解析

3.1 子查詢分類與語(yǔ)法

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 按子查詢相關(guān)性分類

非相關(guān)子查詢

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

相關(guān)子查詢

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

比較運(yùn)算符子查詢

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

3.3 子查詢性能優(yōu)化

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

-- 當(dāng)子查詢結(jié)果集大時(shí)更高效
SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM projects p 
             WHERE p.dept_id = d.dept_id);

限制子查詢返回的列數(shù)

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

四、連接查詢?nèi)嬷v解

4.1 連接類型詳解

4.1.1 內(nèi)連接(INNER JOIN)

-- 基本內(nèi)連接
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 帶條件的內(nèi)連接
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)

-- 查詢所有部門(mén)及其員工(包括沒(méi)有員工的部門(mén))
SELECT d.dept_name, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

右外連接(RIGHT JOIN)

-- 查詢所有員工及其部門(mén)(包括沒(méi)有部門(mén)的員工)
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通過(guò)UNION實(shí)現(xiàn)

-- 查詢所有員工和所有部門(mén)的組合
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)

-- 生成員工和項(xiàng)目的所有可能組合
SELECT e.emp_name, p.project_name
FROM employees e
CROSS JOIN projects p;

4.1.4 自連接(SELF JOIN)

-- 查詢員工及其經(jīng)理信息
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 連接查詢優(yōu)化策略

下面關(guān)于索引和視圖的知識(shí)后面還會(huì)詳細(xì)講解

確保連接條件有索引

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

選擇適當(dāng)?shù)倪B接順序

-- 小表驅(qū)動(dòng)大表原則
SELECT /*+ JOIN_ORDER(d, e) */ d.dept_name, e.emp_name
FROM departments d  -- 假設(shè)部門(mén)表比員工表小
JOIN employees e ON d.dept_id = e.dept_id;

使用STRAIGHT_JOIN強(qiáng)制連接順序

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查詢高級(jí)應(yīng)用

5.1 UNION基礎(chǔ)用法

-- 合并員工和部門(mén)名稱
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的區(qū)別

-- UNION會(huì)去重，UNION ALL不會(huì)
SELECT dept_id FROM employees WHERE salary > 20000
UNION
SELECT dept_id FROM departments WHERE budget > 1500000;

-- 使用UNION ALL提高性能(當(dāng)確定不需要去重時(shí))
SELECT emp_name FROM employees WHERE dept_id = 1
UNION ALL
SELECT emp_name FROM employees WHERE salary > 18000;

5.3 復(fù)雜UNION查詢示例

-- 按類型統(tǒng)計(jì)人數(shù)和預(yù)算
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高級(jí)用法

6.1 派生表(MySQL 5.7+)

-- 計(jì)算各部門(mén)薪資統(tǒng)計(jì)信息
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 公用表表達(dá)式(CTE, MySQL 8.0+)

6.2.1 基本CTE

-- 查詢參與項(xiàng)目的員工信息
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

-- 組織結(jié)構(gòu)層級(jí)查詢
WITH RECURSIVE org_hierarchy AS (
    -- 基礎(chǔ)查詢：找出所有沒(méi)有經(jīng)理的員工(頂層管理者)
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 遞歸查詢：找出每個(gè)員工的下屬
    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;

七、復(fù)合查詢實(shí)戰(zhàn)案例

7.1 多層級(jí)數(shù)據(jù)分析

-- 分析各部門(mén)項(xiàng)目參與情況
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 復(fù)雜業(yè)務(wù)邏輯實(shí)現(xiàn)

-- 找出每個(gè)部門(mén)薪資高于部門(mén)平均且參與項(xiàng)目的員工
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;

八、性能優(yōu)化與最佳實(shí)踐

8.1 復(fù)合查詢性能優(yōu)化

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;

索引優(yōu)化建議
- 為所有連接條件創(chuàng)建索引
- 為WHERE子句中的條件列創(chuàng)建索引
- 考慮復(fù)合索引的順序

查詢重寫(xiě)技巧

-- 不推薦：使用HAVING過(guò)濾分組前數(shù)據(jù)
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING dept_id IN (1, 2, 3);
-- 推薦：在WHERE子句中提前過(guò)濾
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
WHERE dept_id IN (1, 2, 3)
GROUP BY dept_id;

8.2 復(fù)合查詢最佳實(shí)踐

保持查詢簡(jiǎn)潔：避免過(guò)度復(fù)雜的嵌套
合理使用注釋：解釋復(fù)雜查詢的邏輯
分步構(gòu)建查詢：先測(cè)試子查詢?cè)俳M合
考慮使用視圖：對(duì)常用復(fù)雜查詢創(chuàng)建視圖

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;

九、常見(jiàn)問(wèn)題與解決方案

9.1 性能問(wèn)題排查

問(wèn)題：復(fù)合查詢執(zhí)行緩慢

解決方案：

使用EXPLAIN分析執(zhí)行計(jì)劃
檢查是否使用了適當(dāng)?shù)乃饕?/li>
考慮將復(fù)雜查詢拆分為多個(gè)簡(jiǎn)單查詢
評(píng)估是否可以使用臨時(shí)表存儲(chǔ)中間結(jié)果

9.2 結(jié)果不符合預(yù)期

問(wèn)題：查詢返回的行數(shù)多于或少于預(yù)期

解決方案：

檢查連接條件是否正確
確認(rèn)使用正確的JOIN類型(INNER/LEFT/RIGHT)
驗(yàn)證WHERE條件邏輯
檢查NULL值的處理方式

9.3 語(yǔ)法錯(cuò)誤處理

常見(jiàn)錯(cuò)誤：

子查詢返回多行但使用了比較運(yùn)算符
在GROUP BY或HAVING中引用了不存在的列
UNION查詢的列數(shù)或類型不匹配

解決方案：

-- 錯(cuò)誤示例：子查詢返回多行
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);

十、總結(jié)與進(jìn)階學(xué)習(xí)建議

10.1 復(fù)合查詢核心要點(diǎn)總結(jié)

子查詢適合解決分步查詢問(wèn)題，但要注意性能
連接查詢是處理表關(guān)系的強(qiáng)大工具
UNION提供了垂直合并結(jié)果集的能力
CTE提高了復(fù)雜查詢的可讀性和可維護(hù)性

10.2 進(jìn)階學(xué)習(xí)建議

深入學(xué)習(xí)執(zhí)行計(jì)劃：掌握EXPLAIN輸出解讀
了解查詢優(yōu)化器原理：學(xué)習(xí)MySQL如何優(yōu)化查詢
研究分區(qū)表查詢：大數(shù)據(jù)量下的查詢優(yōu)化
學(xué)習(xí)窗口函數(shù)：MySQL 8.0+的高級(jí)分析功能

以上就是關(guān)于MySQL查詢中的所有相關(guān)知識(shí)點(diǎn)，除了前面常用的外，后面的有些時(shí)候并不一定能用到，但都是有必要掌握的，由于篇幅原因，有些問(wèn)題并不能全面刨析到，建議大家看到不理解的地方可以再去找一些教學(xué)視頻看一下

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL復(fù)合查詢從基礎(chǔ)到高級(jí)應(yīng)用全面解析

目錄

一、復(fù)合查詢基礎(chǔ)概念

1.1 什么是復(fù)合查詢

1.2 復(fù)合查詢的主要類型

二、示例數(shù)據(jù)庫(kù)結(jié)構(gòu)詳解

2.1 完整的表結(jié)構(gòu)設(shè)計(jì)

2.2 示例數(shù)據(jù)填充

三、子查詢深度解析

3.1 子查詢分類與語(yǔ)法

3.1.1 按子查詢位置分類

3.1.2 按子查詢相關(guān)性分類

3.2 子查詢操作符詳解

3.3 子查詢性能優(yōu)化

四、連接查詢?nèi)嬷v解

4.1 連接類型詳解

4.1.1 內(nèi)連接(INNER JOIN)

4.1.2 外連接(OUTER JOIN)

4.1.3 交叉連接(CROSS JOIN)

4.1.4 自連接(SELF JOIN)

4.2 連接查詢優(yōu)化策略

五、UNION查詢高級(jí)應(yīng)用

5.1 UNION基礎(chǔ)用法

5.2 UNION ALL與UNION的區(qū)別

5.3 復(fù)雜UNION查詢示例

六、派生表與CTE高級(jí)用法

6.1 派生表(MySQL 5.7+)

6.2 公用表表達(dá)式(CTE, MySQL 8.0+)

6.2.1 基本CTE

6.2.2 遞歸CTE

七、復(fù)合查詢實(shí)戰(zhàn)案例

7.1 多層級(jí)數(shù)據(jù)分析

7.2 復(fù)雜業(yè)務(wù)邏輯實(shí)現(xiàn)

八、性能優(yōu)化與最佳實(shí)踐

8.1 復(fù)合查詢性能優(yōu)化

8.2 復(fù)合查詢最佳實(shí)踐

九、常見(jiàn)問(wèn)題與解決方案

9.1 性能問(wèn)題排查

9.2 結(jié)果不符合預(yù)期

9.3 語(yǔ)法錯(cuò)誤處理

十、總結(jié)與進(jìn)階學(xué)習(xí)建議

10.1 復(fù)合查詢核心要點(diǎn)總結(jié)

10.2 進(jìn)階學(xué)習(xí)建議

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

二、示例數(shù)據(jù)庫(kù)結(jié)構(gòu)詳解

四、連接查詢?nèi)嬷v解

五、UNION查詢高級(jí)應(yīng)用

六、派生表與CTE高級(jí)用法

八、性能優(yōu)化與最佳實(shí)踐

十、總結(jié)與進(jìn)階學(xué)習(xí)建議