2023 数据库系统原理试验教学指导书

本文格式为Word版，下载可任意编辑——2023数据库系统原理试验教学指导书

数据库系统原理实验指导书

目录

试验1SQLServer常用管理工具的使用1试验2数据库的创立和管理3试验3表的创立和管理8试验4数据查询11试验5视图的创立和使用13试验6索引的创立和使用15试验7T-SQL语言17试验8存储过程22试验9触发器25试验10安全管理29试验11数据库的备份与恢复32试验12SQLServer数据转换35

试验1SQLServer常用管理工具的使用

试验目的：

熟悉和把握SQLServer的常用管理工具的使用:

1、服务管理器：理解服务的概念，把握SQLServer服务的启动、暂停和中止；2、企业管理器：熟悉SQLServer数据库结构，把握企业管理器的基本操作；3、查询分析器：理解查询分析器的作用，把握查询分析器的常用用法；4、联机丛书：把握联机丛书的用法。

试验内容与步骤：

1.服务管理器的用法

SQLServer是作为Windows网络操作系统的一个服务运行的。通过设置，可以在启动操作系统时自动启动SQLServer，也可以远程启动和中止SQLServer。可以使用以下工具手工启动、暂停和中止SQLServer服务：

?SQLServer企业管理器?SQLServer服务管理器?控制面板中的“服务〞?在命令提醒符中使用net命令

SQLServer服务启动成功后，会在屏幕右下角的图标中出现一个符号。

试验内容1：使用SQLServer服务管理器查看SQLServer服务是否正在运行，若正在运行，将其中止。

试验内容2：使用控制面板中的?管理工具?--?服务?管理控制台将已中止的SQLServer服务启动。

提醒：在?名称?里选择SQLSERVERAGENT，然后启动

2.企业管理器的使用

企业管理器是SQLServer提供的最主要的数据库管理图形界面工具，它以树形结构来组织数据库服务器、数据库和数据库中的对象，大部分的数据库管理工作都可以使用它来完成。

试验内容3：启动企业管理器，查看SQLServer的注册属性。

提醒：程序--MicrosoftSQLServer--企业管理器，在控制台树中，展开?MicrosoftSQLServers?，然后展开?SQLServer组?，右击自己的服务器名或local，然后单击?编辑SQLServer注册属性?,进行查看。

试验内容4：在企业管理器中，查看Northwind数据库，并查看表Employee的内容。

第1页

提醒：在企业管理器的目录树中展开数据库文件夹—>Northwind数据库图标，选中Northwind下的‘表’图标，右边将列出Northwind数据库所有的表。选中Employee，单击右键，选择?开启此表?->?返回所有行?。

3.查询分析器的使用

查询分析器是图形化的数据库编程接口，用户可以以自由的文本格式编辑、调试和执行SQL脚本。

试验内容5：在查询分析器中，使用SQL语句查看pubs数据库中authors表的数据信息。提醒：1）首先，启动查询分析器，出现连接界面，在?SQLServer(s)?所对应的框中，输入?.?，然后输入密码即可（上机时无密码），然后确定。开启查询分析器后，在查询窗口中输入如下的SQL语句：

UsepubsGo

Select*fromauthors

2）然后，单击工具栏中的绿色的小三角（或按F5）执行查询。即可查看结果。

试验内容6：通过对象浏览器查看数据库对象提醒：可通过单击工具栏上的图标

，来开启和关闭对象浏览器。

注意：在对数据库进行相应的操作后，对象浏览器有时刷新较慢，需要关闭断开，再连接。技巧：假使需要执行某语句时，可以选中该语句，然后在执行该语句即可。

4.联机丛书的使用

联机丛书中包含所有SQLServer2000的使用说明。在本课程的学习中，各位同学应当把握联机丛书的使用。

试验内容:7：开启联机丛书，在目录中展开?安装SQLServer?，查看有关SQLServer2000的硬件和软件安装要求。

试验内容8：开启联机丛书，在?索引?中查找有关sp_help存储过程的帮助信息。

