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

MySQL连接查询详解（附带实例）

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

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

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

MySQL内连接查询

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

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

CREATE TABLE fruits
(
f_id    char(10)      NOT NULL,
s_id    INT           NOT NULL,
f_name  char(255)     NOT NULL,
f_price decimal(8,2)  NOT NULL,
PRIMARY KEY(f_id)
);

插入如下数据：

mysql> INSERT INTO fruits (f_id, s_id, f_name, f_price)
       VALUES('a1', 101,'apple',5.2),
       ('b1',101,'blackberry', 10.2),
       ('bs1',102,'orange', 11.2),
       ('bs2',105,'melon',8.2),
       ('t1',102,'banana', 10.3),
       ('t2',102,'grape', 5.3),
       ('o2',103,'coconut', 9.2),
       ('c0',101,'cherry', 3.2),
       ('a2',103, 'apricot',2.2),
       ('l2',104,'lemon', 6.4),
       ('b2',104,'berry', 7.6),
       ('m1',106,'mango', 15.7),
       ('m2',105,'xbabay', 2.6),
       ('t4',107,'xbababa', 3.6),
       ('m3',105,'xxtt', 11.6),
       ('b5',107,'xxxx', 3.6);

再创建数据表 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');

【实例 1】在表 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 语句后面指定的列分别属于两张不同的表，f_name 和 f_price 字段在表 fruits 中，而 s_name 字段在表 suppliers 中，s_id 字段在表 fruits 和表 suppliers 中都存在；同时 FROM 子句列出了表 fruits 和 suppliers。WHERE 子句在这里作为过滤条件，表明只有两张表中的 s_id 字段值相等的时候，才符合连接查询的条件。

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

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

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

【实例 2】在表 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 后面指定的条件相同。

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

【实例 3】查询 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 第一次出现的别名为 f1，第二次出现的别名为 f2，在使用 SELECT 语句返回列时，明确指出返回以 f1 为前缀的列的全名；WHERE 连接两张表，并按照第二张表的 f_id 对数据进行过滤，返回所需数据。

MySQL外连接查询

外连接查询将查询多张表中相关联的行。内连接查询，返回的查询结果集合中仅是符合查询条件和连接条件的行。有时候需要包含没有关联的行中的数据，即在返回的查询结果集合中，不仅包含符合连接条件的行，还包含左表（左外连接或左连接）和右表（右外连接或右连接）。

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

LEFT JOIN（左连接）：返回左表中的所有记录和右表中与左表中的行匹配的记录；
RIGHT JOIN（右连接）：返回右表中的所有记录和左表中与右表中的行匹配的记录。

1) LEFT JOIN（左连接）

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

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

CREATE TABLE customers
(
c_id          int        NOT NULL AUTO_INCREMENT,
c_name        char(50)   NOT NULL,
c_address     char(50)   NULL,
c_city        char(50)   NULL,
c_zip         char(10)   NULL,
c_contact     char(50)   NULL,
c_email       char(255)  NULL,
PRIMARY KEY (c_id)
);

插入数据，SQL 语句如下：

INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip,  c_contact, c_email)
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin',  '300000',  'LiMing',
'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000',  'Zhangbo',
'Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao',  '266000', 'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou',  '570000',  'YangShan',
'sam@hotmail.com');

再创建表 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);

【实例 4】在表 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 | 30005 |
| 10001 | 30001 |
| 10002 |  NULL |
| 10003 | 30002 |
| 10004 | 30003 |
+-------+-------+

结果显示了 5 条记录，c_id 等于 10002 的客户目前并没有下订单，所以对应的表 orders 中并没有该客户的订单信息，因此该条记录只取出了表 customers 中相应的值，而从表 orders 中取出的值为 NULL。

2) RIGHT JOIN（右连接）

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

【实例 5】在表 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复合条件连接查询

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

【实例 6】在表 customers 和表 orders 中，使用 INNER JOIN 语法查询表 customers 中 c_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 |
+-------+-------+

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

继续使用连接查询，并对查询的结果进行排序。

【实例 7】在表 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   |
+--------+---------------+------------+--------+