MySQL知识（十）——连接查询与子查询

1 连接查询

　　连接查询是关系数据库中最主要的查询，主要包括连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。

1.1 内连接查询

　　内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的纪录。在内连接查询中，只有满足条件的记录才能出现在结果关系中。
　　（1）fruits表和suppliers表的结构：

mysql> DESC fruits;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id    | char(10)     | NO   | PRI | NULL    |       |
| s_id    | int(11)      | NO   |     | NULL    |       |
| f_name  | char(255)    | NO   |     | NULL    |       |
| f_price | decimal(8,2) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set

mysql> DESC suppliers;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| s_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| s_name | char(50) | NO   |     | NULL    |                |
| s_city | char(50) | YES  |     | NULL    |                |
| s_zip  | char(10) | YES  |     | NULL    |                |
| s_call | char(50) | NO   |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
5 rows in set

　　（2）SELECT、FROM、WHERE查询

mysql> SELECT suppliers.s_id,s_name,f_name,f_price
    -> FROM fruits,suppliers
    -> WHERE fruits.s_id=suppliers.s_id;
+------+----------------+------------+---------+
| s_id | s_name         | f_name     | f_price |
+------+----------------+------------+---------+
|  101 | FastFruit Inc. | apple      | 5.2     |
|  103 | ACME           | apricot    | 2.2     |
|  101 | FastFruit Inc. | blackberry | 10.2    |
|  104 | FNK Inc.       | berry      | 7.6     |
|  107 | DK Inc.        | xxxx       | 3.6     |
|  102 | LT Supplies    | orange     | 11.2    |
|  105 | Good Set       | melon      | 8.2     |
|  101 | FastFruit Inc. | cherry     | 3.2     |
|  104 | FNK Inc.       | lemon      | 6.4     |
|  106 | Just Eat Ours  | mango      | 15.6    |
|  105 | Good Set       | xbabay     | 2.6     |
|  105 | Good Set       | xxtt       | 11.6    |
|  103 | ACME           | coconut    | 9.2     |
|  102 | LT Supplies    | banana     | 10.3    |
|  102 | LT Supplies    | grape      | 5.3     |
|  107 | DK Inc.        | xbababa    | 3.6     |
+------+----------------+------------+---------+
16 rows in set

　　（3）使用INNER JOIN语法进行查询

mysql> SELECT suppliers.s_id,s_name,f_name,f_price
    -> FROM fruits INNER JOIN suppliers
    -> ON fruits.s_id=suppliers.s_id;
+------+----------------+------------+---------+
| s_id | s_name         | f_name     | f_price |
+------+----------------+------------+---------+
|  101 | FastFruit Inc. | apple      | 5.2     |
|  103 | ACME           | apricot    | 2.2     |
|  101 | FastFruit Inc. | blackberry | 10.2    |
|  104 | FNK Inc.       | berry      | 7.6     |
|  107 | DK Inc.        | xxxx       | 3.6     |
|  102 | LT Supplies    | orange     | 11.2    |
|  105 | Good Set       | melon      | 8.2     |
|  101 | FastFruit Inc. | cherry     | 3.2     |
|  104 | FNK Inc.       | lemon      | 6.4     |
|  106 | Just Eat Ours  | mango      | 15.6    |
|  105 | Good Set       | xbabay     | 2.6     |
|  105 | Good Set       | xxtt       | 11.6    |
|  103 | ACME           | coconut    | 9.2     |
|  102 | LT Supplies    | banana     | 10.3    |
|  102 | LT Supplies    | grape      | 5.3     |
|  107 | DK Inc.        | xbababa    | 3.6     |
+------+----------------+------------+---------+
16 rows in set

　　（4）自连接
　　在一个连接查询中，涉及到的连个表都是同一个表。自连接是一个特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
　　例如，查询f_id=’a1’的水果供应商提供的其他水果种类：

mysql> SELECT f1.f_id,f1.f_name
    -> FROM fruits AS f1,fruits AS f2
    -> WHERE f1.s_id=f2.s_id AND f2.f_id='a1';