试验思考

可以在一台计算机中注册多个命名实例和服务器，现在若需要将18号机器的SQLServer服务注册到本地企业管理器中来，应当怎么做？

第2页

试验2数据库的创立和管理

试验目的:

1、熟悉使用向导创立SQLServer数据库的方法；2、把握使用企业管理器创立SQLServer数据库的方法；3、把握使用T-SQL语言创立SQLServer数据库的方法；4、把握附加和分开数据库的方法；

5、把握使用企业管理器或存储过程查看SQL数据库属性的方法；6、熟悉数据库的收缩、更名和删除；

7、把握使用企业管理器或sp_dboption存储过程修改数据库选项的方法。

试验内容和步骤:

本次试验所创立数据库（包括数据库文件和事务日志）存放位置都为“D:\\TestDB〞。因此首先在D盘下新建文件夹TestDB。

1.数据库的创立

创立数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和文件存放位置的过程。

1.1使用向导创立数据库

试验步骤：1）开启企业管理器，把左边的对象展开，选中（local),从?工具?菜单中选择?向导?选项

2）从?选择向导?对话框中，展开?数据库?，选中?创立数据库向导?命令，

依照提醒一步一步完成数据库的创立。

试验内容1：创立一个名为Test0的数据库，数据文件的初始大小设为20MB，文件增长方式设为自动增长，文件增长增量设为5MB，文件的增长不设上限；日志文件的初始大小为20MB，文件增长增量设为1MB，文件的增长限制设为100MB。不许创立维护计划。

创立完后开启D盘的文件夹TestDB，来查看我们所建物理文件。同时通过企业管理器来查看我们所见的数据库，新建数据库中的表只包括系统表。1.2用企业管理器创立数据库

试验步骤：1）在企业管理器中，在数据库文件夹或其下属任一数据库图标上右击，从弹出的快捷菜单中选择?新建数据库?命令，出现?数据库属性?的对话框。

2）在?常规?选项卡中，输入数据库名称

第3页

3）在?数据文件?选项卡，输入数据库文件的名称、位置（单击图标

择存储位置）、初始容量大小和所属文件组名称。

4）在?事务日志?选项卡，用来设置事务日志文件信息。

来选

试验内容2：使用SQLServer企业管理器创立一个数据库，具体要求如下：（大家有疑问）

1)数据库名称为Test1。

2)主要数据文件：规律文件名为Test1_Data1，物理文件名为Test1_Data1.mdf，初始容量为1MB，最大容量为10MB，递增量为1MB。

3)次要数据文件：规律文件名为Test1_Data2，物理文件名为Test1_Data2.ndf，初始容量为1MB，最大容量为10MB，递增量为1MB。

4)事务日志文件：规律文件名为Test1_Log，物理文件名为Test1_Log.ldf，初始容量为1MB，大容量为5MB，递增量为1MB。其他选项为默认值。

1.3用Transact-SQL（T-SQL）语句创立数据库

试验步骤：启动?查询分析器?，在编辑窗口输入SQL语句。

试验内容3：用T-SQL语句创立一个名为teach的数据库，它由5MB的主数据文件、2MB的次数据文件和1MB的日志文件组成。并且主数据文件以2MB的增长速度增长，其最大容量为15MB；次数据文件以10%的增长速度增长，其最大容量为10MB；事务日志文件以1MB增长速度增长，其最大日志文件大小为10MB。运行完语句后，细心查看结果框中的消息。提醒：在查询分析器中输入如下SQL语句。

CREATEDATABASEteachOn

(name=teach_data1,

filename='d:\\TestDB\\teach_data1.mdf',size=5,maxsize=15,filegrowth=2),(name=teach_data2,

filename='d:\\TestDB\\teach_data2.ndf',size=5,maxsize=10,filegrowth=10%)logon

(name=teach_log,

filename='d:\\TestDB\\teach_log.ldf',size=1,maxsize=10,filegrowth=1)

第4页

2数据库的管理

2.1数据库的分开与附加

