第06章 TransactSQL程序设计例题解答.docx

第06章 TransactSQL程序设计例题解答.docx

《第06章 TransactSQL程序设计例题解答.docx》由会员分享，可在线阅读，更多相关《第06章 TransactSQL程序设计例题解答.docx（20页珍藏版）》请在冰豆网上搜索。

第06章TransactSQL程序设计例题解答

第6章Transact-SQL程序设计——例题解答

例6-1-1：

使用GO语句的例子。

程序清单如下：

--第一个批处理完成打开STUDENT数据库的操作

USESTUDENT

GO

/*GO是批处理结束标志*/

--第二个批处理查询T_STUDENT表中的数据

SELECT*FROMT_STUDENT

GO

--第三个批处理查询T_STUDENT表中

--姓张的男学生的学号、姓名和性别

SELECTS_NUMBER,S_NAME,SEX

FROMT_STUDENT

WHERES_NAMElike'张%'

ANDSEX='男'

GO

注意：

GO语句本身并不是Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端命令。

例6-2-1：

在程序中使用注释的例子。

程序清单如下：

--本程序是一个使用注释的例子。

USESTUDENT--打开STUDENT数据库

GO

/*下面的SQL语句完成在T_STUDENT表中查询

05541班学生的学号、姓名和出生日期三个字段

的记录，要求按姓名的降序排序*/

SELECTS_NUMBER,S_NAME,BIRTHDAY

FROMT_STUDENT

WHERESUBSTRING（T_STUDENT.S_NUMBER,1,5）='05541'

ORDERBYS_NAMEDESC

GO

/*下面的SQL语句完成在T_STUDENT表中查询

B04511班性别为男的学生记录*/

SELECT*FROMT_STUDENT

WHERESUBSTRING（T_STUDENT.S_NUMBER,1,6）='B04511'

ANDSEX='男'

GO

--下面的SQL语句完成在T_COURSE表中

--插入一条新的记录

INSERTINTOT_COURSE/*此表共有四个字段：

C_NUMBER,C_NAME,HOURS,CREDIT*/

（C_NUMBER,C_NAME,HOURS,CREDIT）

VALUES

（'','Pascal程序设计',72,3）

GO

--修改上面插入的记录

UPDATET_COURSE

SETHOURS=64,

Credit=2.5

WhereC_NUMBER=''

例6-3-1：

创建了一个变量@CurrentDateTime，然后将GETDATE（）函数的值放在变量中，最后输出@CurrentDateTime变量的值。

程序清单如下：

--声明变量@CurrentDateTime

DECLARE@CurrentDateTimechar（30）

--给变量@CurrentDateTime赋值

SELECT@CurrentDateTime=GETDATE（）

--显示变量@CurrentDateTime的值

SELECT@CurrentDateTimeAS'当前的日期和时间'

GO

注意：

变量只在定义它的批处理中有效，因此，在上例中的程序中间不能写入GO语句。

例6-3-2：

查询T_STUDENT表，将返回的记录数赋给变量@RowsReturn。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--声明变量

DECLARE@RowsReturnint

--给变量赋值

SET@RowsReturn=（SELECTCOUNT（*）FROMT_STUDENT）

--显示变量的值

SELECT@RowsReturnAS'SELECT返回的记录数'

GO

例6-3-3：

在SELECT语句中使用由SET赋值的变量。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--声明变量

DECLARE@StuSexchar

（2）

--给变量赋值

SET@StuSex='女'

--根据变量@StuSex的值进行查询

SELECTS_NUMBER,S_NAME,SEX,BIRTHDAY

FROMT_STUDENT

WHERESEX=@StuSex

GO

例6-3-4：

在T_SCORE表中，求05541班学生高等数学课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--声明变量

DECLARE@MaxScorereal,@MinScorereal

--给变量赋值

SELECT@MaxScore=MAX（SCORE）,

@MinScore=MIN（SCORE）

FROMT_SCORE,T_COURSE

WHERET_SCORE.C_NUMBER=T_COURSE.C_NUMBER

ANDSUBSTRING（T_SCORE.S_NUMBER,1,5）='05541'

ANDT_COURSE.C_NAME='高等数学'

--根据变量@MaxScore和@MinScore的值进行查询，

--查询学生的学号、姓名、课程名称、考试分数

SELECTT_SCORE.S_NUMBER,S_NAME,C_NAME,SCORE

FROMT_STUDENT,T_COURSE,T_SCORE

WHERET_STUDENT.S_NUMBER=T_SCORE.S_NUMBER

ANDT_SCORE.C_NUMBER=T_COURSE.C_NUMBER

AND（

SCORE=@MaxScore

OR

