从多个表中检索记录

最新推荐文章于 2023-03-30 22:00:34 发布
最新推荐文章于 2023-03-30 22:00:34 发布
阅读量1.1k 收藏 2
点赞数
分类专栏： MySQL

你把记录放在数据库中并没有什么好处，除非你从中检索它们，并对它们进行处理。这就是SELECT语句的目的--帮助你获得你的数据。SELECT可能是SQL语句中使用最频繁的语句了，但是它也可能是最需要技巧的一个；你选择使用的限制条件可能很复杂，并且涉及到对许多表中的列进行比较。

SELECT语句的基本语法看起来像：

SELECT selection_list                  # What columns to select
FROM table_list                        # Where to select rows from
WHERE primary_constraint               # What conditions rows must satisfy
GROUP BY grouping_columns              # How to group results
ORDER BY sorting_columns               # How to sort results
HAVING secondary_constraint            # Secondary conditions rows must satisfy
LIMIT count;                           # Limit on results</span>

在这个语法中所有的都是可选的，除了SELECT和指定你想要检索的selection_list部分。一些数据库也需要FROM子句。但是MySQL并不需要，这就使得你可以在不指定任何表的前提下去评估表达式：

SELECT SQRT(POW(3，2)+POW(4，2));
在第一章中，我们专注于单表的SELECT语句，主要集中在输出列，以及WHERE， GROUP BY， ORDER BY，和LIMIT。这一章我们包括SELECT经常混淆的一部分--joins语句；也就是SELECT从多个表中检索记录。我们将讨论MySQL支持的join类型，它们是什么以及如何指定它们。这应该帮助你更高效地部署MySQL，因为在许多情况下，指出如何写一个查询语句的实际问题是由合适的将表加在一起的方式决定的。

使用SELECT的一个问题是，当你第一次遇到了一个新的问题，并不总是很容易去写一个SELECT查询语句去解决问题。但是，当你解决了之后，你在未来遇到相似的问题后，你可以根据你的经验去解决问题。想要高效地使用SELECT语句，过往的经验可能起着最大的作用。因为它应用在各种各样的问题上。

随着你经验的增长，会变得很容易接受使用join去解决新的问题。你会发现你自己思考问题像“是的，那是一个left join的事”，或者，“那是一个通过限制关键列的公共对的三方的join”。（我有点不情愿地指出，实际上，你可能发现经验帮助了你会显得振奋人心。另一方面，你可能发现你可能结束以那样的方式去思考是令人震惊的！）

许多例子以如下的两个表t1和t2来说明如何使用MySQL支持的join操作。它们很小，使得它们足够简单一眼看到join对每个类型的效果。

Table t1:   Table t2:
+----+----+  +----+----+
| i1 | c1 |  | i2 | c2 |
+----+----+  +----+----+
| 1  | a  |  | 2  | c  |
| 2  | b  |  | 3  | b  |
| 3  | c  |  | 4  | a  |
+----+----+  +----+----+

谈不上join的join

最简单的join是微不足道的join,在这里仅仅只有一个表。在这个例子中，从命名的表中选出行。

mysql> SELECT * FROM t1;
+----+----+
| i1 | c1 |
+----+----+
| 1  | a  |
| 2  | b  |
| 3  | c  |
+----+----+

一些人根本不认为这是一个SELECT形式的join，他们认为join仅仅是针对两到三个表的SELECT。我想这只是一个认知角度的问题。

 Full join(全连接)

如果SELECT语句选择多个表在from从句中，并以逗号分隔多个表，MySQL就执行一个全连接。例如，如果你join t1和t2如下所示，t1中的每一行与t2中的每一行进行组合。

mysql> select t1.*, t2.* from t1, t2;
+------+------+------+------+
| i1   | c1   | i1   | c2   |
+------+------+------+------+
|    1 | a    |    2 | c    |
|    2 | b    |    2 | c    |
|    3 | c    |    2 | c    |
|    1 | a    |    3 | b    |
|    2 | b    |    3 | b    |
|    3 | c    |    3 | b    |
|    1 | a    |    4 | a    |
|    2 | b    |    4 | a    |
|    3 | c    |    4 | a    |
+------+------+------+------+
一个全连接也被称作cross连接，因为每一个表的每一行与另一个表的每一行相交叉来产生所有可能的组合。这就是著名的笛卡尔乘积。像这样连接表会潜在地产生非常大的行数，因为可能的行数是在每个表中的行数的乘积。一个三个表的全连接，每个表分别包含100，200，300行，则会返回一个100x200x300=6百万行的结果。即使对每一个表来说行数都不大，但是产生的结果却很大。在这种情况下，where语句通常会被用来减少结果的集合到一个可操作的大小。

