实训七KUTransact-SQL程序设计

1、实训七 Transact-SQL程序设计一、实训目的1. 掌握Transact-SQL中全局变量和局部变量的使用方法；2. 掌握Transact-SQL函数的使用方法；3. 掌握自定义函数的方法；4. 初步掌握Transact-SQL流程控制语句的使用。二、实训步骤 (一) 变量的使用1. 全局变量的使用(1) 显示到当前日期和时间为止试图登录SQL Server的次数。SELECT GETDATE() AS '当前登陆日期和时间' , CONNECTIONS AS '企图登陆次数'当前登陆日期和时间 企图登陆次数- -2011-10-25 11:44:23.4

2、20 196(1 行受影响)(2) 显示SQL Server安装的日期、版本和处理器类型。SELECT VERSION AS '当前的SQL Server信息' 当前的SQL Server信息-Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft CorporationStandard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)(1 行受影响)2. 局部

3、变量的使用(1) 编程实现将student数据库中t_student表的记录个数存入到局部变量，并通过局部变量显示出来。 USE STUDENTGODECLARE RowsReturn intSET RowsReturn = (SELECT COUNT(*) FROM T_STUDENT)SELECT RowsReturn AS 'SELECT 返回的记录数'GOSELECT 返回的记录数-20(1 行受影响)(2) 编程实现将student数据库中t_score表中的分数的最大值和最小值分别存入到两个局部变量，并通过局部变量显示出来。 -声明变量DECLARE MaxScor

4、e real,MinScore real-给变量赋值SELECT MaxScore = MAX(SCORE), MinScore = MIN(SCORE)FROM T_SCORESELECT MaxScore as '最大值', MinScore as '最小值'GO最大值 最小值- -93 52(1 行受影响) (二) 函数的使用1. 系统函数的使用(1) 显示服务器端计算机的名称 select host_name() as '服务器端计算机名称'(2) 显示用户的数据库用户名 select user_name() as '数据库用户名

5、'2. 字符串函数的使用(1) 显示字符A的ASCII码。SELECT ascii('A') as 'A的ASCII码'A的ASCII码-65(1 行受影响)(2) 显示ASCII码为68的字符。SELECT char(68) as 'ASCII码为的字符'ASCII码为的字符-D(1 行受影响)(3) 利用substring、right和len函数，显示t_student表中班级、班内学号、姓名字段的内容。（参考教材P119例6-23）SELECT SUBSTRING(S_NUMBER,1,LEN(S_NUMBER)-2) AS 

6、9;班级', RIGHT(RTRIM(S_NUMBER),2) AS 学号, S_NAME AS 姓名, SEX AS 性别, BIRTHDAY AS 出生日期, POLITY AS 政治面貌FROM T_STUDENTWHERE LEFT(S_NUMBER,5)='05541'班级 学号 姓名 性别 出生日期 政治面貌- - - - - -05541 01 刘洋 男 1984-10-14 00:00:00.000 党员05541 02 李志娟 女 1984-11-12 00:00:00.000 团员05541 03 赵雪梅 女 1984-06-25 00:00:00.

7、000 团员05541 04 王华 女 1984-09-11 00:00:00.000 团员05541 05 张金辉 男 1985-05-16 00:00:00.000 群众05541 06 贾波 男 1984-10-03 00:00:00.000 党员05541 07 周萍 女 1984-07-12 00:00:00.000 群众05541 08 马帅 男 1984-05-22 00:00:00.000 团员05541 09 唐芸 女 1985-08-25 00:00:00.000 群众05541 10 王华 男 1984-01-20 00:00:00.000 团员(10 行受影响)3. 日

