SQL_Server_2000实验指导书(2008).doc

《SQL_Server_2000实验指导书(2008).doc》由会员分享，可在线阅读，更多相关《SQL_Server_2000实验指导书(2008).doc（40页珍藏版）》请在三一文库上搜索。

1、第 1 页 共 40 页 SQLSQL ServerServer 20002000 数据库数据库 实验指导书实验指导书 第 2 页 共 40 页 实验一实验一 熟悉熟悉 SQLSQL ServerServer 20002000 常用工具的使用常用工具的使用 一、实验目的 1、了解 SQL Server 2000 的安装过程 2、熟悉 SQL Server 2000 服务管理器的启动方法 3、熟悉 SQL Server 2000 常用工具的使用 二、实验内容 1、练习 SQL Server 2000 服务器的几种启动方式： 1）通过“开始”菜单启动服务管理器 2）设置在启动 OS 时自动启动 S

2、QL Server 服务管理器 3）使用命令方式启动服务管理器 4）练习 SQL Server 2000 服务的开始、暂停、继续、停止等操作 2、打开企业管理器，观察企业管理器的各个组成部分，练习有关的操作。 3、打开查询分析器，观察查询分析器的组成部分，练习有关的操作。 4、练习 SQL Server 2000 的联机丛书的使用，并使用联机丛书学习自己感兴趣的知识。 三、选做题 1、创建 SQL Server 服务器组。 2、练习 SQL Server 2000 的数据导入/导出操作。 四、填写实验报告要求 1及时撰写实验报告，在实验完成后一内一周内上交； 2内容详略得当，字迹工整； 3既要

3、包含所做实验的全部内容，又要包含实验中出现的问题和如何解决的； 4最后要谈谈本次实验的体会。 第 3 页 共 40 页 实验二实验二 标准标准 SQLSQL 的数据定义的数据定义 一、实验目的一、实验目的 1、掌握使用标准 SQL 创建和删除数据库。 2、掌握使用标准 SQL 创建和删除数据表。 二、实验内容二、实验内容 1、使用标准 SQL 的 create database 命令创建图书管理系统数据库。 2、使用标准 SQL 的 create table 命令创建图书管理系统数据库中的四个数据表的结构： 1）readers（读者信息表） 2）books(图书信息表) 3）borrowinf

4、（借阅信息表） 4）readtype（读者类型表） 3、对以上四个表分别用 insert 命令输入多条记录。 三、数据库中表的结构及数据 1、readers 表结构： Readers 表的部分数据： 2、books 表的结构： 第 4 页 共 40 页 Books 表的数据： 3、borrowinf 表的结构： Borrowinf 表的数据： 4、readtype 表的结构： Readtype 表的数据： 第 5 页 共 40 页 实验三实验三 数据更新操作与数据更新操作与标准标准 SQLSQL 的简单查询的简单查询 一、实验目的 1、掌握使用标准 SQL 进行数据的增加、删除及修改 2、掌握

5、无条件查询和条件查询来实现选择运算 3、掌握 select 子句的使用 4、查询结果的排序 二、实验内容 1、用 insert 命令在 readers 表中插入两条记录。 2、用 update 命令将 readtype 表中教师的限借阅数量修改为 30，借阅期限修改为 180 天。 3、用 delete 命令删除书名为“数据结构”的图书信息。 4、查询读者表的所有信息。 5、查阅编号为”2002060328”的读者的借阅信息。 6、查询图书表中“清华大学出版社”出版的图书书名和作者。 7、查询书名中包含“程序设计”的图书信息。 8、查询图书表中“清华大学出版社”出版的图书信息，结果按图书单价升

6、序排列。 9、查询成绩最高的前 3 名学生的学号、成绩。 10、 查询选修了“数据库原理”课程成绩最差的 3 名学生的学号成绩。 四、参考语句四、参考语句 1 1、insertinsert intointo readersreaders values()values() 2 2、updateupdate readertypereadertype setset 限借阅数量限借阅数量=30,=30,借阅期限借阅期限=180=180 wherewhere 类型名称类型名称=教师教师 3 3、deletedelete fromfrom booksbooks wherewhere 书名书名=数据结构数据

