如何用mysql实现课程管理系统_mysql课程管理数据库搭建

CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  gender ENUM('男','女'),
  phone VARCHAR(15),
  email VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE teachers (
  teacher_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(50), -- 所属院系
  title VARCHAR(30),      -- 职称
  phone VARCHAR(15),
  email VARCHAR(100)
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(100) NOT NULL,
  credit INT DEFAULT 2,        -- 学分
  hours INT DEFAULT 32,        -- 学时
  department VARCHAR(50),      -- 开设院系
  description TEXT
);

CREATE TABLE classes (
  class_id INT PRIMARY KEY AUTO_INCREMENT,
  course_id INT,
  teacher_id INT,
  semester VARCHAR(20),        -- 如 '2024-秋季'
  classroom VARCHAR(30),
  time_slot VARCHAR(50),       -- 上课时间安排
  FOREIGN KEY (course_id) REFERENCES courses(course_id),
  FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT,
  class_id INT,
  enroll_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  status ENUM('已选','退课') DEFAULT '已选',
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (class_id) REFERENCES classes(class_id),
  UNIQUE KEY unique_enrollment (student_id, class_id) -- 防止重复选课
);

INSERT INTO courses (course_name, credit, hours, department) 
VALUES ('数据库原理', 3, 48, '计算机科学与技术');

SELECT s.name AS 学生, c.course_name AS 课程, t.name AS 教师, cl.semester AS 学期
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN classes cl ON e.class_id = cl.class_id
JOIN courses c ON cl.course_id = c.course_id
JOIN teachers t ON cl.teacher_id = t.teacher_id
WHERE s.name = '张三' AND e.status = '已选';

SELECT c.course_name, COUNT(e.student_id) AS 选课人数
FROM courses c
LEFT JOIN classes cl ON c.course_id = cl.course_id
LEFT JOIN enrollments e ON cl.class_id = e.class_id AND e.status = '已选'
GROUP BY c.course_id, c.course_name;

UPDATE enrollments SET status = '退课' WHERE student_id = 101 AND class_id = 201;