SQL-pubs(例子學(xué)習(xí))

1、實(shí)驗(yàn)SQL Server 2000 表的查詢SQL Server 2000系統(tǒng)自帶的Pubs例如數(shù)據(jù)庫為例，以一個(gè)虛擬圖書出版 公司為模型。以下是它的E_R圖模型。1該系統(tǒng)中數(shù)據(jù)庫根本表如下:Authors :屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引au_idId作者編號否是1主鍵aun amevarchar(40)作者姓否au_fnamevarchar(20)作者名否phonechar(12)否addressvarchar(40)地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)是是2con tractBit是否簽約否1au_id CHECK約

2、束定義為：(au_id LIKE '0-90-90-9-0-90-9-0-90-90-90-9')2 zip CHECK約束定義為：(zip LIKE'0-90-90-90-90-9')disco unts屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引disco un ttypevarchar(40)折扣類型否stor_idchar(4)商丿占編號是夕卜鍵 stores(stor_id)lowqtySmalli nt數(shù)量下限是highqtySmalli nt數(shù)量上限是disco untFloat折扣否Employee屬性名數(shù)據(jù)類型含義說明可為空默認(rèn)值檢查鍵/索引emp

3、_idEmpid職工編號否是1主鍵fnamevarchar(20)職工名否mi nitchar(1)是In amevarchar(30)職工姓否job_idSmalli nt工作編號否1夕卜鍵 jobs(job_id)job_lvlTi nyi nt否10pub_idchar(4)編號否'9952'夕卜鍵 publishers(pub_id)Hire_dateDatetime工作日期否GETDATE()CHECK勺束定義為：(emp_id LIKE'A-ZA-ZA-Z1-90-90-90-90-9FM') OR(emp_id LIKE 'A-Z-A-Z1

4、-90-90-90-90-9FM')Jobs屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引job_idSmalli nt工作編號否主鍵job_descvarchar (50)工作描述否minvlTi nyi nt否是1max_lvlTi nyi nt否是2(1) min_lvl CHECK約束定義為(min_lvl >= 10)。(2) max_lvl CHECK 約束定義為(max_lvl <= 250)。pub_ info屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引pub_idchar(4)編號否主鍵，外鍵 publishers(pub_id)logoImage標(biāo)志圖是pr_i

5、nfoText出版信息是Publishers屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引pub_idchar(4)編號否是1主鍵pub_ namevarchar(40)名稱是cityvarchar(20)所在城市是statechar(2)所在州是countryvarchar(30)所在國家是1 pub_id CHECK約束定義為(pub_id = '1756' OR ( pub_id = '1622' OR ( pub_id = '0877' OR ( pub_id = '0736' OR (pub_id = '1389

6、9;) OR ( pub_id LIKE'990-90-0')。roysched屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引title_idTid書編號否外鍵 titles(title_id)lora ngeInt低是hira ngeInt高是royaltyInt是Sales屬性名數(shù)據(jù)類型含義說明可為空鍵/索引stor_idchar(4)商店編號否組合主鍵，聚集索引，外鍵stores(stor_id)ord_numvarchar(20)訂單編碼否組合主鍵，聚集索引ord_dateDatetime訂購日期否qtySmalli nt數(shù)量否paytermsvarchar(12)付款方式否

7、title_idTid書編號否組合主鍵，聚集索引，外鍵titles(title_id)titles屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引title_idTid書編號否主鍵titlevarchar(80)書名否typechar(12)類型否pub_idchar(4)編號是夕卜鍵 publishers (pub_id)priceMoney價(jià)格是advaneeMoney預(yù)付款是royaltyInt版稅是Ytd_salesInt年銷售量是n otesvarchar(200)簡介是pubdateDatetime出版日期是Stores屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引stor_idchar(4)商

8、店編號否主鍵stor_ namevarchar(40)商店名稱是stor_addressvarchar(40)商店地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)是titleauthor屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引audid作者編號否組合主鍵，聚集索引，外鍵authors(aud)title_idtid書編號否組合主鍵，聚集索引，外鍵titles(title id)au_ordtinyint是royaltyperint百分比是、目的要求：掌握表的查詢，學(xué)會(huì)使用查詢語句、容步驟：上機(jī)練習(xí)題翻開每題輸入完都先后執(zhí)行1: SELECT A

9、LL*FROM authors2: SELECT title,price,pubdate FROM titles3:SELECT title_id,title,price*0.8as NewPrice'FROMtitles|t L-tl&ldtNewPriee1BUI 032The Busy Executive1 s Database Guide15.9920C2BUI 111Cooking with Computers: Surrepti9.660003BW2075You Can Comtat Computer St r ess I2. 39200BUT632Straight

10、 Talk About Coonputers15.盹迥sMC2222Silicon Galley G-astronomic Treats15.992006比 3D21The GonrirLedL Microvrave2. 392007MC302eThe Psychology of Camputar CookingNULL8PC HO 35But Is It User Friendly?18.JSOOOgPCS8SSSecrets of Silrcon Valley16.OOOOC10PC&S99Nel Etiquell eNULL|iiFS1372Camputer Fhobic AND

11、 Non-Phobic L 17.272004 : USE pubsSELECT DISTINCT type FROM titles5: USE pubsSELECT au_l name,au_f name,pho ne FROM authors where state='CA't itlepricepub dat &1Thue Busy Executive1 s Database Guide19.9500 1991-05-12 00:00:OC.0002Cooking 甲ith Computers: Suxrepti.11,9500 1991-DS-U9 00:00:

12、00.0003You Can Combat Cciin.p'U'ter St less 100001931-05-30 00:00:00.0001St raight Talk Ab ant Comput ers19. 9900 1991-06-22 00:00: DiO. 0005Sil icon Valley Gast ronojnic Treats19.9900 1991-OS-O9 00:00:00.000JThe (gourmet Microwave2.99C019&1-Q6-IS 00:00:OO.COO1Thue Psychology of Coputer

13、匚cokingNULL2OOO-O3-U0 01：.33:54. 123pBut Is It User Friendly?22,9500 1991-05-30 00:00:00.000Secrets of Silicon Valley20.0000 1094-06-12 00:00:00.00010Net rtique+teNULL2000-09-1)6 01:33:54, 14011CQjnputec Phobic AND UQri-Fhobic 21,5500 1991-10-21 00:00;00.000aii inaneaufnamephoneHVflil-teJohnson.409

14、495-7223G-recnMacjori e415 986-7020_ CarsenCheryl415 54B-T7230r LearyMichael408 288-2428St rsightDean415 834-2919BarinetAbraham415 658-9932DullAnn415 335-712Sell ijLg.lcsljyDullTOT 93&-044DLo clifleyChai: lane415 585-4fi20YokonotsAkika415 935-4228St ringer415 843-29916: USE pubsSELECT title FROM

15、 titles where price<10 AND type= 'busi ness'7 USE pubsSELECT ALL* FROM titles WHERE price BETWEEN 10 AND 20 ;title idtitletyue?ub idrr seeadvsneerovaKy yiiDU1032The Busy Exs-cut ive s DatGuidebusing 31網(wǎng)19.9900000. 00001042BU1K1Cookinr with CDir.putars: Surrepti,. TIjusirLess13891L950D50DL

16、 0000LO33BIJ7B32Straight Tali'： About ComiputerabusmensL38D1PDDOO60DC. 0000104qHC2222Siliccn Valley Gastrononic Treatsnod_cook387719.9900.00001226PC8888Secrets of Silicon. Vilify138520.000090DC. 0000104t)PS20U1Is Anger the Enemy?psychology10.960U2275. 0000122TPS3333?rolonged.a Eepriat 1 ort: Fcu

17、r* 亠psrcKology3736iddoo20 DO. 00001048TC4203Fifty Tears in EuckinghLani PaLscetrad cook- -J97711.95004000. 000014195ushij Anyone?+rad_coot037714.9900汕Ml 00001048: USE pubsSELECT ALL* FROM titles WHERE price<10 or price>20< >Titl6 idtitletypepub.ilpriceadvanceroyalty IEU20T5You Csn Comb i

18、t Conputsr Stress IbusinessJ 7362.9900L012S.0D00242K3021The &curm*t Kierawavemod_cookJ87T2.P900L 5000.0000243rcioasEut氐 Udcf FiieiLtlLy?p upLLl ai138922. 950C7000. 00D0lu4FS13T2Conputer Hhobic ABD Nr.-Phobic 1.psycho LoairETT21. b90L?ooo. oo uu105PS21DCLife Withotrt F±arOOcKoLog3rJ73fi7.0C0

19、0SOIL OODO106FST777Emoticnil Security: A Keij Algori±hjLpsyckolog5r37367.©9004000. OODO107TC3218driLonSj Leeksj and Garlic: Cdoki.t radcook08廠20. 9SOO*000. OODO109: USE pubsSELECT ALL* FROM authors WHEREcity='Oakla nd'ORcity='Covelo'ORcity='Palo Alto'auidau Lnaneauf

20、naiLEphoneaddresscit jsi at t£jlpcozitr aci213-45-B915GrenMarjorie415 9S6-70?n30=J R3rd St.學(xué)41 1OaklandCAM6I81274-5 Q-9 39 LStraightDe胡416 834-2919昶EQ ColLege Av.OaklandCA珈0913427-17-2319ETAmqi5 836-71283410 Blonds St,Palo AltoCA901144T2-27-234?GKinglesbySuriTO" 938-£<H5FO Box 132C

21、oveloCA9M281572H9315tr:neerDirk41G 843-29B15420 Velserarh Av.OaklandCAB46090S724-B0-P39LS Leal lu41E 354 712644 Upland Hid.OHokloridCASMGL2776(-30-7391KarsenLivia415 534-&21S5720 HtAuler St*OaklandCAM609E046-92- 7186Hunt rrShe iyl415 836-71283410 Dlundc Si.Palo A.lt oCA110: USE pubsSELECT ALL* F

22、ROM authors WHERE au_fname LIKE('') ORDERBYau_l name壬-11iau idau liaan&au£nanephoneaddresscityst atezipa472-27-2349Gringlesby血玳707 938-6445P0 Box T9?CoveloCA954 2 S12899-46-2035RingerAnne301 826-C7B267 Seventh. Av.Salt Like 匚ityITT9415213274-S0-S391StraightDean415 S34-29105420 Colle

23、se Av.OaklandCA9400914724-06-5931SIringerDirk415 S43-2991542D Telegraph EOakland.CA94600011 : USE pubsSELECT ALL* FROM authors WHEREauname LIKE('S%') ANDau_fnameLIKE('%e%')au idau lrijneau fnanephoiteaddresscitystat &zipcontrac t1341-22-1782SmithMeander913 843-046210 Mississippi

24、Dr.LaurenceKS66D44o n2274-80-9391St raight>ean415 S34-2&195420 College Av-OaklandCA&4609112 USE pubsSELECT ALL* FROM authors WHERE aun ame LIKE'AK%'13: USE pubsSELECT MAX(price) as 最高價(jià)格，MIN(price) 最低價(jià)格,SUM(price)as 總價(jià) 格 ，AVG(price)as平 均 價(jià) 格FROMtitles園平艸格22-9500Z. 9900236.2000U,766

25、2時(shí)響的行數(shù)為1行)警昔：聚合或碁它元T操作消除了空值。14: USE pubsSELECT COUNT(DISTINCT(type) as 圖書種類 FROM titles15: USE pubsSELECT COUNT(*) AS加利福亞洲作者人數(shù) FROM authors where state='CA'16: USE pubsSELECEtate AS 州名,COUNT(au_id) AS 該州作者人數(shù) FROMauthors GROUBY state17： USE pubsSELECType AS 圖書分類，AVG(price) AS 平均價(jià)格 FROMitles G

26、ROUBYtype18: USE pubsSELECTitle AS 書名,type AS 類型,price AS 單價(jià) FROMitles ORDEBYtype COMPUTEAVG(price)BYtype12書名糞塑單伯The Buy Executive5 s Database GuidebusinessCooking with Computers; Surreptitious.« business19.9900U.S500You Can Combat Computer Stress!business2. 9900qStraight Talk About Computersbu

27、siness13*9900113.73001書名類型單價(jià)Silicon Valley Gastronowiic Treats mod_cack1999002The Gourmat Microwavemod cook9900I1 1.4900)1書名類型 單惜But Is It User Friendly? popular_ccjmp 22.95002Secrets of Silicon Valley popular_comp 0. 00003Net Et iquet t epopular_cojnp NULLavg121.47E01書名類型單價(jià)CQnput&r Phobic AIW H

28、on-Phobic Individuals: psychclcgy21. 59002Is Angftr thfl Enemy?psychology10*9500Cl Gnds 11)Messages三、練習(xí)容目的1:1. 加深對表間關(guān)系的理解。2. 理解數(shù)據(jù)庫中數(shù)據(jù)的查詢方法和應(yīng)用。3. 學(xué)會(huì)各種查詢的異同與相互之間的轉(zhuǎn)換方法。容1：1. 查詢所有作者的作者號、信息SELECT au_id,aun ame,au_fnameFROM authors;2. 查詢所有作者的、作者號信息，并在每個(gè)作者的作者號前面顯示字符串“號："，說明顯示的信息是信息SELECT '號：'a

29、s 號，aud,au_lname,au_fname FROM authors;3. 查詢在CA州的作者和城市SELECT aun ame,au_fname,cityFROM authorsWHERE state='CA：4. 查詢出版日期在1992.1.1-2000.12.31之間的書名和出版日期(查詢1991年出版的書)SELECT title,pubdateFROM titlesWHERE pubdate betwee n '1/1/1992' and '12/31/2000：5. 查詢每個(gè)出版的書SELECT title,pub_ nameFROM tit

30、les,PublishersWHERE titles.pub_id=Publishers.pub_id order by pub_ name;6. 查詢某店銷售某書的數(shù)量SELECT stor_ name,sum(qty)FROM sales,storesWHERE sales.stor_id=stores.stor_id group by stor_ name;7. 查詢有銷售記錄的所有書信息，包括書的編號、書名、類型和價(jià)格SELECT dist inct sales.title_id,title,type,priceFROM sales,titlesWHERE sales.title_id

31、=titles.title_id ;8. 顯示所有的書名無銷售記錄的書也包括在SELECT titleFROM titles ;9. 查詢已銷售書的信息書號、書名、作者等SELECT DISTINCT sales.title_id, titles.title,authors.aunameFROM sales,titles, titleauthor,authorsWHERE sales.title_id=titles.title_id and sales.title_id= titleauthor.title_id and titleauthor.ord=1 and titleauthor.au

32、_id = authors.au_id;10. 查詢所有出版商業(yè)business書籍的的名稱SELECT DISTINCT pub_ nameFROM titles,publishersWHERE titles.pub_id = publishers.pub_id and type='bus in ess'目的2：1. 理解數(shù)據(jù)庫中數(shù)據(jù)的其他查詢方法和應(yīng)用；2. 學(xué)會(huì)各種查詢要求的實(shí)現(xiàn)。容2：在實(shí)驗(yàn)1的根底上，練習(xí)查詢語句的使用，包括計(jì)算列、求和、最大、最小 值、各類選擇條件、字符匹配、分組和排序，體會(huì)各種查詢的執(zhí)行過程，為簡單 綜合應(yīng)用打下良好的根底。1. 查詢書名以T開頭或者號為0877,而且價(jià)格大于16的書的信息。SELECT *FROM titlesWHERE (title LIKE 'T%' OR pub_id='0087') AND PRICE>16;2. 按照類型的升序和價(jià)格的降序在類型一樣時(shí)顯示書的信息書名、 作者、類型、價(jià)格SELECTtitles.title,authors.au_l name,publishers.pub_in ame,titles.type,tit