《數(shù)據(jù)庫系統(tǒng)》上機(jī)題,實(shí)驗(yàn)四：T

《《數(shù)據(jù)庫系統(tǒng)》上機(jī)題,實(shí)驗(yàn)四：T》由會員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫系統(tǒng)》上機(jī)題,實(shí)驗(yàn)四：T（20頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、實(shí)驗(yàn)四：T-SQL查詢、存儲過程、觸發(fā)器、完整性 姓名：張浩 學(xué)號：20082617 專業(yè)：信息管理與信息系統(tǒng) 班級：1 課程名稱：數(shù)據(jù)庫系統(tǒng)概論 課程代碼： 實(shí)驗(yàn)時(shí)間：2010年12月24日，2010年12月18日 實(shí)驗(yàn)地點(diǎn)：12號樓406室 1. 實(shí)驗(yàn)特點(diǎn)： 類型：驗(yàn)證 類別：專業(yè)基礎(chǔ) 計(jì)劃學(xué)時(shí)：4學(xué)時(shí) 每組人數(shù)：1人 2. 實(shí)驗(yàn)?zāi)康呐c要求： 實(shí)驗(yàn)?zāi)康模? 1） 熟練掌握局部變量、游標(biāo)的定義和使用 2） 熟練掌握存儲過程、觸發(fā)器的創(chuàng)建和使用 3 ）掌握數(shù)據(jù)庫完整性約束的定義和使用 基本要求： 1）掌握上述過程定義的一般格式 3. 主要儀器設(shè)備：計(jì)算機(jī)+SQL S

2、erver 2000 第一部分：T-SQL程序設(shè)計(jì) (1 ).如果3號課程的平均成績在 80分以上，則輸岀“ 3號課程成績良好”，否則輸岀“ 3號成績 一般” if (select avg(grade) from sc where eno='3')>80 begin print '3 號成績良好’ end else begin print '3 號成績一般’ if Select -wg (grade) from sc where cna-5 ： 80 begm print V號成績良奸° end JP妙煩氓ST I. 翌網(wǎng)格aS消息 end 汁- :yr .

3、 「“I,” 蘭亠： 1 l . ,提示信息 (2 )計(jì)算并輸岀 95003 號學(xué)生的平均成績，若無該生信息，則顯示“該生未選課” declare @message varchar(200) if exists(select sno from sc where sc.s no='95003') beg in select avg(grade) from sc where sc.s no='95003' end pri nt @message end declare Message varchar f200) a if ezists select sno frtym ^here

4、sc. begin sele匚七 av? \ grade) froiiL sc where else begin s&t Message '該生未選諜' print ?message end v < " i >r < 亠 Ilin □網(wǎng)格HP消息 GH4張浩20092&171 0 00:00 □行 行陽列訊 (3 ).如果有成績在 90分以上的學(xué)生，則顯示他的學(xué)號，課程和成績，否則顯示“沒有學(xué)生的課程成績 在 90 分以上”提示信息 declare @msg varchar(200) if exists(select grade from sc where sc.grade>=9

5、0) select sno,cno ,grade from sc where sc.grade>=90 else set @msg=' 沒有學(xué)生的成績在 90以上 pri nt @msg declare varchar(200' a if ex 1^1: z i select grade from sc where select sno；, cnoj grade from sc where ￡ else set @snsg- '沒有學(xué)生的成績在90以上’ v 1 ■■ ! > sno crto grade 1 95001 1 92 95D02

6、2 90 n冏格so消息 張浩SOOB26171 0:00:00 2 行 行「列 (4).利用游標(biāo)逐行顯示 student 表中的記錄。declare @cursorvar cursor set @cursorvar=cursor scroll dyn amic for select* from stude nt ope n @cursorvar fetch n ext from @cursorvar while @@fetch_status=O beg in fetch next from @cursorvar end close @cursorvar dealloc

7、ate @cursorvar (5).用自定義函數(shù)計(jì)算全體男生的平均年齡 CREATE FUNCTION average(@c num char(2)) RETURNS int as beg in declare @aver int select @aver=(select avg(sage) from stude nt where ssex=@c num group by ssex) retur n @aver end go declare @aver1 in t,@c num1 char(2) set @c num 仁' 男' select @av

8、er1=dbo.average (@c num1) select @aver1 as' 全體男生的平均年齡 CREATE FUNCTION averagfl (Acnun char C2)) )a.| begin declare (Saver int select Oaver- i seleut avg (sage ' ret urn ?aver end v 全體男生的平均年齡 1 ~ GIU〔張；§200&26171 0:00:00 i 行 各 11 ,列 I (6) .顯示course 表中課程名的前 2個(gè)字符。 select left(c name, 2) fro

