曹操外卖实现功能

/*最新外卖显示*/SELECT*FROM orders ORDER BY OrderDate DESC LIMIT 3;
SELECT OrederID FROM orders ORDER BY OrderDate DESC LIMIT 3
SELECT OrederID FROM (SELECT OrederID FROM orders ORDER BY OrderDate DESC LIMIT 3)as t
SELECT goodsid FROM orderdetails WHERE orderid in(SELECT orderid FROM (SELECT orderid FROM orders ORDER BY OrderDate DESC LIMIT 3)as t)
SELECT*FROM goods WHERE goodsid in(SELECT goodsid FROM orderdetails WHERE orderid in(SELECT orederid FROM(SELECT orederid FROM orders ORDER BY OrderDate DESC LIMIT 3)as t))
/*畅销外卖显示*/
SELECT a.title,a.unitprice,a.discount,a.description
FROM goods a,orderdetails b
WHERE a.goodsid=b.goodsid and qty in(SELECT max(qty) FROM orderdetails);
/*活动外卖显示*/
SELECT*FROM goods WHERE Discount NOT IN (1) ORDER BY Discount ASC;
/*按照某关键字与外卖标题的匹配查询外卖信息并显示*/
SELECT*FROM goods WHERE Title LIKE '%汤%' AND Description LIKE '%辣%';
/*按照外卖类型分类显示外卖信息*/
SELECT Title,UnitPrice,Discount FROM goods GROUP BY title;
/*用户点击某一个外卖,显示该外卖的详细信息*/
SELECT*FROM goods WHERE GoodsId=3;
/*用户可以选择自己喜欢的外卖,放入购物车(加入字段likel,喜欢的显示为1)*/
UPDATE goods SET likel='1' WHERE GoodsId in(1,3);
/*用户可以对购物车中的外卖下订单,下订单前需要先登录*/
UPDATE customers SET logstart='已登录' WHERE CustName='小黑' and LoginPwd=123456;
/*用户登录后可以查询自己的订单的情况,包括是否发货,商品在途情况,商品签收情况*/
SELECT a.custname,b.orderstatus FROM customers a,orders b WHERE a.custid=b.custid and a.custname='小紫';

/*外卖基本信息录入*/
INSERT INTO goods(title,unitprice,packingexpense) VALUE('大盘鸡','25.00','2');
/*外卖信息修改*/
UPDATE goods SET description='酸辣' WHERE goodsid=3;
/*外卖销售折扣设置*/
UPDATE goods SET discount=0.9 WHERE goodsid=3;
/*订单发货*/
SELECT*FROM orders WHERE orderstatus='已付款';
/*订单信息查询*/
SELECT*FROM orders WHERE orderno=1010205;
/*用户账户充值*/
UPDATE customers SET Accout=1000 WHERE custname='小红';
/*用户密码修改*/
UPDATE customers SET LoginPwd='965636' WHERE custname='小黑';

/*前台客户注册*/
INSERT INTO customers(CustID,CustName,LoginPwd,phone,Email,address)
VALUES('28','Tom','123456','010-60257566','tom@hotmail.com','北京市海淀区苏州街18号维亚大厦12楼');

/*查询所有外卖商品信息*/
SELECT*FROM goods;
/*查询'川湘菜'类型的外卖信息*/
SELECT*FROM goods WHERE CateId IN(SELECT CateId FROM category WHERE CateName='川湘菜');
/*查询出所有包含'大盘鸡'标题的外卖商品*/
SELECT*FROM goods WHERE Title LIKE '%大盘鸡%';
/*查询评分为5并且描述中含有麻辣内容的外卖商品*/
SELECT*FROM goods WHERE Roat=5 and Description='微辣';
/*查询编号为24的外卖商品信息*/
SELECT*FROM goods WHERE GoodsId=24;
/*查询外卖商品销售数量前3名的外卖商品编号*/
select a.title,a.CateId FROM goods a,orderdetails b WHERE a.GoodsId=b.GoodsID ORDER BY qty DESC LIMIT 3;
/*客户订单查询*/
SELECT*FROM orders WHERE custname='黄雅玲';
/*查询人均消费在30-50之间并且是川湘菜的商品信息*/
SELECT*FROM goods WHERE cateid=11 and storeid IN
(SELECT storeid FROM store WHERE personspending>=30 and personspending<=50);
/*查询所有每种类型的外卖商品的总销售额*/
SELECT a.cateName,b.title,c.unitprice as 总销售额
FROM category a,goods b,orderdetails c
WHERE a.cateid=b.cateid and b.goodsid=c.goodsid;
/*查询今天的外卖销售总额*/
SELECT sum(unitprice) as 销售总额 FROM orderdetails;
/*查询每个类别的商品数量*/
SELECT a.cateName,b.title,c.qty
FROM category a,goods b,orderdetails c WHERE a.cateid=b.cateid and b.goodsid=c.goodsid;
/*查询所有张三大盘鸡的外卖商品*/
SELECT*FROM goods WHERE storeid=18;