将teach数据库与服务器分开。可以使用企业管理器或SQL语言对数据库进行分开的操作。分开数据库的存储过程是sp_detach_db。

将已分开的“图书〞数据库附加到服务器上。可以使用企业管理器或SQL语言对数据库进行附加的操作。附加数据库的存储过程是sp_attach_db。试验内容4：分开数据库teach

提醒：sp_detach_dbteach

语句执行成功后，开启企业管理器，查看是否存在该数据库对象。

*若出现?无法分开数据库'teach'，由于它当前正在使用。?问题，应把企业管理器关闭。试验内容5：附加数据库

提醒：sp_attach_dbteach,'d:\\TestDB\\teach_data1.mdf'语句执行成功后，开启企业管理器，查看是否存在该数据库对象。2.2查看和修改数据库属性

数据库创立以后，可以使用企业管理器或SQL语言来查看或修改数据库的属性。使用企业管理器的方法是右击数据库名称，在快捷菜单中选择“属性〞，然后数据库属性对话框中就可以查看或修改数据库的属性。

也可以使用sp_helpdb、sp_spaceused、sp_helpfile和sp_helpfilegroup等存储过程查看和数据库有关的属性。

修改数据库属性可以使用ALTERDATABASE命令。

试验内容6：使用sp_helpdb存储过程查看teach数据库的信息，然后企业管理器将teach数据库的事务日志文件的增长大小改为按2MB字节增长，再使用sp_helpdb存储过程查看student数据库的信息。

提醒：在查询分析器中输入如下SQL语句：sp_helpdbteach

试验内容7：使用ALTERDATABASE命令为teach数据库添加一个新的数据文件，文件规律名为teach3，初始大小为2MB，增长值为1MB，其他属性默认。提醒：在查询分析器中输入如下SQL语句：

ALTERDATABASEteachADDFILE

(name=teach_data3,

filename='d:\\TestDB\\teach_data3.ndf',

第5页

size=2,filegrowth=1)

2.3数据库的收缩

数据库收缩可以缩小数据库的空闲空间。可以使用企业管理器或SQL语言对数据库进行收缩工作。收缩数据库使用的SQL语句是DBCCSHRINKDATABASE命令。也可以使用ALTERDATABASE的SET子句或sp_dboption将数据库设为自动收缩。试验内容8：将teach数据库设为自动收缩。提醒：

1）使用企业管理器。开启企业管理器－选择要收缩的数据库－属性－选项－自动收缩（给服务器性能带来额外负担）2）使用SQL语句。

DBCCSHRINKDATABASE(teach)

其他：DBCCSHRINKDATABASE(UserDB,10)--把数据库UserDB收缩到90％DBCCSHRINKDATABASE(DataFile1,7)--把数据文件DataFile1收缩到7M2.4数据库的更名

使用系统存储过程sp_renamedb更改数据库的名称。在重命名之前，应确保没有用户使用该数据库。

试验内容9：将teach数据库改名为teaching。提醒：在查询分析器中输入如下SQL语句：sp_renamedbteach,teaching2.5数据库选项的配置

可以使用ALTERDATABASE语句的SET子句、sp_dboption系统存储过程，或者在某些状况下使用SQLServer企业管理器设置数据库选项。试验内容10：使用企业管理器将teaching数据库设为只读。

提醒：开启企业管理器，选中teaching，单击右键，选中?属性?，单击?选项?，选中?只读?，确定。

试验内容11：使用sp_dboption存储过程取消teaching数据库的只读设置。提醒：sp_dboption'teaching','readonly','FALSE'2.6数据库的删除

可以使用企业管理器删除数据库。在企业管理器中，右击所要删除的数据库，从弹出的快捷菜单中选择“删除〞命令或直接按下键盘上Delete按钮即可删除数据库。

可以使用DROP语句删除数据库。注意：对正在使用的数据库不能被删除。

第6页

试验内容12：使用DROPDATABASE命令将teaching数据库删除。提醒：DROPDATABASEteaching

