mysql多对多数据会多

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

-- 创建课程表
CREATE TABLE courses (
    id INT PRIMARY KEY,
    name 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)
);

-- 添加索引
ALTER TABLE student_courses ADD INDEX idx_student_id (student_id);
ALTER TABLE student_courses ADD INDEX idx_course_id (course_id);

-- 查询某个学生选修的所有课程
SELECT c.name AS course_name
FROM courses c
JOIN student_courses sc ON c.id = sc.course_id
WHERE sc.student_id = 1;

-- 创建触发器
DELIMITER //
CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
BEGIN
    DELETE FROM student_courses WHERE student_id = OLD.id;
END //
DELIMITER ;

-- 删除学生及其选课记录
DELETE FROM students WHERE id = 1;