数据库实验报告3

1、数据库原理及应用实验报告题目： 实验二、三、五、七 专业： 软件工程 班级： 软件131 姓名： 陈万全 学号：132852 指导教师： 张满囤 完成日期： 2015.12.22 第2章 ：数据库的完整性控制实验2.1 实体完整性一、实验目的学习实体完整性的建立，以及实践违反实体完整性的结果。二、实验题目1.在School数据库中建立一张新表Class，包括Class_id（varchar（4），name（varchar（10），Department（varchar（20）三个列，并约束Class_id为主键create table Class(Class_id varchar(4),name

2、 varchar(10),Department varchar(20)constraint pk_Class primary key(Class_id)2、 创建事务T3，在事务中插入一个元组（00001,01CSC,CS）,并在T3中嵌套创建事务T4，T4也插入和T3一样的元组，编写代码测试，查看结果。use schoolset xact_abort onbegin transaction T3insert into Classvalues(0001,01CSC,CS)set xact_abort offbegin transaction oninsert into Classvalues(

3、0001,01CSC,CS)commit transaction T4commit transaction T3结果：(1 行受影响)消息2627，级别14，状态1，第8 行违反了PRIMARY KEY 约束pk_Class。不能在对象dbo.Class 中插入重复键。开始时，T3中的insert执行成功，而当T4中的insert执行不成功后滚回T3，导致T3出错，后滚回所以整体上执行不成功。分析：当SET XACT_ABORT为ON时，如果Transact-SQL产生运行错误，整个事物将终止并会滚。为OFF时，只回滚产生错误的Transact-SQL语句，而事物将继续进行处理。对于大多数OL

4、EDB提供程序，隐性或显式事物中的数据修改语句必须将XACT_ABORT设置为ON。唯一不需要该选项的情况是提供程序支持嵌套事务时。实验2.2 参照完整性一、实验目的 学习建立外键，以及利用FOREIGNKEYREFERENCES子句以及各种约束保证参照完整性。二、实验题目3、创建一个班里的学生互助表，规定：包括学生编号、学生姓名、学生的帮助对象，每个学生有且仅有一个帮助对象，帮助对象也必须是班里的学生。create table HELP(sid char(10) primary key,cname varchar(12),helpid char(10)not null,)alter tabl

5、e HELP add constraint FK_help foreign key(helpid)references HELP(sid)4、 学校学生会的每个部门都有一个部长，每个部长领导多个部员，每个部只有一个部员有监察评测部长的权力。请给出体现这两种关系（即领导与评测）的两张互参照的表的定义。create table leader(sid char(9),sname varchar(20),myleader char(9),constraint PK_leader primary key(sid)create table monitor(sid char(9),sname varchar

6、(20),mymonitor char(9)constraint PK_monitor primary key(sid)constraint PF_monitor foreign key(mymonitor)references leader(sid)alter table leaderadd constraint FK_leader foreign key(myleader) references monitor(sid)实验2.3 用户自定义完整性一、实验目的学习用户自定义约束，并实践用户自定义完整性，利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。

7、二、实验题目1、加入约束U3，令sage的值=0。use schoolalter table Workeradd constraint U3 check(sage=0)2、 加入规则R2、确保插入的记录的sage的值在1到100之间，并绑定到sage的属性上。use schoolgo create rule R3 as vale1 and vale备份，打开如下页面：备份类型选择完整，在目标项选择备份文件存储路径，点击确定，提示备份成功：2.差异备份操作同完整性备份，只不过在备份类型选项选择差异，如下图：点击确定，提示备份成功。3.事务日志备份由于简单恢复模式不允许备份事务日志文件，所以必须先

8、更改恢复模式，右键School数据库，选择属性-选项，将恢复模式更改为完整，如下图所示：点击确定即可。接下来按前两次备份操作，同样在备份选项选择事务日志，点击确定。实验5.2 SQL Server 数据库的还原一、实验目的 数据库恢复是数据库系统发生故障后，数据库管理员或用户利用先前建立的数据库的备份，将数据库从故障中恢复过来的过程。二、实验题目（1）作为数据库管理员，针对在实验5.1的实验步骤中提出的School数据库方案，假设数据库在星期三发生了故障，应该采取怎样的恢复措施？利用本月初建立的数据库完全备份恢复数据库，然后再利用上一个星期的差异数据库的备份，将数据库恢复到上一星期的状态。然后

9、再利用周一和周二的日志备份，将数据库恢复到星期二的状态。删除School数据库所有视图，如下所示：进行数据库还原，右键School数据库，选择任务-还原-数据库，进入如下窗口：点击确定，即完成数据库的还原：还原后的数据库如下所示：实验5.3 系统数据库的备份与恢复一、实验目的 读者通过对系统数据库的备份与还原，深入了解系统数据库Master，Model，Msdb的作用，并制定系统数据库的备份与恢复策略，以保证数据库系统的可靠性与正确性。二、实验题目右键点击School数据库，选择任务-导出数据，进入如下窗体：点击数据源的下拉按钮，选择SQL Server Native Client 11.0,

10、点击下一步，进入如下窗体：目标选择平面文件目标，选择所要导出的目标文件D:SourceCourse.xlsx，点击下一步,选择复制一个或多个表或视图的数据，点击下一步：选择Course表，点击下一步勾选立即运行，点击下一步：点击完成，结果如下：数据导入右键School数据库，选择任务-导出数据，在数据源选项选择平面文件，文件名选择D:SourceAddress.xlsx:点击下一步，进入选择目标窗体，选择SQL Server Native Client 11.0：一直点下一步直到完成。第7章 ：物理优化实验7.1 建立索引一、实验目的学会在SQL Sever中建立索引。二、实验题目自己动手试一

11、试在students表上为sname字段建立聚簇索引，为email建立非聚簇索引：use schoolgocreate index ix_email on students(email)/*建立非聚簇索引*/declare name varchar(20)select name=name from sysindexes where id=object_id(students)exec(alter table students drop constraint+name)goalter table students with nocheck add constraint pk_id primary

12、 key nonclustered(sid)gocreate clustered index ix_sname on students(sname)go实验7.2 覆盖索引的作用一、实验目的通过本实验体会覆盖索引的作用，在以后的实践中，能够适时地使用覆盖索引来提高数据库的性能。二、实验题目假设经常会有这类查询:查询某学生选课的课程号.该如何建立索引,自己试试看建立索引前后的查询效率的区别没有建立索引时的查询效率：use Schoolgoset statistics io ongoset statistics time ongoselect cid from CHOICES where sid

13、like 846806971go建立索引时的查询效率：create index ix_sid_cid on choices(sid,cid)goselect cid from CHOICES where sid like 846806971go实验7.3 聚簇索引一、实验目的通过实验体会聚簇索引的优缺点，学会根据具体情况创建聚簇索引。2、 实验题目分别试试在三种情况下(无索引、有非聚簇索引、有聚簇索引)向一个表中插入记录的耗时情况，看看是否与分析的结论一致。1.建立一个数据库表use schoolgocreate table test_insert( id uniqueidentifier n

14、ot null rowguidcol default(newid() primary key nonclustered,name varchar(50) not null, age int, rent int not null)-2.往表中加入数据declare i intset i=1while i=100000begin insert into test_insert(name,age,rent) values(newid(),i,rand()*10000000) set i=i+1endgo用时32秒-3删除所有记录，添加一个非聚簇索引delete from test_insertcre

15、ate index ix_name on test_insert(name)go-4向表中加入记录declare i intset i=1while i=100000begin insert into test_insert(name,age,rent) values(newid(),i,rand()*10000000) set i=i+1endgo用时2分36秒-5删除所有记录，更改非聚簇索引为聚簇索引delete from test_insertgodrop index test_insert.ix_namegocreate clustered index ix_name on test_

16、insert(name)go -6向表添加记录declare i intset i=1while i=100000begin insert into test_insert(name,age,rent) values(newid(),i,rand()*10000000) set i=i+1endgo用时3分58秒结果分析：没有索引时插入数据最快，有非聚簇索引时插入慢些，因为要修改索引，而有聚簇索引时最慢，因为数据插入前要根据索引找到插入的位置，有可能还要把其他数据移位，插入后要修改索引（但是在并行状况下，没有索引或有非聚簇索引的插入可能会造成争索而延迟，对性能影响大，因为这两种都是在随后数据页

17、面插入数据。而聚簇索引则不会。）实验7.4 维护索引的代价一、实验目的索引对于查询等操作是很有帮助的，但是建立和维护索引也是需要额外开销的，空间和时间上都需要，本实验帮助读者理解聚簇索引的开销，由此可以看到，索引并不都是有用的，索引不是建得越多越好，不必要的索引会给系统带来负担。二、实验题目试试在choices表上建立几个非聚簇索引，看看建立和删除非聚簇索引所需时间。再比较有非聚簇索引时和没有时更新的速度。建立索引：set statistics time oncreate index ix_sidon choices(sid); SQL Server 分析和编译时间: CPU 时间= 93 毫

18、秒，占用时间= 2118 毫秒。 SQL Server 执行时间: CPU 时间= 78 毫秒，占用时间= 459 毫秒。 SQL Server 执行时间: CPU 时间= 187 毫秒，占用时间= 2647 毫秒。删除索引：set statistics time ondrop index ix_sidon choices;SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 22 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 92 毫秒。建立索引前，进

19、行更新：set statistics time onupdate choicesset score=score-1; SQL Server 分析和编译时间: CPU 时间= 15 毫秒，占用时间= 363 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。 SQL Server 执行时间: CPU 时间= 63 毫秒，占用时间= 3023 毫秒。(9613 行受影响)建立索引IX_sid，进行更新：set statistics time oncreate index

20、ix_sidon choices(sid);update choicesset score=score-1;SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 1 毫秒。 SQL Server 执行时间: CPU 时间= 47 毫秒，占用时间= 45 毫秒。 SQL Server 执行时间: CPU 时间= 47 毫秒，占用时间= 129 毫秒。SQL Server 分析和编译时间: CPU 时间=

21、 0 毫秒，占用时间= 0 毫秒。SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。 SQL Server 执行时间: CPU 时间= 15 毫秒，占用时间= 1300 毫秒。(9613 行受影响)实验7.5 冗余索引一、实验目的 如实验7.4中所说，索引虽然是一种很有用的查询结构，但会给系统带来额外的负担，而且有的情况下所有的索引是冗余的，对提高查询速度也没有帮助，本实验通过一个例子说明了这个问题，并非任意创建索引都是有用的。2、 实验题目 在choices表里建立score和cid字段上的非聚簇索引，并执行查询试一试看对查询有没有性能的提升。没有建立索

22、引时的查询效率：use Schoolgoset statistics io ongoset statistics time ongoselect score from CHOICES where sid like 846806971goSQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 76 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。SQL Serve

23、r 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 10 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。SQL Server 分析和编译时间: CPU 时间= 16 毫秒，占用时间= 23 毫秒。 SQL Server 执行时间: CPU 时间= 16 毫秒，占用时间= 8 毫秒。建立索引后的查询效率：use Schoolcreate index ix_score on choices(score)goselect score from CHOICES where sid like 846806971GoSQL Server 分析和编译时间:

24、 CPU 时间= 0 毫秒，占用时间= 28 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。SQL Server 分析和编译时间: CPU 时间= 31 毫秒，占用时间= 40 毫秒。表CHOICES。扫描计数1，逻辑读取2416 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 16 毫秒。 SQL Server 执行时间: CPU 时间= 31 毫秒，占用时间= 60 毫秒。SQL Server 分析和编译时间: CPU 时

25、间= 0 毫秒，占用时间= 0 毫秒。 SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 0 毫秒。分析：没有性能提升，因为score最多100个值，而计算知一个数据页大概有200来条记录，也就是说每个数据页里平均都会有两个不同的score值，所以不管什么查询都是表扫描，所以系统不会用score上的非聚簇索引。同理，cid只有50个不同的值实验7.6 表中多个索引的选择一、实验目的通过实验了解单表多索引在执行查询时对索引的选择规律，便于以后创建更加有用的索引。2、 实验题目建立索引：if not exists(select name from sysindexes whe

26、re name=ix_sid)create index ix_sid on choices(sid);if not exists(select name from sysindexes where name=ix_sid)create index ix_sid on choices(sid);执行查询，查看执行计划：set showplan_all ongoset statistics io ongoset statistics time ongoselect *from choices where sid between 880000000 and 889000000and tid betw

27、een 228950000 and 229900000go实验7.7 索引与表的连接查询一、实验目的表的连接查询是很多数据库操作中涉及的一大类查询，而且连接查询是比较费时的一类查询。本实验通过几个例子让读者简单了解如何提高连接查询的效率。二、实验题目1、试一试如果上面实验choices表中没有sid的主外键约束（但还是都有聚簇索引），执行此连接查询时系统默认使用什么连接在企业管理器中把choices表的SID的主外键约束删掉后执行连接查询use Schoolgoset showplan_all ongoselect score from STUDENTS,CHOICES where STUDE

28、NTS.grade=2000 and STUDENTS.sid=CHOICES.sidgo分析：没有主外键约束情况下，系统使用的是哈希连接2、如果在choices表的sid字段上没有索引（students表sid上还是有聚簇索引），或者只有非聚簇索引，在执行以上连接查询时系统的执行计划和执行效率如何？a.没有聚簇索引的情况，执行查询：use Schoolgoset statistics io ongoset statistics time ongoset showplan_all ongoselect score from STUDENTS,CHOICES where STUDENTS.gra

29、de=2000 and STUDENTS.sid=CHOICES.sidgo 查询计划：b.有非聚簇索引的情况，执行查询：create index ix_sid on choices(sid)goselect score from STUDENTS,CHOICES where STUDENTS.grade=2000 and STUDENTS.sid=CHOICES.sidgo查询计划：分析：Choices表SID有非聚簇索引和没有索引时都是table scan，且用的都是哈希连接。效率没有SID上建立聚簇索引时高。实验7.8 填充因子一、实验目的理解填充因子的作用，并会根据实际情况设置合适的填

30、充因子2、 实验题目2）用聚簇索引做一次上面的实验，看看有什么异同use school GO CREATE TABLE test2 (id uniqueidentifier NOT NULL rowguidcol default (newid() primary key nonclustered, name varchar(50)not null, age int, rent int not null )use schooldeclare i int set i=1 while i=100000 begin insert into test2(name,age,rent)values (NEWID (),i,RAND ()*10000000)set i=i+1 end go create index ix_rent on