《数据库开发技术》实验指导书(计本10)-唐军芳

浙江树人大学信息科技学院

《数据库开发技术》

实 验 指 导 书

适合专业：计算机科学与技术本科专业

编写部门：电子商务教研室

编写日期：2012.09

实验一：T-SQL高级查询 ..................................................... 1 实验二：数据完整性测试 .................................................... 7 实验三：T-SQL编程结构 ..................................................... 9 实验四：游标和事务设计 .................................................. 11 实验五：实验六：存储过程的创建和使用实验七：触发器的创建SQL Server函数的使用 ..................................... 13 ...................................... 15 ...................................................... 16

实验一：T-SQL高级查询

一、 实验目的

通过实验使学生掌握T-SQL高级查询。本次实验让学生学会掌握多表查询的思想，使用UNION子句，以及熟悉统计函数的用法；让学生学会使用group by子句，compute和compute by子句，以及掌握较为复杂的嵌套查询的思想。

二、 原理解析 1、 多表查询

在以前的课程中，所使用的查询局限于一张表格中，但在更多的情况下，需要对多张表格中的数据同时进行查询，这是可以把多张表格的名字全部填写在FROM子句中。在使用多表查询时需要注意的是如何避免笛卡尔积的出现。

2、 使用UNION子句

如果有多个不同的查询结果数据集合，但又希望将他们连接在一起组成一组数据。这组数据是这多个结果集合的逻辑联合，在这种情况下，可以使用UNION子句。在UNION子句的使用中，有两条基本原则：

1)、每一个结果集的数据类型都必须相同，更确切地说是兼容； 2)、每一个结果集中列的数量都必须相等，排列顺序必须相互对应。

3、 使用统计函数

为了有效的处理使用SQL查询得到的数据集合，SQL Server提供了一系列统计函数。这些函数把存储在数据库中的数据描述为一个整体而不是一行行孤立的记录。通过使用这些函数可以实现对数据集合汇总，求平均值等各种运算。

4、 使用group by 子句

在大多数情况下使用统计函数，返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就需要使用group by子句了。需要注意的是在group by子句中不支持对列分配的别名也不支持任何使用了统计函数的集合列。另外，Select后面每一列数据除了出现在统计函数中的列以外都必须在group by子句中应用。

5、 使用compute 和compute by子句

使用compute子句，允许同时观察查询所得的各列数据的细节以及统计各列数据所产生的总和。通过使用compute子句既可以计算数据分类后的和，也可以计算所有数据的总和。需要注意的是，从返回的查询结果来看，compute子句和group by子句非常类似。但

1

是两者之间存在着较大的区别，使用group by子句只能产生一个结果集合；使用compute可以返回多种结果集。

6、 使用嵌套查询

在以前的实验中，我们所使用的查询都是单层查询，但在实际应用中经常要应用到嵌套查询。嵌套查询要求服务器在处理最终查询工作之前先生成一个结果，然后根据当前的查询结果再进一步继续下面的查询工作。但嵌套查询返回的结果作为查询条件等号右边的值存在时之允许嵌套查询返回一行结果，否则系统出错。

三、 实验内容

1、 2、 3、 4、

查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩情况； 查询学生信息数据表中所有学生的学号、姓名、院系名称和院系编号； 查询每门课程名称及该门课的任课教师的姓名、编号；

在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的基

本信息和成绩； 5、 6、 7、 8、 9、 10、 11、 12、 13、 14、

查询所学专业为“计算机控制技术”或年龄为21岁的所有学生的姓名； 查询计算机工程系全体教师的平均工资； 求计算机工程系教师的平均年龄； 求计算机工程系教师的总人数。

统计计算机工程系各个专业的学生的平均入学成绩； 在学生成绩表中统计各门课程的人数；

在教师信息表中，按职称分组统计“教授”或“副教授”的工资总额； 按职称统计各个教研室的教师人数；

对teacher_info中职称为“讲师”的教师工资生成汇总行和明细行；

对teacher_info中职称为“讲师”或“助教”的教师工资，按照其职称生成分组汇总

行和明细行； 15、 16、 现）； 17、

查询课程号为“0401010106”的课程的成绩在80～89分的学生的学号、姓名（用查询工资高于“孙乐多”的所有老师的编号、姓名、性别及工资（用子查询实现）； 在学生成绩表中查询课程类型为“考试”的学生的学号、姓名、成绩（用子查询实

