SQL Server数据库应用与实践教程(第2版) 实验文档 实验1-创建数据库-实验12-授权与回收权限

实验：数据库管理

一、实验目的与任务

目的：旨在训练学生使用T-SQL语句创建及修改教据库，能根据给出的语法创建符合

用户需求的数据库，通过数据库的创建，了解数据库的存储结构，能够分离及附加数据库。

任务：

创建数据库。

任务1：使用T-SQL语句创建数据库Employee,主文件组上有一个主数据文件，文件名

为employeel,存放路径为d盘下，文件初始大小10M,最大值不限，增长量为2M；另有两

个日志文件，文件名分别为employee2与employees,存放路径为d盘下，两个文件初始大

小均为1M,最大值5U,增长量为1M。试按照要求创建该数据库。

任务2：为Employee添加文件组Grp2,并为该组添加文件employee4,存放在D盘下，

初始大小为2M,最大为5M,文件增长量为1M。

任务3：分离与附加数据库。

三、内容与要求

课题名称——数据库管理

（-）课题内容

按照任务1至任务3逐步完成，完成后学生可以独立管理数据库，对数据库创建，修改,

备份，复原，分离及附加，

（二）课题要求

按照实验任务书逐步完成实验

（三）考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：表管理-创建及维护表

一、实验目的与任务

目的：旨在训练学生使用T-SQL语句创建及维护表，能根据给出的语法创建符合用户

需求的表结构，通过表的创建，了解表的约束性规则及表数据的维护。

任务：创建表及表数据的维护，在数据库中创建相应的表，并对表进行数据的添加、删

除及修改。

任务1：使用T-SQL语句创建数据库，脚本如下：

CREATEDATABASESchoolinfo一创建数据库

任务2：创建表Department

列名数据类说明

型

DepIDintPrimaryKey

DepNamevarcharNOTNULL

(20)

任务3：创建表Course

列名数据类说明

型

Course11)intPrimaryKey

CourseNamevarchar(20)NOTNULL

任务4：创建表Stud2nt

列名数据类型说明

StuIDchar(10)PrimaryKey,学号以A,B

或Z打头，后头跟两位数字代

表的系号，再跟1位数字代表

的班号，最后跟2位数字代表

的个人学号

StuNcimevcirchar(20)NOTNULL

StuAgeintStuAge在15到100之间

StuSexchar(2)只能取值为男或女，默认

男

StuCityvarchar(20)无

StuScorefloatStuScore在0到100之

间

DepIDint参照DeparImenl表主键

任务5：创建表SC

列名数据类型说明

Stull)char(10)主键，外键

CourselDint主键，外铤

Scorefloat介于0到100之间

任务6：使用insert语句为各表添加若干记录（试插入违反约束性规则的记录杳看执

行的结果）

任务7：使用update语句将1系学生的StuScore值加1,年龄加1岁。

任务8：删除SC表选择1号课程的记录。

三、内容与要求

课题名称——表的创建及维护

（一）课题内容

按照任务1至任务8逐步完成，完成后学生可以独立管理表，对表创建，表数据的维护。

（二）课题要求

按照实验任务书逐步完成实验

（三）考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：表查询-初步

注：从本实验开始需要使用SchoolInfo数据库（数据库上机实验安装代码）o

一、实验目的与任务

目的：旨在训练学生使用T-SQL语句查询表，能根据给出的语法写出符合用户需求的

查询语句，能对记录进行条件查询，排序，分组及统计。

任务：根据需求，写出满足条件的查询语句。

任务1:写出以下查询语句（基础语句）

1.显示学生的学号，姓名字段信息

2.显示前3个学生所有字段的信息

3.显示学生所有年龄，年龄值不得重复。

4.显示年龄为20岁以上的学生的学号及姓名。

5.显示A00001号学生的姓名，年龄，及所在系的信息。

6.显示员工表中年龄大于19岁的员工的工号及年龄。

7.统计2号课程超过90分以上的总人数。

任务2:写出以下查询语句（分组、统计）

5.使用子查询查找选修了“数据库”课程的学生姓名。

6.要显示院系平均入学成绩大于所有学生平均成绩的记录

7.查询没有任何一门课程成绩在80分以下的学生的学号和姓名。

8.查询选修“数据库原理”课程成绩最高的学生姓名

9.将计算机系所有学生成绩置为0.

10.查询非“计算机系”的所有年龄小于“计算机系”最小年龄的学生信

息。

三、内容与要求

课题名称一表的查询

（一）课题内容

按照任务1逐步完成，完成后学生可以创建基于多表的查询语句。

（二）课题要求

按照实验任务书逐步完成实验

（三）考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：索引与视图

一、实验目的与任务

目的：旨在训练学生能创建适当的索引与视图

任务：根据需求，写出满足条件的查询语句。

任务1:完成以下要求

1.理解索引的分类，在什么情况下创建什么索引，理解课件上的示例。

2.创建视图显示选修了“数据库”课程的学生姓名，所在系名，年龄及该课程成绩（连接

语句）。