7、结构 4 4、selectselect * * fromfrom readersreaders 5 5、查阅编号为、查阅编号为”2002060328”2002060328”的读者的借阅信息。的读者的借阅信息。 SELECTSELECT * * FROMFROM borrowinfborrowinf WHEREWHERE 读者编号读者编号=2002060328=2002060328 6 6、selectselect 书名书名, ,作者作者 fromfrom booksbooks wherewhere 出版社出版社=清华大学出版社清华大学出版社 7 7、查询书名中包含、查询书名中包含“程序设计程序

8、设计”的图书信息的图书信息 SELECTSELECT * * FROMFROM booksbooks 第 6 页 共 40 页 WHEREWHERE 书名书名 likelike %程序设计程序设计% 8 8、selectselect * * fromfrom booksbooks wherewhere 出版社出版社=清华大学出版社清华大学出版社 OrderOrder byby 单价单价 ascasc 9 9、selectselect toptop 3 3 学号学号, ,成绩成绩 fromfrom 选课成绩选课成绩 orderorder byby 成绩成绩 descdesc 1010、selec

9、tselect toptop 3 3 学号学号, ,成绩成绩 fromfrom 选课成绩选课成绩 sc,sc,课程课程 c c wherewhere sc.sc.学号学号=c.=c.学号学号 andand 课程名课程名=数据库原理数据库原理 orderorder byby 成绩成绩 ascasc 第 7 页 共 40 页 实验四实验四 分组统计查询分组统计查询 一、一、实验目的实验目的 1、掌握数据的分组统计 2、掌握连接查询 二、实验内容二、实验内容 1、查询图书馆的藏书量。 2、查询图书馆的图书总价值。 3、查询各出版社的馆藏图书数量。 4、查询 2004-1-1 和 2004-12-31

10、 之间各读者的借阅数量 5、查询 2004-1-1 和 2004-12-31 之间作者为“粱晓峰”的图书的借阅情况 6、使用统计函数计算选课成绩表中的最高分、最低分和平均分。 7、使用统计函数计算选课成绩表中每门课的最高分、最低分和平均分。 8、查询选修课程门数超过 2（包括 2 门）的学生学号、选修门数。 三、参考语句三、参考语句 1、SELECTSELECT COUNT(*)COUNT(*) FROMFROM booksbooks 2 2、SELECTSELECT SUM(SUM(定价定价) ) FROMFROM booksbooks 3、SELECTSELECT 出版社，出版社，COUN

11、T(*)COUNT(*) FROMFROM booksbooks GROUPGROUP BYBY 出版社出版社 4 4、SelectSelect 读者编号读者编号,count(*),count(*) asas 借阅数量借阅数量 FROMFROM borrowinfborrowinf WHEREWHERE 借期借期 betweenbetween 2004-1-12004-1-1 andand 2004-12-312004-12-31 GROUPGROUP BYBY 读者编号读者编号 5 5、SelectSelect a a .*.* FROMFROM borrowinfborrowinf a a

12、 innerinner joinjoin booksbooks b b onon a.a.图书编号图书编号=b.=b.编号编号 WHEREWHERE 借期借期 betweenbetween 2004-1-12004-1-1 andand 2004-12-31and2004-12-31and 作者作者=梁晓峰梁晓峰 6 6、selectselect max(max(成绩成绩) ) asas 最高分最高分,min(,min(成绩成绩) ) asas 最低分最低分,avg(,avg(成绩成绩) ) asas 平均分平均分 FromFrom 选课成绩选课成绩 7 7、selectselect 课程号课

13、程号,max(,max(成绩成绩) ) asas 最高分最高分,min(,min(成绩成绩) ) asas 最低分最低分,avg(,avg(成绩成绩) ) asas 平均平均 分分 FromFrom 选课成绩选课成绩 GroupGroup byby 课程号课程号 8 8、SELECTSELECT 学号学号,COUNT(*),COUNT(*) ASAS 门数门数 FROMFROM 选课成绩选课成绩 ; ; GROUP BY 学号学号 HAVING COUNT(*)=2 第 8 页 共 40 页 实验五实验五 子查询和连接查询连接查询 一、一、实验目的实验目的 1、进一步熟悉简单查询中的各种子句的

