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
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