exists实现）。 18、

完成习题中的相应内容。

2

四、 实验步骤

1、查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩情况： select from where

2、查询学生信息数据表中所有学生的学号、姓名、院系名称和院系编号： select from where substring（ ）=

3、查询每门课程名称及该门课的任课教师的姓名、编号： select from where

4、 在Stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的基本信息和成绩： select *

from stud_info stud_grade on order by stud_info.stud_id

5、 查询所学专业为“计算机控制技术”或年龄为21岁的所有学生的姓名： select stud_id, name from where union

select stud_id, name from stud_info where DATEDIFF）（ ）=21

6、 查询计算机工程系全体教师的平均工资：

select as 计算机工程系教师平均工资

3

from teacher_info where left（ ）=

（select deptcode from dept_code where deptname=’计算机工程系’）

7、 求计算机工程系教师的平均年龄： select as 平均年龄 from teacher_info where left（ ）=

（select deptcode from dept_code where deptname=’计算机工程系’）

8、 求计算机工程系教师的总人数： select from teacher_info where left（ ）=

（select deptcode from dept_code where deptname=’计算机工程系’）

9、 统计计算机工程系各个专业的学生的平均入学成绩： select from where substring（stud_id,5,2） 10、

在学生成绩表中统计各门课程的人数：

select from 11、

在教师信息表中，按职称分组统计“教授”或“副教授”的工资总额：

select from

4

12、

按职称统计各个教研室的教师人数：

select tech_title 职称， from teacher_info

group by 13、

对teacher_info中职称为“讲师”的教师工资生成汇总行和明细行：

select tech_title,salary from teacher_info

where order by tech_title 14、

对teacher_info中职称为“讲师”或“助教”的教师工资，按照其职称生成分组汇

总行和明细行：

select tech_title,salary from teacher_info

where order by tech_title

15、

查询工资高于“孙乐多”的所有老师的编号、姓名、性别及工资：

select from teacher_info

where 16、

在学生成绩表中查询课程类型为“考试”的学生的学号、姓名、成绩：

select

5

from teacher_info

where 17、

查询课程号为“0401010106”的课程的成绩在80～89分的学生的学号、姓名（用

exists实现）：

select from stud_info where exists

6

实验二：数据完整性测试

一、实验目的

通过实验使学生理解数据库设计中的数据完整性概念，通过学习教程，掌握和熟悉实施数据完整性的途径，本章实施数据完整性的途径包括约束、规则、默认值、标识列。

二、原理解析

数据的完整性是指存储在数据库中数据的正确性和相容性。设计数据库完整性的目的是为了防止数据库中存在不符合语义的数据，防止错误信息的输入和输出。

1、 使用约束实施数据完整性

约束的用途是检查输入到表中的值的范围，SQL Server提供的约束主要有：主键约束、外键约束、惟一约束、检查约束、NULL约束、CASCADE级联引用一致性约束等。

2、 使用规则

规则是一组使用T-SQL书写的条件语句，它可以和列或者是用户自定义数据类型捆绑在一起。当用户向绑定有规则的数据列上插入或修改值时，规则会检测修改的完整性。

3、 使用默认值

SQL Server提供的默认值有助于处理用户不含全部表列的INSERT操作。使用方法类似于规则。

4、 使用标识列

定义为标识列属性的列不必在插入新行时为其赋值，因为服务器会自动为新增加的行中的Identity列设置一个唯一的行序列号。

三、 实验内容

1、 在企业管理中为表stud_info中的学生性别列（GENDER）创建一CHECK约束，使得该列的值只能是“男”或“女”；

2、 使用T-SQL语句为stud_info创建一个名为“CK_stud_info”的CHECK约束； 3、 使用T-SQL为表stud_info船舰一个约束名为“DE_gender”的DEFAULT约束，要求性别（gender）的默认值为“男”；

4、 使用T-SQL语句为表stud_info中的学号（stud_id）定义名为“PK_XH”的主键约束；5、分别用企业管理器和T-SQL语句为表stud_grade和表lesson_info建立外键约束； 6、用T-SQL语句为表stud_info的“电话号码”列创建名为UN_telcode的UNIQUE约束；

7

7、用T-SQL语句在数据库student中创建默认MR_GRADE，并将其绑定到表stud_info中的学生总分（mark）列上，从而实现每名学生的总分默认值为560分；

