數(shù)據(jù)庫(kù)上機(jī)實(shí)驗(yàn)答案4

數(shù)據(jù)庫(kù)上機(jī)實(shí)驗(yàn)答案4學(xué)號(hào)：姓名：日期：年月日實(shí)驗(yàn)?zāi)康模簩W(xué)習(xí)使用ODBC的方法；體驗(yàn)T-SQL的功能；體驗(yàn)存儲(chǔ)過(guò)程的功能；體驗(yàn)表值函數(shù)、標(biāo)量值函數(shù)的作用；體驗(yàn)ranking等功能。1練習(xí)視圖及連接查詢(xún)。（1）創(chuàng)建一個(gè)視圖，視圖名為viNF，視圖內(nèi)容為selectid,count(*)asnffromfriendsgroupbyid。執(zhí)行成功后，將SQL語(yǔ)句復(fù)制到下方。createviewviNFas(selectid,count(*)asnffromfriendsgroupbyid)（2）基于viNF視圖，查找擁有最多好友的用戶(hù)、最少好友的用戶(hù)。執(zhí)行成功后，將SQL語(yǔ)句復(fù)制到下方。selecttop1*fromviNForderbynfascselecttop1*fromviNForderbynfdesc或者select*fromviNFwherenf=(selectmin(nf)fromviNF)select*fromviNFwherenf=(selectmax(nf)fromviNF)（3）基于users表和viNF視圖進(jìn)行連接查詢(xún)。分別進(jìn)行內(nèi)連接、全外連接、左外連接、右外連接四種操作。執(zhí)行成功后，將SQL語(yǔ)句復(fù)制到下方，并回答：四種結(jié)果表，哪兩個(gè)的結(jié)果是一致的，為什么？select*fromusersinnerjoinviNFonusers.id=viNF.idselect*fromusersfullouterjoinviNFonusers.id=viNF.idselect*fromusersleftouterjoinviNFonusers.id=viNF.idselect*fromusersrightouterjoinviNFonusers.id=viNF.id其中，innerjoin和rightouterjoin結(jié)果一致，都是63條；fullouterjoin和leftouterjoin結(jié)果一致，都是64條。這是因?yàn)閡sers表有64條數(shù)據(jù)，而viNF只有63條數(shù)據(jù)。有一個(gè)id沒(méi)有朋友，因此不在viNF視圖中出現(xiàn)。（4）將題（3）中全外連接保存為一個(gè)新的視圖viUAF。createviewviUAFas(selectusers.*,viNF.nffromusersfullouterjoinviNFonusers.id=viNF.id)21通過(guò)ODBC用Excel打開(kāi)users表。控制面板管理工具數(shù)據(jù)源(ODBC)添加新的數(shù)據(jù)源SQLServerExcel數(shù)據(jù)OLEBODBC3體驗(yàn)T-SQL?；仡檶?shí)驗(yàn)2中的題目：定義最低價(jià)格為成本價(jià)；依據(jù)此成本價(jià)做如下計(jì)算：連接Goods，Goods_Extent，Sellers表，按照總利潤(rùn)，輸出前10名；要求輸出表的格式為（商品名稱(chēng)，賣(mài)家名稱(chēng)，商品價(jià)格，運(yùn)費(fèi)，賣(mài)家信譽(yù)，賣(mài)家好評(píng)率，歷史銷(xiāo)量，歷史利潤(rùn)，期內(nèi)銷(xiāo)量，期內(nèi)利潤(rùn)，總銷(xiāo)量，總利潤(rùn)）利用如下語(yǔ)句進(jìn)行查詢(xún)，體會(huì)和之前有什么不同。如有時(shí)間，自己可以仿照寫(xiě)一個(gè)變量定義、賦值及應(yīng)用的例子。declare@costasfloat;select@cost=min(good_price)fromgoods;selecttop10good_nameas商品名稱(chēng),goods.seller_nameas賣(mài)家名稱(chēng),good_priceas商品價(jià)格,good_shippingas運(yùn)費(fèi),rp_as_selleras賣(mài)家信譽(yù),positive_percent_as_selleras賣(mài)家好評(píng)率,good_soldbeforeas歷史銷(xiāo)量,good_soldbefore*(good_price-@cost)as歷史利潤(rùn),good_soldintermas期內(nèi)銷(xiāo)量,good_soldinterm*(good_price-@cost)as期內(nèi)利潤(rùn),good_soldbefore+good_soldintermas總銷(xiāo)量,(good_soldbefore+good_soldinterm)*(good_price-@cost)as總利潤(rùn)fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id=sellers.seller_idorderby總利潤(rùn)desc;注意：這里用了declare@costasfloat來(lái)聲明變量用了select@cost=min(good_price)fromgoods;來(lái)賦值應(yīng)用時(shí)直接用@cost4體驗(yàn)存儲(chǔ)過(guò)程。執(zhí)行如下語(yǔ)句：CREATEPROCEDUREgetFriendsList@idintASBEGINprint('存儲(chǔ)過(guò)程樣例');selectid,gender,homeprovincefromuserswhereidin(selectfriendIDfromfriendswhereid=@id);END執(zhí)行完畢后，在可編程性存儲(chǔ)過(guò)程下查看自己創(chuàng)建的存儲(chǔ)過(guò)程，并右鍵點(diǎn)擊修改存儲(chǔ)過(guò)程，觀察所展示的代碼與上述代碼的不同之處。在查詢(xún)窗口中執(zhí)行如下語(yǔ)句，理解這一存儲(chǔ)過(guò)程：execgetFriendsList155100422;注意：創(chuàng)建時(shí)為CREATE，修改時(shí)為MODIFY@id為傳入?yún)?shù)執(zhí)行存儲(chǔ)過(guò)程要用exec…5體驗(yàn)存儲(chǔ)過(guò)程的輸入和輸出。執(zhí)行如下語(yǔ)句：CREATEProcedurecal_median@sourceTBnvarchar(100),@sourceColumnnvarchar(100),@sourceConditionsnvarchar(500),@medianfloatoutASBEGINdeclare@sqlnvarchar(1000)declare@median_bottomfloatdeclare@median_topfloatset@sql='SELECT@median_bottom=MAX('+@sourceColumn+')FROM(SELECTTOP50PERCENT'+@sourceColumn+'FROM(select'+@sourceColumn+'from'+@sourceTB+'where'+@sourceConditions+')asvariable_listORDERBY'+@sourceColumn+')ASBottomHalf'execsp_executesql@sql,N'@median_bottomfloatout',@median_bottomoutset@sql='SELECT@median_top=MIN('+@sourceColumn+')FROM(SELECTTOP50PERCENT'+@sourceColumn+'FROM(select'+@sourceColumn+'from'+@sourceTB+'where'+@sourceConditions+')asvariable_listORDERBY'+@sourceColumn+'DESC)ASTopHalf'execsp_executesql@sql,N'@median_topfloatout',@median_topoutset@median=round((@median_bottom+@median_top)*1.0/2,2)END在查詢(xún)窗口中執(zhí)行如下語(yǔ)句，理解這一存儲(chǔ)過(guò)程是如何實(shí)現(xiàn)輸出的：declare@medianfloatexeccal_median'DBCourse.dbo.users','nFriends','homeProvince=''陜西''',@medianoutprint@median注意：這個(gè)存儲(chǔ)過(guò)程用來(lái)計(jì)算中值。計(jì)算的過(guò)程為：前一半的最大值和后一半的最小值的平均值。存儲(chǔ)過(guò)程的輸出用out來(lái)指出。6體驗(yàn)表值函數(shù)。執(zhí)行如下語(yǔ)句：createfunctiongetFriends(@idasint)returns@friendstable(idint,gendervarchar(2),placevarchar(50))asbegininsertinto@friendsselectid,gender,homeprovincefromuserswhereidin(selectfriendIDfromfriendswhereid=@id);return;end執(zhí)行完畢后，在可編程性函數(shù)表值函數(shù)下查看自己創(chuàng)建的表值函數(shù)，并右鍵點(diǎn)擊修改函數(shù)，觀察所展示的代碼與上述代碼的不同之處。在查詢(xún)窗口中執(zhí)行如下語(yǔ)句，理解表值函數(shù)的應(yīng)用方式：select*fromgetFriends(155100422)注意：表值函數(shù)類(lèi)似于創(chuàng)建一張表并插入數(shù)據(jù)。7體驗(yàn)標(biāo)量值函數(shù)。執(zhí)行如下語(yǔ)句：createfunctiongetFriendsNumber(@idasint)returnsintasbegindeclare@nFriendsint;set@nFriends=(selectcount(*)fromfriendswhereid=@id);return@nFriends;end執(zhí)行完畢后，在可編程性函數(shù)標(biāo)量值函數(shù)下查看自己創(chuàng)建的標(biāo)量值函數(shù)，并右鍵點(diǎn)擊修改函數(shù)，觀察所展示的代碼與上述代碼的不同之處。在查詢(xún)窗口中執(zhí)行如下語(yǔ)句，理解標(biāo)量值函數(shù)的應(yīng)用方式：select*,dbo.getFriendsNumber(id)fromusers注：若此處報(bào)錯(cuò)，查看標(biāo)量值函數(shù)的函數(shù)名，對(duì)dbo做相應(yīng)修改。注