菜品和套餐的关系究竟是怎样的？删除一个菜品时，需要操作哪些表？下面先分析多对多关系，最后回到案例执行删除操作。
- 菜品，例如青菜、煎蛋、烤肠、牛奶
- 套餐：青菜+烤肠、煎蛋+牛奶、青菜 +煎蛋…
所以菜品与套餐的关系就是多对多的关系，一个套餐可能关联多个菜品，一个菜品也可能被多个套餐包含。
使用额外的一张表来描述它们的关系，把多对多拆分成了一对多。
- dish：菜品表
- setmeal：套餐表
- setmeal_dish: 菜品-套餐表。
这张多对多关系描述表实际上有冗余字段，后面会讲到为什么冗余
1
2
3
4
5
6
7
8
9
10
11
12
| -- auto-generated definition
create table setmeal_dish
(
id bigint auto_increment comment '主键'
primary key,
setmeal_id bigint null comment '套餐id',
dish_id bigint null comment '菜品id',
name varchar(32) null comment '菜品名称 （冗余字段）',
price decimal(10, 2) null comment '菜品单价（冗余字段）',
copies int null comment '菜品份数'
)
comment '套餐菜品关系' collate = utf8mb3_bin;
|
存放套餐的时候，应该操作哪些表？例如现在打算创建两个套餐，分别为
那么SQL语句为
1
2
3
4
| insert into setmeal(category_id, name, price, description, image, create_time, update_time, create_user, update_user)
VALUES
(2, '青菜爆炒烤肠', 4.0, '青菜烤肠套餐，好吃！', '1.jpg', now(), now(), 1,1),
(2, '青菜煎鸡蛋', 5, '青菜煎鸡蛋，也好吃！', '2.jpg', now(), now(), 1,1)
|
查询数据库
1
| select id,name from setmeal
|
输出
此时只是插入了套餐setmeal自身的数据。但是菜品信息在哪里？显然，如果要创建套餐，必然保证有这个菜品，因此，还需要插入菜品信息
1
2
3
4
5
| insert into dish(name, category_id, price, image, description, status, create_time, update_time, create_user, update_user)
VALUES
('青菜', 1, 2, 'qingcai.jpg', '青色的菜', 1,now(), now(), 1,1),
('烤肠', 1, 3, 'kaochang.jpg', '烤的肠', 1, now(), now(), 1,1),
('煎蛋', 1, 3, 'jiandan.jpg', '煎的蛋', 1, now(), now(), 1,1)
|
查询数据库
1
| select id,name from dish
|
输出
现在它们在数据库中是独立的数据，毫无关联。因此，需要在第三方表格中，真正建立起关系
1
2
3
4
| insert into setmeal_dish(setmeal_id, dish_id, name, price, copies)
VALUES
(32, 78, '', 1, 99), -- 关联套餐1与青菜
(32, 79, '', 1, 99) -- 关联套餐1与烤肠
|
1
2
3
4
| insert into setmeal_dish(setmeal_id, dish_id, name, price, copies)
VALUES
(33, 78, '', 1, 99), -- 关联套餐2与青菜
(33, 80, '', 1, 99) -- 关联套餐2与煎蛋
|
现在套餐和菜品的关系才完全建立起来。分析发现name字段和price字段是多余的，因为不清楚name到底描述的是套餐的name还是菜品的name，价格也同理。
查询套餐1: 青菜爆炒烤肠关联的菜品
1
2
3
4
5
6
7
8
| -- 查询青菜爆炒烤肠关联的菜品id
select sd.dish_id from setmeal_dish sd left join setmeal s on sd.setmeal_id = s.id where s.name = '青菜爆炒烤肠';
-- 使用嵌套查询方式查询青菜爆炒烤肠关联的菜品名称与id
select id,name from dish where id in (select sd.dish_id
from setmeal_dish sd
left join setmeal s on sd.setmeal_id = s.id
where s.name = '青菜爆炒烤肠')
|
查询套餐2: 青菜煎鸡蛋关联的菜品
1
2
3
4
5
| -- 使用嵌套查询方式查询青菜煎鸡蛋关联的菜品名称与id
select id,name from dish where id in (select sd.dish_id
from setmeal_dish sd
left join setmeal s on sd.setmeal_id = s.id
where s.name = '青菜煎鸡蛋')
|
通过以上的分析，我们知道，一个菜品，可能被多个套餐关联，例如青菜同时被套餐1和套餐2引用了，因此在删除青菜的时候，应该先查询是否存在关联，再执行操作。可以使用嵌套查询
1
| delete from dish where name = '青菜' and id not in (select dish_id from setmeal_dish)
|
