mysql如何设计学生选课关系表

CREATE TABLE student (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  gender CHAR(1),
  class VARCHAR(20),
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE course (
  course_id INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(100) NOT NULL,
  teacher VARCHAR(50),
  credit INT,
  max_students INT DEFAULT 30
);

CREATE TABLE student_course (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  select_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  grade DECIMAL(4,1) NULL,
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id),
  UNIQUE KEY unique_enrollment (student_id, course_id)
);

INSERT INTO student_course (student_id, course_id) VALUES (1001, 201);

SELECT c.course_name, c.teacher, sc.select_time 
FROM student_course sc
JOIN course c ON sc.course_id = c.course_id
WHERE sc.student_id = 1001;

SELECT COUNT(*) FROM student_course WHERE course_id = 201;

UPDATE student_course SET grade = 87.5 WHERE student_id = 1001 AND course_id = 201;