北邮大三数据库实验六数据查询分析实验.docx

实验六 数据查询分析实验实验目的通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力，熟悉了解Sybase中查询分析器的使用，并进一步提高编写复杂查询的SQL 程序的能力。实验内容1.索引对查询的影响（1）对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体学生的信息）：不建立索引，（学号上）建立非聚集索引，（学号上）建立聚集索引。建立聚集索引：create clustered index studenton student(student_id)go建立非聚集索引：create nonclustered index student_indexon student(student_id)go用查询分析器的执行步骤和结果对执行进行分析比较。select * from student where student_id=30201不建立索引建立聚集索引建立非聚集索引（2）对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似（1）的三种情况进行执行比较。select * from student where student_id30401不建立索引：建立聚集索引：建立非聚集索引：（3）对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。select * from student where student_id between 31201 and 31415不建立索引：建立聚集索引：建立非聚集索引：（4）索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC 上插入数据），比较插入的执行效率。insert into student values(31710,张攀,男,1993-1-1 00:00:00,计算机,3146)delete from student where student_id =31710无索引：建立聚集索引：建立非聚集索引：2、对相同查询功能不同查询语句的执行比较分析（1） group byselect avg(score)from scgroup by course_idhaving course_id=C01select avg(score)from scwhere course_id=C01比较其查询效率可知，没有group by的查询时间比较短，查询效率较高（2）select student_id,student_name,birthdayfrom student s1where birthday=(select max(birthday)from student s2where s1.department = s2.department）另一个：select department,max(birthday) as maxAge into tmpfrom studentgroup by department;select student_id,student_name,birthdayfrom student,tmpwhere student.birthday = tmp.maxAge and tmp.department=student.departmentdrop table tmp查询结果来看，重写的执行时间要快一些，但相差不多，如果数据库比较大的话，执行效果也许更明显（3）对下面两个查询进行比较select student_name,birthdayfrom studentwhere department!=电信 and birthday all(select birthdayfrom studentwhere department =电信）另：select student_name,birthdayfrom studentwhere department!=电信 and birthday (select max(birthday)from studentwhere department =电信）3、查询优化除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。（1） 查找选修了每一门课的学生。方法一：with student1(num,account) as(select distinct student_id, count(course_id)from scgroup by student_id)select student_namefrom student1,studentwhere student1.account=5 and student1.num=student.student_id方法二：select student_namefrom studentwhere not exists(select * from coursewhere not exists(select *from scwhere sc.student_id=student.student_id and course.course_id=sc.course_id)尝试了两种方法，相比较而言，第二种方法的执行速度更快。（2） 查找至少选修了课程数据库原理和操作系统的学生的学号。方法一：(select student_idfrom studentviewwhere course_name=操作系统)intersect(select student_idfrom studentview where course_name=数据库原理)方法二：(select student_idfrom sc,coursewhere course.course_name=操作系统 and sc.course_id= course.course_id)intersect(select student_idfrom sc, coursewhere course.course_name=数据库原理 and sc. course_id = course. course_id)相比较而言不使用视图的方法查询速度较快实验总结：1、 本次实验遇到的第一个问题就是如何建立索引的问题，在之前实验的基础上开始做，发现设置了主键之后，数据库会默认以主键为搜索码建立聚集索引，而且无法删除索引也不好更改，所以只好重新建表格进行实验，自己来设置聚集索引或非聚集索