2. 首页 > MySQL教程 > 正文

MySQL教程FG079-MySQL复杂查询技巧

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：7

内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

复杂查询是指使用高级SQL技术来解决复杂的数据查询问题，包括子查询、连接查询、聚合函数、窗口函数等。这些技术可以帮助我们从数据库中获取更复杂、更有价值的信息。 03 学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 标量子查询

-- 标量子查询：返回单个值
SELECT 
    name, 
    salary, 
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

-- 标量子查询作为条件
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

2.2 列子查询

-- 列子查询：返回一列值
SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE name LIKE '%技术%');

2.3 行子查询

-- 行子查询：返回一行值
SELECT * FROM employees 
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

2.4 表子查询

-- 表子查询：返回一个表
SELECT * FROM (
    SELECT department_id, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY department_id
) AS dept_avg 
WHERE avg_salary > 8000;

2.5 相关子查询

-- 相关子查询：子查询依赖外部查询
SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);

Part03-生产环境项目实施方案

3.1 内连接

-- 内连接：只返回匹配的行
SELECT e.name, e.salary, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

3.2 左连接

-- 左连接：返回左表所有行，右表匹配的行
SELECT e.name, e.salary, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

3.3 右连接

-- 右连接：返回右表所有行，左表匹配的行
SELECT e.name, e.salary, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

3.4 全连接

-- 全连接：返回两个表的所有行（MySQL不直接支持，使用UNION）
SELECT e.name, e.salary, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, e.salary, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

3.5 自连接

-- 自连接：表与自身连接
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

3.6 多表连接

-- 多表连接：连接多个表
SELECT e.name, e.salary, d.name AS department, p.name AS project
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN employee_project ep ON e.id = ep.employee_id
JOIN projects p ON ep.project_id = p.id;

Part04-生产案例与实战讲解

4.1 基本聚合函数

-- 基本聚合函数
SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

4.2 分组聚合

-- 分组聚合
SELECT 
    department_id, 
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

4.3 带条件的聚合

-- 带条件的聚合
SELECT 
    department_id,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary > 10000 THEN 1 END) AS high_salary_count,
    AVG(CASE WHEN age > 30 THEN salary END) AS avg_salary_over_30
FROM employees
GROUP BY department_id;

4.4 嵌套聚合

-- 嵌套聚合
SELECT 
    MAX(avg_salary) AS max_avg_salary
