1.

2.

– ============================================================
– 学生成绩管理数据库
– 版本: 1.0
– 创建时间: 2026-04-22
– 功能: 学生成绩管理、班级/年级排名、学科分析、各类统计
– ============================================================

– 建议在 MySQL 8.0+ 下运行（使用了 Window Functions / CTE）

---

– 1. 创建数据库

---

CREATE DATABASE IF NOT EXISTS student_grade_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

USE student_grade_db;

– ============================================================
– 2. 基础表结构
– ============================================================

---

– 2.1 年级表

---

CREATE TABLE IF NOT EXISTS grade (
grade_id INT NOT NULL AUTO_INCREMENT COMMENT ‘年级ID’,
grade_name VARCHAR(20) NOT NULL COMMENT ‘年级名称，如：高一、高二、初三’,
grade_year YEAR NOT NULL COMMENT ‘入学年份’,
remark VARCHAR(100) DEFAULT NULL COMMENT ‘备注’,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (grade_id),
UNIQUE KEY uq_grade_name_year (grade_name, grade_year)
) ENGINE=InnoDB COMMENT=‘年级表’;

---

– 2.2 班级表

---

CREATE TABLE IF NOT EXISTS class (
class_id INT NOT NULL AUTO_INCREMENT COMMENT ‘班级ID’,
grade_id INT NOT NULL COMMENT ‘所属年级ID’,
class_name VARCHAR(30) NOT NULL COMMENT ‘班级名称，如：1班、2班’,
teacher VARCHAR(30) DEFAULT NULL COMMENT ‘班主任姓名’,
capacity TINYINT DEFAULT 45 COMMENT ‘班级人数上限’,
remark VARCHAR(100) DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (class_id),
KEY idx_class_grade (grade_id),
CONSTRAINT fk_class_grade FOREIGN KEY (grade_id) REFERENCES grade (grade_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT=‘班级表’;

---

– 2.3 学生表

---

CREATE TABLE IF NOT EXISTS student (
student_id INT NOT NULL AUTO_INCREMENT COMMENT ‘学生ID’,
student_no VARCHAR(20) NOT NULL COMMENT ‘学号（唯一）’,
real_name VARCHAR(30) NOT NULL COMMENT ‘姓名’,
gender TINYINT(1) NOT NULL DEFAULT 1 COMMENT ‘性别: 1=男 0=女’,
birth_date DATE DEFAULT NULL COMMENT ‘出生日期’,
class_id INT NOT NULL COMMENT ‘所在班级ID’,
enroll_date DATE NOT NULL COMMENT ‘入学日期’,
status TINYINT(1) NOT NULL DEFAULT 1 COMMENT ‘状态: 1=在读 0=离校’,
remark VARCHAR(200) DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (student_id),
UNIQUE KEY uq_student_no (student_no),
KEY idx_student_class (class_id),
CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class (class_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT=‘学生表’;

---

– 2.4 学科表

---

CREATE TABLE IF NOT EXISTS subject (
subject_id INT NOT NULL AUTO_INCREMENT COMMENT ‘学科ID’,
subject_name VARCHAR(30) NOT NULL COMMENT ‘学科名称，如：语文、数学、英语’,
subject_code VARCHAR(10) NOT NULL COMMENT ‘学科编码’,
full_score DECIMAL(6,2) NOT NULL DEFAULT 150.00 COMMENT ‘满分值’,
pass_score DECIMAL(6,2) NOT NULL DEFAULT 90.00 COMMENT ‘及格分’,
category VARCHAR(20) DEFAULT NULL COMMENT ‘学科类别: 主科/副科/选修’,
remark VARCHAR(100) DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (subject_id),
UNIQUE KEY uq_subject_code (subject_code)
) ENGINE=InnoDB COMMENT=‘学科表’;

---

– 2.5 考试/学期表

---

CREATE TABLE IF NOT EXISTS exam (
exam_id INT NOT NULL AUTO_INCREMENT COMMENT ‘考试ID’,
exam_name VARCHAR(50) NOT NULL COMMENT ‘考试名称，如：2025学年第一学期期末’,
exam_type VARCHAR(20) NOT NULL COMMENT ‘考试类型: 期末/期中/月考/模拟’,
semester VARCHAR(20) NOT NULL COMMENT ‘学期，如：2024-2025-1’,
exam_date DATE NOT NULL COMMENT ‘考试日期’,
grade_id INT NOT NULL COMMENT ‘参与年级’,
remark VARCHAR(200) DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (exam_id),
KEY idx_exam_grade (grade_id),
KEY idx_exam_semester (semester),
CONSTRAINT fk_exam_grade FOREIGN KEY (grade_id) REFERENCES grade (grade_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT=‘考试/学期表’;

---

– 2.6 成绩表（核心表）

---

CREATE TABLE IF NOT EXISTS score (
score_id BIGINT NOT NULL AUTO_INCREMENT COMMENT ‘成绩ID’,
student_id INT NOT NULL COMMENT ‘学生ID’,
exam_id INT NOT NULL COMMENT ‘考试ID’,
subject_id INT NOT NULL COMMENT ‘学科ID’,
score DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT ‘得分’,
is_absent TINYINT(1) NOT NULL DEFAULT 0 COMMENT ‘是否缺考: 1=缺考’,
is_invalid TINYINT(1) NOT NULL DEFAULT 0 COMMENT ‘是否违纪: 1=成绩无效’,
remark VARCHAR(100) DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (score_id),
UNIQUE KEY uq_score_record (student_id, exam_id, subject_id),
KEY idx_score_exam (exam_id),
KEY idx_score_student (student_id),
KEY idx_score_subject (subject_id),
CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student (student_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_score_exam FOREIGN KEY (exam_id) REFERENCES exam (exam_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_score_subject FOREIGN KEY (subject_id) REFERENCES subject (subject_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT=‘成绩表’;

– ============================================================
– 3. 视图（Views）
– ============================================================

---

– 3.1 学生完整信息视图

---

CREATE OR REPLACE VIEW v_student_info AS
SELECT
s.student_id,
s.student_no,
s.real_name,
IF(s.gender = 1, ‘男’, ‘女’) AS gender,
s.birth_date,
c.class_id,
c.class_name,
g.grade_id,
g.grade_name,
g.grade_year,
IF(s.status = 1, ‘在读’, ‘离校’) AS status
FROM student s
JOIN class c ON s.class_id = c.class_id
JOIN grade g ON c.grade_id = g.grade_id;

---

– 3.2 单科成绩汇总视图（含学生、考试、学科信息）

---

CREATE OR REPLACE VIEW v_score_detail AS
SELECT
sc.score_id,
sc.exam_id,
e.exam_name,
e.exam_type,
e.semester,
e.exam_date,
sc.student_id,
st.student_no,
st.real_name,
c.class_id,
c.class_name,
g.grade_id,
g.grade_name,
sc.subject_id,
sub.subject_name,
sub.subject_code,
sub.full_score,
sub.pass_score,
sc.score,
ROUND(sc.score / sub.full_score * 100, 2) AS score_pct,
CASE
WHEN sc.is_absent = 1 THEN ‘缺考’
WHEN sc.is_invalid = 1 THEN ‘无效’
WHEN sc.score >= sub.pass_score THEN ‘及格’
ELSE ‘不及格’
END AS score_status
FROM score sc
JOIN student st ON sc.student_id = st.student_id
JOIN class c ON st.class_id = c.class_id
JOIN grade g ON c.grade_id = g.grade_id
JOIN exam e ON sc.exam_id = e.exam_id
JOIN subject sub ON sc.subject_id = sub.subject_id;

---

– 3.3 学生总分视图（每次考试的各科总分）

---

CREATE OR REPLACE VIEW v_student_total_score AS
SELECT
sc.exam_id,
e.exam_name,
e.semester,
sc.student_id,
st.student_no,
st.real_name,
c.class_id,
c.class_name,
g.grade_id,
g.grade_name,
SUM(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score ELSE 0 END) AS total_score,
COUNT(DISTINCT sc.subject_id) AS subject_count,
ROUND(AVG(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score END), 2) AS avg_score,
MAX(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score END) AS max_single_score,
MIN(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score END) AS min_single_score
FROM score sc
JOIN student st ON sc.student_id = st.student_id
JOIN class c ON st.class_id = c.class_id
JOIN grade g ON c.grade_id = g.grade_id
JOIN exam e ON sc.exam_id = e.exam_id
WHERE sc.is_absent = 0 AND sc.is_invalid = 0
GROUP BY sc.exam_id, sc.student_id;

---

– 3.4 班级单科成绩统计视图

---

CREATE OR REPLACE VIEW v_class_subject_stat AS
SELECT
sd.exam_id,
sd.exam_name,
sd.semester,
sd.class_id,
sd.class_name,
sd.grade_id,
sd.grade_name,
sd.subject_id,
sd.subject_name,
COUNT(sd.score_id) AS total_students,
ROUND(AVG(sd.score), 2) AS avg_score,
MAX(sd.score) AS max_score,
MIN(sd.score) AS min_score,
ROUND(STDDEV(sd.score), 2) AS std_dev,
SUM(CASE WHEN sd.score >= sd.pass_score THEN 1 ELSE 0 END) AS pass_count,
ROUND(SUM(CASE WHEN sd.score >= sd.pass_score THEN 1 ELSE 0 END)
/ COUNT(sd.score_id) * 100, 2) AS pass_rate,
SUM(CASE WHEN sd.score >= sd.full_score * 0.9 THEN 1 ELSE 0 END) AS excellent_count,
ROUND(SUM(CASE WHEN sd.score >= sd.full_score * 0.9 THEN 1 ELSE 0 END)
/ COUNT(sd.score_id) * 100, 2) AS excellent_rate
FROM v_score_detail sd
WHERE sd.score_status NOT IN (‘缺考’,‘无效’)
GROUP BY sd.exam_id, sd.class_id, sd.subject_id;

---

– 3.5 年级单科成绩统计视图

---

CREATE OR REPLACE VIEW v_grade_subject_stat AS
SELECT
sd.exam_id,
sd.exam_name,
sd.semester,
sd.grade_id,
sd.grade_name,
sd.subject_id,
sd.subject_name,
COUNT(sd.score_id) AS total_students,
ROUND(AVG(sd.score), 2) AS avg_score,
MAX(sd.score) AS max_score,
MIN(sd.score) AS min_score,
ROUND(STDDEV(sd.score), 2) AS std_dev,
SUM(CASE WHEN sd.score >= sd.pass_score THEN 1 ELSE 0 END) AS pass_count,
ROUND(SUM(CASE WHEN sd.score >= sd.pass_score THEN 1 ELSE 0 END)
/ COUNT(sd.score_id) * 100, 2) AS pass_rate,
SUM(CASE WHEN sd.score >= sd.full_score * 0.9 THEN 1 ELSE 0 END) AS excellent_count,
ROUND(SUM(CASE WHEN sd.score >= sd.full_score * 0.9 THEN 1 ELSE 0 END)
/ COUNT(sd.score_id) * 100, 2) AS excellent_rate
FROM v_score_detail sd
WHERE sd.score_status NOT IN (‘缺考’,‘无效’)
GROUP BY sd.exam_id, sd.grade_id, sd.subject_id;

– ============================================================
– 4. 排名视图（使用 Window Functions，需 MySQL 8.0+）
– ============================================================

---

– 4.1 班级内总分排名

---

CREATE OR REPLACE VIEW v_rank_class_total AS
SELECT
vt.*,
RANK() OVER (PARTITION BY vt.exam_id, vt.class_id ORDER BY vt.total_score DESC) AS class_rank,
DENSE_RANK() OVER (PARTITION BY vt.exam_id, vt.class_id ORDER BY vt.total_score DESC) AS class_dense_rank,
PERCENT_RANK()OVER (PARTITION BY vt.exam_id, vt.class_id ORDER BY vt.total_score DESC) AS class_percent_rank
FROM v_student_total_score vt;

---

– 4.2 年级内总分排名

---

CREATE OR REPLACE VIEW v_rank_grade_total AS
SELECT
vt.*,
RANK() OVER (PARTITION BY vt.exam_id, vt.grade_id ORDER BY vt.total_score DESC) AS grade_rank,
DENSE_RANK() OVER (PARTITION BY vt.exam_id, vt.grade_id ORDER BY vt.total_score DESC) AS grade_dense_rank,
PERCENT_RANK()OVER (PARTITION BY vt.exam_id, vt.grade_id ORDER BY vt.total_score DESC) AS grade_percent_rank,
NTILE(4) OVER (PARTITION BY vt.exam_id, vt.grade_id ORDER BY vt.total_score DESC) AS grade_quartile
FROM v_student_total_score vt;

---

– 4.3 班级内单科排名

---

CREATE OR REPLACE VIEW v_rank_class_subject AS
SELECT
sd.*,
RANK() OVER (PARTITION BY sd.exam_id, sd.class_id, sd.subject_id ORDER BY sd.score DESC) AS class_subject_rank,
DENSE_RANK() OVER (PARTITION BY sd.exam_id, sd.class_id, sd.subject_id ORDER BY sd.score DESC) AS class_subject_dense_rank
FROM v_score_detail sd
WHERE sd.score_status NOT IN (‘缺考’,‘无效’);

---

– 4.4 年级内单科排名

---

CREATE OR REPLACE VIEW v_rank_grade_subject AS
SELECT
sd.*,
RANK() OVER (PARTITION BY sd.exam_id, sd.grade_id, sd.subject_id ORDER BY sd.score DESC) AS grade_subject_rank,
DENSE_RANK() OVER (PARTITION BY sd.exam_id, sd.grade_id, sd.subject_id ORDER BY sd.score DESC) AS grade_subject_dense_rank,
NTILE(4) OVER (PARTITION BY sd.exam_id, sd.grade_id, sd.subject_id ORDER BY sd.score DESC) AS grade_subject_quartile
FROM v_score_detail sd
WHERE sd.score_status NOT IN (‘缺考’,‘无效’);

---

– 4.5 班级总分排行榜（班级间横向对比）

---

CREATE OR REPLACE VIEW v_rank_class_comparison AS
SELECT
sc.exam_id,
e.exam_name,
e.semester,
c.class_id,
c.class_name,
g.grade_id,
g.grade_name,
COUNT(DISTINCT sc.student_id) AS student_count,
ROUND(SUM(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score ELSE 0 END)
/ COUNT(DISTINCT sc.student_id), 2) AS class_avg_total,
ROUND(AVG(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score END), 2) AS class_avg_single,
MAX(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score END) AS class_max,
MIN(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score END) AS class_min,
RANK() OVER (PARTITION BY e.exam_id, g.grade_id
ORDER BY SUM(CASE WHEN sc.is_absent=0 AND sc.is_invalid=0 THEN sc.score ELSE 0 END)
/ COUNT(DISTINCT sc.student_id) DESC) AS class_avg_rank
FROM score sc
JOIN student st ON sc.student_id = st.student_id
JOIN class c ON st.class_id = c.class_id
JOIN grade g ON c.grade_id = g.grade_id
JOIN exam e ON sc.exam_id = e.exam_id
GROUP BY sc.exam_id, c.class_id;

– ============================================================
– 5. 存储过程（Stored Procedures）
– ============================================================

DELIMITER $$

---

– 5.1 查询某次考试某班级的总分排名
– 参数: p_exam_id 考试ID, p_class_id 班级ID

---

CREATE PROCEDURE IF NOT EXISTS sp_class_total_rank(
IN p_exam_id INT,
IN p_class_id INT
)
BEGIN
SELECT
t.student_no,
t.real_name,
t.class_name,
t.total_score,
t.avg_score,
t.subject_count,
t.class_rank,
t.class_dense_rank,
CONCAT(ROUND(t.class_percent_rank * 100, 1), ‘%’) AS beat_percent
FROM v_rank_class_total t
WHERE t.exam_id = p_exam_id
AND t.class_id = p_class_id
ORDER BY t.class_rank;
END$$

---

– 5.2 查询某次考试年级总分排名 TOP N
– 参数: p_exam_id, p_grade_id, p_top_n

---

CREATE PROCEDURE IF NOT EXISTS sp_grade_total_rank(
IN p_exam_id INT,
IN p_grade_id INT,
IN p_top_n INT
)
BEGIN
SELECT
t.grade_rank,
t.student_no,
t.real_name,
t.class_name,
t.total_score,
t.avg_score,
t.grade_quartile,
CASE t.grade_quartile
WHEN 1 THEN ‘优秀（前25%）’
WHEN 2 THEN ‘良好（25%-50%）’
WHEN 3 THEN ‘中等（50%-75%）’
WHEN 4 THEN ‘待提高（后25%）’
END AS quartile_label
FROM v_rank_grade_total t
WHERE t.exam_id = p_exam_id
AND t.grade_id = p_grade_id
ORDER BY t.grade_rank
LIMIT p_top_n;
END$$

---

– 5.3 查询某学生的成绩单（含班级/年级排名）
– 参数: p_student_id, p_exam_id

---

CREATE PROCEDURE IF NOT EXISTS sp_student_report(
IN p_student_id INT,
IN p_exam_id INT
)
BEGIN
– 各科详情及排名
SELECT
rs.subject_name,
rs.score,
rs.full_score,
CONCAT(rs.score_pct, ‘%’) AS score_pct,
rs.score_status,
rs.class_subject_rank AS 班级名次,
rs.grade_subject_rank AS 年级名次
FROM v_rank_grade_subject rs
WHERE rs.student_id = p_student_id
AND rs.exam_id = p_exam_id
ORDER BY rs.subject_name;

-- 总分及排名
SELECT
    rt.total_score,
    rt.avg_score,
    rt.class_rank   AS 班级总分名次,
    rt.grade_rank   AS 年级总分名次,
    CONCAT(ROUND((1 - rt.grade_percent_rank) * 100, 1), '%') AS 年级超越比例
FROM v_rank_grade_total rt
WHERE rt.student_id = p_student_id
  AND rt.exam_id    = p_exam_id;

END$$

---

– 5.4 学科分数分段统计
– 参数: p_exam_id, p_subject_id, p_scope (‘grade’|‘class’), p_scope_id

---

CREATE PROCEDURE IF NOT EXISTS sp_subject_score_distribution(
IN p_exam_id INT,
IN p_subject_id INT,
IN p_scope VARCHAR(10), – ‘grade’ 或 ‘class’
IN p_scope_id INT
)
BEGIN
SELECT
CASE
WHEN sc.score >= sub.full_score * 0.95 THEN ‘满分段(≥95%)’
WHEN sc.score >= sub.full_score * 0.85 THEN ‘优秀(85%-95%)’
WHEN sc.score >= sub.full_score * 0.75 THEN ‘良好(75%-85%)’
WHEN sc.score >= sub.full_score * 0.60 THEN ‘及格(60%-75%)’
WHEN sc.score >= sub.full_score * 0.45 THEN ‘偏低(45%-60%)’
ELSE ‘较差(<45%)’
END AS score_range,
COUNT() AS student_count,
ROUND(COUNT() / SUM(COUNT(*)) OVER () * 100, 2) AS percentage,
MIN(sc.score) AS range_min,
MAX(sc.score) AS range_max,
ROUND(AVG(sc.score), 2) AS range_avg
FROM score sc
JOIN student st ON sc.student_id = st.student_id
JOIN class c ON st.class_id = c.class_id
JOIN subject sub ON sc.subject_id = sub.subject_id
WHERE sc.exam_id = p_exam_id
AND sc.subject_id = p_subject_id
AND sc.is_absent = 0
AND sc.is_invalid = 0
AND (
(p_scope = ‘grade’ AND c.grade_id = p_scope_id) OR
(p_scope = ‘class’ AND c.class_id = p_scope_id)
)
GROUP BY score_range
ORDER BY range_min DESC;
END$$

---

– 5.5 学生多次考试成绩趋势（成绩进退步分析）
– 参数: p_student_id, p_subject_id (0=总分)

---

CREATE PROCEDURE IF NOT EXISTS sp_student_score_trend(
IN p_student_id INT,
IN p_subject_id INT
)
BEGIN
IF p_subject_id = 0 THEN
– 总分趋势
SELECT
e.exam_date,
e.exam_name,
e.exam_type,
vt.total_score AS score,
vt.grade_rank,
LAG(vt.total_score) OVER (ORDER BY e.exam_date) AS prev_score,
vt.total_score - LAG(vt.total_score) OVER (ORDER BY e.exam_date) AS score_change,
LAG(vt.grade_rank) OVER (ORDER BY e.exam_date) AS prev_rank,
vt.grade_rank - LAG(vt.grade_rank) OVER (ORDER BY e.exam_date) AS rank_change
FROM v_rank_grade_total vt
JOIN exam e ON vt.exam_id = e.exam_id
WHERE vt.student_id = p_student_id
ORDER BY e.exam_date;
ELSE
– 单科趋势
SELECT
e.exam_date,
e.exam_name,
e.exam_type,
sub.subject_name,
sc.score,
vrs.grade_subject_rank AS grade_rank,
LAG(sc.score) OVER (ORDER BY e.exam_date) AS prev_score,
sc.score - LAG(sc.score) OVER (ORDER BY e.exam_date) AS score_change
FROM score sc
JOIN exam e ON sc.exam_id = e.exam_id
JOIN subject sub ON sc.subject_id = sub.subject_id
JOIN v_rank_grade_subject vrs
ON vrs.score_id = sc.score_id
WHERE sc.student_id = p_student_id
AND sc.subject_id = p_subject_id
AND sc.is_absent = 0
AND sc.is_invalid = 0
ORDER BY e.exam_date;
END IF;
END$$

---

– 5.6 班级与年级平均分对比
– 参数: p_exam_id, p_grade_id

---

CREATE PROCEDURE IF NOT EXISTS sp_class_vs_grade_avg(
IN p_exam_id INT,
IN p_grade_id INT
)
BEGIN
SELECT
vcs.class_name,
vcs.subject_name,
vcs.avg_score AS class_avg,
vgs.avg_score AS grade_avg,
ROUND(vcs.avg_score - vgs.avg_score, 2) AS diff_from_grade,
CASE
WHEN vcs.avg_score > vgs.avg_score THEN ‘高于年级’
WHEN vcs.avg_score < vgs.avg_score THEN ‘低于年级’
ELSE ‘与年级持平’
END AS compare_result,
vcs.pass_rate,
vcs.excellent_rate
FROM v_class_subject_stat vcs
JOIN v_grade_subject_stat vgs
ON vcs.exam_id = vgs.exam_id AND vcs.subject_id = vgs.subject_id AND vcs.grade_id = vgs.grade_id
WHERE vcs.exam_id = p_exam_id
AND vcs.grade_id = p_grade_id
ORDER BY vcs.class_name, vcs.subject_name;
END$$

DELIMITER ;

– ============================================================
– 6. 示例数据（演示用）
– ============================================================

– 年级
INSERT INTO grade (grade_name, grade_year) VALUES
(‘高一’, 2024),
(‘高二’, 2023),
(‘高三’, 2022);

– 班级
INSERT INTO class (grade_id, class_name, teacher, capacity) VALUES
(1, ‘1班’, ‘张老师’, 45),
(1, ‘2班’, ‘李老师’, 45),
(1, ‘3班’, ‘王老师’, 45),
(2, ‘1班’, ‘赵老师’, 45),
(2, ‘2班’, ‘钱老师’, 45),
(3, ‘1班’, ‘孙老师’, 45),
(3, ‘2班’, ‘周老师’, 45);

– 学科
INSERT INTO subject (subject_name, subject_code, full_score, pass_score, category) VALUES
(‘语文’, ‘CHINESE’, 150, 90, ‘主科’),
(‘数学’, ‘MATH’, 150, 90, ‘主科’),
(‘英语’, ‘ENGLISH’, 150, 90, ‘主科’),
(‘物理’, ‘PHYSICS’, 110, 66, ‘主科’),
(‘化学’, ‘CHEM’, 100, 60, ‘主科’),
(‘生物’, ‘BIOLOGY’, 100, 60, ‘副科’),
(‘历史’, ‘HISTORY’, 100, 60, ‘副科’),
(‘地理’, ‘GEOGRAPHY’,100, 60, ‘副科’),
(‘政治’, ‘POLITICS’, 100, 60, ‘副科’);

– 考试
INSERT INTO exam (exam_name, exam_type, semester, exam_date, grade_id) VALUES
(‘2024学年第一学期期中考试’, ‘期中’, ‘2024-2025-1’, ‘2024-11-15’, 1),
(‘2024学年第一学期期末考试’, ‘期末’, ‘2024-2025-1’, ‘2025-01-20’, 1),
(‘2024学年第二学期期中考试’, ‘期中’, ‘2024-2025-2’, ‘2025-04-15’, 1);

– 学生（高一1班示例5人）
INSERT INTO student (student_no, real_name, gender, birth_date, class_id, enroll_date) VALUES
(‘20240101’, ‘张三’, 1, ‘2008-03-15’, 1, ‘2024-09-01’),
(‘20240102’, ‘李四’, 1, ‘2008-07-22’, 1, ‘2024-09-01’),
(‘20240103’, ‘王五’, 0, ‘2008-11-08’, 1, ‘2024-09-01’),
(‘20240104’, ‘赵六’, 0, ‘2008-05-30’, 1, ‘2024-09-01’),
(‘20240105’, ‘孙七’, 1, ‘2008-09-12’, 1, ‘2024-09-01’),
(‘20240201’, ‘陈八’, 0, ‘2008-04-01’, 2, ‘2024-09-01’),
(‘20240202’, ‘林九’, 1, ‘2008-12-20’, 2, ‘2024-09-01’);

– 成绩示例（第1次考试，部分科目）
INSERT INTO score (student_id, exam_id, subject_id, score) VALUES
– 张三
(1, 1, 1, 128.0),(1, 1, 2, 142.0),(1, 1, 3, 135.0),(1, 1, 4, 98.0),(1, 1, 5, 87.0),
– 李四
(2, 1, 1, 115.0),(2, 1, 2, 130.0),(2, 1, 3, 118.0),(2, 1, 4, 88.0),(2, 1, 5, 76.0),
– 王五
(3, 1, 1, 136.0),(3, 1, 2, 118.0),(3, 1, 3, 141.0),(3, 1, 4, 75.0),(3, 1, 5, 90.0),
– 赵六
(4, 1, 1, 105.0),(4, 1, 2, 109.0),(4, 1, 3, 122.0),(4, 1, 4, 65.0),(4, 1, 5, 58.0),
– 孙七
(5, 1, 1, 122.0),(5, 1, 2, 135.0),(5, 1, 3, 129.0),(5, 1, 4, 91.0),(5, 1, 5, 82.0),
– 陈八
(6, 1, 1, 118.0),(6, 1, 2, 125.0),(6, 1, 3, 110.0),(6, 1, 4, 80.0),(6, 1, 5, 72.0),
– 林九
(7, 1, 1, 131.0),(7, 1, 2, 138.0),(7, 1, 3, 126.0),(7, 1, 4, 95.0),(7, 1, 5, 88.0);

– ============================================================
– 7. 常用查询示例（注释形式，复制执行即可）
– ============================================================

/*
– ① 查询第1次考试、1班的总分排名
CALL sp_class_total_rank(1, 1);

– ② 查询第1次考试、高一年级总分排名前10
CALL sp_grade_total_rank(1, 1, 10);

– ③ 查询学生ID=1在第1次考试的成绩单（含各科班级/年级排名）
CALL sp_student_report(1, 1);

– ④ 查询第1次考试数学（subject_id=2）在年级的分数分布
CALL sp_subject_score_distribution(1, 2, ‘grade’, 1);

– ⑤ 查询学生ID=1的总分趋势（多次考试）
CALL sp_student_score_trend(1, 0);

– ⑥ 班级与年级平均分对比（第1次考试，高一年级）
CALL sp_class_vs_grade_avg(1, 1);

– ⑦ 直接查询年级总分排行榜视图
SELECT grade_rank, student_no, real_name, class_name, total_score, avg_score, grade_quartile
FROM v_rank_grade_total
WHERE exam_id = 1 AND grade_id = 1
ORDER BY grade_rank;

– ⑧ 单科年级排名视图
SELECT grade_subject_rank, real_name, class_name, subject_name, score, full_score, score_pct
FROM v_rank_grade_subject
WHERE exam_id = 1 AND grade_id = 1 AND subject_name = ‘数学’
ORDER BY grade_subject_rank;

– ⑨ 学科分析：高一所有班级语文平均分对比
SELECT class_name, subject_name, avg_score, pass_rate, excellent_rate, std_dev
FROM v_class_subject_stat
WHERE exam_id = 1 AND grade_id = 1 AND subject_name = ‘语文’
ORDER BY avg_score DESC;

– ⑩ 年级整体学科统计（哪科最难/最容易）
SELECT subject_name, avg_score, max_score, min_score, pass_rate, excellent_rate
FROM v_grade_subject_stat
WHERE exam_id = 1 AND grade_id = 1
ORDER BY avg_score DESC;
*/

– ============================================================
– 完成！数据库结构说明：
– 基础表: grade / class / student / subject / exam / score
– 视图: v_student_info / v_score_detail / v_student_total_score
– v_class_subject_stat / v_grade_subject_stat
– v_rank_class_total / v_rank_grade_total
– v_rank_class_subject / v_rank_grade_subject
– v_rank_class_comparison
– 存储过程: sp_class_total_rank / sp_grade_total_rank
– sp_student_report / sp_subject_score_distribution
– sp_student_score_trend / sp_class_vs_grade_avg
– ============================================================