河南科技大學數(shù)據(jù)庫實驗報告

1、實驗一：數(shù)據(jù)定義及更新語句練習一實驗?zāi)康模菏炀氄莆沼肧QL語句實現(xiàn)數(shù)據(jù)庫和基本表的創(chuàng)建、數(shù)據(jù)的更新。二實驗內(nèi)容：（一）用SQL語句建立如下mySPJ數(shù)據(jù)庫，包括S，P，J，和SPJ四個基本表(教材第二章習題5中的四個表)，要求實現(xiàn)關(guān)系的三類完整性。S(SNO,SNAME,STATUS,CITY);P(PNO,PNAME,COLOR,WEIGHT);J(JNO,JNAME,CITY);SPJ(SNO,PNO,JNO,QTY)供應(yīng)商表S由供應(yīng)商代碼（SNO）、供應(yīng)商姓名（SNAME）、供應(yīng)商狀態(tài)（STATUS）、供應(yīng)商所在城市（CITY）組成。零件表P由零件代碼（PNO）、零件名（PNAME）、顏

2、色（COLOR）、重量（WEIGHT）組成。工程項目表J由工程項目代碼（JNO）、工程項目名（JNAME）、工程項目所在城市（CITY）組成。供應(yīng)情況表SPJ由供應(yīng)商代碼（SNO）、零件代碼（PNO）、工程項目代碼（JNO）、供應(yīng)數(shù)量（QTY）組成，表示某供應(yīng)商 供應(yīng)某種零件 給某工程項目的數(shù)量為QTY。（二）在數(shù)據(jù)庫mySPJ中建立S，P，J，和SPJ四個基本表：按照實驗內(nèi)容給出的基本表結(jié)構(gòu)在查詢分析器窗口中書寫SQL語句分別建立各表，并設(shè)置主鍵和外鍵約束；三完成情況USE masterGOCREATE DATABASE myspjON ( NAME = TEXTS_dat, FILENAM

3、E = F:數(shù)據(jù)庫實驗myspjdat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = TEXTS_log, FILENAME = F:數(shù)據(jù)庫實驗myspjlog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )bGOcreate table s(sno char(9) primary key,sname char(20),statu int,city char(20),);create table p(pno char(9) primary key,pname ch

4、ar(20),color char(20),weigh int,);create table j(jno char(9) primary key,jname char(20),city char(20),);create table spj(sno char(9),pno char(9),jno char(9),qty int,primary key(sno,pno,jno),foreign key (sno)references s(sno),foreign key (pno)references p(pno),foreign key (jno)references j(jno),);1.利

5、用Insert 語句將習題中給出的示例記錄插入各表。insert into svalues(s1,精益,20,天津);insert into svalues(s2,盛錫,10,北京);insert into svalues(s3,東方紅,30,北京);insert into svalues(s4,豐泰盛,20,天津);insert into svalues(s5,為民,30,上海);insert into pvalues(p1,螺母,紅,12);insert into pvalues(p2,螺栓,綠,17);insert into pvalues(p3,螺絲刀,藍,14);insert int

6、o pvalues(p4,螺絲刀,紅,14);insert into pvalues(p5,凸輪,藍,40);insert into pvalues(p6,齒輪,紅,30);insert into jvalues(j1,三建,北京);insert into jvalues(j2,一汽,長春);insert into jvalues(j3,彈簧廠,天津);insert into jvalues(j4,造船廠,天津);insert into jvalues(j5,機車廠,唐山);insert into jvalues(j6,無線電廠,常州);insert into jvalues(j7,半導體廠,

7、南京);insert into spjvalues(s1,p1,j1,200);insert into spjvalues(s1,p1,j3,100);insert into spjvalues(s1,p1,j4,700);insert into spjvalues(s1,p2,j2,100);insert into spjvalues(s2,p31,j1,400);insert into spjvalues(s2,p3,j2,200);insert into spjvalues(s2,p3,j4,500);insert into spjvalues(s2,p3,j5,400);insert

