数据库存储过程（count sno group by；sum credit）

修改成性别查询

例题：

#请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuRepInfo_学号最后两位.利用学生学号查询该名学生各学期的选修课程情况，
# 要求显示学生的学号，姓名，选修的学年，学期，选修的课程号，课程名及其成绩，显示结果按学年和学期升序排列。

delimiter //
create procedure PROC_StuRepInfo_
 (in sid char(10))
    begin
        select s.sno, sname,Racademicyear,Rterm, c.Cno,Cname,Grade
            from students s
                INNER JOIN reports r ON s.Sno = r.Sno
                INNER JOIN Courses c ON r.Cno = c.Cno

            where s.sno=sid
        ORDER BY sno ,cno;
end//

CALL PROC_StuRepInfo_('2015112101');

请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGrade

查询某位学生指定课程的成绩和可获得这门课程的学分

（注：若该学生的课程成绩小于60分或是为空，则学分要显示为0分）

请写出下列调用存储过程PROC_StuGrade_学号最后两位的MySQL语句，

（1）查询'张建国'选修的'数据库系统原理'课程的成绩和学分

（2）查询'朱玉玲'选修的'计算机科学概论'课程的成绩和学分

DELIMITER //

CREATE PROCEDURE PROC_StuGrade(

SN VARCHAR(4),CN VARCHAR(20))

BEGIN

    SELECT S.Sno,Sname,Cname,Grade,

        (CASE

            WHEN Grade<60 OR Grade IS NULL THEN 0

            ELSE Ccredit

         END) AS "Credit"

    FROM Students S INNER JOIN Reports R ON S.Sno=R.Sno

        INNER JOIN Courses C ON R.Cno=C.Cno

    WHERE S.Sname=SN AND C.Cname=CN;

END //

DELIMITER ;

CALL Proc_StuGrade('张建国','数据库系统原理');
CALL Proc_StuGrade('朱玉玲','计算机科学概论');

#请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_DepNativeInfo.统计指定入学年级指定学院名中，
# 各省的生源人数，要求显示入学年级，学院名，生源地（只显示省份）及生源人数
请写出下列调用存储过程PROC_DepNativeInfo最后两位的MySQL语句， 查询2014级计算机与信息工程学院的生源情况

DELIMITER //
CREATE PROCEDURE PROC_DepNativeInfo(

StuGrd CHAR(4), DN VARCHAR(15))

BEGIN
    SELECT LEFT(Sno,4) AS "入学年级", Dname AS "学院名", LEFT(Snative,2) AS "省份", COUNT(Sno) AS "生源数"
    FROM Students S, Department D
    WHERE S.Dno=D.Dno AND LEFT(Sno,4)=StuGrd
    AND Dname=DN
    GROUP BY LEFT(Snative,2),LEFT(Sno,4),Dname;
END //
DELIMITER ;

CALL PROC_DepNativeInfo('2014','计算机与信息工程学院');

存储过程名命名为PROC_StuGradeNum_学号最后两位

统计指定班级指定课程的考试情况，要求显示出不同分值段（10分为一个分值段）以及尚未考试的学生人数，如（100~90,2人；89~80,7人；…未考,2人）

DELIMITER //
CREATE PROCEDURE PROC_StuGradeNum(
CLS CHAR(8), CN VARCHAR(20))
BEGIN
SELECT (CASE
WHEN Grade BETWEEN 90 AND 100 THEN '90~100'
WHEN Grade BETWEEN 80 AND 89 THEN '80~89'
WHEN Grade BETWEEN 70 AND 79 THEN '70~79'
 WHEN Grade BETWEEN 60 AND 69 THEN '60~69'
WHEN Grade BETWEEN 0 AND 59 THEN '0~59'
ELSE '未考'
END) AS "成绩分段", COUNT(Sno) AS "分段人数"
FROM Reports R,Courses C
WHERE R.Cno=C.Cno AND LEFT(Sno,8)=CLS AND Cname=CN
GROUP BY (CASE
WHEN Grade BETWEEN 90 AND 100 THEN '90~100'
WHEN Grade BETWEEN 80 AND 89 THEN '80~89'
WHEN Grade BETWEEN 70 AND 79 THEN '70~79'
WHEN Grade BETWEEN 60 AND 69 THEN '60~69'
WHEN Grade BETWEEN 0 AND 59 THEN '0~59'
ELSE '未考'
END);
END //
DELIMITER ;

带输出存储过程

DELIMITER //

CREATE PROCEDURE PROC_StuCreditInfo(

SN CHAR(10), ADyear INT, TM INT, OUT GetCredits INT, OUT NotGetCredits INT)

BEGIN

    SELECT SUM(Ccredit)

    FROM Reports R, Courses C

    WHERE R.Cno=C.Cno AND Sno=SN AND Racademicyear=ADyear

    AND Rterm=TM AND Grade>=60

    INTO GetCredits;



    SELECT SUM(Ccredit)

    FROM Reports R, Courses C

    WHERE R.Cno=C.Cno AND Sno=SN AND Racademicyear=ADyear

    AND Rterm=TM AND (Grade<60 OR Grade IS NULL)

    INTO NotGetCredits;

END //

DELIMITER ;

#查询验证
SET @GetCredit=0,@NotGetCredit=0;

CALL PROC_StuCreditInfo('2016115101',2017,2,@GetCredit,@NotGetCredit);

SELECT '2016115101' AS "学号",2017 AS "学年", 2 AS "学期", 

    IFNULL(@GetCredit,0) AS "已获学分", IFNULL(@NotGetCredit,0) AS "未获学分";