MySQL函数的创建与管理（实验六）

MySQL函数的创建与管理

准备工作：脚本文件xkgl.sql下载和执行：文件xkgl.sql下载
1、查询所有学生选修课程的情况，包括学号，课程号，成绩以及成绩对应的等级，没有成绩者显示未考试。（90及以上：优秀；80及以上：良好；70及以上：中等；60及以上：及格；60以下：不及格。）
代码：

select StudentID,CourseID,Grade,case
when Grade >= 90 then '优秀'
when Grade >= 80 then '良好'
when Grade >= 70 then '中等'
when Grade >= 60 then '及格'
when Grade ='null' then '未考试'
else
'不及格'
end as 等级
from grade;

结果截图：

2、定义一个根据学生姓名查询该生选修课程门数的函数stu_count。
代码：

delimiter $$
create function stu_count(name char(20)) returns int
reads sql data
begin
declare StudentID1 char(20);
declare num int;
select StudentID into StudentID1 from Student where StudentName=name;
select count(*) into num from grade where StudentID=StudentID1;
return num;
end;
$$
delimiter ;

结果截图：

3、调用函数stu_count，查询“你”个人学号尾数对应学生表中的学生选修的课程门数。注：先将你学号尾数对应的学生找出来。
代码：

select stu_count('李宇凯');
select stu_count('黄树军');

结果截图：

4、定义一个根据系名和课程名，查询该系学生在该门课程上程的平均分的函数sdept_avggrade
代码：

delimiter $$
create function sdept_avggrade(depname char(20),courname char(20)) returns decimal(5,2)
reads sql data
begin
declare depid char(20);
declare avge decimal(5,2);
declare courid char(20);
select DepartmentID into depid from department where DepartmentName=depname;
select CourseID into courid from course where courname=CourseName;
select avg(cst.Grade) into avge from (select grade.CourseID,grade.StudentID,grade.Grade,student.ClassID,class.DepartmentID from grade inner join student on grade.StudentID=student.StudentID inner join class on student.ClassID=class.ClassID where CourseID=courid and DepartmentID=depid) cst;
return avge;
end;
$$
delimiter ;

结果截图：

5、调用函数sdept_avggrade,查询计算机系学生在《数据库原理与应用》这门课程上的平均成绩
代码：

select sdept_avggrade('计算机系','数据库原理与应用');

结果截图：

6、编写一个函数f1，根据学生的学号和选修的课程号查询该生在该门课程上的成绩，然后返回这个成绩所对应的等级（90及以上：优秀；80及以上：良好；70及以上：中等；60及以上：及格；60以下：不及格。）
代码：

delimiter $$
create function f1(stuid char(20),courid char(20)) returns char(20)
reads sql data
begin
declare gd decimal(5,1);
declare dj char(20);
select Grade into gd from grade where StudentID=stuid and CourseID=courid;
if(gd >= 90) then set dj='优秀';
elseif(gd>= 80) then set dj='良好';
elseif(gd>= 70) then set dj='中等';
elseif(gd>= 60) then set dj='及格';
else set dj='不及格';
end if;
return dj;
end;
$$
delimiter ;

结果截图：

7、创建名为teacherinfo2的函数。要求：函数teacherinfo2有两个参数：teacher id和type。满足：根据编号(teacher id)来查询teacher表中的记录。如果type的值是1时，则返回教师姓名；如果type的值是2时，则返回教师年龄；如果type为其他值，则返回字符串“Error”。
代码：

delimiter $$
create function teacherinfo2(teacher_id char(20),type int) returns char(20)
reads sql data
begin
declare ans char(20);
if(type=1) then
select Teachername into ans from teacher where TeacherID=teacher_id;
elseif(type=2) then
select year(now())-year(Brith) into ans from teacher where TeacherID=teacher_id;
else set ans='Error';
end if;
return ans;
end;
$$
delimiter ;

结果截图：

8、使用SELECT语句调用teacherinfo2函数，返回dep01001号教师的姓名。
代码：

select teacherinfo2('dep01001',1);

结果截图：

9、使用sql语句来删除teacherinfo2函数。
代码：

drop function teacherinfo2;

结果截图：

【分析与思考】
（1） 变量有哪些类型，用什么符号标识？
答：1、局部变量：declare语句专门用于定义局部变量，可以使用default来说明默认值。2、用户会话变量：以一个‘@’开头； 3、系统会话变量以两个‘@’开头；
（2） Mysql编程支持哪几种循环语句？
答：while语句，repeat语句，loop语句；
（3） 查询中的case与程序中的case，有哪些区别？
答：case具有两种格式。简单case函数和case搜索函数。这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判定式。还有一个需要注重的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。