SCORE=@MinScore

）

GO

例6-4-1：

计算表达式的值，并将结果赋给变量@ExpResult。

程序清单如下：

--定义变量

DECLARE@ExpResultnumeric

--给变量赋值

SET@ExpResult=67%31

--显示变量的值

SELECT@ExpResultAS'表达式计算结果'

例6-4-2：

查询05541班的学生信息，要求列出的字段为：

班级、本班内的学号、姓名、性别、出生日期、政治面貌。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--查询05541班的学生信息

SELECT班级=SUBSTRING（S_NUMBER,1,5）,

本班内的学号=SUBSTRING（S_NUMBER,6,7）,

姓名=S_NAME,

性别=SEX,

出生日期=BIRTHDAY,

政治面貌=POLITY

FROMT_STUDENT

WHERESUBSTRING（S_NUMBER,1,5）='05541'

GO

例6-4-3：

使用位运算符计算表达式的值。

程序清单如下：

--声明4个变量

DECLARE@ExpResult1int,

@ExpResult2int,

@ExpResult3int

--给变量赋值

SELECT@ExpResult1=20&12,

@ExpResult2=20|12,

@ExpResult3=20^12

--输出变量的值

SELECT@ExpResult1AS'位与运算结果',

@ExpResult2AS'位或运算结果',

@ExpResult3AS'位异或运算结果'

GO

例6-4-4：

使用比较运算符计算表达式的值。

程序清单如下：

--定义两个变量

DECLARE@Exp1integer,

@Exp2integer

--给变量赋值

SET@Exp1=30

SET@Exp2=50

--根据两个变量的值输出结果

IF@Exp1>@Exp2

PRINT'第1个变量的值大于第2个变量的值。

'

ELSEIF@Exp1=@Exp2

PRINT'第1个变量的值等于第2个变量的值。

'

ELSE

PRINT'第1个变量的值小于第2个变量的值。

'

GO

例6-4-5：

使用比较运算符计算表达式的值。

程序清单如下：

--定义两个变量

DECLARE@Exp1integer,

@Exp2integer

--给变量赋值

SET@Exp1=30

SET@Exp2=50

--根据两个变量的值输出结果

--与运算结果

IF@Exp1>@Exp2AND@Exp1<@Exp2

PRINT'@Exp1>@Exp2AND@Exp1<@Exp2的结果值为真。

'

ELSE

PRINT'@Exp1>@Exp2AND@Exp1<@Exp2的结果值为假。

'

--或运算结果

IF@Exp1>@Exp2OR@Exp1<@Exp2

PRINT'@Exp1>@Exp2OR@Exp1<@Exp2的结果值为真。

'

ELSE

PRINT'@Exp1>@Exp2OR@Exp1<@Exp2的结果值为假。

'

GO

例6-4-6：

使用字符串连接运算符计算表达式的值。

程序清单如下：

--定义变量

DECLARE@ExpResultchar（60）

--对变量赋值

SELECT@ExpResult='河北省廊坊市'+'北华航天工业学院'+'计算机软件教研室'

--输出变量的值

SELECT@ExpResultAS'字符串的连接结果'

GO

例6-4-7：

使用括号改变表达式的计算顺序。

程序清单如下：

--定义变量

DECLARE@ExpResultint

--给变量赋值

SELECT@ExpResult=100+（20-5）*2/3

--输出变量的值

SELECT@ExpResultAS'计算结果'

GO

例6-5-1：

从STUDENT数据库中返回T_STUDENT表的第4列的名称。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--使用系统函数求第4列的名称

SELECTCOL_NAME（OBJECT_ID（'T_STUDENT'）,4）

AS'T_STUDENT表中第4列的名称'

例6-5-2：

从STUDENT数据库中返回T_STUDENT表的S_NUMBER列的长度。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--使用系统函数求S_NUMBER列的长度

SELECTCOL_LENGTH（'T_STUDENT','S_NUMBER'）

AS'T_STUDENT表中的S_NUMBER列的长度'

例6-5-3：

检查sysdatabases中的每一个数据库，使用数据库标识号来确定数据库名称。

程序清单如下：

USEmaster

SELECTdbid,DB_NAME（dbid）ASDB_NAME

FROMsysdatabases

ORDERBYdbid

例6-5-4：

在T_STUDENT表中先插入三条新记录，其中的POLITY字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示“群众”。

程序清单如下：

--打开数据库STUDENT

USESTUDENT

GO

--向T_STUDENT表中插入三条记录

INSERTINTOT_STUDENT（

S_NUMBER,S_NAME,SEX）

VALUES（

'Y','王志鹏','男'）

INSERTINTOT_STUDENT（

