sqlcookbook小结C1-C2

concat

连接列值

select  concat (ename, 'WORKS AS A',job)as msg
    from emp
    where deptno = 10

case

使用case表达式直接在select语句中执行条件逻辑

select ename,sal,
    case when sal <=2000 then 'UNDERPAID'
        when sal >=4000 then 'OVERPAID'
        else 'OK'
    end as status
from emp

rand

随机返回n条数据

select ename,job 
    from emp
order by rand() limit 5

coalesce

使用coalesce函数用实际的值来代替空值

select coalesce(comm,0)#comm非空返回comm值否则返回0
    from emp

case实现

select case 
    when comm is null then 0
    else comm
    end
from emp

like %

模式匹配,通配符%

select ename,job
    from emp
    where deptno in (10,20)
        and (ename like '%I% or job like '%ER')

substr

按照职位字段的最后两个字符排序

select ename,job 
    from emp
        order by substr(job,length(job)-2)

order by 3

select ename,sal,comm 
    from emp
        order by 3  #第三列

union all 、union

select deptno
    from emp
union 
select deptno
    from dept

同

select distinct deptno
    from (
    select deptno
        from emp
    union all 
    select detno
        from dept
    )