试验思考:

1.一个数据库中包含哪几种文件？

2.事务日志文件的大小一般为数据文件大小的多少适合？

3.若需修改数据库文件的大小，可以使用的方法有哪些？若要求使用T-SQL语言，则命令应如何编写？

4.能不能删除master数据库？若一个用户数据库当前正在被访问，能不能被删除？

第7页

试验3表的创立和管理

试验目的:

1、把握使用企业管理器和T-SQL语句创立表；2、把握使用企业管理器和T-SQL语句修改表的结构；3、把握使用企业管理器和T-SQL语句实现对数据的操作；4、理解数据完整性的概念和SQLServer实现数据完整性的机制；

5、把握使用企业管理器和T-SQL语句定义数据完整性，重点把握主键、外键、检查、唯一

和默认值等约束的定义和使用。

建表方法:

1．在企业管理器中创立和管理数据表

通过企业管理器可以做一下的操作：1）创立数据表

在企业管理中，开启想要创立新表的数据库，右击“表〞对象，或者在窗口右侧中的表显示窗口中右击，从弹出的快捷菜单中选择“新建表〞命令，会出现新建表对话框。在对话框中，定义列，及其属性。然后保存此表。2）修改数据库结构

右击要修改的数据表，从弹出的快捷菜单中选择“设计表〞命令，出现“设计表〞窗口，可以完成增加、删除和修改字段的操作。3）管理数据表中记录

右击要录入数据的数据表，从弹出的快捷菜单中选择“开启表〞命令。4）删除数据表

右击要删除的数据表，从弹出的快捷菜单中选择“删除〞命令。

2．使用Transact-SQL语言创立和管理数据表

试验内容和步骤:

1.首先创立数据库teach（创立方法见试验2），并开启该数据库2.然后在该数据库下创立如下三个表：

表名：Course

属性名称属性描述数据类型字节数空否备注第8页

CnoCNameCpnoCredit课程号课程名称先行课学分charvarcharcharsmallint42042否否主键，标识列外码表名：Student

属性名称SnoSName属性描述学号姓名数据类型charvarchar字节数空否98否否备注主键默认值为男，只能输入男和女Ssex性别char2SageSdept年龄所在系smallintchar表名：SC

20属性名称SnoCnoGradeSql代码提醒如下：

属性描述学号课程号成绩数据类型charcharsmallint字节数空否94否否备注主键，外码主键，外码0--100useteach--开启数据库createtableStudent(

SnoCHAR(9)PRIMARYKEY,SnameVARCHAR(20)NOTNULL,

SsexCHAR(2)default'男',SageSMALLINT,

第9页

SdeptCHAR(20),

check((Ssex='男')or(Ssex='女')))Go

CREATEtableSC(

SnoCHAR(9),CnoCHAR(4),

GradeSMALLINT,

PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno),check((Grade=0)))GO

3.使用T-SQL语句实现对数据的操作

(1)使用INSERT语句向表中添加记录(2)使用UPDATE语句对表中数据进行修改(3)使用DELETE语句对表中数据将删除。

第10页

试验4数据查询

试验目的：

1、把握查询语句SELECT的语法；

2、把握SELECT语句相关子句（FROM,WHERE,GROUPBY,ORDERBY等）的使用；3、把握使用SELECT语句进行简单查询、连接查询、嵌套查询。

试验内容：

完成简单查询操作。该试验包括投影、选择条件表达，数据排序，使用临时表等；完成连接查询、嵌套查询、组合查询和统计查询等操作。

首先完成数据库和表的创立（具体代码，见“建库.sql〞，可用文本编辑器开启）。具体操作：开启“查询分析器〞->点击工具栏中的图标即可得到试验所需数据库和数据。1．单表查询

1)查询每个学生的所有数据。

2)查询全体学生的学号、姓名、所在系。3)查询所有年龄在20岁以下的学生姓名及年龄。

