1. 查询语句语法规则

• “[ ]” 包含的内容可以省略；

• “{ }” 包含的内容必须存在；

• DISTINCT： 设定 **distinct** 可以去掉重复记录；

• AS： 表明或字段名过长时，可以用 **AS** 关键字起别名，也可省略不写，但查询语句复杂时可读性会很差；

• GROUP BY： 按组分类显示查询出的数据；

• HAVING：**GROUP BY** 分组时依赖的分组条件；

• ORDER BY： 将查询出来的结果集按照一定顺序排序完成；

• LIMIT： 限制显示查询结果的条数；

2. WHERE 和 ON 的区别

WHERE	ON
用来过滤单个表或过滤连接后的结果集；	用来执行 join 操作时指定连接条件；
可以在没有连接的情况下单独使用；	只能在连接操作时使用，用于确定如何将两个或多个表中的行进行匹配；
子句的条件是在连接操作之后应用；	子句的条件基于两个表之间的关联列；
可以引用任何列，不局限于连接条件的列；	在连接操作过程中用于确定哪些列应该被连接；

3. 实验准备

SQL 脚本：

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80016
 Source Host           : localhost:3306
 Source Schema         : git_db

 Target Server Type    : MySQL
 Target Server Version : 80016
 File Encoding         : 65001

 Date: 08/05/2024 23:31:48
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ----------------------------
-- Table structure for dish
-- ----------------------------
DROP TABLE IF EXISTS `dish`;
CREATE TABLE `dish`  (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '菜品名称',
  `category_id` bigint(20) NOT NULL COMMENT '菜品分类id',
  `price` decimal(10, 2) NULL DEFAULT NULL COMMENT '菜品价格',
  `code` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '商品码',
  `image` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '图片',
  `description` varchar(400) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '描述信息',
  `status` int(11) NOT NULL DEFAULT 1 COMMENT '0 停售 1 起售',
  `sort` int(11) NOT NULL DEFAULT 0 COMMENT '顺序',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  `create_user` bigint(20) NOT NULL COMMENT '创建人',
  `update_user` bigint(20) NOT NULL COMMENT '修改人',
  `is_deleted` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_dish_name`(`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '菜品管理' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dish
-- ----------------------------
INSERT INTO `dish` VALUES (1397849739276890114, '辣子', 1397844263642378242, 7800.00, '222222222', 'FmftrRvdL8_XcB5k6hYOhLhGEooz', '来自鲜嫩美味的小鸡，值得一尝', 1, 0, '2021-05-27 09:38:43', '2024-01-05 17:25:18', 1, 1737037426064642049, 1);
INSERT INTO `dish` VALUES (1397850140982161409, '毛氏红烧肉', 1397844263642378242, 6800.00, '123412341234', 'FmJKaugRK038RldaGmXXdRIcgxHS', '毛氏红烧肉毛氏红烧肉，确定不来一份？', 1, 0, '2021-05-27 09:40:19', '2023-12-26 10:52:55', 1, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1397850392090947585, '组庵鱼翅', 1397844263642378242, 4800.00, '123412341234', 'FgOalYD18aSVNp3Jmd5hmpPpvwvz', '组庵鱼翅，看图足以表明好吃程度', 1, 0, '2021-05-27 09:41:19', '2024-01-05 17:25:18', 1, 1737037426064642049, 1);
INSERT INTO `dish` VALUES (1397850851245600769, '霸王别姬', 1397844263642378242, 12800.00, '123412341234', 'FliwXr-EIkopolHKj-Fw55vZKQPL', '还有什么比霸王别姬更美味的呢？', 0, 0, '2021-05-27 09:43:08', '2023-12-27 19:15:23', 1, 1722873762260840450, 1);
INSERT INTO `dish` VALUES (1397851099502260226, '全家福', 1397844263642378242, 11800.00, '23412341234', 'FtyT7Hfvwk2os6eLd2sbQfwJ7saW', '别光吃肉啦，来份全家福吧，让你长寿又美味', 0, 0, '2021-05-27 09:44:08', '2023-12-27 19:15:23', 1, 1722873762260840450, 1);
INSERT INTO `dish` VALUES (1397851370462687234, '邵阳猪血丸子', 1397844263642378242, 13800.00, '1246812345678', 'Fq3HHLNj5LUwdtbA2oYHFDbD8Zcc', '看，美味不？来嘛来嘛，这才是最爱吖', 1, 0, '2021-05-27 09:45:12', '2023-12-26 14:33:00', 1, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1397851668262465537, '口味蛇', 1397844263642378242, 16800.00, '1234567812345678', 'FnVsJK_n0qCgLqiCcmyXjtwig7sR', '爬行界的扛把子，东兴-口味蛇，让你欲罢不能', 0, 0, '2021-05-27 09:46:23', '2023-12-27 19:15:23', 1, 1722873762260840450, 1);
INSERT INTO `dish` VALUES (1397852391150759938, '辣子鸡丁', 1397844303408574465, 8800.00, '2346812468', 'FmftrRvdL8_XcB5k6hYOhLhGEooz', '辣子鸡丁，辣子鸡丁，永远的魂', 1, 0, '2021-05-27 09:49:16', '2023-12-27 11:05:20', 1, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1397853183287013378, '麻辣兔头', 1397844303408574465, 19800.00, '123456787654321', 'Fvp6Rux94uPXaq9qBBKRrB_XpNyR', '麻辣兔头的详细制作，麻辣鲜香，色泽红润，回味悠长', 1, 0, '2021-05-27 09:52:24', '2023-12-27 11:06:25', 1, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1397853709101740034, '蒜泥白肉', 1397844303408574465, 9800.00, '1234321234321', 'FraH3a_GWeUcDBPOlpzfETbmd0Uy', '多么的有食欲啊', 1, 0, '2021-05-27 09:54:30', '2023-12-27 11:07:14', 1, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1397862477831122945, '上汤焗龙虾', 1397844391040167938, 108800.00, '', 'Fm07DblGbESyZm7EwJiHXYjhxD5K', '上汤焗龙虾是一道色香味俱全的传统名菜，属于粤菜系。此菜以龙虾为主料，配以高汤制成的一道海鲜美食。本品肉质洁白细嫩，味道鲜美，蛋白质含量高，脂肪含量低，营养丰富。是色香味俱全的传统名菜。', 1, 0, '2021-05-27 10:29:20', '2023-12-27 11:04:23', 1, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1413342036832100354, '北冰洋', 1413341197421846529, 500.00, '', 'Fh3638DI4tN5vt89JaQsoRN3IP27', '', 1, 0, '2021-07-09 11:39:35', '2024-01-05 17:25:18', 1, 1737037426064642049, 1);
INSERT INTO `dish` VALUES (1413384757047271425, '王老吉', 1413341197421846529, 500.00, '', 'Fir_CMcjZOaho4R4ZhjZGOa349BV', '', 1, 0, '2021-07-09 14:29:20', '2023-12-27 11:07:45', 1, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1413385247889891330, '米饭', 1413384954989060097, 200.00, '', 'FpC_XrOuJ5WeKnIQTmUMQjbJW9X0', '', 1, 0, '2021-07-09 14:31:17', '2024-01-05 17:20:57', 1, 1737037426064642049, 0);
INSERT INTO `dish` VALUES (1537734154819469314, '剁椒鱼头', 1397844303408574465, 2000.00, '', 'FqCDofIDL-zsDIWcp-Cfb0Z8gP7L', '无', 0, 0, '2022-06-17 17:49:27', '2023-12-27 19:15:23', 1, 1722873762260840450, 1);
INSERT INTO `dish` VALUES (1539187286716006402, '宫保鸡丁', 1397844303408574465, 2000.00, '', 'Fp9olpQsWJKHUgVQzZgUPLtMiFWI', '很下饭', 0, 0, '2022-06-21 18:03:41', '2023-12-27 19:15:23', 1, 1722873762260840450, 1);
INSERT INTO `dish` VALUES (1739535644308574209, '红烧狮子头', 1397844263642378242, 4399.00, '1231231234567', 'FiKfhSo5St-xQAW57gtddZND-cuR', '', 1, 0, '2023-12-26 14:36:31', '2023-12-26 14:36:31', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish` VALUES (1739536700186857474, '饭小满', 1397844263642378242, 6899.00, '1231231234567', 'FibIOLVZNQ5lCiybqQeLsR1RY2Mf', '汤小鲜和饭小满，惠子专属', 0, 0, '2023-12-26 14:40:43', '2024-01-05 17:25:18', 1722873762260840450, 1737037426064642049, 1);
INSERT INTO `dish` VALUES (1743157716142280706, '蛋蛋', 1397844263642378242, 2334.00, '1231231234567', 'Fpt-V3t4igDXkgtkFiUdMiOEX83N', '', 1, 0, '2024-01-05 14:29:20', '2024-01-05 14:31:50', 1722873762260840450, 1722873762260840450, 1);
INSERT INTO `dish` VALUES (1743201852635525122, '东北菜', 1737398571216732161, 58.80, '1231231234567', 'FrU7WUw7HOQ4Mnv-GSQ7tma0zTNR', '', 1, 0, '2024-01-05 17:24:43', '2024-01-05 17:25:18', 1737037426064642049, 1737037426064642049, 1);

-- ----------------------------
-- Table structure for dish_flavor
-- ----------------------------
DROP TABLE IF EXISTS `dish_flavor`;
CREATE TABLE `dish_flavor`  (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `dish_id` bigint(20) NOT NULL COMMENT '菜品',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '口味名称',
  `value` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '口味数据list',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  `create_user` bigint(20) NOT NULL COMMENT '创建人',
  `update_user` bigint(20) NOT NULL COMMENT '修改人',
  `is_deleted` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '菜品口味关系表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dish_flavor
-- ----------------------------
INSERT INTO `dish_flavor` VALUES (1397849417888346113, 1397849417854791681, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 09:37:27', '2021-05-27 09:37:27', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397849936421761025, 1397849936404983809, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-27 09:39:30', '2021-05-27 09:39:30', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397849936438538241, 1397849936404983809, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 09:39:30', '2021-05-27 09:39:30', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397850630734262274, 1397850630700707841, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-27 09:42:16', '2021-05-27 09:42:16', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397850630755233794, 1397850630700707841, '辣度', '[\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 09:42:16', '2021-05-27 09:42:16', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397853423486414850, 1397853423461249026, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 09:53:22', '2021-05-27 09:53:22', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397853890283089922, 1397853890262118402, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 09:55:13', '2021-05-27 09:55:13', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397854133632413697, 1397854133603053569, '温度', '[\"热饮\",\"常温\",\"去冰\",\"少冰\",\"多冰\"]', '2021-05-27 09:56:11', '2021-05-27 09:56:11', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397854652623007745, 1397854652581064706, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-27 09:58:15', '2021-05-27 09:58:15', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397854652635590658, 1397854652581064706, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 09:58:15', '2021-05-27 09:58:15', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397854865735593986, 1397854865672679425, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 09:59:06', '2021-05-27 09:59:06', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397855742303186946, 1397855742273826817, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:02:35', '2021-05-27 10:02:35', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397855906497605633, 1397855906468245506, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-27 10:03:14', '2021-05-27 10:03:14', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397856190573621250, 1397856190540066818, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:04:21', '2021-05-27 10:04:21', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397859056709316609, 1397859056684150785, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:15:45', '2021-05-27 10:15:45', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397859277837217794, 1397859277812051969, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:16:37', '2021-05-27 10:16:37', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397859487502086146, 1397859487476920321, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:17:27', '2021-05-27 10:17:27', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397859757061615618, 1397859757036449794, '甜味', '[\"无糖\",\"少糖\",\"半躺\",\"多糖\",\"全糖\"]', '2021-05-27 10:18:32', '2021-05-27 10:18:32', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397860242086735874, 1397860242057375745, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:20:27', '2021-05-27 10:20:27', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397860963918065665, 1397860963880316929, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:23:19', '2021-05-27 10:23:19', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397861135754506242, 1397861135733534722, '甜味', '[\"无糖\",\"少糖\",\"半躺\",\"多糖\",\"全糖\"]', '2021-05-27 10:24:00', '2021-05-27 10:24:00', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397861370035744769, 1397861370010578945, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-27 10:24:56', '2021-05-27 10:24:56', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397861683459305474, 1397861683434139649, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-27 10:26:11', '2021-05-27 10:26:11', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397861898467717121, 1397861898438356993, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-27 10:27:02', '2021-05-27 10:27:02', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1397862198054268929, 1397862198033297410, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-27 10:28:14', '2021-05-27 10:28:14', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398089545865015297, 1398089545676271617, '温度', '[\"热饮\",\"常温\",\"去冰\",\"少冰\",\"多冰\"]', '2021-05-28 01:31:38', '2021-05-28 01:31:38', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398089782323097601, 1398089782285348866, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:32:34', '2021-05-28 01:32:34', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398090003262255106, 1398090003228700673, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-28 01:33:27', '2021-05-28 01:33:27', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398090264554811394, 1398090264517062657, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-28 01:34:29', '2021-05-28 01:34:29', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398090455399837698, 1398090455324340225, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:35:14', '2021-05-28 01:35:14', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398090685449023490, 1398090685419663362, '温度', '[\"热饮\",\"常温\",\"去冰\",\"少冰\",\"多冰\"]', '2021-05-28 01:36:09', '2021-05-28 01:36:09', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398090825358422017, 1398090825329061889, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-28 01:36:43', '2021-05-28 01:36:43', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398091007051476993, 1398091007017922561, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:37:26', '2021-05-28 01:37:26', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398091296164851713, 1398091296131297281, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:38:35', '2021-05-28 01:38:35', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398091546531246081, 1398091546480914433, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2021-05-28 01:39:35', '2021-05-28 01:39:35', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398091729809747969, 1398091729788776450, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:40:18', '2021-05-28 01:40:18', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398091889499484161, 1398091889449152513, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:40:56', '2021-05-28 01:40:56', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398092095179763713, 1398092095142014978, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:41:45', '2021-05-28 01:41:45', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398092283877306370, 1398092283847946241, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:42:30', '2021-05-28 01:42:30', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398094018939236354, 1398094018893099009, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:49:24', '2021-05-28 01:49:24', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1398094391494094850, 1398094391456346113, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-05-28 01:50:53', '2021-05-28 01:50:53', 1, 1, 0);
INSERT INTO `dish_flavor` VALUES (1399574026165727233, 1399305325713600514, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2021-06-01 03:50:25', '2021-06-01 03:50:25', 1399309715396669441, 1399309715396669441, 0);
INSERT INTO `dish_flavor` VALUES (1739479372016144386, 1397850140982161409, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2023-12-26 10:52:55', '2023-12-26 10:52:55', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739479372066476033, 1397850140982161409, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2023-12-26 10:52:55', '2023-12-26 10:52:55', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739532310017249283, 1739532310017249282, '麻辣', '[\"香辣\"]', '2023-12-26 14:23:16', '2023-12-26 14:23:16', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739534757175529474, 1397851370462687234, '温度', '[\"热饮\",\"常温\",\"去冰\",\"少冰\",\"多冰\"]', '2023-12-26 14:33:00', '2023-12-26 14:33:00', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739534757175529475, 1397851370462687234, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2023-12-26 14:33:00', '2023-12-26 14:33:00', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739534757242638338, 1397851370462687234, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2023-12-26 14:33:00', '2023-12-26 14:33:00', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739535644308574210, 1739535644308574209, '辣味', '[\"麻辣\"]', '2023-12-26 14:36:31', '2023-12-26 14:36:31', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739844644069826562, 1397862477831122945, '辣度', '[\"不辣\",\"微辣\",\"中辣\"]', '2023-12-27 11:04:23', '2023-12-27 11:04:23', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739844884004986881, 1397852391150759938, '忌口', '[\"不要葱\",\"不要香菜\",\"不要辣\"]', '2023-12-27 11:05:20', '2023-12-27 11:05:20', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739844884004986882, 1397852391150759938, '辣度', '[\"不辣\",\"微辣\",\"重辣\"]', '2023-12-27 11:05:20', '2023-12-27 11:05:20', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739845155649085441, 1397853183287013378, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2023-12-27 11:06:25', '2023-12-27 11:06:25', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739845364533813249, 1397853709101740034, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2023-12-27 11:07:14', '2023-12-27 11:07:14', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739845491591864322, 1413384757047271425, '温度', '[\"常温\",\"冷藏\"]', '2023-12-27 11:07:45', '2023-12-27 11:07:45', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739845691626610689, 1413342036832100354, '温度', '[\"常温\",\"冷藏\"]', '2023-12-27 11:08:32', '2023-12-27 11:08:32', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739845919285043202, 1739536700186857474, '甜味', '[\"香甜\"]', '2023-12-27 11:09:27', '2023-12-27 11:09:27', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846471691657218, 1397850392090947585, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2023-12-27 11:11:38', '2023-12-27 11:11:38', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846471691657219, 1397850392090947585, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2023-12-27 11:11:38', '2023-12-27 11:11:38', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846656018735105, 1539187286716006402, '甜味', '[\"少糖\"]', '2023-12-27 11:12:22', '2023-12-27 11:12:22', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846656018735106, 1539187286716006402, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2023-12-27 11:12:22', '2023-12-27 11:12:22', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846656018735107, 1539187286716006402, '辣度', '[\"中辣\",\"重辣\"]', '2023-12-27 11:12:22', '2023-12-27 11:12:22', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846825481199617, 1537734154819469314, '甜味', '[\"全糖\"]', '2023-12-27 11:13:03', '2023-12-27 11:13:03', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846825481199618, 1537734154819469314, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2023-12-27 11:13:03', '2023-12-27 11:13:03', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846825481199619, 1537734154819469314, '温度', '[\"常温\"]', '2023-12-27 11:13:03', '2023-12-27 11:13:03', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739846825552502786, 1537734154819469314, '辣度', '[\"微辣\"]', '2023-12-27 11:13:03', '2023-12-27 11:13:03', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739847028082860033, 1397851668262465537, '温度', '[\"热饮\",\"常温\",\"去冰\",\"少冰\",\"多冰\"]', '2023-12-27 11:13:51', '2023-12-27 11:13:51', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739847213240410114, 1397851099502260226, '忌口', '[\"不要葱\",\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2023-12-27 11:14:35', '2023-12-27 11:14:35', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739847213240410115, 1397851099502260226, '辣度', '[\"不辣\",\"微辣\",\"中辣\"]', '2023-12-27 11:14:35', '2023-12-27 11:14:35', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739847432023695361, 1397850851245600769, '忌口', '[\"不要蒜\",\"不要香菜\",\"不要辣\"]', '2023-12-27 11:15:27', '2023-12-27 11:15:27', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1739847432023695362, 1397850851245600769, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2023-12-27 11:15:27', '2023-12-27 11:15:27', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1743156290007687170, 1397849739276890114, '辣度', '[\"不辣\",\"微辣\",\"中辣\",\"重辣\"]', '2024-01-05 14:23:40', '2024-01-05 14:23:40', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1743157716142280707, 1743157716142280706, '1234', '[\"菜品\"]', '2024-01-05 14:29:20', '2024-01-05 14:29:20', 1722873762260840450, 1722873762260840450, 0);
INSERT INTO `dish_flavor` VALUES (1743201954439671809, 1743201852635525122, '甜味', '[\"7分甜\"]', '2024-01-05 17:25:08', '2024-01-05 17:25:08', 1737037426064642049, 1737037426064642049, 0);


SET FOREIGN_KEY_CHECKS = 1;

实验环境

操作系统：Windows 11 专业版

MySQL版本号：8.0.11

可视化工具：Navicat Premium 12

WHERE | AND | ON

where…and…

    select name,category_id,price,description from dish 
      where category_id='1397844303408574465' 
      and price=2000;

left join … on …

    select dish.name,category_id,price,description,dish_flavor.name,dish_flavor.`value` from dish 
      left join dish_flavor 
      on dish.id = dish_flavor.dish_id;

left join … on … where …

    select dish.name,category_id,price,description,dish_flavor.name,dish_flavor.`value` 
    from dish 
    left join dish_flavor 
    on dish.id = dish_flavor.dish_id
    where dish_flavor.name='辣度';

left join … on … and …

    select dish.name,category_id,price,description,dish_flavor.name,dish_flavor.`value` 
    from dish 
    left join dish_flavor 
    on dish.id = dish_flavor.dish_id
    and dish_flavor.name='辣度';

left join … on … where … and …

    select dish.name,category_id,price,description,dish_flavor.name,dish_flavor.`value` 
    from dish 
    left join dish_flavor 
    on dish.id = dish_flavor.dish_id
    where dish_flavor.name='辣度'
    and dish.name='组庵鱼翅';

left join … on … and … where …

    select dish.name,category_id,price,description,dish_flavor.name,dish_flavor.`value` 
    from dish 
    left join dish_flavor 
    on dish.id = dish_flavor.dish_id
    and dish_flavor.name='辣度'
    where dish.name='组庵鱼翅';

结论

• **left join ... on ... where ...** <= **left join ... on ... and ...**

1. where 会对含有 null 值的记录进行过滤，and 不会；

• **left join ... on ... where ... and ....** = **left join ... on ... and ... where ...**

2. 前者经过 where 对含有 null 值的记录进行了过滤，后者在 and 语句完成后对结果集进行了 null 值记录过滤，所以二者的效果几乎一样；

• 由于数据库记录条数较少，在进行查询性能测试时误差在0.03秒左右，作不了考证，故没有对此作叙述，使用了 <= 和 = 来表述两条查询语句之间的关系qwq

总结

1. where 用于对结果集的任何列进行过滤，包括没有参与连接的列；

   a. 在连接之后应用，不会影响连接的执行计划，仅影响返回的行数；

2. on 用于指定连接两个或多个表中的连接条件，通常基于两个表之间的关联列；

   a. 影响连接的执行计划，减少参与连接的行数，从而提高查询性能。

3. and 用于条件追加，不会对含有 null 值的记录进行过滤；

---