MySQL的复合查询

多表查询

MySQL中的多表查询是一个强大的功能，允许你在一个查询中组合来自多个表的数据。这通常通过JOIN语句实现，但也可以使用子查询或集合操作符（如UNION）。下面我将介绍几种常见的多表查询方式及其用途。

INNER JOIN（内连接）

INNER JOIN返回两个表中有匹配的记录。如果一行在一个表中有匹配，在另一个表中也有，则MySQL将这两行合并为一行并返回。

语法示例：

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

这个查询会返回所有员工的名字和他们部门的名字，即使某些员工没有对应的部门信息也会显示出来，这时部门名字为NULL。

RIGHT JOIN（右连接）

RIGHT JOIN和LEFT JOIN相似，但它返回从右表的所有记录以及左表中匹配的记录。如果右表的记录在左表中没有匹配，结果中这些记录对应的左表部分将包含空值。

语法示例：

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

这个查询将返回所有部门的名字和在该部门工作的员工的名字，如果某些部门没有员工，员工名字为NULL。

CROSS JOIN（交叉连接）

CROSS JOIN生成的是两个表的笛卡尔积。每个左表的行与右表的每行相结合。

语法示例

SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;

这个查询会返回所有可能的员工与部门的组合，不管他们是否有实际的对应关系。

使用多个JOIN

可以在一个查询中使用多个JOIN操作来连接多个表。

语法示例：

SELECT employees.name, departments.name, offices.location
FROM employees
JOIN departments ON employees.department_id = departments.department_id
JOIN offices ON departments.office_id = offices.office_id;

这个查询将连接三个表，显示员工名、他们的部门名和部门所在的办公室位置。

注意事项

• 使用JOIN时务必确保ON子句中的连接条件正确，否则可能会导致结果不正确或效率低下。
• 对于大表，合适的索引是提高JOIN性能的关键。
• 每种JOIN类型根据查询需求选择使用，理解每种JOIN的数据返回特性非常重要。

子查询

在MySQL中，子查询是指嵌套在另一个查询中的查询。子查询可以用于SELECT, INSERT, UPDATE, 或 DELETE语句中，提供了一种强大的方式来处理复杂的数据关系。根据其返回的数据行数和列数，子查询可以被分类为单行子查询、多行子查询和多列子查询。此外，子查询还可以在FROM子句中使用。下面是对这些类型的详细解释和例子：

单行子查询

单行子查询返回单个行的单个列。它常用于条件表达式中，与比较运算符一起使用，如 =, >, <, >=, <=。

示例：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

这个查询会找出所有薪水高于公司平均薪水的员工。

多行子查询

多行子查询返回一列但是多行结果。它常与IN, ANY, ALL, EXISTS等运算符一起使用。

示例：

SELECT name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

这个查询会返回所有在纽约部门工作的员工的名字和部门ID。

多列子查询

多列子查询返回多个列的一行或多行。这种类型的子查询通常在比较涉及多个列的情况下使用。

示例：

SELECT employee_id, name
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

这个查询返回每个部门薪水最高的员工的ID和名字。

FROM子查询（子查询作为派生表）

在FROM子句中使用的子查询被称为派生表。这允许你在选择从子查询结果进行操作。

示例：

SELECT AVG(salary)
FROM (SELECT department_id, salary FROM employees WHERE department_id > 100) AS dept_salaries;

EXISTS子查询

EXISTS是一个布尔运算符，用来测试子查询是否返回任何行。如果子查询返回至少一行，则EXISTS子查询的结果为真。

示例：

SELECT name
FROM departments d
WHERE EXISTS (SELECT * FROM employees e WHERE e.department_id = d.department_id);

合并查询

在MySQL中，合并查询通常是指使用UNION或UNION ALL来组合两个或多个SELECT语句的结果集为一个单一的结果集。这种方法非常有用于从多个表中检索类似的数据，特别是当这些表具有相似的数据结构时。

使用UNION和UNION ALL

• UNION：UNION操作符用于合并多个SELECT语句的结果集，同时自动去除重复行。所有的SELECT语句需要有相同数量的列，并且对应列的数据类型也需要兼容。

• UNION ALL：与UNION类似，UNION ALL也是合并多个SELECT语句的结果集，但它不会去除结果中的重复行，因此通常比UNION执行得更快。

示例

假设有两个表格：employees和contractors，这两个表格都包含关于工作人员的信息，但存储在不同的表中。

employees 表:

• employee_id
• name
• department

contractors 表:

• contractor_id
• name
• working_area

你想要一个包含所有员工和承包商名单的列表。

使用UNION

SELECT name, department AS work_place
FROM employees
UNION
SELECT name, working_area AS work_place
FROM contractors;

这个查询将从两个表中获取名字和工作区域，并通过UNION合并结果，同时去除任何可能的重复记录。

使用UNION ALL

SELECT name, department AS work_place
FROM employees
UNION ALL
SELECT name, working_area AS work_place
FROM contractors;

这个查询与上面的查询类似，但使用UNION ALL确保包括所有的记录，即使它们是重复的。

使用场景

• 数据整合：当你需要从不同的表中获取类似的数据并进行整合显示时。
• 报表生成：当你需要为报表生成一个综合视图，这个视图可能需要聚合多个表中的数据。
• 数据比较：使用UNION来比较或找出两个表中不同的记录。