⑧【MySQL】数据库查询：内连接、外连接、自连接、子查询、多表查询

MySQL 是一种广泛使用的关系型数据库管理系统，具有强大的数据操作能力。在进行数据库查询时，内连接、外连接、自连接、子查询和多表查询是几个常用的查询方式。本文将逐一介绍这些查询方法，并通过代码示例进行说明。

1. 内连接（INNER JOIN）

内连接是最常用的连接查询方式，它只会返回两个表中联结字段匹配的记录。假设我们有两个表：employees（员工表）和 departments（部门表）。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- 示例数据
INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 1);

INSERT INTO departments (id, department_name) VALUES
(1, 'HR'),
(2, 'Engineering');

-- 内连接查询
SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

输出结果：

name    | department_name
--------|------------------
Alice   | HR
Charlie | HR
Bob     | Engineering

2. 外连接（OUTER JOIN）

外连接分为左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN）。外连接会返回一个表中所有记录，即使在另一个表中没有匹配记录也会返回。

左外连接示例：

-- 左外连接查询
SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

输出结果：

name    | department_name
--------|------------------
Alice   | HR
Bob     | Engineering
Charlie | HR

右外连接示例：

-- 右外连接查询
SELECT e.name, d.department_name 
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id;

输出结果：

name    | department_name
--------|------------------
Alice   | HR
Bob     | Engineering
NULL    | NULL

（如果departments表中有没有对应employees表的数据，employees的相关字段会返回NULL。）

3. 自连接（SELF JOIN）

自连接是指在同一个表中进行连接查询。假设 employees 表中还包含一个字段 manager_id 表示员工的上级，这样可以用自连接查询员工及其上级的名字。

ALTER TABLE employees ADD manager_id INT;

UPDATE employees SET manager_id = 1 WHERE id = 2; -- Bob 的上级是 Alice
UPDATE employees SET manager_id = 1 WHERE id = 3; -- Charlie 的上级是 Alice

-- 自连接查询
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

输出结果：

Employee | Manager
---------|---------
Alice    | NULL
Bob      | Alice
Charlie  | Alice

4. 子查询（SUBQUERY）

子查询是嵌套在另一个查询中的查询。它可以在 SELECT、FROM 和 WHERE 子句中使用。以下是一个简单的例子，查询所有员工中处于HR部门的员工：

SELECT name 
FROM employees 
WHERE department_id = (SELECT id FROM departments WHERE department_name = 'HR');

5. 多表查询

多表查询是指在一个查询中结合多个表的数据，通常使用连接操作。我们已经在内连接和外连接中展示了多表查询。还有一种常见的方式是使用联合（UNION），将两个或多个查询的结果进行合并。

SELECT name FROM employees
UNION
SELECT department_name FROM departments;

小结

通过本文的介绍，我们对 MySQL 的几种常见查询方式进行了详细的阐述。从内连接到外连接，自连接到子查询，最后到多表查询，都是数据库操作中不可或缺的工具。掌握这些基本查询方式对于进行高效的数据处理和分析至关重要。在实际应用中，选择合适的查询可以提高数据库的性能和可读性。