数据库Mysql学习——day7（多表查询（JOIN）进阶）

1、多表连接查询（JOIN 多张表）

1.1. 基本概念

• 当需要同时查询三张或更多张表 的数据时，可以使用多次 JOIN 把表连接起来。
• 连接逻辑通常基于主键和外键之间的关联关系。

1.2. 三表连接示例：学生、课程、教师表

假设有以下三张表：

-- 学生表
CREATE TABLE student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 课程表
CREATE TABLE course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    teacher_id INT
);

-- 教师表
CREATE TABLE teacher (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR(100)
);

查询需求： 列出每个学生选的课程及任课教师姓名。
通常还需要一个中间表（选课表）：

-- 选课表
CREATE TABLE enrollment (
    enroll_id INT PRIMARY KEY,
    student_id INT,
    course_id INT
);

1.3. 多表连接查询语句

SELECT 
    student.name AS student_name,
    course.course_name,
    teacher.teacher_name
FROM enrollment
INNER JOIN student ON enrollment.student_id = student.student_id
INNER JOIN course ON enrollment.course_id = course.course_id
INNER JOIN teacher ON course.teacher_id = teacher.teacher_id;

• 解释：
• 通过 enrollment 表把学生和课程联系起来。
• 通过 course 表中的teacher_id字段进一步连接到教师表。

2、自连接（SELF JOIN）

2.1. 基本概念

• 自连接 就是同一张表 自己连接自己。
• 适合表示具有层级关系 的数据，如员工与经理、类别与子类别。

2.2. 示例：员工和经理关系

假设有一张员工表：

-- 员工表
CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    manager_id INT  -- 上级经理ID，指向本表emp_id
);

插入示例数据：

INSERT INTO employee (emp_id, emp_name, manager_id) VALUES
(1, '王总', NULL),
(2, '李经理', 1),
(3, '张主管', 2),
(4, '赵员工', 3);

2.3. 自连接查询语句

查询每位员工及其经理的名字：

SELECT 
    e1.emp_name AS employee_name,
    e2.emp_name AS manager_name
FROM employee e1
LEFT JOIN employee e2 ON e1.manager_id = e2.emp_id;

• 解释：
• e1 表示员工自己。
• e2 表示经理。
• 使用 LEFT JOIN，即使有些员工（如总经理）没有上级，结果中也能出现。

3、笛卡尔积（CROSS JOIN）

3.1. 基本概念

• CROSS JOIN ** 会返回两张表的所有组合** （即：表A的每一行都和表B的每一行匹配）。
• 通常如果不加 ON 条件，或者写错了连接条件，就会出现笛卡尔积 现象，导致数据量暴涨。

3.2. 语法示例

SELECT *
FROM 表A
CROSS JOIN 表B;

或

SELECT *
FROM 表A, 表B;

• 结果 ：行数 = 表A行数 × 表B行数。

3.3. 注意事项

• 笛卡尔积在大多数实际场景下需要避免 ，因为通常是由于漏写或写错连接条件引起的。
• 实际应用中，明确 JOIN 条件 非常重要！

4、实践任务

4.1. 创建三张表（员工表、部门表、经理表）

-- 员工表
CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    manager_id INT
);

-- 部门表
CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- 经理表（也是员工，只是额外列出）
CREATE TABLE manager (
    manager_id INT PRIMARY KEY,
    manager_name VARCHAR(100)
);

4.2. 插入数据

-- 员工
INSERT INTO employee (emp_id, emp_name, dept_id, manager_id) VALUES
(1, '张三', 10, 100),
(2, '李四', 20, 101),
(3, '王五', 10, 100);

-- 部门
INSERT INTO department (dept_id, dept_name) VALUES
(10, '技术部'),
(20, '市场部');

-- 经理
INSERT INTO manager (manager_id, manager_name) VALUES
(100, '赵总'),
(101, '钱总');

4.3. 多表联合查询：查询员工姓名、部门名称和经理姓名

SELECT 
    e.emp_name,
    d.dept_name,
    m.manager_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN manager m ON e.manager_id = m.manager_id;

4.4. 自连接案例（更简单版）

如果不用经理表，只用员工表（员工自己记录经理ID）：

SELECT 
    e1.emp_name AS employee,
    e2.emp_name AS manager
FROM employee e1
LEFT JOIN employee e2 ON e1.manager_id = e2.emp_id;

5、今日小结