《北邮大三数据库实验六数据查询分析实验.docx》由会员分享，可在线阅读，更多相关《北邮大三数据库实验六数据查询分析实验.docx（13页珍藏版）》请在三一文库上搜索。
1、北邮大三数据库实验六数据查 询分析实验实验六数据查询分析实验实验目的通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理 解，提高优化数据库系统的实践能力，熟悉了解Sybase中查询分析器的使用，并进一步提高编写复杂查询的SQL程序的能力。实验内容1.索引对查询的影响（1）对结果集只有一个元组的查询分三种情况 进行执行（必如查询一个具体学生的信息）：不建立索引，（学号上）建立非聚集索引，（学 号上）建立聚集索引。建立聚集索引： create clustered index student on student（student id）建立非聚集索引：create non
2、clustered index studenndexon student(studend)g。用查询分析器的执行步骤和结果对执行进行分析比较。select * from studentwhere studentid=130201I；1s_id s_name sex birthdaydept class1 rSioi 吴磊里 1981M)1 旬3 00:00:00.000 电信 3022不建立索引Aggregate StatusConnection failuresElapsed time00:00:00,499Finish time2013/6/1 8:58:06NamelocalhostRo
3、ws retu med1Start time2013/6/1 8:58:06StateOpen.建立聚集索引Aggregate StatusConnection failuresElapsed time00:00:00,027Finish time2013/5/30 20:4&:45NamelocalhostRows returned1Start time2013/5/30 20:48:45StateOpen建立非聚集索引Aggregate StatusConnection failuresElapsed time-00:00:00,062Finish time2013/6/1 9:02:29
4、NamelocalhostRows returned1Start time2013/6/1 9:02:29StateOpen对结果集中有多个元组的查询（例如查看某门 成绩的成绩表）分类似（1）的三种情况进行执行比较。select * from studentwhere studentid130401-3 Results _j Messagesstud ent Jdstudent_nanne sexbirthdaydepartmentclass1j 31401%I郭子敬里1980-1-2 00:00计算机314G231402胡雅杰更1980-1-2 00:00计算机31463314.03杨磊男1
5、980-1-2 00:00计算机314G431404马宇宁男1980-1-2 00:00计算机3146531405黄荣里1950-1-2 00:00计算机3146631406宋月男1980-1-2 00:00计算机3146731407赵卫京男15801-2 00:00计算机3146831408刘树魁1930-1-2 00:00计算机3146931409牛海锋更19B0-1-2 00:00计算机31461031410无丁男1980-1-2 00:00计算机314G1131411毛文高男1980-1-2 00:00计算机3146不建立索引:Aggregate StatusConnection fa
6、iluresElapsed timeFinish timeNameRows retu medStart timeState00:00:00.0932013/6/1 8;59;12 localhost272013/6/1 8:59:12Open建立聚集索引：Aggregate StatusConnection failuresElapsed time00:00:00,056Finish time2013/5/30 20:51:32NamelocalhostRows returned27Start time2013/5/30 20:51:32StateOpen建立非聚集索引:Aggregate S
7、tatusConnertion failuresElapsed timeFinish time00:00:00.0932013/6/1 9:03:28NamelocalhostRows returned27Start time2013/6/1 9:03:28StateOpen（3）对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分 类似（1）的三种情况进行执行比较，注意系统 处理的选择。select from studentwhere studentid between * 312011 and 131415施 ResultsMessagess ids_namese
8、xbirthday1| 31401i郭子敬里19&0-01-03 00:00:00.000231402胡雅杰里1980-014)300:00:00.000331403杨磊另19&0-01-0300:00;00.000431404冯宇宁更19&0-01-03 00:00:00.000531405黄荣勇1980-01-03 00:00:00.000631406宋月男19S0-01-03 00:00:00.000731407赵卫京里19&0-01-03 00:00:00.000331408刘树魁另19&XJ 193 00:00:00,000931409牛海窿勇19&0-01-03 00:00:00.
9、0001031410五丁空19&0-01-03 00:00:00.0001131411毛文高男1950-01-03 00:00:00.000d机 算 计机 算 计机 算 计机 算 计机 算 计机 算 计机机 算算 计计机机 算算 计计-63143144631446314G 6 4 4 3131463144631446314G 6 4 4 3131不建立索引:Aggregate StatusConnection failuresElapsed timeFinish timeNameRows retu rnedStart timeState00:00:00,0932013/6/1 500:03lo
10、calhost152013/6/1 9:00:03Open建立聚集索引:Aggregate StatusConnection failuresElapsed time00;X:00,089Finish time2013/5/30 20:53:55NamelocalhostRows returned15Start time2013/5/30 20:53:55StateOpen建立非聚集索引:Aggregate StatusConnection failuresElapsed time00:00:00,093Finish time2013/6/1 9:25:36Namelocal hostRows
11、 retu rned15Start time2013/6/1 9:25:35StateOpen索引代价。在有索引和无索引的情况下插入数据(例如在选课情况表SC上插入数据)，比 较插入的执行效率。insert into student values(317101 z ,张攀I，男，1993-1-1 00:00:00, J 计算机 I，3146,)delete from student where studentid = 1 31710,Messages(56 row3) affected)(1 rows) affected.)1 row all(select birthday from stud
12、entwhere department =，电信)Aggregate StatusConnection failuresElapsed time00;00:00,062Finish time2013/6/1 9:独56NamelocalhostRows returned0Start time2013/6/1 9:38:56StateOpen另：select student_name , birthdayfrom studentwhere departmen七！=电信 and birthday (select max(birthday) from studentwhere department
13、= , 电信)Aggregate StatusConnection failuresElapsed time00:00:00.046Finish time2013/6/1 9;40;24NamelocalhostRows returned0Start time2013/6/1 9:40:24StateOpen3、查询优化除了建立适当索引，对SQL语句重写外，还有 其他手段来进行查询调优，例如调整缓冲区大 小，事先建立视图等。设计实现下列查询，使之 运行效率最高。写出你的查询形式，以及调优过程；并说明最优 情况下的运行时间。(1)查找选修了每一门课的学生。方法一：with studentl(nu
14、m,account) asselect distinct student_id, count (course_id) from scgroup by student id )select student_namefrom studentl,studentwhere studentl.account=5 and studentl.num=student.student_idJ Results 岛Messages|student-nameAggregate StatusConnection failuresElapsed time00;00;00Q78Finish time2013/6/1 9:4
15、7:02NamelocalhostRows returned0Start time2013/6/1 9:47:01StateOpen方法二：select student_name from studentwhere not exists(select * from course where not exists ( select from sc where sc.student id=student.student id andcourse.course id=sc.course idAggregate StatusConnection failuresElapsed time00:00:00
16、,046F：n：sh time2013/6/1 10:07:45NamelocalhostRows returned0Start time2013/6/1 10:07:45StateOpen尝试了两种方法，相比较而言，第二种方法的执行速度更快。(2)查找至少选修了课程数据库原理和操作系统的学生的学号。方法一：(select studentid from studentview where course_name- 操作系统，) intersect(select student_id from studentviewwhere course_name- 1 数据库原理 r )_3 Results
17、 _台 Messagesstud ent J d1j 31401、 till 一2314033314044314065314-0763140873140983141193141210314141131415Aggregate StatusConnection failuresElapsed timeGOiOOiOO.IOSFinish time2013/6/1 10:03:38NamelocalhostRows returned20Start time2013/6/1 10:03:37StateOpen方法二:(select student_id from sc,coursewhere cou
18、rse . course_name=操作系统 and sc . course_id= course . courseid) intersect(select studentid from sc, coursewhere course . course_name=数据 库原理 . and sc. courseid = course. course id)Aggregate StatusConnection failure-sElapsed time00;00;00.078Finish time2013/6/110:05:33NamelocalhostRows retu rned20Start t
19、ime2013/6/110:05:33State相比较而言不使用视图的方法查询速度较快实验总结：1、本次实验遇到的第一个问题就是如何建立索引的问题，在之前实验的基础上 开始做，发现设置了主键之后，数据库会默认以主键为搜索码建立聚集 索引，而且无法删除索引也不好更改，所以只好重新建表格进行实验， 自己来设置聚集索引或非聚集索引2、实验遇到的第二个问题是在进行第二步实验的时候，原表格中没有age这一 属性，只有birthday这一属性，所以把age全部改为birthday进行实 验。3、实验的第三部分有点难度，想到了一种方法之后就很难再用另一种方法把它表示出来，参考了网上的一些资料，集思广益完成了查询优化的设计