数据库开发技术实验指导书计本10唐军芳

1、浙江树人大学信息科技学院数据库开发技术实验指导书适合专业：计算机科学与技术本科专业编写部门：电子商务教研室编写日期：2012.09实验一：T-SQL高级查询1实验二：数据完整性测试7实验三：T-SQL编程结构9实验四：游标和事务设计11实验五：SQL Server函数的使用14实验六：存储过程的创建和使用16实验七：触发器的创建18实验一：T-SQL高级查询一、 实验目的通过实验使学生掌握T-SQL高级查询。本次实验让学生学会掌握多表查询的思想，使用UNION子句，以及熟悉统计函数的用法；让学生学会使用group by子句，compute和compute by子句，以及掌握较为复杂的嵌套查询的

2、思想。二、 原理解析1、 多表查询在以前的课程中，所使用的查询局限于一张表格中，但在更多的情况下，需要对多张表格中的数据同时进行查询，这是可以把多张表格的名字全部填写在FROM子句中。在使用多表查询时需要注意的是如何避免笛卡尔积的出现。2、 使用UNION子句如果有多个不同的查询结果数据集合，但又希望将他们连接在一起组成一组数据。这组数据是这多个结果集合的逻辑联合，在这种情况下，可以使用UNION子句。在UNION子句的使用中，有两条基本原则：1)、每一个结果集的数据类型都必须相同，更确切地说是兼容；2)、每一个结果集中列的数量都必须相等，排列顺序必须相互对应。3、 使用统计函数为了有效的处理

3、使用SQL查询得到的数据集合，SQL Server提供了一系列统计函数。这些函数把存储在数据库中的数据描述为一个整体而不是一行行孤立的记录。通过使用这些函数可以实现对数据集合汇总，求平均值等各种运算。4、 使用group by 子句在大多数情况下使用统计函数，返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就需要使用group by子句了。需要注意的是在group by子句中不支持对列分配的别名也不支持任何使用了统计函数的集合列。另外，Select后面每一列数据除了出现在统计函数中的列以外都必须在group by子句中应用。5、 使用compute 和

4、compute by子句使用compute子句，允许同时观察查询所得的各列数据的细节以及统计各列数据所产生的总和。通过使用compute子句既可以计算数据分类后的和，也可以计算所有数据的总和。需要注意的是，从返回的查询结果来看，compute子句和group by子句非常类似。但是两者之间存在着较大的区别，使用group by子句只能产生一个结果集合；使用compute可以返回多种结果集。6、 使用嵌套查询在以前的实验中，我们所使用的查询都是单层查询，但在实际应用中经常要应用到嵌套查询。嵌套查询要求服务器在处理最终查询工作之前先生成一个结果，然后根据当前的查询结果再进一步继续下面的查询工作。但

5、嵌套查询返回的结果作为查询条件等号右边的值存在时之允许嵌套查询返回一行结果，否则系统出错。三、 实验内容1、 查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩情况；2、 查询学生信息数据表中所有学生的学号、姓名、院系名称和院系编号；3、 查询每门课程名称及该门课的任课教师的姓名、编号；4、 在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的基本信息和成绩；5、 查询所学专业为“计算机控制技术”或年龄为21岁的所有学生的姓名；6、 查询计算机工程系全体教师的平均工资；7、 求计算机工程系教师的平均年龄；8、 求计算机工程系教师的总人数

6、。9、 统计计算机工程系各个专业的学生的平均入学成绩；10、 在学生成绩表中统计各门课程的人数；11、 在教师信息表中，按职称分组统计“教授”或“副教授”的工资总额；12、 按职称统计各个教研室的教师人数；13、 对teacher_info中职称为“讲师”的教师工资生成汇总行和明细行；14、 对teacher_info中职称为“讲师”或“助教”的教师工资，按照其职称生成分组汇总行和明细行；15、 查询工资高于“孙乐多”的所有老师的编号、姓名、性别及工资（用子查询实现）；16、 在学生成绩表中查询课程类型为“考试”的学生的学号、姓名、成绩（用子查询实现）；17、 查询课程号为“040101010

7、6”的课程的成绩在8089分的学生的学号、姓名（用exists实现）。18、 完成习题中的相应内容。四、 实验步骤1、查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩情况：select from where 2、查询学生信息数据表中所有学生的学号、姓名、院系名称和院系编号：select from where substring（ ）= 3、查询每门课程名称及该门课的任课教师的姓名、编号：select from where 4、 在Stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的基本信息和成绩：select *from stud_in

8、fo stud_gradeon order by stud_info.stud_id5、 查询所学专业为“计算机控制技术”或年龄为21岁的所有学生的姓名：select stud_id, name from where unionselect stud_id, name from stud_info where DATEDIFF）（ ）=216、 查询计算机工程系全体教师的平均工资：select as 计算机工程系教师平均工资from teacher_info where left（ ）= （select deptcode from dept_code where deptname=计算机工程系

