编辑代码

CREATE DATABASE IF NOT EXISTS student_score_db;
USE student_score_db;

CREATE TABLE student (
student_id VARCHAR(20) PRIMARY KEY, 
name VARCHAR(50) NOT NULL, 
gender CHAR(1) CHECK(gender IN ('男','女')),
class VARCHAR(30) NOT NULL, 
admission_date DATE 
);

CREATE TABLE subject (
subject_id VARCHAR(10) PRIMARY KEY,
subject_name VARCHAR(50) NOT NULL, 
credit DECIMAL(3,1) NOT NULL CHECK(credit > 0), 
department VARCHAR(30) 
);

CREATE TABLE exam (
exam_id VARCHAR(20) PRIMARY KEY,
exam_type ENUM('月考', '期中', '期末') NOT NULL,
exam_date DATE NOT NULL
);

CREATE TABLE score (
student_id VARCHAR(20) NOT NULL,
subject_id VARCHAR(10) NOT NULL,
exam_id VARCHAR(20) NOT NULL,
score DECIMAL(5, 2) CHECK (score >= 0 AND score <= 100),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (subject_id) REFERENCES subject(subject_id),
FOREIGN KEY (exam_id) REFERENCES exam(exam_id),
PRIMARY KEY (student_id, subject_id, exam_id)
);

INSERT INTO student 
VALUES(5526646, '张三', '男', '1班', '2023-10-05');

INSERT INTO student 
VALUES(5568456,'李四','女','1班','2023-10-21');

UPDATE student 
SET class = '2班' 
WHERE student_id = '5568456';

DELETE FROM student 
WHERE student_id = '5568456';

SELECT * FROM student 
WHERE class = '1班';

SELECT subject_id, AVG(score) AS 平均分 
FROM score 
WHERE exam_id = '期中考试' 
GROUP BY subject_id;

SELECT subject_id, MAX(score) AS 最高分, MIN(score) AS 最低分
FROM score
WHERE exam_id = '期中考试'
GROUP BY subject_id;

SELECT curr.student_id, 
curr.subject_id,
curr.score - prev.score AS score_change
FROM score curr
JOIN score prev ON curr.student_id = prev.student_id 
AND curr.subject_id = prev.subject_id
WHERE curr.exam_id = '期末考试' 
AND prev.exam_id = '期中考试';