MySQL 实验六——存储过程

存储过程

目录

存储过程

一、实验目的及要求

二、实验环境

三、实验内容

四、实验步骤

---

 

一、实验目的及要求

（1）能够应用某一具体DBMS支持的编程语言和编程规范，规范设计存储过程。

（2）根据数据库应用系统的设计需要，执行存储过程等工具对采集到的实验数据进行整理、分析和解释，得出有效结论。

二、实验环境

操作系统：Windows  

应用软件：MySQL5.6  Navicat 12

三、实验内容

有一个学生数据库，结构如下：

请使用存储过程完成以下操作：

1. 创建存储过程proce_avg_prade统计指定课程的平均成绩；

2. 统计数据库原理与应用课程的成绩分布情况，即按照各分数段统计人数，分数段有[100,90]、(90,80]、(80,70]、(70,60]和(60,0]等五段；

3. 将学生选课成绩从百分制改为等级制（即[100,90]A、(90,80]B、(80,70]C、(70,60]D、(60,0]E）显示。

四、实验步骤

1. 创建存储过程proce_avg_prade统计指定课程的平均成绩；

运行代码：

DELIMITER $  
CREATE PROCEDURE p_Avg_Grade(IN courses VARCHAR(20), OUT avgnum DECIMAL(5,2))  
BEGIN  
    SELECT AVG(sc.Grade) INTO avgnum   
    FROM sc   
    JOIN course ON sc.Cno = course.Cno   
    WHERE course.CName = courses;  
END $ 
DELIMITER ;

运行结果

 2. 统计数据库原理与应用课程的成绩分布情况，即按照各分数段统计人数，分数段有[100,90]、(90,80]、(80,70]、(70,60]和(60,0]等五段；

创建rank表：

create table rank(
division char(66),
sub_sum int
);
insert into rank(division)
VALUES ('[0,60)'),('[60,70]'),('[70,80]'),('[80,90]'),('[90,100]');

运行代码：

DELIMITER $  
CREATE PROCEDURE printcourse (IN pcname CHAR(20))  
BEGIN  
    DECLARE pcno CHAR(20);  
    SELECT Cno INTO pcno FROM Course WHERE Cname = pcname LIMIT 1;  
    IF pcno IS NULL THEN  
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '您输入的课程号不存在，请重新输入！';  
    ELSE  
        -- 更新Rank表的sub_sum字段，使用JOIN来计算每个分段的数量  
        UPDATE Rank r JOIN (  
            SELECT Cno, COUNT(*) as sub_count   
            FROM SC   
            WHERE Grade < 60 AND Cno = pcno   
            GROUP BY Cno  
        ) sc ON r.division = '[0,60)'   
        SET r.sub_sum = sc.sub_count;  
          
        UPDATE Rank r JOIN (  
            SELECT Cno, COUNT(*) as sub_count   
            FROM SC   
            WHERE Grade >= 60 AND Grade < 70 AND Cno = pcno   
            GROUP BY Cno  
        ) sc ON r.division = '[60,70)'   
        SET r.sub_sum = sc.sub_count;  
          
        UPDATE Rank r JOIN (  
            SELECT Cno, COUNT(*) as sub_count   
            FROM SC   
            WHERE Grade >= 70 AND Grade < 80 AND Cno = pcno   
            GROUP BY Cno  
        ) sc ON r.division = '[70,80)'   
        SET r.sub_sum = sc.sub_count;  
          
        UPDATE Rank r JOIN (  
            SELECT Cno, COUNT(*) as sub_count   
            FROM SC   
            WHERE Grade >= 80 AND Grade < 90 AND Cno = pcno   
            GROUP BY Cno  
        ) sc ON r.division = '[80,90)'   
        SET r.sub_sum = sc.sub_count;  
          
        UPDATE Rank r JOIN (  
            SELECT Cno, COUNT(*) as sub_count   
            FROM SC   
            WHERE Grade = 90 AND Grade <= 100 AND Cno = pcno   
            GROUP BY Cno  
        ) sc ON r.division = '[90,100)'   
        SET r.sub_sum = sc.sub_count;  
    END IF;  
END $ 
DELIMITER ;

查询结果

3. 将学生选课成绩从百分制改为等级制（即[100,90]A、(90,80]B、(80,70]C、(70,60]D、(60,0]E）显示。

