MySQL表中的連接方式其實非常簡單，這里就簡單的羅列出他們的特點。
表的連接（JOIN）可以分為內連接（JOIN/INNER JOIN）和外連接（LEFT JOIN/RIGHT JOIN）。
首先我們看一下我們本次演示的兩個表：
mysql> SELECT * FROM student;
+------+----------+------+------+
| s_id | s_name | age | c_id |
+------+----------+------+------+
| 1 | xiaoming | 13 | 1 |
| 2 | xiaohong | 41 | 4 |
| 3 | xiaoxia | 22 | 3 |
| 4 | xiaogang | 32 | 1 |
| 5 | xiaoli | 41 | 2 |
| 6 | wangwu | 13 | 2 |
| 7 | lisi | 22 | 3 |
| 8 | zhangsan | 11 | 9 |
+------+----------+------+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM class;
+------+---------+-------+
| c_id | c_name | count |
+------+---------+-------+
| 1 | MATH | 65 |
| 2 | CHINESE | 70 |
| 3 | ENGLISH | 50 |
| 4 | HISTORY | 30 |
| 5 | BIOLOGY | 40 |
+------+---------+-------+
5 rows in set (0.00 sec)
首先，表要能連接的前提就是兩個表中有相同的可以比較的列。
1.內連接
mysql> SELECT * FROM student INNER JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
+------+----------+------+------+------+---------+-------+
7 rows in set (0.00 sec)
簡單的講，內連接就是把兩個表中符合條件的行的所有數(shù)據(jù)一起展示出來，即如果不符合條件，即在表A中找得到但是在B中沒有（或者相反）的數(shù)據(jù)不予以顯示。
2.外連接
mysql> SELECT * FROM student LEFT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
| 8 | zhangsan | 11 | 9 | NULL | NULL | NULL |
+------+----------+------+------+------+---------+-------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM student RIGHT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| NULL | NULL | NULL | NULL | 5 | BIOLOGY | 40 |
+------+----------+------+------+------+---------+-------+
8 rows in set (0.00 sec)
上面分別展示了外連接的兩種情況：左連接和右連接。這兩種幾乎是一樣的，唯一的區(qū)別就是左連接的主表是左邊的表，右連接的主表是右邊的表。而外連接與內連接不同的地方就是它會將主表的所有行都予以顯示，而在主表中有，其他表中沒有的數(shù)據(jù)用NULL代替。
總結
到此這篇關于MySQL中表的幾種連接方式的文章就介紹到這了,更多相關MySQL表的連接方式內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySQL中基本的多表連接查詢教程
- mysql delete 多表連接刪除功能
- 圖文詳解MySQL中兩表關聯(lián)的連接表如何創(chuàng)建索引
- MySQL表LEFT JOIN左連接與RIGHT JOIN右連接的實例教程
- mysql多表連接查詢實例講解
- MySQL中對表連接查詢的簡單優(yōu)化教程
- mysql三張表連接建立視圖
- 詳解MySQL數(shù)據(jù)庫--多表查詢--內連接，外連接，子查詢，相關子查詢
- MySQL的LEFT JOIN表連接的進階學習教程
- MySQL多表連接的入門實例教程
