关于mysql:mysql的连接查询

1.图示各种连贯：

INNER JOIN(JOIN):

LEFT JOIN:

RIGHT JOIN:

2.应用案例

创立a\_table和b\_table，并插入测试数据

CREATE TABLE `a_table` (  `a_id` int(11) DEFAULT NULL,  `a_name` varchar(10) DEFAULT NULL,  `a_part` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into a_table values(1,'老潘','总裁部');insert into a_table values(2,'老王','秘书部');insert into a_table values(3,'老张','设计部');insert into a_table values(4,'老李','运行部');CREATE TABLE `b_table` (  `b_id` int(11) DEFAULT NULL,  `b_name` varchar(10) DEFAULT NULL,  `b_part` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into b_table values(2,'老王','秘书部');insert into b_table values(3,'老张','设计部');insert into b_table values(5,'老刘','人事部');insert into b_table values(6,'老黄','生产部');insert into b_table values(7,'老张','测试部');

各种连贯测试的sql脚本：

-- left joinleft join select a.a_name,a.a_part,b.b_name,b.b_part from a_table a left join b_table b on a.a_id = b.b_id;-- 主表与左连贯表中若有1:n的连贯，选取左连贯表中最大的id进行左连贯查问select a.a_name,a.a_part,b.b_name,b.b_partfrom a_table aleft join(    select * from b_table t1     join (select max(b_id) as id from b_table group by b_name ) t2 on t1.b_id = t2.id)b on a.a_name = b.b_name;-- inner join select a.a_name,a.a_part,b.b_name,b.b_partfrom a_table ainner join b_table b on a.a_id = b.b_id;-- right joinselect a.a_name,a.a_part,b.b_name,b.b_partfrom a_table aright join b_table b on a.a_id = b.b_id;

以上参考自：https://blog.csdn.net/plg17/article/details/78758593
https://blog.csdn.net/J\_\_Max/article/details/87453024