先把分数属性定义为字符

代码：

ALTER TABLE SC MODIFY Grade CHAR(5);  
DELIMITER $ 
CREATE PROCEDURE ConvertGradesToLevel()  
BEGIN  
    UPDATE SC  
    SET Grade = CASE  
        WHEN Grade >= 90 THEN 'A'  
        WHEN Grade > 80 THEN 'B'  
        WHEN Grade > 70 THEN 'C'  
        WHEN Grade > 60 THEN 'D'  
        ELSE 'E'  
    END;  
END $  
DELIMITER ;

运行结果：

 

 

该学生数据库为stu_course,sql语句如下：

/*创建表student*/
CREATE TABLE Student          
(
	Sno CHAR(8) PRIMARY KEY, /* 列级完整性约束条件*/                  
	Sname VARCHAR(20) ,     
	Ssex CHAR(6),
	Sbirthdate Date,
	Smajor VARCHAR(40)
);

/*创建表course*/
CREATE TABLE Course
( 
	Cno CHAR(5) PRIMARY KEY,
	Cname VARCHAR(40) ,    
	Ccredit SMALLINT,
	Cpno CHAR(5),               	                      
	FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
);
/*创建表sc*/
CREATE TABLE  SC
(
	Sno  CHAR(8),
	Cno  CHAR(5),  
	Grade    SMALLINT, /*成绩*/
	Semester CHAR(5),       /*开课学期*/
	Teachingclass CHAR(8), /*学生选修某一门课程所在的教学班*/
	PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成，表级约束*/
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
                  /* 表级完整性约束条件，Sno是外码，被参照表是Student */
	FOREIGN KEY (Cno) REFERENCES Course(Cno)
                 /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
); 

/*向表中插入数据*/
INSERT INTO student VALUES ('20180001', '李勇', '男', '2000-03-08', '信息安全');
INSERT INTO student VALUES ('20180002', '刘晨', '女', '1999-09-01', '计算机科学与技术');
INSERT INTO student VALUES ('20180003', '王敏', '女', '2001-08-01', '计算机科学与技术');
INSERT INTO student VALUES ('20180004', '张立', '男', '2000-01-08', '计算机科学与技术');
INSERT INTO student VALUES ('20180005', '陈新奇', '男', '2001-11-01', '信息管理与信息系统');
INSERT INTO student VALUES ('20180006', '赵明', '男', '2000-06-12', '数据科学与大数据技术');
INSERT INTO student VALUES ('20180007', '王佳佳', '女', '2001-12-07', '数据科学与大数据技术');

INSERT INTO course VALUES ('81001', '程序设计基础与C语言', 4, NULL);
INSERT INTO course VALUES ('81002', '数据结构', 4, '81001');
INSERT INTO course VALUES ('81003', '数据库系统概论', 4, '81002');
INSERT INTO course VALUES ('81004', '信息系统概论', 4, '81003');
INSERT INTO course VALUES ('81005', '操作系统', 4, '81001');
INSERT INTO course VALUES ('81006', 'Python语言', 3, '81002');
INSERT INTO course VALUES ('81007', '离散数学', 4, NULL);
INSERT INTO course VALUES ('81008', '大数据技术概论', 4, '81003');


INSERT INTO sc VALUES ('20180001', '81001', 85, '20192', '81001-01');
INSERT INTO sc VALUES ('20180001', '81002', 96, '20201', '81002-01');
INSERT INTO sc VALUES ('20180001', '81003', 87, '20202', '81003-01');
INSERT INTO sc VALUES ('20180002', '81001', 80, '20192', '81001-02');
INSERT INTO sc VALUES ('20180002', '81002', 98, '20201', '81002-01');
INSERT INTO sc VALUES ('20180002', '81003', 71, '20202', '81003-02');
INSERT INTO sc VALUES ('20180003', '81001', 81, '20192', '81001-01');
INSERT INTO sc VALUES ('20180003', '81002', 76, '20201', '81002-02');
INSERT INTO sc VALUES ('20180004', '81001', 56, '20192', '81001-02');
INSERT INTO sc VALUES ('20180004', '81002', 97, '20201', '81002-02');
INSERT INTO sc VALUES ('20180005', '81003', 68, '20202', '81003-01');
INSERT INTO sc VALUES ('20180005', '81001', null, '20192', '81001-02');