9 TransactSQL程序设计.docx
《9 TransactSQL程序设计.docx》由会员分享，可在线阅读，更多相关《9 TransactSQL程序设计.docx（18页珍藏版）》请在冰豆网上搜索。
9TransactSQL程序设计
9Transact-SQL程序设计
9.1实验目的
1.掌握Transact-SQL中全局变量和局部变量的使用方法；
2.掌握Transact-SQL函数的使用方法；
3.掌握自定义函数的方法；
4.初步掌握Transact-SQL流程控制语句的使用。
9.2实验环境
硬件环境：
计算机；
软件环境：
SQLServer2005；
9.3实验内容
9.3.1变量的使用
9.3.1.1全局变量的使用
（1）显示到当前日期和时间为止试图登录SQLServer的次数。
（2）显示SQLServer安装的日期、版本和处理器类型。
9.3.1.2局部变量的使用
（1）编程实现将EDUC数据库中教师档案表的记录个数存入到局部变量，并通过局部变量显示出来。
（2）编程实现将EDUC数据库中学生成绩表中的平时成绩的最大值和最小值分别存入到两个局部变量，并通过局部变量显示出来。
9.3.2函数的使用
9.3.2.1系统函数的使用
（1）显示服务器端计算机的名称
selecthost_name（）as‘服务器端计算机名称’
（2）显示用户的数据库用户名
selectuser_name（）as‘数据库用户名’
9.3.2.2字符串函数的使用
（1）显示字符’A’的ASCII码。
（2）显示ASCII码为68的字符。
（3）利用substring、right和len函数，显示学生成绩表中课程门类、班内学号、平时成绩、期末成绩的内容。
9.3.2.3日期型函数的使用
（1）利用getdate和year函数显示学生档案表中的学号、姓名、性别、年龄字段的内容。
（2）显示今天距1999年12月31日相隔的天数。
9.3.2.4数学函数的使用
（1）利用rand函数随机产生一个0到1之间的小数。
（2）利用rand和floor函数随机产生一个0-99之间的整数。
9.3.2.5用户自定义函数的使用
（1）编写一个用户自定义函数，完成以下功能：
根据两个输入的参数（成绩的上限和下限），求t_score表中满足输入条件的学生人数。
（2）利用上面的用户自定义函数，求t_score表中成绩在60-90分之间的学生人数。
9.3.3流程控制语句的使用
（1）根据学生成绩表中的考试成绩，查询学生课程号为JS1301的课程的平均成绩，若平均成绩大于75,显示“成绩较理想”，否则显示“成绩不理想”。
利用if语句编写程序实现以上功能。
（2）根据学生成绩表中课程号为“JS1301”的考试成绩，并根据考试分数输出考试等级。
当分数大于等于90分，输出“优”；当分数在80-90之间，输出“良”；当分数在70至80之间，输出“中”；当分数在60至70之间，输出“及格”；当分数在60分以下，输出“不及格”。
利用case语句编写程序实现以上功能。
9.4实训步骤
9.4.1操作步骤
1.在开始菜单中：
所有程序－>SQL Server 2005 －>SQL Server Management
Studio， 单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。
2.在SQL Server Management Studio中，在【对象资源管理器】中选择“EDUC”数据库，右键选择“新建查询”，如图9-1所示；
图9-1新建查询窗口
3.进入查询编辑器窗口，在其中中编辑所要查询的数据，单击快捷工具栏的“！
执行”按钮，执行上述语句，在结果框中显示出查询的结果；
9.4.3变量的使用
9.4.1.1全局变量的使用
（1）显示到当前日期和时间为止试图登录SQLServer的次数，在查询窗口输入程序，执行语句，在结果框中显示出查询的结果，如图9-2所示。
SQL语句：
SELECTGETDATE（）AS'当前登陆日期和时间',
@@CONNECTIONSAS'企图登陆次数'
图9-2试图登录SQLServer的次数
（2）显示SQLServer安装的日期、版本和处理器类型，在查询窗口输入程序，执行语句，在结果框中显示出查询的结果，如图9-3所示；
SQL语句：
SELECT@@VERSIONAS'当前的SQLServer信息'
9.4.1.2局部变量的使用
（1）编程实现将EDUC数据库中教师档案表的记录个数存入到局部变量，并通过局部变量显示出来，在查询窗口输入程序，执行语句，在结果框中显示出查询的结果，如图9-4所示；
SQL语句：
declare@RowsReyurnint
set@RowsReyurn=（selectcount（*）from教师档案表）
select@RowsReyurnas'记录个数'
图9-3显示SQLServer安装的日期、版本和处理器类型
图9-4显示教师档案表的记录个数
（2）编程实现将EDUC数据库中学生成绩表中的平时成绩的最大值和最小值分别存入到两个局部变量，并通过局部变量显示出来，执行上述语句，在结果框中显示出查询的结果，如图9-5所示；
SQL语句：
declare@maxscorereal,@minscorereal
select@maxscore=max（平时成绩）,@minscore=min（平时成绩）from学生成绩
select@maxscoreas'最大值',@minscoreas'最小值'
图9-5学生成绩表中的平时成绩的最大值和最小值
9.4.2函数的使用
9.4.2.1系统函数的使用
（1）显示服务器端计算机的名称，执行上述语句，在结果框中显示出查询的结果，如图9-6所示；
SQL语句：
selecthost_name（）as'服务器端计算机名称'
（2）显示用户的数据库用户名，执行上述语句，在结果框中显示出查询的结果，如图9-7所示；
SQL语句：
selectuser_name（）as'数据库用户名'
图9-6显示服务器端计算机的名称
图9-7显示用户的数据库用户名
9.4.2.2字符串函数的使用
（1）显示字符’A’的ASCII码，执行上述语句，在结果框中显示出查询的结果，如图9-8所示；
SQL语句：
selectascii（'A'）as'字符A的ASCII码'
（2）显示ASCII码为68的字符，执行上述语句，在结果框中显示出查询的结果，如图9-9所示；
SQL语句：
selectchar（'68'）as'ASCII码为的字符'
图9-8显示字符’A’的ASCII码
（3）利用substring、right和len函数，显示学生成绩表中课程门类、班内学号、平时成绩、期末成绩的内容，执行上述语句，在结果框中显示出查询的结果，如图9-10所示；
SQL语句
selectsubstring（课程ID,1,len（课程ID）-4）as'课程门类',
right（rtrim（学号）,2）as学号,
平时成绩as平时成绩,
期末成绩as期末成绩
fromdbo.学生成绩
图9-9显示ASCII码为68的字符
图9-10显示学生成绩表
9.3.2.3日期型函数的使用
（1）利用getdate和year函数显示学生档案表中的学号、姓名、性别、年龄字段的内容，执行上述语句，在结果框中显示出查询的结果，如图9-11所示；
SQL语句：
SELECT学号ASnum,
姓名ASsname,
性别ASsex,
YEAR（GETDATE（））-YEAR（出生日期）ASage
FROM学生档案
图9-11显示学生档案表
（2）显示今天距1999年12月31日相隔的天数，执行上述语句，在结果框中显示出查询的结果，如图9-12所示；
SQL语句：
SELECTDATEDIFF（day,'1999-12-31',getdate（））
AS'今天距离1999年12月31日相隔的天数'
图9-12显示今天距1999年12月31日相隔的天数
9.4.2.4数学函数的使用
（1）利用rand函数随机产生一个0到1之间的小数，执行上述语句，在结果框中显示出查询的结果，如图9-13所示；
SQL语句：
selectrand（9）as'0到1之间的一个小数'
图9-13随机产生一个0到1之间的小数
（2）利用rand和floor函数随机产生一个0-99之间的整数，执行上述语句，在结果框中显示出查询的结果，如图9-14所示；
SQL语句：
selectfloor（rand（6）*100）as'在0至99产生一个整数的值'
9.4.2.5用户自定义函数的使用
（1）编写一个用户自定义函数，完成以下功能：
根据两个输入的参数（成绩的上限和下限），求t_score表中满足输入条件的学生人数，执行上述语句，在结果框中显示出查询的结果，如图9-14所示；
SQL语句：
Createfunctionfun
（@maxlinereal,@minlinereal）
returnsint
begin
declare@personcountasint
select@personcount=count（*）from学生成绩
where期末成绩<=@maxlineand期末成绩>=@minline
return@personcount
end
图9-14随机产生一个0-99之间的整数
图9-15自定义函数
（2）利用上面的用户自定义函数，求t_score表中成绩在60-90分之间的学生人数，执行上述语句，在结果框中显示出查询的结果，如图9-16所示；
SQL语句：
selectdbo.fun（90,60）as'60到90分的学生人数'
图9-16显示t_score表中成绩在60-90分之间的学生人数
9.4.3流程控制语句的使用
（1）根据学生成绩表中的考试成绩，查询学生课程号为JS1301的课程的平均成绩，若平均成绩大于75,显示“成绩较理想”，否则显示“成绩不理想”。
利用if语句编写程序实现以上功能，执行上述语句，在结果框中显示出查询的结果，如图9-17，9-18所示；
SQL语句：
if（selectavg（期末成绩）from学生成绩whereleft（课程ID,6）='JS1301'）>75
begin
print'计算机网络课程的期末平均成绩较理想!
'
print''
selectavg（期末成绩）as平均成绩
from学生成绩
whereleft（课程ID,6）='JS1301'
end
else
begin
print'计算机网络课程的期末平均成绩不理想!
'
print''
selectavg（期末成绩）as平均成绩
from学生成绩
whereleft（课程ID,6）='JS1301'
end
图9-17显示结果
图9-18显示消息
（2）根据学生成绩表中课程号为“JS1301”的考试成绩，并根据考试分数输出考试等级。
当分数大于等于90分，输出“优”；当分数在80-90之间，输出“良”；当分数在70至80之间，输出“中”；当分数在60至70之间，输出“及格”；当分数在60分以下，输出“不及格”。
利用case语句编写程序实现以上功能，执行上述语句，在结果框中显示出查询的结果，如图9-19所示；
SQL语句：
select学号as学号,
考试等级=case
when期末成绩>=90then'优'
when期末成绩>=80then'良'
when期末成绩>=70then'中'
when期末成绩>=60then'及格'
else
'不及格'
end
from学生成绩
whereleft（课程ID,6）='JS1301'
orderby学号
图9-19case语句编写程序
9.5实验报告要求
1.对此次实验原理的归纳；
2.给出每一步查询的截图；
2.对此次实验过程中出现问题的分析及解决办法；
3.此次实验的收获或体会；
升级会员 