数据库原理实验指导书

① 使用系统存储过程sp_help查看grade表的外键信息。

② 在grade表中插入一条记录，学号为0100，课程编号为0001，分数为78。观察SQL Server会做何处理，为什么？如何解决所产生的问题？

③ 使用查询设计器删除grade表的外键fk_sid。 3．实验思考

（1）在SQL Server 2008中，可采用哪些方法实现数据完整性? （2）比较默认对象和默认约束的异同。

（3）可以使用SQL Server管理平台创建规则和默认值对象吗？

（4）在数据库中建立的规则不绑定到数据表的列上会起作用吗？为什么？ （5）请说明唯一约束和主键约束之间的联系和区别。

17

数据库原理实验指导书

实验7 Transact-SQL程序设计

1．实验目的

（1）掌握Transact-SQL的数据类型、常量变量、表达式等的概念和使用方法。 （2）掌握程序中注释的基本概念和使用方法。 （3）掌握程序中的流程控制语句的使用方法。

（4）掌握SQL Server 2008中常用函数的用法。

（5）掌握游标的概念和声明方法，以及使用游标进行数据的查询、修改、删除操作等。 2．实验内容及步骤

（1）选择studentsdb数据库，打开新建查询编辑器，输入以下代码。

DECLARE @stu_name varchar(10) SELECT @stu_name=姓名 FROM student_info WHERE 姓名 LIKE '张%'

观察显示的结果，与student_info表中数据进行比较，@stu_name赋值的是SELECT结果集中的哪个数据？

（2）定义int型局部变量@grademax、@grademin、@gradesum，在grade表中查找最高分、最低分和总分，分别赋给@grademax、@grademin和@gradesum，并显示。

DECLARE @grademax int,@grademin int,@gradesum int

SELECT @grademax=max(分数),@grademin= min(分数),@gradesum=sum(分数) FROM grade

SELECT @grademax,@grademin,@gradesum

SELECT @stu_name

（3）使用SET命令将查询结果集记录数目赋值给int型局部变量@row。给下面代码中的划线处填上适当的内容，以完成上述操作。

DECLARE @rows

SET =(SELECT COUNT(*) FROM grade)

（4）以下代码在curriculum表中插入新记录：

DECLARE @intCId int，@intErrorCode int

INSERT INTO curriculum(课程编号,课程名称,学分)

VALUES('0006','VB程序设计',2)

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

@rows --显示@rows的值

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

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

（6）在grade表中，使用适当函数找出“高等数学”课程的最高分、最低分和平均分。

18

SELECT @intCId,@intErrorCode

数据库原理实验指导书

（7）定义一个datetime型局部变量@studate，以存储当前日期。计算student_info表中的学生的年龄，并显示学生的姓名、年龄。在以下代码的划线部分填入适当内容，以实现上述功能。

DECLARE datetime

SET @studate= --给@studate赋值为当前日期 SELECT 姓名, (@studate)-year(出生日期) AS 年龄

（8）运行以下代码，写出运行结果。

DECLARE @a int,@b int SET @a=168 SET @b=73

FROM student_info

（9）在局部变量@stu_id中存储了学号值。编写代码查询学号为0001的学生的各科平均成绩，如果平均分?60，则显示“你的成绩及格了，恭贺你 ！！”，否则显示“你的成绩不及格”。

IF ((SELECT AVG(分数) FROM grade WHERE 学号='0001')<60)

PRINT '你的成绩不及格'

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

SELECT @a & @b,@a|@b,@a^@b

（10）运行以下代码段，写出运行的结果。

DECLARE @counter int SET @counter=1 WHILE @counter<10

BEGIN SELECT '@counter的值现在为： '+CONVERT(CHAR(2),@counter) SET @counter=@counter+1 END

（11）查询grade表。如果分数大于等于90，显示A； 如果分数大于等于80小于90，显示B；如果分数大于等于70小于80，显示C；如果分数大于等于60小于70，显示D； 其他显示E。在以下代码的划线部分填入适当内容完成上述功能。

SELECT 学号,分数,等级= CASE

分数>=90 THEN 'A'

WHEN 分数>=80 AND 分数<90 'B' WHEN 分数>=70 AND 分数<80 THEN 'C' WHEN 分数>=60 AND 分数<70 THEN ELSE 'E' END

FROM grade

（12）计算grade表的分数列的平均值。如果小于80，则分数增加其值的5%；如果分数的最高值超过95，则终止该操作。在以下代码划线处填入适当的内容以完成上述功能。

WHILE (SELECT (分数) FROM grade)<80 BEGIN

19

数据库原理实验指导书

UPDATE grade

SET 分数=分数*1.05

if (SELECT MAX(分数) FROM grade)> BREAK ELSE END

（13）编写代码计算并显示@n=1+2+3+?+20。

（14）编写代码计算并显示1~100之间的所有完全平方数。例如，81=92，则称81为完全平方数。

（15）计算1~100以内的所有素数。

（16）在studentsdb数据库中，使用游标查询数据。 ① 打开查询设计器，声明一个stu_cursor游标，要求返回student_info表中性别为“男”的学生记录，且该游标允许前后滚动和修改。

② 打开stu_cursor游标。 ③ 获取并显示所有数据。 ④ 关闭该游标。

（17）使用游标修改数据。 ① 打开stu_cursor游标。

② 将姓马的男同学的出生日期的年份加1。 ③ 关闭stu_cursor游标。

（18）声明游标变量@stu_c，使之关联stu_cursor游标，利用@stu_c查询年龄在6～9月份出生的学生信息。

（19）使用系统存储过程sp_cursor_list显示在当前作用域内的游标及其属性。

3．实验思考

（1）Transact-SQL语言的运算符主要有哪些？

（2）流程控制语句与其他编程语言提供的语句有何差别？ （3）区分局部变量与全局变量的不同，思考全局变量的用处。 （4）什么函数能将字符串前和尾的空格去掉？

（5）使用什么语句可以打开游标？打开成功后，游标指针指向结果集的什么位置？

20