S_NUMBER,S_NAME,SEX）

VALUES（

'Y','周小娟','女'）

INSERTINTOT_STUDENT（

S_NUMBER,S_NAME,SEX）

VALUES（

'Y','李磊','男'）

--查询刚插入的记录信息

SELECTS_NUMBERAS学号,S_NAMEAS姓名,

SEXAS性别,POLITYAS政治面貌

FROMT_STUDENT

WHERESUBSTRING（S_NUMBER,1,6）='Y04522'

--将政治面貌字段的值为空的记录用'群众'代替NULL显示

SELECTS_NUMBERAS学号,S_NAMEAS姓名,

SEXAS性别,（ISNULL（POLITY,'群众'））AS政治面貌

FROMT_STUDENT

WHERESUBSTRING（S_NUMBER,1,6）='Y04522'

例6-5-5：

利用@@ERROR返回程序的错误代码。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--对T_COURSE表操作，在T_COURSE表中

--插入一条新的记录

INSERTINTOT_COURSE

（C_NUMBER,C_NAME,HOURS,CREDIT）

VALUES

（'','C语言程序设计',72,3）

--输出此时错误代码的值

SELECT@@ERRORAS'第一次插入记录时的错误代码'

GO

/*在T_COURSE表中再插入一条新的记录,

但课程号与上面插入的记录相同,

输出此时错误代码的值*/

INSERTINTOT_COURSE

（C_NUMBER,C_NAME,HOURS,CREDIT）

VALUES

（'','VB程序设计',64,2.5）

--输出此时错误代码的值

SELECT@@ERRORAS'第二次插入记录时的错误代码'

GO

例6-5-6：

显示到当前日期和时间为止试图登录SQLServer的次数。

程序清单如下：

SELECTGETDATE（）AS'今天的日期和时间',

@@CONNECTIONSAS'企图登录的次数'

例6-5-7：

返回在程序执行过程中上一条SQL语句影响的记录数。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--查询T_STUDENT表中的记录

SELECT*

FROMT_STUDENT

--查询上一条SELECT语句影响的记录数

SELECT@@ROWCOUNTAS'第一次查询返回的记录数'

GO

--查询T_STUDENT表中性别为男的记录

SELECT*

FROMT_STUDENT

WHERESEX='男'

--查询上一条SELECT语句影响的记录数

SELECT@@ROWCOUNTAS'第二次查询返回的记录数'

GO

--对T_COURSE表操作，在T_COURSE表中

--插入一条新的记录

INSERTINTOT_COURSE

（C_NUMBER,C_NAME,HOURS,CREDIT）

VALUES

（'','VB程序设计',72,3）

--查询上一条INSERT语句影响的记录数

SELECT@@ROWCOUNTAS'第一次插入记录时影响的记录数'

GO

--对T_COURSE表操作，在T_COURSE表中

--插入一条新的记录，

--但课程号与前面插入的记录相同

INSERTINTOT_COURSE

（C_NUMBER,C_NAME,HOURS,CREDIT）

VALUES

（'','VC程序设计',64,2.5）

--查询上一条INSERT语句影响的记录数

SELECT@@ROWCOUNTAS'第二次插入记录时影响的记录数'

GO

例6-5-8：

返回SQLServer安装的日期、版本和处理器类型。

程序清单如下：

SELECT@@VERSIONAS'当前的SQLServer信息'

例6-5-9：

使用RTRIM函数删除字符型字段中的尾部空格。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

--使用字符串函数RTRIM去掉尾部空格

SELECTCOL_LENGTH（'T_COURSE','C_NAME'）AS'课程名称列的长度',

C_NAMEAS课程名称,

DATALENGTH（RTRIM（C_NAME））AS'课程名称列的实际数据长度'

FROMT_COURSE

例6-5-10：

使用SUBSTRING函数、LEFT函数和RIGHT函数求T_STUDENT中的班级名称和学号。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--使用字符串函数

SELECTSUBSTRING（S_NUMBER,1,LEN（S_NUMBER）-2）AS'班级',

RIGHT（RTRIM（S_NUMBER）,2）AS学号,

S_NAMEAS姓名,

SEXAS性别,

BIRTHDAYAS出生日期,

POLITYAS政治面貌

FROMT_STUDENT

WHERELEFT（S_NUMBER,5）='05541'

例6-5-11：

在第一个字符串（GoodMorning）中删除从第6个位置（字符M）开始的7个字符，然后在删除的起始位置插入第二个字符串（Afternoon），创建并返回一个新的字符串。

程序清单如下：

--定义变量@varString

DECLARE@varStringchar（30）

--给变量赋值

SELECT@varString=STUFF（'GoodMorning',6,7,'Afternoon'）