8、用T-SQL语句在数据库student中创建一个规则GZ_GRADE，并将其绑定到表stud_grade中的学生成绩（grade）列上，使得用户输入的成绩在0～100的范围内，否则提示输入无效。 9、完成习题中的相应内容。

8

实验三：T-SQL编程结构

一、实验目的

通过实验使学生掌握更复杂的T-SQL查询的应用规则，如需要循环、判断才能表达清楚的查询过程。 二、原理解析 1、批处理

批处理是一个或多个Transact-SQL语句的集合，从应用程序一次性发送到SQL Server并由SQL Server编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条。 2、变量

分局部变量和全局变量，局部变量以@打头，全局变量以@@打头。局部变量用DECLARE语句声明，用SET语句赋值，具体语法请参见帮助。 3、流程控制语句 ?BEGINN…END语句块 ?IF…ElSE语句 ?CASE语句 ?WAITFOR语句 ?WHILE语句 ?RETURN语句 三、实验内容

1、分析程序的运行结果 2、使用局部变量向表中插入数据 3、if…else语句的使用 4、case语句的使用 5、while语句的使用 实验步骤

1、执行下列语句，分析语句的执行结果：

9

use student

create view teacher_info_view as

select teacher_id,name,tech_title,salary from teacher_info go

select * from teacher_info go

2、分析下列语句的执行结果（局部变量的作用域）：

declare @stud_var int go

select @stud_var=25 go

print @stud_var go

3、使用局部变量，向表格stud_info插入一行数据（先声明变量，再赋值，最后将这些变量的值插入对应表格中），‘0401010705’、‘王小明’、‘1986-10-12’、‘男’、‘上海市杨浦区’、‘021-65872345’、‘201800’、‘560’。

4、使用if…else语句，从数据表stud_grade中读出学生“陈红”的成绩，将百分制转换成等级制，成绩在90分到100分之间等级为A，80分以上为B，70分以上为C，60分以上为D，60分以下为E。

5、使用CASE语句实现从数据表stud_info中，选取stud_id、gender，如果gender为“男”则输出“M”，如果为“女”则输出“F”。

6、使用while循环语句实现以下功能：求2~300之间的所有素数。

7、从stud_grade表中查询所有同学考试成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分输出“优秀”。

8、 使用CASE语句，输出教师基本信息表（teacher_info）中所有教师所在的年龄段（每6年划分一个段），并说明对应教师的职称。 9、编写计算n!（n=20）的T-SQL语句，并显示计算结果。 10、

完成习题中的相应内容。

10

实验四：游标和事务设计

一、实验目的

数据库游标是类似于C语言指针的语言结构。游标的功能比较复杂，要灵活使用游标需要花费较长的时间练习和积累经验。本次实验让学生掌握游标设计最基本和最常用的方法。

SQL Server作为典型的关系数据库，为事务控制提供了完善的编程结构。通过实验掌握事务的各种特性。 二、 原理解析 1、游标设计

通常情况下，数据库执行的大多数SQL命令都是同时处理集合内部的所有数据。但是，有时候用户也需要对这些数据集合中的每一行进行操作。在没有游标的情况下，这种工作不得不放到数据库前端，用高级语言来实现，这将导致不必要的数据传输，从而延长执行的时间。通过使用游标可以在服务器端有效的解决这个问题。

游标的使用一般可按顺序分为如下几个步骤： ? 声明游标

语法：DECLARE 游标名 [INSENSITIVE] [SCOROLL] CURSOR

FOR SELECT 语句

? 打开游标

语法：OPEN CURSOR_NAME ? 使用游标取数或修改数据

语法：FETCH COURSOR_NAME [INTO @VAR] ? 关闭游标

语法：CLOSE CURSOR_NAME ? 释放游标

语法：DEALLOCATE CURSOR_NAME 2、事务

事务处理控制语句有以下4个：

11

? BEGIN TRAN [TRAN_NAME] ? COMMIT TRAN ? ROLLBACK TRAN ? SAVE TRAN 三、实验内容和步骤

1、统计“多媒体技术”课程考试成绩的各分数段的分布情况，即100分的有己几人，90～100分有几人，80～90有几人，70～80有几人，60～70有几人，不及格有几人，用游标实现。

2、运用事务处理将student数据库中课程信息表lesson_info中的多媒体技术课程编号course_id由0401010106改为0401010116。

