站长资源数据库
MySQL中表的几种连接方式
简介MySQL表中的连接方式其实非常简单，这里就简单的罗列出他们的特点。表的连接（JOIN）可以分为内连接（JOIN/INNER JOIN）和外连接（LEFT JOIN/RIGHT JOIN）。首先我们看一下我们本次演示的两个表：mysql> SELECT * FROM student;+----
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) 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)
上面分别展示了外连接的两种情况：左连接和右连接。这两种几乎是一样的，唯一的区别就是左连接的主表是左边的表，右连接的主表是右边的表。而外连接与内连接不同的地方就是它会将主表的所有行都予以显示，而在主表中有，其他表中没有的数据用NULL代替。
总结