計(jì)算機(jī)+A班+試驗(yàn)報(bào)告4+簡(jiǎn)單檢索數(shù)據(jù)3

1、中山丸學(xué)新華摩院XINHUA COLLEGE OF SUN YAT SBN UNIVERSITY實(shí)驗(yàn)報(bào)告（2018-20佃學(xué)年第二學(xué)期）課程名稱：數(shù)據(jù)庫(kù)系統(tǒng)理論與實(shí)踐學(xué)系：信息科學(xué)學(xué)院專業(yè)：計(jì)算機(jī)科學(xué)與技術(shù)學(xué)生姓名：陳銳楷學(xué)號(hào)：17132045班級(jí)：A 組實(shí)訓(xùn)名稱第XX次實(shí)驗(yàn)（第XX周星期XX）指導(dǎo)教師王辰尹知識(shí)點(diǎn):十一、使用數(shù)據(jù)處理函數(shù)；十二、匯總數(shù)據(jù)；十三、分組數(shù)據(jù)實(shí)驗(yàn)步驟數(shù)據(jù)準(zhǔn)備:mysql SELECT COUNT(*) AS numjprods- FROM products- UHERE vend id = 1003:執(zhí)行SQL文+num_pr0dsHh表，導(dǎo)入數(shù)據(jù)（先創(chuàng)建或者調(diào)用數(shù)

2、+1 row in set (0. 00 sec)據(jù)庫(kù)，然后create.sq 文件創(chuàng)建表的結(jié)構(gòu)，然后執(zhí)行populate.sql向表中插入數(shù)據(jù)；） 語(yǔ)法：source文件路徑;表一：產(chǎn)品表，結(jié)構(gòu)如下:products(prod_id,vend_id,prod_name,prod_price,prod_desc)其中: prodd代表產(chǎn)品ID23mysql SELECT COUNT(*) AS nuEiiitenis, MIN(prod_price) AS price_min, - MAX(prod_prlce) AS price_max AVG(prod_price) AS price_av

3、g - FROM products;i-price_minnum itemsprice_maxprice_avg142.5055. 0016. 133571ven1 row in set (0.00 sec)品供應(yīng)商ID，prod name代表產(chǎn)品名稱,prod_price代表產(chǎn)品價(jià)格, prod desc代表產(chǎn)品扌田述。具體數(shù)據(jù)如下:prtxl idvend idprod iiDieprod priceprod血昶ANVOiANV02ANV03 DTXTR I FBFCI FU1JFIOOO jP2ono 0L1 SAFESLIXGTNT II TNT211133325523333 刖知00

4、00000000刖刖00000000 IL 1L 11 11 41 1 11 IL IL 1L 11 11 JI 1.弓 ton anvi11 ton anvil2 ton anvilDetonator Bird seed CarrotsFussJetPath 1000 JetPaek 帥(Hi Oil canSafeSlingTNT (1 stick)TNT (5 sticks)船船舲000050蛇00加船.00.49.008Boa4.2.0.h S ton anvi Ir hUrkr rcwipletp with Imndy hx)k1 Ton anvi I, blacks romplet

5、e with handy hook and cairy ing2 ton anvil, black* complete with handy hook and carrying case Detonator (plunger powered), fuses not includedLarge bug (suitable for road runners)Carrols (rabbit hunting season only)L doten, extra longJetPaek 1000, intended for single useJetPack 200Or ffulTi_iiseOil c

6、an, redSafe with combination locklinE one size fits allTNT, red, single stickTNT, red, pack of 10 sticks表二：供應(yīng)商表，結(jié)構(gòu)如下:vendors(vendd,vend_name,vend_address,vend_city,vend_state,vend_zip,vend_cou)；其中vend id代表供應(yīng)商ID,vend name代表供應(yīng)商名稱,vend address代表供應(yīng)商地址,vend_city代表供應(yīng)商的城市,vend state代表供應(yīng)商的州,vend zip代表供應(yīng)商的郵政

7、編碼,vend_country代表供應(yīng)商的國(guó)豕。具體數(shù)據(jù)如下:vcnd_idvcricj_iMmcvcnci_afld 心劣vcnd_c ilySt fil Cvcnd_zipcoun I r y1U0110021003100410051006AnviIs R UsLT SuppliesACMEIarbal I Inc,Jet SetJouet Ei Ours123 Main Street500 Park Street 565 High Street )000 5th Avenue42 Galaxy RoadL Ru SELECT vend-name, Upper (vend_naine) A

