四種連接查詢(xún)
- 內(nèi)連接 —— 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 創(chuàng)建2個(gè)數(shù)據(jù)表并添加數(shù)據(jù)
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練習(xí)
內(nèi)聯(lián)查詢(xún)其實(shí)就是兩張表的數(shù)據(jù)通過(guò)某個(gè)相等的字段袜瞬，進(jìn)行連接查詢(xún)。
方法一:相當(dāng)于多表查詢(xún),用的是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來(lái)實(shí)現(xiàn)內(nèi)連接）
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的全部數(shù)據(jù)取出，右邊表的數(shù)據(jù)如果條件字段有相等的就取出，沒(méi)有就補(bǔ)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的全部數(shù)據(jù)取出，左邊表的數(shù)據(jù)如果條件字段有相等的就取出霞势，沒(méi)有就補(bǔ)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，實(shí)現(xiàn)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)
-
總結(jié)
四種連接對(duì)比圖：
image.png
