Chapter 5 : NULL 值的处理、逻辑操作和函数嵌套

  Chapter 5 : NULL 值的处理、逻辑操作和函数嵌套
  select e.ename, e.job, e.sal, e.comm
  from emp e
  where e.job in ('CLERK','SALESMAN')
  order by e.job
 
  NULL:"unavailable,unassigned,undefined,unknow,immeasurable,inapplicable"
 
  select e.ename "Name", e.sal + e.comm "Income",e.job
  from emp e
  where e.job not like 'SALES%'
  order by e.job
 
 
  select e.empno, e.ename, e.sal, e.job, e.comm
  from emp e
  where e.comm = NULL;
 
  select e.empno, e.ename, e.sal, e.job, e.comm
  from emp e
  where e.comm != NULL;
 
  没有办法证明NULL等于某个值，也没有办法证明不等于某个值
 
  select e.empno, e.ename, e.sal, e.job, e.comm
  from emp e
  where e.comm is NULL;
 
  select e.empno, e.ename, e.sal, e.job, e.comm
  from emp e
  where e.comm is not NULL;
 
  select e.ename, e.job, e.comm
  from emp e
  order by e.comm;  //Default asc 升序(上小->下大)
 
  select e.ename, e.job, e.comm
  from emp e
  order by e.comm desc;
 
  T and NULL is NULL | NULL and T is NULL | NULL and NULL is NULL
 
  F or NULL is null
  NULL or F is null
  null or T is T
  T or NULL is T
  NULL or NULL is NULL
 
  Not null is null
 
  (1)算术运算符
  (2)连接运算符
  (3)比较(关系)运算符
  (4)is null,is not null,not like,not in 运算符
  (5)between ,not between运算符
  (6)Not 逻辑运算符
  (7)And 逻辑运算符
  (8)Or 逻辑运算符
 
  select e.empno, e.ename, e.sal, e.job
  from emp e
  where e.job = 'CLERK'
  OR e.job = 'SALESMAN'
  and e.sal >= 1300
 
 
  select e.empno, e.ename, e.sal, e.job
  from emp e
  where (e.job = 'CLERK'
  OR e.job = 'SALESMAN')
  and e.sal >= 1300
 
  select e.ename "Name", e.sal + e.comm "Income",e.job
  from emp e
  where e.job not like 'SALSES%'
  order by job;
 
  select e.ename "Name", e.sal + nvl(e.comm,0) "Income",e.job, e.sal,e.comm
  from emp e
  where e.job not like 'SALSES%'
  order by job;
 
  NOTE: value + NULL = NULL
 
  nvl可以避免空值(NULL)产生错误
 
  条件分支语句：
  select e.ename "Name", e.job, e.sal "Salary",
         Decode(e.job, 'SALESMAN', e.sal * 1.15,
                       'CLERK', e.sal * 1.20,
                       'ANALYST', e.sal * 1.25,
                       e.sal * 1.40) "New Salary"
  from emp e
  order by e.job
 
  单值函数的嵌套计算从内到外
 
  select e.ename "Name", nvl(to_char(e.comm),e.ename || ' is not a Salesperson!') "Commission"
  from emp e
  order by 2
 
  select e.ename "Name", nvl(to_char(e.comm),e.ename || ' is not a Salesperson!') "Commission"
  from emp e
  order by "Commission";
 
  select e.ename "Name", nvl2(e.comm,e.sal + e.comm,e.sal) "Income", e.job
  from emp e
  where e.job not like 'SALES%'
  order by e.job;
 
  select e.ename, e.job, length(e.ename) "Name_Length", length(e.job) "Job_Length",
         NULLIF(length(e.ename),length(e.job)) "Comparision"
  from emp e;
 
 
  create table emp_null
  as select ename,sal,comm
  from emp;
 
 
  select * from emp_null;
 
 
  insert into emp_null (ename,sal,comm)
  values('QUEEN',NULL,NULL);
 
  select e.ename "Name", e.sal "Salary", e.comm "Commission",
         Coalesce(comm, e.sal * 0.1, 100) "New Commission"
  from emp_null e;
 
  select e.ename "Name", e.job, e.sal "Salary",
         case e.job when 'SALESMAN' then e.sal * 1.15
                    when 'CLERK' then e.sal * 1.15
                    when 'ANALYST' then e.sal * 1.15
                    else                 e.sal * 1.40 end "New Salary"
  from emp e
  order by e.job