如果你增加一个where从句导致表只匹配在特定列的值上，join就变成了所谓的equi-join，因为你只选择在指定列上有相等值的行：

mysql> SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2;
+----+----+----+----+
| i1 | c1 | i2 | c2 |
+----+----+----+----+
| 2  | b  | 2  | c  |
| 3  | c  | 3  | b  |
+----+----+----+----+
Join和cross join和','（逗号）连接符可看做是一样的。例如，如下语句的结果是完全一样的： 

SELECT t1.*, t2.* FROM t1,t2 WHERE t1.i1 = t2.i2;
SELECT t1.*, t2.* FROM t1 JOIN t2 WHERE t1.i1 = t2.i2;
SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1 = t2.i2;
通常情况下，在扫描表来最快地检索行时，MySQL优化器会自行决定顺序。偶尔优化器也会做出非最优的选择。如果你发现了，又可以通过使用STRIGHT_JOIN关键字来覆盖优化器的选择。Join执行有STRIGHT_JOIN时像一个cross join，但是会迫使表以from中的顺序来被连接。

STRIGHT_JOIN可以在SELECT语句中的两个地方被指定。你可以指定它在介于SELECT关键字和选择列表之间，这样会有一个全局效果在所有的cross joins。你也可以在FROM从句中指定它。如下的两种语句是等价的：

SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ;
SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;

左连接和右连接

等连接(equi-join)仅显示在两个表中都能匹配的行。左连接和右连接也显示匹配行，同时也会有仅出现在一个表中而另外一个表没有匹配的行。在这里以左连接（LEFT JOIN）为例，会识别在左表中但并没有匹配在右表中的行。右连接除了表换一下以外，别的都一样。

左连接像这样工作：你指定被用来匹配行在两个表中的列。当左表的一行匹配了右表的一行时，该行的内容会被选择并输出。当左表的行没有在右表中找到匹配，它仍然会被选择并输出，但是会连接一个假的行从右表中，并且第二个表中所有的列被设置为NULL。换句话说，左连接会强制结果集包含左表中的所有行，不管是否在右表中有匹配的行。没有被匹配的行可以被右表中的所有列被设置为NULL所识别。

再一次考虑如下两个表，t1和t2：

Table t1:   Table t2:
+----+----+  +----+----+
| i1 | c1 |  | i2 | c2 |
+----+----+  +----+----+
| 1  | a  |  | 2  | c  |
| 2  | b  |  | 3  | b  |
| 3  | c  |  | 4  | a  |
+----+----+  +----+----+
如果我们使用交叉连接来匹配这些表在t1.i1和t2.i2上，我们仅会得到输出为在两个表中都出现的值2和值3.
mysql> select t1.*, t2.* from t1, t2 where t1.i1 = t2.i2;
+------+------+------+------+
| i1   | c1   | i2   | c2   |
+------+------+------+------+
|    2 | b    |    2 | c    |
|    3 | c    |    3 | b    |
+------+------+------+------+
左连接会输出表t1中的每一行，而不管是否表2中有与它匹配的行。要写一个左连接，通过将LEFT JOIN介于两个表之间，而不是一个逗号，使用ON从句来指定匹配条件（而不是WHERE从句）

mysql> select t1.*, t2.* from t1 LEFT JOIN t2 on t1.i1=t2.i2;
+------+------+------+------+
| i1   | c1   | i2   | c2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | c    |
|    3 | c    |    3 | b    |
+------+------+------+------+
现在有一个输出，即使值1在表t2中并没有匹配的值。

当你想要找到那些左表的行，而这些行又没有在右表中被匹配到，那么LEFT JOIN是非常有用的。你可以添加where子句来寻找在右表中值为NULL的行，换句话说，该行在一个表中出现，但是在另一个表中没有出现。

mysql> SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2
  -> WHERE t2.i2 IS NULL;
+----+----+------+------+
| i1 | c1 | i2   | c2   |
+----+----+------+------+
| 1  | a  | NULL | NULL |
+----+----+------+------+
正常情况下，你真正关心的是左表中未匹配的值。展现在右表中的NULL列并不是你所感兴趣的，所以你可以在输出列中不显示它们： 

mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2
  -> WHERE t2.i2 IS NULL;
+----+----+
| i1 | c1 |
+----+----+
| 1  | a  |
+----+----+
LEFT JOIN实际上允许通过两种方式指定匹配条件。On是其中一种；它可以使用在你要联合的两列是否有相同的名字： 

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2;
另一种语法涉及到USING()子句；在概念上与ON是相似的，但是联合的列的名字或者列必须有相同的名字在每一个表中。例如，以下的语句联合mytbl1.b到mytbl2.b上： 

SELECT mytbl1.*, mytbl2.* FROM mytbl1 LEFT JOIN mytbl2 USING (b);
LEFT JOIN有一些同义词和变量。LEFT OUTER JOIN是一个LEFT JOIN的同义词。也有一个被MySQL接受的ODBC-style的表示法（OJ意思是“outer join”）： 

{ OJ tbl_name1 LEFT OUTER JOIN tbl_name2 ON join_expr }
NATURAL LEFT JOIN和LEFT JOIN很相似；它执行一个LEFT JOIN，匹配所有在左表和右表中有相同的列。

对LEFT JOIN来说有一点需要注意的是，如果你要联合的列没有被声明为NOT NULL，你可能在结果中得到有问题的行。例如，如果右表中包含一些列中有NULL值，你将不能够区分这些NULL与那些被标明为未匹配的行导致的NULL。

正如我们上面提到的，LEFT JOIN对于回答”哪些值丢失了的问题”是很有用的。当你想要知道哪些值在一个表中存在但是在另一个表中不存在时，你使用LEFT JOIN在两个表上并且寻找在第二个表中值为NULL的行。让我们考虑一个同样类型比起前面使用的t1和t2更复杂的例子。
我们在第一章中提到了一个分数保持项目，我们有一个student表列出了学生，event表列出了已发生的班级事件，和一个score表列出了每个班级事件的每个学生的分数。但是，如果一个学生在某次测试或者考试时生病了，score表将不会显示该学生该事件的分数值，所以该学生应该接受补考。我们怎样才能够得到这些缺失的记录，从而使得我们可以确保这些学生得到一次补考？

问题是来决定对指定的班级事件哪些学生没有分数。另一种方式说这个事情是，我们想要找出哪些学生和事件组合没有在分数表中出现。这个“哪个值没有出现”是一个我们需要LEFT JOIN的提示。这个join不和之前的例子那样简单，因为我们并不是只寻找没有出现在单一列中的值；我们要寻找的是两个列的组合。我们想要的这个组合是所有的学生/事件组合，这需要通过跨student表和event表来产生：

FROM student, event

然后我们得到那个join的结果并且执行一个LEFT JOIN到score表来找到匹配值：

FROM student, event
   LEFT JOIN score ON student.student_id = score.student.id
           AND event.event_id = score.event_id
注意ON子句允许在score表中的行根据在不同表中的匹配，那是解决该问题的关键。LEFT JOIN通过student表和event表的交叉连接产生的每一行，即使没有对应的记录在score表中。这些缺失的得分记录的行可以通过从得分表中的列为NULL来标识。我们可以选择这些记录在WHERE子句中。任何来自于score表中的列，因为我们寻找的是缺失的分数，可能是概念上最清晰地测试score列： 

WHERE score.score IS NULL
我们可以通过使用ORDER BY子句来对结果进行排序。两个最具有逻辑的排序是通过每个student的event或者每个event的student。这里我将选择第一种： 

ORDER BY student.student_id, event.event_id
现在我们需要做的事是对我们想要在输出中看到的列进行命名。我们做好了，这是我们最终的查询语句： 

SELECT
   student.name, student.student_id,
   event.data, event.event_id, event.type
FROM
   student, event
   LEFT JOIN score ON student.student_id = score.student_id AND event.event_id = score.event_id
WHERE
   score.score IS NULL
ORDER BY
   student.student_id, event.event_id;

运行上面的查询语句产生如下的结果： 

