MySQL은 주문 시스템의 요리 맛 관리 기능을 구현합니다.

-- 创建菜品表
CREATE TABLE dishes (
  dish_id INT PRIMARY KEY,
  dish_name VARCHAR(255),
  price DECIMAL(10, 2)
);

-- 创建口味表
CREATE TABLE flavours (
  flavour_id INT PRIMARY KEY,
  flavour_name VARCHAR(255)
);

-- 创建连接表
CREATE TABLE dish_flavour (
  dish_id INT,
  flavour_id INT,
  PRIMARY KEY (dish_id, flavour_id),
  FOREIGN KEY (dish_id) REFERENCES dishes(dish_id),
  FOREIGN KEY (flavour_id) REFERENCES flavours(flavour_id)
);

-- 插入菜品数据
INSERT INTO dishes (dish_id, dish_name, price) VALUES
(1, '宫保鸡丁', 28.0),
(2, '鱼香肉丝', 32.0),
(3, '水煮鱼', 38.0);

-- 插入口味数据
INSERT INTO flavours (flavour_id, flavour_name) VALUES
(1, '微辣'),
(2, '中辣'),
(3, '重辣'),
(4, '不辣');

-- 插入菜品口味关系数据
INSERT INTO dish_flavour (dish_id, flavour_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 4),
(3, 1),
(3, 3),
(3, 4);

SELECT d.dish_name, f.flavour_name
FROM dishes d
JOIN dish_flavour df ON d.dish_id = df.dish_id
JOIN flavours f ON df.flavour_id = f.flavour_id;

SELECT f.flavour_name
FROM dishes d
JOIN dish_flavour df ON d.dish_id = df.dish_id
JOIN flavours f ON df.flavour_id = f.flavour_id
WHERE d.dish_name = '宫保鸡丁';

SELECT d.dish_name
FROM dishes d
JOIN dish_flavour df ON d.dish_id = df.dish_id
JOIN flavours f ON df.flavour_id = f.flavour_id
WHERE f.flavour_name = '重辣';