|
漫谈oracle中的空值(zt)
漫谈oracle中的空值
河北省统计局贾书民
在数据库中，空值用来表示实际值未知或无意义的情况。在一个表中，如果一行中的某列没有值，那么就称它为空值（NULL）。任何数据类型的列,只要没有使用非空（NOTNULL）或主键（PRIMARYKEY）完整性限制，都可以出现空值。在实际应用中，如果忽略空值的存在，将会造成造成不必要的麻烦。
例如，在下面的雇员表（EMP）中，雇员名（ENAME）为KING的行，因为KING为最高官员（PRESIDENT），他没有主管（MGR），所以其MGR为空值。因为不是所有的雇员都有手续费（COMM），所以列COMM允许有空值，除300、500、1400、0以外的其它各行COMM均为空值。
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
-------------------------------------------------------------------------
7369SMITHCLERK790217-DEC-8080020
7499ALLENSALESMAN769820-FEB-81160030030
7521WARDSALESMAN769822-FEB-81125050030
7566JONESMANAGER783902-APR-81297520
7654MARTINSALESMAN769828-SEP-811250140030
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7788SCOTTANALYST756609-DEC-82300020
7839KINGPRESIDENT17-NOV-81500010
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778812-JAN-83110020
7900JAMESCLERK769803-DEC-8195030
7902FORDANALYST756603-DEC-81300020
7934MILLERCLERK778223-JAN-82130010
本文将以上述EMP表为例，具体讨论一下空值在日常应用中所具有的一些特性。
一、空值的生成及特点
1.空值的生成
如果一列没有非空（NOTNULL）完整性限制，那么其缺省的值为空值，即如果插入一行时未指定该列的值，则其值为空值。
使用SQL语句INSERT插入行，凡未涉及到的列，其值为空值；涉及到的列，如果其值确实为空值，插入时可以用NULL来表示（对于字符型的列，也可以用''来表示）。
例：插入一行，其EMPNO为1、ENAME为'JIA'、SAL为10000、job和comm为空值。
SQL>insertintoemp(empno,ename,job,sal,comm)values(1,'JIA',NULL,1000,NULL);
SQL>select*fromempwhereempno=1;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
-------------------------------------------------------------------------
1JIA1000
可以看到新插入的一行，除job和comm为空值外，mgr、hiredate、deptno三列由于插入时未涉及，也为空值。
使用SQL语句UPDATE来修改数据，空值可用NULL来表示（对于字符型的列，也可以用''来表示）。例:
SQL>updateempsetename=NULL,sal=NULLwhereempno=1;
2.空值的特点
空值具有以下特点：
*等价于没有任何值。
*与0、空字符串或空格不同。
*在where条件中,Oracle认为结果为NULL的条件为FALSE，带有这样条件的select语句不返回行，并且不返回错误信息。但NULL和FALSE是不同的。
*排序时比其他数据都大。
*空值不能被索引。
二、空值的测试
因为空值表示缺少数据，所以空值和其它值没有可比性，即不能用等于、不等于、大于或小于和其它数值比较，当然也包括空值本身（但是在decode中例外，两个空值被认为是等价）。测试空值只能用比较操作符ISNULL和ISNOTNULL。如果使用带有其它比较操作符的条件表达式，并且其结果依赖于空值，那么其结果必定是NULL。在where条件中，Oracle认为结果为NULL的条件为FALSE，带有这样条件的select语句不返回行，也不返回错误信息。
例如查询EMP表中MGR为NULL的行：
SQL>select*fromempwheremgr='';
norowsselected
SQL>select*fromempwheremgr=null;
norowsselected
SQL>select*fromempwheremgrisnull;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
-------------------------------------------------------------------------
7839KINGPRESIDENT17-NOV-81500010
第1、2句写法不妥，WHERE条件结果为NULL，不返回行。第三句正确，返回MGR为空值的行。
三、空值和操作符
1.空值和逻辑操作符
逻辑操作符
表达式
结果
AND
NULLANDTRUE
NULL
NULLANDFALSE
FALSE
NULLANDNULL
NULL
OR
NULLORTRUE
TRUE
NULLORFALSE
NULL
NULLORNULL
NULL
NOT
NOTNULL
NULL
可以看到，在真值表中，除NULLANDFALSE结果为FALSE、NULLORTRUE结果为TRUE以外，其它结果均为NULL。
虽然在where条件中，Oracle认为结果为NULL的WHERE条件为FALSE，但在条件表达式中NULL不同于FALSE。例如在NOT（NULLANDFALSE）和NOT（NULLANDNULL）二者中仅有一处FALSE和TRUE的区别，但NOT（NULLANDFALSE）的结果为TRUE，而NOT（NULLANDNULL）的结果为NULL。
下面举例说明空值和逻辑操作符的用法：
SQL>select*fromempwherenotcomm=nullandcomm!=0;
norowsselected
SQL>select*fromempwherenot(notcomm=nullandcomm!=0);
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
-------------------------------------------------------------------------
7844TURNERSALESMAN769808-SEP-811500030
第一个Select语句，条件“notcomm=nullandcomm!=0”等价于NULLANDCOMM!=0。对于任意一行，如果COMM为不等于0的数值，条件等价于NULLANDTRUE，结果为NULL；如果COMM等于0，条件等价于NULLANDFALSE，结果为FALSE。所以，最终结果不返回行。
第二个Select语句的条件为第一个Select语句条件的“非”（NOT），对于任意一行，如果COMM为不等于0的数值，条件等价于NOTNULL，结果为NULL；如果COMM等于0，条件等价于NOTFALSE，结果为TRUE。所以，最终结果返回行COMM等于0的行。
2.空值和比较操作符
（1）IS[NOT]NULL：是用来测试空值的唯一操作符（见“空值的测试”）。
（2）=、!=、>=、<=、>、<
SQL>selectename,sal,commfromempwheresal>comm;
ENAMESALCOMM
----------------------------
ALLEN1600300
WARD1250500
TURNER15000
sal或comm为空值的行，sal>comm比较结果为NULL，所以凡是sal或comm为空值的行都没有返回。
（3）IN和NOTIN操作符
SQL>selectename,mgrfromempwheremgrin(7902,NULL);
ENAMEMGR
-------------------
SMITH7902
在上述语句中，条件“mgrin(7902,NULL)”等价于mgr=7902ormgr=NULL。对于表EMP中的任意一行，如果mgr为NULL，则上述条件等价于NULLORNULL，即为NULL；如果mgr为不等于7902的数值，则上述条件等价于FALSEORNULL，即为NULL；如果mgr等于7902，则上述条件等价于TRUEORNULL，即为TRUE。所以，最终结果能返回mgr等于7902的行。
SQL>selectdeptnofromempwheredeptnonotin('10',NULL);
norowsselected
在上述语句中，条件“deptnonotin('10',NULL)”等价于deptno!='10'anddeptno!=NULL，对于EMP表中的任意一行，条件的结果只能为NULL或FALSE，所以不返回行。
（4）any,some
SQL>selectename,salfromempwheresal>any(3000,null);
ENAMESAL
-------------------
KING5000
条件“sal>any(3000,null)”等价于sal>3000orsal>null。类似前述（3）第一句，最终结果返回所有sal>3000的行。
（5）All
SQL>selectename,salfromempwheresal>all(3000,null);
norowsselected
条件“sal>all(3000,null)”等价于sal>3000andsal>null,结果只能为NULL或FALSE，所以不返回行。
（6）（not）between
SQL>selectename,salfromempwheresalbetweennulland3000;
norowsselected
条件“salbetweennulland3000”等价于sal>=nullandsal<=3000,结果只能为NULL或FALSE，所以不返回行。
SQL>selectename,salfromempwheresalnotbetweennulland3000;
ENAMESAL
-------------------
KING5000
条件“salnotbetweennulland3000”等价于sal<nullorsal>3000,类似前述（3）的第一句，结果返回sal>3000的行。
下表为比较操作符和空值的小结：
比较操作符
表达式（例：A、B是NULL、C=10）
结果
ISNULL、ISNOTNULL
AISNULL
TRUE
AISNOTNULL
FALSE
CISNULL
FALSE
CISNOTNULL
TRUE
=、!=、>=、<=、>、<
A=NULL
NULL
A>NULL
NULL
C=NULL
NULL
C>NULL
NULL
IN(=ANY)
AIN(10,NULL)
NULL
CIN(10,NULL)
TRUE
CIN(20,NULL)
NULL
NOTIN
(等价于！=ALL)
ANOTIN(20,NULL)
NULL
CNOTIN(20,NULL)
FALSE
CNOTIN(10,NULL)
NULL
ANY，SOME
A>ANY(5,NULL)
NULL
C>ANY(5,NULL)
TRUE
C>ANY(15,NULL)
NULL
ALL
A>ALL(5,NULL)
NULL
C>ALL(5,NULL)
NULL
C>ALL(15,NULL)
FALSE
(NOT)BETWEEN
ABETWEEN5ANDNULL
NULL
CBETWEEN5ANDNULL
NULL
CBETWEEN15ANDNULL
FALSE
ANOTBETWEEN5ANDNULL
NULL
CNOTBETWEEN5ANDNULL
NULL
CNOTBETWEEN15ANDNULL
TRUE
3、空值和算术、字符操作符
（1）算术操作符：空值不等价于0，任何含有空值的算术表达式其运算结果都为空值，例如空值加10为空值。
（2）字符操作符||：因为ORACLE目前处理零个字符值的方法与处理空值的方法相同（日后的版本中不一定仍然如此），所以对于||，空值等价于零个字符值。例：
SQL>selectename,mgr,ename||mgr,sal,comm,sal+commfromemp;
ENAMEMGRENAME||MGRSALCOMMSAL+COMM
-----------------------------------------------------------
SMITH7902SMITH7902800
ALLEN7698ALLEN769816003001900
WARD7698WARD769812505001750
JONES7839JONES78392975
MARTIN7698MARTIN7698125014002650
BLAKE7839BLAKE78392850
CLARK7839CLARK78392450
SCOTT7566SCOTT75663000
KINGKING5000
TURNER7698TURNER7698150001500
ADAMS7788ADAMS77881100
JAMES7698JAMES7698950
FORD7566FORD75663000
MILLER7782MILLER77821300
我们可以看到，凡mgr为空值的，ename||mgr结果等于ename；凡是comm为空值的行，sal+comm均为空值。
四、空值和函数
1．空值和度量函数
对于度量函数，如果给定的参数为空值，则其（NVL、TRANSLATE除外）返回值为空值。如下例中的ABS（COMM），如果COMM为空值，ABS(COMM)为空值。
SQL>selectename,sal,comm,abs(comm)fromempwheresal<1500;
ENAMESALCOMMABS(COMM)
-------------------------------------
SMITH800
WARD1250500500
MARTIN125014001400
ADAMS1100
JAMES950
MILLER1300
2.空值和组函数
组函数忽略空值。在实际应用中，根据需要可利用nvl函数用零代替空值。例：
SQL>selectcount(comm),sum(comm),avg(comm)fromemp;
COUNT(COMM)SUM(COMM)AVG(COMM)
-----------------------------
42200550
SQL>selectcount(nvl(comm,0)),sum(nvl(comm,0)),avg(nvl(comm,0))
fromemp;
COUNT(NVL(COMM,0))SUM(NVL(COMM,0))AVG(NVL(COMM,0))
--------------------------------------------------
142200157.14286
第一个SELECT语句忽略COMM为空值的行，第二个SELECT语句使用NVL函数统计了所有的COMM，所以它们统计的个数、平均值都不相同。
另外需要注意的是，在利用组函数进行数据处理时，不同的写法具有不同的不同含义，在实际应用中应灵活掌握。例如：
SQL>selectdeptno,sum(sal),sum(comm),sum(sal+comm),sum(sal)+sum(comm),sum(nvl(sal,0)+nvl(comm,0))
fromemp
groupbydeptno;
DEPTNOSUM(SAL)SUM(COMM)SUM(SAL+COMM)SUM(SAL)+SUM(COMM)SUM(NVL(SAL,0)+NVL(COMM,0))
-------------------------------------------------------------------------------------
1087508750
201087510875
309400220078001160011600
可以看到SUM(SAL+COMM)、SUM(SAL)+SUM(COMM)、SUM(NVL(SAL,0)+NVL(COMM,0))的区别：SUM(SAL+COMM)为先加然后计算各行的和，如果SAL、COMM中有一个为NULL，则该行忽略不计；SUM(SAL)+SUM(COMM)为先计算各行的合计然后再加，SAL、COMM中的NULL都忽略不计，但如果SUM(SAL)、SUM(COMM)二者的结果之中有一个为NULL，则二者之和为NULL；在SUM(NVL(SAL,0)+NVL(COMM,0))里，SAL、COMM中的NULL按0处理。
五、空值的其它特性
1.空值在排序时大于任何值。例如：
SQL>selectename,commfromempwheredeptno='30'orderbycomm；
ENAMECOMM
-------------------
TURNER0
ALLEN300
WARD500
MARTIN1400
BLAKE
JAMES
2.空值不能被索引。虽然在某列上建立了索引，但是对该列的空值查询来说，因为空值没有被索引，所以不能改善查询的效率。例如下面的查询不能利用在MGR列上创建的索引。
SQL>selectenamefromempwheremgrisnull;
ENAME
----------
KING
另外正是因为空值不被索引，所以可在含有空值的列上建立唯一性索引（UNIQUEINDEX）。例如，可以在EMP表的COMM列上建立唯一性索引：
SQL>createuniqueindexemp_commonemp(comm);
Indexcreated.
_____________________________________________
木言 回复于：2003-12-11 14:05:49
好文章，要是可以排版一下就好看多了
tang_wei_jia 回复于：2004-03-05 16:35:07
ok,详尽
查看上一页
返回分类首页 返回96PC首页
查看下一页 |
