MYSQL的连接查询

#转载请联系

• 连接能干什么？

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集进行汇总显示。

• MYSQL的三种连接

1.内连接查询，查询的结果为两个表匹配到的数据

2.右（外）连接查询，查询的结果为两个表匹配到的数据和右表特有的数据，对于左表中不存在的数据使用null填充。

3.左(外)连接查询：查询的结果为两个表匹配到的数据和左表特有的数据，对于右表中不存在的数据使用null填充。

• 连接基本语法

select * from 表1 inner或left或right join 表2 on 表1.列 运算符 表2.列

应用场景：

1.学生表有的学生来自1，2，3班，但是班级表只有1，2班。如果使用内连接把这两个表拼在一起，就要舍弃学生表的一些学生。

mysql> select * from students inner join classes on students.cls_id=classes.id;
+----+-----------+------+--------+--------+--------+-----------+----+------+
| id | name      | age  | height | gender | cls_id | is_delete | id | name |
+----+-----------+------+--------+--------+--------+-----------+----+------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |  1 | 1班  |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 | 
         |  2 | 2班  |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |  1 | 1班  |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 | 
         |  2 | 2班  |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |  1 | 1班  |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 | 
         |  2 | 2班  |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 | 
         |  1 | 1班  |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |  1 | 1班  |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |  2 | 2班  |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |  2 | 2班  |
+----+-----------+------+--------+--------+--------+-----------+----+------+

2.学生表有的学生来自1，2，3班，但是班级表只有1，2班。如果学生表是右表，班级表是左表。使用右连接把这两个表拼在一起，班级别表没有3班，就要用null来填充信息。

mysql> select * from classes as c right join students as s on c.id=s.cls_id;
+------+------+----+-----------+------+--------+--------+--------+-----------+
| id   | name | id | name      | age  | height | gender | cls_id | is_delete |
+------+------+----+-----------+------+--------+--------+--------+-----------+
|    1 | 1班  |  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
|    1 | 1班  |  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
|    1 | 1班  |  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |
|    1 | 1班  |  7 | 王祖贤    |   18 | 172.00 | 女     |      1 | 
         |
|    1 | 1班  |  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
|    2 | 2班  |  2 | 小月月    |   18 | 180.00 | 女     |      2 | 
         |
|    2 | 2班  |  4 | 刘德华    |   59 | 175.00 | 男     |      2 | 
         |
|    2 | 2班  |  6 | 凤姐      |   28 | 150.00 | 保密   |      2 | 
         |
|    2 | 2班  |  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
|    2 | 2班  | 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
| NULL | NULL | 11 | 金星      |   33 | 162.00 | 中性   |      3 | 
         |
| NULL | NULL | 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
| NULL | NULL | 13 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
| NULL | NULL | 14 | 周杰      |   34 | 176.00 | 女     |      5 |           |
+------+------+----+-----------+------+--------+--------+--------+-----------+

3.左连接和右连接基本一样，我们需要实验可以调换下学生表和班级表的顺序即可。

mysql> select * from students as s left join classes as c on s.cls_id=c.id;

 

• 自连接

相对于两个表的连接，自连接稍微抽象一点，就是自己连接自己。

自连接的示例就不用上面的数据库了，我们用全国省份和城市的表。

关于这个表的说明:

• 因为省没有所属的省份，所以可以填写为null
• 城市所属的省份pid，填写省所对应的编号id
• 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
• 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

问题: 如果还是要查询广东省对应的所有的市的信息，咱们应该怎么做呢? areas表和自身进行连接操作 就称为自连接。

表的数据很大，有3000多条，所以只展示一小部分。

