1、数据库第三部分数据库第三部分实 验 报 告课程名称 数据库技术实践 实验项目 索引、存储过程和触发器、函数和游标 实验仪器 SQL Server 2008 系 别_计算机科学与技术系 _专 业_计算机科学与技术_ 班级/学号_xxxxxxxxxxxxxxxxxxxxxxxx学生姓名 _xxxxxxxxxxx 实验日期 _2xxxxxxxxxx成 绩 _ 指导教师 _ 梁琦 _ _1Production.ProductReview 表包含的列有：ProductID（int）、ReviewerName（nvarchar(50) ）和 Comments（nvarchar (3850)）。假设经常执行
2、下列形式的查询，请为该类查询创建合适的索引，以最大程度地提高查询效率。SELECT Comments,ReviewerNameFROM Production.ProductReview WHERE ProductID = 937 and ReviewerName like a-d%;create index pidx on Production.ProductReview(ProductID) include(Comments,ReviewerName) WHERE ProductID = 937建立索引前： 建立索引后：3在Person.Address表上创建具有一个键列（PostalCod
3、e）和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的包含列索引。查看索引建立前后下列查询语句的执行计划，观察索引对效率的提高情况。SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCodeFROM Person.AddressWHERE PostalCode BETWEEN N94000 and N95999;CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address(PostalCo
4、de) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); 索引使该查询速度变快了建立索引前： 建立索引后：4.设经常需要执行下列类型的查询，以统计2003年某一段时间各产品的销售总量。 SELECT ProductID, SUM(sod.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.OrderDate =
5、 2003-08-02 AND soh.OrderDate 2003-08-31GROUP BY sod.ProductID为尽可能提高该类查询的执行效率，请分别为Sales.SalesOrderHeader和Sales.SalesOrderDetail表建立合适的索引，并简单说明理由。查看索引建立前后上述查询语句的执行计划，观察索引对该查询的效率提高情况。索引使该查询速度变快了三存储过程和触发器如无特别说明，以下各题均利用第6章建立的Students数据库以及Student、Course和SC表实现。1创建满足下述要求的存储过程，并查看存储过程的执行结果。（1）查询每个学生的修课总学分，要求
6、列出学生学号及总学分。 CREATE PROCEDURE SELECT_STUDENTAS SELECT Sno,sum(Credit) as 总学分 FROM sc join course c on o = o Group by Sno EXEC SELECT_STUDENT（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”。执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。CREATE PROC informationdept CHAR(20)=计算机系ASSELECT sc$.sno,sname,sc$.cno,cname
7、,credit FROM student$ s inner join sc$ ON s.sno=sc$.sno inner join course$ c ON o=sc$.cnoWHERE dept=deptEXEC information 信息管理系EXEC SELECT_STUDENT1 通信工程系,null（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。CREATE PROCEDURE SELECT_STUDENT2t_Dept nvarchar(20),total_man int OUTPUTASSELECT total_man = COUNT(*) FROM stude
8、ntWHERE Dept=t_Dept AND Sex=男PRINT total_man DECLARE RE_man int -声明变量EXEC SELECT_STUDENT2 计算机系,RE_man OUTPUT-PRINT RE_man（4）查询指定学生（姓名）在指定学期的选课门数和考试平均成绩，要求姓名和学期为输入参数，选课门数和平均成绩用输出参数返回，平均成绩保留到小时点后2位。 create procedure ti_4 name char(10),semes int,countCno int output,avgG numeric(4,2) outputasselect coun
9、tCno =COUNT(*),avgG =avg(Grade*1.00) from SC join Course c on SC.Cno=c.Cnojoin Student s on s.Sno=SC.Snowhere Semester=semesand Sname=namegroup by SC.Cnodeclare c int ,a numeric(4,2)exec ti_4 李勇, 2,c output,a outputprint c print a（5）查询指定学生（学号）的选课门数。如果指定学生不存在，则返回代码1；如果指定的学生没有选课，则返回代码2；如果指定学生有选课，则返回代码
10、0，并用输出参数返回该学生的选课门数。create procedure ti_5sno char(7),Count int outputas if exists (select * from Student where Sno=sno) begin if exists (select * from sc where sno=sno) begin select Count=count(Cno) from Student left join sc on Student.Sno=sc.Sno where student.Sno=sno return 0 end else return 2 end e
11、lse return 1（6）删除指定学生（学号）的修课记录，如果指定的学生不存在，则显示提示信息“没有指定学生”；如果指定的学生没有选课，则显示提示信息“该学生没有选课”。学号为输入参数。 create procedure ti_6 sno char(7)as if sno is not null and exists(select * from Student where Sno=sno)begin if exists(select * from SC where Sno=sno) begin delete from SC where Sno=sno end else print 该学生没
12、有选课endelseprint没有指定学生（7）修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。 create procedure ti_7cno char(6),sem intas update Course set Semester=sem where Cno=cno（8）在Course表中插入一行数据，课程号、课程名、学分、开课学期均为输入参数。课程号为C100、课程名为操作系统、学分为4、开课学期为4，开课学期的默认值为3。如果学分大于10或者小于1，则不插入数据，并显示提示信息“学分为110间的整数”。 create procedure ti_8cno char(6),
13、cname varchar(20),credit tinyint,sem tinyintas if credit between 1 and 10 insert into Course values(cno,cname,credit,sem) else print 学分为110间的整数2创建满足下述要求的DML触发器（前触发器、后触发器均可），并验证触发器执行情况。（1）限制学生所在系的取值范围为计算机系，信息管理系，数学系，通信工程系 create trigger tri_depton Student after insert, updateas if exists (select * fr
14、om inserted where Sdept not in(计算机系,信息管理系,数学系,通信工程系) rollbackgo（2）限制每个学期开设的课程总学分在2030范围内。create TRIGGER eve_total_creditON course AFTER INSERTASdeclare t int SELECT t=SUM(C.Credit)FROM Course c JOIN INSERTED I ON I.Semester = c.Semester IF (t30)BEGIN PRINT 本学期课程学分限制在-30之间！ROLLBACKEND插入正常的数据：插入超限的数据提
15、醒：（3）限制每个学生每学期选课门数不能超过6门（设只针对单行插入操作）。CREATE TRIGGER eve_total_cnoON course AFTER INSERTASdeclare t int SELECT t=COUNT(*)FROM Course c JOIN INSERTED I ON I.Semester = c.Semester JOIN SC S ON S.cno=C.cnoIF (t6)BEGIN PRINT 本学期选课门数不能超过6门！ROLLBACKEND（4）限制不能删除有人选的课程。create trigger tri_delCnoon course afte
16、r deleteas if exists (select * from deleted d where d.Cno in(select Cno from sc) )rollbackgo（5）利用10.2.2例6建立的Teachers表和Depts表，编写实现如下要求的触发器：每当在Teachers表中修改了某个教师的职称时，自动维护Depts表中职称人数统计的一致性。（考虑同时修改多名教师职称的情况）（6）利用10.2.2例6建立的Teachers表和Depts表，首先为Depts表增加一个记录部门教师人数的列，列名为：DeptCount，类型为整型。然后编写实现如下要求的触发器：每当在Tea
17、chers表中插入一行数据或者是删除一行数据时，自动维护Depts表中的相关信息。四函数和游标如无特别说明，以下各题均利用第6章建立的Student、Course和SC表实现。3创建满足下述要求的用户自定义标量函数。（1）查询指定学生已经得到的修课总学分（考试及格的课程才能拿到学分），学号为输入参数，总学分为函数返回结果。并写出利用此函数查询0811101学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句。CREATE FUNCTION DBO.FIND_XF(SNO CHAR(7)RETURNS INT ASBEGIN DECLARE X INT SELECT X=SU
18、M(CREDIT) FROM STUDENT S JOIN SC ON S.SNO=SC.SNO JOIN COURSE C ON SC.CNO=C.CNO where grade=60 and s.sno=SNO RETURN X ENDSELECT sname AS 姓名,cname AS 课程名, credit AS 课程学分, grade AS 考试成绩, dbo.find_xf(s.sno) as 总学分 from STUDENT S JOIN SC ON S.SNO=SC.SNO JOIN COURSE C ON SC.CNO=C.CNO where s.sno=0811101 （
19、2）查询指定系在指定课程(课程号)的考试平均成绩。 CREATE FUNCTION DBO.FIND_AVG(DEPT CHAR(20),CNO CHAR(6)RETURNS numeric(4,2)AS BEGIN DECLARE AVG numeric(4,2)SELECT AVG=AVG(GRADE) FROM SC JOIN student s on sc.sno=s.sno join course c on o=owhere o=cno and dept=dept return AVG Endselect S.dept,So, dbo.FIND_AVG(S.dept,o) AS av
20、g_GRADE from sc join student s on sc.sno = s.sno where dept = 信息管理系（3）查询指定系的男生中选课门数超过指定门数的学生人数。create FUNCTION DBO.FIND_man(DEPT CHAR(20),CNO int)returns int AS BEGINDECLARE cum int select cum=COUNT(*) from student s join sc on s.sno=sc.sno where dept=dept and sex=男 group by s.Sno having COUNT(*) cn
21、oreturn cum Endselect sname,dept,cname, DBO.FIND_man(dept, 1) as 学生人数 from student s join sc on s.sno=sc.sno join course c on o=o where dept=计算机系 4创建满足下述要求的用户自定义内联表值函数。（1）查询选课门数在指定范围内的学生的姓名、所在系和所选的课程名。CREATE function find_1(x int)returns table return (select sname,dept,cname from student s join sc o
22、n s.sno=sc.sno join course c on o=o where s.sno in ( select sno from SC group by Sno having COUNT(*) x )-利用函数查询选课门数超过门的情况select * from find_1(3) （2）查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生姓名、课程名和考试成绩。create function find_2(x char(20)returns table return (SELECT sname,de
23、pt,cname,grade FROM student AS s LEFT JOIN sc ON s.sno = sc.sno LEFT JOIN course AS c ON o = o WHERE grade = 90 and s.dept = x ) select sname,cname,grade from find_2(计算机系) 5创建满足下述要求的用户自定义多语句表值函数。（1）查询指定系年龄最大的前2名学生的姓名和年龄，包括并列的情况。alter FUNCTION DBO.FIND_AGE (DEPT CHAR(20)RETURNS RET_FIND_AGE TABLE(SNA
24、ME CHAR(10),AGE INT )AS BEGIN INSERT INTO RET_FIND_AGESELECT TOP 2 WITH ties sname, YEAR(GETDATE()-YEAR(BirTHDAY) AGE FROM STUDENT WHERE DEPT = DEPTORDER BY AGE DESCRETURNENDSELECT SNAME,AGE from FIND_AGE(计算机系)（2）查询指定学生（姓名）的考试情况，列出姓名、所在系、修的课程名和考试情况，其中考试情况列的取值为：如果成绩大于等于90，则为“优”；如果成绩在8089，则为“良好”；如果成绩在
25、7079，则为“一般”；如果成绩在6069，则为“不太好”；如果成绩小于60，则为“很糟糕”。并写出利用此函数查询李勇的考试情况的SQL语句。create function find_3(x nchar(5)returns table return (SELECT sname,dept,cname,grade, case when grade =90 then 优 when grade between 80 and 89 then 良 when grade between 70 and 79 then 一般 when grade between 60 and 69 then 不太好 when
26、grade 60 then 很糟糕 END AS 考试情况 FROM student AS s LEFT JOIN sc ON s.sno = sc.sno LEFT JOIN course AS c ON o = o WHERE SNAME=x ) SELECT *FROM FIND_3(李勇)6创建满足下述要求的游标。（1）查询Java课程的考试情况，并按图11-18所示样式显示结果数据。declare cname varchar(20),cno char(8),sname nchar(5),sex nchar(1), dept nvarchar(20),grade smallint-（a
27、)声明游标declare cur_cno cursor for select distinct cname from Course where cno in (select cno from sc where grade is not null and cname = VB)open cur_cno-(3)取数据 fetch next from cur_cno into cnamewhile FETCH_STATUS = 0begin print 课程名: + cname print 姓名 性别所在系 成绩 - -(2)对每一门课程，查询选了这门课程的学生(高等数学):用游标实现 -（a）声明
28、游标 declare cur_s cursor for select sname,sex,dept,grade from student s join SC on s.Sno=SC.Sno join course c on o = o where cname =cname -（b）打开游标 open cur_s -取数据处理 fetch next from cur_s into sname,sex,dept,grade while FETCH_STATUS = 0 begin if grade is not null print sname + sex + + dept + +cast(gra
29、de as char(4) else print sname + sex + + dept + null fetch next from cur_s into sname,sex,dept,grade end close cur_s deallocate cur_s FETCH NEXT FROM cur_cno INTO cnameendclose cur_cnodeallocate cur_cno（2）统计每个系的男生人数和女生人数，并按图11-19所示样式显示结果数据。DECLARE counts INT,dept NVARCHAR(14),sex CHAR(10)DECLARE c2 CURSOR FOR SELECT dept,sex,count(*) FROM student$GROUP BY s
