1、精選優質文檔-傾情為你奉上精選優質文檔-傾情為你奉上專心-專注-專業專心-專注-專業精選優質文檔-傾情為你奉上專心-專注-專業SQL-Server實驗答案上海師范大學 計算機系 目 錄 TOC o 1-3 h z u 第一部分 企業管理器的使用第二部分 SQL語言試驗一 數據庫創建目的：1掌握利用SQL語言進行數據庫的創建、維護。 2 sp_helpdb 命令要求：1 創建數據庫 2 修改數據庫 3 刪除數據庫一 建立school 數據庫1 使用查詢分析器創建數據庫 school Create DataBase school2 使用 SP_helpdb 查詢數據庫 School 的信息3 使用

2、SQL-Server 的企業管理器查看數據庫 school 的信息。 4 記錄：1）school 數據庫文件所在的文件夾。 2）school 數據庫的文件名二 刪除School數據庫1 使用查詢分析器刪除數據庫 school DROP DATABASE school2 使用SQL-Server 的企業管理器刪除數據庫 school 。 三 create Database 深入研究1 建立school數據庫,要求數據庫存儲在c:data文件夾下，初始大小為5MB ,增量為 1MB。 CREATE DATABASE school ON( Name = school_dat, Filename =

3、c:sqldataschool.mdf,SIZE = 5, FILEGROWTH = 1 )2使用SQL-Server 的企業管理器，將數據庫的每次增量改為20%。試驗二 創建表目的：1 掌握利用SQL語言創建表的方法。 2 sp_help 命令要求：1 創建表 2 修改表結構 3 刪除表一 寫出使用 Create Table 語句創建表 student , sc,course 的SQL語句。學生表、課程表、選課表屬于數據庫 School ，其各自得數據結構如下：學生 Student (Sno,Sname,Ssex,Sage,Sdept)序號列名含義數據類型長度1Sno學號字符型(char)6

4、2Sname姓名字符型(varchar)83Ssex性別字符型(char)24Sage年齡整數 (smallint)5sdept系科字符型(varchar)15課程表 course(Cno,Cname,Cpno,Ccredit)序號列名含義數據類型長度1Cno課程號字符型(char)42cname課程名字符型(varchar)203Cpno先修課字符型(char)44Ccredit學分短整數 (tinyint)學生選課 SC(Sno,Cno,Grade)序號列名含義數據類型長度1Sno學號字符型(char)42Cno課程名字符型(char)63Grade成績小數(decimal)12,1二 把

5、創建表的sql 語句的腳本存儲到文件 school.sql 。create table Student ( Sno char(6) , Sname char(10) , Ssex char(2) , Sage smallint , Sdept char(10) ,) create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int,)create table SC( Sno char(6), Cno char(4) , Grade int )三 使用 SP_HELP 查看表 student 的表結構 利

6、用企業管理器查看表 sc 的表結構四 利用 sql 語句表結構修改1 在student 表中添加列：家庭地址 address 長度為 60 varchar 型入學日期 inDate 日期型 ALTER TABLE student ADD address varchar(60) ALTER TABLE student ADD inDate datetime完成后用sp_help 查看是否成功。2 將家庭地址 address 長度為 50 ALTER TABLE student ALTER COLUMN varchar(50) 完成后用sp_help 查看是否成功。3 刪除 student 表的

7、inDate 列 ALTER TABLE student DROP COLUMN inDate五 刪除表 1 刪除表 sc 2 刪除表 student 3 刪除表 course試驗三 創建數據完整性目的：1掌握創建數據完整性約束的命令。 2 掌握完整性約束的修改、刪除。要求：1 能建立完整性約束 2 修改完整性約束 3 刪除完整性約束一 寫出帶有完整性約束的 Create Table 命令建立表student、course、sc 。要求：1 Student表的主碼：sno student 的約束： 姓名不可為空，且唯一性別 不能為空且取值范圍為男，女年齡大于16歲sdept 默認為 JSJ 系

