mysql 多对多关系查询

-- students 表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- courses 表
CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- student_courses 表
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)
);

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

SELECT s.name AS student_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE c.name = '数学';

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

-- 添加索引
CREATE INDEX idx_student_courses_student_id ON student_courses(student_id);
CREATE INDEX idx_student_courses_course_id ON student_courses(course_id);