mysql项目练习_mysql练习项目 - osc_wy5qpqnh的个人空间 - OSCHINA - 中文开源技术交流社区...

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 ;