數(shù)據(jù)庫(kù)建立及查詢

數(shù)據(jù)庫(kù)建立及查詢一、實(shí)驗(yàn)?zāi)康?.熟練掌握用SQL語(yǔ)句實(shí)現(xiàn)基本表的創(chuàng)建。熟練掌握用SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)插入、數(shù)據(jù)更新和數(shù)據(jù)查詢。熟練掌握用SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)的簡(jiǎn)單查詢和連接查詢。二、實(shí)驗(yàn)環(huán)境SQLServer2000三、預(yù)習(xí)要求SQLServer2000的查詢分析器和企業(yè)管理器的使用，SQL數(shù)據(jù)定義和數(shù)據(jù)操縱語(yǔ)言。四、實(shí)驗(yàn)內(nèi)容（一）用SQL語(yǔ)句建立如下mySPJ數(shù)據(jù)庫(kù)，包括S,P,J,和SPJ四個(gè)基本表（四張表的數(shù)據(jù)見附錄部分，要求實(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）＞X程項(xiàng)目代碼（JNO）、供應(yīng)數(shù)量（QTY）組成，表示某供應(yīng)商供應(yīng)某種零件給某工程項(xiàng)目的數(shù)量為QTY。（二）分別使用插入、刪除、修改的方式更新基本表中的數(shù)據(jù)。利用Insert語(yǔ)句將習(xí)題中給出的示例記錄插入各表。InsertintoSPJvalues（'s1','p1','j1',200）利用Update更新表中的記錄：將p表中的所有紅色零件的重量增加5。updatePsetWEIGHT=WEIGHT+5whereCOLOR=紅'將spj表中所有天津供應(yīng)商的QTY屬性值減少10。用子查詢。updateSPJsetQTY=QTY-10whereSNO=any（selectSNOfromSwhereCITY=天津'）3.利用Delete語(yǔ)句刪除p表中的所有紅色零件的記錄。deletefromPwhereCOLOR=紅'（一）完成下面的簡(jiǎn)單查詢：查詢所有“天津”的供應(yīng)商明細(xì)；select*fromSwhereCITY='天津’查詢所有“紅色”的14公斤以上的零件。select*fromPwhereCOLOR=紅'andWEIGHT>=14查詢工程名稱中含有“廠”字的工程明細(xì)。select*fromJwhereJNAMElike'%廠%'（二）完成下面的連接查詢：等值連接：求s表和j表的相同城市的等值連接。select*fromJ,SwhereJ.CITY=S.CITY;自然連接：查詢所有的供應(yīng)明細(xì)，要求顯示供應(yīng)商、零件和工程的名稱，并按照供應(yīng)、工程、零件排序。selectS.SNAME,J.JNAME,P.PNAMEfromS,J,P,SPJwhereS.SNO=SPJ.SNOandJ.JNO=SPJ.JNOandP.PNO=SPJ.PNO;笛卡爾積：求s和p表的笛卡爾積select*fromP,S左連接：求j表和spj表的左連接。select*fromJ,SPJwhereJ.JNO*=SPJ.JNO;右連接：求spj表和j表的右連接。select*fromJ,SPJwhereSPJ.JNO=*J.JNO;（三）分組查詢：求各種顏色零件的平均重量。selectCOLOR,AVG（WEIGHT）fromPgroupbyP.COLOR求北京供應(yīng)商和天津供應(yīng)商的總個(gè)數(shù)。selectS.CITY,COUNT（*）fromSgroupbyS.CITY;求各供應(yīng)商供應(yīng)的零件總數(shù)。selectSUM(QTY)fromSPJ;求各供應(yīng)商供應(yīng)給各工程的零件總數(shù)。selectSNO,SUM(QTY)fromSPJgroupbySPJ.SNO;求使用了100個(gè)以上P1零件的工程名稱。selectJ.JNAMEfromJwhereJ.JNOin(selectSPJ.JNOfromSPJwhereSPJ.PNO='p1'andSPJ.QTY>100);求各工程使用的各城市供應(yīng)的零件總數(shù)。selectS.CITY,sum(SPJ.QTY)fromSPJ,SwhereS.SNO=SPJ.SNOgroupbyS.CITY;嵌套查詢：in連接謂詞查詢：查詢沒有使用天津供應(yīng)商供應(yīng)的紅色零件的工程名稱。selectJ.JNAMEfromJwhereJNOin(selectJNOfromSPJ,PwhereJ.CITY!=天津'andP.COLOR=紅')；查詢供應(yīng)了1000個(gè)以上零件的供應(yīng)商名稱。(having)selectJ.JNAMEfromJwhereJ.JNOin(selectSPJ.JNOfromSPJgroupbySPJ.JNOhavingSUM(SPJ.QTY)>1000);比較運(yùn)算符：求重量大于所有零件平均重量的零件名稱。selectP.PNAMEfromPwhereP.WEIGHT>=any(selectavg(WEIGHT)fromP);五、實(shí)驗(yàn)方法和步驟(一)使用MicrosoftSQLServer企業(yè)管理器和查詢分析器建立數(shù)據(jù)庫(kù)mySPJ:1.打開"開始一>程序一>MicrosoftSQLServer—>企業(yè)管理器"；在企業(yè)管理器左邊的樹標(biāo)簽中依次打開“MicrosoftSQLServer—>SQLServer組一>(local)(WindowsNT)—>數(shù)據(jù)庫(kù)"，(local)(WindowsNT)前的紅色標(biāo)記轉(zhuǎn)化為綠色標(biāo)記表明NT服務(wù)已啟動(dòng)；從企業(yè)管理器的“工具”菜單中選擇“SQL查詢分析器”，打開查詢分析器后，在其窗口書寫創(chuàng)建數(shù)據(jù)庫(kù)mySPJ的SQL語(yǔ)句，點(diǎn)擊執(zhí)行按鈕(或F5鍵)執(zhí)行該SQL語(yǔ)句；在企業(yè)管理器左邊的樹標(biāo)簽中查看數(shù)據(jù)庫(kù)是否建立成功。(二)在數(shù)據(jù)庫(kù)mySPJ中建立S，P，J，和SPJ四個(gè)基本表：按照實(shí)驗(yàn)內(nèi)容給出的基本表結(jié)構(gòu)在查詢分析器窗口中書寫SQL語(yǔ)句分別建立各表，并設(shè)置主鍵和外鍵約束；createtables(pnochar10notnull,(snochar10notnull,pnamechar10,snamechar10,colorchar10,stattusint4,weightint4)citychar10)createtablejcreatetablep(jnochar10notnull,

jnomechar10,(snochar10notnull,citychar10)pnochar10notnull,jnochar10notnull,createtablespjqtyint4)（三）更新表數(shù)據(jù)：1.利用Insert語(yǔ)句將習(xí)題中給出的示例記錄插入各表。insertintosvalues（s1,精益，20，天津）;values（s2,盛錫，10，北京）；values（s3,東方紅，30，北京）;values（s4,豐泰盛，20，天津）;values（s5,為民，30，上海）；30，洛陽(yáng)）;values(s6,一拖，rLS津京京謹(jǐn)海陽(yáng)天北北天上洛n-n-_u_u_u-U213233紅盛

益錫方泰民拖

精盛東豐為一234565L_1EESinsertintopvalues（p1,螺母，紅，12）；values（p2,螺栓，綠，17）；values（p3,螺絲刀，藍(lán)，14）；values（p4,螺絲刀，紅，14）；values（p5,凸輪，藍(lán)，40）；values（p6,齒輪，紅，30）；得到如下圖表：prL：=ifTiecolor30insertintojvalues（J1,三建，北京）；values（J2,一汽，長(zhǎng)春）；values（J4，彈簧廠，天津）；values（J5,造船廠，天津）；values（J6，機(jī)車廠，唐山）；values（J7，無(wú)線電廠，常州）;values（J8,半導(dǎo)體廠，南京）;得到如下圖表：irL：dirien三建J2一汽J3彈簧廠J4造船廠J5機(jī)車廠J6無(wú)戲電廠J7半導(dǎo)體廠京春津津山州京北長(zhǎng)天天唐常南1insertintospjvalues（s1,p1，j1，200）；values（s1,p1，j3，100）；values（s1,p1，j4，700）；values（s1,p2，j2，100）；values（s2,p3，j1，400）；values（s2,p3，j2，200）；values（s2,p3，j4，500）；values（s2,p3，j5，400）；values（s2,p5，j1，400）；values（s2,p5，j2，100）；values（s3,p1，j1，200）；values（s3,p3，j1，200）；values(s4,p5,jl,100);values(s4,p6,j3,300);values(s4,p6,j4,200);values(s5,p2,j4,100);values(s5,p3,jl,200);values(s5,p6,j2,200);values(s5,p6,j4,500)；得到如下圖表：snoIpnoIjnoIqtyhiplJl200slplJ3100siplJ4700slp2J2100s2p3Jl400s2p3J2200e2p3J4500e2p3j5400e2p5jl400e2p5j2100s3pljl200s3p3jl200s4p5jl100s4p6J3300s4p6J4200p2J4100p3Jl200s5pBJ2200s5pBJ45002.利用Update更新表中的記錄。將p表中的所有紅色零件的重量增加5。updatepsetweight=weight+5wherecolor='紅'；將spj表中所有天津供應(yīng)商的QTY屬性值減少10。updatespjsetqty=qty-10wheresnoin(selectsnofromswherecity='天津');3.利用Delete語(yǔ)句刪除表中滿足條件的記錄。deletefromspjwherepnoin(selectpnofrompwherecolor='紅')；deletefrompwherecolor='紅'；六、實(shí)驗(yàn)中的