JavaWeb——MySQL-多表查询(6/7)-案例1（案例准备与表关系梳理多表查询需求实现：六道实际应用题以及测试数据）

本文链接：https://blog.youkuaiyun.com/li13437542099/article/details/144729665

案例准备与表关系梳理

案例背景与表结构回顾

本案例围绕苍穹外卖项目相关表结构展开多表查询实践，涉及分类表（category）、菜品表（dish）、套餐表（set_meal）及套餐菜品关系表（set_meal_dish）。分类表存储菜品与套餐分类，菜品表含菜品详细信息及分类 ID 关联分类表，套餐表有套餐信息及分类 ID 关联分类表，套餐菜品关系表维护套餐与菜品多对多关系，含套餐 ID、菜品 ID 及份数字段。此前多表设计已确定表结构及关系，为多表查询提供基础。

测试数据准备与表关系梳理

为不影响阅读，测试数据已放置文末

分类表 7 条数据，含名称、类型、排序、状态及操作时间；

菜品表 19 条数据，涵盖名称、分类、价格、图像、描述、状态；

套餐表 3 条数据；套餐菜品关系表记录套餐与菜品关联及份数。

表间关系为分类与菜品、分类与套餐一对多，套餐与菜品多对多，关系图助于理解，为查询需求分析与语句编写提供依据。

多表查询需求实现

需求 1：查询价格低于 10 元菜品的相关信息（名称、价格、分类名称）

需求分析与表连接确定

查询涉及菜品名称、价格（菜品表字段）及分类名称（需关联分类表），确定涉及菜品与分类表。需求未特殊要求，采用内连接。

查询语句编写与执行

编写

SELECT d.name, d.price, c.name
FROM dish d,
     category c
WHERE d.category_id = c.id
  AND d.price < 10;

先以别名连接两表，设连接条件，添加价格筛选条件，指定返回字段（菜品名称、价格、分类名称）

执行查询获 7 条数据，价格均低于 10 元，含菜品及对应分类名称，结果符合预期，准确满足需求。

需求 2：查询 10 - 50 元起售菜品的相关信息（名称、价格、分类名称），包含无分类菜品

需求解读与连接方式选择

查询涉及菜品名称、价格、分类名称及状态（起售），需菜品与分类表。因要求包含无分类菜品，需完全包含菜品表数据，选用左外连接，菜品表放左，分类表放右。

查询语句构建与结果验证

编写

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;

用左外连接关联表，设连接条件，添加价格与状态筛选条件，指定返回字段。

执行查询得 5 条记录，价格在 10 - 50 元间且起售，含无分类菜品（分类名称可能为空），结果正确，满足需求。

需求 3：查询每个分类下最贵菜品的相关信息（分类名称、菜品价格）

需求剖析与查询思路确定

查询涉及分类名称（分类表）与菜品价格（菜品表），需两表连接。先连接查询获取所有菜品及分类信息，再按分类分组，用聚合函数MAX求每组（每个分类）菜品价格最大值。

查询语句分步实现与结果呈现
- 连接查询：
```
SELECT * FROM dish d, category c WHERE d.category_id = c.id;
```
  获取所有菜品及分类信息。
- 分组查询：
```
SELECT c.name, MAX(d.price)
FROM dish d,
     category c
WHERE d.category_id = c.id
GROUP BY c.name;
```
  在连接查询基础上，按分类名称分组，用MAX函数求每个分类下菜品价格最大值，指定返回字段为分类名称与最贵菜品价格。

执行查询得每个分类下最贵菜品价格及分类名称，如酒水饮料最贵 6 元、传统主食最贵 2 元、经典川菜最贵 138 元，结果符合需求，实现查询每个分类下最贵菜品信息功能。

需求 4：查询所有套餐及包含菜品信息（套餐名称、菜品名称、菜品价格、菜品数量）

需求分析与表关联规划

查询涉及套餐名称（套餐表）、菜品名称、价格（菜品表）及菜品数量（套餐菜品关系表），需关联套餐表、菜品表与套餐菜品关系表。因需展示所有套餐及菜品信息，考虑使用左外连接确保数据完整性。

查询语句编写与结果验证

编写

SELECT sm.name, d.name, d.price, smd.copies
FROM setmeal sm
         LEFT JOIN setmeal_dish smd ON sm.id = smd.setmeal_id
         LEFT JOIN dish d ON smd.dish_id = d.id;

用左外连接依次关联套餐表、套餐菜品关系表与菜品表，设连接条件，指定返回字段（套餐名称、菜品名称、价格、数量）。

执行查询获套餐及包含菜品信息，套餐下菜品信息完整，无套餐或菜品信息缺失，结果正确，满足查询所有套餐及菜品详细信息需求。

