首页 > 编程笔记 > MySQL笔记阅读：74

MySQL连接查询的多种方式（非常详细，附带实例）

连接是关系数据库模型的主要特点，连接查询是关系数据库中最主要的查询，主要包括内连接、外连接等，通过连接运算符可以实现多个表查询。

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

本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询。

MySQL内连接查询

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

为了演示的需要，首先创建数据表 suppliers，SQL 语句如下：

CREATE TABLE suppliers
(
s_id int NOT NULL AUTO_INCREMENT,
s_name char(50) NOT NULL,
s_city char(50) NULL,
s_zip char(10) NULL,
s_call CHAR(50) NOT NULL,
PRIMARY KEY (s_id)
) ;

插入需要演示的数据，SQL 语句如下：

INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');

【实例】在 fruits 表和 suppliers 表之间使用内连接查询。
查询之前，查看两个表的结构：

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

mysql> DESC suppliers;
+--------+----------+------+-----+--------+----------------+
| Field  | Type     | Null | Key | Default| Extra          |
+--------+----------+------+-----+--------+----------------+
| s_id   | int      | 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   |                |
+--------+----------+------+-----+--------+----------------+

由结果可以看到，fruits 表和 suppliers 表中都有相同数据类型的字段 s_id，两个表通过 s_id 字段建立联系。

接下来从 fruits 表中查询 f_name、f_price 字段，从 suppliers 表中查询 s_id、s_name，SQL 语句如下：

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.20  |
|  103 | ACME           | apricot    |    2.20  |
|  101 | FastFruit Inc. | blackberry |   10.20  |
|  104 | FNK Inc.       | berry      |    7.60  |
|  107 | DK Inc.        | xxxx       |    3.60  |
|  102 | LT Supplies    | orange     |   11.20  |
|  105 | Good Set       | melon      |    8.20  |
|  101 | FastFruit Inc. | cherry     |    3.20  |
|  104 | FNK Inc.       | lemon      |    6.40  |
|  106 | Just Eat Ours  | mango      |   15.70  |
|  105 | Good Set       | xbabay     |    2.60  |
|  105 | Good Set       | xxtt       |   11.60  |
|  103 | ACME           | coconut    |    9.20  |
|  102 | LT Supplies    | banana     |   10.30  |
|  102 | LT Supplies    | grape      |    5.30  |
|  107 | DK Inc.        | xbababa    |    3.60  |
+------+----------------+------------+----------+

在这里，SELECT 语句与前面所介绍的一个最大的差别是：SELECT 后面指定的列分别属于两个不同的表，f_name、f_price 在表 fruits 中，而另外两个字段在表 suppliers 中；同时 FROM 子句列出了两个表 fruits 和 suppliers。

WHERE 子句在这里作为过滤条件，指明只有两个表中的 s_id 字段值相等的时候才符合连接查询的条件。

从返回的结果可以看到，显示的记录是由两个表中的不同列值组成的新记录。

因为 fruits 表和 suppliers 表中有相同的字段 s_id，因此在比较的时候需要完全限定表名（格式为“表名.列名”），如果只给出 s_id，MySQL 将不知道指的是哪一个，并返回错误信息。

下面的内连接查询语句返回与前面例子完全相同的结果。

【实例】在 fruits 表和 suppliers 表之间，使用 INNER JOIN 语法进行内连接查询，SQL语句如下：

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.20 |
|  103 | ACME           | apricot    |    2.20 |
|  101 | FastFruit Inc. | blackberry |   10.20 |
|  104 | FNK Inc.       | berry      |    7.60 |
|  107 | DK Inc.        | xxxx       |    3.60 |
|  102 | LT Supplies    | orange     |   11.20 |
|  105 | Good Set       | melon      |    8.20 |
|  101 | FastFruit Inc. | cherry     |    3.20 |
|  104 | FNK Inc.       | lemon      |    6.40 |
|  106 | Just Eat Ours  | mango      |   15.70 |
|  105 | Good Set       | xbabay     |    2.60 |
|  105 | Good Set       | xxtt       |   11.60 |
|  103 | ACME           | coconut    |    9.20 |
|  102 | LT Supplies    | banana     |   10.30 |
|  102 | LT Supplies    | grape      |    5.30 |
|  107 | DK Inc.        | xbababa    |    3.60 |
+------+----------------+------------+---------+

