如何处理在线考试系统中的学生考试成绩数据：MySQL表结构设计技巧

CREATE TABLE students (
  student_id   INT PRIMARY KEY AUTO_INCREMENT,
  name         VARCHAR(20) NOT NULL,
  grade        INT NOT NULL,
  class        INT NOT NULL
);

CREATE TABLE exams (
  exam_id      INT PRIMARY KEY AUTO_INCREMENT,
  exam_date    DATE NOT NULL,
  subject      VARCHAR(50) NOT NULL,
  duration     INT NOT NULL
);

CREATE TABLE scores (
  student_id   INT NOT NULL,
  exam_id      INT NOT NULL,
  score        INT NOT NULL,
  PRIMARY KEY (student_id, exam_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (exam_id) REFERENCES exams(exam_id)
);

-- 向学生表中插入数据
INSERT INTO students (name, grade, class)
VALUES ('张三', 2020, 1),
       ('李四', 2020, 1),
       ('王五', 2020, 2);

-- 向考试表中插入数据
INSERT INTO exams (exam_date, subject, duration)
VALUES ('2021-01-01', '数学', 120),
       ('2021-01-02', '英语', 90);

-- 向成绩表中插入数据
INSERT INTO scores (student_id, exam_id, score)
VALUES (1, 1, 80),
       (2, 1, 85),
       (3, 1, 90),
       (1, 2, 75),
       (2, 2, 90),
       (3, 2, 95);

-- 查询学生的考试成绩
SELECT s.name, e.subject, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN exams e ON sc.exam_id = e.exam_id;