8、into spjvalues(s2,p5,j1,400);insert into spjvalues(s2,p5,j2,100);insert into spjvalues(s3,p1,j1,200);insert into spjvalues(s3,p3,j1,200);insert into spjvalues(s4,p5,j1,100);insert into spjvalues(s4,p6,j3,300);insert into spjvalues(s4,p6,j4,200);insert into spjvalues(s5,p2,j4,100);insert into spjvalu

9、es(s5,p3,j1,200);insert into spjvalues(s5,p6,j2,200);2.利用Update更新表中的記錄：1將p表中的所有紅色零件的重量增加5。update pset weigh=weigh+5where color=紅;2將spj表中所有天津供應(yīng)商的QTY屬性值減少10。用子查詢。并記錄在實驗報告中。update spjset qty=qty-10where sno in(select sno from s where city=天津);3.利用Delete語句刪除p表中的所有紅色零件的記錄。delete from p where color=紅;思考題（

10、一） 在為各表設(shè)定關(guān)鍵字時彈出的頁面中有關(guān)鍵字和索引選項，試通過給基本表設(shè)定這兩個項目并說明二者的區(qū)別。答:關(guān)鍵字受主鍵影響，而索引不受主鍵影響（二） 在基本表中輸入數(shù)據(jù)時，注意數(shù)據(jù)與字段的數(shù)據(jù)類型和長度以及能否為空的屬性是否一致，保存時如有錯誤分析錯誤原因，及時改正并將錯誤報告寫在實驗報告中。七實驗總結(jié)：心得體會，建議等。答：從這次實驗中我明白一個道理：細節(jié)決定成敗，尤其是要注意sql語言與c語言的差別。 實驗二：簡單查詢和連接查詢一實驗?zāi)康模菏炀氄莆沼肧QL語句實現(xiàn)的簡單查詢和多個數(shù)據(jù)表連接查詢。二實驗內(nèi)容：(一) 完成下面的簡單查詢：查詢所有“天津”的供應(yīng)商明細；查詢所有“紅色”的14公

11、斤以上的零件。查詢工程名稱中含有“廠”字的工程明細。(二) 完成下面的連接查詢：等值連接：求s表和j表的相同城市的等值連接。自然連接：查詢所有的供應(yīng)明細，要求顯示供應(yīng)商、零件和工程的名稱，并按照供應(yīng)、工程、零件排序。笛卡爾積：求s和p表的笛卡爾積左連接：求j表和spj表的左連接。右連接：求spj表和j表的右連接。三完成情況(三) 完成下面的簡單查詢：詢所有“天津”的供應(yīng)商明細； select *from s where city=天津;查詢所有“紅色”的14公斤以上的零件。select pnofrom p where color=紅and weigh14;查詢工程名稱中含有“廠”字的工程明細。

12、select *from j where jname like%廠;(四) 完成下面的連接查詢：值連接：求s表和j表的相同城市的等值連接。 select s.*,j.*from s,jwhere s.city=j.city;自然連接：查詢所有的供應(yīng)明細，要求顯示供應(yīng)商、零件和工程的名稱，并按照供應(yīng)、工程、零件排序。select s.sname,p.pname ,j.jname from s,p,j,spjwhere s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno;笛卡爾積：求s和p表的笛卡爾select *from s,p左連接：求j表和sp

13、j表的左連接。select j.*,sno,pno,qtyfrom j left outer join spj on (j.jno=spj.jno);右連接：求spj表和j表的右連接。select j.*,sno,pno,qtyfrom spj right outer join j on (j.jno=spj.jno);回答思考題提出的問題，查看實驗內(nèi)容2中左連接和右連接的執(zhí)行結(jié)果是否一致，為什么？答：不一樣，左連接是輸出第一個表的全部，二右連接是輸出第二個表的全部。七實驗總結(jié)：心得體會，建議等。答：從這次實驗中我明白一個道理：細節(jié)決定成敗，尤其是要注意sql語言與c語言的差別。實驗三：分組查