8、 2Course表的主碼：cno course 的約束： Ccredit 取值范圍 0 ,1,2,3,4,5 課程表的每一行的 Cno 與 cpno 不可相同3 Sc表的主碼：sno，cno 。主碼名為 PK_SCSc的外碼：外碼：SC 表的sno 參照表 student 的 sno外碼：sc 表的Cno 參照表 course 的 cno 4 把上述創建表的sql 語句的腳本存儲到文件 createSchool.sql 。create table Student ( Sno char(6) , Sname char(10) not null unique , Ssex char(2) chec

9、k (ssex=男 or ssex=女) , Sage smallint check(sage16) , Sdept char(10) not null default JSJ , primary key (sno) ) create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int check (Ccredit =0 and Ccredit=5), check( cnocpno) , -約束 primary key (cno) )create table SC( Sno char(6), Cno c

10、har(4) , Grade int check(grade=0 and grade163006學生不能輸入，性別不對。select * from student 查看你輸入了那些數據。2 course 表數據的輸入CnoCnameCpnoCcredit1085C+91086語文10863輸入上述數據，記錄出現的問題，說明原因。1086 不能輸入，因為有約束 check(cnocpno) select * from student 查看你輸入了那些數據。3 SC 表數據的輸入SnoCnoGrade30021081128輸入上述數據，記錄出現的問題，說明原因。3002 這條數據不能輸入，因為 g

11、rade不能大于 100分select * from student 查看你輸入了那些數據。三 參照完整性約束掌握表之間建立外碼后，對被參照表的如下操作會有何影響：修改主碼、插入新行、刪除新行？對參照表添加新行、刪除行、修改外碼值有何影響？掌握級聯修改、級聯刪除的概念。注意：表SC的 Sno是外碼，參照student的sno。表SC的 Cno是外碼，參照course的cno。1 輸入實驗前的數據學生表 StudentSnoSnameSsexSageSdept4001趙尹男20SX4002楊開女20JSJ課程表 course CnoCnameCpnoCcredit1088Java51089數學3

12、學生選課 SC SnoCnoGrade400110889040021088862 試驗過程 1) 在SC表中添加新行：SnoCnoGrade4001106676 記錄試驗結果.,寫出出現此結果的原因. 不能添加，因為在 cno是外碼，參照course的 cno , 但在course 中沒有 1066課程。2) 在student表中添加新行 SnoSnameSsexSageSdept4003趙輝男21SX 記錄試驗結果.,寫出出現此結果的原因.可以輸入3) 刪除student 表的 4001 ，4002學生 記錄試驗結果.,寫出出現此結果的原因. 兩個學生不能被刪除，因為sc的外碼 sno 參照

13、student的sno, sc中已經有4001，4002學生的數據，因此不能刪除。思考：刪除SC表的記錄有限制嗎？沒有 采取什么技術能使不能成功執行的命令變得可以執行，且使數據庫保持數據完整性。 級聯刪除4) 把 student 表的學號 4003 改為 4018 , 4001改為4021。 記錄試驗結果.,寫出出現此結果的原因.4003 可以改為 4018, 4001不能改為 4021 因為sc的外碼 sno 參照student的sno, sc中已經有4001的數據，但沒有 4003的選課數據。 思考：采取什么技術能使本題不能執行的命令可以執行，且使數據庫保持數據完整性。 級聯修改5) 把s

14、c表中的如下記錄的學號從4001改為4011。SnoCnoGrade4001108890 記錄試驗結果.,寫出出現此結果的原因.不能修改，因為sc的外碼 sno 參照student的sno, 4011在 student中不存在。如不成功，則可以采取什么方法來實現此要求。 需要在student表中添加 4011學生。如不成功，那么把4001修改為4003，能成功嗎？能成功！思考: 參照完整性規則中,外碼可以為空, 但SC表中的外碼可以為空嗎?為什么?舉一個外碼可以為空的例子。不可以，因為 sc表的主碼為 sno+cno, 即sno,cno為主屬性，所以不能為空。試驗五 索引目的：掌握索引的建立、