9、）7、 求计算机工程系教师的平均年龄：select as 平均年龄from teacher_infowhere left（ ）=（select deptcode from dept_code where deptname=计算机工程系）8、 求计算机工程系教师的总人数：select from teacher_infowhere left（ ）=（select deptcode from dept_code where deptname=计算机工程系）9、 统计计算机工程系各个专业的学生的平均入学成绩：select from where substring（stud_id,5,2）10、 在学生

10、成绩表中统计各门课程的人数：select from 11、 在教师信息表中，按职称分组统计“教授”或“副教授”的工资总额：select from 12、 按职称统计各个教研室的教师人数：select tech_title 职称， from teacher_infogroup by 13、 对teacher_info中职称为“讲师”的教师工资生成汇总行和明细行：select tech_title,salaryfrom teacher_infowhere order by tech_title 14、 对teacher_info中职称为“讲师”或“助教”的教师工资，按照其职称生成分组汇总行和明细行

11、：select tech_title,salaryfrom teacher_infowhere order by tech_title 15、 查询工资高于“孙乐多”的所有老师的编号、姓名、性别及工资：select from teacher_infowhere 16、 在学生成绩表中查询课程类型为“考试”的学生的学号、姓名、成绩：select from teacher_infowhere 17、 查询课程号为“0401010106”的课程的成绩在8089分的学生的学号、姓名（用exists实现）：select from stud_infowhere exists 实验二：数据完整性测试一、实验

12、目的通过实验使学生理解数据库设计中的数据完整性概念，通过学习教程，掌握和熟悉实施数据完整性的途径，本章实施数据完整性的途径包括约束、规则、默认值、标识列。二、原理解析数据的完整性是指存储在数据库中数据的正确性和相容性。设计数据库完整性的目的是为了防止数据库中存在不符合语义的数据，防止错误信息的输入和输出。1、 使用约束实施数据完整性约束的用途是检查输入到表中的值的范围，SQL Server提供的约束主要有：主键约束、外键约束、惟一约束、检查约束、NULL约束、CASCADE级联引用一致性约束等。2、 使用规则规则是一组使用T-SQL书写的条件语句，它可以和列或者是用户自定义数据类型捆绑在一起。

13、当用户向绑定有规则的数据列上插入或修改值时，规则会检测修改的完整性。3、 使用默认值SQL Server提供的默认值有助于处理用户不含全部表列的INSERT操作。使用方法类似于规则。4、 使用标识列定义为标识列属性的列不必在插入新行时为其赋值，因为服务器会自动为新增加的行中的Identity列设置一个唯一的行序列号。三、 实验内容1、 在企业管理中为表stud_info中的学生性别列（GENDER）创建一CHECK约束，使得该列的值只能是“男”或“女”；2、 使用T-SQL语句为stud_info创建一个名为“CK_stud_info”的CHECK约束；3、 使用T-SQL为表stud_inf

14、o船舰一个约束名为“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、用T-SQL语句在数据库student中创建默认MR_GRADE，并将其绑定到表stud_info中的学生总分（mark）列上，从而实现每名学生的总分默认值为560分；8、用T-S

15、QL语句在数据库student中创建一个规则GZ_GRADE，并将其绑定到表stud_grade中的学生成绩（grade）列上，使得用户输入的成绩在0100的范围内，否则提示输入无效。9、完成习题中的相应内容。实验三：T-SQL编程结构一、实验目的通过实验使学生掌握更复杂的T-SQL查询的应用规则，如需要循环、判断才能表达清楚的查询过程。二、原理解析1、批处理批处理是一个或多个Transact-SQL语句的集合，从应用程序一次性发送到SQL Server并由SQL Server编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条。2、变量分局部变量和全局变量，局部变量以打头，

16、全局变量以打头。局部变量用DECLARE语句声明，用SET语句赋值，具体语法请参见帮助。3、流程控制语句vBEGINNEND语句块 vIFElSE语句vCASE语句vWAITFOR语句vWHILE语句vRETURN语句 三、实验内容1、分析程序的运行结果2、使用局部变量向表中插入数据3、ifelse语句的使用4、case语句的使用5、while语句的使用实验步骤1、 执行下列语句，分析语句的执行结果：use studentcreate view teacher_info_viewasselect teacher_id,name,tech_title,salary from teacher_in

17、fogoselect * from teacher_infogo2、 分析下列语句的执行结果（局部变量的作用域）：declare stud_var intgoselect stud_var=25goprint stud_vargo3、 使用局部变量，向表格stud_info插入一行数据（先声明变量，再赋值，最后将这些变量的值插入对应表格中），0401010705、王小明、1986-10-12、男、上海市杨浦区201800、560。4、 使用ifelse语句，从数据表stud_grade中读出学生“陈红”的成绩，将百分制转换成等级制，成绩在90分到100分之间等级为A

