西南交通大學(xué)數(shù)據(jù)庫原理實(shí)驗(yàn)課程設(shè)計(jì)

1、2014-2015學(xué)年第一學(xué)期數(shù)據(jù)庫原理課程實(shí)驗(yàn)報(bào)告學(xué) 號(hào)： 20122617 學(xué)生姓名： 徐玉松 班 級(jí)： 軟件工程2012 教 師： 陶宏才 輔導(dǎo)老師： 王澤洲 趙紅芳 2014年12月實(shí)驗(yàn)一：表及約束的創(chuàng)建1.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)容目的：創(chuàng)建數(shù)據(jù)表、添加和刪除列、實(shí)現(xiàn)所創(chuàng)建表的完整性約束。內(nèi)容：11-2、11-2633。 注：實(shí)驗(yàn)內(nèi)容編號(hào)均取自數(shù)據(jù)庫原理及設(shè)計(jì)（第2版）第11章的實(shí)驗(yàn)！即：實(shí)驗(yàn)內(nèi)容以第2版教材為準(zhǔn)！報(bào)告：以11-31作為實(shí)驗(yàn)一的報(bào)告。1.2 實(shí)驗(yàn)代碼及結(jié)果1.2.1 實(shí)驗(yàn)代碼create table person20122617-創(chuàng)建新表(P_no char(6) prima

2、ry key,P_name varchar(10) not null,Sex char(2) not null,Birthdate datetime null,Date_hired datetime not null,Deptname varchar(10) not null DEFAULT'培訓(xùn)部',P_boss char(6) null,constraint birth_hire_check -為約束創(chuàng)建一個(gè)名稱check (Birthdate<Date_hired)create table customer20122617(Cust_no char(6) prima

3、ry key,Cust_name varchar(10) not null,Sex char(2) not null,BirthDate datetime null,City varchar(10) null,Discount Dec(4,2) not null,constraint Discount_check-檢查約束 的名稱check (Discount>=0.5 and Discount<=1 )-檢查約束)-create rule d as state between 0 and 1-創(chuàng)建規(guī)則-sp_bindrule d,'customer20122617.Dis

4、count' -這種方法也可以給Discount約束 綁定規(guī)則create table orderdetail20122617(Order_no char(6) primary key,constraint Order_no_constraintCHeck(Order_no LIKE'A-ZA-Z0-90-90-90-9'),Cust_no char(6) not null,P_no char(6) not null,Order_total int not null,Order_date datetime not null,constraint person201226

5、17_contrFOREIGN KEY(P_no)-定義外鍵為P_noREFERENCES person20122617(P_no)-外鍵參照主表person20122617中的P_noon delete NO Action-參照定義為不許刪除on update cascade,-定義為可隨著主表跟新constraint cusrtomer20122617_contrforeign key (Cust_no)REFERENCES customer20122617(Cust_no)-參考o(jì)n delete NO Action on update cascade)create table sala

6、ry20122617(P_no Char(6) primary key, Base Dec(8,2) not null,Bonus Dec(8,2) not null,Fact AS Base+Bonus ,constraint person2_contrFOREIGN KEY(P_no) REFERENCES person20122617(P_no)on delete NO Actionon update cascade) -建表完成1.2.2 實(shí)驗(yàn)結(jié)果注：僅附有實(shí)際意義的結(jié)果。運(yùn)行代碼得到結(jié)果后拷屏，用Windows畫圖工具切下有意義的部分，然后粘貼到此處。Person20122617表的

7、創(chuàng)建Customer20122617表的創(chuàng)建Orderdetail20122617表的創(chuàng)建Salary20122617表的創(chuàng)建實(shí)驗(yàn)二：SQL更新語句2.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)容目的：update、delete、insert 語句的練習(xí)。內(nèi)容：11-68。報(bào)告：以11-7、11-8作為實(shí)驗(yàn)二的報(bào)告。2.2 實(shí)驗(yàn)代碼及結(jié)果2.2.1 實(shí)驗(yàn)代碼2.2.1.1實(shí)驗(yàn)數(shù)據(jù)準(zhǔn)備：insert into person20122617 -插入person表的數(shù)據(jù)values('000001','林峰','男','1975-04-07','2003

8、-08-03','銷售部','000007' )insert into person20122617values('000002','謝志文','男','1975-02-14','2003-12-07','培訓(xùn)部','000005' )insert into person20122617values('000003','李浩然','男','1970-08-25','2000

9、-05-16','銷售部','000007' )insert into person20122617values('000004','廖小玲','女','1979-08-06','2004-05-06','培訓(xùn)部','000005' )insert into person20122617values('000005','梁玉瓊','女','1970-08-25','2001

10、-03-13','培訓(xùn)部','NULL' )insert into person20122617values('000006','羅向東','男','1979-05-11','2000-07-09','銷售部','000007' )insert into person20122617values('000007','肖佳慶','男','1963-07-14','1988-0

11、6-06','銷售部','NULL' )insert into person20122617values('000008','李浩然','男','1975-01-30','2002-04-12','培訓(xùn)部','000005' )insert into person20122617values('000009','趙文龍','男','1969-01-20','1996-08-

12、12','培訓(xùn)部','000007' )INSERT INTO customer20122617 -為customer表插入數(shù)據(jù) VALUES ('000001','王云','男' ,'1972-01-30','成都','1.00') INSERT INTO customer20122617 VALUES ('000002','林國平','男' ,'1985-08-14','成都'

13、,'0.85') INSERT INTO customer20122617 VALUES ('000003','鄭洋','女' ,'1973-04-07','成都','1.00') INSERT INTO customer20122617 VALUES ('000004','張雨潔','女' ,'1983-09-06','北京','1.00') INSERT INTO customer20

