logo标签列表

从多个表中返回数据的SQL查询

mysqlsqlselect
465
我想了解以下内容：
  • 如何从我的数据库中获取多个表的数据？
  • 有哪些方法可以实现这一点？
  • 联接和联合是什么，它们彼此有什么不同？
  • 相对于其他方法，什么情况下应该使用每个方法？

我计划在我的（例如-PHP）应用程序中使用此功能，但不想针对数据库运行多个查询，请问我有哪些选项可以在单个查询中从多个表中获取数据？

注意：我写这篇文章是因为我希望能够链接到一份精心编写的指南，介绍PHP队列中经常遇到的众多问题，这样我在发布答案时就可以链接到这个指南以获取更多详细信息。

答案包括以下内容：

  1. 第1部分-联接和联合
  2. 第2部分-子查询
  3. 第3部分-技巧和高效代码
  4. 第4部分-来自子查询的From子句
  5. 第5部分-John的综合技巧
6个回答
504

部分一 - 连接和联合

本答案涵盖：

  1. 部分一
    • 使用内连接将两个或多个表连接在一起（有关更多信息，请参见维基百科条目）
    • 如何使用联合查询
    • 左外连接和右外连接（这个堆栈溢出的答案非常好地描述了连接的类型）
    • 交集查询（以及如何在数据库不支持它们的情况下重现它们）- 这是 SQL-Server 的一个功能（查看信息），也是我写这篇文章的原因之一。
  2. 部分二
    • 子查询 - 它们是什么，可以在哪里使用以及需要注意什么
    • 笛卡尔积连接，也称为“哦，痛苦！”

在数据库中检索多个表的数据有许多方法。在本答案中，我将使用 ANSI-92 连接语法。这可能与其他许多教程中使用旧的 ANSI-89 语法不同（如果您习惯于使用 89，则可能会感到远不如此直观 - 但我所能说的是尝试一下），因为当查询开始变得更加复杂时，它会更容易理解。为什么要使用它？是否有性能提高？简短的答案是否定的，但是一旦你习惯了它，它确实更容易阅读。使用此语法编写的其他人的查询更容易阅读。

我还将使用一个小汽车场的概念，该汽车场具有用于跟踪其可用汽车的数据库。车主已经雇用了您作为他的 IT 计算机专业人员，并期望您能够随时满足他的要求。

我制作了许多查找表，这些表将由最终表使用。这将给我们一个合理的模型来工作。首先，我将针对具有以下结构的示例数据库运行我的查询。我将尝试想到在开始时常见的错误，并解释它们出了什么问题 - 当然还要展示如何纠正它们。

第一个表只是颜色列表，以便我们知道汽车场有哪些颜色。

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)

品牌表格是用于识别汽车销售场地可能出售的不同汽车品牌。

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)

这将为我们提供足够的数据（我希望可以）涵盖下面不同类型的连接示例并足以使它们有价值。

所以进入实质，老板想知道他拥有所有跑车的ID。

这是一个简单的两个表连接。我们有一个表来识别模型和另一个表中的可用库存。正如您所看到的，在汽车表的model列中的数据与我们拥有的汽车表的models列相关。现在，我们知道模型表对于Sports有一个ID为1，所以让我们写连接。

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子句，以仅获取要求的运动汽车。由于我更喜欢使用表别名方法而不是反复使用表名，所以从此处开始我将坚持使用它。

显然，我们需要向查询添加一个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)

太好了！老板很高兴。当然，作为老板，他从来不会对他要求的东西感到满意，他看了一下信息，然后说我也想要颜色。

好的，我们已经编写好了查询的一部分，但我们需要使用第三个表格-颜色。现在，我们的主信息表cars存储了汽车颜色ID，并将其链接回颜色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)

天哪，虽然表已经正确连接，并且相关的列已经链接了，但我们忘记从刚刚链接的新表中提取实际的信息。

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 子句将我们的主表链接起来（我经常使用包含信息而不是查找或维度表的表格。查询所有表格切换后仍然可以正常工作，但当我们在几个月后回到这个查询时，理解起来就会更困难了。因此，最好尝试编写一个易于理解的查询-直观地布局，使用漂亮的缩进，以使所有内容尽可能清晰明了。如果您要教别人，请尝试在他们的查询中灌输这些特点-尤其是如果您将对它们进行故障排除。