14、使用； 2、理解联合查询； 3、掌握带有子查询的条件的几种常见格式； 二、二、实验内容实验内容 1、查询选修了课程的学生信息。 2、查询没有选修课程的学生信息。 3、使用嵌套查询，查询定价大于所有图书平均定价的图书信息。 4、查询高等教育出版社出版的定价高于所有图书平均定价的图书信息。 5、查询选修了 kc02 成绩最高的学生的学号、姓名、课程号、成绩。 6、查询选修了 “数据库原理”课程的学生信息。 7、查询选修课程门数超过 2（包括 2 门）的学生信息。 8、查询选修人数超过 2（包括 2 人）的课程号，课程名。 三、三、参考语句参考语句 1、SELECTSELECT * * FROMFR

15、OM 学生学生 WHEREWHERE 学号学号 ININ ; ; (SELECT(SELECT 学号学号 FROMFROM 成绩成绩) ) 2、SELECTSELECT * * FROMFROM 学生学生 WHEREWHERE 学号学号 NOTNOT ININ ; ; (SELECT(SELECT 学号学号 FROMFROM 成绩成绩) ) 3、SELECTSELECT * * FROMFROM booksbooks WHEREWHERE 定价定价any;any; (SELECT(SELECT AVG(AVG(定价定价) ) FROMFROM books)books) 4、SELECTSELE

16、CT * * FROMFROM booksbooks WHEREWHERE 出版社出版社=高等教育出版社高等教育出版社 andand 定价定价any;any; (SELECT(SELECT AVG(AVG(定价定价) ) FROMFROM books)books) 5 5、SELECTSELECT TOPTOP 1 1 S.S.学号学号, ,姓名姓名, ,课程号课程号, ,成绩成绩 FROMFROM 学生学生 S,S,选修成绩选修成绩 SCSC WHEREWHERE S.S.学号学号=SC.=SC.学号学号 ANDAND 课程号课程号=kc02=kc02 ORDERORDER BYBY 成绩成

