mysql 多表查询 & 子查询

多表查询

在数据库管理系统中，多表查询是一个重要的概念，它允许我们从多个表中同时检索数据。这在现实世界中非常重要，因为它可以帮助我们快速有效地从不同的数据源中整合信息。

连接查询

连接查询是多表查询的一个关键组成部分，它基于一定的连接条件将两个或多个表中的行结合起来。连接查询可以是内连接（INNER JOIN）、左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）或其他类型的连接。

隐式内连接

隐式内连接是一种不需要明确指定连接关键字的连接方法。在这种连接方式中，两个表通过WHERE子句中的条件连接起来。语法结构如下：

SELECT fields FROM table1, table2 WHERE condition;

在这个查询中，condition定义了两个表之间的连接条件。

显式内连接

显式内连接是一种明确指定连接关键字的连接方法。在这种连接方式中，两个表通过INNER JOIN关键字连接起来。语法结构如下：

SELECT fields
FROM table1 [INNER] JOIN table2
ON condition;

inser 是可以省略的。在这个查询中，ON condition定义了两个表之间的连接条件。

案例对比

假设我们有以下两个表：

• employees 表有 id、name 和 department_id 三个字段。
• departments 表有 id 和 department_name 两个字段。

如果我们想要查找所有员工的名字和他们所在的部门名称，我们可以使用隐式内连接和显式内连接两种方式：

隐式内连接案例

SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.id;

显式内连接案例

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

这两个查询都会返回相同的结果，因为它们都是基于相同的连接条件连接两个表。然而，显式内连接提供了更好的可读性和灵活性，因为它允许我们在ON子句中定义更复杂的连接条件。

外连接查询

外连接查询包括左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL JOIN）。左外连接返回左表的所有行，即使右表中没有匹配的行；右外连接则相反，全外连接会返回两个表的所有行。

左外连接

左外连接返回左侧表（即第一个被引用的表）的所有行，以及右侧表中匹配的行。如果右侧表中没有匹配的行，则右侧表的列将填充 NULL 值。语法结构如下：

SELECT fields
FROM table1
LEFT JOIN table2
ON condition;

右外连接

右外连接返回右侧表（即第二个被引用的表）的所有行，以及左侧表中匹配的行。如果左侧表中没有匹配的行，则左侧表的列将填充 NULL 值。语法结构如下：

SELECT fields
FROM table1
RIGHT JOIN table2
ON condition;

案例对比

假设 employees 表有 id、name 和 department_id 三个字段，departments 表有 id 和 department_name 两个字段。如果我们想要查找所有员工的名字和他们所在的部门名称，即使有些员工没有分配到任何部门，我们可以使用左外连接和右外连接两种方式：

左外连接案例

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

右外连接案例

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

这两个查询都会返回相同的结果，因为它们都是基于相同的连接条件连接两个表。然而，左外连接和右外连接的使用场景不同，左外连接适用于左侧表的数据更全面的情况，而右外连接适用于右侧表的数据更全面的情况。

自连接

自连接查询

自连接查询是将同一张表作为两个不同的表进行连接查询的过程。在这种情况下，通常需要为表创建别名以区分两个表。语法结构如下：

SELECT fields
FROM table1 AS alias1
JOIN table1 AS alias2
ON condition;

联合查询

联合查询是将多个查询结果合并成一个结果集的查询。它允许您将来自不同表或同一表的不同查询结果的数据组合在一起。语法结构如下：

SELECT fields
FROM table1
UNION [ALL]
SELECT fields
FROM table2;

这里的 UNION [ALL] 关键字用于合并查询结果。如果不带 ALL，则结果集中不会出现重复的行。

案例对比

假设我们有 employees 表和 departments 表，我们想要查找所有员工的名字和他们的部门名称，即使有些员工没有分配到任何部门。我们可以使用自连接查询和联合查询两种方式：

自连接查询案例

SELECT e1.name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

联合查询案例

SELECT name, department_name
FROM employees
UNION
SELECT name, department_name
FROM departments;

这两个查询都会返回相同的结果，因为它们都是基于相同的连接条件连接两个表。然而，自连接查询适用于同一张表的连接查询，而联合查询适用于不同表的查询结果的合并。

子查询

标量子查询

标量子查询返回单个值，如数字、字符串或日期。它可以作为查询语句中的一个常量使用。语法结构如下：

SELECT field1, (SELECT field2 FROM table2 WHERE condition) AS subfield
FROM table1;

例如，假设我们有一个 employees 表和一个 departments 表，我们想要查找每个部门有多少名员工。我们可以这样写：

SELECT department_name, (SELECT COUNT(*) FROM employees WHERE department_id = departments.id) AS number_of_employees
FROM departments;

列子查询

列子查询返回一列数据，即多行单列或多列。语法结构如下：

SELECT field1, (SELECT field2 FROM table2 WHERE condition) AS subfield
FROM table1;

例如，假设我们有一个 employees 表和一个 departments 表，我们想要查找每个部门有多少名男性员工。我们可以这样写：

SELECT department_name, (SELECT COUNT(*) FROM employees WHERE gender = '男' AND department_id = departments.id) AS number_of_male_employees
FROM departments;

行子查询

行子查询返回一行或多行数据，即多列单行或多行。语法结构如下：

SELECT field1, (SELECT field2 FROM table2 WHERE condition) AS subfield
FROM table1;

例如，假设我们有一个 employees 表和一个 departments 表，我们想要查找每个部门的第一名员工的信息。我们可以这样写：

SELECT department_name, (SELECT * FROM employees WHERE department_id = departments.id LIMIT 1) AS first_employee
FROM departments;

表子查询

表子查询返回多行多列数据，即整个表。语法结构如下：

SELECT field1, (SELECT * FROM table2 WHERE condition) AS subtable
FROM table1;

例如，假设我们有一个 employees 表和一个 departments 表，我们想要查找所有部门的所有员工信息。我们可以这样写：

SELECT department_name, (SELECT * FROM employees WHERE department_id = departments.id) AS employees
FROM departments;