按照这种方式继续链接更多的表格是完全可行的。

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的列，该列链接回brands表上的ID字段，则可以执行以下操作：

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表进行了关联，还指定了已经连接的表之间的连接。如果没有这样做，结果被称为笛卡尔积 - 这是数据库管理员用来表示糟糕查询的术语。笛卡尔积是指返回行数的查询，因为信息没有告诉数据库如何限制结果，所以查询将返回符合条件的所有行。

因此，我们举一个笛卡尔积的例子，运行以下查询：

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。然而，由于我们没有指定如何连接这些表，数据库匹配了第一个表的每一行与第二个表的每一行。

好吧，老板回来了，他再次需要更多信息。 我想要同样的列表，但也包括四轮驱动车辆。

这为我们提供了两种不同实现此操作的绝佳机会。我们可以在 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查询对于特定情况非常有效。它们是从不容易连接在一起-或者毫无关系的表格中返回特定结果的好方法。但是需要遵循一些规则。

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

现在，您可能会想知道union和union all之间的区别。 union查询将删除重复项，而union all不会。这意味着使用union比使用union all会有一点性能损失，但结果可能值得-我不会在此进行推测。

关于这一点，可能值得在此添加一些附加说明。

  • 如果我们想要对结果排序，我们可以使用order by，但不能再使用别名。在上面的查询中，附加一个order by a.ID会导致错误-就结果而言，该列被称为ID而不是a.ID-尽管在两个查询中都使用了相同的别名。
  • 我们只能有一个order by语句，并且它必须作为最后一个语句。

对于下一个示例，我向我们的表格添加了一些额外的行。

我已将Holden添加到品牌表中。我还向cars添加了一行，其中color值为12，该值在颜色表中没有引用。

