MYSQL教程之漫 谈oracle 中 的 空 值
任何规模的组织都可能受益于外包服务，并在一个标准化和优化的平台上统一其数据库管理任务。基于其本身的特性，DBaaS提供了敏捷和高效的数据库服务，它可以支持多变的需求。oracle在数据库中，空值用来表示实际值未知或无意义的情况。在一个表中，如果一行中的某列没有值，那么就称它为空值（NULL）。任何数据类型的列,只要没有使用非空（NOTNULL）或主键（PRIMARYKEY）完整性限制，都可以出现空值。在实际应用中，如果忽略空值的存在，将会造成造成不必要的麻烦。----例如，在下面的雇员表（EMP）中，雇员名（ENAME）为KING的行，因为KING为最高官员（PRESIDENT），他没有主管（MGR），所以其MGR为空值。因为不是所有的雇员都有手续费（COMM），所以列COMM允许有空值，除300、500、1400、0以外的其它各行COMM均为空值。
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------7369SMITHCLERK790217-DEC-80800207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207654MARTINSALESMAN769828-SEP-8112501400307698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756609-DEC-823000207839KINGPRESIDENT17-NOV-815000107844TURNERSALESMAN769808-SEP-8115000307876ADAMSCLERK778812-JAN-831100207900JAMESCLERK769803-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-82130010
----本文将以上述EMP表为例，具体讨论一下空值在日常应用中所具有的一些特性。
----1、空值的生成及特点
----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是不同的。
----*排序时比其他数据都年夜。
----*空值不能被索引。
----2、空值的测试
----因为空值表示缺少数据，所以空值和其它值没有可比性，即不能用等于、不等于、年夜于或小于和其它数值比较，当然也包括空值本身（但是在decode中例外，两个空值被认为是等价）。测试空值只能用比较操作符ISNULL和ISNOTNULL。如果使用带有其它比较操作符的条件表达式，并且其结果依赖于空值，那么其结果必定是NULL。在where条件中，Oracle认为结果为NULL的条件为FALSE，带有这样条件的select语句不返回行，也不返回错误信息。
----例如查询EMP表中MGR为NULL的行：
SQL>select*fromempwheremgr=;norowsselectedSQL>select*fromempwheremgr=null;norowsselectedSQL>select*fromempwheremgrisnull;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------7839KINGPRESIDENT17-NOV-81500010
----第1、2句写法不妥，WHERE条件结果为NULL，不返回行。第三句正确，返回MGR为空值的行。
----3、空值和操作符
----1.空值和逻辑操作符
----逻辑操作符
----表达式
----结果
ANDNULLANDTRUENULLNULLANDFALSEFALSENULLANDNULLNULLORNULLORTRUETRUENULLORFALSENULLNULLORNULLNULLNOTNOTNULLNULL
----可以看到，在真值表中，除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;norowsselectedSQL>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）ISNULL：是用来测试空值的唯一操作符（见"空值的测试"）。
（2）=、!=、>=、<=、>、<SQL>selectename,sal,commfromempwheresal>comm;ENAMESALCOMM----------------------------ALLEN1600300WARD1250500TURNER15000
----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!=10anddeptno!=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"等价于sal3000,类似前述（3）的第一句，结果返回sal>3000的行。
----下表为比较操作符和空值的小结：
----比较操作符
----表达式（例：A、B是NULL、C=10）
----结果
ISNULL、ISNOTNULLAISNULLTRUEAISNOTNULLFALSECISNULLFALSECISNOTNULLTRUE=、!=、>=、<=、>、<A=NULLNULLA>NULLNULLC=NULLNULLC>NULLNULLIN(=ANY)AIN(10,NULL)NULLCIN(10,NULL)TRUECIN(20,NULL)NULLNOTIN(等价于！=ALL)ANOTIN(20,NULL)NULLCNOTIN(20,NULL)FALSECNOTIN(10,NULL)NULLANY，SOMEA>ANY(5,NULL)NULLC>ANY(5,NULL)TRUEC>ANY(15,NULL)NULLALLA>ALL(5,NULL)NULLC>ALL(5,NULL)NULLC>ALL(15,NULL)FALSE(NOT)BETWEENABETWEEN5ANDNULLNULLCBETWEEN5ANDNULLNULLCBETWEEN15ANDNULLFALSEANOTBETWEEN5ANDNULLNULLCNOTBETWEEN5ANDNULLNULLCNOTBETWEEN15ANDNULLTRUE
----3、空值和算术、字符操作符
----（1）算术操作符：空值不等价于0，任何含有空值的算术表达式其运算结果都为空值，例如空值加10为空值。
----（2）字符操作符||：因为ORACLE目前处理零个字符值的方法与处理空值的方法相同（日后的版本中不一定仍然如此），所以对于||，空值等价于零个字符值。例：
SQL>selectename,mgr,ename||mgr,sal,comm,sal+commfromemp;ENAMEMGRENAME||MGRSALCOMMSAL+COMM-----------------------------------------------------------SMITH7902SMITH7902800ALLEN7698ALLEN769816003001900WARD7698WARD769812505001750JONES7839JONES78392975MARTIN7698MARTIN7698125014002650BLAKE7839BLAKE78392850CLARK7839CLARK78392450SCOTT7566SCOTT75663000KINGKING5000TURNER7698TURNER7698150001500ADAMS7788ADAMS77881100JAMES7698JAMES7698950FORD7566FORD75663000MILLER7782MILLER77821300
----我们可以看到，凡mgr为空值的，ename||mgr结果等于ename；凡是comm为空值的行，sal+comm均为空值。
----4、空值和函数
----1．空值和度量函数
----对于度量函数，如果给定的参数为空值，则其（NVL、TRANSLATE除外）返回值为空值。如下例中的ABS（COMM），如果COMM为空值，ABS(COMM)为空值。
SQL>selectename,sal,comm,abs(comm)fromempwheresal<1500;ENAMESALCOMMABS(COMM)-------------------------------------SMITH800WARD1250500500MARTIN125014001400ADAMS1100JAMES950MILLER1300
----2.空值和组函数
----组函数忽略空值。在实际应用中，根据需要可利用nvl函数用零代替空值。例：
SQL>selectcount(comm),sum(comm),avg(comm)fromemp;COUNT(COMM)SUM(COMM)AVG(COMM)-----------------------------42200550SQL>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))fromempgroupbydeptno;DEPTNOSUM(SAL)SUM(COMM)SUM(SAL+COMM)SUM(SAL)+SUM(COMM)SUM(NVL(SAL,0)+NVL(COMM,0))-----------------------------------------------1087508750201087510875309400220078001160011600
----可以看到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处理。
----5、空值的其它特性
----1.空值在排序时年夜于任何值。例如：
SQL>selectename,commfromempwheredeptno=30orderbycomm；ENAMECOMM-------------------TURNER0ALLEN300WARD500MARTIN1400BLAKEJAMES
----2.空值不能被索引。虽然在某列上建立了索引，但是对该列的空值查询来说，因为空值没有被索引，所以不能改善查询的效率。例如下面的查询不能利用在MGR列上创建的索引。
SQL>selectenamefromempwheremgrisnull;ENAME----------KING
----另外正是因为空值不被索引，所以可在含有空值的列上建立唯一性索引（UNIQUEINDEX）。例如，可以在EMP表的COMM列上建立唯一性索引：
SQL>createuniqueindexemp_commonemp(comm);Indexcreated.
如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。 比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。 另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程); 这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。 是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。 其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！ 是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。
页:
[1]