9、m course order by eno  (無列名〕 n網(wǎng)格ami 張浩2 0:00:00 (7) .在一列中顯示 student 中各元組的學(xué)號中的年級，列名顯示為“年級” ；另一列中顯示學(xué)號中 的學(xué)生序列號，列名顯示為“序號” 。SELECT SUBSTRINGS no, 1,2)'年級',SUBSTRINGS no, 3, LEN(s no)-1)'序號 FROM stude nt ORDER BY sno SELECT SUBSTRIMG (sno, 1, 2)"年ST, SUE STR： a FROM student ORDER BY sno

10、 <.1^ i>. 年級序號］ 1 95 001 2 95 002 3 95 003 4 95 004 "□岡格圜消息； 曲I加舒i張浩200826171 0:00:00 2行 行 S 列1 (8) .在選課表中顯示學(xué)號、 課程號，并根據(jù)成績：0-59顯示“不合格”；60-79顯示“合格” ;80-89 顯示"良好” ；90-100 顯示"優(yōu)秀?！?select sno,cno,grade= case whe n grade>=0 and grade<60 then '不合格' whe n grade>=60 an

11、d grade<80 the n ' 合格' whe n grade>=80 and grade<90 then '良好' whe n grade>=90 and grade<=100 then '優(yōu)秀' end from sc select sno, cna< grades a case when grade 0 -irid grade '50 then '下 合格’ when grade =60 遼呂 grade ?0 then J 含掐’ when grade -S0 ind ^rade 90 then 艮忙 vhen grade>=90 md grad

12、e =100 thg "優(yōu)香’ end from sc v sno eno grade 1 2 3 4 5 95001 1 0 2 950D1 3 1 2 95002 3 tisaff n網(wǎng)榕團(tuán)消息| c?H4O6^ SH4OB2T\Admtnistrator (54)張浩200026171 0:00:00 ：5 存 行釦列 1 第二部分：存儲過程 (1 )創(chuàng)建一個(gè)為 worker表添加職工記錄的存儲過程 Addworker CREATE proc Addworker @ 職工號 char(4), @名姓 char(8), @

13、性別 char(2), @岀生日期 datetime, @黨員否char(2), @參加工作 datetime, @部門號 char (4) as in sert into worker values（@ 職工號,@名姓,@性別,@岀生日期，@黨員否,@參加工作,@部門號）; (2)創(chuàng)建一個(gè)存儲過程 Delworker 刪除worker 表中指定職工號的記錄 create proc Delworker @zhigonghao char(4) as delete from worker where 職工號 =@zhig on ghao; go 

14、 (3)顯示存儲過程 Delworker 的定義信息 (4)刪除存儲過程 Addworker 和Delworker 。不要截圖 drop procedure Addworker drop procedure Delworker (5 )創(chuàng)建并執(zhí)行以下存儲過程： a.從數(shù)據(jù)庫表中查詢，返回學(xué)生學(xué)號、姓名、課程名、成績 create proc s_back as select stude nt.s no,sn ame,c name,grade from stude nt,sc,course where stude nt.s no=sc.s no and sc.c no=c

15、ourse.c no go exec s back creat e prac gb ack A as select student, sno, snane, cnane. grade from student 、scj course where student.sno=sc. sno and o=course. eno exec s_back V | < 1 > sno sname cnafte grade A | 1 95001 李勇

16、 數(shù)據(jù)庫 02 2 95001 E5 3 95001 李勇 信息系統(tǒng) E8 4 95002 劉辰 90 5 95002 信息系統(tǒng) 80 V | 13網(wǎng)格圍消息］ jH406￡7Udni imstrator 強(qiáng)浩 200326171 0:00:00 5行行4列i b.從數(shù)據(jù)庫表中查詢指定學(xué)號的學(xué)生學(xué)號，姓名、班級，該存儲過程接受與傳遞參數(shù)，精確匹配的 值 create proc r_back @a char(10) as select sno,sn ame from stude nt where sno=@

17、a go exec r back'95003' create proc r_back a @a char（10） as select sno, sname from student whe Sxec r back' ^5003' 尸 _ v -J11L 二 Lil > i eq snaiiiE 1 *95003 王敏 *昌岡格_團(tuán)消息: （張＞9200826171 0:00:00 1 行行 5,列曲 第三部分：觸發(fā)器 ⑴在表depart上創(chuàng)建一個(gè)觸發(fā)器 depart_update , 當(dāng)更改部門號時(shí)同步更改 worker表中對應(yīng) 的部門號 create tri