8、S vend_name_upcase- FROM vendors- ORDER BY vend_name:+kbveni_naine|vend_name_upcas e+bhACMEAnvils R Us Furball Inc.Jet SetJouets Et OursLT SuppliesACNIEANVILS R US FURBALL INC.JET SETJOUETS ET OURS LT SUPPLIES6 rows in set (0.02 sec)11.2.1日期和時(shí)間處理函數(shù)表12常用日期和時(shí)間處理函數(shù)函數(shù)說(shuō)明AddDatef)增加一個(gè)日期（天.周等）AddTime()增加一個(gè)

9、時(shí)間（時(shí)、分等）CurDate()返回當(dāng)前日期CurTime()返回當(dāng)前時(shí)間Date()返回日期時(shí)間的日期部分DateDiff()計(jì)算兩個(gè)日期Z差DateAddf)高度靈活的日期運(yùn)算函數(shù)DateFormat()返回一個(gè)格式化的日期或時(shí)間串oay()返回一個(gè)日期的天數(shù)部分DayOfWeekf)對(duì)于一個(gè)日期，返回對(duì)應(yīng)的星期幾Hour()返回一個(gè)時(shí)間的小時(shí)部分Minute()返回一個(gè)時(shí)間的分鐘部分Month ()返回一個(gè)日期的月份部分Now()返回當(dāng)前日期和時(shí)間Second()返回一個(gè)時(shí)間的秒部分Tiuief)返回一個(gè)日期時(shí)間的時(shí)間部分Year()返回一個(gè)日期的年粉部分注意：應(yīng)該總是用4位數(shù)的年份，

10、日期格式為yyyy-mm-dd;例：檢索訂單日期為20069-01的訂單號(hào)和客戶id;mysql SELECT custidj- FROM ordersorderimm- WHERE order_date = * 2005-09-01+-icust_id卜+order_numj 10001+Tr200051 F1 row in set (0. 00 sec)例：檢索訂單日期為2005年9月的訂單號(hào)和客戶id ；SELECT cust_id, order_nuipFROM orders- WHERE Datc(ordcr_date) BETWEEN 2005-9-1 AND 2005-9-30;

11、+11 cust_id1Porder_nuro11111000112000510003200061000420007+-卜3 rows in set (0, 00 sec)11.2.3數(shù)值處理函數(shù)表1仁3常用數(shù)值處理函數(shù)函數(shù)說(shuō)明Abs()返回一個(gè)數(shù)的絕對(duì)值Cos()返回一個(gè)角度的余弦Expt)返回一個(gè)數(shù)的捋數(shù)值Mod()返回除操作的余數(shù)Pi()返回圓周率Rand()返回一個(gè)隨機(jī)數(shù)Sin ()返回一個(gè)角度的正弦Sqrt()返冋一個(gè)數(shù)的平方根Tan()返回一個(gè)角度的正切第12章匯總數(shù)據(jù)12.1聚集函數(shù)確定表中行數(shù)（或者滿足某個(gè)條件或包含某個(gè)特定值的行數(shù)）； 獲得表中行組的和；找出表列（或所有行或某

12、些特定的行）的最大值、最小值和平均值。a 12-1 SQL聚集函數(shù)函數(shù)說(shuō)明AVG()返回某列的平均值COUNT()返回某列的行數(shù)MAX()返回某列的疑大值MIN()返回某列的最小值SUM()返回某列值之和注意：聚集函數(shù)括號(hào)里一般為1個(gè)列名，或者一個(gè)計(jì)算表達(dá)式;12.1.1 A/G（）函數(shù)例：查詢products表中所有產(chǎn)品的平均價(jià)格；例：查詢products表中由供應(yīng)商1003所提供的所有產(chǎn)品的平均價(jià)格；njysql SELECT AVG (prod_price) AS avg_priceFROM products- WHERE vendid 二 1003;+1+b| 13.212857 |+