15、刪除的方法。一 創建索引1 建 student 的索引為姓名建立索引，索引名：Ix_student_sname為系科建立索引，索引名：Ix_student_sdeptcreate index ix_student_sname ON student(sname)create index ix_student_sdept ON student(sdept)2 SC 的索引為課程號建立索引： ix_sc_cno create index ix_sc_cno ON sc(cno)3 Course 的索引為課程名建立唯一性索引 ：Ix_course_cname create unique index i

16、x_course_cname ON course( cname)4 如何 SP_HELP 查看索引剛才建立的索引？ 如何在企業管理器中查看索引？ 二 刪除索引 course 表的索引 IX_course_cname DROP INDEX course.ix_course_cname三 思考：如何把索引 IX_student_sname 修改為唯一性索引？ 可以使用企業管理器 或先刪除索引，再重新建立。 *四 思考建立索引的目的1 輸入下列存儲過程，該程序生成大量數據供測試：create procedure usp_makedata asdeclare nCnt int , sNo varcha

17、r(6) , sname varchar(8)set nCnt =12000 -計數器 while nCntbegin set nCnt = nCnt + 1 set sNo = convert(varchar(6) ,nCnt) set sName = 張+sno insert into student (sno,sname,ssex,sage) values ( sno,sname,男,20)endreturn 2 exec usp_makedata -生成測試數據 3 輸入下述測試程序：create procedure usp_test as declare nCount int ,da

18、ta intset nCount=0while nCount100begin select data=count(*) from student where sname 張8800 set nCount =nCount + 1end 4 測試1）建立姓名的索引，查看運行時間(8秒). create index ix_student_sname on student(sname) -建立索引 exec usp_test2) 刪除姓名索引，查看運行時間（2分11秒），比較與1）的時間長短。drop index student.ix_student_sname -刪除索引 exec usp_test

19、試驗六 更新數據目的：掌握insert,update ,delete 語句的使用。一 insert 1 寫出把下述學生的信息添加到student表中的命令。 學號姓名性別年齡系科4001趙茵男20SX4002楊華女21Insert into student (sno,sname,ssex,sage,sdept) values (4001,趙茵,男,20,SX)Insert into student (sno,sname,ssex,sage) values (4002,楊華,女,21)2 批量插入數據1） 建立一個新表 sc_name ，有屬性 sno , sname , ssex , cno

20、, grade 。 CREATE TABLE sc_name (Sno char(6) , Sname varchar(20),Ssex char(2) ,cno char(4) ,grade int )2） 把 SX 系學生的sno,sname,ssex, cno , grade 插入到表 sc_name 中。 Insert into sc_name (sno,sname,ssex,cno , grade) select student.sno,sname , ssex,cno,grade from student,sc where student.sno=sc.sno and sdept=

21、SX 3） 察看 sc_name 表的數據 select * from sc_name二 Update1 修改 0001 學生的系科為: JSJUpdate student set sdept=JSJ where sno=00012 把陳小明的年齡加1歲，性別改為女。Update student set sage=sage+1 , ssex=女 where sname= 陳小明3 修改李文慶的1001課程的成績為 93 分update sc set grade=93 where cno=1001 and sno in ( select sno from student where sname=

22、 李文慶)4 把“數據庫原理”課的成績減去1分 update sc set grade=grade - 1 where cno in ( select cno from course where cname=數據庫原理 )三 Delete1 刪除所有 JSJ 系的男生 delete from student where sdept=JSJ2 刪除“數據庫原理”的課的選課紀錄 Delete from sc where cno in (select cno from course where cname=數據庫原理 ) 思考：修改數據的命令與修改表結構的命令有何區別？ 試驗七 Sql 查詢語句目的

23、: 掌握 Select 查詢語句。一 單表1查詢年齡在19至21歲之間的女生的學號,姓名,年齡,按年齡從大到小排列。select sno,sname,sage from student where sage between 19 and 21 and ssex=女 order by sage desc2查詢姓名中第戎2個字為“明”字的學生學號、性別。select sname ,ssex from student where sname like _明%3查詢 1001課程沒有成績的學生學號、課程號select sno,cno from sc where grade is null and cn

