编辑代码

-- 创建数据库
CREATE DATABASE StudentCourseSystem;
USE StudentCourseSystem;

-- 创建学生表
CREATE TABLE Students (
    StudentID CHAR(10) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Gender CHAR(2) NOT NULL CHECK (Gender IN ('男', '女')),
    Age INT NOT NULL CHECK (Age BETWEEN 15 AND 40),
    Major VARCHAR(30) NOT NULL
);

-- 创建教师表
CREATE TABLE Teachers (
    TeacherID CHAR(10) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Gender CHAR(2) NOT NULL CHECK (Gender IN ('男', '女')),
    Title VARCHAR(20) NOT NULL
);

-- 创建课程表
CREATE TABLE Courses (
    CourseID CHAR(8) PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL,
    Credits DECIMAL(2,1) NOT NULL CHECK (Credits > 0),
    TeacherID CHAR(10),
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);

-- 创建选课表
CREATE TABLE CourseSelection (
    StudentID CHAR(10),
    CourseID CHAR(8),
    Grade DECIMAL(3,1) CHECK (Grade BETWEEN 0 AND 100),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- 插入测试数据
INSERT INTO Students VALUES 
('20230001', '张三', '男', 20, '计算机科学与技术'),
('20230002', '李四', '女', 21, '软件工程'),
('20230003', '王五', '男', 19, '数据科学与大数据技术');

INSERT INTO Teachers VALUES 
('T001', '赵教授', '男', '教授'),
('T002', '钱老师', '女', '副教授'),
('T003', '孙讲师', '男', '讲师');

INSERT INTO Courses VALUES 
('C001', '数据结构', 4.0, 'T001'),
('C002', '操作系统', 3.5, 'T002'),
('C003', 'Python程序设计', 3.0, 'T003');

INSERT INTO CourseSelection VALUES 
('20230001', 'C001', 85.5),
('20230001', 'C002', NULL),
('20230002', 'C001', 90.0),
('20230002', 'C003', 88.5),
('20230003', 'C002', 78.0);

-- 数据查询示例
-- 1. 查询所有学生信息
SELECT * FROM Students;

-- 2. 查询选修了C001课程的学生名单
SELECT S.StudentID, S.Name, CS.Grade 
FROM Students S 
JOIN CourseSelection CS ON S.StudentID = CS.StudentID 
WHERE CS.CourseID = 'C001';

-- 3. 查询每个课程的选课人数
SELECT C.CourseID, C.CourseName, COUNT(CS.StudentID) AS EnrollmentCount 
FROM Courses C 
LEFT JOIN CourseSelection CS ON C.CourseID = CS.CourseID 
GROUP BY C.CourseID, C.CourseName;

-- 4. 查询教师T001所授课程的平均成绩
SELECT C.CourseName, AVG(CS.Grade) AS AverageGrade 
FROM Courses C 
JOIN CourseSelection CS ON C.CourseID = CS.CourseID 
WHERE C.TeacherID = 'T001' 
GROUP BY C.CourseName;

-- 数据修改示例
-- 1. 更新学生成绩
UPDATE CourseSelection 
SET Grade = 89.0 
WHERE StudentID = '20230001' AND CourseID = 'C002';

-- 2. 为课程C003分配新教师
UPDATE Courses 
SET TeacherID = 'T002' 
WHERE CourseID = 'C003';

-- 数据删除示例
-- 1. 删除学生20230003的选课记录（级联删除由外键约束保证）
DELETE FROM CourseSelection 
WHERE StudentID = '20230003';

-- 2. 删除课程C003（需先删除关联的选课记录）
DELETE FROM CourseSelection 
WHERE CourseID = 'C003';
DELETE FROM Courses 
WHERE CourseID = 'C003';

-- 表结构修改示例
-- 1. 为课程表添加课程简介字段
ALTER TABLE Courses 
ADD CourseDescription VARCHAR(200);

-- 2. 修改学生表年龄字段的默认值
ALTER TABLE Students 
ALTER COLUMN Age SET DEFAULT 18;