Mysql存储过程

基于school数据库中的student, course, sc表，创建满足下述要求的存储过程:

Cmd命令窗口运行

1. 查询指定学生的选修课总学分。

DELIMITER $$

CREATE PROCEDURE p1(IN name VARCHAR(5) character set utf8,OUT sumcredit INT)

BEGIN

    SELECT  Sname,sum(Ccredit) from Student join SC on SC.Sno=Student.Sno join Course on Course.Cno=SC.Cno WHERE Sname=name;

END $$

DELIMITER ;

 

测试用例

CALL P1('李勇',@sumcredit);

 

2. 查询考试平均成绩超过指定分值的学生姓名和平均成绩。

DELIMITER $$

CREATE PROCEDURE p2(IN sc_grade INT,OUT Sname VARCHAR(5),OUT AVG_grade INT)

BEGIN

select Student.Sname,avg(sc.grade) from student,sc where student.sno=sc.sno group by student.sno having avg(sc.grade)>sc_grade;

END $$

DELIMITER ;

 

测试用例

CALL P2(50,@Sname,@AVG_grade);

 

3. 查询指定系的学生中，选课门数最多的学生学号、选课门数和平均成绩。

DELIMITER $$

CREATE PROCEDURE p3(IN s_sdept VARCHAR(5) character set utf8,OUT sno INT,OUT count INT,OUT avg_grade INT)

BEGIN

select student.sno,count(*),avg(grade) from sc join student on sc.sno=student.sno where sdept=s_sdept group by student.sno order by count(*) desc;

END $$

DELIMITER ;

 

测试用例

CALL p3("CS",@sno,@count,@avg_grade);

 

 

4. 删除指定学生某课程的选课记录。

DELIMITER $$

CREATE PROCEDURE p4(IN sc_sno INT)

BEGIN

delete from sc where sno=sc_sno;

END $$

DELIMITER ;

 

测试用例

CALL p4(200215123);

 

 

5. 查询指定课程的学生成绩大于他自己选修课程平均成绩的学生学号和姓名。

DELIMITER $$

CREATE PROCEDURE p5(IN name VARCHAR(10))

select sno from sc,course where course.cno=sc.cno and cname=name and sc.grade>ALL(Select avg(grade) from sc);

END $$

DELIMITER ;

 

测试用例

CALL p5(“数学”);

 

数据库链接：https://pan.baidu.com/s/1_OtHuZHF86HRMVY9ROn3xQ    提取码：kr9l