24、o=10014查詢JSJ 、SX、WL 系的學生學號,姓名，結果按系及學號排列select sno,sname from student where sdept in (JSJ,SX,WL)order by sdept,sno5按10分制查詢學生的sno,cno,10分制成績 （1-10分 為1 ，11-20分為2 ，30-39分為3，。90-100為10） select sno , cno , grade/10.0+1 as level from sc6查詢 student 表中的學生共分布在那幾個系中。（distinct） select distinct sdept from studen

25、t 7查詢0001號學生1001，1002課程的成績。 Select cno from sc where sno=0001 and (cno=1001 or cno=1002)二 統計1查詢姓名中有“明”字的學生人數。select count(*) from student where sname like %明%2計算JSJ系的平均年齡及最大年齡。 Select avg(sage) , max(sage) from student Where sdept=JSJ3計算每一門課的總分、平均分，最高分、最低分，按平均分由高到低排列 select cno,sum(grade),avg(grade)

26、,max(grade),min(grade) from sc group by cno order by avg(grade) desc4 計算 1001,1002 課程的平均分。Select cno , avg(grade) from sc where cno in (1001,1002) Group by cno5 查詢平均分大于80分的學生學號及平均分 select sc.sno , avg(grade) from scgroup by sc.snohaving avg(grade)806 統計選修課程超過 2 門的學生學號 select sno from sc group by sno

27、 having count(*)27 統計有10位成績大于85分以上的課程號。Select cno from sc where grade85 group by cno having count(*) =108 統計平均分不及格的學生學號select sno from sc group by sno having avg(grade)609 統計有大于兩門課不及格的學生學號select sno from sc where grade2三 連接 1查詢 JSJ 系的學生選修的課程號 select cno from student,sc where student.sno=sc.sno and s

