数据库实验报告.docx

数据库原理实验报告题目：实验四视图与索引学号姓名班级日期XxxxXxxxxxx2016.10.20一. 实验内容、步骤以及结果1 在Student数据库中，利用图形用户界面，创建一个选修了“数据库原理”课程并且是1986年出生的学生的视图，视图中包括学号，性别，成绩三个信息。2 用两种不同的SQL语句创建第五版教材第三章 第9题中要求的视图（视图名：V_SPJ）方法一：create view V_SP as select sno,pno,qty from spj where spj.jno in (select jno from j where j.jname=三建) 方法二：create view V_SPJ as select sno,pno,qty from spj,j where j.jno=spj.jno and j.jname=三建3 用SQL语句完成第五版教材第三章 第11题中的视图查询(1)找出三建工程项目使用的各种零件代码及其数量。SELECT PNO,QTY FROM V_SP(2)找出供应商S1的供应情况。SELECT * FROM V_SPJ WHERE SNO=S14 用SQL语句完成视图的数据更新。(1) 给视图V_SPJ中增加一条数据。INSERTINTO V_SPJ（SNO,PNO,QTY）VALUES( s5,p3,900)提示：- SPJ表中JNO允许为空时，数据可以插入基本表，此时JNO为NULL，由于JNO为NULL，所以视图中没有该条数据。- SPJ表中JNO不能为空时，可以使用instead of触发器实现。(2) 修改视图V_SPJ中的任意一条数据的供应数量。update V_SPJ set qty=111 where sno=s1 and pno=p1(3) 删除视图V_SPJ中的任意一条数据（注意所创建视图可以视图消解时，才能正常删除，否则会删除失败；也可以考虑用instead of触发器实现）。DELETE V_SPJ where sno=s1 and pno=p1 and qty=111用instead of触发器实现CREATE TRIGGER trdV_SPJ ON V_SPJ INSTEAD OF DELETE ASBEGINDelete from V_SPJWHERE sno=s1 and pno=p1 and qty=111END可以看到s1、p1、111已将被删除了5 用图形用户界面对Student数据库中C表的Cno字段创建一个降序排列的唯一索引,索引名称IX_CNo。(5分)6 使用SQL语句对Student数据库完成以下的索引操作。（15分，每题5分）(1) 在C表的CName属性上创建一个非唯一性的聚簇索引，索引名IX_CName。CREATE CLUSTER INDEX IX_CName ON C(CName)(提示：创建这个聚簇索引之前，需要首先删除C表的主键约束。SQL Server中，给某张表指定主键时，会自动创建为主属性一个聚簇索引。)(2) 在SC表上创建一个名为IX_Cnosno的非聚簇复合索引，该索引是针对sno,cno属性集建立的升序索引。CREATE INDEX IX_CnosnoON SC(sno ASC,cno ASC)(3) 删除C表的索引IX_CName。DROP INDEX IX_CName ON C7 自己设计一个实验验证索引对数据库查询效率的提升作用。（40分） (提示：需要数据量比较大的情况下才容易进行对比)1、创建表：CREATE TABLE dbo.Article( Id int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, MsId int NOT NULL, Title nvarchar(96) NOT NULL, TitleBak nvarchar(96) NOT NULL, Summary nvarchar(512) NOT NULL, SummaryImageUrl nvarchar(256) NOT NULL, Tag nvarchar(50) NOT NULL, ArticleChannel_Id int NOT NULL, ArticleCategory_Id int NOT NULL, IsApproved bit NOT NULL, Creator_Id int NOT NULL, CreatedDateTime datetime NOT NULL, ModifiedDateTime datetime NOT NULL, ViewCount int NOT NULL, ReplyCount int NOT NULL, DiggCount int NOT NULL, FavoriteCount int NOT NULL, LastReplyUser_Id int NOT NULL, LastReplyDateTime datetime NOT NULL, RightType int NOT NULL, IsDisplayContent bit NOT NULL, IsSensitive bit NOT NULL, Source int NOT NULL, CONSTRAINT PK_Articles PRIMARY KEY CLUSTERED ( Id ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY ) ON PRIMARY2、加入测试数据： DECLARE number INT SET number = 200000 WHILE number 0 BEGIN INSERT dbo.Article ( MsId, Title, TitleBak, Summary, SummaryImageUrl, Tag,ArticleChannel_Id, ArticleCategory_Id, IsApproved, Creator_Id,CreatedDateTime, ModifiedDateTime, ViewCount, ReplyCount, DiggCount,FavoriteCount, LastReplyUser_Id, LastReplyDateTime, RightType, IsDisplayContent,IsSensitive, Source ) VALUES ( number, Title+cast(number AS VARCHAR(20), TitleBak+cast(number AS VARCHAR(20), Summary+cast(number AS VARCHAR(20), SummaryImageUrl+cast(number AS VARCHAR(20), Tag+cast(number AS VARCHAR(20), 1, 2, 0, number,GETDATE(), GETDATE(), 100, 29, 123,12, number, GETDATE(), 1,0, 0, 2 ) SET number=number-1 END创建200000条数据，每条数据包含MsId, Title, TitleBak, Summary, SummaryImageUrl, Tag, ArticleChannel_Id, ArticleCategory_Id, IsApproved, Creator_Id,CreatedDateTime, ModifiedDateTime, ViewCount, ReplyCount, DiggCount,FavoriteCount, LastReplyUser_Id, LastReplyDateTime, RightType, IsDisplayContent, IsSensitive这些属性，部分数据截图如下：3、没建立索引前，利用语句，查询开销，看执行计划 SELECT CreatedDateTime,ViewCount ,title,titlebak,summary,summaryimageurlFROM Article WHERE Creator_Id=200000 WITH TEMP AS ( SELECT ROW_NUMBER() OVER (ORDER BY CreatedDateTime) AS ROW, CreatedDateTime,ViewCount ,title,titlebak,summary,summaryimageurl FROM Article WHERE Creator_Id=200000 ) SELECT * FROM TEMP WHERE ROW BETWEEN 1 AND 30建立索引之后，再执行一次查询,执行计划如下:由此可见，有索引时，查询开销变小，查询更有效。二. 实