18、gger depart_update on depart for update as update worker set 咅部門號=（select 咅部門號 from inserted） where worker. 咅咅門號=（select 咅咅門號 from deleted） ⑵在表worker上創(chuàng)建一個(gè)觸發(fā)器 worker_delete,當(dāng)刪除職工記錄時(shí)同步刪除 salary表中對應(yīng)的職 工記錄 create trigger worker_delete on worker for delete as delete from salary where 職工號=(select

19、 職工號 from deleted) ⑶刪除觸發(fā)器depart_update drop trigger depart_update ⑷刪除觸發(fā)器worker_delete drop trigger worker_delete (5)在數(shù)據(jù)庫中創(chuàng)建一個(gè)觸發(fā)器， 向選課表添加一條紀(jì)錄時(shí)，檢查該紀(jì)錄的學(xué)號在學(xué)生表中是否存在, 檢查該紀(jì)錄的課程號在課程表中是否存在，若其中有一項(xiàng)為否，則拒絕添加操作，并顯示“違反數(shù) 據(jù)一致性”提示信息。 例如：向選課表中添加一條記錄 ('95007','2',99)，說明95009不在student表中 create trigger sc_in sert

20、 on sc for in sert as declare @xuehao char(10),@keche nghao char(10),@che ngji smalli nt,@a,@b select @xuehao=s no, @keche nghao=c no from in serted set @a= select sno from stude nt where sno=@xuehao set @b= select eno from course where cno=@keche nghao if exists(@a and @b) beig n insert i

21、nto sc values(@xuehao,@keche nghao,@che ngji) end rollback transaction print' 違反數(shù)據(jù)一致性' in sert into sc values('95007','2',99) 第四部分：數(shù)據(jù)庫完整性 1、實(shí)施worker表的“性別”字段默認(rèn)值為“男”的約束 alter table worker add constraint con1 default ' 男'for 性另 V go 例如： insert worker( 職工號)values(111) select * from worker 2實(shí)

22、施salary表的“工資”字段值在 0~9999的約束 create table salary (職工號char(4), 姓名 char(8), 日期 datetime, 工資 decimal not null check( 工資 >=0 and 工資 <=9999), primary key(職工號，日期)) 3實(shí)施depart表的“部門號”字段值唯一的非聚集索引的約束 create table depart (部門號 char(4) unique, 部門名char(10)) Go 4、為worker表建立外鍵“部門號”，參考表depart的“部門號”列。 Crea

23、te table worker (職工號 char(4) primary key, 姓名 char(8), 性別 char(2), 岀生日期 datetime, 黨員否char(2), 參加工作 datetime, 部門號char(4), create rule worker_rule as @性別like '男'or @性別 go exec sp_b in drule 'worker_rule', 'worker. Go 6、 刪除1小題所建立的約束 Alter table worker drop con stra int con1 7、 刪除2小題所建立

24、的約束。 Alter table salary drop 工資 check 8、 刪除3小題所建立的約束 Alter table depart drop uni que 9、 刪除4小題所建立的約束 Alter table worker drop foreig n key 10、 解除5小題所建立的綁定并刪除規(guī)則 if exists (select n ame from sysobjects where n ame = 'worker_rule' and type = 'R') like '女' 性別 sex begin

25、 drop rule worker_rule 倚窗遠(yuǎn)眺，目光目光盡處必有一座山，那影影綽綽的 黛綠色的影，是春天的顏色。周遭流嵐升騰，沒露出那真實(shí)的面孔。面對那流轉(zhuǎn)的 薄霧，我會幻想，那里有一個(gè)世外桃源。在天階夜色涼如水的夏夜，我會靜靜地， 靜靜地，等待一場流星雨的來臨… 許下一個(gè)愿望，不乞求去實(shí)現(xiàn)，至少，曾經(jīng)，有那么一刻，我那還未枯萎的， 青春的，詩意的心，在我最美的年華里，同星空做了一次靈魂的交流… 秋日里，陽光并不刺眼，天空是一碧如洗的藍(lán)，點(diǎn)綴著飄逸的流云。偶爾，一 片飛舞的落葉，會飄到我的窗前。斑駁的印跡里，攜刻著深秋的顏色。在一個(gè)落雪 的晨，這紛紛揚(yáng)揚(yáng)的雪，飄落著一如千年前的潔白。窗外，是未被污染的銀白色世 界。我會去迎接，這人間的圣潔。在這流轉(zhuǎn)的歲月里，有著流轉(zhuǎn)的四季，還有一顆 流轉(zhuǎn)的心，亙古不變的心。