mysql建关联表

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

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

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)
);

-- 错误示例
INSERT INTO student_courses (student_id, course_id) VALUES (1, 99);

-- 正确示例
INSERT INTO student_courses (student_id, course_id) VALUES (1, (SELECT id FROM courses WHERE name = 'Math'));

-- 使用索引优化查询
CREATE INDEX idx_student_courses ON student_courses(student_id, course_id);

-- 分页查询示例
SELECT s.name AS student_name, c.name AS course_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
LIMIT 10 OFFSET 0;