3.25 曹操外卖查询

点击下载曹操外卖数据表及数据:
[button color=”info” icon=”#93DDF8″ url=”https://www.dlck.cn/usr/uploads/2022/03/3192835753.rar” type=”round”]点击下载[/button]

1. 向商品分类表中插入数据

   insert into category(catename)
   values('饮品'),
   ('烧烤'),
   ('面食'),
   ('米饭'),
   ('米粉'),
   ('米线'),
   ('凉皮'),
   ('热炒'),
   ('冷拼');
   insert into category(catename)
   values('小吃'),
   ('火锅'),
   ('早餐');

2. 向商家表中添加数据

   insert into store(storename,address,personspending)
   values('速味居黄焖鸡', '郑州市金水区文化路88号', 20),
   ('蜜雪冰城', '郑州市东风路78号', 15),
   ('逍遥镇胡辣汤', '郑州市金水区博颂路66号', 15),
   ('兰州拉面', '郑州市金水区文化路80号', 20),
   ('巴奴', '郑州市金水区文化路70号', 150),
   ('海底捞火锅', '郑州市金水区东风路60号', 200),
   ('和平饭店', '上海市南京路1号', 800),
   ('肯德基', '郑州市管城区文化路2号', 40),
   ('小豪凉皮', '郑州市惠济区航海路80号', 10);

3. 向商品表中添加外卖商品

   insert into goods(title,unitprice,cateid,storeid,packingexpense)values
   ('双拼奶茶',8,1,2,1),
   ('红豆奶茶',8,1,2,1),
   ('珍珠奶茶',9,1,2,1),
   ('臭豆腐',10,10,9,1),
   ('胡辣汤',5,12,3,1),
   ('香辣鸡腿堡',20,10,8,1),
   ('黄焖鸡米饭',16,4,1,1),
   ('佛跳墙',200,8,7,5),
   ('烩面',12,3,4,1),
   ('麻辣烫',10,5,9,1),
   ('烤羊肉串',5,2,9,1),
   ('北京烤鸭',300,2,7,5);

4. 向客户表中添加数据

   insert into customers(custname,loginpwd,phone,address,account)values
   ('luban123','123456','13566666666','金水区文化路79号', 10000),
   ('hanxin666','hanxin666','17566666666','金水区东风路79号', 10000),
   ('libai888','libai888','18566666666','二七区凤凰路79号', 10000),
   ('xiaohao333','xiaohao333','16566666666','管城区花园路79号', 10000),
   ('daji777','daji777','18766666666','黄埔区南京路79号', 10000),
   ('yuji123','yuji123','16666666666','金水区博宋路79号', 10000);

5. 向订单表中添加数据

   insert into orders(orderno,custid,custname,custphone,custaddress,orderstatus)
   values
   ('or12311243',1,'luban123','13566666666','金水区文化路79号','已收货'),
   ('or12311212',2,'hanxin666','17566666666','金水区东风路79号','已收货'),
   ('or12311223',1,'luban123','13566666666','金水区文化路79号','已收货'),
   ('or12311245',3,'libai888','18566666666','二七区凤凰路79号','已收货'),
   ('or12311246',4,'xiaohao333','16566666666','管城区花园路79号','已收货'),
   ('or12311244',4,'xiaohao333','16566666666','管城区花园路79号','已收货'),
   ('or12311241',5,'daji777','18766666666','黄埔区南京路79号','已收货');

6. 向订单明细表中添加数据

   insert into orderdetails
   values
   (1, 1, 1, 8),
   (1, 2, 1, 8),
   (2, 4, 1, 10),
   (2, 8, 1, 200),
   (3, 5, 1, 5),
   (3, 6, 1, 10),
   (3, 11, 5, 5),
   (4, 7, 1, 16),
   (5, 9, 1, 12),
   (6, 3, 1, 9);

   网站前台业务模拟

   -- 1
   SELECT * FROM goods;
   -- 2
   select * from goods LEFT JOIN category ON goods.cateid = category.cateid WHERE category.cateid = 1;
   -- 3
   SELECT * FROM goods WHERE title LIKE'%奶茶%';
   -- 4
   SELECT * FROM goods WHERE roat = 5 and description LIKE '%麻辣';
   -- 5
   SELECT * FROM goods WHERE goodsid = 6;
   -- 6
   SELECT goodsid,(qty) FROM orderdetails ORDER BY qty desc LIMIT 3;
   -- 7
   SELECT * FROM orders WHERE custname = 'daji777';
   -- 8
   SELECT * FROM goods WHERE unitprice BETWEEN 5 AND 10 AND cateid = (SELECT cateid FROM category WHERE catename = '饮品');
   -- 9
   SELECT ss.cateid, SUM(qty*unitprice) FROM (SELECT od.goodsid, od.qty,od.unitprice,g.cateid FROM orderdetails od LEFT JOIN goods g on od.goodsid = g.goodsid) ss GROUP BY ss.cateid;
   -- 10
   SELECT SUM(orderprice) FROM orders WHERE orderdate BETWEEN '2022-03-25 00:00:00' and '2022-03-26 00:00:00';
   -- 11
   SELECT ss.cateid, SUM(qty) FROM (SELECT od.goodsid, od.qty,od.unitprice,g.cateid FROM orderdetails od LEFT JOIN goods g on od.goodsid = g.goodsid) ss GROUP BY ss.cateid;
   -- 12
   SELECT * FROM goods WHERE storeid = (SELECT storeid FROM store WHERE storename = '和平饭店');

   网站后台业务模拟

   -- 1
   SELECT ss.orderdate,ss.title,ss.qty,ss.unitprice FROM (SELECT o.orderdate,g.title,od.qty,g.unitprice FROM orderdetails od left JOIN orders o ON od.orderid = o.orederid left JOIN goods g on o.orederid = g.cateid LEFT JOIN category c on g.cateid = c.cateid) ss GROUP BY ss.orderdate;
   -- 2
   SELECT orderno,custname,custaddress,custphone FROM orders WHERE orderno = 20210508004;
   -- 3
   SELECT * FROM orders WHERE custphone LIKE '188%';
   -- 4
   SELECT * FROM orders WHERE custaddress LIKE '二七区%';
   -- 5
   SELECT max(orderprice),custname,custphone FROM orders;
   -- 6
   SELECT ss.custaddress '收货地址',sum(qty) '购物次数' FROM (SELECT od.qty,o.custaddress FROM orderdetails od LEFT JOIN orders o on od.orderid = o.orederid) ss GROUP BY ss.custaddress;
   -- 7
   SELECT * FROM orders WHERE custaddress = '北京市和平东路四段32号';
   -- 8
   SELECT ss.title,ss.custname,ss.custaddress,ss.description,ss.unitprice,ss.qty,ss.catename FROM (SELECT o.custname,o.custaddress,od.qty,c.catename,g.title,g.unitprice,g.description FROM orderdetails od left JOIN orders o ON od.orderid = o.orederid left JOIN goods g on o.orederid = g.cateid LEFT JOIN category c on g.cateid = c.cateid) ss GROUP BY ss.title HAVING custaddress = '北京市和平东路四段32号';
   -- 9
   SELECT ss.title,ss.custname,ss.custaddress,ss.description,ss.unitprice,ss.qty,ss.catename FROM (SELECT o.custname,o.custaddress,od.qty,c.catename,g.title,g.unitprice,g.description FROM orderdetails od left JOIN orders o ON od.orderid = o.orederid left JOIN goods g on o.orederid = g.cateid LEFT JOIN category c on g.cateid = c.cateid) ss GROUP BY ss.title HAVING custaddress = '北京市和平东路四段32号' ORDER BY unitprice desc;
   -- 10 -- 待完成
   SELECT SUM(orderprice) FROM orders WHERE orderdate BETWEEN '2022-03-25 00:00:00' and '2022-03-26 00:00:00';
   -- 11
   SELECT cg.catename,COUNT(cateid) FROM (SELECT c.catename,g.cateid FROM category c LEFT JOIN goods g on c.cateid = g.cateid) cg GROUP BY cg.catename;
   -- 12
   SELECT MAX(orderprice),custname FROM orders;
   -- 13
   SELECT cg.catename,max(unitprice),MIN(unitprice),avg(unitprice) FROM (SELECT c.catename,g.cateid,g.unitprice FROM category c LEFT JOIN goods g on c.cateid = g.cateid) cg GROUP BY cg.catename;