28、dept=JSJ2查詢選修1002 課程的學生的學生姓名 (不用嵌套及嵌套2種方法）a: select sname from student,sc where student.sno = sc.sno and cno=1002b: select sname from student where sno in (select sno from sc where cno=1002)3查詢數據庫原理不及格的學生學號及成績select sno,grade from sc ,course where o=o and cname=數據庫原理4查詢選修“數據庫原理”課且成績 80 以上的學生姓名(不用嵌套及

29、嵌套2種方法）a: select sname from student , sc , course where student.sno=sc.sno and o = o and grade80 and cname=數據庫原理 b: select sname from student where sno in ( select sno from sc where grade80 and cno in ( select cno from course where cname=數據庫原理) )5查詢平均分不及格的學生的學號，姓名,平均分。select sno, max(sname) , avg(gr

30、ade) as avggrade from sc , student where student.sno=sc.sno group by student.snohaving avg(grade) 75)B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=女 Group by student.sno having avg(grade)757查詢男學生學號、姓名、課程號、成績。(一門課程也沒有選修的男學生也要列出，不能遺漏)select student.sno,sname,cno,grade from st

31、udent left join sc ON student.sno=sc.sno and ssex=男四 嵌套、相關及其他1 查詢平均分不及格的學生人數select count(*) from student where sno in ( select sno from sc group by sno having avg(grade)=all ( select avg(grade) from sc group by sno )*4 查詢沒有選修1001，1002課程的學生姓名。 Select sname from student where not exists ( Select * fro

32、m course where cno in (1001,1002) and Not exists ( select * from sc where sno=student.sno and cno=o ) )5 查詢1002課程第一名的學生學號（2種方法）a: select top 1 sno from sc cno=1002 order by grade descb: select sno from sc where cno=1002 and grade =all (select grade from sc where cno=1002)6 查詢平均分前三名的學生學號select top 3

33、sno from sc group by sno order by avg(grade) desc7 查詢 JSJ 系的學生與年齡不大于19歲的學生的差集a: select * from student where sdept=JSJ and sage19b: select * from student where sdept=JSJ except select * from student where sage90 union select sno,sname from student where sno in ( select sno from sc group by sno having

34、 avg(grade)85 )9 查詢每門課程成績都高于該門課程平均分的學生學號select sno from student where sno not in (select sno from sc X where grade( select avg(grade) from sc Y where Y.sno=X.sno) select sno from student where sno not in (select sno from sc X where grade (select avg(sage) from student y where sdept=x.sdept)試驗八 視圖目的:

35、 掌握視圖的建立、使用。 1建立學生學號、姓名、性別、課程號、成績的視圖 v_sc 查看V_sc中的數據。Create view v_sc (sno , sname,ssex , cno, grade ) as Select student.sno , sname,ssex , cno , grade from student , sc Where student.sno=sc.sno Select * from v_sc 1 建立學生學號、姓名、出生年月的視圖 v_age 查看V_age中的數據。 Create view v_age (sno,sname, sbirth) as Select

36、 sno , sname , 2008 sage from student Select * from v_age2 建立 JSJ 系的學生學號、姓名、性別、年齡的視圖 V_JSJCreate view v_jsj (sno,sname,ssex, sage) asSelect sno,sname,ssex,sage from student where sdept=JSJ3 建立每門課程的平均分的視圖 V_avggradeCreate view v_avgGrade(cno, grade1 ) as Select cno , avg(grade) from sc group by cno 4

37、 將 視圖 v_jsj 中 李文慶 的年齡改為21歲Update v_jsj set sage=sage+1 where sname=李文慶5 察看 student 中李文慶的年齡查看 v_age 中李文慶的出生年月Select * from student where sname= 李文慶Select * from v_age where sname=李文慶6 查詢每門課程的及格率 Create view v1 (cno , cnt1) as Select cno, count(*) from sc group by cno Create view v2 (cno , cnt1) as Se

38、lect cno, count(*) from sc where grade=60 group by cnoSelect o , cnt2*1.0 / cnt1 from v1,v2 where o=o 思考: 1 利用 V_JSJ 視圖,可以更新SX 的學生的年齡嗎? 寫出理由如: update v_jsj set sage=25 where sno= 0004 0004 號學生為 SX 系.試驗九 安全性控制實驗目的:掌握Sql-server 的授權機制.1）建立新用戶 mary , 密碼1234 Sp_addLogin mary, 12342) 授予 mary 可以訪問 School 數

39、據庫的權力選擇 school 數據庫Sp_grantDBaccess mary 3) 以mary 登錄 sql-server ,執行 select * from student ,記錄執行結果，說明原因。 無法查到數據，因為mary 沒有查詢 student 的權限。4）將 course 的查詢、更改權限授予 mary Grant select , update on course to mary5）把查詢 student 表和修改學生學號的權限授予用戶 mary,且他能將此權限轉授他人。Grant select , update(sno) on student to mary with gr

40、ant option6) 把對 course 表的更改權限從mary 收回Revoke update on course from mary7) 把第5）小題授予mary的權限收回。revoke select , update(sno) on student from mary cascade8）mary 只能查詢 1001 號課程的學生成績,請問如何授權 Create view v_sc1 (sno,cno,grade) as Select sno, cno,grade from sc where cno=0001 Grant select on v_sc1 to mary思考: 1 sp_

41、addlogin , sp_grantdbaccess 語句的區別.2 如有200個人需要授權，SQL-SERVER如何簡化授權機制。試驗十 存儲過程目的: 掌握存儲過程的概念、編程及使用1 編寫一個存儲過程 usp_avgage , 向客戶端返回每個系科的學生平均年齡。 系科 平均年齡 JSJ 21 SX 20 。 1) 編寫存儲過程的代碼 Create procedure usp_avgage asSelect sdept,avg(sage) from student group by sdept 2）調試、運行該存儲過程。 Usp_avgage2編寫一個存儲過程 usp_sdept, 傳

42、入一個系科代碼，返回該系的平均年齡，人數Create procedure usp_sdept dept char(10) asSelect avg(sage),count(*) from student where sdept=dept3 編寫存儲過程 usp_updateGrade , 傳入參數為課程號,處理邏輯: 對傳入的這門課,進行如下處理:如某學生該門課成績80 , 則加 2 分如某學生該門課成績60 , 則加 1 分如某學生該門課成績80 Update sc set grade=grade + 1 where cno=cno and grade between 60 and 80 U

