数据库实验7.docx

数据库实验7.docx

《数据库实验7.docx》由会员分享，可在线阅读，更多相关《数据库实验7.docx（12页珍藏版）》请在冰豆网上搜索。

数据库实验7

数据库技术与应用

实验报告

实验名称：

Transact-SQL程序设计

系（科）：

信息系

班级：

学号：

姓名：

完成时间：

2012-6-7

南京师范大学中北学院

实验7Transact-SQL程序设计

一、实验目的

1.掌握Transact-SQL的数据类型、常量变量、表达式等概念。

2.掌握SQLServer2005中常用函数的用法。

3.掌握程序中注释的基本概念和使用方法。

4.了解程序中的流程控制语句。

二、实验准备

1．了解函数的使用方法。

2．了解系统提供的常用数学函数、日期和时间函数、字符串函数和数据类型转换函数的用法。

3．了解程序中注释的语法格式。

4．了解程序中的流程控制语句：

IF-ELSE、CASE、WHILE等控制流语句。

三、实验内容和步骤

1．在查询分析器中，选择studentsdb数据库，在学生表中查找姓“张”的学生，并将该生姓名赋于变量@stu_name。

DECLARE@stu_namechar（8）

SELECT@stu_name=姓名

FROM学生表

WHERE姓名LIKE'张%'

SELECT@stu_name

提示：

首先要定义变量@stu_name。

2．定义int型局部变量@grademax、@grademin、@gradesum，在成绩表中查找课程编号是“C002”课程的最高分、最低分和总分，分别赋给@grademax、@grademin和@gradesum，并显示。

DECLARE@grademaxint,@grademinint,@gradesumint

SELECT@grademax=Max（分数）,@grademin=Min（分数）,@gradesum=Sum（分数）

FROM成绩表

WHERE课程编号='0002'

SELECT@grademax,@grademin,@gradesum

3.使用SET命令将查询结果集记录数目赋值给int型局部变量@row。

给下面代码中的划线处填上适当的内容，以完成上述操作。

DECLARE@rows_____int______

SET___@rows_________=（SELECTCOUNT（*）FROM成绩表）

_SELECT_______@rows--显示@rows的值

4．以下代码在课程表中插入新记录：

DECLARE@intCIdint,@intErrorCodeint

INSERTINTO课程表（课程编号,课程名称,学分）

VALUES（'0006','VB程序设计',2）

SELECT@intCId=@@identity,@intErrorCode=@@error

SELECT@intCId,@intErrorCode

将该代码段连续执行两次，观察两次显示的信息及课程表中数据的变化，为什么前后两次执行时显示的信息会不同?

提示：

@@identity,@@error参看教材P172表9-2

首先两次显示的结果都是如上图所示，在课程表中的结果是如下图第一次增加了一行，第二次增加了同样的一行。

5．在studentsdb数据库的学生表中，以“性别”为分组条件，分别统计男生和女生人数。

提示：

定义两个变量，用SET分别赋值。

DECLARE@maleint,@femaleint

SET@male=（SELECTCOUNT（*）FROM学生表

WHERE性别='男'）

SET@female=（SELECTCOUNT（*）FROM学生表

WHERE性别='女'）

SELECT@male,@female

6．在成绩表中，使用适当函数找出“高等数学”课程的最高分、最低分和平均分，并分别赋予@grademax、@grademin、@gradeavg。

DECLARE@grademaxint,@grademinint,@gradeavgint

SELECT@grademax=Max（分数）,@grademin=Min（分数）,@gradeavg=Avg（分数）

FROM成绩表

WHERE课程编号=（SELECT课程编号FROM课程表

WHERE课程名称='高等数学'）

SELECT@grademax,@grademin,@gradeavg

7．定义一个datetime型局部变量@studate，以存储当前日期。

计算学生表中的学生的年龄，并显示学生的姓名、年龄。

在以下代码的划线部分填人适当内容，以实现上述功能。

DECLARE_@studate____datetime

SET@studate=___GETDATE（）_______--给@studate赋值为当前日期

SELECT姓名，_YEAR（GETDATE（））-YEAR（出生日期）__AS年龄

FROM学生表

提示：

计算年龄可参考教材表9-9和9-10相关函数。

8．请用单行注释将下列命令行所实现的功能予以注释（填入括号内）。

（--查找学生表中姓陈的人的姓名与家庭住址）

SELECT姓名,家庭地址

FROM学生表

WHERE姓名LIKE'陈%‘

9.请用多行注释将下列命令行所实现的功能予以注释（填入括号内）。

（/*查找学生表中80后学生的姓名、性别、出生年月和家庭住址情况

*/）

SELECT姓名,性别，出生年月，家庭地址

FROM学生表

WHEREYEAR（出生年月）>80

10.在局部变量@stu_id记中存储了学号值。

编写代码查询学号为0001的学生的各科平均成绩，如果平均分>=60则显示“你的成绩及格了，恭贺你!

!

”，否则显示“你的成绩不及格”。

IF（（SELECTAVG（分数）FROM成绩表WHERE学号='0001'）<60）

PRINT'你的成绩不及格'

ELSE

PRINT'你的成绩及格了,恭贺你!

'

11．运行以下代码段，写出运行的结果。

12．编写程序查询成绩表。

如果分数大于等于90，显示优秀；如果分数大于等于80小于90显示良好；如果分数大于等于70小于80显示中等；如果分数大于等于60小于70显示及格，其他显示不及格。

SELECT学号,分数,等级=CASE

WHEN分数>=90THEN'优秀'

WHEN分数>=80AND分数<90THEN'良好'

WHEN分数>=70AND分数<80THEN'中等'

WHEN分数>=60AND分数<70THEN'及格'

ELSE'不及格'

END

FROM成绩表

13．计算grade表的分数列的平均值。

如果小于80，则分数增加其值的5％；如果分数的最高值超过95，则终止该操作。

在以下代码划线处填入适当的内容以完成上述功能。

WHILE（SELECTAVG（分数）FROM成绩表）<80

BEGIN

UPDATE成绩表

SET分数=分数*1.05

IF（SELECTMAX（分数）FROM成绩表）>95

BREAK

ELSE

CONTINUE

END

四、实验思考

1．编写代码计算并显示@n=1+2+3+…+20。

代码：

DECLARE@sint,@nint,@tint,@cint

SET@n=0

SET@s=1

SET@c=1

SET@t=0

WHILE@s<=20

BEGIN

SET@t=@t+@c

SET@c=@c+1

SET@s=@s+1

END

SET@n=@n+@t

SELECT@n

2．区分局部变量与全局变量的不同，思考全局变量的用处。

答：

局部变量使用DECLARE语句定义，仅存在于声明它的批处理、存储过程或触发器中，处理结束后，存储在局部变量中的信息将丢失。

通常局部变量的首字母为单个@。

全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息，不能显示地被赋值或声明。

全局变量由系统维护，名称前常加@@符号。

全局变量的作用：

通常被服务器用来跟踪服务器范围和特定会话期间的信息。