14、122617 VALUES ('000005','劉菁','女' ,'1971-08-20','北京','0.95') INSERT INTO customer20122617 VALUES ('000006','李宇中','男' ,'1979-08-06','上海','1.00') INSERT INTO customer20122617 VALUES ('000007','顧培

15、銘','男' ,'1973-07-23','上海','1.00') INSERT INTO orderdetail20122617-為orderdetail表插入數(shù)據(jù) VALUES ('AS0058','000006','000002','150000','2006-04-05') INSERT INTO orderdetail20122617 VALUES ('AS0043','000005','000

16、005','90000','2006-03-25') INSERT INTO orderdetail20122617 VALUES ('AS0030','000003','000001','70000','2006-02-14') INSERT INTO orderdetail20122617 VALUES ('AS0012','000002','000005','85000','2005-11-11&#

17、39;) INSERT INTO orderdetail20122617 VALUES ('AS0011','000007','000009','130000','2005-08-13') INSERT INTO orderdetail20122617VALUES ('AS0008','000001','000007','43000','2006-06-06') INSERT INTO orderdetail20122617 VALUE

18、S ('AS0005','000001','000007','72000','2006-05-12') INSERT INTO orderdetail20122617 VALUES ('BU0067','000007','000003','110000','2006-03-08') INSERT INTO orderdetail20122617 VALUES ('BU0043','000004','

19、;000008','70000','2006-12-25') INSERT INTO orderdetail20122617 VALUES ('BU0039','000002','000005','90000','2006-10-12') INSERT INTO orderdetail20122617 VALUES ('BU0032','000006','000002','32000','2006-08-

20、08') INSERT INTO orderdetail20122617 VALUES ('BU0021','000004','000006','66000','2006-04-01') INSERT INTO orderdetail20122617 VALUES ('CX0044','000007','000009','80000','2006-12-12') INSERT INTO orderdetail20122617 V

21、ALUES ('CX0032','000003','000001','35000','2006-09-18') INSERT INTO orderdetail20122617 VALUES ('CX0025','000002','000003','90000','2006-05-02') INSERT INTO orderdetail20122617 VALUES ('CX0022','000001',&

22、#39;000007','66000','2006-12-04')insert into salary20122617-為salary表插入數(shù)據(jù)values('000001','2100','300' )insert into salary20122617values('000002','1800','300' )insert into salary20122617values('000003','2800','280&

23、#39; )insert into salary20122617values('000004','2500','250' )insert into salary20122617values('000005','2300','275' )insert into salary20122617values('000006','1750','130' )insert into salary20122617values('000007','

24、;2400','210' )insert into salary20122617values('000008','1800','235' )insert into salary20122617values('000009','2150','210' )-數(shù)據(jù)插入均完成2.2.1.1 11-7實(shí)驗(yàn)代碼update salary20122617 set Base=1800 where P_no=000006 -跟新號(hào)員工的工資和獎(jiǎng)金update salary20122617 se

25、t Bonus='160' where P_no=000006update salary20122617 -將兩年內(nèi)沒有訂單的員工獎(jiǎng)金下調(diào)25%set Bonus=Bonus*.75where not exists(select *from orderdetail20122617where salary20122617.P_no=orderdetail20122617.P_no and Order_date>=GETDATE()-365*2) -getdate獲取當(dāng)前日期2.2.1.2 11-8實(shí)驗(yàn)代碼delete from person20122617 where p_

26、no=000010-刪除號(hào)員工信息2.2.2 實(shí)驗(yàn)結(jié)果2.2.2.1 11-7實(shí)驗(yàn)結(jié)果將salary20122617表中共哈維000006的員工工資增加為1800，獎(jiǎng)金增加為160下調(diào)成功將兩年內(nèi)沒有簽訂單的員工獎(jiǎng)金下調(diào)25%2.2.2.2 11-8實(shí)驗(yàn)結(jié)果由于person20122617表中沒有000010號(hào)員工，故有0行受到影響實(shí)驗(yàn)三：SQL查詢語句3.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)容目的：select語句中各種查詢條件的實(shí)驗(yàn)。內(nèi)容：11-1218。報(bào)告：以11-13、11-14作為實(shí)驗(yàn)三的報(bào)告。3.2 實(shí)驗(yàn)代碼及結(jié)果3.2.1 實(shí)驗(yàn)代碼3.2.1.1 11-13實(shí)驗(yàn)代碼select distinct

27、Deptname from person20122617-查詢person表中的不同部門select * from person20122617 where Sex='女' and P_boss='null'- is null -查詢女經(jīng)理的數(shù)據(jù)select * from person20122617 whereP_name in ('林峰','謝志文','羅向東') -查詢姓名為林峰 謝志文羅向東的信息select * from salary20122617 where P_no between '000

28、003' and '000008' order by Fact asc -把員工號(hào)為000003到0000008的員工按薪水排序select P_no 工號(hào) ,2*Base+1.5*Bonus 收入 from salary20122617 where P_no='000002' -查詢工號(hào)為000002的員工的基本工資加倍,獎(jiǎng)金加.5倍后的實(shí)際收入3.2.1.2 11-14實(shí)驗(yàn)代碼select Deptname 部門 ,avg(Bonus) 平均獎(jiǎng)金 from salary20122617 a join person20122617 b on a.P_n

29、o=b.P_nogroup by Deptname having avg(Bonus)>200 order by avg (Bonus) desc -查詢平均獎(jiǎng)金在元以上的部門并排序join 的用法select count (*) 訂單數(shù),sum(Order_total)訂單總額from orderdetail20122617 , customer20122617where orderdetail20122617.Cust_no=customer20122617.Cust_no and City='上海' -查詢上海顧客的訂單數(shù)和訂單總額3.2.2 實(shí)驗(yàn)結(jié)果3.2.2.1

30、 11-13實(shí)驗(yàn)結(jié)果Person20122617表中確實(shí)只有培訓(xùn)部和銷售部女經(jīng)理確實(shí)只有梁玉瓊一位，上述輸出正確。查詢姓名為林峰 謝志文羅向東的信息查詢結(jié)果正確。把員工號(hào)為000003到0000008的員工按薪水排序。從結(jié)果看出排序正確。查詢工號(hào)為000002的員工的基本工資加倍,獎(jiǎng)金加.5倍后的實(shí)際收入經(jīng)計(jì)算上述輸出正確3.2.2.2 11-14實(shí)驗(yàn)結(jié)果查詢平均獎(jiǎng)金在元以上的部門并排序因?yàn)闆]有獎(jiǎng)金平均數(shù)大于200元的部門存在，故沒有輸出。查詢上海顧客的訂單數(shù)和訂單總額上海顧客的總訂單數(shù)和訂單金額均正確實(shí)驗(yàn)四：視圖及索引的建立和維護(hù)4.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)容目的：創(chuàng)建表的視圖，修改和刪除表的視圖，

