MySQL常用开发技巧之join从句

最近在复习mysql的相关知识，发现之前并没写在博客里，今天开始会陆续将2016年3到4月的几篇基础学习笔记补上来。

inner join从句

示例语句：

select a.id,a.username,a.content,a.jid from user a inner join manager b on a.username = b.username;

left join从句

left join左外连接以a为基础；

示例语句：

select a.id,a.username,b.username,a.jid from user a left join manager b on a.username = b.username;

right join从句

right join 右外连接以b为基础；

示例语句：

select b.id,a.username,b.username,b.jid from user a right join manager b on a.username = b.username where a.username is not null;

full join 全连接

MySQL并不直接支持使用full join;

cross join 交叉连接

join避免子查询

执行sql语句:

select * from user a left join job b on a.jid = b.jid;

执行结果：

利用主键删除重复数据

查询重复数据：

select username,jid,count(*) from user group by username having count(*)>1;

查询结果：

过滤后的数据：

select *,group_concat(distinct username) as user from user group by username order by id;

过滤结果：

处理重复数据