计算机+A班+实验报告4+简单检索数据3

1、中山丸學新華摩院XINHUA COLLEGE OF SUN YAT SBN UNIVERSITY实验报告（2018-20佃学年第二学期）课程名称：数据库系统理论与实践学系：信息科学学院专业：计算机科学与技术学生姓名：陈锐楷学号：17132045班级：A 组实训名称第XX次实验（第XX周星期XX）指导教师王辰尹知识点:十一、使用数据处理函数；十二、汇总数据；十三、分组数据实验步骤数据准备:mysql SELECT COUNT(*) AS numjprods- FROM products- UHERE vend id = 1003:执行SQL文+num_pr0dsHh表，导入数据（先创建或者调用数

2、+1 row in set (0. 00 sec)据库，然后create.sq 文件创建表的结构，然后执行populate.sql向表中插入数据；）语法：source文件路径;表一：产品表，结构如下:products(prod_id,vend_id,prod_name,prod_price,prod_desc)其中:prodd代表产品IDmysql SELECT COUNT(*) AS nuEiiitenis, MIN(prod_price) AS price_min, - MAX(prod_prlce) AS price_max AVG(prod_price) AS price_avg -

3、FROM products;i-price_minnum itemsprice_maxprice_avgven142.5055. 0016. 1335711 row in set (0.00 sec)品供应商ID，prod name代表产品名称,prod_price代表产品价格, prod desc代表产品扌田述。具体数据如下:prtxl idvend idprod iiDieprod priceprod血昶ANVOiANV02ANV03 DTXTR I FBFCI FU1JFIOOO jP2ono 0L1 SAFESLIXGTNT II TNT211133325523333 刖知000000

4、0000刖刖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 romplete wi

5、th 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 can,

6、redSafe with combination locklinE one size fits allTNT, red, single stickTNT, red, pack of 10 sticks表二：供应商表，结构如下:vendors(vendd,vend_name,vend_address,vend_city,vend_state,vend_zip,vend_cou)；其中vend id代表供应商ID,vend name代表供应商名称,vend address代表供应商地址,vend_city代表供应商的城市,vend state代表供应商的州,vend zip代表供应商的邮政编码,v

7、end_country代表供应商的国豕。具体数据如下: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) AS ve

8、nd_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日期和时间处理函数表12常用日期和时间处理函数函数说明AddDatef)增加一个日期（天.周等）AddTime()增加一个时间（时

9、、分等）CurDate()返回当前日期CurTime()返回当前时间Date()返回日期时间的日期部分DateDiff()计算两个日期Z差DateAddf)高度灵活的日期运算函数DateFormat()返回一个格式化的日期或时间串oay()返回一个日期的天数部分DayOfWeekf)对于一个日期，返回对应的星期几Hour()返回一个时间的小时部分Minute()返回一个时间的分钟部分Month ()返回一个日期的月份部分Now()返回当前日期和时间Second()返回一个时间的秒部分Tiuief)返回一个日期时间的时间部分Year()返回一个日期的年粉部分注意：应该总是用4位数的年份，日期格式

10、为yyyy-mm-dd;例：检索订单日期为20069-01的订单号和客户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月的订单号和客户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数值处理函数表1仁3常用数值处理函数函数说明Abs()返回一个数的绝对值Cos()返回一个角度的余弦Expt)返回一个数的捋数值Mod()返回除操作的余数Pi()返回圆周率Rand()返回一个随机数Sin ()返回一个角度的正弦Sqrt()返冋一个数的平方根Tan()返回一个角度的正切第12章汇总数据12.1聚集函数确定表中行数（或者满足某个条件或包含某个特定值的行数）； 获得表中行组的和；找出表列（或所有行或某些特定的

12、行）的最大值、最小值和平均值。a 12-1 SQL聚集函数函数说明AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的疑大值MIN()返回某列的最小值SUM()返回某列值之和注意：聚集函数括号里一般为1个列名，或者一个计算表达式;12.1.1 A/G（）函数例：查询products表中所有产品的平均价格；例：查询products表中由供应商1003所提供的所有产品的平均价格；njysql SELECT AVG (prod_price) AS avg_priceFROM products- WHERE vendid 二 1003;+1+b| 13.212857 |+F1 匸

