CREATE DATABASE IF NOT EXISTS StudentManageSystem DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
USE StudentManageSystem;
DROP TABLE IF EXISTS Student;
CREATE TABLE Student (
SID CHAR(10) NOT NULL COMMENT '学号(主键)',
SName VARCHAR(50) NOT NULL COMMENT '姓名',
Sex CHAR(2) NOT NULL COMMENT '性别(男/女)',
BirthDate DATE COMMENT '出生日期',
Major VARCHAR(50) COMMENT '专业',
PRIMARY KEY (SID),
CHECK (Sex IN ('男', '女'))
) ENGINE=InnoDB COMMENT '学生信息表';
DROP TABLE IF EXISTS Teacher;
CREATE TABLE Teacher (
TID CHAR(8) NOT NULL COMMENT '工号(主键)',
TName VARCHAR(50) NOT NULL COMMENT '姓名',
Sex CHAR(2) NOT NULL COMMENT '性别(男/女)',
Title VARCHAR(50) COMMENT '职称',
Department VARCHAR(50) COMMENT '所属院系',
PRIMARY KEY (TID),
CHECK (Sex IN ('男', '女'))
) ENGINE=InnoDB COMMENT '教师信息表';
DROP TABLE IF EXISTS Course;
CREATE TABLE Course (
CID CHAR(6) NOT NULL COMMENT '课程编号(主键)',
CName VARCHAR(50) NOT NULL COMMENT '课程名称',
Credit INT NOT NULL COMMENT '学分(必须大于0)',
Type VARCHAR(50) COMMENT '课程类型',
PRIMARY KEY (CID),
CHECK (Credit > 0)
) ENGINE=InnoDB COMMENT '课程信息表';
DROP TABLE IF EXISTS Teach;
CREATE TABLE Teach (
TID CHAR(8) NOT NULL COMMENT '教师工号',
CID CHAR(6) NOT NULL COMMENT '课程编号',
Semester VARCHAR(20) NOT NULL COMMENT '学期(如2025-2026学年第一学期)',
PRIMARY KEY (TID, CID, Semester),
FOREIGN KEY (TID) REFERENCES Teacher(TID) ON DELETE CASCADE,
FOREIGN KEY (CID) REFERENCES Course(CID) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT '教师授课关系表';
DROP TABLE IF EXISTS SelectCourse;
CREATE TABLE SelectCourse (
SID CHAR(10) NOT NULL COMMENT '学号',
CID CHAR(6) NOT NULL COMMENT '课程编号',
Grade DECIMAL(5,2) COMMENT '成绩(保留两位小数)',
PRIMARY KEY (SID, CID),
FOREIGN KEY (SID) REFERENCES Student(SID) ON DELETE CASCADE,
FOREIGN KEY (CID) REFERENCES Course(CID) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT '学生选课及成绩表';
INSERT INTO Student (SID, SName, Sex, BirthDate, Major)
VALUES
('S2025001', '张三', '男', '2005-03-15', '计算机科学'),
('S2025002', '李四', '女', '2005-05-20', '软件工程');
INSERT INTO Teacher (TID, TName, Sex, Title, Department)
VALUES
('T2025001', '王老师', '男', '教授', '计算机学院'),
('T2025002', '李老师', '女', '讲师', '软件学院');
INSERT INTO Course (CID, CName, Credit, Type)
VALUES
('C001', '数据库原理', 4, '专业必修课'),
('C002', 'Java编程', 3, '专业选修课');
INSERT INTO Teach (TID, CID, Semester)
VALUES ('T2025001', 'C001', '2025-2026学年第一学期');
INSERT INTO SelectCourse (SID, CID, Grade)
VALUES
('S2025001', 'C001', 85.5),
('S2025002', 'C002', 90.0);
SELECT
S.SID,
S.SName,
C.CName,
SC.Grade
FROM Student S
LEFT JOIN SelectCourse SC ON S.SID = SC.SID
LEFT JOIN Course C ON SC.CID = C.CID;
SELECT
T.TID,
T.TName,
C.CName,
Tch.Semester
FROM Teacher T
JOIN Teach Tch ON T.TID = Tch.TID
JOIN Course C ON Tch.CID = C.CID
WHERE T.TID = 'T2025001';
SELECT
C.CID,
C.CName,
AVG(SC.Grade) AS AverageGrade
FROM Course C
LEFT JOIN SelectCourse SC ON C.CID = SC.CID
GROUP BY C.CID, C.CName;
SELECT S.*
FROM Student S
LEFT JOIN SelectCourse SC ON S.SID = SC.SID
WHERE SC.SID IS NULL;
UPDATE Student
SET Major = '人工智能'
WHERE SID = 'S2025001';
UPDATE Course
SET Credit = 5
WHERE CID = 'C001';
DELETE FROM SelectCourse
WHERE SID = 'S2025001' AND CID = 'C001';