17、绩 DESCDESC 6 6、SELECTSELECT S.*S.* FROMFROM 学生学生 S,S,选修成绩选修成绩 SCSC WHEREWHERE S.S.学号学号=SC.=SC.学号学号 ANDAND 课程号课程号= = (SELECT(SELECT 课程号课程号 FROMFROM 课程课程 WHEREWHERE 课程名课程名=数据库原理数据库原理) 或或 SELECTSELECT S.*S.* FROMFROM 学生学生 S,S,选修成绩选修成绩 SC,SC,课程课程 C C WHEREWHERE S.S.学号学号=SC.=SC.学号学号 ANDAND SC.SC.课程号课程号=C

18、.=C.课程号课程号 ANDAND 课程名课程名=数据库原理数据库原理 第 9 页 共 40 页 7、SELECTSELECT 学生学生.*,COUNT(*).*,COUNT(*) ASAS 门数门数 FROMFROM 学生学生 S,S,选修成绩选修成绩 SC;SC; WHEREWHERE S.S.学号学号=SC.=SC.学号；学号； GROUP BY 学号学号 HAVING COUNT(*)=2 8、SELECTSELECT 课程号课程号, ,课程名课程名,COUNT(*),COUNT(*) ASAS 人数人数 FROMFROM 选修成绩选修成绩 SCSC JOINJOIN 课程课程 C C

19、 ONON SC.SC.课程号课程号=C.=C.课程号课程号; ; GROUP BY 课程号课程号 HAVING COUNT(*)=2 第 10 页 共 40 页 实验六实验六 创建数据库及数据库表创建数据库及数据库表 一、实验目的 1、掌握使用企业管理器及 T-SQL 语句创建和管理数据库 2、掌握使用企业管理器及 T-SQL 语句创建和修改数据库表结构 3、掌握数据库表的数据维护操作 二、实验内容 1 （1）分别使用企业管理器、向导、CREATE DATABASE 命令三种方法创建数据库 和事务日志。要求如下： 数据库名字为 Sale，尺寸为 5MB； 数据文件为 3MB，最大尺寸为 10

20、MB，文件增量为 1MB； 事务日志为 2MB，最大尺寸为 15MB，文件增量为 1MB； 位置均为 C:目录下。 （2）对第 1 步所建的数据库 Sale 进行如下操作： 扩充或缩小数据库的容量（容量任意） ； 重命名数据库（名称任意） ； 删除数据库。 2. 用 T-SQL 语句创建 KCGL（课程管理）数据库。 3在数据库 KCGL 中分别用企业管理器和 T-SQL 语句创建如下四个表： STUDENT（学号，姓名，性别，班级名，系别代号，地址，出生日期） COURSE（课程号，课程名，教师） SC（学号，课程号，成绩） DEPT(代号，系名称) 要求： （1） 各字段数据类型请读者按语

21、义分析自行决定。 （2） 每个表要定义主键。 （3） 为性别字段定义约束条件：只能为“男”或者“女” 。 （4） 为成绩字段定义约束条件：只能取 0-100 之间的数。 （5） 为地址字段定义默认约束：江苏南京。 输入数据记录，验证各种约束，以加深对各种约束作用的理解。 第 11 页 共 40 页 STUDENT 学号姓名性别班级名系别代号地址出生日期 011110李建国男计 012101湖北武汉1984-9-28 011103李宁女电 013402江西九江1985-5-6 011202赵娜女英 011203广西南宁1984-2-21 021204孙亮男电 013402湖南长沙1986-9-8

22、 011110赵琳女计 012101江苏南京1985-11-18 021405罗宇波男英 011203江苏南通1985-12-12 SC 学号课程号成绩 0111100150 0212040270 0111030390 0112020498 0214050267 0212040345 0111100280 0214050475 0112020389 0111100459 0111030180 COURSE 课程号课程名教师 01英语刘江虎 02数学李小则 03C 语言何晓敏 04数据库张超 DEPT 代号系别号 01计算机系 02机电系 03英语系 第 12 页 共 40 页 实验七实验七 索

23、引、约束与视图索引、约束与视图 a)a) 实验目的实验目的 1、利用企业管理器创建和管理索引 2、利用 T-SQL 语句创建和删除索引 3、利用表的属性对话框设置表的约束 4、利用企业管理器创建和删除视图 二、实验内容二、实验内容 根据习题三的四个表，完成如下操作： 1、利用向导创建 STUDENT、COURSE 表的主键索引 2、利用企业管理器在 STUDENT、COURSE 表上创建聚集索引和惟一索引 3、利用管理索引对话框新建、修改和删除索引 4、利用 T-SQL 语句创建索引 5、打开 STUDENT 表设计窗口，设置性别的域为（男 ， 女 ） ，默认值为男 。 6、建立相应的视图，给

24、出借阅超期信息单 7、用查询分析器创建一个从表 Student、Course、SC、DEPT 中查看“计算机系”的所有 同学及成绩情况的视图，视图名为 Computedept_view，要求显示学生的学号、姓名、班 级名、课程名称、课程的成绩。 8、从前面建的 Computedept_view 视图查询选修了“计算机基础”课程的学生的学号， 姓名，班级名，课程成绩 9、用查询分析器删除视图 Computedept_view 10、用查询分析器创建一个从学生表 Student、班级 Class、选课表 Choice 和课程表 Course 中查看“计算机系”的所有同学及成绩情况的视图，视图名为

