实验3 交互式SQL2Word文件下载.docx
- 文档编号:16755064
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:32
- 大小:439.94KB
实验3 交互式SQL2Word文件下载.docx
《实验3 交互式SQL2Word文件下载.docx》由会员分享,可在线阅读,更多相关《实验3 交互式SQL2Word文件下载.docx(32页珍藏版)》请在冰豆网上搜索。
A.建立关系表,注意定义数据完整性。
答:
在上一次实验中已经建立了完整的关系表,本次实验继续使用上次实验的数据库。
在数据库中已经导入了一些数据。
B.建立索引。
在五张表的主码上建立聚簇索引;
自选在一些属性上建立非聚簇索引;
建立索引时,请思考索引的填充因子是什么意思,有什么作用;
请思考索引的数据结构有哪些?
可否指定索引的数据结构?
如果不可以,请说明为什么?
如果可以,请说明你的操作过程或SQL操作。
(1)经过查询SQLServer联机丛书知道,在SQLServer中,存在三种类型的索引,UNIQUE、CLUSTERED和NONCLUSTERED三种类型,并且UNIQUE类型可以与CLUSTERED或NONCLUSTERED可以组合使用,例如UNIQUECLUSTERED类型。
为此,在主码上建立CLUSTERED索引,在其他一些属性列中建立非聚簇索引。
首先,在五个表的主码上建立聚簇索引,其SQL语句为:
CREATECLUSTEREDINDEXCourseIndONCourse(CourseNo)withFILLFACTOR=100
CREATECLUSTEREDINDEXEnrollmentIndONEnrollment(OfferNo)withFILLFACTOR=100
CREATECLUSTEREDINDEXFacultyIndONFaculty(FacSSN,StdSSN)withFILLFACTOR=100
CREATECLUSTEREDINDEXOfferingIndONOffering(OfferNo)withFILLFACTOR=100
CREATECLUSTEREDINDEXStudentIndONStudent(StdSSN)withFILLFACTOR=100
(2)a.然后在Course表上建立CrsDesc属性的非聚簇索引,并以升序排列,为此设计SQL语句如下:
CREATEUNIQUENONCLUSTEREDINDEXCrsDescInd
ONCourse(CrsDescASC)
b.在Enrollment表上建立EnrGrade属性的非聚簇索引,并使EnrGrade已降序排列,设计SQL语句如下:
CREATENONCLUSTEREDINDEXEnrGradeInd
ONEnrollment(EnrGradeDESC)
c.在Faculty表上建立FacNameInd属性的非聚簇索引,并使FacFirstName和FacLastName的升序排列索引,设计SQL语句如下:
CREATEUNIQUENONCLUSTEREDINDEXFacNameIndONFaculty(FacFirstNameASC,
FacLastNameASC)
d.在Offering表上建立OffYear的UINQUENONCLUSTERED索引,为此设计SQL语句:
CREATEUNIQUENONCLUSTEREDINDEXOffYearInd
ONOffering(OffYearDESC)
语句分析正确,但在执行时报错如下:
服务器:
消息1505,级别16,状态1,行1
CREATEUNIQUEINDEX终止,因为发现了索引ID2的重复键。
最重要的主键为'
2003'
。
语句已终止。
原来UNIQUE只能建立单值索引,而OffYear属性列中存在重复值,因此不能在该属性列上建立UNIQUE索引。
去掉UNIQUE后,重新执行,建立索引成功。
e.在Student表的StdFirstName和StdLastName上建立升序索引,在StdGPA上建立降序索引。
其SQL语句如下:
CREATENONCLUSTEREDINDEXStdNameIndONStudent(StdFirstNameASC,
StdLastNameASC)
CREATENONCLUSTEREDINDEXStdGPAIndONStudent(StdGPADESC)
(3)建立索引时,请思考索引的填充因子是什么意思,有什么作用;
①在建立索引时,可以指定索引的填充因子,其定义为:
在系统中,填充因子的作用为:
创建索引时,可以指定一个填充因子,以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间,供将来表的数据存储容量进行扩充和减少页拆分的可能性。
填充因子的值是从0到100的百分比数值,指定在创建索引后对数据页的填充比例。
值为100时表示页将填满,所留出的存储空间量最小。
只有当不会对数据进行更改时(例如,在只读表中)才会使用此设置。
值越小则数据页上的空闲空间越大,这样可以减少在索引增长过程中对数据页进行拆分的需要,但需要更多的存储空间。
当表中数据会发生更改时,这种设置更为适当。
②根据所学的数据结构和数据库的知识,索引的作用就是进行快速检索,所以任何高效的检索数据结构都可以作为索引的数据结构。
因此索引数据结构可以有二叉树、哈希表、倒排表、B树和B+树等。
不可以。
或许现在我还没有发现。
c.导入数据。
在数据库中已经导入了一些数据,并且由手工加入了一些数据。
出现的问题:
在进行导入数据时,有一次导入Enrollment表的数据总是出错,后来发现原来在进行查询时,删去了Faculty表中FacSSN为1234的一行,而Enrollment表中引用了FacSSN作为外码,而在要导入的数据中含有FacSSN为1234的数据,从而使原来合法的数据变成了非法的,导致倒入数据失败。
结论:
导入数据时一定要注意数据的合法性,违反了任何一条完整性约束条件的数据都会导致整个表的数据导入失败。
2.简单查询
a.在单表上进行查询,查看某个或多个特定属性.
(1)建立一个非常简单的查询,查询University数据库Student表中StdMajor为IS的学生的所有信息,设计SQL语句如下:
SELECT*
FROMstudent
WHEREstdmajor='
IS'
;
察看其执行计划,发现其查询的方法为聚集索引:
(2)考虑到全表扫描的效率比较低,在要查询的StdMajor属性上建立非聚簇索引,其SQL语句为
CREATENONCLUSTEREDINDEXStdMajorInd
ONStudent(StdMajor);
建立索引后,继续进行刚才的查询,察看其查询计划为:
对比两个查询计划,第一种查询的查询成本要远高于第二种查询,这说明即使是非聚簇索引,在进行单表简单查询时,其查询效率也要高于聚集索引:
操作
ClusteredIndexScan
BookmarkLookup+IndexSeek
预计行计数
7
预计行大小
131
150+45=195
预计I/O成本
0.0375
0.00625+0.00632=0.01257
预计CPU成本
0.000092
0.000008+0.000086=0.000094
预计执行次数
1
预计成本
0.03767
0.006258+0.006415=0.012673
预计子树成本
0.0376
0.0126+0.00641=0.0767
(3)选择一个数据量最大的表Enrollment进行查询,查询语句如下:
SELECTStdSSN,EnrGrade
FROMEnrollment
WHEREOfferNo=4321
其查询计划为:
查询结果为:
结论:
注意到查询计划中预计行计数为15列,而查询结果仅为6列,说明查询计划的信息并不是十分准确。
在执行计划中会显示出工具提示信息:
其具体各项含义为:
当将游标指向每个节点时,该节点显示工具提示信息。
工具提示信息可能包括:
a)使用的物理运算符(物理操作),例如HashJoin或NestedLoops。
以红色显示的物理运算符表示查询优化器已发出警告,例如丢失列统计或丢失联接谓词。
这可能导致查询优化器选择比预期的效率低的查询计划。
有关列统计的更多信息,请参见统计信息。
图形执行计划建议补救操作,例如创建或更新统计或者创建索引。
使用SQL查询分析器的上下文菜单,可以立即创建或更新缺少的列统计和索引。
b)与物理运算符匹配的逻辑运算符(逻辑操作),如Join运算符。
如果逻辑运算符与物理运算符不同,将在工具提示的顶端列在物理运算符之后,并用正斜杠(/)分开。
c)由运算符输出的行数(行计数)。
d)由运算符输出的行的预计大小(预计行大小)。
e)用于操作的所有I/O活动的预计成本(I/O成本)。
该值应尽可能低。
f)用于操作的所有CPU活动的预计成本(CPU成本)。
g)查询时执行操作的次数(执行次数)。
h)查询优化器执行此操作的成本(成本),包括此操作的成本占查询总成本的百分比。
由于查询引擎选择最高效的操作执行查询或执行语句,因此该值应尽可能低。
i)查询优化器执行此操作及同一子树内位于此操作之前的所有操作的总成本(子树成本)。
j)查询所使用的谓词和参数(参数)。
以上信息摘自SQLServer联机从书。
(4)单表查询多个属性列,查询条件也为多个属性列,设计查询语句如下:
SELECTFacFirstName,FacLastName,FacCity,
FacHireDate
FROMFaculty
WHEREyear(FacHireDate)>
1991
起执行结果为:
其执行计划为,两者的行数完全不同,再次说明统计信息不一定准确:
(5)使用LIKE进行查询,查询Offering表中CourseNo中含有‘IS’的行,设计SQL语句如下:
SELECT*
FROMOffering
WHERECourseNoLIKE'
IS%'
查询结果为:
执行计划为:
b.使用排序操作OrderBy
查询Enrollment表的所有行,结果按EnrGrade的降序排列,设计查询语句如下:
ORDERBYEnrGradeDESC
观察其执行计划图为:
使用ORDERBY语句后,系统先对表进行聚集索引查询,然后进行排序。
疑问:
假如在EnrGrade上建立非聚集索引,系统是否还会利用主索引?
?
(2)为此,在EnrGrade上建立非聚集索引,其SQL语句为:
ONEnrollment(EnrGradeDESC)
观察建立索引的执行计划图为:
然后重新执行以上查询:
观察执行计划图如下:
对比两次查询,可以发现,尽管这一次使用了ORDERBY,但是并没有进行SORT,所以效率上也高了许多。
①使用ORDERBY并不一定要进行SORT,如果已经建立的索引上已经经过了排序,则系统并不会进行SORT操作。
②使用聚集索引的效率并不一定高于非聚集索引。
③当属性列上建立有索引时,系统一般都会利用索引进行查询。
c.使用分组操作Groupby、having;
并作相关的集函数查询;
例如sum,count,avg,max,min等
(1)对Faculty和Offering两表作查询,查询两表中FacSSN相同的,并且OffYear都是2003的Faculty,并按照FacSSN和FacDept进行分组,然后选出行数大于1的元组的FacSSN和FacDept,设计SQL语句如下:
SELECTFaculty.FacSSN,FacDept
FROMFaculty,Offering
WHEREOffering.FacSSN=Faculty.FacSSN
ANDOffYear=2003
GROUPBYFaculty.FacSSN,FacDept
HAVINGCOUNT(*)>
1
察看其执行计划,可以看到其中大部分代价是用于排序:
为了验证在已经排过序的情况下,继续进行ORDERBY操作会怎样,在其查询语句的最后面加上ORDERBYFacSSN,重新察看其执行计划,发现与上图完全相同,说明SQLServer并不会做重复的排序工作。
SQLServer中排序的代价很高,但大多数情况下,系统都不会做重复的排序。
(2)sum,avg,max,min的使用。
为了验证sum的使用,查询IS系所开设的课程的总学分数,设计SQL语句如下:
SELECTsum(CrsUnits)asSumOfCourse
FROMCourse
WHERECourseNoLIKE'
察看其执行计划为:
执行结果为:
结果为40,因为CrsUnits属性列中有大量NULL值,可以看到,sum函数并不处理NULL值。
sum函数并不处理NULL值。
同样对于avg函数,设计SQL语句如下:
SELECTavg(CrsUnits)asAvgOfCourse
其执行结果为:
说明avg函数并不处理NULL值。
avg函数并不处理NULL值。
③max,min函数。
查询Enrollment表中GPA最高和最低的学生。
其SQL语句为:
SELECTMAX(EnrGrade)asMaxOfGPA,MIN(EnrGrade)asMinOfGPA
观察其查询计划,发现max函数和min函数的成本完全一样,并且因为已经在EnrGrade上建立了索引,本次查询并没有做排序。
(1)当在属性列上建立索引时,max和min函数不需做排序。
(2)max和min函数不对NULL列做处理。
在此,可以得出结论,集函数不对空值列作处理。
d.取消重复distinct
e.通配符的使用。
已经在前面的查询中涉及到,在此不再赘述。
f.请大家仔细看各个查询的查询计划图,思考如下问题:
1.空值对结果的影响(例如在计算sum,avg,min集函数时系统如何处理空值属性)?
2.注意察看查询执行计划:
如果察看的属性列上有索引和没有索引,系统是读取数据的方式有何不同?
3.是不是在一个表上有索引,所有的查询都会使用该索引去读取数据?
为什么?
4.为什么有时候即使使用了Orderby操作,但查询计划里并没有相应的操作?
对于distinct也有类似的情况。
1.在SQLServer中,即函数不处理空值,即当碰到NULL值时,这一列都被直接跳过。
2.有不同。
当没有索引时,系统使用ClusteredIndexScan;
当有索引时,系统使用IndexSeek。
3.是。
至少我看到的全是。
原因:
假如有索引的话,那就一定会利用索引,可能是因为判定使用索引效率高低的问题本身代价很大,所以系统默认为使用索引的效率更高一些。
4.这是因为元组原来就已经有序,则系统就不再进行排序。
有时Orderby的列在主索引上,这样系统会直接利用索引,也不会进行排序工作。
复杂的查询
a.连接查询。
(1)设计如下SQL语句:
SELECTOfferNo,CourseNo,FacFirstName,
FacLastName
FROMOffering,Faculty
WHEREOffTerm='
FALL'
ANDOffYear=2002
ANDCourseNoLIKE'
ANDFaculty.FacSSN=Offering.FacSSN
其执行计划图如下:
其查询采用NestedLoops/InnerJoin方式,可以看出:
①当表很小时,SQLServer采用NestedLoops/InnerJoin操作方式。
②在SQLServer中引入了流水线的思想,当中间结果不需要写入外存时,并不进行I/O操作。
(2)继续设计左外连接、右外连接、全外连接如下,限于篇幅,不做详细分析:
SELECTOfferNo,CourseNo,Offering.FacSSN,
FacFirstName,FacLastName
FROMOfferingLEFTJOINFaculty
ONOffering.FacSSN=Faculty.FacSSN
FROMOfferingRIGHTJOINFaculty
SELECTFacSSN,FacFirstName,FacLastName,StdSSN,StdFirstName,
StdLastName,StdGPA
FROMFacultyFULLJOINStudent
ONStudent.StdSSN=Faculty.FacSSN
其中全外连接的结果如下:
A.观察查询计划图,Join操作系统有哪些不同的实现join的方式?
Join有InnerJoin、LeftOuterJoin、LeftSemiJoin、LeftAntiSemiJoin、RightOuterJoin、RightSemiJoin和RightAntiSemiJoin等类型。
系统实现JOIN操作的方式有NestedLoops和MergeJoin方式。
B.系统是否区别等值连接和自然连接?
如果不区分,为什么?
如果区分,请说明系统是如何区分的?
不区分。
在系统中没有自然连接,因此用等值连接实现自然连接。
因为在系统中等值连接直接做了去重工作,即为自然连接。
b.嵌套查询
A.对同一个查询,写出非嵌套查询和嵌套查询两种形式;
观察他们的执行速度差异;
观察他们的执行计划有何不同,并解释为什么。
设计两个查询,一个为嵌套查询,另一个为非嵌套查询,其SQL语句如下:
非嵌套查询:
SELECTDISTINCTStudent.StdSSN
FROMStudent,Enrollment
WHEREStudent.StdSSN=Enrollment.StdSSN
ANDStdMajor='
嵌套查询:
SELECTStudent.StdSSN
FROMStudent
WHEREStdMajor=‘IS’ANDStudent.StdSSNIN(SELECTEnrollment.StdSSN
FROMEnrollment);
非嵌套查询的执行计划为:
嵌套查询的执行计划为:
与上图完全相同,说明系统在做优化操作时将这两个查询作为同一种查询进行。
对于同一个查询的不同的查询语句,有时系统内部实现时会可能会完全一样。
B.对同一个查询,写出相关嵌套查询和非相关嵌套查询的形式,执行计划和执行效率对比
设计如下查询:
①相关嵌套
FROMEnrollmente1
WHEREe1.EnrGradeIN(SELECTe2.EnrGrade
FROMEnrollmente2
WHEREe1.EnrGrade=e2.EnrGrade);
②非相关嵌套
FROMEnrollmente
WHEREEXISTS(SELECT*
FROMEnrollmente1,Enrollmente2
对比两者的执行计划:
相关嵌套:
非相关嵌套:
对比两个查询,竟然发现,非相关查询的效率居然可以低于相关查询。
C.通过查询验证IN,Exists,ANY,ALL,Some之间的等价关系
在上个例子中,已经验证了IN和EXISTS的等价性。
在此只验证其余的等价性。
对于以下三个查询,还有上题中的两个查询,查询结果完全相同,说明他们是等价的。
ANY:
SE
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验3 交互式SQL2 实验 交互式 SQL2