从零开始用MySQL设计教务系统：建表/存储过程/事务全流程实操

从零开始用MySQL设计教务系统：建表/存储过程/事务全流程实操

教务管理系统是高校信息化建设的核心模块之一，它涉及学生信息管理、课程安排、成绩统计等关键业务流程。对于初学者而言，如何将零散的MySQL知识点串联成实际可用的系统，是一个极具挑战性的任务。本文将带你从需求分析开始，逐步完成一个简易教务系统的数据库设计与实现，涵盖ER图设计、表结构创建、存储过程编写以及事务处理等核心环节。

1. 需求分析与ER图设计

在开始编写SQL之前，我们需要明确系统的基本功能和数据关系。一个典型的教务系统至少需要管理三类核心数据：学生信息、课程信息和成绩记录。

核心实体关系分析：

• 一个学生可以选修多门课程
• 一门课程可以被多个学生选修
• 每个学生选修每门课程会产生一条成绩记录

基于上述分析，我们可以绘制出简化的ER图：

学生(学生ID,姓名,性别) —— 选修 —— 课程(课程ID,课程名)
       \                     /
        \                   /
         \                 /
          成绩(学生ID,课程ID,分数)

这个ER图清晰地展示了三个实体之间的关系。学生和课程是多对多关系，而成绩表则作为关联表存在，记录每个学生在每门课程中的表现。

2. 数据库表结构设计与实现

根据ER图，我们需要创建三张核心表：学生表(stu)、课程表(course)和成绩表(score)。下面是具体的SQL实现：

-- 创建学生表
CREATE TABLE stu (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50) NOT NULL,
    ssex ENUM('男','女') NOT NULL,
    sclass VARCHAR(20) COMMENT '班级',
    sentry_date DATE COMMENT '入学日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建课程表
CREATE TABLE course (
    cid INT PRIMARY KEY,
    cname VARCHAR(100) NOT NULL,
    credit TINYINT UNSIGNED COMMENT '学分',
    teacher VARCHAR(50) COMMENT '授课教师'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建成绩表
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sid INT NOT NULL,
    cid INT NOT NULL,
    score DECIMAL(5,2) COMMENT '成绩',
    term VARCHAR(20) COMMENT '学期',
    FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE,
    FOREIGN KEY (cid) REFERENCES course(cid) ON DELETE RESTRICT,
    UNIQUE KEY (sid, cid, term) -- 防止同一学期重复录入
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表设计要点说明：

1. 学生表(stu)：

   • 使用自增主键sid作为学生唯一标识
   • ssex字段使用ENUM类型限制输入值
   • 添加了班级和入学日期等扩展字段

2. 课程表(course)：

   • cid作为课程编号，不使用自增以便按学校规则编号
   • 添加了学分和授课教师信息

3. 成绩表(score)：

   • 设置复合外键关联学生和课程表
   • 添加学期字段以区分不同学期的成绩
   • 建立(sid,cid,term)唯一约束防止重复录入
   • 使用ON DELETE CASCADE实现级联删除

3. 基础数据操作与查询

系统运行需要基本的增删改查功能。下面是一些常用操作的SQL示例：

-- 插入学生数据
INSERT INTO stu (sname, ssex, sclass, sentry_date) VALUES 
('张三', '男', '计算机2021', '2021-09-01'),
('李四', '女', '计算机2021', '2021-09-01');

-- 插入课程数据
INSERT INTO course (cid, cname, credit, teacher) VALUES
(1001, '数据库原理', 4, '王教授'),
(1002, '数据结构', 3, '李教授');

-- 插入成绩数据
INSERT INTO score (sid, cid, score, term) VALUES
(1, 1001, 85.5, '2021-2022-1'),
(1, 1002, 92.0, '2021-2022-1'),
(2, 1001, 78.0, '2021-2022-1');

常用查询示例：

1. 查询某学生所有课程成绩：

SELECT c.cname, s.score 
FROM score s JOIN course c ON s.cid = c.cid
WHERE s.sid = 1;

2. 查询某课程平均分：

SELECT c.cname, AVG(s.score) as avg_score
FROM score s JOIN course c ON s.cid = c.cid
WHERE s.cid = 1001;

3. 查询班级成绩排名：

SELECT st.sname, c.cname, sc.score,
       RANK() OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) as rank