25、Computedept_view，要求显示学生的学号、姓名、班级名、课程名称、课程的成绩。 11、从前面建的 Computedept_view 视图查询选修了“计算机基础”课程的学生的学 号，姓名，班级名，课程成绩 12、用查询分析器删除视图 Computedept_view 第 13 页 共 40 页 实验八实验八 自定义类型和变量的使用自定义类型和变量的使用 一、实验目的一、实验目的 1掌握 Transact-SQL 编程基础知识； 2掌握局部变量和全局变量的使用； 3了解系统常用内置函数的使用； 4掌握用户自定义函数的创建和调用。 二、实验内容二、实验内容 1编写 T-SQL 查找李文才

26、的左右同桌 ？学生信息表(stuInfo)： 分析：分析：第一步，找出“李文才“的座位号； 第二步，李文才的座位号加 1 或减 1 2、全局变量的使用 变量含义 ERROR 最后一个 T-SQL 错误的错误号 IDENTITY 最后一次插入的标识值 LANGUAGE 当前使用的语言的名称 MAX_CONNECTIONS 可以创建的同时连接的最大数目 ROWCOUNT 受上一个 SQL 语句影响的行数 SERVERNAME 本地服务器的名称 TRANSCOUNT 当前连接打开的事务数 VERSION SQL Server 的版本信息 全局变量示例： print SQL Server 的版本+VE

27、RSION 第 14 页 共 40 页 print 服务器的名称: +SERVERNAME INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(武松,s25328,男,23) -如果大于 0 表示上一条语句执行有错误 print 当前错误号+convert(varchar(5),ERROR) print 刚才报名的学员，座位号为: +convert(varchar(5),IDENTITY ) UPDATE stuinfo SET stuAge=85 WHERE stuName=李文才 print 当前错误号+convert(varch

28、ar(5),ERROR) GO 3、在查询分析器中运行如下命令并理解各数学函数的功能： Select CEILING(134.393) Select FLOOR(134.3393) Select ABS(-20) Select PI() Select POWER(4,3) Select SQRT(16) 4、定义函数在表 books 中，查找最便宜的书价。并调用该函数。 5、定义函数在表 books 中，查找指定出版社出版的图书中最便宜的书价。并调用该函数。 三、参考程序三、参考程序 1、 USE Studb GO 第 15 页 共 40 页 /*-查找李文才的信息-*/ DECLARE na

29、me varchar(8) -学员姓名 SET name=李文才 -使用 SET 赋值 SELECT * FROM stuInfo WHERE stuName = name /*-查找李文才的左右同桌-*/ DECLARE seat int -座位号 SELECT seat=stuSeat FROM stuInfo -使用 SELECT 赋值 WHERE stuName=name SELECT * FROM stuInfo WHERE (stuSeat = seat+1) OR (stuSeat = seat-1) GO 4 use 图书管理 GO create function mindj(

30、) -即使无参函数，也不能省略（） returns int Begin declare price int Select price=min(定价) from books return price end 调用格式：select dbo.mindj() -调用时需指明函数的所有者(即 dbo) 5如何对上题程序进行修改，完成本题功能。 第 16 页 共 40 页 实验九实验九 T-SQLT-SQL 流程控制语句的使用流程控制语句的使用 【实验目的实验目的】 1、掌握 T-SQL 的变量声明语句及赋值语句 2、熟悉 SQL Server2000 的数据类型、变量、运算符及表达式 3、掌握 T-S

31、QL 的流程控制语句：IF-ELSE、WHILE、CASE-END 【实验内容实验内容】 1、定义一个局部变量score，并为其赋值，然后设计一个 IF-ELSE 语句，如果 score=60 则输出“合格” ，否则输出“不合格”信息。 2、定义一个局部变量score，并为其赋值，然后设计一个 CASE 语句，按照以下规则 输出信息： 90-100：优秀 80-90：良好 7080：中 6070：及格 60 以下：不及格 其它：不合法的值 3、计算 S=1+2+3+100，用 WHILE 语句设计 4、计算 S=20+21+22+231 【实验要求实验要求】 1、 第 1、2 题要求输入不同的