13、F1 匸口何 in set (0+ 00 sec)12.1.2 COUNT ()函數(shù)使用COUNT(*)對(duì)表中行的數(shù)目進(jìn)行計(jì)數(shù)，不管列中包含的是空值(NULL)還是非空值;1使用COUNT(COLUMN)對(duì)特定列中具有值的行進(jìn)行計(jì)數(shù)，忽略 NULL值;例子：查詢customers表中客戶的總數(shù);niysql SELECT COUNT (+) AS num_cust - FROM customers;Fnuiri_cust 1T5 1L1 row in set (0+ 00 sec)例子：查詢customers表中有電子郵件地址客戶的總數(shù);num custmysql SELECT COUNT (

14、cust_einail) AS - FROM customers;+num.丄H_cust十-113 1+F1 row in set (CL 00 sec)12.1.3 MAX ()函數(shù)例子：查詢products表中產(chǎn)品的最高價(jià)格；nrysql SELECT MAX (prod_price) AS ipax_price- FROI products;Finax_price4F |5反 00 |F1 row in set (0 00 sec)12.1.4 MIN()函數(shù)例子：查詢products表中產(chǎn)品的最低價(jià)格；nysqlSELECT fIN(prod_j)rice) AS min_price

15、 FROM products;mirL_pricc 501 roc4 in set (0. 00 sec)12.1.5 SUM()函數(shù)例子：查詢orderitems表中訂單號(hào)為20005所訂購(gòu)物品的總數(shù);例子：查詢orderitems表中訂單號(hào)為20005所訂購(gòu)物品的總數(shù)；12.2聚集不同值注意：使用聚集函數(shù)時(shí)使用 DISTINCT時(shí)結(jié)果不一樣；若添加 DISTINCT在列名前則 表示取消重復(fù)行再進(jìn)行計(jì)算；jnysql SELECT AVG(DISTINCT prodjrice) AS avg_price- FROM products- WHERE vend_id = 1003：+-avg_p

16、riceH+| 15.998000 |k+|1 row in set (0. 01 sec)12.3組合聚集函數(shù)例子：在products表中查詢產(chǎn)品的總數(shù)量，平均價(jià)格，最高價(jià)格，最低價(jià)格；mysql SELECT COUNT 州 AS num itens, MIN (piod_price) AS pricmin, - LX(prod_price) AS price nax, AVG (prod_price) AS price_avg - FROM products;rnumi terns卜rpricemin|rpricemas卜priceavg1nI14十ii 丁n2. 50 155.00 |

17、卜-11H-r16. 133571卜一一-+1 row in set (0. 00 sec)第13章分組數(shù)據(jù)13.1數(shù)據(jù)分組例：查詢供應(yīng)商1003提供的產(chǎn)品數(shù)目；mysql SELECT COUNT(*) AS num_prods - FROM products- WHERE vend.id = 1003;num_prods+|7 |Hk1 row in set (0* 00 sec)13.2創(chuàng)建分組語(yǔ)法：GROUP BY例：查詢每個(gè)供應(yīng)商及其供應(yīng)的產(chǎn)品數(shù)目；|mysql SELECT 呂COUNT (*) AS num_prods - FROM products- GROUP BY vEnd

18、-id:vend_idnum_prodsI1001I3 I1002910031|1005+2 4 rows m set (0. 00 sec)13.3過(guò)濾分組語(yǔ)法：GROUP BYHAVING COUNT(*) 操作符 數(shù)值；例子：在orders表中查詢有兩條及以上訂單記錄的客戶 id和其對(duì)應(yīng)的訂單數(shù)量;imysq.； ShLhCl cust-id, LULA 11料 AS orders -)FROM orders- GROUP BY cust_id- HAVING COHT(*) X 2;4 一 + +cust_id orders10001 1 row in set (C, 00 sec)例