43、pdate sc set grade=grade -1 where cno=cno and grade age2 print name1 + 學生的年齡大 else print name2 + 學生的年齡大 return7 編寫存儲過程 usp_comp_age1 , 比較兩個學生的年齡的高低，兩個學生的學號有參數輸入，最后輸出： XXXX學生的年齡大。 注意： XXXX為學生的姓名Create procedure usp_comp_age1 no1 char(6),no2 char(6) as declare age1 int , age2 int declare name1 char(10

44、) , name2 char(10) -臨時存儲兩個人的姓名 select age1=sage ,name1 = sname from student where sno=no1 select age2=sage, name2 = sname from student where sno=no2 if age1 age2 print name1 + 學生的年齡大 else print name2 + 學生的年齡大 return10 編寫存儲過程 usp_comp_age2 , 比較兩個學生的年齡的高低，兩個學生的學號有參數輸入，最后把年齡大的學生的姓名、性別返回客戶端。 Create proc

45、edure usp_comp_age1 no2 char(6),no2 char(6) as declare age1 int , age2 int declare name1 char(10) , name2 char(10) -臨時存儲兩個人的姓名 select age1=sage ,name1 = sname from student where sno=no1 select age2=sage, name2 = sname from student where sno=no2 if age1 age2 select sname ,ssex from student where sno=

46、no1else select sname ,ssex from student where sno=no2return12 編寫存儲過程 usp_t1，傳入參數為學號,把該學號的課程1001的成績減到58分。每次只能減1分，用循環完成。 create procedure usp_t1 no char(6) asdeclare age intset age=100while age58BEGIN SELECT age = sage from student where sno=no If age58 Update sage=sage -1 where sno=no END RETURN- 以下不

47、需要4 編寫存儲過程 usp_disp , 傳入參數為課程號,處理邏輯: 返回每個學生的成績等級。 成績=90 為優, 成績=80為良,成績=70 為中,成績=60為及格 ,成績=90 set sLevel = 優 else if nGrade=80 set sLevel = 良 else if nGrade=70 set sLevel = 中 else if nGrade=80 set sLevel = 及格 else set sLevel = 不及格 -把結果寫入臨時表 insert into #tmp(sno,cno,grade,level) values (sno,cno,nGrade

48、,sLevel) fetch next from cur1 into sno , nGrade -讀出游標下一行數據 end close cur1 dealLocate cur1 select * from #tmp -返回結果給客戶端 drop table #tmp -刪除臨時表 return 5 編寫一個存儲過程，傳入參數為學號，執行后，把該學號的學生按如下格式輸出成績： （注意：只有一行）學號 姓名 1001課程 1002課程 1003 課程 平均分 6 編寫一個存儲過程，傳入參數為 系科，執行后，把該系科的學生按如下格式輸出學生成績：學號 姓名 1001 課程 1002課程 1003

49、課程 平均分 create procedure usp_grade dept char(15) as create table #tmp ( sno char(4) , sname char(10) , g1 int null, g2 int null , g3 int null , pj int null ) declare no char(4) , name char(10), nG1 int ,nG2 int ,nG3 int declare cur1 cursor for select sno , sname from student where sdept = dept -游標 某一

50、個系的學生open cur1fetch next from cur1 into no , namewhile fetch_status=0begin select nG1=grade from sc where sno=no and cno=1001 select nG2=grade from sc where sno=no and cno=1002 select nG3=grade from sc where sno=no and cno=1003 insert into #tmp(sno,sname,g1,g2,g3,pj) values (no,name,nG1,nG2,nG3,(nG1

51、+nG2+nG3)/3 ) fetch next from cur1 into no , nameendclose cur1dealLocate cur1select * from #tmpdrop table #tmp-執行usp_grade JSJ7 編寫存儲過程，統計男女生1001，1002，1003各自的選修人數，輸出格式如下： 性別 1001人數 1002人數 1003人數 小計 男 3 5 2 10 女 2 4 1 7 合計 5 9 3 17 （數據為示意數據）create procedure usp_tj as create table #tmp (ssex char(2), r