32、变量值使程序进入不同的分支至少执行一次，以测试程序 是否正确。 2、 第 3、4 题程序执行后检查结果是否与老师公布的答案一样。 【参考内容参考内容】 1、declare score float set score=78 第 17 页 共 40 页 IF score=60 Print 合格 Else Print 不合格 2、declare score float，dj char(6) set score=78 select dj=case when score between 90 and 100 then 优 when score between 80 and 89 then 良 when

33、score between 70 and 79 then 中 when score between 60 and 69 then 及格 else 不及格 end print 成绩等级+dj 3、 declare sum1 int,i int set sum1=0 set i=1 while i=100 begin set sum1=sum1+i set i=i+1 End Print 1-100 之和：+convert(varchar(8),sum1) 4、 declare sum2 float,i int,t float -由于数据较大，乘方和求和不能用 int set sum2=1 set

34、 t=1 set i=1 while i70 AND labAvg70) 第 22 页 共 40 页 print 本班考试成绩：优秀 ELSE print 本班考试成绩：较差 print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GO 调用该存储过程：exec proc_stu1 8、CREATE P

35、ROCEDURE proc_stu2 writtenPass int=60, labPass int=60 AS print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExam=writtenPass and labExam=labPass SELECT num=count(*) FROM stu

36、Marks WHERE writtenExam=writtenPass and labExam=labPass Set stunum=num GO 调用该存储过程： declare n int exec proc_stu3 writtenPass=60,labPass=58,stunum=n out print 通过考试的人数：+convert(char(2),n) 10、 USE studb GO CREATE PROCEDURE proc_stu6 writtenPass int=60, labPass int=60, jgl float output AS DECLARE num1 fl

37、oat, num2 float print 参加本次考试及格率： SELECT num1=count(*) FROM stuMarks WHERE writtenExam=writtenPass and labExam=labPass SELECT num2=count(*) FROM stuMarks Set jgl=num1/num2 GO 第 24 页 共 40 页 -调用该存储过程： declare jg float exec proc_stu6 writtenPass=60,labPass=58,jgl=jg out print 通过考试的人数：+convert(varchar(5)

38、,jg) 第 25 页 共 40 页 实验十一实验十一 触发器的应用触发器的应用 【实验目的实验目的】 1.掌握利用掌握利用 T-SQL 语句创建触发器语句创建触发器 2.掌握利用企业管理器创建触发器掌握利用企业管理器创建触发器 3.理解使用触发器来维护数据完整性理解使用触发器来维护数据完整性 【实验预备知识实验预备知识】 1触发器的特点： 触发器定义在特定的表上，与表相关 自动触发执行 不能直接调用 是一个事务（可回滚） 2创建触发器的语法： CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE, INSER

39、T, UPDATE AS T-SQL 语句 GO 【实验内容实验内容】 1.创建一个当学生表中插入一个新同学信息时能自动列出全部同学学生信息的触 发器 Display_trigger 2.调用上次实验创建的存储过程 Insert_student，向学生表中插入一新同学，看触 发器 Display_trigger 是否被执行。 3.练习和管理触发器。 （1） 建立数据库 testdb，并在数据库中建立两个表： 第 26 页 共 40 页 Txl(ID int, Name char(10), Age int) Person_ counts(Person_ count int) （2） 使用 T-S

40、QL 编写一个触发器 tr_person_ins，每当 txl 表中插入一行数据时， 表 Person_ counts 中对应的数量也相应地发生变化。 （3） 使用企业管理器创建一个触发器 tr_person_del，每当 txl 表中删除记录时， 表 Person_ counts 中对应的数量也相应地发生变化。 （4） 使用企业管理器查看触发器 tr_person_del 的内容，并将该触发器的内容加密。 【参考代码参考代码】 1、 USE 学生选课 GO create trigger Display_trigger on 学生 for insert AS Select * from 学生

41、GO 2、EXEC Insert_student stuno=200602011, name=张丽丽, sex=女, class=3, birthday=1988-2-15 3、 （1）CREATE DATABASE testdb GO USE testdb GO CREATE TABLE txl 第 27 页 共 40 页 (ID int, Name char(10), Age int) GO CREATE TABLE Person_counts (Person_count int) GO （2）create trigger tr_person_ins on txl for insert A

