如何掌握MySQL中的复杂查询技巧？

MySQL复杂查询实例详解

在数据库管理中，复杂查询是提升数据分析和处理能力的关键，本文将通过多个实例详细讲解如何在MySQL中进行复杂查询，包括子查询、联合查询、聚合函数等高级功能。

1. 子查询（Subquery）

1 什么是子查询？

子查询是指一个查询嵌套在另一个查询之中，子查询可以出现在SELECT、FROM、WHERE、HAVING 等子句中。

2 实例：查找每个部门工资最高的员工

假设我们有一个名为employees 的表，包含以下列：

employee_id: 员工ID

name: 员工姓名

department_id: 部门ID

salary: 工资

创建示例表并插入数据
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);
INSERT INTO employees (employee_id, name, department_id, salary) VALUES
(1, 'Alice', 1, 5000.00),
(2, 'Bob', 1, 6000.00),
(3, 'Charlie', 2, 7000.00),
(4, 'David', 2, 8000.00),
(5, 'Eve', 3, 9000.00);

要查找每个部门工资最高的员工，可以使用以下查询：

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

3 结果分析

此查询首先为每个department_id 找到最高工资，然后在外部查询中匹配这些最高工资的员工信息。

2. 联合查询（Joins）

1 什么是联合查询？

联合查询用于将来自两个或多个表的数据组合在一起，常见的联合类型有 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN。

2 实例：查找员工及其部门名称

假设我们有一个额外的表departments，包含以下列：

department_id: 部门ID

department_name: 部门名称

创建示例表并插入数据
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);
INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Marketing');

要查找每个员工及其所属部门的名称，可以使用 INNER JOIN：

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

3 结果分析

此查询通过department_id 将employees 表和departments 表连接起来，从而获取每个员工对应的部门名称。

3. 聚合函数（Aggregate Functions）

1 什么是聚合函数？

聚合函数用于执行计算并返回单一值，如计数、总和、平均值等，常见的聚合函数包括COUNT(),SUM(),AVG(),MAX(),MIN()。

3.2 实例：统计各部门的工资总额和平均工资

要统计每个部门的工资总额和平均工资，可以使用以下查询：

SELECT department_id, SUM(salary) AS total_salary, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

3 结果分析

此查询通过GROUP BY 按department_id 分组，并使用SUM() 和AVG() 聚合函数计算每个部门的工资总额和平均工资。

相关问题与解答

问题1：如何在MySQL中实现全外连接（FULL OUTER JOIN）？

解答：MySQL不直接支持FULL OUTER JOIN，但可以通过UNION来实现，要实现employees 表和departments 表的全外连接，可以使用以下查询：

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

问题2：如何在MySQL中使用窗口函数？

解答：MySQL 8.0及以上版本支持窗口函数，窗口函数允许你在查询结果集中执行复杂的计算，要为每个员工添加一个排名列，可以使用以下查询：

SELECT name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

这个查询将为每个部门的员工按工资降序排名。