+-----------+------------+------------+----------+------+
| name      | student_id | date       | event_id | type |
+-----------+------------+------------+----------+------+
| Megan     |     1      | 2002-09-16 |    4     | Q    |
| Joseph    |     2      | 2002-09-03 |    1     | Q    |
| Katie     |     4      | 2002-09-23 |    5     | Q    |
| Devri     |     13     | 2002-09-03 |    1     | Q    |
| Devri     |     13     | 2002-10-01 |    6     | T    |
| Will      |     17     | 2002-09-16 |    4     | Q    |
| Avery     |     20     | 2002-09-06 |    2     | Q    |
| Gregory   |     23     | 2002-10-01 |    6     | T    |
| Sarah     |     24     | 2002-09-23 |    5     | Q    |
| Carter    |     27     | 2002-09-16 |    4     | Q    |
| Carter    |     27     | 2002-09-23 |    5     | Q    |
| Gabrielle |     29     | 2002-09-16 |    4     | Q    |
| Grace     |     30     | 2002-09-23 |    5     | Q    |
+-----------+------------+------------+----------+------+

这里有一个很巧妙的点。输出显示了student的IDs和event的IDs。student_id列既出现在student表中，也出现在score表中，所以第一眼，你可能会认为选择出的列会以student.student_id或者score.student_id命名。但结果并不是这样，因为对可能地找到我们感兴趣的记录的整个基础是所有的score表中返回NULL的列。选择score.student_id将在输出中产生NULL值唯一的一列。相同的原则应用在决定哪一个event_id列会被显示。它既在event表中出现，也在score表中出现，但是我们的查询语句选择event.event_id，因为score.event_id值总是为NULL。

使用子查询

MySQL 4.1引入的其中一个功能是支持子查询，也就是允许SELECT查询语句具备嵌入到另一个查询语句中。如下的例子是查找对应着tests('T')的event记录的IDs，并且使用它们来选择分数记录：

SELECT * FROM score
WHERE event_id IN (SELECT event_id FROM event WHERE type = 'T');

在一些情况下，子查询也可以重写为joins。我将在本章稍后介绍它。如果你的MySQL的版本小于4.1，你会发现子查询从写技术特别有用。

MySQL支持的一个相关的功能是你可以基于一个表中的内容来删除或者更新另一个表中的记录。例如，你可能想要删除一个表中未在别的表中匹配的记录，或者将一个表中的列拷贝到另一个表中。这些操作类型会在本章随后的“多表删除和更新”部分中谈到。
这里有一些写子查询的形式；这部分仅仅概述其中的一部分。

  • 使用一个子查询来产生参考值。在这个例子中，你想要inner SELECT来标识一个单一的值用在于outer SELECT的比较上。例如，为了标识发生在‘2002-09-23’号的测验的分数，使用inner SELECT来决定测验事件ID，然后与在outer SELECT匹配分数记录：
  • SELECT * FROM score
WHERE event_id = 
(SELECT event_id FROM event WHERE date = '2002-09-23' AND type = 'Q');
  • 使用这种形式的子查询，内部查询先于比较运算符，inner join必须产生不能超过一个的单一值（那就是，一行或者一列）。如果它产生多个值，查询就会失败。（在一些情形下，可能需要通过LIMIT 1来限制内部查询的结果来满足这个条件）
  • 这种形式的子选择对于你想要在WHERE子句中使用聚合函数来说是非常方便的。例如，想要决定哪一个总统最先出生，你可以尝试如下语句：
  • SELECT * FROM president WHERE birth = MIN(birth);
  • 这并不会产生你想要的结果，因为你不能在WHERE子句中使用聚合函数。（WHERE子句决定选择哪一些记录，但是MIN()的值并不知道，直到这些记录已经被选择之后）。但是你可以使用子查询来产生最小的出生日期，如下所示：
  • SELECT * FROM president
WHERE birth = (SELECT MIN(birth) FROM president);
  • EXISTS和NOT EXISTS子查询。通过从外层查询传入内层来看是否满足在内层查询中的条件。因为这个原因，如果它们是含混不清的（出现在不止一个表中）你需要用表名量化列名。EXISTS和NOT EXISTS子查询对于找到在一个表中的记录匹配或者不匹配另一个表中的记录是很有用的。再一次引用一下我们的t1和t2表：
  • Table t1:   Table t2:
