四种连接查询
- 内连接 —— join或inner join
- 外连接
2.1 左连接 —— left join 或者 left outer join
2.2 右连接 —— right join 或者 right outer join
2.3 完全外连接 —— full join 或者 full outer join - 使用
3.1 创建2个数据表并添加数据
mysql> create table person(
-> id int,
-> name varchar(20),
-> cardId int
-> );
Query OK, 0 rows affected (1.67 sec)
mysql> create table card(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into card values(1,'card1');
mysql> insert into card values(2,'card2');
mysql> insert into card values(3,'card3');
mysql> insert into card values(4,'card4');
mysql> insert into card values(5,'card5');
mysql> insert into person values(1,'zhang',1);
mysql> insert into person values(2,'zeng',3);
mysql> insert into person values(3,'li',5);
mysql> insert into person values(4,'cheng',6);
3.2 inner join练习
内联查询其实就是两张表的数据通过某个相等的字段，进行连接查询。
方法一:相当于多表查询,用的是where
mysql> select * from person,card where person.cardId=card.id;
+------+-------+--------+------+-------+
| id | name | cardId | id | name |
+------+-------+--------+------+-------+
| 1 | zhang | 1 | 1 | card1 |
| 2 | zeng | 3 | 3 | card3 |
| 3 | li | 5 | 5 | card5 |
+------+-------+--------+------+-------+
3 rows in set (0.00 sec)
方法二:（也可直接使用join来实现内连接）
mysql> select * from person inner join card on person.cardId=card.id;
+------+-------+--------+------+-------+
| id | name | cardId | id | name |
+------+-------+--------+------+-------+
| 1 | zhang | 1 | 1 | card1 |
| 2 | zeng | 3 | 3 | card3 |
| 3 | li | 5 | 5 | card5 |
+------+-------+--------+------+-------+
3 rows in set (0.00 sec)
3.3 left join(把左边表person的全部数据取出，右边表的数据如果条件字段有相等的就取出，没有就补null)
mysql> select * from person left join card on person.cardId=card.id;
+------+-------+--------+------+-------+
| id | name | cardId | id | name |
+------+-------+--------+------+-------+
| 1 | zhang | 1 | 1 | card1 |
| 2 | zeng | 3 | 3 | card3 |
| 3 | li | 5 | 5 | card5 |
| 4 | cheng | 6 | NULL | NULL |
+------+-------+--------+------+-------+
4 rows in set (0.00 sec)
3.4 right join(把右边表card的全部数据取出，左边表的数据如果条件字段有相等的就取出，没有就补null)
mysql> select * from person right join card on person.cardId=card.id;
+------+-------+--------+------+-------+
| id | name | cardId | id | name |
+------+-------+--------+------+-------+
| 1 | zhang | 1 | 1 | card1 |
| NULL | NULL | NULL | 2 | card2 |
| 2 | zeng | 3 | 3 | card3 |
| NULL | NULL | NULL | 4 | card4 |
| 3 | li | 5 | 5 | card5 |
+------+-------+--------+------+-------+
5 rows in set (0.00 sec)
3.5 full join
mysql不支持full join，实现full join可以使用left join union right join
mysql> select * from person right join card on person.cardId=card.id
-> union
-> select * from person left join card on person.cardId=card.id;
+------+-------+--------+------+-------+
| id | name | cardId | id | name |
+------+-------+--------+------+-------+
| 1 | zhang | 1 | 1 | card1 |
| NULL | NULL | NULL | 2 | card2 |
| 2 | zeng | 3 | 3 | card3 |
| NULL | NULL | NULL | 4 | card4 |
| 3 | li | 5 | 5 | card5 |
| 4 | cheng | 6 | NULL | NULL |
+------+-------+--------+------+-------+
6 rows in set (0.00 sec)
-
总结
四种连接对比图：
image.png
