MySQL --- 多表查询 --- 案例

1.5 案例  根据需求，完成多表查询的SQL语句编写

 基于之前设计的多表案例的表结构，来完成今天的多表查询案例需求。

准备环境

将准备好的多表查询的数据准备的SQL脚本导入数据库中。 

• 分类表：category

• 菜品表：dish

• 套餐表：setmeal

• 套餐菜品关系表：setmeal_dish

 

需求实现  

1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称

-- 需求:
-- 1.查询价格低于10元的菜品的名称、价格及其菜品的分类名称
/* 查询技巧:
      明确1:查询需要用到哪些字段
           菜品名称、菜品价格、菜品分类名
      明确2:查询的字段分别归属于哪张表
           菜品表:[菜品名称、菜品价格]
           分类表:[分类名称]
      明确3:如查多表,建立表与表之间的关联
           菜品表.category_id = 分类表.id
      其它:(其它条件、其它要求)
           价格 < 10
 */
-- 表:dish,category
-- SQL:如果在需求当中,没有额外的说要完全包含哪张表的数据,我们直接使用内连接就可以
-- SQL语句比较长,按 Ctrl + Alt + L 对SQL语句进行一个格式化
select d.name 菜品名称, d.price 菜品价格, c.name 菜品分类
from dish as d,
     category as c
where d.price < 10
  and d.category_id = c.id;

-- 2.查询所有价格在10元(含)到50元(含)之间且状态为"起售"的菜品名称、价格及其分类名称(即使菜品没有分类,也要将菜品查询出来)
-- 表:dish,category
-- SQL:要使用外连接,因为内连接仅仅包含两张表交集部分的数据,一般考虑使用左外连接
-- on之后跟上连接查询条件
-- 如果在多表查询当中还有查询条件,继续在where之后来写查询条件
select d.name, d.price, c.name
from dish d
         left join category c on d.category_id = c.id
where d.price between 10 and 50
  and d.status = 1;


-- 3.查询每个分类下最贵的菜品,展示出分类的名称、最贵的菜品的价格
-- 表:dish,category
-- SQL:涉及到两张表,我们就要使用连接查询,如果没有特殊的说明,直接使用内连接即可
-- where之后来消除无效的笛卡尔积
select c.name, max(d.price)
from dish d,
     category c
where d.category_id = c.id
group by c.name; -- 按照分类名字进行分组

-- 4.查询各个分类下 菜品状态 为"起售",并且该分类下菜品总数量大于等于3的分类名称
-- 表:dish,category
-- SQL:各个分类下和总数量,想到分组查询 --- group by 和 聚合统计操作 --- count(*),即分组聚合操作
-- 如果在分组之后还要进行条件的过滤、条件的筛选,不要写在where之后,就可以在group by之后再加上一个关键字having,然后通过having关键字来指定分组后的过滤条件
select c.name, count(*)
from dish d,
     category c
where d.category_id = c.id
  and d.status = 1 -- 起售状态
group by c.name -- 按照分类名分组
having count(*) >= 3; -- 分组后进行菜品总数量的筛选

-- 5.查询出"商务套餐A"中包含了哪些菜品(展示出套餐名称、价格,包含的菜品名称、价格、份数)
-- 表:setmeal,setmeal_dish,dish
-- SQL:where之后通过连接查询调价描述表与表之间的关系
select s.name, s.price, d.name, d.price, sd.copies
from setmeal s,
     setmeal_dish sd,
     dish d
where s.id = sd.setmeal_id
  and sd.dish_id = d.id
  and s.name = '商务套餐A';


-- 6.查询出低于菜品平均价格的菜品信息(展示出菜品名称、菜品价格)
-- 表:dish
-- SQL:
-- a.计算 菜品的平均价格
select avg(price) from dish;
-- b.查询出低于菜品平均价格的菜品信息
select name,price from dish where price < (select avg(price) from dish);

 多表查询总结：