Mysql SQL查询从多个表返回数据

发布于09月18日

我想知道以下几点:

如何从数据库中的多个表中获取数据？
有哪些方法可以做到这一点？
什么是联合和联合，它们之间有什么不同？
我应该在什么时候比较每一个？

我计划在我的(例如-PHP)应用程序中使用它，但不想对数据库运行多个查询，在一个查询中从多个表获取数据需要哪些选项？

注意:我写这篇文章是因为我希望能够链接到一个关于我在PHP队列中经常遇到的许多问题的编写良好的指南，所以当我发布答案时，我可以链接到这篇文章以了解更多细节.

答案包括以下几点:

Part 1 - Joins and Unions

答案包括:

第一部分
第二部分

有许多方法可以从数据库中的多个表中检索数据.在这个答案中，我将使用ANSI-92连接语法.这可能不同于其他一些使用较旧ANSI-89语法的教程(如果你习惯了89，可能看起来不那么直观——但我只能说try 一下)，因为当查询开始变得更复杂时，它更容易理解.为什么要用它？是否有性能提升？short answer是否定的，但一旦你习惯了，阅读起来就容易多了.阅读其他人使用这种语法编写的查询更容易.

我还将使用一个小型停车场的概念，这个停车场有一个数据库来跟踪它有哪些可用的汽车.店主雇佣你做他的IT电脑助手，希望你能马上把他要求的数据交给他.

我制作了许多查找表，最终的表将使用这些表.这将为我们提供一个合理的工作模型.首先，我将针对具有以下 struct 的示例数据库运行查询.我将试着思考在开始时所犯的常见错误，并解释它们出了什么问题——当然也会展示如何纠正它们.

第一张表只是一个 colored颜色列表，这样我们就知道停车场里有什么 colored颜色 .

mysql> create table colors(id int(3) not null auto_increment primary key, 
    -> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into colors (color, paint) values ('Red', 'Metallic'), 
    -> ('Green', 'Gloss'), ('Blue', 'Metallic'), 
    -> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from colors;
+----+-------+----------+
| id | color | paint    |
+----+-------+----------+
|  1 | Red   | Metallic |
|  2 | Green | Gloss    |
|  3 | Blue  | Metallic |
|  4 | White | Gloss    |
|  5 | Black | Gloss    |
+----+-------+----------+
5 rows in set (0.00 sec)

brands表列出了caryard可能销售的不同品牌的汽车.

mysql> create table brands (id int(3) not null auto_increment primary key, 
    -> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into brands (brand) values ('Ford'), ('Toyota'), 
    -> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from brands;
+----+--------+
| id | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  3 | Nissan |
|  4 | Smart  |
|  5 | BMW    |
+----+--------+
5 rows in set (0.00 sec)

模型表将涵盖不同类型的汽车，使用不同的汽车类型比实际的汽车模型更简单.

mysql> create table models (id int(3) not null auto_increment primary key, 
    -> model varchar(15));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| model | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from models;
+----+--------+
| id | model  |
+----+--------+
|  1 | Sports |
|  2 | Sedan  |
|  3 | 4WD    |
|  4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)

最后，把所有其他的桌子绑起来，把所有东西绑在一起的桌子.ID字段实际上是用于识别车辆的唯一批号.

mysql> create table cars (id int(3) not null auto_increment primary key, 
    -> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(3) | NO   | PRI | NULL    | auto_increment |
| color | int(3) | YES  |     | NULL    |                |
| brand | int(3) | YES  |     | NULL    |                |
| model | int(3) | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1), 
    -> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
|  1 |     1 |     2 |     1 |
|  2 |     3 |     1 |     2 |
|  3 |     5 |     3 |     1 |
|  4 |     4 |     4 |     2 |
|  5 |     2 |     2 |     3 |
|  6 |     3 |     5 |     4 |
|  7 |     4 |     1 |     3 |
|  8 |     2 |     2 |     1 |
|  9 |     5 |     2 |     3 |
| 10 |     4 |     5 |     1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)

这将为我们提供足够的数据(我希望)来覆盖下面不同类型联接的示例，并提供足够的数据使它们有价值.

因此，在这件事上，老板想了解The IDs of all the sports cars he has人.

