使用MYSQL workbench实现一些功能

1.创建schema `s-t`

2.在s-t中创建course、sc、student表格

可以直接在最后一行手动添加数据

3.解题：

3.1统计数学的成绩分布情况。

（1）创建函数discrete_math_grade，用于统计课程的不同成绩区间的人数。

-- 删除已经存在的存储过程discrete_math_grade  
DROP PROCEDURE IF EXISTS discrete_math_grade;    
  
DELIMITER //  
  
-- 创建存储过程discrete_math_grade，用于统计课程的不同成绩区间的人数  
CREATE PROCEDURE discrete_math_grade(IN course_name VARCHAR(255))    
BEGIN    
    -- 声明变量，用于存储不同成绩区间的人数  
    DECLARE p_100 INT DEFAULT 0;  -- 100分的人数  
    DECLARE p_90 INT DEFAULT 0;   -- 90-99分的人数  
    DECLARE p_80 INT DEFAULT 0;   -- 80-89分的人数  
    DECLARE p_70 INT DEFAULT 0;   -- 70-79分的人数  
    DECLARE p_60 INT DEFAULT 0;   -- 60-69分的人数  
    DECLARE p_other INT DEFAULT 0; -- 60分以下的人数  
    DECLARE p_grade INT;          -- 声明用于从游标中获取成绩的变量  
    DECLARE done BOOL DEFAULT FALSE; -- 声明一个标志变量，用于控制循环的结束  
  
    -- 声明游标，用于从sc表中获取指定课程的成绩  
    DECLARE dist CURSOR FOR     
        SELECT grade     
        FROM sc     
        WHERE cno = (SELECT cno FROM course WHERE cname = course_name);    
  
    -- 当游标找不到更多数据时，设置done为TRUE  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    
  
    -- 打开游标  
    OPEN dist;    
  
    -- 循环读取游标中的数据  
    read_loop: LOOP    
        -- 从游标中获取成绩  
        FETCH dist INTO p_grade;    
          
        -- 检查是否已经遍历完所有数据  
        IF done THEN    
            LEAVE read_loop;    
        END IF;    
  
        -- 根据成绩更新对应区间的人数  
        IF p_grade = 100 THEN     
            SET p_100 = p_100 + 1;    
        ELSEIF p_grade >= 90 AND p_grade < 100 THEN     
            SET p_90 = p_90 + 1;    
        ELSEIF p_grade >= 80 AND p_grade < 90 THEN     
            SET p_80 = p_80 + 1;    
        ELSEIF p_grade >= 70 AND p_grade < 80 THEN     
            SET p_70 = p_70 + 1;    
        ELSEIF p_grade >= 60 AND p_grade < 70 THEN     
            SET p_60 = p_60 + 1;    
        ELSE    
            SET p_other = p_other + 1;    
        END IF;    
    END LOOP;    
  
    -- 关闭游标  
    CLOSE dist;    
  
    -- 查询不同成绩区间的人数并返回  
    SELECT p_100 , p_90 , p_80 , p_70 , p_60 , p_other ;  
END //  
  
DELIMITER ;  
  
-- 调用存储过程，确保'数学'是course表中cname列的实际值  
CALL discrete_math_grade('数学');

3.2.统计任意一门课的平均成绩

CREATE PROCEDURE ave_grade(IN p_course_name VARCHAR(255))    
BEGIN    
    -- 声明一个变量 v_average_score，用于存储计算出的平均成绩，类型为带有两位小数的十进制数  
    DECLARE v_average_score DECIMAL(10, 4);    
    
    -- 直接从 sc 表和 course 表连接查询，并计算指定课程的平均成绩  
    -- 将计算结果存储到 v_average_score 变量中  
    SELECT AVG(grade) INTO v_average_score    
    FROM sc    
    JOIN course ON sc.cno = course.cno    
    WHERE course.cname = p_course_name;    
    
    -- 输出或处理平均成绩，这里直接通过 SELECT 语句返回结果  
    SELECT v_average_score AS average_score;    
END //    
  
-- 还原分隔符为分号  
DELIMITER ;  
  
-- 调用存储过程，传入课程名 '信息系统'  
call ave_grade('信息系统');

3.3.将学生选课成绩从百分制改为等级制

-- 创建一个名为 GetGradeLevelsForCourse 的存储过程，它接受一个课程名称作为输入参数  
CREATE PROCEDURE GetGradeLevelsForCourse(IN course_name VARCHAR(255))    
BEGIN    
    SELECT       
        grade,     
        CASE       
            WHEN grade BETWEEN 90 AND 100 THEN 'A'   
            WHEN grade BETWEEN 80 AND 89 THEN 'B'   
            WHEN grade BETWEEN 70 AND 79 THEN 'C'    
            WHEN grade BETWEEN 60 AND 69 THEN 'D'   
            ELSE 'E'   
        END AS grade_level    -- 使用 AS 关键字为 CASE 语句的结果设置别名 grade_level  
    FROM       
        sc    
    WHERE    
        cno = (SELECT cno FROM course WHERE cname = course_name); -- 使用子查询从 course 表中匹配课程名称并获取 cno  
END //    
  
-- 还原分隔符为分号  
DELIMITER ;  
  
-- 调用存储过程，传入课程名 '数据库'  
call GetGradeLevelsForCourse('数据库');



DELIMITER //  

4小结：

1. 统计数学的成绩分布情况：通过编写存储过程，我能够按照预设的分数段统计离散数学课程的成绩分布情况。这不仅增强了我对SQL语句中聚合函数（如COUNT）和条件语句（如BETWEEN）的使用能力，还让我对成绩分析有了更深入的理解。

2. 统计任意一门课的平均成绩：我创建了一个带参数的存储过程，该过程能够根据输入的课程ID计算并返回该课程的平均成绩。这一功能的实现，不仅提升了我对SQL函数（如AVG）的应用能力，还锻炼了我对存储过程中参数传递的理解和使用。

3. 将学生选课成绩从百分制改为等级制：在将百分制成绩转换为等级制的过程中，我使用了CASE语句，并根据预设的等级划分条件对成绩进行了分类。这一功能的实现，不仅加强了我对SQL条件语句的掌握，还让我认识到在数据库中进行数据转换的灵活性和重要性。

在编写存储过程的过程中，我遇到了几个挑战和疑问，但通过不断的尝试和思考，我最终找到了解决方案：

• 在统计成绩分布时，如何选择合适的分数段是一个需要考虑的问题。我参考了常见的成绩划分标准，并结合实际情况进行了调整。
• 在计算平均成绩时，我意识到需要确保输入的课程ID在数据库中确实存在，否则可能会返回错误的结果。因此，在存储过程中加入了对课程ID的验证步骤。
• 在将成绩转换为等级时，我注意到直接更新原始数据可能带来风险。因此，我决定先在临时表中进行转换，确认无误后再更新原始数据。这一做法提高了数据的安全性。