3.查询2中的视图显示的信息。

4.使用2中创建的视图更新学生名字为“Tom”的年龄和成绩，使得年龄和成绩各加1。

三、内容与要求

课题名称一索引与视图

（一）课题内容

按照任务1逐步完成，完成后学生可以创建索引与视图。

（二）课题要求

按照实验任务书逐步完成实验

（三）考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：T-SQL语句

一、实验目的与任务

目的：旨在训练学生使用T-SQL语句创建变量，利用变量进行查询，打印变最值；会

使用聚合函数、日期函数和其他函数以及利用流程控制语句编程等。

任务1：声明两个变量@StuID和@611由03,分别赋值为'AOOOO1'和Io杳询SC表

中对应的成绩，并将成绩放入变量@Grade中保存，最后打印该成绩的值。

任务2：查询'AOOOOl，号学生的1号课程成绩，并放入变量保存，如果成绩大于等于

60,则显示“及格”，否则显示“不及格”。

任务3：统计SC表中，A00001，号学生的平均成绩、最高成绩及最低成绩分别放入变量

保存，最后打印变量的值，

三、内容与要求

课题名称一T-SQL语句

（一）课题内容

按照任务1至任务5逐步完成，完成后学生可以使用变量进行T-SQL编程。

（二）课题要求

按照实验任务书逐步完成实验

（三）考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件:SQLSERVER2005

实验：存储过程1

一、实验目的与任务

目的：旨在训练学生能创建带有输入参数的存储过程及带有return语句的存储过程

任务：根据需求，写出满足条件的存储过程。

任务1：在Schoolinfo数据库的基础上,创建ReExam表,脚本如下:

一创建ReExam表

CreatetableReExam

(

StuIDchar(10)constraintfkStuIDlforeignkeyreferencesStudent(StuID),

CourselDintconstraintfkCourselDlforeignkeyreferences

Course(CourseID)

ConstraintpkStuCourselprimarykey(StuID,CourselD),

ScorefloatconstraintchkScorelcheck(Scorebetween0and100)

)

注：该表无任何记录！！！。

任务2:写存储过程根据用户输入的不同学号，打印该学号对应的学生姓名、年龄和系

名。

任务3:使用存储过程实现由用户输入学生的学号及课程名称，根据输入的信息，显

示相应的成绩,如果成绩小于6()分，则向ReExam(StuID,CourselD,Score)表中插入一条记

录。

任务4:写一存储过程，根据不同的系名，查找该系的所有学生最高入学成绩(指Student

表中的StuScore字段)，假设最高分大于等于90分，则返回状态1,否则返回状态0,出

错返回状态T,系名不存在返回状态-2。

用户调用该存储过程，返回值为1,显示存在学生成绩大于等于90,返回状态0,显示

不存在学生最高分大于等于9(),否则提示用户出错了。

三、内容与要求

课题名称一存储过程1

(一)课题内容

按照任务1至任务4逐步完成，完成后学生可以创建带有输入参数及return语句的存

储过程。

(二)课题要求

按照实验任务书逐步完成实验

(三)考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：存储过程2

一、实验目的与任务

目的：旨在训练学生能创建带有输出参数的存储过程及存储过程调用存储过程。

任务：根据需求，写出满足条件的存储过程。

任务1:创建存储过程prcGelSluGradelnfo,输入学生的学号，如果输入信息正确，

则统计该学生所有科目的最高分和不及格的科目数，并洛以上数据使用输出参数返回。

任务2:针对存储过程prcGetStuGradelnfo的返回值写一存储过程，如果不及格数大

于等于3门，则提示用户'亲，您挂了，重头再来吧！'；否则显示学生已获学分。假设

Course表（CourseID,Cc-urseName,Credit）o

任务3：写一个存储过程，查找某个学生是否选修了某门课程，如果选修了则返回1,

否则返回0,错误返回T.

再写一个存储过程调用上一存储过程，当返回值为1,显示选修成绩，为。时提示用户

信息有误，T时提示出错了。

三、内容与要求

课题名称——存储过程2

（一）课题内容

按照任务1至任务3逐步完成,完成后学生可以创建带有输出参数的存储过程及存储过

程调用存储过程。

（二）课题要求

按照实验任务书逐步完成实验

（三）考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件:SQLSERVER2005

实验：事务1

一、实验目的与任务

目的：旨在训练学生能创建带有事务的存储过程及了解事务的并发情况。

任务：根据需求，写出满足条件的存储过程。

任务1:测试事务执行情况。

createprocpreInsertStudent

begintry

begintransaction

INSERTINTOStudentVALUES('A00010'Andy21男'，'Nanjing9D,1)