FROM score sc 
JOIN stu st ON sc.sid = st.sid
JOIN course c ON sc.cid = c.cid
WHERE st.sclass = '计算机2021';

4. 存储过程实现业务逻辑

教务系统中许多业务逻辑适合用存储过程实现。下面展示几个典型示例：

4.1 成绩统计存储过程

DELIMITER //
CREATE PROCEDURE calc_course_stats(IN course_id INT, OUT avg_score DECIMAL(5,2), OUT max_score DECIMAL(5,2), OUT pass_rate DECIMAL(5,2))
BEGIN
    -- 计算平均分
    SELECT AVG(score) INTO avg_score FROM score WHERE cid = course_id;
    
    -- 计算最高分
    SELECT MAX(score) INTO max_score FROM score WHERE cid = course_id;
    
    -- 计算及格率(假设60分及格)
    SELECT COUNT(CASE WHEN score >= 60 THEN 1 END)/COUNT(*) INTO pass_rate 
    FROM score WHERE cid = course_id;
END //
DELIMITER ;

-- 调用示例
CALL calc_course_stats(1001, @avg, @max, @pass);
SELECT @avg, @max, @pass;

4.2 学生成绩单生成

DELIMITER //
CREATE PROCEDURE generate_transcript(IN student_id INT, IN semester VARCHAR(20))
BEGIN
    SELECT c.cname, s.score, c.credit,
           CASE WHEN s.score >= 90 THEN 'A'
                WHEN s.score >= 80 THEN 'B'
                WHEN s.score >= 70 THEN 'C'
                WHEN s.score >= 60 THEN 'D'
                ELSE 'F' END as grade
    FROM score s JOIN course c ON s.cid = c.cid
    WHERE s.sid = student_id AND s.term = semester;
    
    -- 计算GPA
    SELECT SUM(CASE 
                 WHEN s.score >= 90 THEN 4 * c.credit
                 WHEN s.score >= 80 THEN 3 * c.credit
                 WHEN s.score >= 70 THEN 2 * c.credit
                 WHEN s.score >= 60 THEN 1 * c.credit
                 ELSE 0 END) / SUM(c.credit) as gpa
    FROM score s JOIN course c ON s.cid = c.cid
    WHERE s.sid = student_id AND s.term = semester;
END //
DELIMITER ;

-- 调用示例
CALL generate_transcript(1, '2021-2022-1');

4.3 自动成绩录入校验

DELIMITER //
CREATE PROCEDURE add_score_with_check(
    IN student_id INT,
    IN course_id INT,
    IN score_value DECIMAL(5,2),
    IN semester VARCHAR(20)
)
BEGIN
    DECLARE exist_count INT;
    DECLARE student_exist INT;
    DECLARE course_exist INT;
    
    -- 检查学生是否存在
    SELECT COUNT(*) INTO student_exist FROM stu WHERE sid = student_id;
    IF student_exist = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '学生不存在';
    END IF;
    
    -- 检查课程是否存在
    SELECT COUNT(*) INTO course_exist FROM course WHERE cid = course_id;
    IF course_exist = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '课程不存在';
    END IF;
    
    -- 检查是否已存在记录
    SELECT COUNT(*) INTO exist_count FROM score 
    WHERE sid = student_id AND cid = course_id AND term = semester;
    
    IF exist_count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该学期成绩已存在';
    ELSE
        INSERT INTO score (sid, cid, score, term) 
        VALUES (student_id, course_id, score_value, semester);
    END IF;
END //
DELIMITER ;

5. 事务处理保证数据一致性

教务系统中的许多操作需要保证原子性，例如学生转班时需要同时更新多个表。下面是一些典型的事务应用场景：

5.1 学生信息更新事务

START TRANSACTION;