+----+----+  +----+----+
| i1 | c1 |  | i2 | c2 |
+----+----+  +----+----+
| 1  | a  |  | 2  | c  |
| 2  | b  |  | 3  | b  |
| 3  | c  |  | 4  | a  |
+----+----+  +----+----+
  • 如下的查询标明了在两个表中的匹配--那就是，在两个表中都出现的值：
  • mysql> SELECT i1 FROM t1
  -> WHERE EXISTS (SELECT * FROM t2 WHERE t1.i1 = t2.i2);
+----+
| i1 |
+----+
| 2  |
| 3  |
+----+
  • NOT EXISTS标明不匹配--只出现在一个表中，在另一个表中不存在：
  • mysql> SELECT i1 FROM t1
  -> WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.i1 = t2.i2);
+----+
| i1 |
+----+
| 1  |
+----+
  • 通过这种形式的子查询，内部查询使用*作为输出列表列。没有必要显式地命名列，因为内部查询基于是否返回行来评估真或者假，并不基于那一行是否包含特定地值。在MySQL中，你可以对列的选择列表写任何事情，但是如果你想要当内部SELECT成功时显式地你要返回一个真值，你可能需要像下面这样写查询语句：
  • SELECT i1 FROM t1
WHERE EXISTS (SELECT 1 FROM T2 WHERE t1.i1 = t2.i2);
SELECT i1 FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.i1 = t2.i2);
  • IN和NOT IN子查询。IN和NOT IN形式的子查询应该返回一个单一的列的值从内SELECT来在一个比较中评估。例如，之前的EXISTS和NOT EXISTS查询可以通过使用IN和NOT IN写为如下这样：
  • mysql> SELECT i1 FROM t1 WHERE i1 IN (SELECT i2 FROM t2);
+----+
| i1 |
+----+
| 2  |
| 3  |
+----+
mysql> SELECT i1 FROM t1 WHERE i1 NOT IN (SELECT i2 FROM t2);
+----+
| i1 |
+----+
| 1  |
+----+

像Join一样重写子查询

对于MySQL 4.1之前的版本，是没有子查询的。但是，经常重写一个查询语句：以join的形式使用子查询。实际上，即使你用的是4.1 后的MySQL，你可能倾向于以子查询的方式来写。join有时候比起子查询来说效率更高。

重写子查询来选择匹配的值

如下是包含子查询的一个例子；它从分数表中选择分数对所有的测试（那就是，它忽略了小测验）：

SELECT * FROM score WHERE event_id IN (SELECT event_id FROM event WHERE type = 'T');

通过将子查询转换为join可以重写出相同功能的语句：

SELECT score.* FROM score, event WHERE score.event_id = event.event_id AND event.type = 'T';

像另外一个例子，如下的查询语句查询女学生的分数：

SELECT * FROM score
WHERE student_id IN (SELECT student_id FROM student WHERE sex = 'F');

这也可以转为Join，如下所示：

SELECT score.* FROM score, student 
WHERE score.student_id = student.student_id AND student.sex = 'F';
这里有一个模式。子查询语句遵从如下的形式： 

SELECT * FROM table1
WHERE column1 IN (SELECT column2a FROM table2 WHERE column2b = value);
这些查询可以用如下的形式转为join： 

SELECT table1.* FROM table1, table2
WHERE table1.column1 = table2.column2a AND table2.column2b = value;

重写子查询来选择不匹配的值

子查询的另一个常见的类型是查询在一个表中出现，但在另一个表中不出现的值。正如我们之前看到的，“这些值不存在”的问题是一个使用LEFT JOIN的线索。如下是一个带有子查询的查询语句，查询不在absence表中的学生（找到出勤率很好的学生）:

SELECT * FROM student
WHERE student_id NOT IN (SELECT student_id FROM absence);
这个查询语句可以用一个LEFT JOIN重写为： 

SELECT student.*
FROM student LEFT JOIN absence ON student.student_id = absence.student_id
WHERE absence.student_id IS NULL;
子查询语句的通用形式如下： 

SELECT * FROM table1
WHERE column1 NOT IN (SELECT column2 FROM table2);
用LEFT JOIN则可以重写为： 

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2
WHERE table2.column2 IS NULL;
这里table2.column2声明为非空。

从多个表中用UNION检索

如果你想要通过从一个接一个地多个表中选择记录创建一个结果集，你可以使用一个UNION语句。UNION首次出现在MySQL4中，当然之前也有一些其它选项。

假设你有三个表，t1, t2, t3，它们看起来如下：

