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

实验六数据查询分析实验
实验目的
通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力，熟悉了解Sybase中查询分析器的使用，并进一步提高编写复杂查询的SQL 程序的能力。

实验内容
1.索引对查询的影响
（1）对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体学生的信息）：不建立索引，（学号上）建立非聚集索引，（学号上）建立聚集索引。

建立聚集索引：
create clustered index student
on student(student_id)
go
建立非聚集索引：
create nonclustered index student_index
on student(student_id)
go
用查询分析器的执行步骤和结果对执行进行分析比较。

select*from student
where student_id='30201'
不建立索引
建立聚集索引
建立非聚集索引
（2）对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似（1）的三种情况进行执行比较。

select*from student
where student_id>'30401'
不建立索引：
建立聚集索引：
建立非聚集索引：
（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 by
select avg(score)
from sc
group by course_id
having course_id='C01'
select avg(score)
from sc
where course_id='C01'
比较其查询效率可知，没有group by的查询时间比较短，查询效率较高（2）
select student_id,student_name,birthday
from student s1
where birthday=
(select max(birthday)
from student s2
where s1.department = s2.department）
另一个：
select department,max(birthday)as maxAge into tmp
from student
group by department;
select student_id,student_name,birthday
from student,tmp
where student.birthday = tmp.maxAge and tmp.department=student.department drop table tmp
查询结果来看，重写的执行时间要快一些，但相差不多，如果数据库比较大的话，执行效果也许更明显
（3）对下面两个查询进行比较
select student_name,birthday
from student
where department!='电信'and birthday>all
(select birthday
from student
where department ='电信'）
另：
select student_name,birthday
from student
where department!='电信'and birthday>
(select max(birthday)
from student
where department ='电信'）
3、查询优化
除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。

设计实现下列查询，使之运行效率最高。

写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。

（1）查找选修了每一门课的学生。

方法一：
with student1(num,account)as
(
select distinct student_id,count(course_id)
from sc
group by student_id
)
select student_name
from student1,student
where student1.account>=5 and student1.num=student.student_id
方法二：
select student_name
from student
where not exists
(
select*
from course
where not exists
(
select*
from sc
where sc.student_id=student.student_id and course.course_id=sc.course_id )
)
尝试了两种方法，相比较而言，第二种方法的执行速度更快。

（2）查找至少选修了课程数据库原理和操作系统的学生的学号。

方法一：
(select student_id
from studentview
where course_name='操作系统')
intersect
(select student_id
from studentview
where course_name='数据库原理')
方法二：
(select student_id
from sc,course
where course.course_name='操作系统'and sc.course_id= course.course_id)
intersect
(select student_id
from sc, course
where course.course_name='数据库原理'and sc. course_id = course. course_id)
相比较而言不使用视图的方法查询速度较快
实验总结：
1、本次实验遇到的第一个问题就是如何建立索引的问题，在之前实验的基础上
开始做，发现设置了主键之后，数据库会默认以主键为搜索码建立聚集索引，而且无法删除索引也不好更改，所以只好重新建表格进行实验，自己来设置聚集索引或非聚集索引
2、实验遇到的第二个问题是在进行第二步实验的时候，原表格中没有age这一
属性，只有birthday这一属性，所以把age全部改为birthday进行实验。

3、实验的第三部分有点难度，想到了一种方法之后就很难再用另一种方法把它
表示出来，参考了网上的一些资料，集思广益完成了查询优化的设计。