-- 更新学生基本信息
UPDATE stu SET sclass = '计算机2022' WHERE sid = 1;

-- 记录变更日志
INSERT INTO change_log (user_id, action, table_name, record_id, change_time)
VALUES (CURRENT_USER(), 'UPDATE', 'stu', 1, NOW());

COMMIT;

5.2 成绩批量录入事务

START TRANSACTION;

-- 录入成绩1
INSERT INTO score (sid, cid, score, term) VALUES (1, 1001, 85.5, '2021-2022-1');

-- 录入成绩2
INSERT INTO score (sid, cid, score, term) VALUES (1, 1002, 92.0, '2021-2022-1');

-- 检查是否有异常
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

COMMIT;

5.3 复杂业务事务示例

DELIMITER //
CREATE PROCEDURE transfer_student(
    IN old_student_id INT,
    IN new_name VARCHAR(50),
    IN new_sex ENUM('男','女'),
    IN new_class VARCHAR(20)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 插入新学生记录
    INSERT INTO stu (sname, ssex, sclass) VALUES (new_name, new_sex, new_class);
    SET @new_id = LAST_INSERT_ID();
    
    -- 转移成绩记录
    UPDATE score SET sid = @new_id WHERE sid = old_student_id;
    
    -- 删除旧学生记录
    DELETE FROM stu WHERE sid = old_student_id;
    
    -- 记录操作日志
    INSERT INTO admin_log (action, target_table, old_id, new_id, operator)
    VALUES ('TRANSFER', 'stu', old_student_id, @new_id, CURRENT_USER());
    
    COMMIT;
END //
DELIMITER ;

6. 性能优化与索引设计

随着数据量增长，合理的索引设计对系统性能至关重要。以下是为教务系统设计的索引策略：

核心索引建议：

1. 成绩表查询优化：

   -- 添加复合索引提高查询效率
   ALTER TABLE score ADD INDEX idx_sid_cid_term (sid, cid, term);
   
   -- 为单独查询课程成绩添加索引
   ALTER TABLE score ADD INDEX idx_cid_term (cid, term);
   

2. 学生表查询优化：

   -- 班级是常用查询条件
   ALTER TABLE stu ADD INDEX idx_class (sclass);
   
   -- 姓名模糊查询支持
   ALTER TABLE stu ADD INDEX idx_name (sname);
   

3. 课程表查询优化：

   -- 教师查询
   ALTER TABLE course ADD INDEX idx_teacher (teacher);
   

查询性能分析示例：

-- 查看查询执行计划
EXPLAIN SELECT s.sname, c.cname, sc.score
FROM stu s JOIN score sc ON s.sid = sc.sid
JOIN course c ON sc.cid = c.cid
WHERE s.sclass = '计算机2021' AND sc.term = '2021-2022-1';

索引使用注意事项：

1. 避免过度索引，每个索引都会增加写入开销
2. 定期使用ANALYZE TABLE更新统计信息
3. 监控慢查询日志，针对性优化：

   -- 启用慢查询日志
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
   

7. 系统扩展与高级功能

基础教务系统可以进一步扩展以下功能：

7.1 视图简化常用查询

-- 创建学生成绩视图
CREATE VIEW student_scores AS
SELECT s.sid, s.sname, s.sclass, c.cid, c.cname, sc.score, sc.term
FROM stu s JOIN score sc ON s.sid = sc.sid
JOIN course c ON sc.cid = c.cid;

-- 创建课程统计视图
CREATE VIEW course_stats AS
SELECT c.cid, c.cname, COUNT(sc.sid) as student_count, 
       AVG(sc.score) as avg_score, MAX(sc.score) as max_score
FROM course c LEFT JOIN score sc ON c.cid = sc.cid
GROUP BY c.cid, c.cname;

7.2 触发器实现自动化

-- 成绩变化审计触发器
DELIMITER //
CREATE TRIGGER before_score_update
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
    IF OLD.score <> NEW.score THEN
        INSERT INTO score_change_log (sid, cid, old_score, new_score, change_time, operator)
        VALUES (OLD.sid, OLD.cid, OLD.score, NEW.score, NOW(), CURRENT_USER());
    END IF;
END //
DELIMITER ;

-- 学生信息变更触发器
DELIMITER //
CREATE TRIGGER after_student_update
AFTER UPDATE ON stu
FOR EACH ROW
BEGIN
    IF OLD.sclass <> NEW.sclass THEN
        INSERT INTO class_change_log (sid, old_class, new_class, change_time)
        VALUES (NEW.sid, OLD.sclass, NEW.sclass, NOW());
    END IF;
END //
DELIMITER ;

7.3 定时任务维护

-- 创建学期结束统计任务
DELIMITER //
CREATE EVENT semester_end_stats
ON SCHEDULE EVERY '1' YEAR
STARTS '2023-07-01 00:00:00'
DO
BEGIN
    -- 计算并存储学期统计数据
    INSERT INTO semester_stats (term, student_count, course_count, avg_score)
    SELECT sc.term, COUNT(DISTINCT sc.sid), COUNT(DISTINCT sc.cid), AVG(sc.score)
    FROM score sc
    WHERE sc.term = CONCAT(YEAR(CURDATE())-1, '-', YEAR(CURDATE()), '-2')
    GROUP BY sc.term;
    
    -- 归档旧数据
    INSERT INTO score_archive SELECT * FROM score WHERE term < CONCAT(YEAR(CURDATE())-2, '-', YEAR(CURDATE())-1, '-2');
    DELETE FROM score WHERE term < CONCAT(YEAR(CURDATE())-2, '-', YEAR(CURDATE())-1, '-2');
END //
DELIMITER ;

8. 安全与权限管理

教务系统数据敏感，需要严格的权限控制：

-- 创建不同角色
CREATE ROLE 'teacher', 'student', 'admin';

-- 教师权限：查询学生成绩，录入成绩
GRANT SELECT ON school_db.stu TO 'teacher';
GRANT SELECT, INSERT, UPDATE ON school_db.score TO 'teacher';
GRANT SELECT ON school_db.course TO 'teacher';

-- 学生权限：查询自己的成绩
GRANT SELECT ON school_db.stu TO 'student';
GRANT SELECT ON school_db.score TO 'student' 
WHERE sid = SUBSTRING_INDEX(CURRENT_USER(),'@',1);
GRANT SELECT ON school_db.course TO 'student';

-- 管理员权限：完全控制
GRANT ALL PRIVILEGES ON school_db.* TO 'admin';

-- 创建视图限制数据访问
CREATE VIEW student_own_scores AS
SELECT * FROM score WHERE sid = CONVERT(SUBSTRING_INDEX(CURRENT_USER(),'@',1), UNSIGNED);

GRANT SELECT ON school_db.student_own_scores TO 'student';

安全最佳实践：

1. 永远不要使用root账户连接应用
2. 为每个功能创建最小权限账户
3. 定期审计用户权限：

   SHOW GRANTS FOR current_user();
   

4. 敏感数据加密存储：

   -- 使用AES加密存储敏感信息
   INSERT INTO stu (sname, id_card_no) 
   VALUES ('张三', AES_ENCRYPT('123456789012345678', 'encryption_key'));
   
   -- 查询时解密
   SELECT sname, AES_DECRYPT(id_card_no, 'encryption_key') as id_card_no FROM stu;
   

9. 备份与恢复策略

确保数据安全需要完善的备份方案：

常规备份方法：

# 使用mysqldump完整备份
mysqldump -u root -p school_db > school_db_backup_$(date +%Y%m%d).sql

# 仅备份结构
mysqldump -u root -p --no-data school_db > school_db_schema.sql

# 备份特定表
mysqldump -u root -p school_db stu course score > school_db_core_tables.sql

MySQL内部备份命令：

-- 创建备份表
CREATE TABLE score_backup_20230101 LIKE score;
INSERT INTO score_backup_20230101 SELECT * FROM score;

-- 导出查询结果到文件
SELECT * INTO OUTFILE '/tmp/score_data.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM score WHERE term = '2021-2022-1';

恢复策略示例：

-- 从备份表恢复
INSERT INTO score SELECT * FROM score_backup_20230101 
WHERE NOT EXISTS (SELECT 1 FROM score WHERE score.id = score_backup_20230101.id);

-- 从CSV文件导入
LOAD DATA INFILE '/tmp/score_data.csv'
INTO TABLE score
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

10. 常见问题与调试技巧

开发过程中可能遇到的典型问题及解决方案：

问题1：外键约束失败

-- 错误示例
INSERT INTO score (sid, cid, score) VALUES (999, 1001, 85.5);
-- 错误: Cannot add or update a child row: a foreign key constraint fails

-- 解决方案：先验证外键存在
SELECT COUNT(*) FROM stu WHERE sid = 999;

问题2：事务隔离问题

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 执行敏感操作
COMMIT;

问题3：性能排查工具

-- 查看当前运行查询
SHOW PROCESSLIST;

-- 分析表状态
ANALYZE TABLE score;

-- 检查索引使用情况
SHOW INDEX FROM score;

-- 查看存储过程和函数状态
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

调试存储过程技巧：

-- 临时添加调试输出
DELIMITER //
CREATE PROCEDURE debug_procedure()
BEGIN
    DECLARE var1 INT DEFAULT 0;
    SELECT 'Start procedure' as debug;
    
    SET var1 = 10;
    SELECT CONCAT('var1 value: ', var1) as debug;
    
    -- 业务逻辑...
END //
DELIMITER ;

11. 实际项目经验分享

在真实的教务系统开发中，有几个容易忽视但非常重要的细节：

1. 学期处理逻辑：

   • 设计统一的学期编码规则（如'2023-2024-1'表示2023-2024学年第一学期）
   • 创建学期配置表管理当前有效学期

2. 成绩锁定机制：

   • 添加is_locked字段防止已审核成绩被修改

   ALTER TABLE score ADD COLUMN is_locked TINYINT DEFAULT 0;
   
   -- 修改成绩前检查
   UPDATE score SET score = 85 WHERE id = 1 AND is_locked = 0;
   

3. 历史数据追踪：

   • 为关键表添加created_at和updated_at时间戳

   ALTER TABLE stu ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
   ALTER TABLE stu ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
   

4. 批量操作优化：

   • 使用批量插入代替循环

   -- 低效方式
   START TRANSACTION;
   INSERT INTO score (sid, cid, score) VALUES (1, 1001, 85);
   INSERT INTO score (sid, cid, score) VALUES (1, 1002, 90);
   COMMIT;
   
   -- 高效方式
   START TRANSACTION;
   INSERT INTO score (sid, cid, score) VALUES 
   (1, 1001, 85),
   (1, 1002, 90);
   COMMIT;
   

5. 连接池配置：

   • 应用层配置合理的连接池参数
   • 避免长事务占用连接

12. 系统监控与维护

生产环境教务系统需要持续监控：

关键监控指标：

1. 数据库性能：

   -- 查看查询缓存命中率
   SHOW STATUS LIKE 'Qcache%';
   
   -- 查看连接使用情况
   SHOW STATUS LIKE 'Threads_connected';
   

2. 表空间使用：

   -- 查看表大小
   SELECT table_name, 
          round(((data_length + index_length) / 1024 / 1024), 2) as size_mb
   FROM information_schema.TABLES 
   WHERE table_schema = 'school_db';
   

3. 慢查询分析：

   -- 启用慢查询日志
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
   
   -- 分析慢查询
   mysqldumpslow -s t /var/log/mysql/mysql-slow.log
   

定期维护任务：

1. 优化表：

   OPTIMIZE TABLE score;
   

2. 更新统计信息：

   ANALYZE TABLE stu, course, score;
   

3. 检查外键约束：

   -- 查找无效外键
   SELECT s.sid FROM score sc LEFT JOIN stu s ON sc.sid = s.sid WHERE s.sid IS NULL;
   

13. 从开发到生产的最佳实践

将教务系统从开发环境迁移到生产环境时应注意：

1. 配置差异管理：

   • 使用不同的配置文件区分环境
   • 敏感信息如密码使用环境变量

2. 数据库迁移脚本：

   • 版本化所有DDL变更
   • 使用迁移工具如Flyway或Liquibase

3. 压力测试：

   • 模拟高峰期成绩录入和查询
   • 测试事务并发处理能力

4. 监控告警设置：

   • 设置磁盘空间告警
   • 监控长时间运行的事务

5. 回滚计划：

   • 备份生产数据后再部署
   • 验证备份可恢复性

部署检查清单：

1. 确认字符集一致：

   SHOW VARIABLES LIKE 'character_set%';
   

2. 验证存储引擎：

   SHOW TABLE STATUS WHERE Name IN ('stu','course','score');
   

3. 检查权限设置：

   SHOW GRANTS FOR 'app_user'@'%';
   

4. 测试备份恢复流程

5. 验证关键查询性能

14. 未来扩展方向

基础教务系统可以进一步扩展为完整的教学管理平台：

1. 选课系统扩展：

   • 添加课程容量限制
   • 实现选课冲突检测
   • 开发选课排队机制

2. 考勤管理模块：

   CREATE TABLE attendance (
       id INT PRIMARY KEY AUTO_INCREMENT,
       sid INT NOT NULL,
       cid INT NOT NULL,
       date DATE NOT NULL,
       status ENUM('present','absent','late') NOT NULL,
       FOREIGN KEY (sid) REFERENCES stu(sid),
       FOREIGN KEY (cid) REFERENCES course(cid)
   );
   

3. 教学评价系统：

   CREATE TABLE course_evaluation (
       id INT PRIMARY KEY AUTO_INCREMENT,
       sid INT NOT NULL,
       cid INT NOT NULL,
       term VARCHAR(20) NOT NULL,
       score TINYINT UNSIGNED,
       comment TEXT,
       submit_time DATETIME DEFAULT CURRENT_TIMESTAMP,
       FOREIGN KEY (sid) REFERENCES stu(sid),
       FOREIGN KEY (cid) REFERENCES course(cid),
       UNIQUE KEY (sid, cid, term)
   );
   

4. 数据统计分析：

   • 使用MySQL窗口函数分析成绩趋势
   • 连接BI工具生成可视化报表

5. 微服务化改造：

   • 将数据库拆分为独立服务
   • 实现API网关统一访问

15. 真实案例：成绩异常检测

在实际运行中，我们发现一个有趣的现象：某课程成绩分布异常。通过数据分析找出了可能存在的问题：

-- 分析成绩分布
SELECT 
    FLOOR(score/10)*10 as score_range,
    COUNT(*) as student_count,
    RPAD('', COUNT(*)/10, '*') as histogram
FROM score
WHERE cid = 1001
GROUP BY FLOOR(score/10)*10
ORDER BY score_range;

-- 结果示例：
-- score_range | student_count | histogram
-- 50          | 3             | ***
-- 60          | 12            | ************
-- 70          | 8             | ********
-- 80          | 25            | *************************
-- 90          | 2             | **

这个分布显示80分段人数异常多，可能存在评分标准或录入问题。我们随后添加了成绩异常检测存储过程：

DELIMITER //
CREATE PROCEDURE detect_score_anomalies(IN course_id INT, IN semester VARCHAR(20))
BEGIN
    -- 计算平均值和标准差
    SELECT AVG(score), STDDEV(score) INTO @avg, @stddev
    FROM score WHERE cid = course_id AND term = semester;
    
    -- 查找异常低分
    SELECT s.sid, st.sname, s.score
    FROM score s JOIN stu st ON s.sid = st.sid
    WHERE s.cid = course_id AND s.term = semester
    AND s.score < @avg - 2*@stddev;
    
    -- 查找异常高分
    SELECT s.sid, st.sname, s.score
    FROM score s JOIN stu st ON s.sid = st.sid
    WHERE s.cid = course_id AND s.term = semester
    AND s.score > @avg + 2*@stddev;
END //
DELIMITER ;