4)查询年龄在20~25岁（包括20，25）之间的学生的姓名、系别和年龄。5)查询在信息系和数学系学习的学生的姓名和性别。6)查询所有姓李的学生的姓名、学号和性别。

7)查询选修了0003号课程的学生的学号及其成绩，查询结果按分数的降序排序。8)查询全体学生的状况，结果按所在系的升序排序，同一系的按年龄的降序排序。9)统计学生总人数。

10)计算0001号课程的学生平均成绩。11)查询选修了0001号课程的学生最高分数。12)求各课程号及相应的选课人数。

->找到“建库.sql〞，开启->执行。

13)查询选修了3门及以上课程的学生学号。

2.连接查询

14)查询每个学生及其选修课程的状况。

15)查询选修了00002号课程且成绩在90分以上的所有学生的信息。16)查询每个学生的学号、姓名、选修的课程名和成绩17)查询选修了课程名为?数据库?的学生的学号和姓名

18)查询成绩表Grade中每位学生的学号Sno和各科的总成绩，并按总成绩降序排列。

第11页

19)查找课程表中没有学生选修的课程的课程号和课程名称。提醒：

1）select*fromstudent

2）selectsno,sname,sdeptfromstudent

3）selectsname,sagefromstudentwheresage=314)selectstudent.sno,sname,sage,ssex,sdept,cno,grade

fromstudent,scwherestudent.sno=sc.sno15)selectstudent.sno,sname,sage,ssex,sdept

fromstudent,scwherestudent.sno=sc.snoandcno='0002'andgrade>=9016)selectstudent.sno,sname,cname,grade

fromstudent,sc,course

wherestudent.sno=o=o17)selectstudent.sno,snamefromstudent,course,sc

wherestudent.sno=o=oandcname='数据库'18)selectsno,sum(grade)fromscgroupbysnoorderbysum(grade)desc19）

selectcno,cnamefromcoursewherenotexists

(select*o=o)

第12页

试验5视图的创立和使用

试验目的：

1、理解视图的基本概念，把握视图的创立、修改和删除。2、把握对视图进行查询和更新。

试验内容：

视图是从一个或几个表导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据。

首先完成数据库和表的创立，如上。1.创立视图

1)建立计算机系学生的视图s1，并要求对视图进行更新提醒：createviews1

Asselect*fromstudentwheresdept='计算机系'withcheckoption

2)由学生、课程和选修课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、

姓名、课程名和成绩提醒：createviews2

As

Selectstudent.sno,sname,cname,gradeFromstudent,course,sc

Wherestudent.sno=o=oandsdept='计

算机系'

3)将学生的学号、总成绩、平均成绩定义成一个视图。提醒：createviews3

As

Selectsno,sum(grade)总成绩,avg(grade)平均成绩fromscgroupbysno

2.查询视图

1)对视图S1进行查询，查询选修了0005号课程的计算机系学生

提醒：selects1.*froms1,scwheres1.sno=sc.snoandcno='0005'2)对视图S2进行查询，查询选修课成绩大于等于90分的学生信息提醒：select*froms2wheregrade>=903.更新视图

1)将计算机系学生视图s1中的学生的年龄加1提醒：updates1setsage=sage+1

第13页

再观测一下student表中的学生的信息，会发现计算机系学生的年龄增1了4.删除视图1）删除视图s1

提醒：dropviews1

第14页

试验6索引的创立和使用

试验目的：

1、理解索引的概念，把握索引的创立。

2、理解索引的优缺点，把握索引的管理和维护。

试验内容：

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(由于索引也要随之变动，需要维护索引)。

根据索引的顺序与数据表的物理顺序是否一致，可以把索引分成两种类型：聚簇索引与非聚簇索引。1.聚簇索引

?数据表的物理顺序和索引表的顺序一致，它根据表中的一列或多列的值排列记录。?每一个表只能有一个聚簇索引，由于一个表的记录只能以一种物理顺序存放.?一般主键是聚簇索引的良好候选者.2.非聚簇索引

?表的物理顺序与索引顺序不同，索引是有序的，而表中的数据是无序的。?一个表可以有多个非聚簇索引。1.使用企业管理器

