mysql查询语法汇总-连接查询

通过连接运算符，可以实现多个表查询。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或者多个表存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询了。

连接查询主要分为内连接查询、外连接查询、复合条件连接查询这三种。

内连接查询

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

内连接查询又分为传统内连接和自连接查询，往下看。

测试环境准备

首先我们看下现有表test中的表结构：

mysql> desc test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| age   | int          | YES  |     | NULL    |                |
| area  | varchar(255) | YES  |     | NULL    |                |
| like  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>

而后我们可以在新建一个表，这里我们命名为test2；

mysql> create table test2 (id int not null auto_increment,
    -> name varchar(255) not null,
    -> height int not null,
    -> weight int not null,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql>

再查看下test2的表结构；

mysql> desc test2;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| name   | varchar(255) | NO   |     | NULL    |                |
| height | int          | NO   |     | NULL    |                |
| weight | int          | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

而后，我们需要在test2中插入一些测试数据，如下：

mysql> insert into test2 (id, name, height, weight) values (1, 'zhangsan', 160, 70), (2, 'lisi', 170, 75), (3, 'wangwu', 180, 80), (4, 'wahaha', 190, 90);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

分别查下test和test2这两个数据表中现有的数据；

mysql> select * from test;
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  5 | lisi      |   22 | beijing  | 123  |
|  6 | wangwu    |   24 | shandong | 222  |
|  7 | NULL      |   24 | shandong | 222  |
+----+-----------+------+----------+------+
5 rows in set (0.00 sec)

mysql> select * from test2;
+----+----------+--------+--------+
| id | name     | height | weight |
+----+----------+--------+--------+
|  1 | zhangsan |    160 |     70 |
|  2 | lisi     |    170 |     75 |
|  3 | wangwu   |    180 |     80 |
|  4 | wahaha   |    190 |     90 |
+----+----------+--------+--------+
4 rows in set (0.00 sec)

mysql>

实操

从上面可以看到，test和test2中都有一个字段name，我们就通过这个字段，来做内连接查询；

例如，我们查询下两个表中名字相同的人的地区(area）、身高(height)、体重(weight)；

mysql> select test.name, area, height, weight from test, test2 where test.name = test2.name;
+----------+----------+--------+--------+
| name     | area     | height | weight |
+----------+----------+--------+--------+
| zhangsan | shanxi   |    160 |     70 |
| lisi     | beijing  |    170 |     75 |
| wangwu   | shandong |    180 |     80 |
+----------+----------+--------+--------+
3 rows in set (0.00 sec)

mysql>

由于test和test2两个数据表中都有name这个字段，所以select后面添加name字段的时候，需要申明下是哪个数据表中的字段，不然数据库会搞不秦楚。from申明查询哪两个表中的数据，where字段作为过滤条件，说明只有两个表中的name字段相等时才展示结果。

或者我们可以换一种方式，使用inner join来查询；

mysql> select test.name, area, height, weight from test inner join test2 on test.name = test2.name;
+----------+----------+--------+--------+
| name     | area     | height | weight |
+----------+----------+--------+--------+
| zhangsan | shanxi   |    160 |     70 |
| lisi     | beijing  |    170 |     75 |
| wangwu   | shandong |    180 |     80 |
+----------+----------+--------+--------+
3 rows in set (0.00 sec)

mysql>

可以看到结果是一致的。需要注意的是，如果使用inner join连接两个表的时候，连接条件就不是使用where了，而是使用on。

使用where子句定义连接条件比较简单明了，而inner join语法是ansi sql的标准规范，使用inner join连接语法能够确保不会忘记连接条件，而且where子句在某些时候会影响查询的性能。当我们内连接查询语句执行效率低的时候，我们可以尝试替换成inner join ... no这种方式。

上面咱们也提到了，还有一种自连接查询，是什么意思呢？

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

例如：我们可以查询下test数据表中，name为zhangsan的area字段是shanxi的数据；

mysql> select f1.name, f1.area from test as f1, test as f2 where f1.name = f2.name and f2.area = 'shanxi';
+-----------+--------+
| name      | area   |
+-----------+--------+
| zhangsan  | shanxi |
| zhangsan1 | shanxi |
+-----------+--------+
2 rows in set (0.00 sec)

mysql>

外连接查询

外连接查询，也是查询多个表中相关联的行，在内连接时，返回查询结果集合中仅仅是符合查询条件和连接条件的行。但是我们实际上有时候也需要返回没有关联的数据，那么外连接就派上用场了。

外连接的返回查询结果集合中不仅包含符合连接条件的行，还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个变接表中的所有数据行。

左连接（left join）：返回包括左表中的所有记录和右表中连接字段相等的记录；

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

LEFT JOIN左连接

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

我们再看下test和test2中表的现有数据；

mysql> select * from test;
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  5 | lisi      |   22 | beijing  | 123  |
|  6 | wangwu    |   24 | shandong | 222  |
|  7 | NULL      |   24 | shandong | 222  |
+----+-----------+------+----------+------+
5 rows in set (0.00 sec)

mysql> select * from test2;
+----+----------+--------+--------+
| id | name     | height | weight |
+----+----------+--------+--------+
|  1 | zhangsan |    160 |     70 |
|  2 | lisi     |    170 |     75 |
|  3 | wangwu   |    180 |     80 |
|  4 | wahaha   |    190 |     90 |
+----+----------+--------+--------+
4 rows in set (0.00 sec)

mysql>

例如：我们查询下test所有人的身高(height)信息；

mysql> select test.name, test2.height from test left outer join test2 on test.id = test2.id;
+-----------+--------+
| name      | height |
+-----------+--------+
| zhangsan  |    160 |
| zhangsan1 |    190 |
| lisi      |   NULL |
| wangwu    |   NULL |
| NULL      |   NULL |
+-----------+--------+
5 rows in set (0.00 sec)

mysql>

可以看到展示了test数据表中的所有数据，有些用户在test2中没有匹配到，那么直接展示了NULL值。

RIGHT JOIN右连接

右连接正好和左连接相反，将返回右表的所有行，如果右表的某行在左表中没有匹配到相关数据，那么左表将返回空值。

例如：我们查询下test2中所有人的地区(area)信息；

mysql> select test2.name, test.area from test right outer join test2 on test2.name = test.name;
+----------+----------+
| name     | area     |
+----------+----------+
| zhangsan | shanxi   |
| lisi     | beijing  |
| wangwu   | shandong |
| wahaha   | NULL     |
+----------+----------+
4 rows in set (0.00 sec)

mysql>

可以看到，展示了test2中的所有name信息，左表中没有匹配到的area信息就返回了空值。

复合条件连接查询

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

例如，我们查询下属于shanxi地区的人员的身高（height）；

mysql> select area, height from test inner join test2 on test.name = test2.name and test.area = 'shanxi';
+--------+--------+
| area   | height |
+--------+--------+
| shanxi |    160 |
+--------+--------+
1 row in set (0.00 sec)

mysql>

可以看到只查到了一条数据。

至此，本文结束。 

长按以识别二维码关注运维家~

微信号：yunweijia0909