--显示变量的值

SELECT@varStringAS'返回的字符串'

例6-5-12：

查询T_STUDENT表的学生信息，要求显示的字段为：

学号、姓名、性别和学生的年龄。

程序清单如下：

USESTUDENT

GO

SELECTS_NUMBERAS学号,

S_NAMEAS姓名,

SEXAS性别,

YEAR（GETDATE（））-YEAR（BIRTHDAY）AS年龄

FROMT_STUDENT

例6-5-13：

在T_STUDENT表中查询每个学生的出生月份。

程序清单如下：

USESTUDENT

GO

SELECTS_NUMBERAS学号,

S_NAMEAS姓名,

SEXAS性别,

DATEPART（Month,BIRTHDAY）AS出生月份

FROMT_STUDENT

例6-5-14：

从系统当前日期中返回系统当前的年份数、月份数和天数。

程序清单如下：

SELECTYEAR（GETDATE（））AS当前年份,

MONTH（GETDATE（））AS当前月份,

DAY（GETDATE（））AS当前天数

例6-5-15：

在同一表达式中使用CEILING（），FLOOR（），ROUND（）函数。

程序清单如下：

SELECTCEILING（28.6）AS'CEILING（28.6）的值',

FLOOR（28.6）AS'FLOOR（28.6）的值',

ROUND（37.2364,3）AS'ROUND（37.2364,3）的值'

例6-5-16：

查询T_STUDENT表中出生年份为1985年的学生信息。

程序清单如下：

USESTUDENT

GO

SELECTS_NUMBERAS学号,

S_NAMEAS姓名,

SEXAS性别,

BIRTHDAYAS出生日期,

POLITYAS政治面貌

FROMT_STUDENT

WHERECONVERT（char（40）,BIRTHDAY,102）LIKE'1985%'

例6-5-17：

编写一个用户自定义函数fun_SumScores，要求根据输入的班级号和课程号，求此班此门课程的总分。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO

--创建用户自定义函数fun_SumScores

CREATEFUNCTIONfun_SumScores

（

@ClassNameASchar（10）,

@CourseNumberASchar（10）

）

RETURNSreal

BEGIN

DECLARE@SumResultASreal

SELECT@SumResult=SUM（SCORE）

FROMT_SCORE

WHERESUBSTRING（S_NUMBER,1,LEN（S_NUMBER）-2）=@ClassName

ANDC_NUMBER=@CourseNumber

GROUPBYSUBSTRING（S_NUMBER,1,LEN（S_NUMBER）-2）

RETURN@SumResult

END

GO

例6-5-18：

使用自定义函数fun_SumScores，求T_SCORE表中的各个班级的各门课程的总分。

--打开STUDENT数据库

USESTUDENT

GO

--使用自定义函数fun_SumScores求总分

SELECTDISTINCT

班级名称=SUBSTRING（S_NUMBER,1,LEN（S_NUMBER）-2）,

课程名称=T_COURSE.C_NAME,

总分=dbo.fun_SumScores（

SUBSTRING（S_NUMBER,1,LEN（S_NUMBER）-2）,

T_SCORE.C_NUMBER

）

FROMT_SCORE,T_COURSE

WHERET_SCORE.C_NUMBER=T_COURSE.C_NUMBER

例6-6-1：

根据T_SCORE表中的考试成绩，查询05541班学生高等数学的平均成绩，并根据平均成绩输出相应的提示信息。

程序清单如下：

USESTUDENT

GO

IF（SELECTAVG（SCORE）FROMT_SCORE

WHERELEFT（S_NUMBER,5）='05541'

ANDC_NUMBER=''）

>75

BEGIN

PRINT'05541班高等数学的平均考试成绩比较理想！

'

PRINT''

SELECTAVG（SCORE）AS平均考试成绩

FROMT_SCORE

WHERELEFT（S_NUMBER,5）='05541'

ANDC_NUMBER=''

END

ELSE

BEGIN

PRINT'05541班高等数学的平均考试成绩不太理想！

'

PRINT''

SELECTAVG（SCORE）AS平均考试成绩

FROMT_SCORE

WHERELEFT（S_NUMBER,5）='05541'

ANDC_NUMBER=''

END

例6-6-2：

查询05541班学生的考试情况，并使用CASE语句将课程号替换为课程名进行显示。

程序清单如下：

USESTUDENT

GO

SELECTS_NUMBERAS学号,

课程名称=CASEC_NUMBER

WHEN''THEN'高等数学'

WHEN''THEN'关系型数据库原理'

END

SCOREAS考试分数

FROMT_SCORE

WHERELEFT（S_NUMBER,5）='05541'