编辑代码

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';