52、s1 int,rs2 int ,rs3 int ,xj int ) declare nRs1 int , nRs2 int, nRs3 intselect nRs1 = count(*) from student,sc where cno=1001and ssex=男select nRs2 = count(*) from student,sc where cno=1002and ssex=男select nRs3 = count(*) from student,sc where cno=1003and ssex=男 insert into #tmp(ssex,rs1,rs2,rs3,xj) v

53、alues (男,nRs1,nRs2,nRs3, nRs1+nRs2+nRs3)select nRs1 = count(*) from student,sc where cno=1001and ssex=女select nRs2 = count(*) from student,sc where cno=1002and ssex=女select nRs3 = count(*) from student,sc where cno=1003and ssex=女 insert into #tmp(ssex,rs1,rs2,rs3,xj) values (女,nRs1,nRs2,nRs3, nRs1+n

54、Rs2+nRs3) select * from #tmp drop table #tmpreturn8 編寫一個存儲過程，利用存儲過程的參數返回數據庫服務器上的日期時間。 思考：何時需要存儲過程？試驗十二 觸發器目的: 了解觸發器的機制及編程設計、使用一 建立學生表的觸發器 usp_addstudent，當增加學生時，SX系的學生不能超過30歲。1 寫出觸發器2 執行下列語句塊： begin traninsert into student (sno,sname,ssex,sage,sdept) values (0701,劉歡,男,26,SX)if error=0 commitelse roll

55、back end 觀察該學生是否加入到 student 3執行下列語句塊：begin traninsert into student (sno,sname,ssex,sage,sdept) values (0702,趙歡,男,31,SX)if error=0 commitelse rollback end 觀察該學生是否加入到 student 二 實現下列觸發器1 不能刪除年齡大于25歲的學生記錄。create trigger utr_student1 on student for delete asdeclare nCnt int -存儲被刪除的大于25歲的人數select nCnt = c

56、ount(*) from deleted where sage25if nCnt0 begin raiserror(不能刪除大于25歲的學生,16,10) rollback transactionend -測試 insert into student values (8701,aa1,男,27,JSJ) -不能被刪除insert into student values (8702,bb1,男,24,JSJ) -能刪除select * from student where sno in (8701,8702)delete from student where sno=8701select * f

57、rom student where sno in (8701,8702)delete from student where sno=87022 建立觸發器 usp_delcourse , 使課程表中1001，1002，1003 三門課不會被刪除。 注意如何調試。create trigger utr_deleteCourse on course for delete as declare nCnt int select nCnt = count(*) from deleted where cno in (1001,1002,1003)if nCnt0 begin raiserror(不能刪除,1

58、6,10) rollback transactionend return調試： Delete from course where cno=1001 -不會被刪除 Delete from course where cno=1006 -能被刪除3 對學生表建立一觸發器，使更改后的年齡只能比原值大create trigger utr_student_update1 on student for update asif not update(sage) returndeclare nCnt int select nCnt = count(*) from inserted ,deleted where

59、deleted.sno=inserted.sno and inserted.sage0 begin raiserror(更改后的年齡比原值小了,16,10) rollback transactionend 4對sc表建立觸發器，使JSJ系的學生不可選擇 1004號課程create trigger utr_choose on sc for insert asdeclare nCnt int -存儲被刪除的大于25歲的人數select nCnt = count(*) from inserted ,student where student.sno=inserted.sno and sdept=JS

60、J and o=1004 -inserted 存儲insert 命令添加的數據 如 0001,1004,90if nCnt0 begin raiserror(JSJ不可選擇 1004,16,10) rollback transactionend -測試 insert into student values (8701,aa1,男,27,JSJ) insert into sc(sno,cno,grade) values (8701,1001,90) -可以insert into sc(sno,cno,grade) values (8701,1004,90) -不可以select * from s