数据库原理-实验8-查询优化

一、实验目的

1. 熟悉查询查询处理的过程；

2. 掌握查询优化的概念，理解查询优化的必要性； 3. 了解数据库的查询计划；

4. 掌握查询代价的分析方法，并且能通过配置参数或者修改SQL语句来降低查询代价。 二、实验环境

SQL Server 2008

三、实验学时

2学时

四、实验要求

1）求选修了00002号课程的学生姓名。用SQL表达： FROM Student，SC

WHERE Student.Sno=SC.Sno AND SC.Cno=‘00002’ 2）三种实现方法： SELECT Student.Sname

Q1=πSname(σStudent.Sno=SC.Sno∧Sc.Cno='2' (Student×SC)) Q2=πSname(σSc.Cno='2' (Student Q3=πSname(Student

五、实验内容及步骤

（一）实验数据的准备

-- 1.创建数据库（事先在D盘新建一个文件夹stu_opti） create database stu_optimization ON

( NAME = stu_opti,

FILENAME = 'd:\\stu_opti\\stu_opti.mdf', SIZE = 100,

SC))

σSc.Cno='2'(SC))

3）要求：本实验旨在说明查询优化的必要性，只要求把法一Q1与法二Q2

和法三 Q3比较，从而说明查询优化的重要性

MAXSIZE = 500, FILEGROWTH = 10 ) LOG ON

( NAME = 'stu_opti_log',

FILENAME = 'd:\\stu_opti\\stu_opti_log.ldf', SIZE = 50MB,

MAXSIZE = 250MB, FILEGROWTH = 5MB ) GO

-- 2. 创建学生表 create table s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10)) go

drop table s go

-- 3. 为学生表输入数据

--输入30000个计科教育学生 declare @num int declare @n int set @num=30000 set @n=1

while @n<=@num begin

insert into s(sno,sdept)

select '151031'+right('00000'+cast(@n as varchar(5)),5),'计科教育'

set @n=@n+1 end

select * from s

--4. 设置学生性别

--（1）设置15000个学生的性别为女性 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中

INSERT INTO #TEMP_s

SELECT top 15000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--随机设置个学生的性别为女性 update dbo.s set ssex='女'

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--（2）设置其他学生的性别为男性 update dbo.s set ssex='男'

where ssex is null

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

select * from s order by sno

--5. 设置学生年龄

--（1）为5000个学生设置其年龄为21岁 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这个学生的年龄为21岁 update dbo.s set sage=21

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（2）为5000个学生设置其年龄为22岁 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这个学生的年龄为22岁 update dbo.s set sage=22

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（3） 为5000个学生设置其年龄为23岁 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这个学生的年龄为23岁 update dbo.s set sage=23

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（4） 为5000个学生设置其年龄为20岁 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这个学生的年龄为20岁 update dbo.s set sage=20

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--6. 设置学生姓名

--（1）为5000个学生设置其姓名为李

--创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这些学生的姓名为李 update dbo.s set sname='李'

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（2） 为5000个学生设置其姓名为王 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这些学生的姓名为王 update dbo.s set sname='王'

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（3） 为5000个学生设置其姓名为王 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这些学生的姓名为陈 update dbo.s set sname='陈'

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（4） 为5000个学生设置其姓名为刘 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这些学生的姓名为刘 update dbo.s set sname='刘'

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（5） 为5000个学生设置其姓名为张 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这些学生的姓名为张 update dbo.s set sname='张'

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

--（6） 为5000个学生设置其姓名为邱 --创建临时表，其结构与学生表的结构一致 CREATE TABLE #TEMP_s( sno char(11), sname char(10), ssex char(2), sage tinyint, sdept char(10))

--从学生表中随机产生行数据插入到临时表中 INSERT INTO #TEMP_s

SELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.s

ORDER BY NEWID()

--设置这些学生的姓名为邱 update dbo.s set sname='邱'

from dbo.s inner join #TEMP_s on dbo.s.sno=#TEMP_s.sno

--删除临时表

TRUNCATE TABLE #TEMP_s DROP TABLE #TEMP_s

-- 7. 创建课程表，录入课程 create table c( cno char(5),

cname varchar(20), cpno char(5), ccredit tinyint) go

insert into dbo.c(cno,cname,cpno,ccredit) values

--('00001','计算机导论','',2),

--('00002','高级语言程序设计','',2), --('00003','离算数学','',3),

--('00004','数据结构','00002',3), --('00005','c#','00002',2),

--('00006','面向对象程序设计','00005',2), --('00007','数据库原理','00004',3), --('00008','操作系统','',3),

--('00009','计算机组成原理','',3), --('00010','编译原理','',3), --('00011','软件工程','',2),

--('00012','数字图像处理','',2), --('00013','asp.net程序设计','',2), --('00014','平面动画设计','',2),

--('00015','linux操作系统','00008',2), --('00016','数据库新技术','00007',2), --('00017','嵌入式技术','',2),

--('00018','算法设计与分析','00004',2), --('00019','nosql','',2),

('00020','数据库实用技术','00007',2)

select * from c

--8. 创建学生成绩表，录入成绩 --（1）创建学生成绩表sc create table sc(

sno char(11) not null, cno char(5) not null, grade tinyint, primary key(sno,cno)) go

--（2）录入学号和课程号 declare @sno char(11)

declare s_cursor cursor local for select sno from dbo.s open s_cursor

fetch s_cursor into @sno declare @s_row int,@n int set @s_row=30000 set @n=1

while @n<=@s_row begin

insert dbo.sc(sno,cno) select @sno, cno from dbo.c fetch s_cursor into @sno set @n=@n+1 end

close s_cursor

deallocate s_cursor

--（3）为学生选课表生成成绩 --1）创建学生选课表sc12 create table sc12( sno char(11), cno char(5), grade int,

primary key(sno,cno)) go

--2）将学生选课表sc中的选课信息插入到学生选课表sc12，并随机生成相应的选课成绩

declare @sno char(11),@cno char(5) declare @grdae tinyint

declare sc_cur cursor for select sno,cno from dbo.sc open sc_cur

fetch next from sc_cur into @sno,@cno

set @grdae=cast( floor(rand()*50) as int)+50 declare @n int,@sc_row int set @n=1

set @sc_row=600000 while @n<=@sc_row begin

insert into dbo.sc12(sno,cno,grade) values(@sno,@cno,@grdae)

fetch next from sc_cur into @sno,@cno set @grdae=cast( floor(rand()*50) as int)+50 set @n=@n+1 end

close sc_cur

deallocate sc_cur

说明：上面是查询优化数据库的模板程序。由于笛卡尔积计算量非常之大，上面的数据由普通的计算机作为数据服务器难以实现。 （二）实验实现：

本实验在sql server 2000环境下实现 1. 数据库：stu_optimization

1）学生表：s22（5000个元祖） 2）课程表：c22（20个元祖）

3）学生选课表：sc22（100000元祖） 2. 三种方法的时间代价

1）法一Q1中只进行笛卡尔积的时间：

2）法二、法三的时间

这里只是从时间代价上说明查询优化的必要性。通过简单的查询语句难以实现法二和法三在DBMS中的实现细节。

本文来源：https://www.bwwdw.com/article/jonw.html