mysql> select * from areas;
+--------+-----------------------------------------------+--------+
| aid    | atitle                                        | pid    |
+--------+-----------------------------------------------+--------+
| 110000 | 北京市                                        |   NULL |
| 110100 | 北京市                                        | 110000 |
| 110101 | 东城区                                        | 110100 |
| 110102 | 西城区                                        | 110100 |
| 110103 | 朝阳区                                        | 110100 |
| 110104 | 丰台区                                        | 110100 |
| 110105 | 石景山区                                      | 110100 |
| 110106 | 海淀区                                        | 110100 |
| 110107 | 门头沟区                                      | 110100 |
| 110108 | 房山区                                        | 110100 |
| 110109 | 通州区                                        | 110100 |
| 110110 | 顺义区                                        | 110100 |
| 110111 | 昌平区                                        | 110100 |
| 110112 | 大兴区                                        | 110100 |
| 110113 | 怀柔区                                        | 110100 |
| 110114 | 平谷区                                        | 110100 |
| 110115 | 密云县                                        | 110100 |
| 110116 | 延庆县                                        | 110100 |
| 120000 | 天津市                                        |   NULL |
| 120100 | 天津市                                        | 120000 |
| 120101 | 和平区                                        | 120100 |
| 120102 | 河东区                                        | 120100 |
| 120103 | 河西区                                        | 120100 |

既然是自连接，我们就先把两个表用内连接连接在一起。

mysql> select * from areas as province inner join areas as city on province.aid=city.pid where province.atitle='广东省';
+--------+-----------+------+--------+-----------+--------+
| aid    | atitle    | pid  | aid    | atitle    | pid    |
+--------+-----------+------+--------+-----------+--------+
| 440000 | 广东省    | NULL | 440100 | 广州市    | 440000 |
| 440000 | 广东省    | NULL | 440200 | 韶关市    | 440000 |
| 440000 | 广东省    | NULL | 440300 | 深圳市    | 440000 |
| 440000 | 广东省    | NULL | 440400 | 珠海市    | 440000 |
| 440000 | 广东省    | NULL | 440500 | 汕头市    | 440000 |
| 440000 | 广东省    | NULL | 440600 | 佛山市    | 440000 |
| 440000 | 广东省    | NULL | 440700 | 江门市    | 440000 |
| 440000 | 广东省    | NULL | 440800 | 湛江市    | 440000 |
| 440000 | 广东省    | NULL | 440900 | 茂名市    | 440000 |
| 440000 | 广东省    | NULL | 441200 | 肇庆市    | 440000 |
| 440000 | 广东省    | NULL | 441300 | 惠州市    | 440000 |
| 440000 | 广东省    | NULL | 441400 | 梅州市    | 440000 |
| 440000 | 广东省    | NULL | 441500 | 汕尾市    | 440000 |
| 440000 | 广东省    | NULL | 441600 | 河源市    | 440000 |
| 440000 | 广东省    | NULL | 441700 | 阳江市    | 440000 |
| 440000 | 广东省    | NULL | 441800 | 清远市    | 440000 |
| 440000 | 广东省    | NULL | 441900 | 东莞市    | 440000 |
| 440000 | 广东省    | NULL | 442000 | 中山市    | 440000 |
| 440000 | 广东省    | NULL | 445100 | 潮州市    | 440000 |
| 440000 | 广东省    | NULL | 445200 | 揭阳市    | 440000 |
| 440000 | 广东省    | NULL | 445300 | 云浮市    | 440000 |
+--------+-----------+------+--------+-----------+--------+

# 注意右表没有广东省的，因为连接的结点是province.aid=city.pid。广东省的pid是null。不满足条件

这其实就是自连接了。需要去掉左表的表可以这样操作：

mysql> select city.* from areas as province join areas as city on province.aid=city.pid where province.atitle='广东省';
+--------+-----------+--------+
| aid    | atitle    | pid    |
+--------+-----------+--------+
| 440100 | 广州市    | 440000 |
| 440200 | 韶关市    | 440000 |
| 440300 | 深圳市    | 440000 |
| 440400 | 珠海市    | 440000 |
| 440500 | 汕头市    | 440000 |
| 440600 | 佛山市    | 440000 |
| 440700 | 江门市    | 440000 |
| 440800 | 湛江市    | 440000 |
| 440900 | 茂名市    | 440000 |
| 441200 | 肇庆市    | 440000 |
| 441300 | 惠州市    | 440000 |
| 441400 | 梅州市    | 440000 |
| 441500 | 汕尾市    | 440000 |
| 441600 | 河源市    | 440000 |
| 441700 | 阳江市    | 440000 |
| 441800 | 清远市    | 440000 |
| 441900 | 东莞市    | 440000 |
| 442000 | 中山市    | 440000 |
| 445100 | 潮州市    | 440000 |
| 445200 | 揭阳市    | 440000 |
| 445300 | 云浮市    | 440000 |
+--------+-----------+--------+