分享
 
 
 

漫谈oracle中的空值

王朝oracle·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

在数据库中,空值用来表示实际值未知或无意义的情况。在一个表中,如果一行中的某列没有值,那么就称它为空值(NULL)。任何数据类型的列,只要没有使用非空(NOT NULL)或主键(PRIMARY KEY)完整性限制,都可以出现空值。在实际应用中,如果忽略空值的存在,将会造成造成不必要的麻烦。

例如,在下面的雇员表(EMP)中,雇员名(ENAME)为KING的行,因为KING为最高官员(PRESIDENT),他没有主管(MGR),所以其MGR为空值。因为不是所有的雇员都有手续费(COMM),所以列COMM允许有空值,除300、500、1400、0以外的其它各行COMM均为空值。

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

本文将以上述EMP表为例,具体讨论一下空值在日常应用中所具有的一些特性。

一、空值的生成及特点

1. 空值的生成

如果一列没有非空(NOT NULL)完整性限制,那么其缺省的值为空值,即如果插入一行时未指定该列的值,则其值为空值。

使用SQL语句INSERT插入行,凡未涉及到的列,其值为空值;涉及到的列,如果其值确实为空值,插入时可以用NULL来表示(对于字符型的列,也可以用''来表示)。

例:插入一行,其EMPNO为1、ENAME为'JIA'、SAL为10000、job和comm为空值。

SQLinsert into emp(empno,ename,job,sal,comm) values(1,'JIA',NULL,1000,NULL);

SQLselect * from emp where empno=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

1 JIA 1000

可以看到新插入的一行,除job和comm为空值外,mgr、hiredate、deptno三列由于插入时未涉及,也为空值。

使用SQL语句UPDATE来修改数据,空值可用NULL来表示(对于字符型的列,也可以用''来表示)。例:

SQLupdate emp set ename=NULL,sal=NULL where empno=1;

2. 空值的特点

空值具有以下特点:

* 等价于没有任何值。

* 与 0、空字符串或空格不同。

* 在where条件中, Oracle认为结果为NULL的条件为FALSE,带有这样条件的select语句不返回行,并且不返回错误信息。但NULL和FALSE是不同的。

* 排序时比其他数据都大。

* 空值不能被索引。

二、空值的测试

因为空值表示缺少数据,所以空值和其它值没有可比性,即不能用等于、不等于、大于或小于和其它数值比较,当然也包括空值本身(但是在decode中例外,两个空值被认为是等价)。测试空值只能用比较操作符IS NULL 和IS NOT NULL。如果使用带有其它比较操作符的条件表达式,并且其结果依赖于空值,那么其结果必定是NULL。在where条件中,Oracle认为结果为NULL的条件为FALSE,带有这样条件的select语句不返回行,也不返回错误信息。

例如查询EMP表中MGR为NULL的行:

SQLselect * from emp where mgr='';

no rows selected

SQLselect * from emp where mgr=null;

no rows selected

SQLselect * from emp where mgr is null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7839 KING PRESIDENT 17-NOV-81 5000 10

第1、2句写法不妥,WHERE条件结果为NULL,不返回行。第三句正确,返回MGR为空值的行。

三、 空值和操作符

1.空值和逻辑操作符

逻辑操作符

表达式

结果

AND

NULL AND TRUE

NULL

NULL AND FALSE

FALSE

NULL AND NULL

NULL

OR

NULL OR TRUE

TRUE

NULL OR FALSE

NULL

NULL OR NULL

NULL

NOT

NOT NULL

NULL

可以看到,在真值表中,除NULL AND FALSE 结果为FALSE、NULL OR TRUE结果为TRUE以外,其它结果均为NULL。

虽然在where条件中,Oracle认为结果为NULL的WHERE条件为FALSE,但在条件表达式中NULL不同于FALSE。例如在NOT ( NULL AND FALSE )和NOT ( NULL AND NULL )二者中仅有一处FALSE和TRUE的区别,但NOT ( NULL AND FALSE )的结果为 TRUE,而NOT ( NULL AND NULL )的结果为NULL。

下面举例说明空值和逻辑操作符的用法:

SQL select * from emp where not comm=null and comm!=0;

no rows selected

SQL select * from emp where not ( not comm=null and comm!=0 );

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

第一个Select语句,条件“not comm=null and comm!=0”等价于NULL AND COMM!=0。对于任意一行,如果COMM为不等于0的数值,条件等价于NULL AND TRUE,结果为NULL;如果COMM等于0,条件等价于NULL AND FALSE,结果为FALSE。所以,最终结果不返回行。

第二个Select语句的条件为第一个Select语句条件的“非”(NOT),对于任意一行,如果COMM为不等于0的数值,条件等价于NOT NULL,结果为NULL;如果COMM等于0,条件等价于NOT FALSE,结果为TRUE。所以,最终结果返回行COMM等于0的行。

2.空值和比较操作符

(1)IS [NOT] NULL:是用来测试空值的唯一操作符(见“空值的测试”)。

(2)=、!=、=、、

SQLselect ename,sal,comm from emp where salcomm;

ENAME SAL COMM

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

ALLEN 1600 300

WARD 1250 500

TURNER 1500 0

sal或comm为空值的行,salcomm比较结果为NULL,所以凡是sal或comm为空值的行都没有返回。

(3)IN和NOT IN操作符

SQLselect ename,mgr from emp where mgr in (7902,NULL);

ENAME MGR

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

SMITH 7902

在上述语句中,条件“mgr in (7902,NULL)”等价于mgr=7902 or mgr=NULL。对于表EMP中的任意一行,如果mgr为NULL,则上述条件等价于NULL OR NULL,即为NULL;如果mgr为不等于7902的数值,则上述条件等价于FALSE OR NULL,即为NULL;如果mgr等于7902,则上述条件等价于TRUE OR NULL,即为TRUE。所以,最终结果能返回mgr等于7902的行。

SQLselect deptno from emp where deptno not in ('10',NULL);

no rows selected

在上述语句中,条件“deptno not in ('10',NULL)”等价于deptno!='10' and deptno!=NULL,对于EMP表中的任意一行,条件的结果只能为NULL或FALSE,所以不返回行。

(4)any,some

SQLselect ename,sal from emp where sal any(3000,null);

ENAME SAL

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

KING 5000

条件“sal any(3000,null)”等价于sal3000 or salnull。类似前述(3)第一句,最终结果返回所有sal3000的行。

(5)All

SQLselect ename,sal from emp where sal all(3000,null);

no rows selected

条件“sal all(3000,null)”等价于sal3000 and salnull, 结果只能为NULL或FALSE,所以不返回行。

(6)(not)between

SQLselect ename,sal from emp where sal between null and 3000;

no rows selected

条件“sal between null and 3000”等价于sal=null and sal

SQLselect ename,sal from emp where sal not between null and 3000;

ENAME SAL

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

KING 5000

条件“sal not between null and 3000”等价于sal3000,类似前述(3)的第一句,结果返回sal3000的行。

下表为比较操作符和空值的小结:

比较操作符

表达式(例:A、B是NULL、C=10)

结果

IS NULL、IS NOT NULL

A IS NULL

TRUE

A IS NOT NULL

FALSE

C IS NULL

FALSE

C IS NOT NULL

TRUE

=、!=、=、、

A = NULL

NULL

A NULL

NULL

C = NULL

NULL

C NULL

NULL

IN (=ANY)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有