mysql 创建关联表

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL
);

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

-- 创建关联表
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_name) VALUES ('Alice');
INSERT INTO students (student_name) VALUES ('Bob');

-- 插入课程数据
INSERT INTO courses (course_name) VALUES ('Math');
INSERT INTO courses (course_name) VALUES ('Science');

-- 插入关联数据
INSERT INTO student_courses (student_id, course_id) VALUES (1, 1); -- Alice 选修 Math
INSERT INTO student_courses (student_id, course_id) VALUES (1, 2); -- Alice 选修 Science
INSERT INTO student_courses (student_id, course_id) VALUES (2, 1); -- Bob 选修 Math

-- 查询学生及其选修的课程
SELECT s.student_name, 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;

CREATE INDEX idx_student_courses_student_id ON student_courses(student_id);
CREATE INDEX idx_student_courses_course_id ON student_courses(course_id);