如何在 MySQL 中从多个表中进行选择

Rayven Esplanada 2023年1月30日 2020年11月7日
  1. 使用 GROUP BY food 从多个表中 SELECT 的方法
  2. 在 MySQL 中使用 JOIN 从多个表中 SELECT
  3. 在 MySQL 中使用 GROUP_CONCAT() 并处理结果
如何在 MySQL 中从多个表中进行选择

本教程介绍了如何在一个脚本中使用 MySQL 从多个表中查询 SELECT

让我们演示一种情况:

SELECT name, price, details, type,  FROM food, food_order  WHERE breakfast.id = 'breakfast_id'

现在,让我们想象一下每个 FROM 条目的示例表。

  • food
food_id name price options
1 Eggs 10.00 Scrambled, Sunny Side, Boiled
2 Ice cream 30.00 Vanilla, Strawberry, Chocolate
3 Ramen 12.00 Regular, Spicy
  • food_menu
order_id photo food_id
1 eggs_scrambled.jpg 1
2 eggs_sunnyside.jpg 1
3 eggs_boiled.png 1
4 icecream_vanilla.jpg 2
5 icecream_strawberry.jpg 2
6 ice_cream_chocolate.jpg 2
7 ramen_regular.jpg 3
8 ramen_spicy.jpg 3

根据上面的表,我们可以看到鸡蛋有 3 张照片,冰淇淋也有 3 张,而拉面有 2 张,我们要输出的是一个 foodfood_menu 的聚合表,将所有的食物显示在一起,并结合菜单中相应的照片。

如果我们对此进行查询。

SELECT name, price, options, photo 
FROM food, food_menu 
WHERE food_id = '1'

结果会是这样的:

name price options photo
1 Eggs Scrambled, Sunny Side, Boiled eggs_sunnyside.jpg
2 Eggs Scrambled, Sunny Side, Boiled eggs_scrambled.jpg
3 Eggs Scrambled, Sunny Side, Boiled eggs_boiled.jpg

数据是重复的,因为 food_menu 中有多行与 food 相关的数据。在本例中,food_menu 中有 3 张照片是直接与 Eggs 绑定的。

简单的查询不可能将 food_menu 中的所有条目连接到一条记录中,因为它们都被认为是与表 food 相关的独立实体。

如果你想同时在一行中查询 foodfood_menu,那么这里有几个选项。

使用 GROUP BY food 从多个表中 SELECT 的方法

这种方法使用 GROUP BY 将两张表汇总到一个结果中。但缺点是,你只能得到 food_menu 的第一个实例,因为我们强迫结果是唯一的。

下面是对 GROUP BY food 表的查询。

SELECT name, price, options, photo
FROM food, food_menu
WHERE food_id = '1' 
GROUP BY food_id

它将显示以下结果。

name price options photo
1 Eggs Scrambled, Sunny Side, Boiled eggs_sunnyside.jpg

现在我们已经满足了条件,尽管只返回了一张照片,这是查询发现的第一个 food_menu 实例。

在 MySQL 中使用 JOIN 从多个表中 SELECT

这种方法利用了 SQL 的 JOINRIGHT JOIN 命令。

我们在脚本中没有 2 个 FROM 条件,而是根据它的 food_id 外键来连接 food_menu。我们将 f 作为 food 的别名,将 fm 作为 food_menu 的别名。

SELECT f.name, f.price, f.options, fm.food_menu
FROM food AS f
JOIN food_menu AS fm ON fm.food_id = f.food_id
WHERE food_id = '1'
GROUP BY f.food_id

虽然这个方法与前一个方法不同,但它产生的结果是一样的。它返回的是 food_menu 的第一个实例,因为 GROUP BY 迫使查询根据其条件返回唯一的行。

name price options photo
1 Eggs Scrambled, Sunny Side, Boiled eggs_sunnyside.jpg

在 MySQL 中使用 GROUP_CONCAT() 并处理结果

上一个解决方案的变通方法是使用 GROUP_CONCAT()food_menu 的所有结果放在一个单一的字符串中,因此可以将他们的所有记录放在一个行中。

什么是 GROUP_CONCAT()

GROUP_CONCAT 是一个将多行数据合并到一个字段的函数。它是一个 GROUP BY 的特殊函数,如果组中至少包含 1 个非空值,则返回一个修改后的字符串。否则就返回 NULL

我们将上面的查询修改为 GROUP_CONCAT() 照片列,将结果连成一个字符串。

SELECT name, price, options, GROUP_CONCAT(photo, ', ')
FROM food, food_menu
WHERE food_id = '1' 
GROUP BY food_id

通过这样做,我们将 food_menu 的照片列连接起来,这样每条 food 的唯一条目将只产生一条记录。结果将如下所示。

name price options photo
1 Eggs Scrambled, Sunny Side, Boiled eggs_sunnyside.jpg,eggs_scrambled.jpg,eggs_boiled.jpg

正如你所看到的,它连接了 3 个与 Eggs 相关的 food 表中的 photo 列。

如果我们把 GROUP BYWHERE 条件去掉的话

SELECT name, price, options, GROUP_CONCAT(photo, ', ')
FROM food, food_menu

结果是这样的

name price options photo
1 Eggs Scrambled, Sunny Side, Boiled eggs_sunnyside.jpg,eggs_scrambled.jpg,eggs_boiled.jpg
2 Ice Cream Vanilla, Strawberry, Chocolate icecream_vanilla.jpg,icecream_strawberry.jpg,icecream_chocolate.jpg
3 Ramen Regular, Spicy ramen_regular.jpg,ramen_spicy.jpg

在使用 GROUP_CONCAT() 时一定要小心,如果你的字符串中包含一个逗号,而你的 CONCAT 定界符也是一个逗号,那么在解析你的列时就会破坏你的数据。

所以在使用这个函数之前,请确保你的定界符对于你要操作的列来说是一个无效的字符。

Rayven Esplanada avatar Rayven Esplanada avatar

Skilled in Python, Java, Spring Boot, AngularJS, and Agile Methodologies. Strong engineering professional with a passion for development and always seeking opportunities for personal and career growth. A Technical Writer writing about comprehensive how-to articles, environment set-ups, and technical walkthroughs. Specializes in writing Python, Java, Spring, and SQL articles.

LinkedIn

相关文章 - MySQL Table