mysql> SELECT * FROM t1;
+------+-------+
| i    | c     |
+------+-------+
|  1   | red   |
|  2   | blue  |
|  3   | green |
+------+-------+
mysql> SELECT * FROM t2;
+------+------+
| i    | c    |
+------+------+
|  -1  | tan  |
|  1   | red  |
+------+------+
mysql> SELECT * FROM t3;
+------------+------+
| d          | i    |
+------------+------+
| 1904-01-01 | 100  |
| 2004-01-01 | 200  |
| 2004-01-01 | 200  |
+------------+------+

表一和表2含有整型值和字符串列，表3含有时间和整形列。用UNION语句来连接多个检索，只要写一些SELECT语句，并将UNION介于其中。例如，从每个表中选择整形值的列，像如下：

mysql> SELECT i FROM t1 UNION SELECT i FROM t2 UNION SELECT i FROM t3;
+------+
| i    |
+------+
|  1   |
|  2   |
|  3   |
|  -1  |
| 100  |
| 200  |
+------+

UNION具有如下的属性：

  • UNION结果的列的数据名和数据类型来自于第一个SELECT的数据名和数据类型。在UNION中的第二个SELECT和子查询的SELECT语句必须选择相同数量的列，但是它不需要有相同的名字或者类型。列由位置匹配，而不是名字匹配，这就是为什么这两种返回不同结果的原因：
  • mysql> SELECT i, c FROM t1 UNION SELECT i, d FROM t3;
+------+------------+
| i  | c     |
+------+------------+
|  1 | red    |
|  2 | blue    |
|  3 | green   |
| 100 | 1904-01-01 |
| 200 | 2004-01-01 |
+------+------------+
mysql> SELECT i, c FROM t1 UNION SELECT d, i FROM t3;
+------+-------+
| i  | c   |
+------+-------+
|  1 | red  |
|  2 | blue |
|  3 | green |
| 1904 | 100  |
| 2004 | 200  |
+------+-------+
  • 上面的两个例子中，从表t1中选择的列(i和c)决定了在UNION中使用的类型。这些列有整形和字符串型，所以当从表t3中选择值时需要有类型转换。对于第一个查询，d从时间类型转换为字符串类型。这种情况没有导致信息损失。但是对月第二个查询来说，d从时间类型转为整形，这就带来了信息的损失，而且i从整形转换为了字符串型。
  • 默认情况下，UNION会从结果集中删除重复的行：
  • mysql> SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3;
+------+-------+
| i    | c     |
+------+-------+
|  1   | red   |
|  2   | blue  |
|  3   | green |
|  -1  | tan   |
| 1904 | 100   |
| 2004 | 200   |
+------+-------+
  • t1和t2都有包含该值为1和'red'的行，但是在输出中只显示了一行。同样，t3有两行包含'2014-01-01'和200，其中一行就被删掉了。
  • 如果你想要保留重复，将第一个UNION关键字后加一个ALL：
  • mysql> SELECT * FROM t1 UNION ALL SELECT * FROM t2 UNION SELECT * FROM t3;
+------+-------+
| i    | c     |
+------+-------+
|  1   | red   |
|  2   | blue  |
|  3   | green |
|  -1  | tan   |
|  1   | red   |
| 1904 | 100   |
| 2004 | 200   |
| 2004 | 200   |
+------+-------+
  • 为了对UNION的结果进行排序，在最后一个SELECT后加上ORDER BY语句；它将整个查询语句结果作为一个整体来看待。但是，因为UNION使用来自于第一个SELECT的列名为其最终的列名，ORDER BY应该指的是这些名字，而不是从最后一个SELECT中得到的名字，如果它们有不同的话。
  • mysql> SELECT i, c FROM t1 UNION SELECT i, d FROM t3
  -> ORDER BY c;
+------+------------+
| i    | c          |
+------+------------+
| 100  | 1904-01-01 |
| 200  | 2004-01-01 |
|  2   | blue       |
|  3   | green      |
|  1   | red        |
+------+------------+
  • 你也可以对在UNION中单独的SELECT语句指定ORDER BY语句。要这么做，只需要将SELECT（包括ORDER BY）语句用小括号括起来即可：
  • mysql> (SELECT i, c FROM t1 ORDER BY i DESC)
  -> UNION (SELECT i, c FROM t2 ORDER BY i);
