數據庫實驗報告班級名稱：計科113班學生學號：111404060313學生姓名：李曉貝實驗時間：201305實驗一：數據定義及更新語句練習一、 實驗目的：熟練掌握用SQL語句實現數據庫和基本表的創建、數據的更新。二、 實驗內容：（一）用SQL語句建立如下mySPJ數據庫，包括S，P，J，和SPJ四個基本表(教材第二章習題5中的四個表)，要求實現關系的三類完整性。S(SNO,SNAME,STATUS,CITY);P(PNO,PNAME,COLOR,WEIGHT);J(JNO,JNAME,CITY);SPJ(SNO,PNO,JNO,QTY)供應商表S由供應商代碼（SNO）、供應商姓名（SNAME）、供應商狀態（STATUS）、供應商所在城市（CITY）組成。零件表P由零件代碼（PNO）、零件名（PNAME）、顏色（COLOR）、重量（WEIGHT）組成。工程項目表J由工程項目代碼（JNO）、工程項目名（JNAME）、工程項目所在城市（CITY）組成。供應情況表SPJ由供應商代碼（SNO）、零件代碼（PNO）、工程項目代碼（JNO）、供應數量（QTY）組成，表示某供應商 供應某種零件 給某工程項目的數量為QTY。（2） 分別使用插入、刪除、修改的方式更新基本表中的數據。三、完成情況create table s(sno char(9) primary key, /主鍵約束sname char(20) unique , /唯一值status smallint ,city char(20),);create table p(pno char(9) primary key, /主鍵約束pname char(20) , color char(9),weight smallint,);create table j(jno char(9) primary key, /主鍵約束jname char(20) unique, /唯一值city char(20),);create table spj(sno char(9),pno char(9),jno char(9),QTY smallint,primary key(sno,pno,jno), /主鍵約束foreign key (sno) references s(sno), /外鍵約束foreign key (pno) references p(pno), /外鍵約束foreign key (jno) references j(jno), /外鍵約束);四、實驗結果1、利用Insert 語句將習題中給出的示例記錄插入各表insert into s(sno,sname,status,city)values (s1,精益,20,天津)insert into svalues (s2,盛錫,10,北京)insert into svalues (s3,東方紅,30,北京)insert into svalues (s4,豐泰盛,20,天津)insert into svalues (s5,為民,30,上海)select *from sinsert into pvalues (p1,螺母,紅,12)insert into pvalues (p2,螺栓,綠,17)insert into pvalues (p3,螺絲刀,藍,14)insert into pvalues (p4,螺絲刀,紅,14)insert into pvalues (p5,凸輪,藍,40)insert into pvalues (p6,齒輪,紅,30)select *from pinsert 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,半導體廠,南京)select *from jinsert 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,p3,j1,400)insert into spjvalues (s2,p3,j2,200)insert into spjvalues (s2,p3,j4,500)insert into spjvalues (s2,p3,j5,400)insert 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 spjvalues (s5,p3,j1,200)insert into spjvalues (s5,p6,j2,200)insert into spjvalues (s5,p6,j4,500)select *from spj2. 利用Update更新表中的記錄：將p表中的所有紅色零件的重量增加5。update pset weight=weight-5where color=紅將spj表中所有天津供應商的QTY屬性值減少10。用子查詢。update spj set QTY =QTY -10where sno in(select sno from swhere city =天津)3. 利用Delete語句刪除p表中的所有紅色零件的記錄。delete from p where color =紅DELETE 語句與REFERENCE 約束FK_spj_pno_4F7CD00D沖突。該沖突發生于數據庫myspj，表dbo.spj, column pno。語句已終止。（p.pno作為spj.pno的外鍵，兩張表有關系）5、 問題及解決 1、數據庫如何保存？本以為要用一次輸入一次，后來通過老師講解知道可以分離數據庫（只保留.mdf文件就行了，到用時在附加數據庫），還可以備份數據庫。2、 輸入表是出錯？有時候建基本表時容易輸錯，再往下就不然輸入了，只能把整個表刪了重建了。 六、思考題：（1） 在為各表設定關鍵字時彈出的頁面中有關鍵字和索引選項，試通過給基本表設定這兩個項目并說明二者的區別。答：索引可以加快表的查詢速度，經常用來查詢的一個或者幾個字段設置為索引。（2） 在基本表中輸入數據時，注意數據與字段的數據類型和長度以及能否為空的屬性是否一致，保存時如有錯誤分析錯誤原因，及時改正并將錯誤報告寫在實驗報告中。答：在基本表中輸入數據時，不為空的地方不可以輸入空值，否則將無法繼續輸入。七、實驗總結：答：建數據庫的時候容易出錯，工作比較繁瑣，稍不小心，不管通過代碼添加還是手工輸入都很可能稍輸或輸錯。最后還需要仔細檢查才行，語句方面還都是一些簡單的語句，可以完成。對于這次試驗的基礎還是可以把握的，通過這一次的實驗，對數據庫有了一個全新的認識，同時也對開發環境有了一定的了解，生動的實驗比單調的課本理論知識有意思多了！實驗二：簡單查詢和連接查詢1、 實驗目的：熟練掌握用SQL語句實現的簡單查詢和多個數據表連接查詢。2、 實驗內容：(一) 完成下面的簡單查詢：查詢所有“天津”的供應商明細；查詢所有“紅色”的14公斤以上的零件。查詢工程名稱中含有“廠”字的工程明細。(二) 完成下面的連接查詢：等值連接：求s表和j表的相同城市的等值連接。自然連接：查詢所有的供應明細，要求顯示供應商、零件和工程的名稱，并按照供應、工程、零件排序。笛卡爾積：求s和p表的笛卡爾積左連接：求j表和spj表的左連接。右連接：求spj表和j表的右連接。3、 完成情況：（1） 完成下面的簡單查詢：查詢所有“天津”的供應商明細；select *from swhere city =天津查詢所有“紅色”的14公斤以上的零件select *from pwhere color =紅 and weight 14查詢工程名稱中含有“廠”字的工程明細select *from jwhere jname like%廠（2） 完成下面的連接查詢：等值連接：求s表和j表的相同城市的等值連接。select s.*,j.*from s ,jwhere s.city =j.city自然連接：查詢所有的供應明細，要求顯示供應商、零件和工程的名稱，并按照供應、工程、零件排序。select sname,jname,pnamefrom s ,p ,j,spjwhere spj.sno = s.sno and spj.pno = p.pno and spj.jno = j.jno笛卡爾積：求s和p表的笛卡爾積select *from s,p左連接：求j表和spj表的左連接select j.jno,jname,city,spj.sno,pno ,QTYfrom j left join spj on(j.jno=spj.jno)右連接：求spj表和j表的右連接select j.jno,jname,city,spj.sno,pno ,QTYfrom spj right join j on(j.jno=spj.jno)4、 實驗結果：（一）查詢所有“天津”的供應商明細；查詢所有“紅色”的14公斤以上的零件查詢工程名稱中含有“廠”字的工程明細（二）等值連接：求s表和j表的相同城市的等值連接自然連接：查詢所有的供應明細，要求顯示供應商、零件和工程的名稱，并按照供應、工程、零件排序。笛卡爾積：求s和p表的笛卡爾積左連接：求j表和spj表的左連接右連接：求spj表和j表的右連接5、 問題及解決：答：對“笛卡爾積”這一概念不了解，因此在實驗時遇到了麻煩，仔細翻閱書本知識后，再加上實驗的應用，對這一塊的知識點理解不少。6、 思考題：查看實驗內容2中左連接和右連接的執行結果是否一致，為什么？一樣，j表和spj表的左連接和spj表和j表的右連接本質是一樣的，j表和spj表的左連接保留了j表的全部，spj表和j表的右連接還是保留了j表的全部，所以最后查詢結果一致。7、 實驗總結：答：通過實驗的實踐隊一些概念的理解和掌握有很大的幫助，今后的學習應多動手，多做實驗，有助于掌握知識點。實驗三：分組查詢和嵌套查詢一、 實驗目的：熟練掌握用SQL語句實現多個數據表的分組查詢和嵌套查詢。二、 預習要求：實驗前復習講授過的有關分組查詢和嵌套查詢的知識，編寫相應的SQL語句。三、 實驗內容：(一) 分組查詢：1. 求各種顏色零件的平均重量。2. 求北京供應商和天津供應商的總個數。3. 求各供應商供應的零件總數。4. 求各供應商供應給各工程的零件總數。5. 求使用了100個以上P1零件的工程名稱。6. 求各工程使用的各城市供應的零件總數。（二） 嵌套查詢：1. in連接謂詞查詢： 查詢沒有使用天津供應商供應的紅色零件的工程名稱。 查詢供應了1000個以上零件的供應商名稱。(having)2. 比較運算符：求重量大于所有零件平均重量的零件名稱。3. Exists連接謂詞： 查詢供應J1的所有的零件都是紅色的供應商名稱。 至少用了供應商S1所供應的全部零件的工程號JNO。4、 完成情況及實驗結果（1） 分組查詢：（2） 1.求各種顏色零件的平均重量。 SELECT COLOR ,AVG(WEIGH)平均重量 FROM P group by color;2. 求北京供應商和天津供應商的總個數。select count(city) as 數目,CITYfrom swhere city=北京 or city=天津group by city3. 求各供應商供應的零件總數。select sno, sum(qty)零件總數from spjgroup by sno4. 求各供應商供應給各工程的零件總數。select sno,jno, sum(qty)零件總數from spjgroup by sno,jno5. 求使用了100個以上P1零件的工程名稱。 select jno,sum(qty)as qty from spj where pno=p1 group by jno having sum(qty)100 6. 求各工程使用的各城市供應的零件總數。 select spj.jno,s.city,sum(qty)零件總數 from spj,s where s.sno=spj.sno group by spj.jno,s.city （二） 嵌套查詢：4. in連接謂詞查詢： 查詢沒有使用天津供應商供應的紅色零件的工程名稱。select jno,jnamefrom jwhere not exists (select * from spj where spj.jno=j.jno and sno in (select sno from s where city=天津 )and pno in (select pno from p where color=紅) 查詢供應了1000個以上零件的供應商名稱。(having)select sno,snamefrom swhere sno in (select sno from spj group by spj.sno having sum(qty)1000)5. 比較運算符：求重量大于所有零件平均重量的零件名稱。select pnamefrom pwhere weigh (select avg(weigh) from p)6. Exists連接謂詞： 查詢供應J1的所有的零件都是紅色的供應商名稱。select sno,snamefrom swhere sno in(select distinct snofrom spj xwhere not exists (select * from spj y where y.sno=x.sno and y.jno=j1and not exists (select * from p where p.pno=y.pno and color=紅) 至少用了供應商S1所供應的全部零件的工程號JNO。select distinct jnofrom spj spjxwhere not exists (select * from spj spjy where sno=s1 and not exists (select * from spj spjz where spjz.pno=spjy.pno and spjz.jno=spjx.jno)四、 思考題：（1） 嵌套查詢中的in連接謂詞查詢，關鍵字in可以直接用any代替么？什么情況下in和any可以互相代替？答：關鍵字 in 不可以直接用any 代替。但“=any”的等價于in。（2） 嵌套查詢中的內查詢為外查詢返回的內容是什么？是表達式？視圖？還是物理數據集合？答：含 in，any 的嵌套查詢返回的結果通常是一個集合，含exists謂語的子查詢不返回任何數據，只產生邏輯值“true”或“false”。(一) 問題及解決：首先寫出執行語句不成功的時候系統報告的錯誤信息。然后分析錯誤原因，并給出解決辦法。select pnamefrom pwhere weight (select avg(weight) from p)分析錯誤原因：表中列名與SQL中列名不一致，因此導致weight 無效。解決方法：將weight該為weigh 。(二) 實驗總結：心得體會，建議等。答：本次實驗，使我對書本上的知識有了進一步的了解，尤其是分組查詢那部分。只有自己親自去做去實踐，才能發現問題，自己解決問題，更感覺到了實踐的重要性，學過的知識應該親自上機練習一下，這樣才能對理論的知識掌握更牢固，理解更透徹。實驗四：視圖操作和數據控制 一、 實驗目的：熟練掌握用SQL語句實現視圖操作和數據控制。二、 預習要求：實驗前復習講授過的有關視圖操作和數據控制的知識，編寫相應的SQL語句。三、 實驗內容：（一）定義如下視圖：查詢北京的供應商的編號、名稱和城市。查詢S1供應商的所有供應明細。查詢各工程名稱使用的各種顏色零件的個數。查詢上面定義的視圖。 （二）數據控制： 使用GRANT把對S表查詢的權利授予WangLi。 使用GRANT把對P表查詢、插入、修改、刪除的權利授予LiMing。 使用REVOKE把LiMing對P表插入、刪除的權利回收。4、 完成情況及實驗結果（一）定義如下視圖：查詢北京的供應商的編號、名稱和城市。 create view s_bj(sno,sname,city) as (select sno,sname,city from s where city=北京)查詢S1供應商的所有供應明細。 create view s1_spj as select sno,pno,jno,qty from spj where sno=s1查詢各工程名稱使用的各種顏色零件的個數。 create view jno_pcolor_qty(jno,color,qty) as select spj.jno,