一、实验目的
1.熟悉查询查询处理的过程；
2.掌握查询优化的概念，理解查询优化的必要性；
3.了解数据库的查询计划；
4.掌握查询代价的分析方法，并且能通过配置参数或者修改SQL语句来降低
查询代价。
二、实验环境
SQL Server
三、实验学时
2学时
四、实验要求
1）求选修了00002号课程的学生姓名。用SQL表达：
SELECT Student.Sname
FROM Student，SC
WHERE Student.Sno=SC.Sno AND https://www.doczj.com/doc/53797088.html,o=‘00002’
2）三种实现方法：
Q1=πSname(σStudent.Sno=SC.Sno∧https://www.doczj.com/doc/53797088.html,o='2' (Student×SC))
Q2=πSname(σhttps://www.doczj.com/doc/53797088.html,o='2' (Student SC))
Q3=πSname(Student σhttps://www.doczj.com/doc/53797088.html,o='2'(SC))
3）要求：本实验旨在说明查询优化的必要性，只要求把法一Q1与法二Q2和法三 Q3比较，从而说明查询优化的重要性
五、实验内容及步骤
（一）实验数据的准备
-- 1.创建数据库
create database stu_optimization
ON
( NAME = stu_opti,
FILENAME = 'E:\stu_opti\stu_opti.mdf',
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 10 )
LOG ON
( NAME = 'stu_opti_log',
FILENAME = 'E:\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
--删除临时表
TRUNCATE TABLE #TEMP_s
DROP TABLE #TEMP_s
--（5）为5000个学生设置其年龄为19岁
--创建临时表，其结构与学生表的结构一致
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()
--设置这个学生的年龄为19岁
update dbo.s
set sage=19
from dbo.s inner join #TEMP_s
on dbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATE TABLE #TEMP_s
DROP TABLE #TEMP_s
--（6）为5000个学生设置其年龄为18岁
--创建临时表，其结构与学生表的结构一致
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()
--设置这个学生的年龄为18岁
update dbo.s
set sage=18
from dbo.s inner join #TEMP_s
on dbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATE TABLE #TEMP_s
DROP TABLE #TEMP_s
update s set sage=21 where sage is null select * from s order by 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','https://www.doczj.com/doc/53797088.html,程序设计','',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中的实现细节。