13、口何 in set (0+ 00 sec)12.1.2 COUNT ()函数使用COUNT(*)对表中行的数目进行计数，不管列中包含的是空值(NULL)还是非空值;1使用COUNT(COLUMN)对特定列中具有值的行进行计数，忽略 NULL值;例子：查询customers表中客户的总数;niysql SELECT COUNT (+) AS num_cust - FROM customers;Fnuiri_cust 1T5 1L1 row in set (0+ 00 sec)例子：查询customers表中有电子邮件地址客户的总数;num custmysql SELECT COUNT (cust

14、_einail) AS - FROM customers;+num.丄H_cust十-113 1+F1 row in set (CL 00 sec)12.1.3 MAX ()函数例子：查询products表中产品的最高价格；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()函数例子：查询products表中产品的最低价格；nysqlSELECT fIN(prod_j)rice) AS min_price FRO

15、M products;mirL_pricc 501 roc4 in set (0. 00 sec)12.1.5 SUM()函数例子：查询orderitems表中订单号为20005所订购物品的总数;例子：查询orderitems表中订单号为20005所订购物品的总数；12.2聚集不同值注意：使用聚集函数时使用 DISTINCT时结果不一样；若添加 DISTINCT在列名前则 表示取消重复行再进行计算；jnysql SELECT AVG(DISTINCT prodjrice) AS avg_price- FROM products- WHERE vend_id = 1003：+-avg_price

16、H+| 15.998000 |k+|1 row in set (0. 01 sec)12.3组合聚集函数例子：在products表中查询产品的总数量，平均价格，最高价格，最低价格；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 |卜-11

17、H-r16. 133571卜一一-+1 row in set (0. 00 sec)第13章分组数据13.1数据分组例：查询供应商1003提供的产品数目；mysql SELECT COUNT(*) AS num_prods - FROM products- WHERE vend.id = 1003;num_prods+|7 |Hk1 row in set (0* 00 sec)13.2创建分组语法：GROUP BY例：查询每个供应商及其供应的产品数目；|mysql SELECT 吕COUNT (*) AS num_prods - FROM products- GROUP BY vEnd-id:

18、vend_idnum_prodsI1001I3 I1002910031|1005+2 4 rows m set (0. 00 sec)13.3过滤分组语法：GROUP BYHAVING COUNT(*) 操作符 数值；例子：在orders表中查询有两条及以上订单记录的客户 id和其对应的订单数量;imysq.； ShLhCl cust-id, LULA 11料 AS orders -)FROM orders- GROUP BY cust_id- HAVING COHT(*) X 2;4 一 + +cust_id orders100011 row in set (C, 00 sec)例子：在pr

19、oducts表中查询供应2个(含)以上、价格为10 (含)以上的产品的供应 商；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表中查询供应2个(含)以上产品的供应商;mysql SELECT vend_id, COUNT(*) AS num_prods FROM pro

20、ducts- GROUP BY vend.id- HAVING COUNT(*) = 2:vend_id1 - 1卜十num_prodsL-J1001100210031005r-t3宀704 rows in set (0,00 sec)13.4分组和排序查询：在orderitems中查询总计订单价格大于等于 50的订单的订单号和总计订单价格;ILiysql SELECT ordernum, SUH(quan tity * item_price) AS order to tai- FROM ordcritons- GROUP BY order_num HArIXG SCM(quantity *

21、iteiD_price)JJL50;TTorder_num|-Lordertotal20005 I149.87 I2000655.00200071000. 0020008JLI125.00 |4 rovs in set(0.01 sec)查询：在orderitems中查询总计订单价格大于等于 50的订单的订单号和总计订单价格, 并按订单价格升序输出；Imysql SELECT order num, SUM (quantity * item price) AS order- FROM orderitems- GROUP BY order_num-HAVING SUM(quantity * ite

22、m_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子句及其顺序子旬说阴是否必须便用SELECT要返回的列或表达式是FROM从中检索数据的表仅在从表选择数据时便用WHERE行级过滤否（续）子旬说明是否必须使用GROUP BY分组说明仅在按组计算聚集时使用HAVING组级过滤否ORDEF 8Y输出排序顺序否LIMIT要检索的行

23、数否加分练习题:1在products表中查询价格最高的产品;2、在orderitems表中查询所有订单的平均价格，最高价格，最带价格，以及订单数量;円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. E0 |

24、11 |L rw In set (0.02 eec)3、 在 ve nodrs 表中返回每个供应商名称的前 3个字符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表中返回每个供应商名称的长度；rsal SELECT LENGTH(vend_name)- FR01 vendors;+ TI L

25、ENGTH Wend. ”name |4b11 |11 |4 |12 |7 |141+F6 rows in set 0 01 sec)5、 在 orders 表中查询在 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 表中查询在2005年10月下的订单的数量，有多少个？；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月的订单数量； nysql SELECT lonth order_datej h COUNT cust_id