連接查詢_01

【準備】創(chuàng)建boys girls 2張表，每個表中插入6個記錄

create table boys ( b_name varchar(20), b_id int);
 create table girls ( gb_name varchar(20), g_id int);
insert into boys values('Tom',1),('Jim',2),('Harry',3),('Snape',4),('Draco',5),('Lucius',6);
insert into girls values('Ada',1),('Bell',2),('Hermione',3),('Marrye',4),('Rose',5),('Cathy',6);

1【內(nèi)連接】---交集

1.1 2種格式，效果相同
隱式內(nèi)連接：SELECT * FROM tab1,tab2 WHRER.....;
顯式內(nèi)連接：SELECT * FROM tab1 [INNER] JOIN tab2 ON.....;
省略Inner時可以用where作為刷新條件的關(guān)鍵詞

mysql> select * from boys,girls;
+--------+------+----------+------+
| b_name | b_id | gb_name  | g_id |
+--------+------+----------+------+
| Tom    |    1 | Ada      |    1 |
| Jim    |    2 | Ada      |    1 |
| Harry  |    3 | Ada      |    1 |
| Snape  |    4 | Ada      |    1 |
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Tom    |    1 | Bell     |    2 |
| Jim    |    2 | Bell     |    2 |
| Harry  |    3 | Bell     |    2 |
| Snape  |    4 | Bell     |    2 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Tom    |    1 | Hermione |    3 |
| Jim    |    2 | Hermione |    3 |
| Harry  |    3 | Hermione |    3 |
| Snape  |    4 | Hermione |    3 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Tom    |    1 | Marrye   |    4 |
| Jim    |    2 | Marrye   |    4 |
| Harry  |    3 | Marrye   |    4 |
| Snape  |    4 | Marrye   |    4 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Tom    |    1 | Rose     |    5 |
| Jim    |    2 | Rose     |    5 |
| Harry  |    3 | Rose     |    5 |
| Snape  |    4 | Rose     |    5 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Tom    |    1 | Cathy    |    6 |
| Jim    |    2 | Cathy    |    6 |
| Harry  |    3 | Cathy    |    6 |
| Snape  |    4 | Cathy    |    6 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
+--------+------+----------+------+
36 rows in set (0.00 sec)

1.2 交換2張表的位置

mysql> select * from girls,boys;
+----------+------+--------+------+
| gb_name  | g_id | b_name | b_id |
+----------+------+--------+------+
| Ada      |    1 | Tom    |    1 |
| Bell     |    2 | Tom    |    1 |
| Hermione |    3 | Tom    |    1 |
| Marrye   |    4 | Tom    |    1 |
| Rose     |    5 | Tom    |    1 |
| Cathy    |    6 | Tom    |    1 |
| Ada      |    1 | Jim    |    2 |
| Bell     |    2 | Jim    |    2 |
| Hermione |    3 | Jim    |    2 |
| Marrye   |    4 | Jim    |    2 |
| Rose     |    5 | Jim    |    2 |
| Cathy    |    6 | Jim    |    2 |
| Ada      |    1 | Harry  |    3 |
| Bell     |    2 | Harry  |    3 |
| Hermione |    3 | Harry  |    3 |
| Marrye   |    4 | Harry  |    3 |
| Rose     |    5 | Harry  |    3 |
| Cathy    |    6 | Harry  |    3 |
| Ada      |    1 | Snape  |    4 |
| Bell     |    2 | Snape  |    4 |
| Hermione |    3 | Snape  |    4 |
| Marrye   |    4 | Snape  |    4 |
| Rose     |    5 | Snape  |    4 |
| Cathy    |    6 | Snape  |    4 |
| Ada      |    1 | Draco  |    5 |
| Bell     |    2 | Draco  |    5 |
| Hermione |    3 | Draco  |    5 |
| Marrye   |    4 | Draco  |    5 |
| Rose     |    5 | Draco  |    5 |
| Cathy    |    6 | Draco  |    5 |
| Ada      |    1 | Lucius |    6 |
| Bell     |    2 | Lucius |    6 |
| Hermione |    3 | Lucius |    6 |
| Marrye   |    4 | Lucius |    6 |
| Rose     |    5 | Lucius |    6 |
| Cathy    |    6 | Lucius |    6 |
+----------+------+--------+------+
36 rows in set (0.00 sec)