这是一个简单的两表连接.我们有一个表，用于标识模型和表中的可用库存.如你所见，cars表的model列中的数据与cars表的models列相关.现在，我们知道models表的ID为1表示Sports，所以让我们编写连接.

select
    ID,
    model
from
    cars
        join models
            on model=ID

所以这个查询看起来不错，对吧？我们已经确定了这两个表，包含了我们需要的信息，并使用了一个正确标识要连接哪些列的连接.

ERROR 1052 (23000): Column 'ID' in field list is ambiguous

哦，不！我们第一次查询时出错了！是的，它是李子.你看，这个查询确实得到了正确的列，但其中一些列存在于两个表中，所以数据库会对我们所指的实际列和位置感到困惑.有两种解决方案.第一个很简单，我们可以用tableName.columnName来告诉数据库我们的意思，比如:

select
    cars.ID,
    models.model
from
    cars
        join models
            on cars.model=models.ID

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
|  2 | Sedan  |
|  4 | Sedan  |
|  5 | 4WD    |
|  7 | 4WD    |
|  9 | 4WD    |
|  6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)

另一种可能更常用，称为表别名.本例中的表有简单的好名字和短名字，但是输入KPI_DAILY_SALES_BY_DEPARTMENT这样的名字可能会很快变老，所以一个简单的方法是给表起一个昵称，如下所示:

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID

现在，回到请求.正如你所看到的，我们有我们需要的信息，但我们也有未被要求的信息，因此我们需要在声明中加入where条款，以便只按要求获得跑车.由于我更喜欢table alias方法，而不是反复使用表名，因此从现在开始我将继续使用它.

显然，我们需要在查询中添加where子句.我们可以通过ID=1或model='Sports'识别跑车.由于ID和主键都已编制索引(而且碰巧键入的更少)，所以让我们在查询中使用它.

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID
where
    b.ID=1

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

答对了老板很高兴.当然，作为一名老板，他对自己的要求从不满意，他看了看信息，然后说I want the colors as well.

好的，我们已经编写了查询的大部分内容，但是我们需要使用第三个表，即colors.现在，我们的主信息表cars存储了汽车 colored颜色 ID，并链接回 colored颜色 ID列.因此，以与原始表类似的方式，我们可以加入第三个表:

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
where
    b.ID=1

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

该死，虽然表被正确连接，相关列被链接，但我们忘记了从刚刚链接的新表中提取实际的information.

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
where
    b.ID=1

+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)

好的，那是我们背后的老板.现在，我们来更详细地解释一下.如你所见，我们声明中的from条款连接了我们的主表(我经常使用一个包含信息的表，而不是一个查找表或维度表.当所有的表都切换过来时，查询也会工作得很好，但当我们在几个月后再回到这个查询来阅读它时，就没有什么意义了，所以通常最好try 编写一个好的、易于理解的查询——直观地展示它首先，使用良好的缩进，使一切尽可能清晰.如果你继续教别人，试着在他们的问题中灌输这些特点——尤其是如果你要解决问题的话.

以这种方式链接越来越多的表是完全可能的.

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1

虽然我忘了包含一个表，我们可能希望在join语句中连接多个列，但下面是一个示例.如果models表有特定于品牌的型号，因此也有一个名为brand的列，该列链接回ID字段上的brands表，则可以这样做:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
            and b.brand=d.ID
where
    b.ID=1

可以看到，上面的查询不仅将连接的表链接到主cars表，还指定了已连接表之间的连接.如果不这样做，结果被称为笛卡尔连接——这是dba的坏话.笛卡尔连接是一种返回行的连接，因为信息不会告诉数据库如何限制结果，所以查询返回符合条件的行.

因此，为了给出笛卡尔连接的示例，让我们运行以下查询:

select
    a.ID,
    b.model
from
    cars a
        join models b

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  1 | Sedan  |
|  1 | 4WD    |
|  1 | Luxury |
|  2 | Sports |
|  2 | Sedan  |
|  2 | 4WD    |
|  2 | Luxury |
|  3 | Sports |
|  3 | Sedan  |
|  3 | 4WD    |
|  3 | Luxury |
|  4 | Sports |
|  4 | Sedan  |
|  4 | 4WD    |
|  4 | Luxury |
|  5 | Sports |
|  5 | Sedan  |
|  5 | 4WD    |
|  5 | Luxury |
|  6 | Sports |
|  6 | Sedan  |
|  6 | 4WD    |
|  6 | Luxury |
|  7 | Sports |
|  7 | Sedan  |
|  7 | 4WD    |
|  7 | Luxury |
|  8 | Sports |
|  8 | Sedan  |
|  8 | 4WD    |
|  8 | Luxury |
|  9 | Sports |
|  9 | Sedan  |
|  9 | 4WD    |
|  9 | Luxury |
| 10 | Sports |
| 10 | Sedan  |
| 10 | 4WD    |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)

