我想知道以下情况。
我打算在我的(比如说--PHP)应用程序中使用这个,但不想对数据库进行多次查询,我有什么办法可以在一次查询中从多个表中获取数据?
注意:我写这个是因为我希望能够链接到一个写得很好的关于我在PHP队列中不断遇到的众多问题的指南,这样我就可以在发布答案时链接到这个指南以获得更多细节。
这些答案涵盖了以下内容。
1.1.第一部分--连接和联合 2.2. 第2部分 - 子查询 3.第3部分 - 技巧和高效代码 4.第4部分:从句中的子查询 5.第5部分:约翰的杂项技巧
本答案包括 1.第1部分
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。 这是一个简单的两表连接。我们有一个标识车型的表和一个包含可用库存的表。正如你所看到的,"cars "表的 "model "列中的数据与我们的 "cars "表的 "models "列有关。现在,我们知道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 "的列,在 "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"。然而,由于我们没有指定如何连接这些表,数据库将第一个表中的每一条记录与第二个表中的每一条记录进行了匹配。 好了,老板回来了,他又想要更多的信息。我想要同样的列表,但也包括四驱车*。 然而,这给了我们一个很好的借口,让我们看看两种不同的方法来完成这个任务。我们可以在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
虽然上面的方法可以很好地工作,但我们还是要以不同的方式来看待它,这是一个很好的借口来展示 "联合 "查询是如何工作的。 我们知道,下面的内容将返回所有的跑车。
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
而下面的内容将返回所有的四驱车。
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",这在颜色表中没有任何参考。
好了,老板又回来了,大声要求--*我想知道我们经营的每个品牌和其中的汽车数量!--典型的,我们刚讨论到一个有趣的部分,老板就想要更多的工作。
Rightyo,所以我们需要做的第一件事是获得一份可能的品牌的完整清单。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 "品牌的提及。 这是因为连接是在两张表中寻找匹配的行。由于汽车中没有属于 "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)
就这样,老板溜之大吉了。 现在,为了更详细地解释这个问题,外连接可以是 "左 "或 "右 "类型。左或右定义了哪个表被完全包括在内。一个 "左外连接 "将包括左边表中的所有行,而(你猜对了)一个 "右外连接 "将右边表中的所有结果带入结果中。 有些数据库允许 "全外连接",它将带回两个表的结果(无论是否匹配),但这并不是所有数据库都支持。 现在,我可能想知道在这个时候,你是否可以在一个查询中合并连接类型--答案是肯定的,你绝对可以。
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
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)
正如我们所看到的,我们在查询中有两个外层连接,结果如期出现了。 现在,你问的那些其他类型的连接是怎么回事?交叉的情况如何? 嗯,不是所有的数据库都支持 "交叉",但是几乎所有的数据库都允许你通过一个连接(或者至少是一个结构良好的where语句)来创建一个交叉。 交叉点是一种有点类似于上述 "联盟 "的连接类型--但不同的是,它*只返回在联盟所连接的各个单独查询之间相同的数据行(我是说相同的)。只有那些在各方面都相同的数据行才会被返回。 一个简单的例子是这样的。
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
一个正常的 "union "查询将返回表中的所有记录(第一个查询返回任何超过 "ID>2 "的记录,第二个查询返回任何有 "ID<4 "的记录),这将导致一个完整的集合,一个intersect查询将只返回符合 "id=3 "的记录,因为它符合这两个条件。 现在,如果你的数据库不支持 "交叉 "查询,上述情况可以通过以下查询轻松实现。
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)
如果你希望使用不支持交叉查询的数据库在两个不同的表之间进行交叉,你需要在表的每一列上创建一个连接。
好了,现在老板又闯进来了--我想要一个我们所有汽车品牌的列表,以及我们有多少个该品牌的汽车的总数!。 这是一个很好的机会来使用我们的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
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)
然后Bam!,这就可以了。如果你注意到,这个子查询将不得不为我们返回的每一行数据运行。即使在这个小例子中,我们只有五个不同的汽车品牌,但子查询却运行了11次,因为我们有11行数据要返回。所以,在这种情况下,这似乎不是编写代码的最有效方式。 对于一个不同的方法,让我们运行一个子查询,并假装它是一个表。
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 "子句中选择了两个表,然后写了一个查询并告诉数据库它实际上是一个表--数据库对此非常满意。使用这种方法有*的好处(也有一些限制)。最重要的是,这个子查询只运行一次。如果我们的数据库包含大量的数据,很可能会比第一种方法有巨大的改进。然而,由于我们将其作为一个表使用,我们必须引入额外的数据行--以便它们能够真正地被连接到我们的数据行上。如果我们要使用上述查询中的简单连接,我们还必须确保有足够的数据行。如果你记得,连接将只拉回在连接的两边都有匹配数据的行。如果我们不小心,这可能会导致有效的数据不能从我们的汽车表中返回,如果这个子查询中没有匹配的行。
现在,回顾一下第一个子查询,也有一些限制。因为我们是把数据拉回到单行中,所以我们只能**拉回一行数据。在查询的 "选择 "子句中使用的子查询通常只使用一个聚合函数,如 "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)
这将返回一个品牌ID和品牌名称的列表(添加第二列只是为了向我们展示品牌),这些品牌名称中包含字母o
。
现在,我们可以在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)
同样,你可以看到子查询与手动输入在从数据库返回时是如何改变行的顺序的。 当我们在讨论子查询时,让我们看看我们还能用子查询做什么。
我想我应该增加一些额外的部分,用于出现的技巧和窍门。
我看到有一个问题出现的频率很高,那就是如何从两个表中获取不匹配的行,我看到最普遍接受的答案是类似以下的东西(基于我们的汽车和品牌表--其中有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子句中指定目标表'品牌'进行更新。
MySQL不允许你在同一个表上用子选择运行一个 "更新... "查询。现在,你可能会想,为什么不直接把它放在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