INSERTINTOStudentVALUES(fA00011','mary',21J女一Nanjing1150,1)

committransaction

endtry

begincatch

rollbacktransaction

endcatch

以上事务执行时，A00011号记录的成绩值超出了范围，编译器报错，此时会被Catch

语句捕获，回滚事务，数据库中两条记录都不能插入。

可将A00011号记录的成绩改为90,重新测试存储过程，两条语句同时被成功插入数据

库。

任务2:创建事务，假设给定学号的学生将从原系转入给定的系，则相应的系总人数

将发生变化，如果两系相差人数小于10,则允许该生转系，否则回滚事务。假设系表

Department(DepID,DepName,Total)

任务3:SC表和Department表需要用下面的事务来更新：

事务1：

UPDATESC

SETScore=Score+1

WHEREStuID='A0D001'andCourselD=1

UPDATESC

SETScore=Score-1

WHEREStuID='AODOOl'andCourselD=2

事务2:

INSERTINTOSTUDENTVALUES

('A00004','mary',21,'男'，'Nanjing',90,1)

UPDATEDepartment

SETTotal=Total+1

WHEREDepID=1

所有更新应一起来做，对于Department,如果Total值大于100,由第二个事务所产生

的改变必须被回滚，而由第一个事务产生的改变是允许的。

任务4：在SchoolInfo数据库中执行如下语句：

createtableTicket

Ticketinfovarchar(20)primarykey,

Ticketl.eaveNumint

)

insertintoTicketvalues(?G00011,10)

任务5:在编译器中使两个事务同时执行，查看数据库情况

CREATEPROCprcSellTicket

©Ticketinfovarchar(20),一车次信息

@TicketNumint一售出张数

AS

Bogintran

Declare@TieketLeaveNumint

Select©TicketLeaveNum=TicketLeaveNumfromTicket

WhereTicketinfo=@TicketInfo一①

waitfordelay'00:00:20.0051

UpdateTicketsetTicketLeaveNum=@TicketLeaveNum-@TicketNum

WhereTicketinfo=©Ticketinfo一②

If(0TicketLeaveNum<@TicketNum)

beginrollbacktranprint'售票失败'end

ElsebeginCommittranprint'售票成功'end

以上程序如果多个窗口同时执行，数据会产生错误吗？如何修改以上程序？

三、内容与要求

课题名称一事务1

(一)课题内容

按照任务1至任务5逐步完成，完成后学生可以创建带有事务的存储过程及了解事务的

并发情况。

(二)课题要求

按照实验任务书逐步完成实验

(三)考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：触发器

一、实验目的与任务

目的：旨在训练学生创建各种类型的触发器。

任务：根据需求，创建符合条件的触发器。

任务1：有“Student”和“Department”表，假设学生表中插入一条学生记录,则相

应系人数加1,如果总人数超过150人，则拒绝插入该学生信息。

任务2：有“Student”和“Department”表，创建触发器，如果有学生转系,则相应

系人数也一并修改。

任务3：在“Deparlmenl”表中创建一个INSERT触发器，如果插入记录的院系名称在

“系”表中已存在，则不执行插入操作，并提示用户。

任务4：在学生表上创建更新触发器，当学生更新其他字段时，提示用户更新成功；如

果更新了姓名，则让更新操作回滚。

任务5：有“Student”和“Department”表,如删除某个系,则相应的学生全部删除。

三、内容与要求

课题名称一触发器

（一）课题内容

按照任务1至任务5逐步完成，完成后学生可以创建各种类型的触发器。

（二）课题要求

按照实验任务书逐步完成实验

（三）考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：游标

一、实验目的与任务

目的：旨在训练学生使用游标遍历数据。

任务：根据需求，创建游标遍历数据。

任务1:创建一个存储过程，根据给定的系名，要求使用游标输出该系的所有学生学

号及姓名的信息。

任务2：使用游标完成教师工资的生成。

任务3：有Course表和SC表，使用嵌套游标，游标1先找出Course表中的每个课程

号，对于找到的课程号，再使用游标2找出选修该课程的所有学生的学号和成绩。

三、内容与要求

课题名称一游标

（-）课题内容

按照任务1至任务4逐步完成，完成后学生可以掌握游标遍历数据。

(二)课题要求

按照实验任务书逐步完成实验

(三)考核及报告要求

1.考核

能独立完成任一项任务。

2.报告要求

提交-一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附

带每个任务的实验结果。

四、主要仪器设备

硬件：PC机

软件：SQLSERVER2005

实验：授权与回收权限

一、实验目的与任务

目的：旨在训练学生能使用GRANT、REVOKE及DENY语句来保证数据的安全性。

任务：根据需求，写出满足条件的授权及回收权限的语句。

任务1:按要求完成以下操作保证数据库安全性要求。

(1)创建登录帐户userkuser2>user3,密码为123456,默认数据库为Schoollnfoo

(2)创建数据库用户userl%user2、user3«

(3)创建角色Rolel。

(4)给角色Rolel添加成员userl和user2。

(5)授予user3对Department表SELECT权限；授予角色Rolel对Department表

SELECT和UPDATE的权限，并允许Rolel可■将该权限继续授予其他人。

(6)先测试user3是否可以更新Department表，然后以userl帐户登录，将对

Department表的SELECT、UPDATE权限授予user3o

(7)以uscr3身份登录，查看是否可以更新Department表，然后以us