实验3 交互式SQL2.docx

资源描述

实验3 交互式SQL2.docx

《实验3 交互式SQL2.docx》由会员分享，可在线阅读，更多相关《实验3 交互式SQL2.docx（32页珍藏版）》请在冰豆网上搜索。

实验3 交互式SQL2.docx

实验3交互式SQL2

实验3交互式SQL

（2）

实验目的

1．熟悉SQL查询语句。

2．了解查询执行计划、加深理解关系代数理论

3．熟悉视图的创建和使用

4．熟悉访问控制语句

5．巩固对DDL，DML语言的应用

实验平台

数据库系统：

SQLServer2000

操作系统：

WindowsProfessional2000

实验素材

关于关系表。

在本实验中，继续使用上一个实验中的大学数据库University，其中包括5个表（student,enrollment,course,offering,faculty）：

student:

（StdSSN,StdFirstName,StdLastName,StdCity,StdState,StdMajor,StdClass,StdGPA,StdZip）

enrollment:

（OfferNo,StdSSN,EnrGrade）

course:

（CourseNo,CrsDesc,CrsUnits）

offering:

（OfferNo,CourseNo,OffTerm,OffYear,OffLocation,OffTime,FacSSN,OffDays）

faculty:

（FacSSN,FacFirstName,FacLastName,FacCity,FacState,FacDept,FacRank,FacSalary,FacSupervisor,FacHireDate,FacZipCode）

注：

上述定义中，标注下划线的属性是表的主码。

在表faculty中，FacSupervisor是faculty的外码，属于自引用关系。

该数据库的ER-图为：

实验内容：

1．建立关系表，并建立索引；导入数据。

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）建立索引时，请思考索引的填充因子是什么意思，有什么作用；

请思考索引的数据结构有哪些？

可否指定索引的数据结构？

如果不可以，请说明为什么？

如果可以，请说明你的操作过程或SQL操作。

答：

①在建立索引时，可以指定索引的填充因子，其定义为：

在系统中，填充因子的作用为：

创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。

填充因子的值是从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

预计行计数

预计行大小

131

150+45=195

预计I/O成本

0.0375

0.00625+0.00632=0.01257

预计CPU成本

0.000092

0.000008+0.000086=0.000094

预计执行次数

预计成本

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

起执行结果为：

其执行计划为，两者的行数完全不同，再次说明统计信息不一定准确：

（５）使用LIKE进行查询，查询Offering表中CourseNo中含有‘IS’的行，设计SQL语句如下：

SELECT*

FROMOffering

WHERECourseNoLIKE'IS%'

查询结果为：

执行计划为：

b.使用排序操作OrderBy

答：

查询Enrollment表的所有行，结果按EnrGrade的降序排列，设计查询语句如下：

SELECT*

FROMEnrollment

ORDERBYEnrGradeDESC

观察其执行计划图为：

结论：

使用ORDERBY语句后，系统先对表进行聚集索引查询，然后进行排序。

疑问：

假如在EnrGrade上建立非聚集索引，系统是否还会利用主索引？

？

（２）为此，在EnrGrade上建立非聚集索引，其SQL语句为：

CREATENONCLUSTEREDINDEXEnrGradeInd

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'IS%'

察看其执行计划为：

执行结果为：

结果为40，因为CrsUnits属性列中有大量NULL值，可以看到，sum函数并不处理NULL值。

结论：

sum函数并不处理NULL值。

同样对于avg函数，设计SQL语句如下：

SELECTavg（CrsUnits）asAvgOfCourse

FROMCourse

WHERECourseNoLIKE'IS%'

其执行结果为：

说明avg函数并不处理NULL值。

结论：

avg函数并不处理NULL值。

③max，min函数。

查询Enrollment表中GPA最高和最低的学生。

其SQL语句为：

SELECTMAX（EnrGrade）asMaxOfGPA,MIN（EnrGrade）asMinOfGPA

FROMEnrollment

观察其查询计划，发现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'IS%'

ANDFaculty.FacSSN=Offering.FacSSN

其执行计划图如下：

其查询采用NestedLoops/InnerJoin方式，可以看出：

①当表很小时，SQLServer采用NestedLoops/InnerJoin操作方式。

②在SQLServer中引入了流水线的思想，当中间结果不需要写入外存时，并不进行I/O操作。

结论：

①当表很小时，SQLServer采用NestedLoops/InnerJoin操作方式。

②在SQLServer中引入了流水线的思想，当中间结果不需要写入外存时，并不进行I/O操作。

（2）继续设计左外连接、右外连接、全外连接如下，限于篇幅，不做详细分析：

SELECTOfferNo,CourseNo,Offering.FacSSN,

FacFirstName,FacLastName

FROMOfferingLEFTJOINFaculty

ONOffering.FacSSN=Faculty.FacSSN

WHERECourseNoLIKE'IS%'

SELECTOfferNo,CourseNo,Offering.FacSSN,

FacFirstName,FacLastName

FROMOfferingRIGHTJOINFaculty

ONOffering.FacSSN=Faculty.FacSSN

WHERECourseNoLIKE'IS%'

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='IS';

嵌套查询：

SELECTStudent.StdSSN

FROMStudent

WHEREStdMajor=‘IS’ANDStudent.StdSSNIN（SELECTEnrollment.StdSSN

FROMEnrollment）;

非嵌套查询的执行计划为：

嵌套查询的执行计划为：

与上图完全相同，说明系统在做优化操作时将这两个查询作为同一种查询进行。

结论：

对于同一个查询的不同的查询语句，有时系统内部实现时会可能会完全一样。

B.对同一个查询，写出相关嵌套查询和非相关嵌套查询的形式，执行计划和执行效率对比

答：

设计如下查询：

①相关嵌套

SELECT*

FROMEnrollmente1

WHEREe1.EnrGradeIN（SELECTe2.EnrGrade

FROMEnrollmente2

WHEREe1.EnrGrade=e2.EnrGrade）;

②非相关嵌套

SELECT*

FROMEnrollmente

WHEREEXISTS（SELECT*

FROMEnrollmente1,Enrollmente2

WHEREe1.EnrGrade=e2.EnrGrade）;

对比两者的执行计划：