好的，老板回来了，大声要求-*我想知道我们携带的每个品牌和其中的汽车数量！`-典型的，我们刚刚讨论完有趣的部分，老板就要求更多工作。

好的，首先我们需要做的是获取可能品牌的完整列表。

select
    a.brand
from
    brands a

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

现在，当我们将它与我们的汽车表连接时，我们会得到以下结果：

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品牌的任何提及。
这是因为连接在两个表中寻找匹配的行。由于cars表中没有类型为Holden的数据，因此它不会被返回。这就是我们可以使用外部连接的地方。这将返回一个表中的所有结果，无论它们是否在另一个表中匹配：
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，它将从两个表中返回结果（无论是否匹配），但并非所有数据库都支持此操作。

现在，在这一点上，您可能想知道是否可以在查询中合并连接类型 - 答案是肯定的，您绝对可以。

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)

那么为什么结果不是我们预期的呢？因为虽然我们从cars到brands选择了外连接，但没有在连接到颜色时进行指定 - 因此该特定连接只会带回在两个表中都匹配的结果。

以下是能够得到预期结果的查询：

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，但几乎所有数据库都允许你通过联接（或者至少一个良好结构化的where语句）来创建一个交集。

交集是一种类似于上面描述的union的连接类型 - 但不同之处在于它仅返回各个单独查询通过union连接后在每个方面都完全相同的数据行。只有完全相同的行将被返回。

一个简单的例子如下：

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

普通的union查询会返回表中的所有行（第一个查询返回任何大于ID>2的内容，第二个查询返回任何小于ID<4的内容），这将导致一个完整的集合，而交集查询只会返回符合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)

如果您希望在一个不支持交集查询的数据库中执行跨两个不同表格的交集，您需要在这些表格的每一列上创建一个连接。

3
@Fluffeh，回答得很好。 我有一个建议：如果你想把它变成一个杀手级别的SQL教程，你只需要添加维恩图；由于维恩图，我立刻理解了左连接和右连接。个人请求：你有没有关于常见错误/性能调优的教程？ - Ondino
31
哦，我的滚轮坏了。非常棒的问题和答案。我希望我能给这个问题点赞十次。 - Amal Murali
4
谢谢你的正面反馈。请继续向下滚动，这只是第一个答案。因为我的答案太长了，无法放在一个“答案”中，所以我不得不使用几个答案 :) - Fluffeh
10
老实说，我认为这个答案需要缩短一些。 - einpoklum
1
优秀的文章。数据库连接 101。 - maqs
103

好的，我发现这篇文章非常有趣，我想分享一些有关创建查询的知识。感谢Fluffeh。其他人如果看到并认为我的答案有误，完全可以编辑和批评我的回答。（老实说，感谢指出我的错误。）

我将发布一些在MySQL标签中经常被问到的问题。

技巧1（匹配多个条件的行）

给定此架构

CREATE TABLE MovieList
(
    ID INT,
    MovieName VARCHAR(25),
    CONSTRAINT ml_pk PRIMARY KEY (ID),
    CONSTRAINT ml_uq UNIQUE (MovieName)
);

INSERT INTO MovieList VALUES (1, 'American Pie');
INSERT INTO MovieList VALUES (2, 'The Notebook');
INSERT INTO MovieList VALUES (3, 'Discovery Channel: Africa');
INSERT INTO MovieList VALUES (4, 'Mr. Bean');
INSERT INTO MovieList VALUES (5, 'Expendables 2');

CREATE TABLE CategoryList
(
    MovieID INT,
    CategoryName VARCHAR(25),
    CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName),
    CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID)
);

INSERT INTO CategoryList VALUES (1, 'Comedy');
INSERT INTO CategoryList VALUES (1, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Drama');
INSERT INTO CategoryList VALUES (3, 'Documentary');
INSERT INTO CategoryList VALUES (4, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Action');

问题

查找属于 喜剧 和 浪漫 类别的所有电影。

解决方案

有时候，这个问题可能会非常棘手。似乎像这样的查询就是答案:

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName = 'Comedy' AND
        b.CategoryName = 'Romance'

SQLFiddle演示

这是肯定非常错误的，因为它产生没有结果。其解释是在每一行只有一个有效的CategoryName值。例如，第一个条件返回true，第二个条件始终为false。因此，使用AND运算符时，两个条件都应为真，否则将为假。另一个查询如下所示：

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')

SQLFiddle演示

结果仍然不正确，因为它匹配到拥有至少一个categoryName匹配的记录。 真正的解决方案 应该通过计算每个电影的记录数量来实现。 实例的数量应该与条件中提供的值的总数相匹配。

SELECT  a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
HAVING COUNT(*) = 2

SQLFiddle演示（答案）

技巧二（每个条目的最大记录数）

给定模式，

CREATE TABLE Software
(
    ID INT,
    SoftwareName VARCHAR(25),
    Descriptions VARCHAR(150),
    CONSTRAINT sw_pk PRIMARY KEY (ID),
    CONSTRAINT sw_uq UNIQUE (SoftwareName)  
);

INSERT INTO Software VALUES (1,'PaintMe','used for photo editing');
INSERT INTO Software VALUES (2,'World Map','contains map of different places of the world');
INSERT INTO Software VALUES (3,'Dictionary','contains description, synonym, antonym of the words');

CREATE TABLE VersionList
(
    SoftwareID INT,
    VersionNo INT,
    DateReleased DATE,
    CONSTRAINT sw_uq UNIQUE (SoftwareID, VersionNo),
    CONSTRAINT sw_fk FOREIGN KEY (SOftwareID) REFERENCES Software(ID)
);

INSERT INTO VersionList VALUES (3, 2, '2009-12-01');
INSERT INTO VersionList VALUES (3, 1, '2009-11-01');
INSERT INTO VersionList VALUES (3, 3, '2010-01-01');
INSERT INTO VersionList VALUES (2, 2, '2010-12-01');
INSERT INTO VersionList VALUES (2, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 3, '2011-12-01');
INSERT INTO VersionList VALUES (1, 2, '2010-12-01');
INSERT INTO VersionList VALUES (1, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 4, '2012-12-01');

问题

查找每个软件的最新版本。显示以下列：SoftwareName，Descriptions，LatestVersion（从VersionNo列），DateReleased。

解决方案

一些SQL开发人员错误地使用MAX()聚合函数。他们往往会创建这样的代码：

SELECT  a.SoftwareName, a.Descriptions,
        MAX(b.VersionNo) AS LatestVersion, b.DateReleased
FROM    Software a
        INNER JOIN VersionList b
            ON a.ID = b.SoftwareID
GROUP BY a.ID
ORDER BY a.ID

SQLFiddle演示

（大多数关系型数据库管理系统会因为没有在group by子句中指定一些非聚合列而生成语法错误，）结果在每个软件上产生了正确的LatestVersion，但显然DateReleased是不正确的。MySQL尚未像某些RDBMS那样支持Window Functions和Common Table Expression。解决这个问题的方法是创建一个子查询，在每个软件上获取最大的versionNo，然后将其连接到其他表中。

SELECT  a.SoftwareName, a.Descriptions,
        b.LatestVersion, c.DateReleased
FROM    Software a
        INNER JOIN
        (
            SELECT  SoftwareID, MAX(VersionNO) LatestVersion
            FROM    VersionList
            GROUP BY SoftwareID
        ) b ON a.ID = b.SoftwareID
        INNER JOIN VersionList c
            ON  c.SoftwareID = b.SoftwareID AND
                c.VersionNO = b.LatestVersion
GROUP BY a.ID
ORDER BY a.ID

SQLFiddle演示（答案）

就是这样。如果我想起其他与MySQL标签有关的常见问题，我会很快发布另一篇文章。谢谢您阅读这篇小文章。我希望您从中至少获得了一些知识。

更新1

技巧3（查找两个ID之间的最新记录）

给定架构

CREATE TABLE userList
(
    ID INT,
    NAME VARCHAR(20),
    CONSTRAINT us_pk PRIMARY KEY (ID),
    CONSTRAINT us_uq UNIQUE (NAME)  
);

INSERT INTO userList VALUES (1, 'Fluffeh');
INSERT INTO userList VALUES (2, 'John Woo');
INSERT INTO userList VALUES (3, 'hims056');

CREATE TABLE CONVERSATION
(
    ID INT,
    FROM_ID INT,
    TO_ID INT,
    MESSAGE VARCHAR(250),
    DeliveryDate DATE
);

INSERT INTO CONVERSATION VALUES (1, 1, 2, 'hi john', '2012-01-01');
INSERT INTO CONVERSATION VALUES (2, 2, 1, 'hello fluff', '2012-01-02');
INSERT INTO CONVERSATION VALUES (3, 1, 3, 'hey hims', '2012-01-03');
INSERT INTO CONVERSATION VALUES (4, 1, 3, 'please reply', '2012-01-04');
INSERT INTO CONVERSATION VALUES (5, 3, 1, 'how are you?', '2012-01-05');
INSERT INTO CONVERSATION VALUES (6, 3, 2, 'sample message!', '2012-01-05');

问题

查找两个用户之间的最新对话。

解决方案

SELECT    b.Name SenderName,
          c.Name RecipientName,
          a.Message,
          a.DeliveryDate
FROM      Conversation a
          INNER JOIN userList b
            ON a.From_ID = b.ID
          INNER JOIN userList c
            ON a.To_ID = c.ID
WHERE     (LEAST(a.FROM_ID, a.TO_ID), GREATEST(a.FROM_ID, a.TO_ID), DeliveryDate)
IN
(
    SELECT  LEAST(FROM_ID, TO_ID) minFROM,
            GREATEST(FROM_ID, TO_ID) maxTo,
            MAX(DeliveryDate) maxDate
    FROM    Conversation
    GROUP BY minFROM, maxTo
)

SQLFiddle演示

太棒了！但是，约翰，你的第一个解决方案之所以有效，仅仅是因为这两个字段上有唯一约束。你可以使用更通用的解决方案来帮助解决常见问题。在我看来，唯一的解决方案是对“喜剧”和“浪漫”分别进行选择。此时，“Having”并不适用。 - nawfal
@nawfal 不是很准确，如果没有添加唯一约束，则需要在having子句中添加distinct SQLFiddle演示 :D - John Woo
66

第二部分 - 子查询

好的，现在老板又冲进来了 - 我想要一张我们所有汽车品牌的列表，并且还要加上该品牌汽车的总数！

这是一个很好的机会来使用我们 SQL 神器包中的下一个技巧 - 子查询。如果您对这个术语不熟悉，子查询是在另一个查询内运行的查询。有许多不同的用法。

对于我们的请求，让我们首先组合一个简单的查询，将每辆汽车和品牌列出：

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

现在，如果我们想要按品牌对汽车进行简单的计数，当然可以这样写:

select
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    b.brand

+--------+-----------+
| brand  | countCars |
+--------+-----------+
| BMW    |         2 |
| Ford   |         2 |
| Nissan |         1 |
| Smart  |         1 |
| Toyota |         5 |
+--------+-----------+

那么，我们应该能够简单地将计数函数添加到我们的原始查询中，对吧？


select
    a.ID,
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    a.ID,
    b.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         1 |
|  2 | Ford   |         1 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         1 |
|  6 | BMW    |         1 |
|  7 | Ford   |         1 |
|  8 | Toyota |         1 |
|  9 | Toyota |         1 |
| 10 | BMW    |         1 |
| 11 | Toyota |         1 |
+----+--------+-----------+
11 rows in set (0.00 sec)

抱歉，我们不能这样做。原因是当我们添加汽车ID（列a.ID）时，我们必须将其添加到“group by”中 - 因此，当计数函数工作时，每个ID只会匹配一个ID。

不过，我们可以使用子查询 - 实际上，我们可以执行两种完全不同类型的子查询，以获得我们需要的相同结果。第一种方法是将子查询简单地放在“select”子句中。这意味着每次获取数据行时，子查询将运行，获取数据列，然后将其插入我们的数据行中。

select
    a.ID,
    b.brand,
    (
    select
        count(c.ID)
    from
        cars c
    where
        a.brand=c.brand
    ) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  2 | Ford   |         2 |
|  7 | Ford   |         2 |
|  1 | Toyota |         5 |
|  5 | Toyota |         5 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 11 | Toyota |         5 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  6 | BMW    |         2 |
| 10 | BMW    |         2 |
+----+--------+-----------+
11 rows in set (0.00 sec)

咔嚓！这样就可以了。但是请注意，对于我们返回的每一行数据，这个子查询都必须运行。即使在这个小例子中，我们只有五个不同品牌的汽车，但由于我们返回了十一行数据，子查询运行了十一次。因此，在这种情况下，这似乎不是编写代码最有效的方法。

为了采用不同的方法，让我们运行一个子查询并假装它是一张表：

select
    a.ID,
    b.brand,
    d.countCars
from
    cars a
        join brands b
            on a.brand=b.ID
        join
            (
            select
                c.brand,
                count(c.ID) as countCars
            from
                cars c
            group by
                c.brand
            ) d
            on a.brand=d.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         5 |
|  2 | Ford   |         2 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         5 |
|  6 | BMW    |         2 |
|  7 | Ford   |         2 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 10 | BMW    |         2 |
| 11 | Toyota |         5 |
+----+--------+-----------+
11 rows in set (0.00 sec)

好的，所以我们得到了相同的结果（稍微有些顺序不同——似乎数据库想要返回按我们这次选择的第一列排序的结果），但是数字都是正确的。

那么两者之间有什么区别，何时应该使用每种类型的子查询呢？首先，让我们确保我们理解第二个查询是如何工作的。我们在查询的from子句中选择了两个表，然后编写了一个查询，并告诉数据库它实际上是一个表——这是数据库完全可以接受的。使用这种方法可能会有一些好处（以及一些局限性）。首要的是，这个子查询只运行了一次。如果我们的数据库包含大量数据，那么使用第一种方法可能会有巨大的改进。但是，由于我们将其用作表格，因此我们必须引入额外的数据行，以便它们实际上可以与我们的数据行联接。我们还必须确保有足够的数据行，如果我们要像上面的查询中那样使用简单的联接。如果您记得，联接只会返回在联接的两侧具有匹配数据的行。如果我们不小心，这可能导致在此子查询中没有匹配行时，从我们的cars表中未返回有效数据。

现在，回顾第一个子查询，也有一些局限性。因为我们将数据拉回到单个行中，所以我们只能拉回一行数据。查询子句中使用的子查询通常仅使用聚合函数，例如sum、count、max或另一个类似的聚合函数。它们不一定需要这样写，但通常是这样写的。

因此，在我们继续之前，让我们快速看一下还可以在哪些地方使用子查询。我们可以在where子句中使用它——现在，这个例子有点牵强附会，因为在我们的数据库中，有更好的方法来获取以下数据，但既然这只是一个例子，让我们看一下：

select
    ID,
    brand
from
    brands
where
    brand like '%o%'

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  6 | Holden |
+----+--------+
3 rows in set (0.00 sec)

这将返回包含字母o在品牌名称中的品牌ID和品牌名称列表（第二列仅用于显示品牌名称）。

现在，我们可以在where语句中使用此查询的结果：

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in
        (
        select
            ID
        from
            brands
        where
            brand like '%o%'
        )

+----+--------+
| ID | brand  |
+----+--------+
|  2 | Ford   |
|  7 | Ford   |
|  1 | Toyota |
|  5 | Toyota |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

正如您所看到的，尽管子查询返回了三个品牌ID，但我们的汽车表中只有其中两个的条目。

在这种情况下，为了进一步说明，子查询的工作原理就像我们编写了以下代码：

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in (1,2,6)

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Toyota |
|  2 | Ford   |
|  5 | Toyota |
|  7 | Ford   |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

再次说明，您可以看到一个子查询与手动输入相比，在从数据库返回时如何改变行的顺序。

在讨论子查询时，让我们看看还有哪些其他用途：

  • 您可以将一个子查询放置在另一个子查询中，以此类推。这取决于您的数据库而有所不同，但除了某些疯狂和狂热的程序员的递归函数之外，大多数人永远不会达到那个限制。
  • 您可以将多个子查询放入单个查询中，一些在select子句中，一些在from子句中，还有一些在where子句中 - 只要记住，您放入的每个子查询都使查询更加复杂，可能需要更长时间才能执行。

如果您需要编写高效的代码，编写多种查询方式并查看（通过计时或使用解释计划）哪种是获得结果的最佳查询可能会很有益。第一种有效的方法并不总是做事情的最佳方式。

1
对于新开发人员非常重要的一点是：子查询可能会为每个结果运行一次，除非您可以将子查询用作连接（如上所示）。 - Xeoncross
61

第三部分 - 技巧和高效代码

MySQL in() 的效率

我想添加一些额外的技巧和提示。

经常有人问一个问题，即如何从两个表中获取不匹配的行。最常见的答案是以下内容（基于我们的汽车和品牌表 - 其中列出了“Holden”作为品牌，但未出现在汽车表中）：

select
    a.ID,
    a.brand
from
    brands a
where
    a.ID not in(select brand from cars)

是的，它将有效。 

+----+--------+
| ID | brand  |
+----+--------+
|  6 | Holden |
+----+--------+
1 row in set (0.00 sec)

然而，它在一些数据库中并不高效。这里有一个关于此的Stack Overflow问题链接，以及一个深入文章，如果您想深入了解细节，可以参考。
简而言之，如果优化器不能有效处理它，那么使用以下查询获取未匹配行可能会更好：
select
    a.brand
from
    brands a
        left join cars b
            on a.id=b.brand
where
    b.brand is null

+--------+
| brand  |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)

使用子查询更新相同的表格

啊哈，又是一个老但好的问题——无法在FROM子句中指定目标表'brands'进行更新。

MySQL不允许您在相同的表格上运行带有子选择的update...查询。现在，您可能会想，为什么不将它放入where子句中呢？但是，如果您只想更新一行中的max()日期而不是一堆其他行，该怎么办？您不能在where子句中完全这样做。

update 
    brands 
set 
    brand='Holden' 
where 
    id=
        (select 
            id 
        from 
            brands 
        where 
            id=6);
ERROR 1093 (HY000): You can't specify target table 'brands' 
for update in FROM clause

那么，我们不能这样做吗？不完全是这样的。有一个巧妙的变通方法，令人惊讶的是很多用户并不知道 - 尽管它包含一些需要注意的黑客技巧。

您可以将子查询放在另一个子查询中，这样两个查询之间就有足够的间隔，以便它能够正常工作。但是，请注意最好将查询放在事务中 - 这将防止在查询运行时对表进行任何其他更改。

update 
    brands 
set 
    brand='Holden' 
where id=
    (select 
        id 
    from 
        (select 
            id 
        from 
            brands 
        where 
            id=6
        ) 
    as updateTable);

Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0
4
只是想提醒一下，从“效率的角度来看”，WHERE NOT EXISTS()的结构几乎完全相同，但在我看来更容易阅读/理解。不过，我的知识仅限于MSSQL，我无法保证在其他平台上是否也是如此。 - deroby
我前几天刚尝试了这种比较方式，其中 NOT IN() 包含了数百个 ID 的列表，与查询的联接版本之间没有任何区别。也许当你处理成千上万甚至亿级别的数据时会有所不同。 - Buttle Butkus
19

你可以在FROM关键字中使用多个查询的概念。让我给你举一个例子：

SELECT DISTINCT e.id,e.name,d.name,lap.lappy LAPTOP_MAKE,c_loc.cnty COUNTY    
FROM  (
          SELECT c.id cnty,l.name
          FROM   county c, location l
          WHERE  c.id=l.county_id AND l.end_Date IS NOT NULL
      ) c_loc, emp e 
      INNER JOIN dept d ON e.deptno =d.id
      LEFT JOIN 
      ( 
         SELECT l.id lappy, c.name cmpy
         FROM   laptop l, company c
         WHERE l.make = c.name
      ) lap ON e.cmpy_id=lap.cmpy

您可以使用任意数量的表格。在必要时使用外部连接和联合，甚至在表子查询内也可以使用。

这是一种很简单的方法，可以涉及多个表格和字段。

10
希望这样可以在阅读内容时找到表格： jsfiddle
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    |                |
+-------+-------------+------+-----+---------+----------------+
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接