+------+------------+
| f_id | f_name     |
+------+------------+
| a1   | apple      |
| b1   | blackberry |
| c0   | cherry     |
+------+------------+
3 rows in set

1.2 外连接查询

　　连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。
　　外连接分为左外连接和右外连接
　　①LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
　　②RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
　　（1）左连接
　　左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。

mysql> SELECT customers.c_id,orders.o_num
    -> FROM customers LEFT OUTER JOIN orders
    -> ON customers.c_id=orders.c_id;
+-------+-------+
| c_id  | o_num |
+-------+-------+
| 10001 | 30001 |
| 10003 | 30002 |
| 10004 | 30003 |
| 10001 | 30005 |
| 10002 | NULL  |
+-------+-------+
5 rows in set

　　（2）右连接
　　右连接时左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。

mysql> SELECT customers.c_id,orders.o_num
    -> FROM customers RIGHT OUTER JOIN orders
    -> ON customers.c_id=orders.c_id;
+-------+-------+
| c_id  | o_num |
+-------+-------+
| 10001 | 30001 |
| 10003 | 30002 |
| 10004 | 30003 |
| NULL  | 30004 |
| 10001 | 30005 |
+-------+-------+
5 rows in set

1.3 复合条件连接查询

　　复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。

mysql> SELECT customers.c_id,orders.o_num
    -> FROM customers INNER JOIN orders
    -> ON customers.c_id=orders.c_id AND customers.c_id=10001;
+-------+-------+
| c_id  | o_num |
+-------+-------+
| 10001 | 30001 |
| 10001 | 30005 |
+-------+-------+
2 rows in set

2 子查询

　　子查询是一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或多个表。子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套。

2.1 带ANY、SOME关键字的子查询

　　ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

mysql> CREATE TABLE tbl1(num1 INT NOT NULL);
Query OK, 0 rows affected

mysql> CREATE TABLE tbl2(num1 INT NOT NULL);
Query OK, 0 rows affected

mysql> INSERT INTO tbl1 VALUES(1),(5),(13),(27);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl2 VALUES(6),(14),(11),(20);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT num1
    -> FROM tbl1
    -> WHERE num1>ANY(SELECT num1 FROM tbl2);
+------+
| num1 |
+------+
|   13 |
|   27 |
+------+
2 rows in set

　　只要大于tbl2表中任意一个数即为符合条件的结果。

2.2 带ALL关键字的子查询

　　使用ALL需要满足所有内层查询的条件。
　　使用前面的tbl1表和tbl2表：

mysql> SELECT num1
    -> FROM tbl1
    -> WHERE num1>ALL(SELECT num1 FROM tbl2);
+------+
| num1 |
+------+
|   27 |
+------+
1 row in set

2.3 带EXISTS关键字的子查询

　　EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
　　使用前面的fruits表和suppliers表：
　　（1）查询suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的记录：

mysql> SELECT * FROM fruits
    -> WHERE EXISTS
    -> (SELECT s_name FROM suppliers WHERE s_id=107);
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      | 5.2     |
| a2   |  103 | apricot    | 2.2     |
| b1   |  101 | blackberry | 10.2    |
| b2   |  104 | berry      | 7.6     |
| b5   |  107 | xxxx       | 3.6     |
| bs1  |  102 | orange     | 11.2    |
| bs2  |  105 | melon      | 8.2     |
| c0   |  101 | cherry     | 3.2     |
| l2   |  104 | lemon      | 6.4     |
| m1   |  106 | mango      | 15.6    |
| m2   |  105 | xbabay     | 2.6     |
| m3   |  105 | xxtt       | 11.6    |
| o2   |  103 | coconut    | 9.2     |
| t1   |  102 | banana     | 10.3    |
| t2   |  102 | grape      | 5.3     |
| t4   |  107 | xbababa    | 3.6     |
+------+------+------------+---------+
16 rows in set