1.3 用where連接篩選條件，對笛卡爾積進行篩選。
篩選出g_id > 4

mysql> select * from girls Join boys where g_id>4;
+---------+------+--------+------+
| gb_name | g_id | b_name | b_id |
+---------+------+--------+------+
| Rose    |    5 | Tom    |    1 |
| Cathy   |    6 | Tom    |    1 |
| Rose    |    5 | Jim    |    2 |
| Cathy   |    6 | Jim    |    2 |
| Rose    |    5 | Harry  |    3 |
| Cathy   |    6 | Harry  |    3 |
| Rose    |    5 | Snape  |    4 |
| Cathy   |    6 | Snape  |    4 |
| Rose    |    5 | Draco  |    5 |
| Cathy   |    6 | Draco  |    5 |
| Rose    |    5 | Lucius |    6 |
| Cathy   |    6 | Lucius |    6 |
+---------+------+--------+------+
12 rows in set (0.00 sec)

篩選 g_id>b_id

mysql> select * from girls Join boys where g_id>b_id;
+----------+------+--------+------+
| gb_name  | g_id | b_name | b_id |
+----------+------+--------+------+
| Bell     |    2 | Tom    |    1 |
| Hermione |    3 | Tom    |    1 |
| Marrye   |    4 | Tom    |    1 |
| Rose     |    5 | Tom    |    1 |
| Cathy    |    6 | Tom    |    1 |
| Hermione |    3 | Jim    |    2 |
| Marrye   |    4 | Jim    |    2 |
| Rose     |    5 | Jim    |    2 |
| Cathy    |    6 | Jim    |    2 |
| Marrye   |    4 | Harry  |    3 |
| Rose     |    5 | Harry  |    3 |
| Cathy    |    6 | Harry  |    3 |
| Rose     |    5 | Snape  |    4 |
| Cathy    |    6 | Snape  |    4 |
| Cathy    |    6 | Draco  |    5 |
+----------+------+--------+------+
15 rows in set (0.00 sec)

篩選b_name=Harry

mysql> select * from girls Join boys where b_name='Harry';
+----------+------+--------+------+
| gb_name  | g_id | b_name | b_id |
+----------+------+--------+------+
| Ada      |    1 | Harry  |    3 |
| Bell     |    2 | Harry  |    3 |
| Hermione |    3 | Harry  |    3 |
| Marrye   |    4 | Harry  |    3 |
| Rose     |    5 | Harry  |    3 |
| Cathy    |    6 | Harry  |    3 |
+----------+------+--------+------+
6 rows in set (0.00 sec)

1.4 總結(jié)：逗號或者join連接的2張表，會將不滿足篩選條件的2張表中的內(nèi)容都刪除，感覺像是一刀切。

2 【左/右連接】-----左表/右邊+集合

2 .1 使用ON做為篩選的關(guān)鍵詞，左右連接必須和ON語句同時使用，否則會報錯

mysql> select * from girls left join boys;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

2.2 作用：
使用LEFT JOIN連接2張表：保持左邊表格的完整性，空的部分用NULL填入
RIGHT JOIN連接2張表：保持右邊邊表格的完整性，空的部分用NULL填入

mysql> select * from boys,girls where b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name   | g_id |
+--------+------+----------+------+
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
+--------+------+----------+------+
12 rows in set (0.00 sec)

mysql> select * from boys left join girls on  b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name   | g_id |
+--------+------+----------+------+
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
| Tom    |    1 | NULL     | NULL |
| Jim    |    2 | NULL     | NULL |
| Harry  |    3 | NULL     | NULL |
| Snape  |    4 | NULL     | NULL |
+--------+------+----------+------+
16 rows in set (0.00 sec)
mysql> select * from boys rigth join girls on  b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name   | g_id |
+--------+------+----------+------+
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
+--------+------+----------+------+
12 rows in set (0.00 sec)

3【UNION連接】

3.1 作用：將2個select的結(jié)果拼接起來，前提是2個select語句中要顯示的列數(shù)和類型要相同
UNION：去除重復(fù)的行進行拼接
UNION ALL：不去除重復(fù)的行進行拼接
詳情參考：http://www.itxm.net/a/shujuku/2016/1205/841.html

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容