18、，80分以上为B，70分以上为C，60分以上为D，60分以下为E。5、 使用CASE语句实现从数据表stud_info中，选取stud_id、gender，如果gender为“男”则输出“M”，如果为“女”则输出“F”。6、 使用while循环语句实现以下功能：求2300之间的所有素数。7、7、从stud_grade表中查询所有同学考试成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分输出“优秀”。8、 使用CASE语句，输出教师基本信息表（teacher_info）中所有教师所在的年龄段（每6年划分一个段），

19、并说明对应教师的职称。9、 编写计算n!（n=20）的T-SQL语句，并显示计算结果。10、 完成习题中的相应内容。实验四：游标和事务设计一、实验目的数据库游标是类似于C语言指针的语言结构。游标的功能比较复杂，要灵活使用游标需要花费较长的时间练习和积累经验。本次实验让学生掌握游标设计最基本和最常用的方法。SQL Server作为典型的关系数据库，为事务控制提供了完善的编程结构。通过实验掌握事务的各种特性。二、 原理解析1、游标设计通常情况下，数据库执行的大多数SQL命令都是同时处理集合内部的所有数据。但是，有时候用户也需要对这些数据集合中的每一行进行操作。在没有游标的情况下，这种工作不得不放到

20、数据库前端，用高级语言来实现，这将导致不必要的数据传输，从而延长执行的时间。通过使用游标可以在服务器端有效的解决这个问题。游标的使用一般可按顺序分为如下几个步骤： 声明游标语法：DECLARE 游标名 INSENSITIVE SCOROLL CURSORFOR SELECT 语句 打开游标语法：OPEN CURSOR_NAME 使用游标取数或修改数据语法：FETCH COURSOR_NAME INTO VAR 关闭游标语法：CLOSE CURSOR_NAME 释放游标语法：DEALLOCATE CURSOR_NAME2、 事务事务处理控制语句有以下4个： BEGIN TRAN TRAN_NAM

21、E COMMIT TRAN ROLLBACK TRAN SAVE TRAN三、实验内容和步骤1、统计“多媒体技术”课程考试成绩的各分数段的分布情况，即100分的有己几人，90100分有几人，8090有几人，7080有几人，6070有几人，不及格有几人，用游标实现。2、运用事务处理将student数据库中课程信息表lesson_info中的多媒体技术课程编号course_id由0401010106改为0401010116。3、编写一个事务控制程序，要求在事务中包含3个操作：第一个操作是在student数据库的stud_info表中插入一条数据，并检索插入是否成刚，然后 设置一个保存点，紧接着执行

22、第二个操作，删除刚才插入的数据，并检索删除是否成功，然后回滚事务到保存点，最后执行检索操作，看插入的数据是否还存在。4、定义一个游标，将教师表teacher中所有教师的姓名、 教师职称显示出来。5、通过游标将教师表teacher_info中记录号为5的老师的职称由改为“副教授”。6、使用游标显示stud_info表中所有学生的基本信息。7、通过游标实现：对于stud_info表中的每个学生如果在stud_grade表中有相应的选课记录则删除该生的选课记录。8、通过游标实现显示stud_info表中所有男生的基本信息。9、通过游标实现：统计学生人数。10、为stud_info表添加一“备注”字段

23、，某学生如果已选修了课程，则将“备注”字段改为“已选”。11、用游标实现在查询出来的结果集中添加一个新列（有规律），即将stud_info表的STUD_ID、NAME列查询出来到一个新表，并且在新表中添加一列“序号”，用游标实现。12、完成习题中的相应内容。实验五：SQL Server函数的使用一、实验目的为了使用户对数据库进行查询和修改时更加方便，SQL Server在TSQL中提供了许多内部函数以供使用。本次使用掌握其中某些函数的使用方法和意义。SQL Server还提供了用户自定义函数，用户可以按照自己的意愿创建函数。本次实验要求学生掌握用户自定义函数的创建和使用。二、原理解析SQL S

24、erver函数分为：1、 数学函数2、 字符串函数3、 日期函数4、 系统函数5、 统计函数6、 用户自定义函数其中用户自定义函数又可分为标量函数、内嵌表值函数、多语句表值函数。v标量函数：返回单个数据值。v内嵌表值函数：返回值是一个记录集合-表。在此函数中，return语句包含一条单独的select语句。v多语句表值函数：返回值是由选择的结果构成的记录集。19、 实验内容1、创建一标量函数，要求：每次输入一个学号，计算该学生的所有课程的平均分，如果是85100分，返回“优”；如果是7584分，返回“良”；如果是6574，返回“中”；如果是064，返回“差”。2、创建一内嵌表值函数，要求：每次