31、并利用視圖完成表的查詢，創(chuàng)建表的索引、修改和刪除表的索引。內(nèi)容：11-35、11-911。報(bào)告：以11-3、11-4、11-9作為實(shí)驗(yàn)四的報(bào)告。4.2 實(shí)驗(yàn)代碼及結(jié)果4.2.1 實(shí)驗(yàn)代碼4.2.1.1 11-3實(shí)驗(yàn)代碼create view CustomerView as select Cust_no, Cust_name,Sex,Discount from customer20122617 where City='北京' -建立北京顧客的視圖create view TrainingView as select person20122617.P_no,P_name,Sex,De

32、ptname,SUM(Order_total) as Achievementfrom person20122617,orderdetail20122617where person20122617.P_no=orderdetail20122617.P_no and Deptname='培訓(xùn)部' and P_boss!='null' and Order_date>=getdate()-365group by person20122617.P_no,P_name,Sex,Deptname -建立培訓(xùn)員工的視圖4.2.1.2 11-4實(shí)驗(yàn)代碼create inde

33、x name_sort on person20122617(P_name) -在人員表上的姓名列上創(chuàng)建一個(gè)單列索引name_portcreate index birth_name on person20122617(Birthdate,P_name) -在人員表上創(chuàng)建一個(gè)組合索引birth_namecreate unique index u_name_sort on person20122617(P_name) -在人員表“姓名列上創(chuàng)建一個(gè)唯一索引”u_name_sortcreate clustered index fact_idx on salary20122617(Fact DESC) -

