深入SELECT语句的查询功能（二）(1)
发布时间：2015-08-05 00:00 来源：未知
上接《深入SELECT语句的查询功能（一）》
检索语句与多个表的连接
SELECT语句不仅可以从单个表中检索数据，也可以通过连接多个表来检索数据。这里将介绍全连接和左连接的作用。
我们创建两个表作为例子。
|
mysql> CREATE TABLE first -> ( -> id TINYINT, -> first_name CHAR(10) -> ); |
录入如下数据：
+------+-----------+
| id | first_name|
+------+-----------+
| 1 | Tom |
| 2 | Marry |
| 3 | Jarry |
+------+-----------+
|
mysql> CREATE TABLE last -> ( -> id TINYINT, -> last_name CHAR(10) -> ); |
左连接
左连接：全连接给出FROM子句中所有表都有匹配的行。对于左连接，不仅匹配类似前面的行记录，而且还显示左边的表有而右边的表中无匹配的行。对于这样的行，从右边表选择的列均被显示为NULL。这样，每一匹配的行都从左边的表被选出，而如果右边表有一个匹配的行，则被选中，如果不匹配，行仍然被选中，不过，其中右边相应的列在结果集中均设为NULL。即，LEFT JOIN强制包含左边表的每一行，而不管右边表是否匹配。
语法：SELECT FROM table_reference LEFT JOIN table_reference ON conditional_expr
其中table_reference为连接的表，ON子句后接类似WHERE子句的条件。
下面我们详细讲述左连接的使用：
首先，返回一个全连接的结果集：
mysql> SELECT * FROM first,last;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 2 | Stone |
| 1 | Tom | 3 | White |
| 2 | Marry | 3 | White |
| 3 | Jarry | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | 4 | Donald |
| 3 | Jarry | 4 | Donald |
+------+------------+------+-----------+
注意上面的结果，下面的例子要与这个例子对照。
我们在给出一个限制条件的查询：
mysql> SELECT * FROM first,last WHERE first.id=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 3 | White |
+------+------------+------+-----------+
这个结果类似于是从上一个全连接中选择出first.id>last.id 的行。
现在我们给出一个真正的左连接的例子，你可以仔细观察它的结果，要了解检索的记录顺序：
mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | NULL | NULL |
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 3 | White |
+------+------------+------+-----------+
上面的结果，即用左边表的每一行与右边表匹配，如果匹配，则选择到结果集中，如果没有匹配，则结果集中，右边表相应的列置为NULL。
为了进一步理解这一点，我们给出一个有点奇怪的例子：
mysql> SELECT * FROM first LEFT JOIN last ON first.id=1;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 1 | Tom | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | NULL | NULL |
| 3 | Jarry | NULL | NULL |
+------+------------+------+-----------+
因为，在结果的最后两行有似乎你不希望的结果。记住，如果只有ON子句的条件，那么左边表的每一行都会返回，只是如果没有匹配的右边表（虽然本例没有约束右边表的列），则记录集中显示为NULL。
前面只是帮助你理解左连接，下面LEFT JOIN的一些有用的技巧。LEFT JOIN最常见的是与WHERE子句共同使用。
使用IS NULL或者IS NOT NULL操作符可以筛选NULL或者非NULL值的列，这是最常见的技巧。
例如，选出first.id=last.id的组合,并且剔除其中没有右表的匹配记录：
|
mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NOT NULL; |
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 3 | White |
+------+------------+------+-----------+
你看到的是这样做的例子结果与语句
SELECT * FROM first,last WHERE first.id=last.id
的输出是相同的。
又如，检索id值只在左边表出现，而不再右边表出现的记录：
|
mysql> SELECT first.* FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NULL; |
+------+------------+
| id | first_name |
+------+------------+
| 1 | Tom |
+------+------------+
这个语句是不能用功能相同的带WHERE子句的全连接代替的。
注意：全连接和左连接的结果集排列顺序是不同的，例如：
mysql> SELECT * FROM first,last WHERE first.id!=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 3 | Jarry | 2 | Stone |
| 1 | Tom | 3 | White |
| 2 | Marry | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | 4 | Donald |
| 3 | Jarry | 4 | Donald |
+------+------------+------+-----------+
mysql> SELECT * FROM first LEFT JOIN last ON first.id!=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 1 | Tom | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | 3 | White |
| 2 | Marry | 4 | Donald |
| 3 | Jarry | 2 | Stone |
| 3 | Jarry | 4 | Donald |
+------+------------+------+-----------+
总结
本节的内容非常繁杂，各小节之间可能没有什么联系，但是本节所述的都是检索数据时很常用的技巧，主要的一些内容如下：
上接《深入SELECT语句的查询功能（一）》
检索语句与多个表的连接
SELECT语句不仅可以从单个表中检索数据，也可以通过连接多个表来检索数据。这里将介绍全连接和左连接的作用。
我们创建两个表作为例子。
|
mysql> CREATE TABLE first -> ( -> id TINYINT, -> first_name CHAR(10) -> ); |
录入如下数据：
+------+-----------+
| id | first_name|
+------+-----------+
| 1 | Tom |
| 2 | Marry |
| 3 | Jarry |
+------+-----------+
|
mysql> CREATE TABLE last -> ( -> id TINYINT, -> last_name CHAR(10) -> ); |
录入数据
+------+-----------+
| id | last_name |
+------+-----------+
| 2 | Stone |
| 3 | White |
| 4 | Donald |
+------+-----------+
全连接
全连接：在检索时指定多个表，将每个表用都好分隔，这样每个表的数据行都和其他表的每行交叉产生所有可能的组合，这样就是一个全连接。所有可能的组和数即每个表的行数之和。
那么观察下面的检索的结果：
mysql> SELECT * FROM first,last;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 2 | Stone |
| 1 | Tom | 3 | White |
| 2 | Marry | 3 | White |
| 3 | Jarry | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | 4 | Donald |
| 3 | Jarry | 4 | Donald |
+------+------------+------+-----------+
你看到的是输出的结果集中共有3×3=9 行，这就是全连接的结果。
你也可以这样使用SQL语句：
mysql> SELCT first.*,last.* FROM first,last;
输出结果与上面的例子相同，并无二致。记录集的输出的排序是以FROM子句后的表的顺序进行，即先排列位置靠前的表，即使你改变记录集中列的显示顺序，例如下面的例子：
mysql> SELECT last.*,first.* FROM first,last;
+------+-----------+------+------------+
| id | last_name | id | first_name |
+------+-----------+------+------------+
| 2 | Stone | 1 | Tom |
| 2 | Stone | 2 | Marry |
| 2 | Stone | 3 | Jarry |
| 3 | White | 1 | Tom |
| 3 | White | 2 | Marry |
| 3 | White | 3 | Jarry |
| 4 | Donald | 1 | Tom |
| 4 | Donald | 2 | Marry |
| 4 | Donald | 3 | Jarry |
+------+-----------+------+------------+
上面的例子是两个非常小的表的例子，如果是几个非常大的表的全连接，例如，两个行数分别为1000的表，这样的连接可以产生非常大的结果集合1000×1000＝100万行。而实际上你并不需要这么多行的结果，通常你需要使用一个WHERE从句来限制返回的记录集的行数：
mysql> SELECT * FROM first,last WHERE first.id= last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 3 | White |
+------+------------+------+-----------+
左连接
左连接：全连接给出FROM子句中所有表都有匹配的行。对于左连接，不仅匹配类似前面的行记录，而且还显示左边的表有而右边的表中无匹配的行。对于这样的行，从右边表选择的列均被显示为NULL。这样，每一匹配的行都从左边的表被选出，而如果右边表有一个匹配的行，则被选中，如果不匹配，行仍然被选中，不过，其中右边相应的列在结果集中均设为NULL。即，LEFT JOIN强制包含左边表的每一行，而不管右边表是否匹配。
语法：SELECT FROM table_reference LEFT JOIN table_reference ON conditional_expr
其中table_reference为连接的表，ON子句后接类似WHERE子句的条件。
下面我们详细讲述左连接的使用：
首先，返回一个全连接的结果集：
mysql> SELECT * FROM first,last;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 2 | Stone |
| 1 | Tom | 3 | White |
| 2 | Marry | 3 | White |
| 3 | Jarry | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | 4 | Donald |
| 3 | Jarry | 4 | Donald |
+------+------------+------+-----------+
注意上面的结果，下面的例子要与这个例子对照。
我们在给出一个限制条件的查询：
mysql> SELECT * FROM first,last WHERE first.id=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 3 | White |
+------+------------+------+-----------+
这个结果类似于是从上一个全连接中选择出first.id>last.id 的行。
现在我们给出一个真正的左连接的例子，你可以仔细观察它的结果，要了解检索的记录顺序：
mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | NULL | NULL |
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 3 | White |
+------+------------+------+-----------+
上面的结果，即用左边表的每一行与右边表匹配，如果匹配，则选择到结果集中，如果没有匹配，则结果集中，右边表相应的列置为NULL。
为了进一步理解这一点，我们给出一个有点奇怪的例子：
mysql> SELECT * FROM first LEFT JOIN last ON first.id=1;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 1 | Tom | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | NULL | NULL |
| 3 | Jarry | NULL | NULL |
+------+------------+------+-----------+
因为，在结果的最后两行有似乎你不希望的结果。记住，如果只有ON子句的条件，那么左边表的每一行都会返回，只是如果没有匹配的右边表（虽然本例没有约束右边表的列），则记录集中显示为NULL。
前面只是帮助你理解左连接，下面LEFT JOIN的一些有用的技巧。LEFT JOIN最常见的是与WHERE子句共同使用。
使用IS NULL或者IS NOT NULL操作符可以筛选NULL或者非NULL值的列，这是最常见的技巧。
例如，选出first.id=last.id的组合,并且剔除其中没有右表的匹配记录：
|
mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NOT NULL; |
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 2 | Marry | 2 | Stone |
| 3 | Jarry | 3 | White |
+------+------------+------+-----------+
你看到的是这样做的例子结果与语句
SELECT * FROM first,last WHERE first.id=last.id
的输出是相同的。
又如，检索id值只在左边表出现，而不再右边表出现的记录：
|
mysql> SELECT first.* FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NULL; |
+------+------------+
| id | first_name |
+------+------------+
| 1 | Tom |
+------+------------+
这个语句是不能用功能相同的带WHERE子句的全连接代替的。
注意：全连接和左连接的结果集排列顺序是不同的，例如：
mysql> SELECT * FROM first,last WHERE first.id!=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 3 | Jarry | 2 | Stone |
| 1 | Tom | 3 | White |
| 2 | Marry | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | 4 | Donald |
| 3 | Jarry | 4 | Donald |
+------+------------+------+-----------+
mysql> SELECT * FROM first LEFT JOIN last ON first.id!=last.id;
+------+------------+------+-----------+
| id | first_name | id | last_name |
+------+------------+------+-----------+
| 1 | Tom | 2 | Stone |
| 1 | Tom | 3 | White |
| 1 | Tom | 4 | Donald |
| 2 | Marry | 3 | White |
| 2 | Marry | 4 | Donald |
| 3 | Jarry | 2 | Stone |
| 3 | Jarry | 4 | Donald |
+------+------------+------+-----------+
总结
本节的内容非常繁杂，各小节之间可能没有什么联系，但是本节所述的都是检索数据时很常用的技巧，主要的一些内容如下：
1、为表和列使用别名
2、注意NULL值在查询中的使用
3、注意表名、列名、别名和字符串的大小写问题
4、如何避免取出重复的记录
