SQL Cookbook（2010年11月03日）

SQL Cookbook

 

第一章、检索数据

 

  1.7 连接列植

 

  select ename || ' WORKS AS A ' || job as msg
 
    from emp
 
    where deptno = 10

 

   1.8 在SELECT语句中使用条件逻辑

 

   select ename, sal,

      case when sal <= 2000 then 'UNDERPAID'
  
               when sal >= 4000 then 'OVERPAID'
       
               else 'OK'

   end as status
 
from emp

 

   1.10 从表中随机返回n条记录

 

   select * from (

      select ename, job from emp order by dbms_random.value()

 

    )  where rownum <= 5

 

   1.12 将空值转换为实际值

 

   select coalesce (comm, 0) from emp

 

   或

 

   select case

      when comm is null then 0
  
      else comm
  
      end
  
   from emp

 

   但可以看到使用第一种方法更为容易、简易

 

第二章、查询结果排序

 

   2.2 按多个字段排序

 

   order by colName [asc | desc]

 

   如果在查询语句中使用GROUP BY或DISTINCT，则不能按照SELECT列表中没有的列来排序

 

   2.3 按子串排序

 

   例如，要从EMP表中返回员工名字和职位，并且按照职位字段的最后两个字符排序

 

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

 

   2.4 对字母数字混合的数据排序

 

   考虑视图

 

   create view v

      as
      select ename || ' ' || deptno as data
      from emp 

 

   通过deptno进行排序

 

   select data
   from v
   order by replace(
      data ,replace(
         translate(data, '0123456789', '##########'),
          '#', ''
         )
   )

 

   通过ename进行排序

 

   select data from v
   order by replace(
      translate(data, '0123456789', '##########'
      ), '#', ''
   )

  

   replace：字符串级别的代替

   例如：select replace('acdd', 'cd', 'ef') from dual            结果：aefd

  

   translate：字符级别的代替

   例如：select translate('acdd', 'cd', 'ef') from dual          结果：aeff

 

   replace(char, search_string, replacement_string)

 

   translate(char, from_string, to_string)

 

   实战：如何判断一个字符串是否是数字

   1、先执行转换   由于to_string不能为空，可以巧用#号来代替数字

   select translate('abc0.123', '#0123456789.', '#') from dual   得到：abc

   2、判断   nvl2(expr1, expr2, expr3)   expr1不为null，返回expr2，否则返回expr3

   select nvl2(translate('abc0.123', '#0123456789.', '#'), '字符串', '数字') from dual   得到：字符串

  

   2.5 处理排序空值

 

   emp表的comm字段存在空值，如果需要先按照是否为空对结果集排序，然后非空的按照字段排序，处理方案

 

   1、Oracle8i Database以及较早版本的用户可以使用以下方案

   select ename, sal, comm from (
           select ename, sal, comm,
              case when comm is null then 1
                      else 0
              end as is_null
            from emp) x order by is_null, comm

   2、Oracle9i Database以及之后的版本的用户可以使用以下方案

   select ename, sal, comm

      from emp

      order by comm nulls last

 

2.6 根据数据项的键排序

  

   根据某些条件逻辑来排序，emp表中，如果job是SALESMAN，则要根据COMM来排序，否则根据SAL来排序

   select ename, sal, job, comm,
      case when job='SALESMAN' then comm
              else sal
      end as status
   from emp order by status

 

第3章、操作多个表

 

   3.1 记录集的叠加

 

   将来自多个表的数据组织在一起，就像将一个结果集叠加到另一个上面一样，这些表不必有相同的关键字，但是它们对应

   的数据类型应该相同

 

   例如：显示EMP表中部门号为10的员工的名字和部门编号，以及DEPT表中部门名字和编号

 

   select ename as ename_and_dname, deptno
      from emp
      where deptno = 10
   union all
   select '----------------------', null
      from dual
   union all
   select dname, deptno
      from dept

 

   使用union all将包括重复的项目，如果要筛选掉重复项，可以使用union运算符

 

   但如果使用union，很可能会为了去除重复项而进行排序操作

 

   3.2 组合相关的行

 

   多个表有一些相同的列，或者有些列的值相同，要通过链接这些列得到结果

 

   例如：要显示部门10中所有员工的名字，以及每个员工所在部门的工作地点

 

   select e.ename, d.loc
      from emp e, dept d
      where e.deptno = d.deptno and e.deptno = 10

 

   或利用显示的JOIN子句（将链接逻辑放在from子句中）

 

   select e.ename, d.loc, e.deptno as emp_deptno, d.deptno dept_deptno
      from emp e inner join dept d
         on (e.deptno = d.deptno)
      where e.deptno = 10