需求 5：查询包含米饭菜品的套餐信息（套餐名称、菜品名称、菜品价格、菜品数量）

需求解读与查询策略确定

查询涉及套餐名称、菜品名称、价格、数量及筛选条件（包含米饭菜品），需关联套餐表、菜品表与套餐菜品关系表。可先在菜品表中查米饭菜品 ID，再于套餐菜品关系表中找含该 ID 记录，最后关联套餐表获取套餐信息。

查询语句构建与结果展示

编写

SELECT sm.name, d.name, d.price, smd.copies
FROM dish d,
     setmeal_dish smd,
     setmeal sm
WHERE d.name = '米饭'
  AND d.id = smd.dish_id
  AND smd.setmeal_id = sm.id;

通过关联条件连接三表，添加筛选条件（菜品名称为米饭），指定返回字段。

执行查询获包含米饭菜品的套餐信息，准确展示套餐名称、米饭菜品相关信息及数量，结果符合需求，实现按条件查询特定菜品所在套餐信息功能。

需求 6：查询每个套餐的总价格（套餐名称、总价格）

需求剖析与计算思路

查询涉及套餐名称（套餐表）与总价格（需计算套餐内菜品价格总和），需关联套餐表与套餐菜品关系表、菜品表。先连接三表获取套餐及菜品信息，再按套餐分组，用SUM函数计算每个套餐内菜品价格总和。

查询语句实现与结果分析

编写

SELECT sm.name, SUM(d.price * smd.copies) AS total_price
FROM setmeal sm,
     setmeal_dish smd,
     dish d
WHERE sm.id = smd.setmeal_id
  AND smd.dish_id = d.id
GROUP BY sm.name;

连接三表，设连接条件，按套餐名称分组，用SUM函数计算总价格（菜品价格乘数量）并设别名，指定返回字段。

执行查询得每个套餐名称及总价格，总价格计算准确，反映套餐内菜品总价，结果满足需求，实现查询每个套餐总价格功能。

测试数据

（来源：黑马程序员；侵权删，仅供学习使用）

-- 分类表
create table category(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(20) not null unique comment '分类名称',
    type tinyint unsigned not null comment '类型 1 菜品分类 2 套餐分类',
    sort tinyint unsigned not null comment '顺序',
    status tinyint unsigned not null default 0 comment '状态 0 禁用，1 启用',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
) comment '分类' ;

-- 菜品表
create table dish(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(20) not null unique comment '菜品名称',
    category_id int unsigned not null comment '菜品分类ID',
    price decimal(8, 2) not null comment '菜品价格',
    image varchar(300) not null comment '菜品图片',
    description varchar(200) comment '描述信息',
    status tinyint unsigned not null default 0 comment '状态, 0 停售 1 起售',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
) comment '菜品';

-- 套餐表
create table setmeal(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(20) not null unique comment '套餐名称',
    category_id int unsigned not null comment '分类id',
    price decimal(8, 2) not null comment '套餐价格',
    image varchar(300) not null comment '图片',
    description varchar(200) comment '描述信息',
    status tinyint unsigned not null default 0 comment '状态 0:停用 1:启用',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
)comment '套餐' ;

-- 套餐菜品关联表
create table setmeal_dish(
    id int unsigned primary key auto_increment comment '主键ID',
    setmeal_id int unsigned not null comment '套餐id ',
    dish_id int unsigned not null comment '菜品id',
    copies tinyint unsigned not null comment '份数'
)comment '套餐菜品中间表';

-- ================================== 导入测试数据 ====================================
-- category
insert into category (id, type, name, sort, status, create_time, update_time) values (1, 1, '酒水饮料', 10, 1, '2022-08-09 22:09:18', '2022-08-09 22:09:18');
insert into category (id, type, name, sort, status, create_time, update_time) values (2, 1, '传统主食', 9, 1, '2022-08-09 22:09:32', '2022-08-09 22:18:53');
insert into category (id, type, name, sort, status, create_time, update_time) values (3, 2, '人气套餐', 12, 1, '2022-08-09 22:11:38', '2022-08-10 11:04:40');
insert into category (id, type, name, sort, status, create_time, update_time) values (4, 2, '商务套餐', 13, 1, '2022-08-09 22:14:10', '2022-08-10 11:04:48');
insert into category (id, type, name, sort, status, create_time, update_time) values (5, 1, '经典川菜', 6, 1, '2022-08-09 22:17:42', '2022-08-09 22:17:42');
insert into category (id, type, name, sort, status, create_time, update_time) values (6, 1, '新鲜时蔬', 7, 1, '2022-08-09 22:18:12', '2022-08-09 22:18:28');
insert into category (id, type, name, sort, status, create_time, update_time) values (7, 1, '汤类', 11, 1, '2022-08-10 10:51:47', '2022-08-10 10:51:47');

