mysql多表联查的几种方法_多表联查的几种方式

有如下两张表

mysql> select * from teacher;

+------+-----------+

| t_id | t_name |

+------+-----------+

| 1 | 张雪峰 |

| 2 | 老子 |

| 3 | 墨子 |

| 4 | 谢逊 |

+------+-----------+

-- ----------------------

mysql> select * from course;

+------+-----------------------+------+

| c_id | c_name | t_id |

+------+-----------------------+------+

| 1 | python全栈工程师 | 1 |

| 2 | Java全栈工程师 | 2 |

| 3 | linux工程师 | 3 |

| 4 | web | 2 |

+------+-----------------------+------+

先来介绍两种常用的

多表联查

mysql> select * from teacher t,course c where t.t_id = c.t_id;

+------+-----------+------+-----------------------+------+

| t_id | t_name | c_id | c_name | t_id |

+------+-----------+------+-----------------------+------+

| 1 | 张雪峰 | 1 | python全栈工程师 | 1 |

| 2 | 老子 | 2 | Java全栈工程师 | 2 |

| 2 | 老子 | 4 | web | 2 |

| 3 | 墨子 | 3 | linux工程师 | 3 |

+------+-----------+------+-----------------------+------+

左连接

保留左表全部数据,以右表填充左表,没有以null填充

mysql> select * from teacher t left join course c on t.t_id = c.t_id;

+------+-----------+------+-----------------------+------+

| t_id | t_name | c_id | c_name | t_id |

+------+-----------+------+-----------------------+------+

| 1 | 张雪峰 | 1 | python全栈工程师 | 1 |

| 2 | 老子 | 2 | Java全栈工程师 | 2 |

| 3 | 墨子 | 3 | linux工程师 | 3 |

| 2 | 老子 | 4 | web | 2 |

| 4 | 谢逊 | NULL | NULL | NULL |

+------+-----------+------+-----------------------+------+

mysql> select * from teacher t left join course c on t.t_id = c.t_id where c_id = '2';

+------+--------+------+---------------------+------+

| t_id | t_name | c_id | c_name | t_id |

+------+--------+------+---------------------+------+

| 2 | 老子 | 2 | Java全栈工程师 | 2 |

+------+--------+------+---------------------+------+

mysql> select * from teacher t left join course c on t.t_id = c.t_id and c_id = '2';

+------+-----------+------+---------------------+------+

| t_id | t_name | c_id | c_name | t_id |

+------+-----------+------+---------------------+------+

| 1 | 张雪峰 | NULL | NULL | NULL |

| 2 | 老子 | 2 | Java全栈工程师 | 2 |

| 3 | 墨子 | NULL | NULL | NULL |

| 4 | 谢逊 | NULL | NULL | NULL |

+------+-----------+------+---------------------+------+

后面来不常用的

右连接 -- 左连接和右连接成为外连接

保留右表全部数据,以左表填充右表,没有以null填充 -- 其实就是把左连接的两个表的位置互换

mysql> select * from teacher t right join course c on t.t_id = c.t_id;

+------+-----------+------+-----------------------+------+

| t_id | t_name | c_id | c_name | t_id |

+------+-----------+------+-----------------------+------+

| 1 | 张雪峰 | 1 | python全栈工程师 | 1 |

| 2 | 老子 | 2 | Java全栈工程师 | 2 |

| 2 | 老子 | 4 | web | 2 |

| 3 | 墨子 | 3 | linux工程师 | 3 |

+------+-----------+------+-----------------------+------+

里连接

只连接匹配的行

mysql> select * from teacher t inner join course c on t.t_id = c.t_id;

+------+-----------+------+-----------------------+------+

| t_id | t_name | c_id | c_name | t_id |

+------+-----------+------+-----------------------+------+

| 1 | 张雪峰 | 1 | python全栈工程师 | 1 |

| 2 | 老子 | 2 | Java全栈工程师 | 2 |

| 2 | 老子 | 4 | web | 2 |

| 3 | 墨子 | 3 | linux工程师 | 3 |

+------+-----------+------+-----------------------+------+

交叉连接-- 然并卵 ,相当于mysql中不存在的 cross join

产生笛卡尔乘积,两个表的数据交叉匹配--得到的结果没有用

mysql> select * from teacher,course;

+------+-----------+------+-----------------------+------+

| t_id | t_name | c_id | c_name | t_id |

+------+-----------+------+-----------------------+------+

| 1 | 张雪峰 | 1 | python全栈工程师 | 1 |

| 2 | 老子 | 1 | python全栈工程师 | 1 |

| 3 | 墨子 | 1 | python全栈工程师 | 1 |

| 4 | 谢逊 | 1 | python全栈工程师 | 1 |

| 1 | 张雪峰 | 2 | Java全栈工程师 | 2 |

| 2 | 老子 | 2 | Java全栈工程师 | 2 |

| 3 | 墨子 | 2 | Java全栈工程师 | 2 |

| 4 | 谢逊 | 2 | Java全栈工程师 | 2 |

| 1 | 张雪峰 | 3 | linux工程师 | 3 |

| 2 | 老子 | 3 | linux工程师 | 3 |

| 3 | 墨子 | 3 | linux工程师 | 3 |

| 4 | 谢逊 | 3 | linux工程师 | 3 |

| 1 | 张雪峰 | 4 | web | 2 |

| 2 | 老子 | 4 | web | 2 |

| 3 | 墨子 | 4 | web | 2 |

| 4 | 谢逊 | 4 | web | 2 |

+------+-----------+------+-----------------------+------+

全外连接-- full join

full join在mysql中并不存在,他是产生a表和b表的并集,没有匹配的则以null值取代。

在mysql中可以使用union 或union all 来实现(union all 会显示所有的数据,union去掉重复的数据)

mysql> select * from teacher1;

+------+-----------+

| t_id | t_name |

+------+-----------+

| 1 | 张雪峰 |

| 2 | 老子 |

| 3 | 墨子 |

| 4 | 谢逊 |

| 5 | 林冲 |

| 6 | 杨枭 |

+------+-----------+

union和union all 内部的 SELECT 语句必须拥有相同数量的列，每条 SELECT 语句中的列的顺序必须相同，不然会混乱。

mysql> select * from ((select * from teacher) union all (select * from teacher1))s;

+------+-----------+

| t_id | t_name |

+------+-----------+

| 1 | 张雪峰 |

| 2 | 老子 |

| 3 | 墨子 |

| 4 | 谢逊 |

| 1 | 张雪峰 |

| 2 | 老子 |

| 3 | 墨子 |

| 4 | 谢逊 |

| 5 | 林冲 |

| 6 | 杨枭 |

+------+-----------+

-- union

mysql> select * from ((select * from teacher) union (select * from teacher1))s;

+------+-----------+

| t_id | t_name |

+------+-----------+

| 1 | 张雪峰 |

| 2 | 老子 |

| 3 | 墨子 |

| 4 | 谢逊 |

| 5 | 林冲 |

| 6 | 杨枭 |

+------+-----------+