8、期型函数的使用(1) 利用getdate和year函数显示t_student表中的学号、姓名、年龄字段的内容。（参考教材P121例6-25）SELECT S_NUMBER AS 学号, S_NAME AS 姓名, SEX AS 性别, YEAR(GETDATE()-YEAR(BIRTHDAY) AS 年龄FROM T_STUDENT学号 姓名 性别 年龄- - - -0554101 刘洋 男 270554102 李志娟 女 270554103 赵雪梅 女 270554104 王华 女 270554105 张金辉 男 260554106 贾波 男 270554107 周萍 女 270554108

9、 马帅 男 270554109 唐芸 女 260554110 王华 男 27B0451101 张小航 男 27B0451102 王文广 男 26B0451103 李艳红 女 27B0451104 张丽霞 女 27B0451105 王强 男 27B0451106 张保田 男 27B0451107 李博文 男 27B0451108 刘芳芳 女 26B0451109 李海 男 27B0451110 常江宁 男 27(20 行受影响)(2) 利用month或datepart函数显示t_student表中学号、姓名和出生月份（参考教材P122例6-26）。SELECT S_NUMBER AS 学号, S

10、_NAME AS 姓名, SEX AS 性别, DATEPART(Month,BIRTHDAY) AS 出生月份FROM T_STUDENT学号 姓名 性别 出生月份- - - -0554101 刘洋 男 100554102 李志娟 女 110554103 赵雪梅 女 60554104 王华 女 90554105 张金辉 男 50554106 贾波 男 100554107 周萍 女 70554108 马帅 男 50554109 唐芸 女 80554110 王华 男 1B0451101 张小航 男 12B0451102 王文广 男 5B0451103 李艳红 女 6B0451104 张丽霞 女

11、7B0451105 王强 男 11B0451106 张保田 男 7B0451107 李博文 男 8B0451108 刘芳芳 女 4B0451109 李海 男 2B0451110 常江宁 男 3(20 行受影响)(3) 显示今天距2003年1月21日相隔的天数。提示：select datediff(dd,'2003-1-21',getdate() AS 相隔天数SELECT DATEDIFF(day,'2003-1-21',getdate() AS '今天距离1月21日相隔的天数'今天距离1月21日相隔的天数-3201(1 行受影响)4. 转换函数

12、的使用(1) 利用convert函数查询t_student表中出生年份为1985年的学生信息。 SELECT S_NUMBER AS 学号, S_NAME AS 姓名, SEX AS 性别, BIRTHDAY AS 出生日期 POLITY AS 政治面貌FROM T_STUDENTWHERE CONVERT ( char(40),BIRTHDAY,102)LIKE '1985%'(2) 按yy.mm.dd格式显示t_student表中的出生日期。select CONVERT(char(40),BIRTHDAY,102) as 出生日期from t_student(3) 按dd/

13、mm/yy格式显示t_student表中的出生日期。select CONVERT(char(40),BIRTHDAY,103) as 出生日期from t_student(4) 按hh:mi:ss的格式显示当前时间。select CONVERT(char(40),getdate(),108) as 当前时间5. 数学函数的使用(1) 利用rand函数随机产生一个0到1之间的小数。select rand(8) as '0到之间的一个小数'(2) 利用rand和floor函数随机产生一个0-99之间的整数。select floor(rand(6)*100) as '在至产生

14、一个整数的值'6. 用户自定义函数的使用(1) 编写一个用户自定义函数fun_sumscores，要求根据输入的班级号和课程号，求此班此门课程的总分。（参考教材P125例6-30）CREATE FUNCTION Fun_SumScores(ClassName AS char(10), CourseNumber AS char(10)RETURNS realBEGIN DECLARE SumResult AS real SELECT SumResult=sum(score) FROM t_score WHERE SUBSTRING(s_number,1,LEN(s_number)-2)

15、=ClassName AND c_number=CourseNumber GROUP BY SUBSTRING(s_number,1,LEN(s_number)-2) RETURN SumResultEND(2) 利用上面用户自定义函数fun_sumscores,求t_score表中各个班级的各门课程的总分。（参考教材P125例6-31）SELECT DISTINCT 班级名称=SUBSTRING(s_number,1,LEN(s_number)-2), 课程名称=t_course.c_name, 总分=dbo.Fun_SumScores(SUBSTRING(s_number,1,LEN(s_

16、number)-2), t_score.c_number)FROM t_score,t_courseWHERE t_score.c_number=t_course.c_number(3) 编写一个用户自定义函数，完成以下功能：根据两个输入的参数（成绩的上限和下限），求t_score表中满足输入条件的学生人数。Create function fun_sumren(maxline real,minline real)returns intbegindeclare personcount as intselect personcount=count(*) from t_scorewhere scor

17、e<=maxline and score>=minlinereturn personcountend(4) 利用上面的用户自定义函数，求t_score表中成绩在60-90分之间的学生人数。select dbo.fun_sumren(60,90) as '60到分的学生人数'(三) 流程控制语句的使用1根据t_score表中的考试成绩，查询B04511班学生课程号为10010218的课程的平均成绩，若平均成绩大于75,显示“成绩较理想”，否则显示“成绩不理想”。利用if语句编写程序实现以上功能。（参考教材P126例6-32）if (select avg(score)

18、from t_score where left(s_number,6)='B04511'and c_number='10010218')>75begin print 'B04511班课程的平均成绩较理想!'print ''select avg(score) as 平均成绩from t_scorewhere left(s_number,6)='B04511'and c_number='10010218'endelsebeginprint 'B04511班课程的平均成绩不理想!'p

19、rint ''select avg(score) as 平均成绩from t_scorewhere left(s_number,6)='B04511'and c_number='10010218'end2根据t_score表中B04511班的考试成绩，并根据考试分数输出考试等级。当分数大于等于90分，输出“优”；当分数在80-90之间，输出“良”；当分数在70至80之间，输出“中”；当分数在60至70之间，输出“及格”；当分数在60分以下，输出“不及格”。利用case语句编写程序实现以上功能。（参考教材P129例6-34,不需要输出课程名称）se

20、lect s_number as 学号,考试等级=casewhen score>=90 then '优'when score>=80 then '良'when score>=70 then '中'when score>=60 then '及格'else'不及格'endfrom t_scorewhere left(s_number,6)='B04511'order by s_number三、思考题1、全局变量和局部变量有什么区别？答：局部变量必须以标记作为前缀 ，局部变量的使用也是先声明，再赋值 全局变量必须以标记作为前缀，全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值 2、说明下列字符串函数的作用：ltrim() rtrim() lower() upper() replicate() reverse() stuff()substring() str() len()答：ltrim()删除数据前面的空格rtrim() 去掉字符串后面的空格 lower() 转换成小写字母upper() 把给定的字符串变成大写字母 replicate()按照给定的次数，重复表达式的值 reverse(