-- dish
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (1,'王老吉', 1, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/41bfcacf-7ad4-4927-8b26-df366553a94c.png', '', 1, '2022-06-09 22:40:47', '2022-06-09 22:40:47');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (2,'北冰洋', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4451d4be-89a2-4939-9c69-3a87151cb979.png', '还是小时候的味道', 1, '2022-06-10 09:18:49', '2022-06-10 09:18:49');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (3,'雪花啤酒', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/bf8cbfc1-04d2-40e8-9826-061ee41ab87c.png', '', 1, '2022-06-10 09:22:54', '2022-06-10 09:22:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (4,'米饭', 2, 2.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/76752350-2121-44d2-b477-10791c23a8ec.png', '精选五常大米', 1, '2022-06-10 09:30:17', '2022-06-10 09:30:17');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (5,'馒头', 2, 1.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/475cc599-8661-4899-8f9e-121dd8ef7d02.png', '优质面粉', 1, '2022-06-10 09:34:28', '2022-06-10 09:34:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (6,'老坛酸菜鱼', 5, 56.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png', '原料：汤，草鱼，酸菜', 1, '2022-06-10 09:40:51', '2022-06-10 09:40:51');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (7,'经典酸菜鮰鱼', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png', '原料：酸菜，江团，鮰鱼', 1, '2022-06-10 09:46:02', '2022-06-10 09:46:02');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (8,'蜀味水煮草鱼', 5, 38.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png', '原料：草鱼，汤', 1, '2022-06-10 09:48:37', '2022-06-10 09:48:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (9,'清炒小油菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/3613d38e-5614-41c2-90ed-ff175bf50716.png', '原料：小油菜', 1, '2022-06-10 09:51:46', '2022-06-10 09:51:46');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (10,'蒜蓉娃娃菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4879ed66-3860-4b28-ba14-306ac025fdec.png', '原料：蒜，娃娃菜', 1, '2022-06-10 09:53:37', '2022-06-10 09:53:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (11,'清炒西兰花', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/e9ec4ba4-4b22-4fc8-9be0-4946e6aeb937.png', '原料：西兰花', 1, '2022-06-10 09:55:44', '2022-06-10 09:55:44');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (12,'炝炒圆白菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/22f59feb-0d44-430e-a6cd-6a49f27453ca.png', '原料：圆白菜', 1, '2022-06-10 09:58:35', '2022-06-10 09:58:35');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (13,'清蒸鲈鱼', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png', '原料：鲈鱼', 1, '2022-06-10 10:12:28', '2022-06-10 10:12:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (14,'东坡肘子', 5, 138.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a80a4b8c-c93e-4f43-ac8a-856b0d5cc451.png', '原料：猪肘棒', 1, '2022-06-10 10:24:03', '2022-06-10 10:24:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (15,'梅菜扣肉', 5, 58.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/6080b118-e30a-4577-aab4-45042e3f88be.png', '原料：猪肉，梅菜', 1, '2022-06-10 10:26:03', '2022-06-10 10:26:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (16,'剁椒鱼头', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png', '原料：鲢鱼，剁椒', 1, '2022-06-10 10:28:54', '2022-06-10 10:28:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (17,'馋嘴牛蛙', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/7a55b845-1f2b-41fa-9486-76d187ee9ee1.png', '配料：鲜活牛蛙，丝瓜，黄豆芽', 1, '2022-06-10 10:37:52', '2022-06-10 10:37:52');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (18,'鸡蛋汤', 7, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c09a0ee8-9d19-428d-81b9-746221824113.png', '配料：鸡蛋，紫菜', 1, '2022-06-10 10:54:25', '2022-06-10 10:54:25');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (19,'平菇豆腐汤', 7, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/16d0a3d6-2253-4cfc-9b49-bf7bd9eb2ad2.png', '配料：豆腐，平菇', 1, '2022-06-10 10:55:02', '2022-06-10 10:55:02');

-- setmeal
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (1, 4, '商务套餐A', 20.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/21a5ed3a-97f6-447a-af9d-53deabfb5661.png', '2022-06-10 10:58:09', '2022-06-10 10:58:09');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (2, 4, '商务套餐B', 22.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8d0075f8-9008-4390-94ca-2ca631440304.png', '2022-06-10 11:00:13', '2022-06-10 11:11:37');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (3, 3, '人气套餐A', 49.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8979566b-0e17-462b-81d8-8dbace4138f4.png', '2022-06-10 11:11:23', '2022-06-10 11:11:23');

-- setmeal_dish
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (1, 1, 1, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (2, 1, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (3, 1, 11, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (4, 2, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (5, 2, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (6, 2, 9, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (7, 3, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (8, 3, 6, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (9, 3, 5, 1);

END

学习自：黑马程序员——JavaWeb课程