3、编写一个事务控制程序，要求在事务中包含3个操作：第一个操作是在student数据库的stud_info表中插入一条数据，并检索插入是否成刚，然后 设置一个保存点，紧接着执行第二个操作，删除刚才插入的数据，并检索删除是否成功，然后回滚事务到保存点，最后执行检索操作，看插入的数据是否还存在。

4、定义一个游标，将教师表teacher中所有教师的姓名、 教师职称显示出来。

5、通过游标将教师表teacher_info中记录号为5的老师的职称由改为“副教授”。

6、使用游标显示stud_info表中所有学生的基本信息。

7、通过游标实现：对于stud_info表中的每个学生如果在stud_grade表中有相应的选课记录则删除该生的选课记录。

8、通过游标实现显示stud_info表中所有男生的基本信息。 9、通过游标实现：统计学生人数。

10、为stud_info表添加一“备注”字段，某学生如果已选修了课程，则将“备注”字段改为“已选”。

11、用游标实现在查询出来的结果集中添加一个新列（有规律），即将stud_info表的STUD_ID、NAME列查询出来到一个新表，并且在新表中添加一列“序号”，用游标实现。

12、完成习题中的相应内容。

12

实验五：SQL Server函数的使用

一、实验目的

为了使用户对数据库进行查询和修改时更加方便，SQL Server在TSQL中提供了许多内部函数以供使用。本次使用掌握其中某些函数的使用方法和意义。SQL Server还提供了用户自定义函数，用户可以按照自己的意愿创建函数。

本次实验要求学生掌握用户自定义函数的创建和使用。 二、原理解析

SQL Server函数分为： 1、数学函数 2、字符串函数 3、日期函数 4、系统函数 5、统计函数 6、用户自定义函数

其中用户自定义函数又可分为标量函数、内嵌表值函数、多语句表值函数。 ?标量函数：返回单个数据值。

?内嵌表值函数：返回值是一个记录集合--表。在此函数中，return语句包含一条单独的select语句。

?多语句表值函数：返回值是由选择的结果构成的记录集。实验内容

1、创建一标量函数，要求：每次输入一个学号，计算该学生的所有课程的平均分，如果是85～100分，返回“优”；如果是75～84分，返回“良”；如果是65～74，返回“中”；如果是0～64，返回“差”。

2、创建一内嵌表值函数，要求：每次输入一个学号，返回学生选修的课程名和成绩。调用创建的函数查询学号为“0401020201”学生所选修的课程名和成绩。 3、用多语句表值函数实现查询某个学生的学号、姓名及选修门数。利用创建的函数查询学号为“0401020201”的学生的学号、姓名及选修门数。 4、在存储过程中调用用户自定义函数，实现求两个数的较大值。

13

5、利用标量函数和游标修改表数据。

6、创建一内嵌表值函数来替代视图，这个函数返回成绩在所有学生平均成绩之上的学生姓名和成绩。

7、用多语句表值函数实现根据某学生的学号，查询该生的姓名及选修门数。 8、用内嵌表值函数实现第7题。 9、完成习题中的相应内容。 实验步骤

1、创建一标量函数，要求：每次输入一个学号，计算该学生的所有课程的平均分，如果是85～100分，返回“优”；如果是75～84分，返回“良”；如果是65～74，返回“中”；如果是0～64，返回“差”。 查询学号为“0401020201”的学生的平均分的等级。

2、创建一内嵌表值函数，要求：每次输入一个学号，返回学生选修的课程名和成绩。调用创建的函数查询学号为“0401020201”学生所选修的课程名和成绩。 3、用多语句表值函数实现查询某个学生的学号、姓名及选修门数。利用创建的函数查询学号为“0401020201”的学生的学号、姓名及选修门数。 4、在存储过程中调用用户自定义函数，实现求两个数的较大值。

5、利用标量函数和游标修改表数据，要求：编写一标量函数以求得某学生的年龄，向stud_info表中添加一列“年龄”，利用游标修改每个学生的年龄。 6、创建一内嵌表值函数来替代视图，这个函数返回成绩在所有学生平均成绩之上的学生姓名和成绩。

7、用多语句表值函数实现根据某学生的学号，查询该生的姓名及选修门数。 8、用内嵌表值函数实现第7题。

14

实验六：存储过程的创建和使用