天哪，这太难看了.然而，就数据库而言，它是所要求的.在查询中，我们要求从cars得到ID，从models得到model.然而，因为我们没有指定how来连接这些表，所以数据库将第一个表中的every行与第二个表中的every行进行了匹配.

好了，老板回来了，他想再次了解更多信息.I want the same list, but also include 4WDs in it

然而，这给了我们一个很好的理由来研究两种不同的方法来实现这一点.我们可以在where子句中添加另一个条件，如下所示:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
    or b.ID=3

虽然上面的方法可以很好地工作，但让我们换一种方式来看待它，这是一个很好的借口来展示union个查询是如何工作的.

我们知道以下人员将退还所有跑车:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1

下面将返回所有4WD:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3

因此，通过在它们之间添加union all子句，第二个查询的结果将附加到第一个查询的结果中.

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
union all
select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3

+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
|  5 | 4WD    | Green |
|  7 | 4WD    | White |
|  9 | 4WD    | Black |
+----+--------+-------+
7 rows in set (0.00 sec)

如您所见，第一个查询的结果首先返回，然后是第二个查询的结果.

在本例中，简单地使用第一个查询当然会容易得多，但对于特定情况，union个查询可能非常好.它们是一种很好的方法，可以从不容易连接在一起的表中返回特定的结果，或者从completely个不相关的表中返回特定的结果.然而，有一些规则需要遵循.

第一个查询中的列类型必须与下面每个其他查询中的列类型匹配.
第一个查询中的列的名称将用于标识整个结果集.
每个查询中的列数必须相同.

现在，你可能会发现使用union和union all之间有be wondering what the个区别.union查询将删除重复项，而union all查询则不会.这确实意味着当使用union比union all时，性能会有一点下降，但结果可能是值得的——不过，我不会在本文中推测这种情况.

关于这一点，这里可能值得注意一些额外的注意事项.

如果我们想订购结果，我们可以使用order by，但你不能再使用别名.在上面的查询中，添加order by a.ID将导致错误——就结果而言，该列被称为ID而不是a.ID——尽管两个查询中使用了相同的别名.
我们只能有一个order by语句，它必须作为最后一个语句.

在接下来的示例中，我将向表中添加一些额外的行.

我在brands表中添加了Holden个.

