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部分将会被自动忽略。