MySQL中表的几种连接方式中表的几种连接方式
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)
简单的讲，内连接就是把两个表中符合条件的行的所有数据一起展示出来，即如果不符合条件，即在表A中找得到但是在B中
没有（或者相反）的数据不予以显示。
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)
- 1
- 2
前往页