数据库多表中多对多

前言

在实际业务场景中，常常会遇到多对多（Many-to-Many, M:N）关系。例如：学生和课程，一个学生可以选多门课程，一门课程也可以被多个学生选修。MySQL等关系型数据库并不直接支持多对多关系，需要通过“中间表”来实现。

一、基本原理

多对多关系通常通过三张表实现：

• 主表A（如学生表）
• 主表B（如课程表）
• 关联表（如选课表），用于存储A和B的对应关系

1. 表结构示例

假设有学生（students）、课程（courses）、选课（student_courses）三张表：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

二、多对多查询示例

1. 查询某个学生选修了哪些课程

SELECT c.*
FROM courses c
JOIN student_courses sc ON c.id = sc.course_id
JOIN students s ON s.id = sc.student_id
WHERE s.name = '张三';

2. 查询某门课程有哪些学生选修

SELECT s.*
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON c.id = sc.course_id
WHERE c.title = '数据库原理';

三、三表以上多对多关系案例

有时业务更复杂，比如“学生-课程-教师”三者之间的多对多关系。假设：

• 一个学生选多门课
• 一门课由多位教师授课
• 一个教师可以教多门课

1. 表结构设计

CREATE TABLE teachers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE course_teachers (
    course_id INT,
    teacher_id INT,
    PRIMARY KEY (course_id, teacher_id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

2. 查询某学生选修的课程及授课教师

SELECT s.name AS student_name, c.title AS course_title, t.name AS teacher_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON c.id = sc.course_id
JOIN course_teachers ct ON c.id = ct.course_id
JOIN teachers t ON ct.teacher_id = t.id
WHERE s.name = '张三';

四、四表、五表多对多查询案例

以“学生-课程-教师-教室-学期”为例，假设：

• 一门课程在不同学期、不同教室由不同教师授课
• 一个学生在不同学期选修不同课程

1. 新增表结构

CREATE TABLE classrooms (
    id INT PRIMARY KEY AUTO_INCREMENT,
    room_number VARCHAR(20) NOT NULL
);

CREATE TABLE semesters (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL
);

CREATE TABLE course_arrangements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_id INT,
    teacher_id INT,
    classroom_id INT,
    semester_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(id),
    FOREIGN KEY (classroom_id) REFERENCES classrooms(id),
    FOREIGN KEY (semester_id) REFERENCES semesters(id)
);

CREATE TABLE student_course_arrangements (
    student_id INT,
    course_arrangement_id INT,
    PRIMARY KEY (student_id, course_arrangement_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_arrangement_id) REFERENCES course_arrangements(id)
);

2. 查询某学生在某学期选修的课程、教师、教室

SELECT s.name AS student_name, sem.name AS semester, c.title AS course_title, t.name AS teacher_name, cr.room_number
FROM students s
JOIN student_course_arrangements sca ON s.id = sca.student_id
JOIN course_arrangements ca ON sca.course_arrangement_id = ca.id
JOIN courses c ON ca.course_id = c.id
JOIN teachers t ON ca.teacher_id = t.id
JOIN classrooms cr ON ca.classroom_id = cr.id
JOIN semesters sem ON ca.semester_id = sem.id
WHERE s.name = '张三' AND sem.name = '2024春季学期';

五、总结

• 多对多关系通过“中间表”实现
• 查询时通过多表JOIN实现多对多数据的关联
• 复杂业务可通过增加中间表、关联表实现多表多对多关系
• 设计时需注意主外键约束，保证数据一致性