SQL经典实例

select语句

#筛选行
where column is not null  

#拼接字符
select concat(ename,' woeks as a ',job) as msg from emp  

#if条件判断
select ename,sal,
    case  when sal <=2000 then 'underpaid'
         when sal >=4000 then 'overpaid'
         else 'ok'
    end as status
from emp    

#随机选择
select ename,job from emp order by rand() limit 5

#将Null替换为实际值
select coalesce(comm,0) from emp

#查找匹配项
select ename,job from emp where deptno in (10,20)
=
select ename,job from emp where deptno > 10 and depno < 20

order语句

#对子串进行排序
select ename,job from emp order by substr(job,length(job)-2)

#非Null值COMM升序排列，全部Null值放到最后面
select ename,sal,comm from (
   select ename,sal,comm,
        case when comm is null then 0 else 1 end as is_null from emp
) x
order by is_null desc,comm

#按照条件逻辑排序
select ename,sal,job,comm from emp
order by 
   case 
     when job = 'SALESMAN' then comm 
     else sal 
   end 

#union all合并多个表中的行
select ename as ename_and_dname, deptno from emp
where deptno =10 
union all
select dname, deptno from dept

union all 会把项原封不动地追加

union 可以过滤掉重复的项

多表查询

# 
select empno,ename,job,sal,deptno from emp
where (ename,job,sal) in ( 
    select ename,job,sal from emp 
       intersect
    select ename,job,sal from emp where ename='nick'
)

#
select e.empno,e.ename, e.sal,e.deptno,
e.sal*case when eb.type = 1 then .1
        when eb.type = 2 then .2 else .3
    end as bonus
from emp e, emp_bonus eb 
where e.empno = eb.empno and e.deptno = 10