MySQL多表查询

多表查询是指同时从两个或多个表中检索数据，通过表之间的关联关系获取综合信息。在关系型数据库中，数据通常分散存储在多个表中，多表查询是获取关联数据的核心操作。

一、多表查询基础

1. 表关系类型

多表查询基于表之间的关系，常见关系类型包括：

一对一：一个表中的一条记录对应另一个表中的一条记录

一对多：一个表中的一条记录对应另一个表中的多条记录（最常见）

多对多：一个表中的多条记录对应另一个表中的多条记录（通常通过中间表实现）

2. 表连接的基本概念

多表查询通过"连接"（JOIN）操作实现，核心是通过共同字段（通常是主键和外键）建立表之间的关联。

示例表结构（以下示例将基于这些表）：

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 项目表
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE
);

-- 员工-项目关联表（多对多关系）
CREATE TABLE employee_projects (
    emp_id INT,
    project_id INT,
    role VARCHAR(50),
    PRIMARY KEY (emp_id, project_id),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
    FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

二、常用连接类型

1. 内连接（INNER JOIN）

作用：只返回两个表中匹配条件的记录，即两个表的交集。

语法：

SELECT 字段列表
FROM 表1
INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

示例：查询员工及其所属部门信息

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

2. 左连接（LEFT JOIN / LEFT OUTER JOIN）

作用：返回左表中的所有记录，以及右表中匹配条件的记录。如果右表中没有匹配项，则返回NULL。

语法：

SELECT 字段列表
FROM 左表
LEFT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

示例：查询所有部门及该部门的员工（包括没有员工的部门）

SELECT d.dept_id, d.dept_name, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

3. 右连接（RIGHT JOIN / RIGHT OUTER JOIN）

作用：返回右表中的所有记录，以及左表中匹配条件的记录。如果左表中没有匹配项，则返回NULL。

语法：

SELECT 字段列表
FROM 左表
RIGHT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

示例：查询所有员工及其所属部门（包括没有分配部门的员工）

SELECT e.emp_id, e.emp_name, d.dept_name
FROM departments d
RIGHT JOIN employees e ON d.dept_id = e.dept_id;

4. 全连接（FULL JOIN / FULL OUTER JOIN）

作用：返回左表和右表中的所有记录，当某一侧没有匹配项时返回NULL。

注意：MySQL不直接支持FULL JOIN，但可以通过UNION组合LEFT JOIN和RIGHT JOIN实现。

示例：查询所有部门和所有员工，包括没有匹配的记录

SELECT d.dept_id, d.dept_name, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
UNION
SELECT d.dept_id, d.dept_name, e.emp_name
FROM departments d
RIGHT JOIN employees e ON d.dept_id = e.dept_id;

5. 交叉连接（CROSS JOIN）

作用：返回两个表的笛卡尔积，即左表的每条记录与右表的每条记录组合。

注意：结果集可能非常大，使用时需谨慎。

示例：

SELECT e.emp_name, p.project_name
FROM employees e
CROSS JOIN projects p;

三、多表连接（三个及以上表）

可以通过连续的JOIN操作实现多表连接，依次建立表之间的关联。

示例：查询员工、所属部门及参与的项目信息

SELECT e.emp_id, e.emp_name, d.dept_name, p.project_name, ep.role
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN employee_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id;

四、使用别名简化查询

为表指定别名可以简化SQL语句，特别是在多表查询中。

示例：

SELECT e.emp_name, d.dept_name
FROM employees AS e  -- AS关键字可省略，直接写 e
INNER JOIN departments d ON e.dept_id = d.dept_id;

五、多表查询中的筛选与排序

多表查询可以结合WHERE、GROUP BY、HAVING和ORDER BY等子句进行数据筛选和排序。

1. 带条件的多表查询

-- 查询销售部门的员工及其工资（工资大于5000）
SELECT e.emp_name, d.dept_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = '销售部' AND e.salary > 5000;

2. 分组统计的多表查询

-- 按部门分组，统计每个部门的员工人数和平均工资
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count, AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING avg_salary > 4000
ORDER BY avg_salary DESC;

六、子查询与多表查询

子查询可以作为多表查询的补充，在一个查询中嵌套另一个查询。

示例：查询参与了"新产品开发"项目的员工信息

SELECT e.emp_id, e.emp_name
FROM employees e
WHERE e.emp_id IN (
    SELECT ep.emp_id
    FROM employee_projects ep
    INNER JOIN projects p ON ep.project_id = p.project_id
    WHERE p.project_name = '新产品开发'
);

七、多表查询的性能注意事项

1. 确保连接字段上有索引（如主键和外键），提高查询效率

2. 只选择需要的字段，避免使用SELECT *

3. 对于大型表，限制返回的记录数（使用LIMIT）

4. 复杂查询可以拆分为多个简单查询，或使用视图简化

5. 避免不必要的JOIN操作，减少参与连接的表数量