+------+-------+
| i    | c     |
+------+-------+
|  3   | green |
|  2   | blue  |
|  1   | red   |
|  -1  | tan   |
+------+-------+
  • LIMIT也可以作用在UNION上，类似于在ORDER BY上的应用。如果将其加在语句的最后，它将对UNION的结果作为一个整体来使用：
  • mysql> SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3
  -> LIMIT 1;
+------+------+
| i    | c    |
+------+------+
|  1   | red  |
+------+------+
  • 如果将LIMIT在每一个SELECT中括起来使用，它只应用在那一个SELECT上：
  • mysql> (SELECT * FROM t1 LIMIT 1)
  -> UNION (SELECT * FROM t2 LIMIT 1)
  -> UNION (SELECT * FROM t3 LIMIT 1);
+------+------+
| i    | c    |
+------+------+
|  1   | red  |
|  -1  | tan  |
| 1904 | 100  |
+------+------+
  • 你不需要从不同的表中来选择。你可以使用不同的条件来选择相同表的不同的子集。这对于作为一个替代的来运行不同的SELECT查询来说是很有用的，因为你从一个单一的结果集中得到了所有行，而不是多个结果集中。
在早于MySQL 4的版本前，是没有UNION的，但是有一个比较困难的解决方法是通过从每一个表中选择行，并将其放到一个临时表中，最后对这个临时表的内容进行选择。在MySQL 3.23以及以后的版本中，你可以简单地处理这些问题通过允许服务器来为你创建holding表。当然，你也可以将这个表设置为临时表，当在这个服务器中的session终止时，它会自动地删除这个表。为了更高的效率，使用一个HEAP（in memory）表。 

CREATE TEMPORARY TABLE tmp TYPE = HEAP SELECT ... FROM T1 WHERE ...;
INSERT INTO tmp SELECT ... FROM t2 WHERE ... ;
INSERT INTO tmp SELECT ... FROM t3 WHERE ... ;
...
SELECT * FROM tmp ORDER BY ... ;
因为tmp是一个临时表，但你的客户端终止时，服务器会自动将该表删除。（当然，你也可以显式地删除该表，只要你完成了工作，使得服务器释放与之有关的资源。如果你要持续地执行进一步地查询，尤其是对HEAP表，这是一个很好的主意。）

对于早于3.23的MySQL版本，概念是相似的，但是细节上有较大的差别，因为那时是不存在HEAP表类型以及临时表的，就是CREATE TABLE ... SELECT。为了适应上述的进程，首先在检索任何行到其中之前必须显式地创建一个表。（唯一支持的的表类型是ISAM，所以你不能使用TYPE选项）然后将记录检索到表中。当你完成了之后，你必须显式地使用DROP TABLE来删除表，因为服务器不会自动来删除一个表。

CREATE TABLE tmp (column1, column2, ...);
INSERT INTO tmp SELECT ... FROM t1 WHERE ... ;
INSERT INTO tmp SELECT ... FROM t2 WHERE ... ;
INSERT INTO tmp SELECT ... FROM t3 WHERE ... ;
SELECT * FROM tmp ORDER BY ... ;
DROP TABLE tmp;


本文翻译自这里

