SELECT OrderID FROM orders ORDER BY OrderDate DESC LIMIT 2;
SELECT OrderID FROM (SELECT OrderID FROM orders ORDER BY OrderDate DESC LIMIT 2)as x;
SELECT GoodsID FROM orderdetails WHERE OrderID IN(SELECT OrederID FROM (SELECT OrederID FROM orders ORDER BY OrderDate DESC LIMIT 2)as x);
SELECTFROM goods WHERE Goodsld
IN(SELECT GoodsID FROM orderdetails WHERE OrderID
IN(SELECT OrederID FROM (SELECT OrederID FROM orders ORDER BY OrderDate DESC LIMIT 2)as x));
//最新外卖显示
SELECT max(qty) FROM orderdetails;
SELECT a.,b.* FROM goods a,orderdetails b WHERE a.Goodsld=b.GoodsID AND qty=(SELECT max(qty) FROM orderdetails);
//畅销外卖显示
SELECTFROM goods WHERE Disount<1;//活动外卖显示
SELECTFROM goods WHERE Title LIKE ‘%汉堡%’//按照某关键字雨外卖标题的匹配查询外卖信息并显示
SELECT a.,b. FROM category a RIGHT JOIN goods b ON a.CateID=b.CateID;//按照外卖类型分类显示外卖信息
SELECT * FROM goods WHERE Goodsld=1;//用户点击某一个外卖,显示该外卖的详细信息
UPDATE goods SET likes=‘1’ WHERE Goodsld=2;//用户可以选择自己喜欢的外卖,放入购物车显示为‘1’
UPDATE customers SET loginstatus=‘已登录’ WHERE CustName=‘ade’ and LoginPwd=2322421;//用户可以对购物车中的外卖下订单,下订单前需要先登录
SELECT a.CustName,b.OrderStatus FROM customers a,orders b WHERE a.CustID=b.CustID and a.CustName=‘love’;//用户登录可以查询自己的订单的情况,包括是否发货、商品在途情况、商品签收情况。
INSERT INTO goods(Title,Unitprice,PackingExpense) VALUE(‘地三鲜’,‘30.00’,‘2’);//外卖基本信息录入
UPDATE goods SET Roat=3 WHERE Goodsld=2;//外卖信息修改
UPDATE goods SET Disount=0.5 WHERE Goodsld=4;//外卖销售折扣设置
SELECT * FROM orders WHERE OrderStatus=‘已付款’;//订单发货
SELECT * FROM orders WHERE OrderNo=20190220301;//订单信息查询
UPDATE customers SET Account=1000 WHERE CustName=‘ttt’;//用户账户充值
UPDATE customers SET LoginPwd=‘234654’ WHERE CustName=‘ade’;//用户密码修改
网站前台业务模拟
SELECT * FROM goods;//查询所有外卖商品编号、名称、定价、折扣、分类编号、商家编号、评分、描述、包装费
SELECT * FROM goods WHERE CateID=(SELECT CateID FROM category WHERE CateName=‘川湘菜’);//查询“川湘菜”类型的所有外卖商品信息。
SELECT * FROM goods WHERE Title LIKE ‘%大盘鸡%’;//查询出所有包含“大盘鸡”标题的外卖商品。
SELECT * FROM goods WHERE Roat=5 and Description=‘麻辣’;//查询所有评分为5并且描述中含有麻辣内容的外卖商品。
SELECT * FROM goods WHERE Goodsld=24;//查询编号为24的外卖商品编号、名称、定价、折扣、分类编号、商家编号、评分、描述、包装费。
SELECT * FROM orderdetails WHERE GoodsID<=‘10’ ORDER BY qty ASC;//查询外卖商品销售数量前10