在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定。使用这种语法的时候，连接的条件使用 ON 子句而不是 WHERE，ON 和 WHERE 后面指定的条件相同。

使用 WHERE 子句定义连接条件比较简单明了，而 INNER JOIN 语法是 ANSI SQL 的标准规范，使用 INNER JOIN 连接语法能够确保不会忘记连接条件，而且 WHERE 子句在某些时候会影响查询的性能。

如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

【实例】查询 f_id= 'a1' 的水果供应商提供的水果种类，SQL 语句如下：

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     |
+------+------------+

此处查询的两个表是相同的表，为了防止产生二义性，对表使用了别名，fruits 表第 1 次出现的别名为 f1，第 2 次出现的别名为 f2；使用 SELECT 语句返回列时，明确指出返回以 f1 为前缀的列的全名；WHERE 连接两个表，并按照第 2 个表的 f_id 对数据进行过滤，返回所需数据。

MySQL外连接查询

内连接时返回查询结果集合中仅是符合查询条件和连接条件的行，外连接查询将查询多个表中相关联的行。

有时候需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。

外连接分为左外连接（左连接）和右外连接（右连接）：

LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

1) LEFT JOIN左连接

左连接的结果包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。

首先创建表 orders，SQL 语句如下：

CREATE TABLE orders
(
o_num int NOT NULL AUTO_INCREMENT,
o_date datetime NOT NULL,
c_id int NOT NULL,
PRIMARY KEY (o_num)
) ;

插入需要演示的数据，SQL 语句如下：

INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);

【实例】在 customers 表和 orders 表中，查询所有客户，包括没有订单的客户，SQL 语句如下：

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 条记录，ID 等于 10002 的客户目前并没有下订单，所以对应的 orders 表中并没有该客户的订单信息，所以该条记录只取出了 customers 表中相应的值，而从 orders 表中取出的值为空值 NULL。

2) RIGHT JOIN右连接

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

【实例】在 customers 表和 orders 表中，查询所有订单，包括没有客户的订单，SQL 语句如下：

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 条记录，订单号等于 30004 的订单的客户可能由于某种原因取消了该订单，对应的 customers 表中并没有该客户的信息，所以该条记录只取出了orders表中相应的值，而从 customers 表中取出的值为空值 NULL。

MySQL复合条件连接查询

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

【实例】在 customers 表和 orders 表中，使用 INNER JOIN 语法查询 customers 表中 ID 为 10001 的客户的订单信息，SQL 语句如下：

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 |
+-------+-------+

结果显示，在连接查询时指定查询客户 ID 为 10001 的订单信息，添加了过滤条件之后返回的结果将会变少，因此返回结果只有两条记录。

【实例】使用连接查询，并对查询的结果进行排序。在 fruits 表和 suppliers 表之间，使用 INNER JOIN 语法进行内连接查询，并对查询结果排序，SQL 语句如下：

mysql> SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id ORDER BY fruits.s_id;
+------+----------------+------------+---------+
| s_id | s_name         | f_name     | f_price |
+------+----------------+------------+---------+
|  101 | FastFruit Inc. | apple      |   5.20  |
|  101 | FastFruit Inc. | blackberry |   10.20 |
|  101 | FastFruit Inc. | cherry     |    3.20 |
|  102 | LT Supplies    | orange     |   11.20 |
|  102 | LT Supplies    | banana     |   10.30 |
|  102 | LT Supplies    | grape      |    5.30 |
|  103 | ACME           | apricot    |    2.20 |
|  103 | ACME           | coconut    |    9.20 |
|  104 | FNK Inc.       | berry      |    7.60 |
|  104 | FNK Inc.       | lemon      |    6.40 |
|  105 | Good Set       | melon      |    8.20 |
|  105 | Good Set       | xbabay     |    2.60 |
|  105 | Good Set       | xxtt       |   11.60 |
|  106 | Just Eat Ours  | mango      |   15.70 |
|  107 | DK Inc.        | xxxx       |    3.60 |
|  107 | DK Inc.        | xbababa    |    3.60 |
+------+----------------+------------+---------+

由结果可以看到，内连接查询的结果按照 suppliers.s_id 字段进行了升序排序。