使用MySQL workbench编写存储过程

要求1：统计数学的成绩分布情况。

实现：

        1.首先要先在数据库中创建SCHEMA 's-t'，在其中建课程表course,sc,student。

        

        2.然后为各表插入数据。

                1).student:

                2).sc:

                3).course:

        3.创建存储过程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 AS '100分人数', p_90 AS '90-99分人数', p_80 AS '80-89分人数',   
           p_70 AS '70-79分人数', p_60 AS '60-69分人数', p_other AS '60分以下人数';    
END //  
  
DELIMITER ;  
  
-- 调用存储过程，确保'数学'是course表中cname列的实际值  
CALL discrete_math_grade('数学');

        4.运行结果：

小结：

• 代码使用了存储过程来提供更高效的查询和数据处理。
• 定义了几个变量，用于存储不同成绩区间的人数。
• 使用游标从sc表中获取指定课程的成绩，并根据成绩更新对应区间的人数。
• 最后，返回不同成绩区间的人数。

难点分析：

• 游标的使用：代码中使用了游标来遍历sc表中指定课程的成绩。游标是一种用于在结果集中逐行移动的数据结构。需要熟悉游标的声明、打开、读取、关闭等操作。
• 多重条件判断：根据成绩更新对应区间的人数时，使用了多个IF ELSEIF语句进行条件判断。需要理解每个条件的范围和逻辑关系，确保正确更新对应的人数变量。
• 错误处理：代码中使用了CONTINUE HANDLER来处理游标找不到更多数据的情况，并设置done为TRUE以退出循环。需要注意错误处理的逻辑和语法。

要求2.统计任意一门课的平均成绩：

        1.根据以上建表

        2.创建存储过程ave_grade，用于计算任意一门课的平均成绩

CREATE PROCEDURE ave_grade(IN p_course_name VARCHAR(255))    
BEGIN    
    -- 声明一个变量 v_average_score，用于存储计算出的平均成绩，类型为带有两位小数的十进制数  
    DECLARE v_average_score DECIMAL(10, 2);    
    
    -- 直接从 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('信息系统');

不同课程平均成绩即可输出。

        

小结：

• 代码使用存储过程来提供更高效的计算和数据处理。
• 使用了JOIN语句连接sc表和course表，查询指定课程的成绩。
• 使用AVG函数计算平均成绩，并将结果存储到变量中。
• 最后，通过SELECT语句返回计算出的平均成绩。

难点分析：

• 存储过程的使用：存储过程是预定义的一组SQL语句，可以像函数一样被调用。需要熟悉存储过程的声明、参数传递、变量定义、语句执行等操作。
• JOIN语句：代码中使用了JOIN语句来连接sc表和course表，并查询指定课程的成绩。JOIN语句是SQL语言中用于连接多个表的关键字，需要理解JOIN语句的各种类型和语法。
• AVG函数：代码中使用了AVG函数来计算平均成绩。AVG函数是SQL语言中用于计算平均值的聚合函数，需要掌握AVG函数的语法和使用方法。

要求3：将学生选课成绩从百分制改为等级制。

        1.创建存储过程GetGradeLevelsForCourse将学生选课成绩从百分制改为等级制。

-- 创建一个名为 GetGradeLevelsForCourse 的存储过程，它接受一个课程名称作为输入参数  
CREATE PROCEDURE GetGradeLevelsForCourse(IN course_name VARCHAR(255))    
BEGIN    
    -- 从 sc 表中查询指定课程的成绩，并使用 CASE 语句为成绩添加等级标签  
    -- 使用子查询从 course 表中获取课程的 cno  
    SELECT       
        grade,     
        CASE       
            WHEN grade BETWEEN 90 AND 100 THEN 'A'    -- 成绩在 90-100 之间为 A 等级  
            WHEN grade BETWEEN 80 AND 89 THEN 'B'    -- 成绩在 80-89 之间为 B 等级  
            WHEN grade BETWEEN 70 AND 79 THEN 'C'    -- 成绩在 70-79 之间为 C 等级  
            WHEN grade BETWEEN 60 AND 69 THEN 'D'    -- 成绩在 60-69 之间为 D 等级  
            ELSE 'E'    -- 其他成绩为 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('数据库');

即可得到等级制成绩。

小结：

• 代码使用存储过程来提供更高效的计算和数据处理。
• 使用了子查询从course表中匹配课程名称并获取cno。
• 使用了CASE语句为成绩添加等级标签。
• 最后，通过SELECT语句返回查询结果。

难点分析：

• 存储过程的使用：存储过程是预定义的一组SQL语句，可以像函数一样被调用。需要熟悉存储过程的声明、参数传递、变量定义、语句执行等操作。
• 子查询的使用：代码中使用了子查询从course表中匹配课程名称并获取cno。子查询是一个SELECT语句，嵌套在另一个SELECT语句中使用，需要理解子查询的使用方法和限制。
• CASE语句的使用：代码中使用了CASE语句为成绩添加等级标签。CASE语句是SQL语言中用于条件判断和赋值的关键字，需要掌握CASE语句的语法和使用方法。