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

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

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

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

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

为了演示的需要，首先创建数据表 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 对数据进行过滤，返回所需数据。

相关文章