带有关系manyToMany的表

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

-- 创建课程表
CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

-- 创建学生课程关联表
CREATE TABLE Student_Course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

-- 插入学生数据
INSERT INTO Student (student_id, name) VALUES (1, 'Alice');
INSERT INTO Student (student_id, name) VALUES (2, 'Bob');

-- 插入课程数据
INSERT INTO Course (course_id, title) VALUES (101, 'Mathematics');
INSERT INTO Course (course_id, title) VALUES (102, 'History');

-- 插入关联数据
INSERT INTO Student_Course (student_id, course_id) VALUES (1, 101);
INSERT INTO Student_Course (student_id, course_id) VALUES (1, 102);
INSERT INTO Student_Course (student_id, course_id) VALUES (2, 101);

-- 创建索引
CREATE INDEX idx_student_course ON Student_Course(student_id, course_id);

-- 查询某个学生选修的所有课程
SELECT c.title
FROM Course c
JOIN Student_Course sc ON c.course_id = sc.course_id
WHERE sc.student_id = 1;