/*国庆期间外卖商品打折，所有外卖商品8.5折，特色小吃类7.5折销售*/
UPDATE goods set discount=0.85;
UPDATE goods set discount=0.75 WHERE cateid=8;
/*订单编号为‘20190220001’，这个订单已经收货，修改订单的状态*/
UPDATE orders set OrderStatus='已付款' WHERE OrderNo=20190220001;
/*Tom原来的密码太简单，要修改为复杂的新密码为'Tom_Love$book'*/
UPDATE customers set LoginPwd='Tom_Love$book' WHERE custname='Tom';
/*查询已发货的订单，显示订单编号、订单日期、收货人姓名和电话*/
SELECT orderno,orderdate,custname,custphone,orderstatus FROM orders WHERE OrderStatus='已发货';
/*查询已完成的订单的订单明细，显示订单日期、订购的外卖名称、订购数量、订购单价*/
SELECT a.OrderDate,b.Title,c.qty,b.UnitPrice FROM orders a,goods b,orderdetails c
WHERE a.OrederID=c.OrderID AND b.GoodsId=c.GoodsID AND OrderStatus='已收货';
/*查询orderno为‘20110508004’的收货人姓名、地址和电话*/
SELECT custname,custaddress,custphone FROM orders WHERE OrderNo=20110508004;
/*查询收货人电话中以‘188’开头的客户有几人*/
SELECT count(phone) as 人数 FROM customers WHERE phone LIKE '188%';
/*查询收获地址在‘二七广场’地区的有几人*/
SELECT count(CustAddress) as 人数 FROM orders WHERE CustAddress LIKE '%二七广场%';
/*查询出账单金额最高的订单的收货人姓名和电话*/
SELECT CustName,CustPhone FROM orders WHERE OrderPrice=(SELECT max(OrderPrice) FROM orders);
/*在order表中获取所有的收货地址，以及收货地址的购物次数*/
SELECT CustAddress AS 收货地址,COUNT(CustAddress) as 购物次数 FROM orders GROUP BY CustAddress;
/*查询收货地址为“北京市和平东路四段32号”的所购外卖商品的商品名收货人的姓名、地址、商品信息、价格、数量、商品分类*/
SELECT a.custname,a.custaddress,b.title,b.unitprice,c.qty,d.cateName FROM orders a,goods b,orderdetails c,category d
WHERE a.OrderID=c.OrderID and c.GoodsID=b.GoodsId and b.CateId=d.CateID AND CustAddress='北京市和平东路四段32号';
/*查询收货地址为“北京市和平东路四段32号”的所购外卖商品的商品名收货人的姓名、地址、商品信息、价格、数量、商品分类，并按照外卖商品的单价依降序排列*/
SELECT a.custname,a.custaddress,b.title,b.unitprice,c.qty,d.cateName FROM orders a,goods b,orderdetails c,category d
WHERE a.OrderID=c.OrderID and c.GoodsID=b.GoodsId and b.CateId=d.CateID AND CustAddress='北京市和平东路四段32号'
ORDER BY b.UnitPrice DESC;
/*查询出所有外卖商品中单价最高的外卖商品类别*/
SELECT a.CateName FROM category a,goods b WHERE a.CateID=b.CateId AND b.UnitPrice=(SELECT max(UnitPrice) FROM goods);
/*查询销量金额最高的一天*/
SELECT OrderDate,MAX(OrderPrice) FROM orders;
/*统计注册外卖商品中每个商品类别各有多少种商品*/
SELECT a.CateName,count(Title) FROM category a,goods b WHERE a.CateID=b.CateId GROUP BY b.title;
/*查询出外卖订单总价最多的客户名*/
SELECT CustName FROM orders WHERE OrderPrice=(SELECT max(OrderPrice) FROM orders);
/*查询每个商品种类中外卖商品的价格,最低价格和平均价格*/
SELECT MAX(unitprice)AS 最高价格,MIN(unitprice)AS 最低价格,AVG(unitprice)AS 平均价格 FROM goods;