FROM (
    SELECT 
        department_id, 
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg;

Part05-风哥经验总结与分享

5.1 排名函数

-- 排名函数
SELECT 
    name, 
    department_id, 
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

5.2 聚合窗口函数

-- 聚合窗口函数
SELECT 
    name, 
    department_id, 
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
    COUNT(*) OVER (PARTITION BY department_id) AS dept_count
FROM employees;

5.3 移动窗口函数

-- 移动窗口函数
SELECT 
    date, 
    fgsales,
    AVG(fgsales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg,
    SUM(fgsales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM fgsales_data;

5.4 窗口函数的高级用法

-- 窗口函数的高级用法
SELECT 
    name, 
    department_id, 
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary,
    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary,
    LEAD(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS next_higher_salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS previous_higher_salary
FROM employees;

6. 递归查询技巧

6.1 递归CTE基本用法

-- 递归CTE基本用法
WITH RECURSIVE cte AS (
    -- 锚点查询
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询
    SELECT c.id, c.name, c.parent_id 
    FROM categories c
    JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

6.2 递归CTE生成序列

-- 递归CTE生成序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

6.3 递归CTE计算层级

-- 递归CTE计算层级
WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ch.level + 1 
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM category_hierarchy ORDER BY level, id;

7. 复杂条件查询技巧

7.1 高级WHERE条件

-- 高级WHERE条件
SELECT * FROM employees
WHERE 
    (department_id = 1 AND salary > 10000) OR
    (department_id = 2 AND salary > 8000) OR
    (department_id IN (3, 4) AND salary > 6000);

7.2 使用CASE语句

-- 使用CASE语句
SELECT 
    name, 
    salary,
    CASE 
        WHEN salary > 15000 THEN '高收入'
        WHEN salary > 10000 THEN '中等收入'
        ELSE '低收入'
    END AS income_level
FROM employees;

7.3 使用IF函数

-- 使用IF函数
SELECT 
    name, 
    salary, 
    IF(salary > 10000, '高收入', '普通收入') AS income_level
FROM employees;

7.4 使用NULL处理函数

-- 使用NULL处理函数
SELECT 
    name, 
    COALESCE(manager_id, 0) AS manager_id,
    IFNULL(commission, 0) AS commission,
    NULLIF(salary, 0) AS non_zero_salary
FROM employees;

8. 性能优化技巧

8.1 使用索引

-- 确保WHERE条件和JOIN条件使用索引
CREATE INDEX idx_department_salary ON employees(department_id, salary);

8.2 避免SELECT *

-- 只查询需要的列
SELECT id, name, salary FROM employees WHERE department_id = 1;

8.3 使用EXPLAIN分析执行计划

-- 分析执行计划
EXPLAIN SELECT * FROM employees WHERE department_id = 1 AND salary > 10000;

8.4 优化子查询

-- 优化子查询：使用JOIN代替子查询
SELECT e.* 
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY department_id
) AS dept_avg ON e.department_id = dept_avg.department_id 
WHERE e.salary > dept_avg.avg_salary;

8.5 使用LIMIT限制结果集

-- 使用LIMIT限制结果集
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

9. 实际案例分析

9.1 案例1：销售数据分析

-- 销售数据分析
SELECT 
    p.name AS product,
    c.name AS category,
    SUM(s.quantity) AS total_quantity,
    SUM(s.quantity * p.price) AS total_fgsales,
    AVG(s.quantity * p.price) AS avg_sale,
    MAX(s.quantity * p.price) AS max_sale
FROM fgsales s
JOIN products p ON s.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY p.id, c.id
ORDER BY total_fgsales DESC
LIMIT 10;

9.2 案例2：员工绩效分析

-- 员工绩效分析
SELECT 
    e.name AS employee,
    d.name AS department,
    COUNT(s.id) AS sale_count,
    SUM(s.quantity * p.price) AS total_fgsales,
    RANK() OVER (PARTITION BY e.department_id ORDER BY SUM(s.quantity * p.price) DESC) AS dept_rank,
    RANK() OVER (ORDER BY SUM(s.quantity * p.price) DESC) AS overall_rank
FROM employees e
LEFT JOIN fgsales s ON e.id = s.employee_id
LEFT JOIN products p ON s.product_id = p.id
JOIN departments d ON e.department_id = d.id
WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY e.id, d.id
ORDER BY total_fgsales DESC;

9.3 案例3：库存管理

-- 库存管理
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category,
    p.stock_quantity AS current_stock,
    AVG(s.quantity) AS avg_daily_fgsales,
    p.stock_quantity / AVG(s.quantity) AS days_of_inventory
FROM products p
LEFT JOIN fgsales s ON p.id = s.product_id AND s.sale_date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
JOIN categories c ON p.category_id = c.id
GROUP BY p.id, c.id
HAVING days_of_inventory < 7
ORDER BY days_of_inventory ASC;

10. 总结

复杂查询是MySQL数据库操作的高级技术，掌握这些技巧可以帮助我们更有效地从数据库中获取有价值的信息。本文详细介绍了MySQL中的复杂查询技巧，包括子查询、连接查询、聚合函数、窗口函数、递归查询等。 04 风哥提示：

在使用复杂查询时，需要注意以下几点： 05更多学习教程公众号风哥教程itpux_com

  • 选择合适的查询方法，根据具体场景选择最有效的查询方式
  • 注意查询性能，避免使用过于复杂的查询影响数据库性能
  • 使用索引来提高查询速度
  • 使用EXPLAIN分析执行计划，优化查询语句
  • 遵循最佳实践，写出高效、易维护的SQL语句

通过掌握这些复杂查询技巧，可以更有效地处理各种数据查询需求，提高数据分析能力和数据库操作效率。 06 from mysql视频:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息