如何在mysql中开发课程表管理_mysql课程表管理项目实战

-- 学生表
CREATE TABLE students (
    student_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
<p>-- 教师表
CREATE TABLE teachers (
teacher_id VARCHAR(10) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
title VARCHAR(20)
);</p><p>-- 课程表
CREATE TABLE courses (
course_id VARCHAR(10) PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
credits INT
);</p><p>-- 教室表
CREATE TABLE classrooms (
room_id VARCHAR(10) PRIMARY KEY,
location VARCHAR(50),
capacity INT
);</p><p>-- 课程安排表（核心表）
CREATE TABLE schedule (
id INT AUTO_INCREMENT PRIMARY KEY,
course_id VARCHAR(10),
teacher_id VARCHAR(10),
room_id VARCHAR(10),
class_name VARCHAR(20), -- 如：计算机2101
week_day TINYINT,       -- 1=周一, 7=周日
period TINYINT,         -- 第几节课
week_type ENUM('单周', '双周', '每周'), -- 周次类型
FOREIGN KEY (course_id) REFERENCES courses(course_id),
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id),
FOREIGN KEY (room_id) REFERENCES classrooms(room_id)
);</p>

INSERT INTO teachers VALUES ('T001', '张老师', '副教授');
INSERT INTO courses VALUES ('C001', '数据库原理', 3);
INSERT INTO classrooms VALUES ('R101', '第一教学楼101', 60);
<p>INSERT INTO schedule (course_id, teacher_id, room_id, class_name, week_day, period, week_type)
VALUES ('C001', 'T001', 'R101', '计算机2101', 2, 3, '每周');</p>

SELECT 
    c.course_name AS 课程,
    t.name AS 教师,
    s.week_day AS 星期,
    s.period AS 节次,
    r.location AS 教室,
    s.week_type AS 周次
FROM schedule s
JOIN courses c ON s.course_id = c.course_id
JOIN teachers t ON s.teacher_id = t.teacher_id
JOIN classrooms r ON s.room_id = r.room_id
WHERE s.class_name = '计算机2101'
ORDER BY s.week_day, s.period;

SELECT 
    c.course_name, 
    s.class_name, 
    s.week_day, 
    s.period 
FROM schedule s
JOIN courses c ON s.course_id = c.course_id
WHERE s.teacher_id = 'T001';

SELECT * FROM schedule 
WHERE room_id = 'R101' 
  AND week_day = 2 
  AND period = 3 
  AND week_type IN ('每周', '单周'); -- 需结合当前周判断

CREATE INDEX idx_schedule_time_room ON schedule(week_day, period, room_id);