數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告河南科技大學(xué).doc

數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告班級(jí)名稱：計(jì)科113班學(xué)生學(xué)號(hào)：111404060313學(xué)生姓名：李曉貝實(shí)驗(yàn)時(shí)間：201305實(shí)驗(yàn)一：數(shù)據(jù)定義及更新語句練習(xí)一、 實(shí)驗(yàn)?zāi)康模菏炀氄莆沼肧QL語句實(shí)現(xiàn)數(shù)據(jù)庫(kù)和基本表的創(chuàng)建、數(shù)據(jù)的更新。二、 實(shí)驗(yàn)內(nèi)容：（一）用SQL語句建立如下mySPJ數(shù)據(jù)庫(kù)，包括S，P，J，和SPJ四個(gè)基本表(教材第二章習(xí)題5中的四個(gè)表)，要求實(shí)現(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）、顏色（COLOR）、重量（WEIGHT）組成。工程項(xiàng)目表J由工程項(xiàng)目代碼（JNO）、工程項(xiàng)目名（JNAME）、工程項(xiàng)目所在城市（CITY）組成。供應(yīng)情況表SPJ由供應(yīng)商代碼（SNO）、零件代碼（PNO）、工程項(xiàng)目代碼（JNO）、供應(yīng)數(shù)量（QTY）組成，表示某供應(yīng)商 供應(yīng)某種零件 給某工程項(xiàng)目的數(shù)量為QTY。（2） 分別使用插入、刪除、修改的方式更新基本表中的數(shù)據(jù)。三、完成情況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), /外鍵約束);四、實(shí)驗(yàn)結(jié)果1、利用Insert 語句將習(xí)題中給出的示例記錄插入各表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,螺絲刀,藍(lán),14)insert into pvalues (p4,螺絲刀,紅,14)insert into pvalues (p5,凸輪,藍(lán),40)insert into pvalues (p6,齒輪,紅,30)select *from pinsert into jvalues (j1,三建,北京)insert into jvalues (j2,一汽,長(zhǎng)春)insert into jvalues (j3,彈簧廠,天津)insert into jvalues (j4,造船廠,天津)insert into jvalues (j5,機(jī)車廠,唐山)insert into jvalues (j6,無線電廠,常州)insert into jvalues (j7,半導(dǎo)體廠,南京)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表中所有天津供應(yīng)商的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沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)myspj，表dbo.spj, column pno。語句已終止。（p.pno作為spj.pno的外鍵，兩張表有關(guān)系）5、 問題及解決 1、數(shù)據(jù)庫(kù)如何保存？本以為要用一次輸入一次，后來通過老師講解知道可以分離數(shù)據(jù)庫(kù)（只保留.mdf文件就行了，到用時(shí)在附加數(shù)據(jù)庫(kù)），還可以備份數(shù)據(jù)庫(kù)。2、 輸入表是出錯(cuò)？有時(shí)候建基本表時(shí)容易輸錯(cuò)，再往下就不然輸入了，只能把整個(gè)表刪了重建了。 六、思考題：（1） 在為各表設(shè)定關(guān)鍵字時(shí)彈出的頁(yè)面中有關(guān)鍵字和索引選項(xiàng)，試通過給基本表設(shè)定這兩個(gè)項(xiàng)目并說明二者的區(qū)別。答：索引可以加快表的查詢速度，經(jīng)常用來查詢的一個(gè)或者幾個(gè)字段設(shè)置為索引。（2） 在基本表中輸入數(shù)據(jù)時(shí)，注意數(shù)據(jù)與字段的數(shù)據(jù)類型和長(zhǎng)度以及能否為空的屬性是否一致，保存時(shí)如有錯(cuò)誤分析錯(cuò)誤原因，及時(shí)改正并將錯(cuò)誤報(bào)告寫在實(shí)驗(yàn)報(bào)告中。答：在基本表中輸入數(shù)據(jù)時(shí)，不為空的地方不可以輸入空值，否則將無法繼續(xù)輸入。七、實(shí)驗(yàn)總結(jié)：答：建數(shù)據(jù)庫(kù)的時(shí)候容易出錯(cuò)，工作比較繁瑣，稍不小心，不管通過代碼添加還是手工輸入都很可能稍輸或輸錯(cuò)。最后還需要仔細(xì)檢查才行，語句方面還都是一些簡(jiǎn)單的語句，可以完成。對(duì)于這次試驗(yàn)的基礎(chǔ)還是可以把握的，通過這一次的實(shí)驗(yàn)，對(duì)數(shù)據(jù)庫(kù)有了一個(gè)全新的認(rèn)識(shí)，同時(shí)也對(duì)開發(fā)環(huán)境有了一定的了解，生動(dòng)的實(shí)驗(yàn)比單調(diào)的課本理論知識(shí)有意思多了！實(shí)驗(yàn)二：簡(jiǎn)單查詢和連接查詢1、 實(shí)驗(yàn)?zāi)康模菏炀氄莆沼肧QL語句實(shí)現(xiàn)的簡(jiǎn)單查詢和多個(gè)數(shù)據(jù)表連接查詢。2、 實(shí)驗(yàn)內(nèi)容：(一) 完成下面的簡(jiǎn)單查詢：查詢所有“天津”的供應(yīng)商明細(xì)；查詢所有“紅色”的14公斤以上的零件。查詢工程名稱中含有“廠”字的工程明細(xì)。(二) 完成下面的連接查詢：等值連接：求s表和j表的相同城市的等值連接。自然連接：查詢所有的供應(yīng)明細(xì)，要求顯示供應(yīng)商、零件和工程的名稱，并按照供應(yīng)、工程、零件排序。笛卡爾積：求s和p表的笛卡爾積左連接：求j表和spj表的左連接。右連接：求spj表和j表的右連接。3、 完成情況：（1） 完成下面的簡(jiǎn)單查詢：查詢所有“天津”的供應(yīng)商明細(xì)；select *from swhere city =天津查詢所有“紅色”的14公斤以上的零件select *from pwhere color =紅 and weight 14查詢工程名稱中含有“廠”字的工程明細(xì)select *from jwhere jname like%廠（2） 完成下面的連接查詢：等值連接：求s表和j表的相同城市的等值連接。select s.*,j.*from s ,jwhere s.city =j.city自然連接：查詢所有的供應(yīng)明細(xì)，要求顯示供應(yīng)商、零件和工程的名稱，并按照供應(yīng)、工程、零件排序。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、 實(shí)驗(yàn)結(jié)果：（一）查詢所有“天津”的供應(yīng)商明細(xì)；查詢所有“紅色”的14公斤以上的零件查詢工程名稱中含有“廠”字的工程明細(xì)（二）等值連接：求s表和j表的相同城市的等值連接自然連接：查詢所有的供應(yīng)明細(xì)，要求顯示供應(yīng)商、零件和工程的名稱，并按照供應(yīng)、工程、零件排序。笛卡爾積：求s和p表的笛卡爾積左連接：求j表和spj表的左連接右連接：求spj表和j表的右連接5、 問題及解決：答：對(duì)“笛卡爾積”這一概念不了解，因此在實(shí)驗(yàn)時(shí)遇到了麻煩，仔細(xì)翻閱書本知識(shí)后，再加上實(shí)驗(yàn)的應(yīng)用，對(duì)這一塊的知識(shí)點(diǎn)理解不少。6、 思考題：查看實(shí)驗(yàn)內(nèi)容2中左連接和右連接的執(zhí)行結(jié)果是否一致，為什么？一樣，j表和spj表的左連接和spj表和j表的右連接本質(zhì)是一樣的，j表和spj表的左連接保留了j表的全部，spj表和j表的右連接還是保留了j表的全部，所以最后查詢結(jié)果一致。7、 實(shí)驗(yàn)總結(jié)：答：通過實(shí)驗(yàn)的實(shí)踐隊(duì)一些概念的理解和掌握有很大的幫助，今后的學(xué)習(xí)應(yīng)多動(dòng)手，多做實(shí)驗(yàn)，有助于掌握知識(shí)點(diǎn)。實(shí)驗(yàn)三：分組查詢和嵌套查詢一、 實(shí)驗(yàn)?zāi)康模菏炀氄莆沼肧QL語句實(shí)現(xiàn)多個(gè)數(shù)據(jù)表的分組查詢和嵌套查詢。二、 預(yù)習(xí)要求：實(shí)驗(yàn)前復(fù)習(xí)講授過的有關(guān)分組查詢和嵌套查詢的知識(shí)，編寫相應(yīng)的SQL語句。三、 實(shí)驗(yàn)內(nèi)容：(一) 分組查詢：1. 求各種顏色零件的平均重量。2. 求北京供應(yīng)商和天津供應(yīng)商的總個(gè)數(shù)。3. 求各供應(yīng)商供應(yīng)的零件總數(shù)。4. 求各供應(yīng)商供應(yīng)給各工程的零件總數(shù)。5. 求使用了100個(gè)以上P1零件的工程名稱。6. 求各工程使用的各城市供應(yīng)的零件總數(shù)。（二） 嵌套查詢：1. in連接謂詞查詢： 查詢沒有使用天津供應(yīng)商供應(yīng)的紅色零件的工程名稱。 查詢供應(yīng)了1000個(gè)以上零件的供應(yīng)商名稱。(having)2. 比較運(yùn)算符：求重量大于所有零件平均重量的零件名稱。3. Exists連接謂詞： 查詢供應(yīng)J1的所有的零件都是紅色的供應(yīng)商名稱。 至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號(hào)JNO。4、 完成情況及實(shí)驗(yàn)結(jié)果（1） 分組查詢：（2） 1.求各種顏色零件的平均重量。 SELECT COLOR ,AVG(WEIGH)平均重量 FROM P group by color;2. 求北京供應(yīng)商和天津供應(yīng)商的總個(gè)數(shù)。select count(city) as 數(shù)目,CITYfrom swhere city=北京 or city=天津group by city3. 求各供應(yīng)商供應(yīng)的零件總數(shù)。select sno, sum(qty)零件總數(shù)from spjgroup by sno4. 求各供應(yīng)商供應(yīng)給各工程的零件總數(shù)。select sno,jno, sum(qty)零件總數(shù)from spjgroup by sno,jno5. 求使用了100個(gè)以上P1零件的工程名稱。 select jno,sum(qty)as qty from spj where pno=p1 group by jno having sum(qty)100 6. 求各工程使用的各城市供應(yīng)的零件總數(shù)。 select spj.jno,s.city,sum(qty)零件總數(shù) from spj,s where s.sno=spj.sno group by spj.jno,s.city （二） 嵌套查詢：4. in連接謂詞查詢： 查詢沒有使用天津供應(yīng)商供應(yīng)的紅色零件的工程名稱。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=紅) 查詢供應(yīng)了1000個(gè)以上零件的供應(yīng)商名稱。(having)select sno,snamefrom swhere sno in (select sno from spj group by spj.sno having sum(qty)1000)5. 比較運(yùn)算符：求重量大于所有零件平均重量的零件名稱。select pnamefrom pwhere weigh (select avg(weigh) from p)6. Exists連接謂詞： 查詢供應(yīng)J1的所有的零件都是紅色的供應(yīng)商名稱。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=紅) 至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號(hào)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連接謂詞查詢，關(guān)鍵字in可以直接用any代替么？什么情況下in和any可以互相代替？答：關(guān)鍵字 in 不可以直接用any 代替。但“=any”的等價(jià)于in。（2） 嵌套查詢中的內(nèi)查詢?yōu)橥獠樵兎祷氐膬?nèi)容是什么？是表達(dá)式？視圖？還是物理數(shù)據(jù)集合？答：含 in，any 的嵌套查詢返回的結(jié)果通常是一個(gè)集合，含exists謂語的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯值“true”或“false”。(一) 問題及解決：首先寫出執(zhí)行語句不成功的時(shí)候系統(tǒng)報(bào)告的錯(cuò)誤信息。然后分析錯(cuò)誤原因，并給出解決辦法。select pnamefrom pwhere weight (select avg(weight) from p)分析錯(cuò)誤原因：表中列名與SQL中列名不一致，因此導(dǎo)致weight 無效。解決方法：將weight該為weigh 。(二) 實(shí)驗(yàn)總結(jié)：心得體會(huì)，建議等。答：本次實(shí)驗(yàn)，使我對(duì)書本上的知識(shí)有了進(jìn)一步的了解，尤其是分組查詢那部分。只有自己親自去做去實(shí)踐，才能發(fā)現(xiàn)問題，自己解決問題，更感覺到了實(shí)踐的重要性，學(xué)過的知識(shí)應(yīng)該親自上機(jī)練習(xí)一下，這樣才能對(duì)理論的知識(shí)掌握更牢固，理解更透徹。實(shí)驗(yàn)四：視圖操作和數(shù)據(jù)控制 一、 實(shí)驗(yàn)?zāi)康模菏炀氄莆沼肧QL語句實(shí)現(xiàn)視圖操作和數(shù)據(jù)控制。二、 預(yù)習(xí)要求：實(shí)驗(yàn)前復(fù)習(xí)講授過的有關(guān)視圖操作和數(shù)據(jù)控制的知識(shí)，編寫相應(yīng)的SQL語句。三、 實(shí)驗(yàn)內(nèi)容：（一）定義如下視圖：查詢北京的供應(yīng)商的編號(hào)、名稱和城市。查詢S1供應(yīng)商的所有供應(yīng)明細(xì)。查詢各工程名稱使用的各種顏色零件的個(gè)數(shù)。查詢上面定義的視圖。 （二）數(shù)據(jù)控制： 使用GRANT把對(duì)S表查詢的權(quán)利授予WangLi。 使用GRANT把對(duì)P表查詢、插入、修改、刪除的權(quán)利授予LiMing。 使用REVOKE把LiMing對(duì)P表插入、刪除的權(quán)利回收。4、 完成情況及實(shí)驗(yàn)結(jié)果（一）定義如下視圖：查詢北京的供應(yīng)商的編號(hào)、名稱和城市。 create view s_bj(sno,sname,city) as (select sno,sname,city from s where city=北京)查詢S1供應(yīng)商的所有供應(yīng)明細(xì)。 create view s1_spj as select sno,pno,jno,qty from spj where sno=s1查詢各工程名稱使用的各種顏色零件的個(gè)數(shù)。 create view jno_pcolor_qty(jno,color,qty) as select spj.jno,