1.5 案例
基于之前设计的多表案例的表结构，我们来完成今天的多表查询案例需求。
准备环境
将资料中准备好的多表查询的数据准备的SQL脚本导入数据库中。
- 分类表：category
- 菜品表：dish
- 套餐表：setmeal
- 套餐菜品关系表：setmeal_dish
-
需求实现
- 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称
/*查询技巧： 明确1：查询需要用到哪些字段 菜品名称、菜品价格 、 菜品分类名 明确2：查询的字段分别归属于哪张表 菜品表：[菜品名称、菜品价格] 分类表：[分类名] 明确3：如查多表，建立表与表之间的关联 菜品表.caategory_id = 分类表.id 其他：（其他条件、其他要求） 价格 < 10 */ select d.name , d.price , c.name from dish AS d , category AS c where d.category_id = c.id and d.price < 10;
- 查询所有价格在 10元(含)到50元(含)之间 且 状态为"起售"的菜品名称、价格及其分类名称 (即使菜品没有分类 , 也要将菜品查询出来)
select d.name , d.price, c.name from dish AS d left join category AS c on d.category_id = c.id where d.price between 10 and 50 and d.status = 1;
- 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格
select c.name , max(d.price) from dish AS d , category AS c where d.category_id = c.id group by c.name;
- 查询各个分类下 菜品状态为 "起售" , 并且 该分类下菜品总数量大于等于3 的 分类名称
/*查询技巧： 明确1：查询需要用到哪些字段 分类名称、菜品总数量 明确2：查询用到的字段分别归属于哪张表 分类表：[分类名] 菜品表：[菜品状态] 明确3：如查多表，建立表与表之间的关联 菜品表.caategory_id = 分类表.id 其他：（其他条件、其他要求） 条件：菜品状态 = 1 (1表示起售) 分组：分类名 分组后条件： 总数量 >= 3 */ select c.name , count(*) from dish AS d , category AS c where d.category_id = c.id and d.status = 1 -- 起售状态 group by c.name -- 按照分类名分组 having count(*)>=3; -- 各组后筛选菜品总数据>=3
- 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）
select s.name, s.price, d.name, d.price, sd.copies from setmeal AS s , setmeal_dish AS sd , dish AS d where s.id = sd.setmeal_id and sd.dish_id = d.id and s.name='商务套餐A';
- 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)
-- 1.计算菜品平均价格 select avg(price) from dish; -- 查询结果：37.736842 -- 2.查询出低于菜品平均价格的菜品信息 select * from dish where price < 37.736842; -- 合并以上两条SQL语句 select * from dish where price < (select avg(price) from dish);