冯西的技术博客
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
DNSLookup:用于从提供的域中检索DNS记录类型值的CFC
05-25
用于从提供的域中检索DNS记录类型值的CFC。 最终这是一项正在进行的工作，根据给定记录类型可能产生的结果，有相当一部分要添加/编辑。 DNS记录类型的列表可在找到。 一些注意事项。 。 。 数据的格式已经以CFC...
mysql基础：多表查询。
qq_42777490的博客
07-03 604
外键、主键、笛卡尔积、多表查询、内连接、隐式内连接、显式内连接、左外连接、右外连接、自连接、联合查询、子查询、标量子查询、列子查询、行子查询、表子查询、嵌套查询
SQL之多表查询
LP'S Blog
03-30 8851
一对多(多对一) 多对多一对一一对多(多对一)案例:部门与员工的关系关系:一个部门对应多个员工，一个员工对应一个部门实现:在多的一方建立外键，指向一的一方的主键多对多案例:学生与课程的关系关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键一对一案例:用户与用户详情的关系关系:一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。
实验3  通过SQL进行表查询
MrLuo8的博客
04-23 1万+
实验名称 实验3 通过SQL进行表查询 日期 2020年 6 月 11 日 一、实验目的： 掌握简单SQL查询语句的应用，包括like、top、order by、compute 和聚集函数的应用。 掌握两个表以上的连接查询的应用，包括嵌套查询及聚集函数操作。 二、实验环境： PC + Windows + SQL Server 三、实验内容 1．基本查询 （1）查询Student表中全体学生的全部信息。 截图： （2）查询全体...
SQL中EXISTS与IN的效率问题
z69183787的专栏
05-16 1万+
一起学习一下； 有两个简单例子，以说明 “exists”和“in”的效率问题 1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;     T1数据量小而T2数据量非常大时，T1 2) select * from T1 where T1.a in (select T2.a from T2) ;  
MySQL的select多表查询
南宫乘风-Linux运维-虚拟化容器-Python编程 ownit.top
04-17 2549
select 语句： select 语句一般用法为: select 字段名 from tb_name where 条件 ; select 查询语句类型一般分为三种： 单表查询，多表查询，子查询 最简单的单表查询 : select * from tb_name; *表示，所有字段 查询特定字段(投影)： select 字段名1，字段名2， from tb_name; where 语句...
glosario-r：glosario创建和检索多语言词汇表
02-07
格洛萨里奥 glosario允许用户创建和检索多语言词汇表。 默认情况下， glosario提供对由The Carpentries托管的访问。 该存储库还记录了可​​以由glosario管理的词汇表的预期结构。 还有一个。安装glosario仍处于开发...
concat-record:concat记录是EPICS记录，用于将标量常量，从其他记录中检索的标量值和从其他记录（例如从compress，subArray和波形记录以及其他concat记录中检索到）的数组值进行归纳到数组中。记录
03-14
concat记录用于执行一个或多个标量和/或数组值的过程变量（PV）以及标量常量和json链接常量数组值的合并（后者自2.1版以来是新的）。 可以通过诸如aai，compress，subArray和/或波形记录之类的记录以及其他concat...
Sphinx全文检索引擎 for Windows-PHP
07-02
Sphinx是一个基于SQL的全文检索引擎，可以结合MySQL,PostgreSQL做全文搜索...支持每个文档多个全文检索域(默认最大32个);支持每个文档多属性;支持断词;支持单字节编码与UTF-8编码; Tags：Sphinx 全文检索引擎 全文搜索
bcdata:R包，用于从BC数据目录中搜索和检索数据
03-11
它会尽力识别和读取zip文件中的数据，但是，如果zip中有多个数据文件，或者bcdata不知道如何导入的数据文件，它将失败。 如果您在BC Data Catalog中遇到bcdata当前不支持的文件类型，请提出。参考 安装您可以直接从...
简单例子看懂联合查询SQL
随笔一记/**
07-28 360
使用联合查询通常会比使用多表查询快： 例子： select * from T1 ,T2 where T1.userid = T2.userid (其实这样的结果等同于select * from T1 inner join T2 on T1.userid=T2.userid )。 把两个表中都存在userid的行拼成一行(即内联)，但后者的效率会比前者高很多，建议用后者(内联)的写法。 左联(lef...
第三章 在多张表格中检索数据
qq_36627779的博客
02-07 137
在多张表格中检索数据。
数据库学习 - select（多表联查）
热门推荐
linAnPin
04-13 9万+
多表联合查询 多表联合查询可以通过连接运算实现，而连接运算又可以通过广义笛卡尔积后在进行选择运算来实现。   Select 多表联合查询语句 Select 列名 [[,列名]...] from 表名1,表名2,,... Where 查询条件;   可以使用as为表或者字段（属性）取别名；   Select 列名 [[,列名]...] from 表名1 [as] 别名1,表名
多个表中检索数据
studyeglish的博客
05-09 334
多个表中检索数据INNER JOINSJoining Across Databases（跨库合并表）Self Joins（自联结）Joining Multiple TableCompound Join Conditions（复合合并的条件）Implicit Join Syntax（隐式合并语法：不建议）Outer Joins（外连接）Outer Joins Between Multiple Tables（多表间外连接）Self Outer Joins（表的自我外连接）The USING Clause（USI
那如何从这个表中实现ETL
最新发布
07-15
从表中进行ETL（数据提取、转换和加载）可以通过以下步骤实现： 1. 数据提取（Extract）从源表中提数据。在PHP中，你可以使用数据库连接和查询语句来检索数据。在Go中，你可以使用数据库驱动和SQL查询来实现相同的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值