一、 实验目的

本次实验要求学生掌握用户自定义存储过程的创建和使用。 二、 原理解析

存储过程是一组预先编译好的T－SQL代码，可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。由于是已经编译好的代码，所以执行的时候不必再次进行编译，提高了效率。

创建存储过程语法： CREATE PROC PROC_NAME [WITH

{ } ]

AS SQL_STATEMENT [?N]实验内容 1、创建和执行不带参数的存储过程。

2、创建和执行带参数的存储过程。创建和执行带输出参数的存储过程。实验步

骤

1、针对教师基本信息表teacher_info，创建一个名称为teacher_proc1的存储过程，该存储过程的功能是从数据表teacher_info中查询所有男教师的信息。 2、创建一个名称为student_proc1的存储过程，该存储过程的功能是查询学生的姓名、所选修的课程名以及成绩。3、针对教师基本信息表teacher_info，创建一个名为teacher_proc2的存储过程，执行该存储过程将向数据表

teacher_info中插入一条记录，新记录的值由参数提供。针对教师基本信息表teacher_info，创建一个名为teacher_proc3的存储过程，执行该存储过程将向数据表teacher_info中插入一条记录，新记录的值由参数提供，如果未提供职称tech_title的值，则由参数的默认值提供。4、在数据库student上创建一个

RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION

15

名为stud_proc2的存储过程，其功能是输入两个日期型数据，并使用输出参数返回这两个出生日期之间的所有学生人数。

返回出生日期在1986年1月1日与1986年12月31日之间的学生记录的条数。 5、存储过程名为proc1，要求实现如下功能：根据学生学号，查询该学生的选课情况，其中包括该学生学号、姓名、课程号、课程名、成绩等。

6、定义存储过程proc2，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提

示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！”，否则显示“此学生综合成绩不合格！”。 7、完成习题中的相应内容。

实验七：触发器的创建

一、实验目的

通过实验使学生掌握触发器的创建和使用，理解如何使用触发器实现更复杂的数据约束。 二、原理解析

触发器是一种特殊的存储过程，它与表格紧密相连，可以看作是表格定义的一部分。当用户修改指定表或视图中的数据时，触发器将会自动执行。触发器基于一个表创建，但是可以针对多个表进行操作。

创建触发器语法：

CREATE TRIGGER TRIG_NAME ON TABLE|VIEW [WITH ENCRYPTION] {

{FOR|AFTER|INSTEAD OF} [NOT FOR REPLICATION] AS

SQL_STATEMENT }

[?N] [RETURN INTEGER_EXP]

16

三、实验内容

1、创建insert触发器。 2、创建update触发器。 3、创建delete触发器。 4、创建instead of触发器。 四、实验步骤

1、编写一个（teacher_insert）触发器，当向teacher_info表中插入一条记录时，激活触发器，检查记录的course_id是否存在于lesson_info表中，若不存在，则取消插入。 use student go

as

if begin

end

2、编写一个（teacher_insert1）触发器，当向teacher_info表中插入一条记录时，激活触发器，检查记录的course_id是否存在于lesson_info表中，若不存在，则将相应的课程信息在lesson_info表中插入。 use student go

as

17

if begin

declare @course_id char(10) insert into lesson_info

values( ,'数据库开发技术','考试',3,60) end

3、编写一个（teacher_update）触发器，当修改lesson_info表的course_id列值时，该列在teacher_info表中的对应值也作相应的修改。 use student go

as begin

select @course_id＝ update teacher_info from teacher_info,deleted

where end

4、编写一个（teacher_update1）触发器，当修改lesson_info表的course_id列值时，该列在teacher_info表中若有相应的值，则拒绝修改。 use student go

18

as

if begin

end

5、编写一个（teacher_deletee）触发器，当删除lesson_info表中的记录时，也将同时删除该记录course_id列值在teacher_info表中的对应记录。 use student go

as

from deleted,teacher_info

6、编写一个（teacher_delete1）触发器，当删除lesson_info表的course_id列值时，该列在teacher_info表中若有相应的值，则拒绝删除。 use student go

as

if begin

19

end

7、编写一个（teacher_trigger）触发器，当向teacher_info表中的插入、修改、删除记录时，发出报警语句，并取消相应的操作。

as

8、完成习题中的相应内容。

20

本文来源：https://www.bwwdw.com/article/45ep.html