mysql 多对多查询

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

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

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 s.name AS student_name, c.title AS course_title
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE s.id = ?;

CREATE INDEX idx_student_courses ON student_courses(student_id, course_id);

ALTER TABLE student_courses
ADD CONSTRAINT fk_student_courses_students
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE;

ALTER TABLE student_courses
ADD CONSTRAINT fk_student_courses_courses
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE;

CREATE VIEW student_course_view AS
SELECT s.name AS student_name, c.title AS course_title
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id;

SELECT * FROM student_course_view WHERE student_name = 'John Doe';