多表联查的几种方式
有如下两张表
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 | 杨枭 | +------+-----------+