19、子：在products表中查詢供應(yīng)2個(gè)(含)以上、價(jià)格為10 (含)以上的產(chǎn)品的供應(yīng) 商；nysql SELECT vend_id, COUNT(* AS num_prods-FROM products-WHERE prodjprice = 10S GROUP BY vend_id- HAVING COUNT(*) = 2：+彳vend_idij+num_prods10031005+1h+2 rows in sit (0+ 00 sec)例子：在products表中查詢供應(yīng)2個(gè)(含)以上產(chǎn)品的供應(yīng)商;mysql SELECT vend_id, COUNT(*) AS num_prods FRO

20、M products- GROUP BY vend.id- HAVING COUNT(*) = 2:vend_id1 - 1卜十num_prodsL-J1001100210031005r-t3宀704 rows in set (0,00 sec)13.4分組和排序查詢：在orderitems中查詢總計(jì)訂單價(jià)格大于等于 50的訂單的訂單號(hào)和總計(jì)訂單價(jià)格;ILiysql SELECT ordernum, SUH(quan tity * item_price) AS order to tai- FROM ordcritons- GROUP BY order_num HArIXG SCM(quanti

21、ty * iteiD_price)JJL50;TTorder_num|-Lordertotal20005 I149.87 I2000655.00200071000. 0020008JLI125.00 |4 rovs in set(0.01 sec)查詢：在orderitems中查詢總計(jì)訂單價(jià)格大于等于 50的訂單的訂單號(hào)和總計(jì)訂單價(jià)格, 并按訂單價(jià)格升序輸出；Imysql SELECT order num, SUM (quantity * item price) AS order- FROM orderitems- GROUP BY order_num-HAVING SUM(quantity

22、* item_price) = 50-ORDER BY ordertotal;+1-+order_num ordertotal4Hi|-I2000655.0020008125.0020005149.87200071000.00JJL4 rows in set (0. 00 sec)13.5 SELECT子句順序表13-2 SELECT子句及其順序子旬說(shuō)陰是否必須便用SELECT要返回的列或表達(dá)式是FROM從中檢索數(shù)據(jù)的表僅在從表選擇數(shù)據(jù)時(shí)便用WHERE行級(jí)過(guò)濾否（續(xù)）子旬說(shuō)明是否必須使用GROUP BY分組說(shuō)明僅在按組計(jì)算聚集時(shí)使用HAVING組級(jí)過(guò)濾否ORDEF 8Y輸出排序順序否LIMIT

23、要檢索的行數(shù)否加分練習(xí)題:1在products表中查詢價(jià)格最高的產(chǎn)品;2、在orderitems表中查詢所有訂單的平均價(jià)格，最高價(jià)格，最帶價(jià)格，以及訂單數(shù)量;円Kq】SELECT AVQ Ci teruri cc)幡 pr譏匚US priUu；TIN(iteiiLprlce)AS prlc_Eiit COQXT Citsurf cc)AS Erfymcit - FROI arderiietns.:if1-PI pil匚已_刖已 I price_nrax | pricc_mi口 | pri匚已一Dauivt |W = = = = - 1 !1, 11.| 12.904&45 |5B.00 |2.

24、 E0 |11 |L rw In set (0.02 eec)3、 在 ve nodrs 表中返回每個(gè)供應(yīng)商名稱的前 3個(gè)字符W W % 4J77 7* H A& W. W W MX 丄竺晉nysql SELECT SUBSTRING (vend_naiDe, 1, 3)-FROM vendors;+,i!FI SUBSTRING (, 1, 3)|AnvLTACN FurJet Jon6 rows in set (0.00 sec)4、 在ven odrs表中返回每個(gè)供應(yīng)商名稱的長(zhǎng)度；rsal SELECT LENGTH(vend_name)- FR01 vendors;+

25、 TI LENGTH Wend. ”name |4b11 |11 |4 |12 |7 |141+F6 rows in set 0 01 sec)5、 在 orders 表中查詢?cè)?2005 年 10 月下的所有訂單；mysql SELECT cust_i iorder_numr FROM orders- WHERE Date(arder_date) BETWEEN * 2005-10-1* AND J 2005-10-31* ;hcust_idorderunt110005200031000120009k2 rows in set (0. 00 sec)6、在orders 表中查詢?cè)?005年10月下的訂單的數(shù)量，有多少個(gè)？；inysql SELECT COUNT(cus13d)- FROM orders- WHERE Date (order, date) BETWEEN 2OO5T0-1” AND 辺005-1031；I COUNT(cust_id) |+卜I2 | 1 row in set (0. 00 sec)7、在orders 表中查詢分別在2005年9月和2005年10月的訂單數(shù)量； nysql SELECT lonth order_datej h COUNT cust_i