为多对多关系创建mysql视图

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

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_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(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO students (student_id, student_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO courses (course_id, course_name) VALUES
(101, 'Math'),
(102, 'Science'),
(103, 'History');

INSERT INTO student_courses (student_id, course_id) VALUES
(1, 101),
(1, 102),
(2, 101),
(3, 103);

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