　　EXISTS关键字可以和条件表达式一起使用：
　　（2）查询suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的f_price大于10.20的记录：

mysql> SELECT * FROM fruits
    -> WHERE f_price>10.20 AND EXISTS
    -> (SELECT s_name FROM suppliers WHERE s_id=107);
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1  |  102 | orange | 11.2    |
| m1   |  106 | mango  | 15.6    |
| m3   |  105 | xxtt   | 11.6    |
| t1   |  102 | banana | 10.3    |
+------+------+--------+---------+
4 rows in set

　　NOT EXISTS与EXISTS使用方法相同，返回的结果相反。

2.4 带IN关键字的子查询

　　IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。
　　例：在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id：

mysql> SELECT c_id
    -> FROM orders
    -> WHERE o_num
    -> IN
    -> (SELECT o_num FROM orderitems WHERE f_id='c0');
+-------+
| c_id  |
+-------+
| 10004 |
| 10001 |
+-------+
2 rows in set

　　NOT IN关键字与IN正好相反

2.5 带比较运算符的子查询

　　子查询还可以使用其他的比较运算符，如”<”,”<=”,”=”,”>=”,”!=”等
　　例，在suppliers表中查询s_city等于’Tianjin’的供应商s_id，然后再fruits表中查询所有该供应商提供的水果的种类：

mysql> SELECT s_id,f_name FROM fruits
    -> WHERE s_id =
    -> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city='Tianjin');
+------+------------+
| s_id | f_name     |
+------+------------+
|  101 | apple      |
|  101 | blackberry |
|  101 | cherry     |
+------+------------+
3 rows in set

3 合并查询结果

　　使用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
　　UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。
　　例：查询所有价格小于9的水果信息，查询s_id等于101和103所有水果的信息，
　　（1）使用UNION连接查询结果：

mysql> SELECT s_id,f_name,f_price
    -> FROM fruits
    -> WHERE f_price<9.0
    -> UNION
    -> SELECT s_id,f_name,f_price
    -> FROM fruits
    -> WHERE s_id IN(101,103);
+------+------------+---------+
| s_id | f_name     | f_price |
+------+------------+---------+
|  101 | apple      | 5.2     |
|  103 | apricot    | 2.2     |
|  104 | berry      | 7.6     |
|  107 | xxxx       | 3.6     |
|  105 | melon      | 8.2     |
|  101 | cherry     | 3.2     |
|  104 | lemon      | 6.4     |
|  105 | xbabay     | 2.6     |
|  102 | grape      | 5.3     |
|  107 | xbababa    | 3.6     |
|  101 | blackberry | 10.2    |
|  103 | coconut    | 9.2     |
+------+------------+---------+
12 rows in set

　　（2）使用UNION ALL连接查询结果：

mysql> SELECT s_id,f_name,f_price
    -> FROM fruits
    -> WHERE f_price<9.0
    -> UNION ALL
    -> SELECT s_id,f_name,f_price
    -> FROM fruits
    -> WHERE s_id IN(101,103);
+------+------------+---------+
| s_id | f_name     | f_price |
+------+------------+---------+
|  101 | apple      | 5.2     |
|  103 | apricot    | 2.2     |
|  104 | berry      | 7.6     |
|  107 | xxxx       | 3.6     |
|  105 | melon      | 8.2     |
|  101 | cherry     | 3.2     |
|  104 | lemon      | 6.4     |
|  105 | xbabay     | 2.6     |
|  102 | grape      | 5.3     |
|  107 | xbababa    | 3.6     |
|  101 | apple      | 5.2     |
|  103 | apricot    | 2.2     |
|  101 | blackberry | 10.2    |
|  101 | cherry     | 3.2     |
|  103 | coconut    | 9.2     |
+------+------------+---------+
15 rows in set

4 为表和字段取别名

　　（1）为表取别名

表名 [AS] 表别名

　　（2）为字段取别名

列名 [AS] 列别名

5 说明

阅读《MySQL5.5 从零开始学》笔记记录。