1.不存在插入，存在更新

if not exists (select * from table_name where aa=xx)
insert into table_name (a,b,c) values (xx,xx,xx)
else update table_name set a = xx,b = xx where aa = xx

2.分页通用方法

select xx from xx where xx=xx order by id offset (pageNo-1)*pageSize rows fetch next pageSize rows only

3.not exists 替换not in

select tbl1.id from table1 tbl1 where not exists (select 1 from table2 tbl2 where tbl1.id = tbl2.id);

4.对每一行的值执行if else操作（条件逻辑

overPaid如下执行sql后，salary大于4000的status列为overpaid，小于2000的overPaid，在2000到4000之间的为ok

select name,salary,
       case when salary <= 2000 then 'undearPaid'
            when salary >= 4000 then 'overPaid'
            else 'OK'
       end as status
from emp

5.空值替换实际值

虽然可以用case when then来实现相同功能，但既然有函数咋不用呢，如下将空值替换为0

select coalesce(commo,0) from emp;

5.order by多个字段的优先次序是从左到右

如下所示先对departno进行排序，然后再对每个departno中的salary进行倒排

select empno,departno,salary,name,job from emp order by deptno,sal desc;