42、S -必须将字段 Person_count 的初始值输入 0 UPDATE Person_counts SET Person_count=Person_count+1 Select * from Person_counts GO 测试触发器：测试触发器：insert txl values(1,AAAAAA,22) （3）create trigger tr_person_del on txl for delete AS DECLARE n int SELECT n=COUNT(*) FROM deleted -统计 txl 表中删除的记录数 UPDATE Person_counts SET Pe

43、rson_count=Person_count-n Select * from Person_counts GO 测试触发器：测试触发器：delete from txl where Name=DDDDDD （4）右击表）右击表 txl，选择，选择所有任务所有任务下级子菜单中的下级子菜单中的管理触发器管理触发器，在弹出的对话框中可以，在弹出的对话框中可以 创建和修改触发器的定义。创建和修改触发器的定义。 第 28 页 共 40 页 实验十二实验十二 综合编程和应用综合编程和应用 【实验目的实验目的】 1、进一步巩固程序流程控制语句的使用进一步巩固程序流程控制语句的使用 2、熟练掌握存储过程的创建

44、和使用熟练掌握存储过程的创建和使用 3、巩固触发器的创建和使用巩固触发器的创建和使用 【实验内容实验内容】 1、根据如下规则对机试成绩进行反复加分，直到平均分超过 85 分为止，如下图所示。请 编写 T-SQL 语句实现。 90 分以上： 不加分 8089 分： 加 1 分 7079 分： 加 2 分 6069 分： 加 3 分 60 分以下： 加 5 分 2、在成绩表中，统计并显示机试成绩，鉴于试题偏难，假定要提分，确保每人机试都通过。 提分规则是，先给每个人都加 2 分，查看是否都通过，如果没有全通过，每人再加 2 分，再 看是否都通过，如此反复提分。如果加分后超过 100 分，就按 10

45、0 分计算。最后按美国的 ABCDE 五级打分制来显示成绩。 3、在选修成绩表中查询出选修了课程号为 kc01 且成绩比平均分高的学生名单。分别用以 第 29 页 共 40 页 下两种方法实现：(1)使用局部变量 (2)使用子查询 4、创建存储过程查询用户指定图书的书价，并将价格作为返回值。再写出调用该存储过程 的命令。 5、在学生选课数据库中，建立在选课表中输入或更改数据时，必须服从参照完整性约束的 INSERT 和 UPDATE 触发器。 【参考程序参考程序】 1、 SELECT * FROM stuMarks -原始成绩 DECLARE labAvg INT WHILE(1=1) BEG

46、IN UPDATE stuMarks SET labExam= CASE WHEN labExam=85 BREAK END SELECT * FROM stuMarks -加分后的成绩 2、 DECLARE n int UPDATE stuMarks SET writtenExam=writtenExam+2 -每人先加 2 分 WHILE(1=1) -条件永远成立 第 30 页 共 40 页 BEGIN SELECT n=COUNT(*) FROM stuMarks WHERE writtenExam0) BEGIN UPDATE stuMarks SET writtenExam=writ

47、tenExam+2 -每人加 2 分 UPDATE stuMarks SET writtenExam=100 WHERE writtenExam100 END ELSE BREAK -退出循环 END print 加分后的成绩如下： SELECT * FROM stuMarks print ABCDE 五级显示成绩如下： SELECT stuNo,成绩=CASE WHEN writtenExamavgcj AND 课程号=kc01 （2）使用子查询 第 31 页 共 40 页 USE 学生选课 GO SELECT s.* FROM 学生 s INNER JOIN 选修成绩 sc ON s.学号=sc.学号 WHERE 课程号=kc01 AND 成绩all (SELECT AVG(成绩) FROM 选修成绩 WHERE 课程号=kc01) 4、 USE 图书管理 Go CREATE PROCEDURE proc_BooksPrice sm char(42), dj float output As SELECT dj=定价 FROM books WHERE 书名=sm Go 调用存储过程： Declare price float EXEC proc_BooksPrice 计算机文化基础,price output S