SQL Cookbook 笔记（Oracle）（1）条件选择 case when（2）随机排序 order by dbms_random.value()（3）空值替换 coalesce、nvl（6）null值排序处理    select * from emp t order by t.comm desc 【nulls last | nulls first】（7）条件排序    select * from emp t order by t.job, case when t.job = 'SALESMAN' then comm else sal end desc, t.empno    （8）集合操作    intersect、minus    （9）标量子查询：放置在 select列表中的子查询（10）in、update 均可多行（11）update可使用内联视图//=============使用字符串===============================（1）cast 类型转换    select cast(e.sal as char(9)) sal from emp e;    （2）lower、upper 大小写    select lower('AbcD') from dual; --abcd    select upper('AbcD') from dual; --ABCD    （4）translate 转码    select translate('abc', 'ab', '12') from dual; -- '12c'    （5）replace 串替换（6）rpad，lpad 字符截取、用特定字符填充    select rpad('abc', 2, 'X') from dual; --ab    select rpad('abc', 4, 'X') from dual; --abcX    select lpad('abc', 2, 'X') from dual; --ab    select lpad('abc', 4, 'X') from dual; --Xabc（7）substr 截子串（8）递归查询，汇总    select     deptno,    ltrim(sys_connect_by_path(ename, ','), ',') emps    from (        select           deptno,          ename,          row_number() over (partition by deptno order by ename) rn,          count(*) over (partition by deptno) cnt        from emp        where deptno is not null    )    where level = cnt    start with rn = 1 connect by prior deptno = deptno and prior rn = rn - 1    --output    DEPTNO    EMPS    10        CLARK,KING,MILLER    20        ADAMS,FORD,JONES,SCOTT,SMITH    30        ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD    （9）--列出每个部门最高/最低工资    --1    select        deptno,       min_sal,       max_sal    from (        select           deptno,          ename,          sal,          row_number() over (partition by deptno order by sal) rn,          count(*) over (partition by deptno) cnt,          min(sal) over (partition by deptno) min_sal,          max(sal) over (partition by deptno) max_sal        from emp    )    where rn = cnt    --2    select        deptno,       min_sal,       max_sal    from (        select           deptno,          min(sal) over (partition by deptno) min_sal,          max(sal) over (partition by deptno) max_sal,          row_number() over (partition by deptno order by empno) rn        from emp    )    where rn = 1