如何在mysql中设计课程安排数据库

CREATE TABLE course (
  course_id INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(100) NOT NULL,
  credits INT DEFAULT 1,
  description TEXT
);

CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE
);

CREATE TABLE student (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  class_id INT, -- 所属班级
  email VARCHAR(100) UNIQUE
);

CREATE TABLE class (
  class_id INT PRIMARY KEY AUTO_INCREMENT,
  class_name VARCHAR(50) NOT NULL -- 如“高三2班”
);

CREATE TABLE room (
  room_id INT PRIMARY KEY AUTO_INCREMENT,
  room_number VARCHAR(20) NOT NULL,
  capacity INT
);

CREATE TABLE schedule (
  schedule_id INT PRIMARY KEY AUTO_INCREMENT,
  course_id INT,
  teacher_id INT,
  room_id INT,
  class_id INT,           -- 面向的班级
  day_of_week TINYINT,    -- 1=周一, 7=周日
  period TINYINT,         -- 第几节课
  semester VARCHAR(20),   -- 如 "2024-秋季"
  FOREIGN KEY (course_id) REFERENCES course(course_id),
  FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
  FOREIGN KEY (room_id) REFERENCES room(room_id),
  FOREIGN KEY (class_id) REFERENCES class(class_id)
);

CREATE TABLE enrollment (
  student_id INT,
  course_id INT,
  semester VARCHAR(20),
  PRIMARY KEY (student_id, course_id, semester),
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

-- 查询某教师的课表（以 teacher_id = 1 为例）
SELECT c.course_name, cl.class_name, r.room_number, s.day_of_week, s.period
FROM schedule s
JOIN course c ON s.course_id = c.course_id
JOIN class cl ON s.class_id = cl.class_id
JOIN room r ON s.room_id = r.room_id
WHERE s.teacher_id = 1;

-- 查询某班级的每日课表
SELECT c.course_name, t.name AS teacher, s.period, s.day_of_week
FROM schedule s
JOIN course c ON s.course_id = c.course_id
JOIN teacher t ON s.teacher_id = t.teacher_id
WHERE s.class_id = 1
ORDER BY s.day_of_week, s.period;

-- 检查某个教室在某天某节是否被占用
SELECT * FROM schedule 
WHERE room_id = 1 AND day_of_week = 1 AND period = 3;