我想了解以下情况:
如何从我的数据库中的多个表中获取数据? 有哪些类型的方法可以做到这一点? 什么是联合和并集?它们彼此之间有什么不同? 我应该在什么时候使用它们?
我计划在我的(例如- PHP)应用程序中使用它，但不想对数据库运行多个查询，我必须在单个查询中从多个表中获取数据有哪些选项?
注意:我写这篇文章是因为我希望能够链接到一个关于我在PHP队列中经常遇到的许多问题的精心编写的指南，所以当我发布答案时，我可以链接到这个链接以获得进一步的细节。
答案包括以下内容:
第一部分-联合和联合 第2部分-子查询 第3部分-技巧和有效的代码 第4部分- From子句中的子查询 第五部分-约翰的各种把戏
当前回答
可以在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
您可以使用任意数量的表。在任何必要的地方使用外部连接和联合，甚至在表子查询中也是如此。
这是一种非常简单的方法，可以涉及尽可能多的表和字段。
其他回答
希望这能让它在你阅读的时候找到表格:
斯菲德尔
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 | |
+-------+-------------+------+-----+---------+----------------+
好的，我发现这篇文章非常有趣，我想分享一些关于创建查询的知识。谢谢你的绒毛。其他可能读到这篇文章并觉得我错了的人有101%的自由来编辑和批评我的答案。(说实话，我很感谢你改正了我的错误。)
我将在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演示(答案)
关系型划分SQL
技巧2(每项最多记录)
给定的模式,
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, description,LatestVersion (from VersionNo列)，daterreleased
解决方案
一些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演示
(大多数RDBMS在此生成语法错误，因为没有指定group by子句上的一些未聚合的列)结果在每个软件上生成正确的LatestVersion，但显然daterreleased是不正确的。MySQL不像一些RDBMS那样支持窗口函数和公共表表达式。解决这个问题的方法是创建一个子查询，它在每个软件上获得单个的最大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演示
可以在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
您可以使用任意数量的表。在任何必要的地方使用外部连接和联合，甚至在表子查询中也是如此。
这是一种非常简单的方法，可以涉及尽可能多的表和字段。
第2部分-子查询
好吧，现在老板又闯进来了——我要一份我们所有这个品牌的汽车的清单，以及这个品牌我们总共有多少辆!
这是使用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 |
+--------+-----------+
因此，我们应该能够简单地将count函数添加到原始查询中，对吧?
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)时，我们必须将其添加到组中，所以现在，当计数函数工作时，每个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)
和Bam !这对我们有好处。如果您注意到，这个子查询将必须为我们返回的每一行数据运行。即使在这个小例子中，我们只有5个不同的汽车品牌，但是子查询运行了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)
好的，我们得到了相同的结果(排序略有不同——似乎数据库想要返回按我们这次选择的第一列排序的结果)，但是相同的正确数字。
So, what's the difference between the two - and when should we use each type of subquery? First, lets make sure we understand how that second query works. We selected two tables in the from clause of our query, and then wrote a query and told the database that it was in fact a table instead - which the database is perfectly happy with. There can be some benefits to using this method (as well as some limitations). Foremost is that this subquery ran once. If our database contained a large volume of data, there could well be a massive improvement over the first method. However, as we are using this as a table, we have to bring in extra rows of data - so that they can actually be joined back to our rows of data. We also have to be sure that there are enough rows of data if we are going to use a simple join like in the query above. If you recall, the join will only pull back rows that have matching data on both sides of the join. If we aren't careful, this could result in valid data not being returned from our cars table if there wasn't a matching row in this subquery.
现在，回头看看第一个子查询，它也有一些限制。因为我们将数据拉回一行，所以我们只能拉回一行数据。在查询的select子句中使用的子查询通常只使用一个聚合函数，如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子句this中使用这个查询的结果:
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，但我们的cars表只有其中两个品牌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子句中—只需记住，您放入的每一个子查询都会使您的查询更加复杂，并且可能需要更长的执行时间。
如果您需要编写一些高效的代码，那么使用多种方式编写查询并查看(通过计时或使用解释计划)哪种查询是获得结果的最佳查询，这可能是有益的。第一种有效的方法可能并不总是最好的方法。
第3部分-技巧和有效的代码
MySQL在()效率
我想我应该添加一些额外的内容，为已经出现的提示和技巧。
我看到的一个问题是，我如何从两个表中获得不匹配的行，我看到最普遍接受的答案如下(基于我们的汽车和品牌表-霍顿被列为一个品牌，但没有出现在汽车表中):
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将不允许您运行更新…在同一表上使用子选择查询。现在，你可能会想，为什么不直接把它放到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