34、在月薪表上創(chuàng)建一個(gè)聚簇縮影fact_idx，并使其按降序索引4.2.1.3 11-9實(shí)驗(yàn)代碼update CustomerViewset Discount=0.85where Cust_name='王云' -將視圖CustomerView中的姓名為“王云”的顧客的購買折扣改為.854.2.2 實(shí)驗(yàn)結(jié)果4.2.2.1 11-3實(shí)驗(yàn)結(jié)果建立北京顧客視圖成功創(chuàng)建培訓(xùn)員工視圖成功4.2.2.2 11-4實(shí)驗(yàn)結(jié)果在人員表上的姓名列上創(chuàng)建一個(gè)單列索引name_port指令：select *from person20122617 where P_name='林峰'創(chuàng)建成功后我

35、們能通過索引P_name查找數(shù)據(jù)了。在人員表上創(chuàng)建一個(gè)組合索引birth_name指令：select *from person20122617 where Birthdate='1975-02-14'and P_name='謝志文'創(chuàng)建成功后我們能通過組合索引查詢到我們想要的信息了。在人員表“姓名列上創(chuàng)建一個(gè)唯一索引”u_name_sort創(chuàng)建索引失敗，因?yàn)閯?chuàng)建的是一個(gè)唯一索引，而在姓名列中有兩個(gè)“林浩然”的存在，故創(chuàng)建唯一索引失敗。在月薪表上創(chuàng)建一個(gè)聚簇縮影fact_idx，并使其按降序索引創(chuàng)建索引失敗，因?yàn)樵趕alary20122617表中我們已經(jīng)定義了一個(gè)

36、主鍵，主鍵將自動(dòng)默認(rèn)為一個(gè)聚簇索引，而一個(gè)表中只能有一個(gè)聚簇索引故會(huì)出現(xiàn)錯(cuò)誤。4.2.2.3 11-9實(shí)驗(yàn)結(jié)果將視圖CustomerView中的姓名為“王云”的顧客的購買折扣改為.85因?yàn)樵谝晥DCustomerView中不存在“王云”顧客 故受到影響的行數(shù)為0行實(shí)驗(yàn)五：存儲(chǔ)過程的建立和維護(hù)5.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)容目的：創(chuàng)建用戶的存儲(chǔ)過程，修改和刪除存儲(chǔ)過程、執(zhí)行存儲(chǔ)過程。內(nèi)容：11-2224。報(bào)告：以11-24作為實(shí)驗(yàn)五的報(bào)告。5.2 實(shí)驗(yàn)代碼及結(jié)果5.2.1 實(shí)驗(yàn)代碼create proc proc_addbonus( P_no char(6),add dec(5,1)output)asdec

37、lare Order_total intdeclare cur_addbonus_checks cursor forselect Order_totalfrom orderdetail20122617where P_no=P_noselect add=0open cur_addbonus_checks fetch cur_addbonus_checks into Order_totalif(fetch_status<>0)beginClose cur_addbonus_checksdeallocate cur_addbonus_checksreturnendset nocount

38、onwhile (fetch_status=0)begin if Order_total<=100000set add=add+20else set add=add+Order_total/100000*30fetch cur_addbonus_checks into Order_totalendclose cur_addbonus_checks return/*-根據(jù)各員工在噢仁derdetail表中的銷售業(yè)績計(jì)算其總的獎(jiǎng)金金額,員工每簽定一份小雨的訂單其獎(jiǎng)金加 若訂單高于,則獎(jiǎng)金加 add=add+Order_total/100000*30*/5.2.2 實(shí)驗(yàn)結(jié)果根據(jù)各員工在噢仁de

39、rdetail表中的銷售業(yè)績計(jì)算其總的獎(jiǎng)金金額,員工每簽定一份小雨的訂單其獎(jiǎng)金加 若訂單高于,則獎(jiǎng)金加 add=add+Order_total/100000*30存儲(chǔ)過程建立成功我們能成功的查詢到工號(hào)為000002的員工的獎(jiǎng)金總額為50.0 。實(shí)驗(yàn)六：觸發(fā)器的建立和維護(hù)6.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)容目的：創(chuàng)建觸發(fā)器，修改和刪除觸發(fā)器，測(cè)試觸發(fā)器的效果。內(nèi)容：11-34。報(bào)告：以11-34作為實(shí)驗(yàn)六的報(bào)告。6.2 實(shí)驗(yàn)代碼及結(jié)果6.2.1 實(shí)驗(yàn)代碼create trigger delect_p_pnoon person20122617after deleteasif rowcount=0 returnd

40、elete salary20122617from titles t,deleted dwhere t.P_no=d.P_noreturn-構(gòu)建一個(gè)person20122617上的能級(jí)聯(lián)刪除salary20122617表中員工信息的觸發(fā)器create trigger update_salaryon salary20122617for updateasdeclare num_rows intselect num_rows=rowcountif num_rows=0 returnif(select count(*)from person20122617 p,inserted iwhere p.P_n