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代替。
总结
到此这篇关于mysql中表的几种连接方式的文章就介绍到这了,更多相关mysql表的连接方式内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！