ѧϰ̰ 
OracleпֵӦ 
ӱʡͳƾ  

--------------------------------------------------------------------------------

____ݿ,ֵ(NULL)ʾʵֵδ֪һ,һеĳûֵ,ôͳΪֵκ͵,ֻҪûʹ÷ǿ(NOT NUL L)(PRIMARY KEY),ԳֵֿʵӦ,ԿֵĴ,ɺܶ鷳,ĹԱ(EMP),Ա(ENAME)ΪKING,Ϊ KINGΪ߹Ա(PRESIDENT),û(MGR),MGRΪֵ 

 

1 

____ĽEMPΪ,һ¿ֵճӦеһЩԡ 

____ֵɼص 

____1. ֵ 

____һûзǿ(NOT NULL),ôȱʡֵΪֵ,һʱδָеֵ,ֵΪֵ 

____ʹSQLINSERT,δ漰,ֵΪֵ;漰,ֵȷʵΪֵ,ʱNULLʾ(ַ͵,Ҳáʾ) 

____:һ,EMPNOΪ1ENAMEΪJIASALΪ10000jobcommΪֵ: 

____SQL>insert into emp(empno,ename,job,sal,comm) values(1,JIA,NULL,1000 ,NULL); 

____ʹSQLUPDATE޸,ֵNULLʾ,: 

____SQL>update emp set ename=NULL,sal=NULL where empno=1; 

____2. ֵص 

____ֵص: 

____ ȼûκֵ; 

____  0ַոͬ; 

____ where, OracleΪΪNULLΪFALSE,select 䲻,ҲشϢ,NULLFALSEǲͬ; 

____ ʱݶ; 

____ ֵܱ 

____ֵĲ 

____Ϊֵʾȱ,Կֵֵûпɱ,õڡڡڻСںֵȽ,ȻҲֵ(decode,ֵΪǵȼ)ԿֵֻñȽϲIS NULL IS NOT NULLʹôȽϲʽ,ڿֵ,ôضNULLwhere,Or acleΪΪNULLΪFALSE,select䲻,ҲشϢѯEMPMGRΪNULL: 

 

2 

____12д,WHEREΪNULL,Сȷ,MGRΪֵС 

____ֵͲ 

____1. ֵ߼ 

 

3 

____Կ,ֵ,NULL AND FALSE ΪFALSENULL OR TRUEΪTRUE ,ΪNULL 

____Ȼwhere,OracleΪΪNULLwhereΪFALSE,ʽ NULLͬFALSENOT( NULL AND FALSE )NOT ( NULL AND NULL ),ǰ߽Ϊ TRUE,߽ΪNULL 

____˵ֵ߼÷: 

 

4 

____һSelect,"not comm=null and comm!=0"ȼNULL AND COMM!=0һ,COMMΪ0ֵ,ȼNULL AND TRUE,ΪNULL; COMM0,ȼNULL AND FALSE,ΪFALSE,սС 

____ڶSelectΪһSelect""(NOT),һ, COMMΪ0ֵ,ȼNOT NULL,ΪNULL;COMM0,ȼNO T FALSE,ΪTRUE,սCOMM0С 

____2. ֵͱȽϲ 

____(1) IS [NOT] NULL:ԿֵΨһ 

____(2) =!=>=<=><SQL>select ename,sal,comm from emp where sal>co mm; 

____ENAME SAL COMM 

____--------------------------------------------- 

____ALLEN 1600300 

____WARD1250500 

____TURNER15000 

____salcommΪֵ,sal>commȽϽΪNULL,ԷsalcommΪֵжûзء 

____(3) INNOT IN SQL>select ename,mgr from emp where mgr in (7902,NU LL); 

____ENAME MGR 

____SMITH 7902 

____,"mgr in (7902,NULL)"ȼmgr=7902 or mgr=NULLڱE MPеһ,mgrΪNULL,ȼNULL OR NULL,ΪNULL;mgrΪ7902ֵ,ȼFALSE OR NULL,ΪNULL;mgr7902,ȼTRUE OR NULL,ΪTRUE,սܷmgr7902С 

____(4) any,some SQL>select ename,sal from emp where sal> any(3000,null); 

____ENAME SAL 

____KING5000 

____"sal> any(3000,null)"ȼsal>3000 or sal>null,ǰ(3),սsal>3000С 

____(5) AllSQL>select ename,sal from emp where sal> all(3000,null); 

____no rows selected 

____"sal> all(3000,null)"ȼsal>3000 and sal>null, ֻΪNULLFA LSE,ԲС 

____(6) (not)between SQL>select ename,sal from emp where sal between null and 3000; 

____no rows selected 

____"sal between null and 3000"ȼsal>=null and sal<=3000, ֻΪ NULLFALSE,ԲС 

____±ΪȽϲͿֵС: 

 

5 

____3. ֵַ 

____(1) :ֵȼ0,κκпֵʽΪֵ, ֵ10Ϊֵ 

____(2) ַ||:ΪOracleĿǰֵַķ봦ֵķͬ( Ժİ汾вһȻ),Զ||,ֵȼֵַ 

____ֵͺ 

____1. ֵͶ 

____ڶ,ĲΪֵ,(NVLTRANSLATE)ֵΪֵ籾еABS(COMM),COMMΪֵ,ABS(COMM)Ϊֵ 

____2. ֵ麯 

____麯ԿֵʵӦ,Ҫnvlֵ: 

 

6 

____һSELECTCOMMΪֵ,ڶSELECTʹNVLͳеCOMM,ͳƵĸƽֵͬ 

____Ҫע,麯ݴʱ,ͬдвͬĺ,ʵӦӦա: 

 

7 

____ԿSUM(SAL+COMM)SUM(SAL)+SUM(COMM) SUM(NVL(SAL,0)+NVL(COMM,0)):SUM(SAL+COMM)ΪȼȻеĺ,SALCOMMһΪNULL,кԲ;SUM(SAL)+SUM(COMM)ΪȼеĺϼȻټ,SALCOMMеNULLԲ, SUM(SAL)SUM(COMM)ߵĽ֮һΪNULL,֮ΪNULL;S UM(NVL(SAL,0)+NVL(COMM,0)),SALCOMMеNULL0 

____ֵ 

____1. ֵʱκֵ 

____2. ֵܱȻĳϽ,ǶԸеĿֵѯ˵,Ϊֵûб,ԲܸƲѯЧʡ 

____Ϊֵ,ԿںпֵϽΨһ(UNIQUE IND EX),EMPCOMMϽΨһ: 

____SQL>create unique index emp_comm on emp(comm); 

____Index created. 

 
