SELECT * FROM `category`
//最新外卖显示
SELECT max(Goodsld)FROM goods ;
SELECT * FROM goods WHERE Goodsld in (SELECT max(Goodsld)FROM goods);
//畅销外卖显示
SELECT Goodsid FROM orderdetails WHERE qty in(SELECT MAX(qty) FROM orderdetails);
SELECT * FROM goods WHERE Goodsld in (SELECT Goodsid FROM orderdetails WHERE qty in(SELECT MAX(qty) FROM orderdetails));
//活动外卖显示
SELECT Goodsld FROM goods WHERE Discount<1;
SELECT * FROM goods WHERE Goodsld in (SELECT Goodsld FROM goods WHERE Discount<1);
//按照某关键字与外卖标题的匹配查询外卖信息并显示
SELECT * FROM goods WHERE Title like '黑%';
//按照外卖类别分类显示外卖信息
SELECT * FROM goods a,category b WHERE a.Cateid=b.catelid and Cateid=1;
//用户点击一个外卖，显示该外卖的详细信息
SELECT * FROM goods WHERE Goodsld=3;
//用户可以选择自己喜欢的外卖，放入购物车 喜欢为1 默认为0
SELECT * FROM goods WHERE xihaun=1;
//用户可以对购物车中的外卖下订单，下订单钱需先登录
SELECT a.CustName,a.LoginPwd FROM customers a ,orderdetails b WHERE a.Custid=b.Order and a.CustName='黄雅玲' and a.LoginPwd='666666';
//用户登录后可以查询自己的订单的情况，包括是否发货、商品在途情况、商品签收情况
SELECT * FROM orders WHERE Custld IN (SELECT Custid FROM customers WHERE CustName='黄雅玲' and LoginPwd='666666' );
//外卖基本信息录入
INSERT goods
VALUES('29','热干面','6','1','3','3','5','物美价廉','1','1');
//外卖信息修改
UPDATE goods set Discount='0.6' WHERE Goodsld=29;
//外卖销售折扣设置
UPDATE goods set Discount='0.9' WHERE Goodsld=25;
//订单发货
SELECT * FROM orders WHERE OrderStatus='已付款';
//订单信息查询（可随时查询订单货物的情况）
SELECT * FROM orders WHERE OrderStatus!='未付款';
//用户账户充值
UPDATE customers set Account='100' WHERE Custid=1;
//用户密码修改
UPDATE customers set LoginPwd='951753' WHERE Custid=2;
//前台客户注册
INSERT customers
VALUES('7','Tom','123456','010-60257566','tom@hotmail.com','北京市海淀区苏州18号维亚大厦12楼','1');
//添加商品分类信息，商家信息和外卖商品信息
INSERT category
VALUES('8','甜点');
INSERT store
VALUES('8','永超小吃','黄泉路','666');
INSERT goods
VALUES('36','干锅肉丝','36','1','1','1','5','物美价廉','1','1');
//录入其他测试数据
UPDATE goods set Discount='0.7' WHERE Goodsld=36;
//请查询出所有外卖商品编号、名称、定价、折扣、分类编码、商家编码、评分、描述、包装费。
SELECT * FROM goods;
//查询“川湘菜”类型的所有外卖商品信息
SELECT Catelid FROM category WHERE CateName='川湘菜';
SELECT* FROM goods WHERE Cateid in(SELECT Catelid FROM category WHERE CateName='川湘菜');
//请查询出所有包含“大盘鸡”标题的外卖商品
SELECT * FROM goods WHERE Title LIKE'%大盘鸡%';
//请查询出所有评分为5并且描述中含有麻辣内容的外卖商品
SELECT * FROM goods WHERE Roat=5 and Descriptio LIKE'%麻辣%';
//查询编号为24的外卖商品编号、名称、定价、折扣、分类编码、商家编码、评分、描述、包装费。
SELECT * FROM goods WHERE Goodsld=24;
//畅销外卖商品查询，销售数量前10名的外卖商品编号。
SELECT Goodsid FROM orderdetails ORDER BY qty DESC LIMIT 10;
//客户订单查询。黄雅玲已经登录了网站，他需要查看自己的所有历史订单信息（不需要订单明细）。
SELECT OrderNo,CustName FROM orders WHERE Custld IN (SELECT Custid FROM customers WHERE CustName='黄雅玲' and LoginPwd='666666' );
//请查询出人均消费在30-50之间并且是川湘菜的外卖商品。
SELECT * FROM store a,goods b WHERE (a.PersonSpending >=30 and a.PersonSpending<=50)and b.Cateid in(SELECT Catelid FROM category WHERE CateName='川湘菜' );
//查询出所有每种类型的外卖商品的总销售额
SELECT a.Goodsld,b.UnitPeice,c.Catelid FROM goods a,orderdetails b,category c WHERE a.Goodsld=b.Goodsid and a.Cateid=c.Catelid;
//请查询出今天的外卖销售总额
SELECT sum(UnitPeice) FROM orderdetails;
//统计出每个类别的外卖商品数量
SELECT a.Catelid,b.Goodsld,c.qty FROM category a,goods b,orderdetails c WHERE a.Catelid=b.Cateid and b.Goodsld=c.Goodsid;
//查询所有“张三大盘鸡”的外卖商品
SELECT Storeid FROM store WHERE StoreName='张三大盘鸡';
SELECT * FROM goods WHERE Storeld IN(SELECT Storeid FROM store WHERE StoreName='张三大盘鸡');
//国庆期间外卖商品打折，所有外卖商品一律8.5折，特色小吃类外卖商品7.5折销售，据此在数据库中设置打折信息
UPDATE goods set Discount=0.85;
UPDATE goods set Discount=0.75 WHERE Cateid=2;
//Tom最近订购的一个订单编号为“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 FROM orders WHERE OrderStatus='已发货';
//查询已完成的订单的订单明细，显示订单日期、订购的外卖名称、订购数量、订购单价。
SELECT a.orderDate,b.Title,c.qty,b.UnitPrice FROM orders a,goods b,orderdetails c WHERE a.Ordersid=c.Order and b.Goodsld=c. Goodsid and a.OrderStatus='已收货';
//查询出OrderNo为“20110508004”的收货人姓名，地址和电话
SELECT CustName,CustAddress,CustPhone FROM orders WHERE OrderNo='20110508004';
//查询出收货人电话以“188”开头的客户有几人
SELECT COUNT(phone) FROM customers WHERE phone like'188%';
//查询出收货地址在“二七广场”地区的有几人
SELECT count(address) FROM customers WHERE address='二七广场';
//查询出账单金额最高的订单收货人姓名和电话
SELECT a.CustName,a.CustPhone FROM orders a,orderdetails b WHERE a.Ordersid=b.Order and b.UnitPeice in(SELECT max(UnitPeice) FROM orderdetails);
//在orders表中获取所有的收货地址，以及每个收货地址购物次数
SELECT custaddress,COUNT(custaddress) as 购物次数 FROM orders GROUP BY custaddress;
//请查询出收货地地址为“北京市和平东路四段32号“的所购外卖商品的商品名收货人的姓名、地址、商品信息、价格、数量、商品分类
SELECT b.CustName,b.CustAddress,a.Descriptio,a.UnitPrice,c.qty,a.Cateid FROM goods a,orders b,orderdetails c WHERE a.Goodsld=c.Goodsid AND b.Ordersid=c.Order AND custaddress='北京市和平东路四段32号';
//请查询出收货地址为“北京书和平路四段32号”的锁钩外卖商品的商品名收货人的姓名、地址、商品信息、价格、数量、商品分类。并按照外卖商品的单价以降序排列
SELECT b.CustName,b.CustAddress,a.Descriptio,a.UnitPrice,c.qty,a.Cateid FROM goods a,orders b,orderdetails c WHERE a.Goodsld=c.Goodsid AND b.Ordersid=c.Order AND custaddress='北京市和平东路四段32号'ORDER BY unitprice DESC;
//请查询在所有外卖商品中单价最高的外卖商品类别
SELECT Cateid FROM goods WHERE UnitPrice in(SELECT MAX(UnitPrice)FROM goods );
//请查询出销量(销售金额)最高的一天
SELECT MAX(a.qty) ,b.OrderDate FROM orderdetails a,orders b WHERE a.Order=b.Ordersid;
//统计注册外卖商品中每个商品类别各有多少种商品
SELECT b.Catelid ,COUNT(b.CateName) FROM goods a,category b WHERE a.Cateid=b.Catelid GROUP BY b.CateName ;
//大客户查询，查询出外卖订单总价最多的客户名。
SELECT a.CustName FROM orders a,orderdetails b WHERE a.Ordersid=b.Order ORDER BY b.UnitPeice desc LIMIT 1;
//查询出每个商品类别中外卖商品的最高价格，最低价格和平均价格。
SELECT b.Catelid ,max(a.UnitPrice),avg(a.UnitPrice),min(a.UnitPrice) FROM goods a,category b WHERE a.Cateid=b.Catelid GROUP BY b.CateName ;