小项目实训：曹操外卖第四部分

1.SELECT a.prderno,c.title,b.qty,c.unitprice FROM orders a,orderdetails b,goods c WHERE a.orederld=b.orderld and b.goodsld=c.goodsld AND invoicecontent=‘已收货‘;/查询已完成的订单的订单明细，显示订单日期、订购的外卖名称、订购数量、订购单价/2.SELECT ordeprice,custaddress,custphone FROM orders WHERE prderno=‘20110508004‘;/查询出ordeeno为"20110508004"的收货人姓名，地址和电话/3.SELECT COUNT(*)FROM orders WHERE custphone LIKE‘188%‘;/查询出收货人电话中以"188"开头的客户有几人/4.SELECT COUNT(*)FROM orders WHERE custaddress=‘二七广场‘;/查询出收货地址在"二七广场"地区的有几人/5.SELECT custname,custphone, MAX(ordeprice) AS 最高金额 FROM orders;/查询出账单金额最高的订单的收货人姓名电话/6.SELECT custaddress,COUNT(custaddress) as 购物次数 FROM orders GROUP BY custaddress;/在orders表中获取所有的收货地址，以及每个收货地址购物次数/7.SELECT b.custname,b.custaddress,a.description,a.unitprice,c.qty,a.cateld FROM goods a,orders b,orderdetails c WHERE a.goodsld=c.goodsld AND b.orederld=c.orderld AND custaddress=‘北京市和平东路四段32号‘;/请查询出收货地址为"北京书和平路四段32号"的锁钩外卖商品的商品名收货人的姓名、地址、商品信息、价格、数量、商品分类。/8.SELECT b.custname,b.custaddress,a.description,a.unitprice,c.qty,a.cateld FROM goods a,orders b,orderdetails c WHERE a.goodsld=c.goodsld AND b.orederld=c.orderld AND custaddress=‘北京市和平东路四段32号‘ ORDER BY unitprice DESC;/请查询出收货地址为"北京书和平路四段32号"的锁钩外卖商品的商品名收货人的姓名、地址、商品信息、价格、数量、商品分类。并按照外卖商品的单价以降序排列/9.SELECT cateld AS 类别,MAX(unitprice) AS 最高价格 FROM goods ; /请查询出在所有外卖商品中单价最高的外卖商品类别/10.SELECT MAX(a.qty) AS 销量,b.orderdate AS 日期 FROM orderdetails a,orders b WHERE a.goodsld=b.orederld;/请查询出销量（销售金额）最高的一天/11.SELECT COUNT(*) AS 各有几种 FROM goods GROUP BY cateld;/统计注册外卖商品中每个商品类别各有多少种商品/12.SELECT custname,MAX(ordeprice)AS 订单总价 FROM orders;/大客户查询，查询出外卖订单总价最多的客户名/13.SELECT MAX(unitprice)AS 最高价格,MIN(unitprice)AS 最低价格,AVG(unitprice)AS 平均价格 FROM goods;/查询出每个商品类别中外卖商品的最高价格，最低价格和平均价格/