步骤：展开数据库->选中需要创立索引的表，单击右键，选择“所有任务〞->单击“管理索引〞，在出现的窗口中，来完成对索引的创立、编辑和删除。

1）使用企业管理器为数据库teach中的学生表student创立一个以sname为索引关键字的惟一索引。

2）将前述所建立的索引删除。2.使用T-SQL语句

1）为数据库teach中的表course创立一个名为C_Index的惟一非聚簇索引，索引关键字为编号cno，降序，填充因子为80%。提醒：useteach

go

createuniquenonclusteredindexC_Indexoncourse(cnodesc)withfillfactor=80

第15页

2）查看表courese的索引(使用系统存储过程sp_helpindex)提醒：EXECsp_helpindexcourse

3）使用T-SQL语句将教师表中的Teacher_Index删除。提醒：DROPINDEXcourse.C_Index

4）为student表创立一个基于“年龄，学号〞组合列的非聚簇、复合索引nn_xh_index。提醒：

IFEXISTS(SELECTnameFROMsysindexesWHEREname='nn_xh_index')DROPINDEXstudent.nn_xh_index;GO

CREATENONCLUSTEREDINDEXnn_xh_indexONstudent(sage,sno);

第16页

试验7T-SQL语言

试验目的：

1、把握Transact-SQL语言中的变量、运算符、函数；2、把握Transact-SQL语句编写批处理和流程控制程序。3、把握游标的创立，开启，数据读取，关闭，销毁的基本方法

试验内容和步骤：

Transact-SQL语言是在SQLServer2000中使用的程序设计语言。不但包含了标准SQL语言部分，而且为了满足实际应用中的编程需要，在Transact-SQL语言中还另外增加了一些语言要素，它们包括解释、变量、运算符、函数和流程控制语句等。

所有的语句都在查询分析器中执行。

1.Transact-SQL语句

试验内容1：返回在程序执行过程中上一条SQL语句影响的记录数（全局变量@@ROWCOUNT），注意观测结果窗口。

useteach--开启teach数据库

GO

--查询学生表中的记录

SELECT*FROMStudent

--查询上一条SELECT语句影响的记录数

SELECT@@ROWCOUNTAS第一次查询返回的记录数GO

试验内容2：查询学生表的人数，将返回的记录数赋给局部变量@Rows

USEteachGO

DECLARE@Rowsint--声明局部变量

--给局部变量赋值

SET@Rows=(SELECTCOUNT(*)FROMStudent)--显示局部变量的值

SELECT@RowsASSELECT返回的记录数GO

试验内容3：设置一个局部变量为表，进行插入和查询操作

--定义变量为临时表

DECLARE@Table_ExampleTABLE(Col_numintPRIMARYKEY,Col_textchar(50))

--向该变量中添加两行内容

INSERTINTO@Table_ExampleVALUES(1,'thisisa')

第17页

INSERTINTO@Table_ExampleVALUES(2,'tabledatatypeexample')

--从临时表中查询

SELECT*FROM@Table_ExampleGO

试验内容4：(练习，自己试着写一下)设置一个局部变量其值为选修了课程的人数，并显示提醒：DECLARE@Rowsint--声明局部变量

--给局部变量赋值

SET@Rows=(SELECTCOUNT(distinctsno)FROMSC)--显示局部变量的值

SELECT@RowsAS选课人数GO

试验内容5：检查sysdatabases中的每一个数据库，使用数据库标识号来确定数据库名称。

USEmaster--开启master数据库

GO

--DB_NAME是一个函数，功能是根据数据库标识号，来返回数据库的名称SELECTdbid,DB_NAME(dbid)ASDB_NAMEFROMsysdatabasesORDERBYdbidGO

试验内容6：从数据库中返回学生表的第2列的名称

USEteachGO

--定义局部变量，用于存放学生表的ID号DECLARE@学生表IDINT

--OBJECT_ID是一个函数，功能是返回数据