查询:
select ... from ... where ... group by ... having ...;
左连接右连接全连接
left join、 right join、 full join、 inner join 内连接、等值连接
若A有10条数据,B中有15条数据,AB中公共数据有5条
select * from A left join B on ... 结果为10条数据
select * from A right join B on ... 结果为15条数据
select * from A full join B on ... 结果为20条数据
select * from A inner join B on ... 结果为5条数据
exists 与 in
exists用检查子检查的返回值为true或false。
例:select * from A where exists ( select null ) 等价于 select * from A
select * from A where exists (select B.the_id from B where the_id=A.the_id)
select * from A where the_id in (select distinct(the_id) from B)
in只能判断一列,且exists的效率一般比in高,因为in不走索引
isnull(列名,替换字符串)
case when 列名=‘’ then '空' else '非空' end
union 与 union all
A中10条记录,B中15条记录,5条重复记录
select a,b,c from table_A
union
select a,b,c from tbale_B
union默认删除重复记录,所以结果为20条记录
select a,b,c from table_A
union all
select a,b,c from table_B
直接连接两个查询结果,共25条记录