25、输入一个学号，返回学生选修的课程名和成绩。调用创建的函数查询学号为“0401020201”学生所选修的课程名和成绩。3、用多语句表值函数实现查询某个学生的学号、姓名及选修门数。利用创建的函数查询学号为“0401020201”的学生的学号、姓名及选修门数。4、在存储过程中调用用户自定义函数，实现求两个数的较大值。5、利用标量函数和游标修改表数据。6、创建一内嵌表值函数来替代视图，这个函数返回成绩在所有学生平均成绩之上的学生姓名和成绩。7、用多语句表值函数实现根据某学生的学号，查询该生的姓名及选修门数。8、用内嵌表值函数实现第7题。9、完成习题中的相应内容。实验步骤1、创建一标量函数，要求：每次输

26、入一个学号，计算该学生的所有课程的平均分，如果是85100分，返回“优”；如果是7584分，返回“良”；如果是6574，返回“中”；如果是064，返回“差”。 查询学号为“0401020201”的学生的平均分的等级。2、创建一内嵌表值函数，要求：每次输入一个学号，返回学生选修的课程名和成绩。调用创建的函数查询学号为“0401020201”学生所选修的课程名和成绩。3、用多语句表值函数实现查询某个学生的学号、姓名及选修门数。利用创建的函数查询学号为“04010202013、 ”的学生的学号、姓名及选修门数。4、 在存储过程中调用用户自定义函数，实现求两个数的较大值。5、利用标量函数和游标修改表数

27、据，要求：编写一标量函数以求得某学生的年龄，向stud_info表中添加一列“年龄”，利用游标修改每个学生的年龄。6、创建一内嵌表值函数来替代视图，这个函数返回成绩在所有学生平均成绩之上的学生姓名和成绩。7、用多语句表值函数实现根据某学生的学号，查询该生的姓名及选修门数。8、用内嵌表值函数实现第7题。实验六：存储过程的创建和使用一、 实验目的本次实验要求学生掌握用户自定义存储过程的创建和使用。二、 原理解析存储过程是一组预先编译好的TSQL代码，可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。由于是已经编译好的代码，所以执行的时候不必再次进行编译，提高了效率。创建存储过程

28、语法：CREATE PROC PROC_NAMEWITHRECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONAS SQL_STATEMENT N三、 实验内容1、创建和执行不带参数的存储过程。2、创建和执行带参数的存储过程。3、创建和执行带输出参数的存储过程。四、 实验步骤1、针对教师基本信息表teacher_info，创建一个名称为teacher_proc1的存储过程，该存储过程的功能是从数据表teacher_info中查询所有男教师的信息。2、创建一个名称为student_proc1的存储过程，该存储过程的功能是查询学生的姓名、所选修的课程名以及成绩

29、。3、针对教师基本信息表teacher_info，创建一个名为teacher_proc2的存储过程，执行该存储过程将向数据表teacher_info中插入一条记录，新记录的值由参数提供。3、针对教师基本信息表teacher_info，创建一个名为teacher_proc3的存储过程，执行该存储过程将向数据表teacher_info中插入一条记录，新记录的值由参数提供，如果未提供职称tech_title的值，则由参数的默认值提供。4、在数据库student上创建一个名为stud_proc2的存储过程，其功能是输入两个日期型数据，并使用输出参数返回这两个出生日期之间的所有学生人数。调用该存储过程返

30、回出生日期在1986年1月1日与1986年12月31日之间的学生记录的条数。use studentgo 5、存储过程名为proc1，要求实现如下功能：根据学生学号，查询该学生的选课情况，其中包括该学生学号、姓名、课程号、课程名、成绩等。6、定义存储过程proc2，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！”，否则显示“此学生综合成绩不合格！”。7、完成习题中的相应内容。实验七：触发器的创建一、实验目的通过实验使学生掌握触发器的创建和使用，理解如何使用触发器实现更复杂的数据约束。二、原理解析触发器是一种特殊的存储过程，它

31、与表格紧密相连，可以看作是表格定义的一部分。当用户修改指定表或视图中的数据时，触发器将会自动执行。触发器基于一个表创建，但是可以针对多个表进行操作。创建触发器语法：CREATE TRIGGER TRIG_NAMEON TABLE|VIEWWITH ENCRYPTIONFOR|AFTER|INSTEAD OFNOT FOR REPLICATIONASSQL_STATEMENTN RETURN INTEGER_EXP三、实验内容1、创建insert触发器。2、创建update触发器。3、 创建delete触发器。4、 创建instead of触发器。四、实验步骤1、编写一个（teacher_insert）触发