SQL数据库查询优化

一、实验目的1.熟悉查询查询处理的过程；2.掌握查询优化的概念，理解查询优化的必要性；3.了解数据库的查询计划；4.掌握查询代价的分析方法，并且能通过配置参数或者修改SQL语句来降低查询代价。

二、实验环境SQL Server三、实验学时2学时四、实验要求1）求选修了00002号课程的学生姓名。

用SQL表达：SELECT Student.SnameFROM Student，SCWHERE Student.Sno=SC.Sno AND o=‘00002’2）三种实现方法：Q1=πSname(σStudent.Sno=SC.Sno∧o='2' (Student×SC))Q2=πSname(σo='2' (Student SC))Q3=πSname(Student σo='2'(SC))3）要求：本实验旨在说明查询优化的必要性，只要求把法一Q1与法二Q2和法三 Q3比较，从而说明查询优化的重要性五、实验内容及步骤（一）实验数据的准备-- 1.创建数据库create database stu_optimizationON( 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))godrop table sgo-- 3. 为学生表输入数据--输入30000个计科教育学生declare @num intdeclare @n intset @num=30000set @n=1while @n<=@numbegininsert into s(sno,sdept)select '151031'+right('00000'+cast(@n as varchar(5)),5),'计科教育' set @n=@n+1endselect * 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_sSELECT top 15000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--随机设置个学生的性别为女性update dbo.sset ssex='女'from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--（2）设置其他学生的性别为男性update dbo.sset ssex='男'where ssex is null--删除临时表TRUNCATE TABLE #TEMP_sDROP TABLE #TEMP_sselect * 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这个学生的年龄为21岁update dbo.sset sage=21from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这个学生的年龄为22岁update dbo.sset sage=22from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这个学生的年龄为23岁update dbo.sset sage=23from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这个学生的年龄为20岁update dbo.sset sage=20from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这个学生的年龄为19岁update dbo.sset sage=19from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这个学生的年龄为18岁update dbo.sset sage=18from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP TABLE #TEMP_supdate 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这些学生的姓名为李update dbo.sset sname='李'from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这些学生的姓名为王update dbo.sset sname='王'from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这些学生的姓名为陈update dbo.sset sname='陈'from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这些学生的姓名为刘update dbo.sset sname='刘'from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这些学生的姓名为张update dbo.sset sname='张'from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP 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_sSELECT top 5000 sno,sname,ssex,sage,sdept FROM dbo.sORDER BY NEWID()--设置这些学生的姓名为邱update dbo.sset sname='邱'from dbo.s inner join #TEMP_son dbo.s.sno=#TEMP_s.sno--删除临时表TRUNCATE TABLE #TEMP_sDROP TABLE #TEMP_s-- 7. 创建课程表，录入课程create table c(cno char(5),cname varchar(20),cpno char(5),ccredit tinyint)goinsert 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','程序设计','',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）创建学生成绩表sccreate 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_cursorfetch s_cursor into @snodeclare @s_row int,@n intset @s_row=30000set @n=1while @n<=@s_rowbegininsert dbo.sc(sno,cno)select @sno, cno from dbo.cfetch s_cursor into @snoset @n=@n+1endclose s_cursordeallocate s_cursor--（3）为学生选课表生成成绩--1）创建学生选课表sc12create 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 tinyintdeclare sc_cur cursor for select sno,cno from dbo.scopen sc_curfetch next from sc_cur into @sno,@cnoset @grdae=cast( floor(rand()*50) as int)+50declare @n int,@sc_row intset @n=1set @sc_row=600000while @n<=@sc_rowbegininsert into dbo.sc12(sno,cno,grade)values(@sno,@cno,@grdae)fetch next from sc_cur into @sno,@cnoset @grdae=cast( floor(rand()*50) as int)+50set @n=@n+1endclose sc_curdeallocate sc_cur说明：上面是查询优化数据库的模板程序。