好了，老板又回来了，大声喊出要求——*我要数一数我们经营的每个品牌和里面的汽车数量！`-典型的情况是，我们讨论到了一个有趣的部分，老板想要更多的工作.

好吧，所以我们需要做的第一件事是得到一份可能品牌的完整 list .

select
    a.brand
from
    brands a

+--------+
| brand  |
+--------+
| Ford   |
| Toyota |
| Nissan |
| Smart  |
| BMW    |
| Holden |
+--------+
6 rows in set (0.00 sec)

现在，当我们将其加入cars表时，我们得到以下结果:

select
    a.brand
from
    brands a
        join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Nissan |
| Smart  |
| Toyota |
+--------+
5 rows in set (0.00 sec)

这当然是一个问题——我们没有看到任何关于我添加的可爱的Holden品牌的提及.

这是因为联接在both个表中查找匹配的行.由于Holden型车中没有数据，因此不会返回.这是我们可以使用outer连接的地方.这将返回all个来自一个表的结果，无论它们是否在另一个表中匹配:

select
    a.brand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Holden |
| Nissan |
| Smart  |
| Toyota |
+--------+
6 rows in set (0.00 sec)

现在我们有了它，我们可以添加一个可爱的聚合函数来获得一个计数，然后让老板暂时离开我们.

select
    a.brand,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+--------------+
| brand  | countOfBrand |
+--------+--------------+
| BMW    |            2 |
| Ford   |            2 |
| Holden |            0 |
| Nissan |            1 |
| Smart  |            1 |
| Toyota |            5 |
+--------+--------------+
6 rows in set (0.00 sec)

说完，老板就躲起来了.

现在，为了更详细地解释这一点，外部联接可以是left或right类型.左边或右边定义了包含哪些表.left outer join将包含左侧表格中的所有行，而(你猜到了)right outer join将右侧表格中的所有结果带入结果.

一些数据库将允许使用full outer join从both个表中返回结果(无论是否匹配)，但并非所有数据库都支持这种方法.

现在，我可能会想，此时此刻，您可能想知道是否可以在查询中合并联接类型——答案是肯定的，您绝对可以.

select
    b.brand,
    c.color,
    count(a.id) as countOfBrand
from
    cars a
        right outer join brands b
            on b.ID=a.brand
        join colors c
            on a.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)

那么，为什么这不是预期的结果呢？这是因为，虽然我们 Select 了从汽车到品牌的外部连接，但它没有在 colored颜色的连接中指定，因此特定的连接只会返回两个表中匹配的结果.

下面是一个查询，可以得到我们预期的结果:

select
    a.brand,
    c.color,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
        left outer join colors c
            on b.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Holden | NULL  |            0 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| Toyota | NULL  |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)

正如我们所看到的，我们在查询中有两个外部联接，结果正如预期的那样.

那么，你问的其他类型的连接呢？十字路口呢？

不是所有的数据库都支持intersection，但是几乎所有的数据库都允许您通过连接(或者至少是 struct 良好的where语句)创建一个交集.

交集是一种连接类型，类似于上文所述的union，但区别在于它only返回的数据行在由联合连接的各个单独查询之间是相同的(我的意思是相同的).只返回在各个方面都相同的行.

一个简单的例子如下:

select
    *
from
    colors
where
    ID>2
intersect
select
    *
from
    colors
where
    id<4

正常的union查询将返回表中的所有行(第一个查询返回ID>2以上的任何行，第二个查询返回ID<4以上的任何行)，这将生成一个完整的集合，而intersect查询只会返回与id=3匹配的行，因为它同时满足这两个条件.

现在，如果您的数据库不支持intersect查询，则可以轻松地将上面的查询与以下查询结合起来:

select
    a.ID,
    a.color,
    a.paint
from
    colors a
        join colors b
            on a.ID=b.ID
where
    a.ID>2
    and b.ID<4

+----+-------+----------+
| ID | color | paint    |
+----+-------+----------+
|  3 | Blue  | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)

如果希望使用本身不支持交叉查询的数据库跨两个不同的表执行交叉，则需要在every column个表上创建联接.

Mysql SQL查询从多个表返回数据

推荐答案

Part 1 - Joins and Unions

Mysql相关问答推荐

SQL不断返回查询失败，并且不知道从这里到哪里go

含有子查询的MySQL函数出现语法错误

带有值分隔的MySQL分组不起作用

MySQL Group by或Distinct in Window函数

亚马逊RDS MySQL-无法'；t执行'；使用读取锁定刷新表'；

为什么 MAX 函数通过调用在 group 中生成正确的结果

布尔值之间的 SQL less/greater 比较会产生意外结果

MySQL如何在小时和分钟之间 Select 时间

为什么一个 10 位的电话号码不能存储在长度为 10 的整数中？

在分组结果 MySQL 5.7 版中获取最多的重复值

我在查询中没有得到正确的结果

用两个新列制作表格，按偶数或奇数对另一列进行分类，并将一个类别中的所有偶数和奇数相加

让 MySQL 在 OSX 10.7 Lion 上运行

phpmyadmin没有收到要导入的数据错误，如何解决？

基于字符串动态创建 PHP 对象

使用 Java 通过 SSH 连接到远程 MySQL 数据库

Sequelize Query 查找日期范围内的所有记录

MySQL查询/子句执行顺序

更改 Laravel 的 created_at 和 updated_at 的名称

SQL WHERE 列 = 一切