14、詢和嵌套查詢1、 實驗?zāi)康模菏炀氄莆沼肧QL語句實現(xiàn)多個數(shù)據(jù)表的分組查詢和嵌套查詢。2、 實驗內(nèi)容：完成下面的查詢：(一) 完成下面的查詢：1、分組查詢：1 求各種顏色零件的平均重量。 select color,AVG(weigh)weight from p group by color;2 求北京供應(yīng)商和天津供應(yīng)商的總個數(shù)。 select COUNT(city)個數(shù) from s where city=北京or city=天津;3 求各供應(yīng)商供應(yīng)的零件總數(shù)。 select sno,sum(qty)數(shù)目 from spj group by sno order by 數(shù)目 asc;4 求各供應(yīng)商

15、供應(yīng)給各工程的零件總數(shù)。 select sno,jno,sum(qty)數(shù)目 from spj group by sno,jno;5 求使用了100個以上P1零件的工程名稱。 select jname from j where jno in ( select jno from spj where pno=p1 and qty100 );6 求各工程使用的各城市供應(yīng)的零件總數(shù)。 select jname from j where jno in ( select jno from spj where pno=p1 and qty100 );（二） 嵌套查詢：1. in連接謂詞查詢： 查詢沒有使用天

16、津供應(yīng)商供應(yīng)的紅色零件的工程名稱。 select jname from j where jno not in ( select jno from s,p,spj where spj.sno=s.sno and spj.pno=p.pno and p.color=紅and s.city=天津 ) 查詢供應(yīng)了1000個以上零件的供應(yīng)商名稱。(having) select sname from s where sno in ( select sno from spj group by sno having SUM(qty)1000 )2. 比較運算符：求重量大于所有零件平均重量的零件名稱。 sele

17、ct pname from p where weigh( select AVG(weigh) from p3. )Exists連接謂詞： 查詢供應(yīng)J1的所有的零件都是紅色的供應(yīng)商名稱。 select sname from s where exists( select * from p,spj where p.pno=spj.pno and spj.sno=s.sno and jno=j1and color=紅); 至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO。實驗五：觸發(fā)器和存儲過程一、 實驗報告要求：(一) 實驗?zāi)康模?理解觸發(fā)器和存儲過程的含義，掌握用SQL語句實現(xiàn)觸發(fā)器和存儲過程的編

18、寫，并初步掌握什么情況下使用事務(wù)。(二) 實驗內(nèi)容：有一個小型的圖書管理數(shù)據(jù)庫，包含的表為：bookstore（bookid，bookname，bookauthor，purchasedate，state）；-圖書庫存表borrowcard（cardid，ownername）；-借書證表borrowlog（cardid，bookid，borrowdate，returndate）；-借書記錄表寫一個存儲過程，實現(xiàn)借書操作，要求有事務(wù)處理。（1）讀者借書，要先設(shè)置書籍不在庫標志state（借出），然后增加借書記錄，在同一事務(wù)中完成。 （2）要求在事務(wù)執(zhí)行過程中引入錯誤觸發(fā)事件，以此體會事務(wù)的錯誤保護

19、機制和事務(wù)編程的作用。（3）要求用觸發(fā)器實現(xiàn)表的完整性控制。（三）完成情況（附上設(shè)計的SQL語句）。建立數(shù)據(jù)庫：create database bookstoruse bookstorcreate table bookstore(bookid int,bookname char(20),bookauthor char(20),purchasedate char(20),stat char(6),primary key(bookid),Check(stat in(在庫,不在庫),);create table borrowcard(cardid int primary key,ownername

20、char(20),);create table borrowlog(cardid int,bookid int,borrowdate char(20),returndate char(20),primary key(cardid,bookid),foreign key(cardid)references borrowcard(cardid),foreign key(bookid)references bookstore(bookid),); 存儲過程：create procedure dbo.jieshu(cardid int,bookid int,borrowdate char(20),returndate char(20)asbegin transactioninsertinto borro