1、SQL语句结构
select distinct < select_list >
from < left_table > < join_type >
join < right_table >
on < join_condition >
where < where_condition >
group by < group_by_list >
having < having_condition >
order by < order_by_condition >
limit < limit_number >
2、7种Join方式及实例
实验脚本:
drop table IF EXISTS shuzi ;
create table shuzi (id tinyint,note varchar(20));
insert into shuzi values (1,'一'),(2,'二'),(3,'三'),(4,'四'),(5,'五'),(6,'六'),(7,'七'),(8,'八'),(9,'九'),(10,'十');
select * from shuzi;
drop table IF EXISTS qianshu ;
create table qianshu (id int,des varchar(20));
insert into qianshu values (1,'壹'),(2,'贰'),(4,'肆'),(5,'伍'),(6,'陆'),(10,'拾'),(100,'佰'),(1000,'仟'),(10000,'万');
select * from qianshu;
如果需要取的表中的字段信息为必须不为空字段,则用inner join;
如果可为空,或者可有可没有,则用left join;right join
- 左连接,左表的全部,右表不满足的列补空
select a.id,a.note,b.id,b.des from shuzi a left join qianshu b on a.id=b.id order by a.id;
- 右连接,右表的全部,左表不满足的列补空
select a.id,a.note,b.id,b.des from shuzi a right join qianshu b on a.id=b.id order by b.id;
- 内连接,只输出左右表均存在的记录(默认from a,b方式)
SELECT a.id,a.note,b.id,b.des FROM shuzi a INNER JOIN qianshu b ON a.id=b.id ORDER BY b.id;
- 左连接,只保留左表特有数据(差集)
select a.id,a.note,b.id,b.des from shuzi a left join qianshu b on a.id=b.id where b.id is null order by a.id
- 右连接,只保留右表特有数据(差集)
SELECT a.id,a.note,b.id,b.des FROM shuzi a RIGHT JOIN qianshu b ON a.id=b.id WHERE a.id IS NULL ORDER BY b.id;
- 全外连接,获取左右表的所有记录,各自没有时补空
mysql不支持full outer join,要实现全外连接可以通过合并左,右外连接结果集实现
select a.id,a.note,b.id,b.des from shuzi a left join qianshu b on a.id=b.id
union
select a.id,a.note,b.id,b.des from shuzi a right join qianshu b on a.id=b.id
- 获取两表连接交集的补集(最后一个)
SELECT * FROM (
SELECT a.id aid,a.note,b.id bid,b.des FROM shuzi a LEFT JOIN qianshu b ON a.id=b.id
UNION
SELECT a.id aid,a.note,b.id bid,b.des FROM shuzi a RIGHT JOIN qianshu b ON a.id=b.id) v_a
WHERE aid IS NULL OR bid IS NULL;
转载于:https://my.oschina.net/peakfang/blog/2875103