MySQL内连接查询-优快云博客

本文链接：https://blog.youkuaiyun.com/brucexia/article/details/145054719

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

本文重点介绍MySQL的内连接查询。

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

为了演示的需要，首先创建数据表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');

【例5.46】在表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中，而s_name字段在表suppliers中，s_id字段在表fruits和表suppliers中都存在；同时FROM子句列出了表fruits和suppliers。WHERE子句在这里作为过滤条件，表明只有两张表中的s_id字段值相等的时候，才符合连接查询的条件。从返回的结果可以看到，显示的记录是由两张表中的不同列值组成的新记录。

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

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

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

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

【例5.48】查询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对数据进行过滤，返回所需数据。