SQL-pubs(例子学习)

1、实验SQL Server 2000 表的查询SQL Server 2000系统自带的Pubs例如数据库为例，以一个虚拟图书出版 公司为模型。以下是它的E_R图模型。1该系统中数据库根本表如下:Authors :属性名数据类型含义说明可为空检查键/索引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属性名数据类型含义说明可为空检查键/索引disco un ttypevarchar(40)折扣类型否stor_idchar(4)商丿占编号是夕卜键 stores(stor_id)lowqtySmalli nt数量下限是highqtySmalli nt数量上限是disco untFloat折扣否Employee属性名数据类型含义说明可为空默认值检查键/索引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属性名数据类型含义说明可为空检查键/索引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属性名数据类型含义说明可为空检查键/索引pub_idchar(4)编号否主键，外键 publishers(pub_id)logoImage标志图是pr_i

5、nfoText出版信息是Publishers属性名数据类型含义说明可为空检查键/索引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属性名数据类型含义说明可为空检查键/索引title_idTid书编号否外键 titles(title_id)lora ngeInt低是hira ngeInt高是royaltyInt是Sales属性名数据类型含义说明可为空键/索引stor_idchar(4)商店编号否组合主键，聚集索引，外键stores(stor_id)ord_numvarchar(20)订单编码否组合主键，聚集索引ord_dateDatetime订购日期否qtySmalli nt数量否paytermsvarchar(12)付款方式否

7、title_idTid书编号否组合主键，聚集索引，外键titles(title_id)titles属性名数据类型含义说明可为空检查键/索引title_idTid书编号否主键titlevarchar(80)书名否typechar(12)类型否pub_idchar(4)编号是夕卜键 publishers (pub_id)priceMoney价格是advaneeMoney预付款是royaltyInt版税是Ytd_salesInt年销售量是n otesvarchar(200)简介是pubdateDatetime出版日期是Stores属性名数据类型含义说明可为空检查键/索引stor_idchar(4)商

8、店编号否主键stor_ namevarchar(40)商店名称是stor_addressvarchar(40)商店地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)是titleauthor属性名数据类型含义说明可为空检查键/索引audid作者编号否组合主键，聚集索引，外键authors(aud)title_idtid书编号否组合主键，聚集索引，外键titles(title id)au_ordtinyint是royaltyperint百分比是、目的要求：掌握表的查询，学会使用查询语句、容步骤：上机练习题翻开每题输入完都先后执行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网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.学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 最高价格，MIN(price) 最低价格,SUM(price)as 总价 格 ，AVG(price)as平 均 价 格FROMtitles園平艸格22-9500Z. 9900236.2000U,766

25、2時响的行数为1行)警昔：聚合或碁它元T操作消除了空值。14: USE pubsSELECT COUNT(DISTINCT(type) as 图书种类 FROM titles15: USE pubsSELECT COUNT(*) AS加利福亚洲作者人数 FROM authors where state='CA'16: USE pubsSELECEtate AS 州名,COUNT(au_id) AS 该州作者人数 FROMauthors GROUBY state17： USE pubsSELECType AS 图书分类，AVG(price) AS 平均价格 FROMitles G

26、ROUBYtype18: USE pubsSELECTitle AS 书名,type AS 类型,price AS 单价 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书名类型单价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书名类型单价CQnput&r Phobic AIW H

28、on-Phobic Individuals: psychclcgy21. 59002Is Angftr thfl Enemy?psychology10*9500Cl Gnds 11)Messages三、练习容目的1:1. 加深对表间关系的理解。2. 理解数据库中数据的查询方法和应用。3. 学会各种查询的异同与相互之间的转换方法。容1：1. 查询所有作者的作者号、信息SELECT au_id,aun ame,au_fnameFROM authors;2. 查询所有作者的、作者号信息，并在每个作者的作者号前面显示字符串“号："，说明显示的信息是信息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. 查询每个出版的书SELECT title,pub_ nameFROM tit

30、les,PublishersWHERE titles.pub_id=Publishers.pub_id order by pub_ name;6. 查询某店销售某书的数量SELECT stor_ name,sum(qty)FROM sales,storesWHERE sales.stor_id=stores.stor_id group by stor_ name;7. 查询有销售记录的所有书信息，包括书的编号、书名、类型和价格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. 查询所有出版商业business书籍的的名称SELECT DISTINCT pub_ nameFROM titles,publishersWHERE titles.pub_id = publishers.pub_id and type='bus in ess'目的2：1. 理解数据库中数据的其他查询方法和应用；2. 学会各种查询要求的实现。容2：在实验1的根底上，练习查询语句的使用，包括计算列、求和、最大、最小 值、各类选择条件、字符匹配、分组和排序，体会各种查询的执行过程，为简单 综合应用打下良好的根底。1. 查询书名以T开头或者号为0877,而且价格大于16的书的信息。SELECT *FROM titlesWHERE (title LIKE 'T%' OR pub_id='0087') AND PRICE>16;2. 按照类型的升序和价格的降序在类型一样时显示书的信息书名、 作者、类型、价格SELECTtitles.title,authors.au_l name,publishers.pub_in ame,titles.type,tit