已有一餐饮商家的三份经营数据——MySQL课后习题

已有一餐饮商家的三份经营数据，包含：

①8月份每个订单的汇总情况表（order_info.sql）；

②8月份每个订单的明细点餐情况表（order_detail.sql）；

③餐厅所有菜品信息表（dishes.sql）

请根据这三个数据完成以下任务，所有任务均在practice数据库下进行，具体字段说明如下表：

order_info订单汇总表

字段	说明	字段类型
info_id	订单编号	int，主键
emp_id	服务员编号	int
consumers	顾客数	int
table_id	桌号	int
dishes_count	点菜数	int
payble	付款金额	float(5,1)
start_time	订单开始时间	datetime
lock_time	订单结束时间	datetime
order_status	订单状态（0取消，1正常，2未付款）	int

order_detail订单明细表

字段	说明	字段类型
detail_id	明细编号	int，非空
order_id	订单编号	int，非空
dishes_id	菜品编号	int，非空
counts	点某菜品的数量	int
amounts	菜品单价	float(5,1)
order_time	订单时间	datetime
emp_id	服务员编号	int

dishes菜品表

字段	说明	字段类型
dishes_id	菜品编号	int
dishes_name	菜品名	varchar(20)
picture_file	图片文件	varchar(50)
f4	其他说明	varchar(10)
create_time	菜品上架时间	datetime
maker	厨师	varchar(6)

任务

结合【课后练习1、2】已经完成的结果，完成以下任务：

1.查询明细表detail中每个厨师负责菜品的单价,按厨师降序排序。结果参考：

2.通过order_detail表店统计铺每天每道菜品的销售状况:包括日期，菜品名称，总数量，每单平均的菜品数量。结果参考：

3.用CASE WHEN查看8月15日之后每天主打菜品‘麻辣小龙虾’‘剁椒鱼头’‘啤酒鸭’‘干锅田鸡’的销售量，并把结果设置成视图v_topdishe。结果参考：

4.统计姓‘王’的厨师每天被退单（order_status=0）的菜品数量。结果参考：

5.查询店铺每天订单状态正常每道菜品的销售状况:包括日期，菜品编号，数量，每单平均数量。结果参考：

6.查询dishes表中菜品名称有重复的菜品。结果参考：

7.因为菜单更新的缘故，dishes表存在多个重复的菜品名的菜品，现需要只保留最新的菜品信息。方法是创建一个视图v_dishes保存同一个菜名最后一次更新且菜品编号最大的记录来达到去除菜名重复的历史菜品的目的。

8.若存在厨师“张三”的菜品，则查询出张三菜品对应的所有订单明细信息，分别使用exists子查询和in查询。

9.由于查询语句经常用到日期做分组统计，给order_info和order_detail两个表均新增一列order_date（日期格式），再在两个表的order_date列上创建普通索引。

10.在order_detail的dishes_id,detail_id上分别建立普通索引和联合索引；在dishes表的dishes_id上建立普通索引。

1. 查询明细表detail中每个厨师负责菜品的单价,按厨师降序排序
select distinct d.maker,d.disher_name,od.amounts from dishes d,order_detail od
where d.dishes_id=od.dishes_id order by d.maker desc;

2.通过order_detail表店统计铺每天每道菜品的销售状况:包括日期，菜品名称，总数量，每单平均的菜品数量。
select date(order_time) 日期,dishes_name 菜品名称,sum(counts) 总数量,sum(counts)/count(order_id) 每单平均的菜品数量
from dishes d,order_detail od
where d.dishes_id=od.dishes_id 
group by date(order_time),d.dishes_name order by date(order_time); 


3. 用CASE WHEN查看8月15日之后每天主打菜品‘麻辣小龙虾’‘剁椒鱼头’‘啤酒鸭’‘干锅田鸡’的销售量，并把结果设置成视图v_topdishe.
create view v_topdishe as
select date(order_time) 日期,
sum(case when dishes_name = '麻辣小龙虾' then counts else 0 end) 麻辣小龙虾,
sum(case when dishes_name = '剁椒鱼头' then counts else 0 end) 剁椒鱼头,
sum(case when dishes_name = '啤酒鸭' then counts else 0 end) 啤酒鸭,
sum(case when dishes_name = '干锅田鸡' then counts else 0 end) 干锅田鸡
from order_detail od ,dishes d
where od.dishes_id = d.dishes_id and date(order_time)>'2021-8-15'
group by date(order_time) order by date(order_time);

select * from v_topdishe;


4. 统计姓‘王’的厨师每天被退单（order_status=0）的菜品数量
select date(order_time),d.maker,sum(counts) from order_info oi,dishes d ,order_detail od
where d.dishes_id=od.dishes_id and oi.info_id=od.order_id and d.maker like '王%' and oi.order_status=0
group by date(order_time) order by date(order_time);


5. 查询店铺每天订单状态正常(order_status=1)每道菜品的销售状况: 包括日期，菜品编号，数量，每单平均数量
select date(order_time) 日期, od.dishes_id 菜品编号,sum(counts) 数量,sum(counts)/count(order_id) 每单平均数量
from order_detail od, order_info oi
where od.order_id=oi.info_id and oi.order_status=1
group by date(order_time),dishes_id order by date(order_time);


6. 查询dishes表中菜品名称有重复的菜品
select * from dishes_name in 
(select dishes_name from dishes group by dishes_name HAVING count(dishes_name) > 1);


7. 创建一个视图v_dishes,保存同一个菜名(group by dishes_name)最后一次更新(即最大上架时间)并且菜品编号最大(max(dishes_id))的记录
create view v_dishes as 
select * from dishes where (dishes_name,create_time,dishes_id) in 
(select dishes_name,max(create_time),max(dishes_id) from dishes group by dishes_name);



8.若存在厨师“王彥志”的菜品，则查询出王彥志菜品对应的所有订单明细信息，分别使用#exists子查询和in查询。
select * from order_detail where dishes_id in
(select dishes_id from dishes where maker='王彥志');

select * from order_detail where exists
(select dishes_id from dishes where maker='王彥志' and order_detail.dishes_id=dishes.dishes_id);



9.由于查询语句经常用到日期做分组统计，给order_info和order_detail两个表均新增一列order_date（日期格式），再在两个表的order_date列上创建普通索引。
alter table order_info add order_date date;
alter table order_detail add order_date date;
UPDATE order_info set order_date = date(start_time);
update order_detail set order_date = date(order_time);
create index ind_date on order_info(order_date);
create index ind_date on order_detail(order_date);

show index from order_info;



10.在order_detail的dishes_id,detail_id上分别建立普通索引和联合索引；在dishes表的dishes_id上建立普通索引。
create index ind_dish on order_detail(dishes_id); #在order_detail的dishes_id建立普通索引
create index ind_deta on order_detail(detail_id);
create index ind_dish_deta on order_detail(dishes_id,detail_id);
create index ind_dish on dishes(dishes_id);