oracle null使用详解

NULL 使用详解 [轉貼]NULL 使用详解 问:什么是NULL? 答:在我们不知道具体有什么数据的时候,也即未知,可以用NULL,我们称它为空,ORACLE中,含有空值的表列长度为零。 ORACLE允许任何一种数据类型的字段为空,除了以下两种情况: 1、主键字段(primary key), 2、定义时已经加了NOT NULL限制条件的字段 说明: 1、等价于没有任何值、是未知数。 2、 NULL与0、空字符串、空格都不同。 3、对空值做加、减、乘、除等运算操作,结果仍为空。 4、 NULL的处理使用NVL函数。 5、比较时使用关键字用“is null”和“is not null”。 6、空值不能被索引,所以查询时有些符合条件的数据可能查不出来, count(*)中,用nvl(列名,0)处理后再查。 7、排序时比其他数据都大(索引默认是降序排列,小→大),所以NULL值总是排在最后。 ·使用方法: · SQL> select 1 from dual where null=null; · ·没有查到记录 · · SQL> select 1 from dual where null=''; · ·没有查到记录 · · SQL> select 1 from dual where ''=''; · ·没有查到记录 · · SQL> select 1 from dual where null is null; · · 1 · --------- · 1 · · SQL> select 1 from dual where nvl(null,0)=nvl(null,0); · · 1 · --------- · 1 · ·对空值做加、减、乘、除等运算操作,结果仍为空。 · SQL> select 1+null from dual; · SQL> select 1-null from dual; · SQL> select 1*null from dual; · SQL> select 1/null from dual; · · ·查询到一个记录. · ·注:这个记录就是SQL语句中的那个null · ·设置某些列为空值 · update table1 set 列1=NULL where 列1 is not null; · · ·现有一个商品销售表sale,表结构为: · month char(6) --月份 · sell number(10,2) --月销售金额 · · create table sale (month char(6),sell number); · insert into sale values('200001',1000); · insert into sale values('200002',1100); · insert into sale values('200003',1200); · insert into sale values('200004',1300); · insert into sale values('200005',1400); · insert into sale values('200006',1500); · insert into sale values('200007',1600); · insert into sale values('200101',1100); · insert into sale values('200202',1200); · insert into sale values('200301',1300); · insert into sale values('200008',1000); · insert into sale(month) values('200009');(注意:这条记录的sell值为空) · commit; ·共输入12条记录 · · SQL> select * from sale where sell like '%'; · · MONTH SELL · ------ --------- · 200001 1000 · 200002 1100 · 200003 1200 · 200004 1300 · 200005 1400 · 200006 1500 · 200007 1600 · 200101 1100 · 200202 1200 · 200301 1300 · 200008 1000 · ·查询到11记录. · ·结果说明: ·查询结果说明此SQL语句查询不出列值为NULL的字段 ·此时需对字段为NULL的情况另外处理。 · SQL> select * from sale where sell like '%' or sell is null; · SQL> select * from sale where nvl(sell,0) like '%'; · · MONTH SELL · ------ --------- · 200001 1000 · 200002 1100 · 200003 1200 · 200004 1300 · 200005 1400 · 200006 1500 · 200007 1600 · 200101 1100 · 200202 1200 · 200301 1300 · 200008 1000 · 200009 · ·查询到12记录. · · Oracle的空值就是这么的用法,我们最好熟悉它的约定,以防查出 的结果不正确 舉例 在数据库中,空值用来表示实际值未知或无意义的情况。在一个表中,如果一行中的某列没有值,那么就称 它为空值(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 为空值。 SQL >insert into emp(empno,ename,job,sal,comm) values(1,'JIA',NULL,1000,NULL); SQL >select * from emp where empno=1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- 1 JIA 1000 ---- 可以看到新插入的一行,除job 和comm 为空值外,mgr、hiredate、deptno 三列由于插入时未涉及,也为空值。 ---- 使用SQL 语句UPDATE 来修改数据,空值可用NULL 来表示 (对于字符型的列,也可以用'' 来表示)。例: SQL >update 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 的行: SQL >select * from emp where mgr=''; no rows selected SQL >select * from emp where mgr=null; no rows selected SQL >select * 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)=、!=、>=、<=、>、< SQL >select ename,sal,comm from emp where sal >comm; ENAME SAL COMM ---------- --------- --------- ALLEN 1600 300 WARD 1250 500 TURNER 1500 0 ---- sal 或comm 为空值的行,sal>comm 比较结果为NULL,所以凡是sal 或comm 为空值的行都没有返回。 ---- (3)IN 和NOT IN 操作符 SQL >select 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 的行。 SQL >select 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 SQL >select ename,sal from emp where sal > any(3000,null); ENAME SAL ---------- --------- KING 5000 ---- 条件"sal > any(3000,null)" 等价于sal >3000 or sal >null。类似前述(3)第一句,最终结果返回所有sal >3000 的行。 ---- (5)All SQL >select ename,sal from emp where sal > all(3000,null); no rows selected ---- 条件"sal> all(3000,null)" 等价于sal >3000 and sal >null, 结果只能为NULL 或FALSE,所以不返回行。 ---- (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, 结果只能为NULL 或FALSE,所以不返回行。 SQL >select ename,sal from emp where sal not between null and 3000; ENAME SAL ---------- --------- KING 5000 ---- 条件"sal not between null and 3000" 等价于sal3000, 类似前 述(3)的第一句,结果返回sal>3000 的行。---- 下表为比较操作符和空值的小结: ---- 比较操作符 ---- 表达式(例: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) A IN (10,NULL) NULL C IN (10,NULL) TRUE C IN (20,NULL) NULL NOT IN ( 等价于!=ALL) A NOT IN (20,NULL) NULL C NOT IN (20,NULL) FALSE C NOT IN (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 A BETWEEN 5 AND NULL NULL C BETWEEN 5 AN D NULL NULL C BETWEEN 15 AN D NULL FALSE A NOT BETWEEN 5 AND NULL NULL C NOT BETWEEN 5 AN D NULL NULL C NOT BETWEEN 15 AN D NULL TRUE ---- 3、空值和算术、字符操作符 ---- (1)算术操作符:空值不等价于0,任何含有空值的算术表达式其运算结果都为空值,例如空值加10 为空值。 ---- (2)字符操作符||:因为ORACLE 目前处理零个字符值的方法与处理空值的方法相同(日后的版本中不一定仍然如此),所以对于||,空值等价于零个字符值。例: SQL >select ename,mgr,ename||mgr,sal,comm,sal+comm from emp; ENAME MGR ENAME||MGR SAL COMM SAL+COMM ---------- --------- ------------- --------- --------- --------- SMITH 7902 SMITH7902 800 ALLEN 7698 ALLEN7698 1600 300 1900 WARD 7698 WARD7698 1250 500 1750 JONES 7839 JONES7839 2975 MARTIN 7698 MARTIN7698 1250 1400 2650 BLAKE 7839 BLAKE7839 2850 CLARK 7839 CLARK7839 2450 SCOTT 7566 SCOTT7566 3000 KING KING 5000 TURNER 7698 TURNER7698 1500 0 1500 ADAMS 7788 ADAMS7788 1100 JAMES 7698 JAMES7698 950 FORD 7566 FORD7566 3000 MILLER 7782 MILLER7782 1300 ---- 我们可以看到,凡mgr 为空值的,ename||mgr 结果等于ename;凡是comm 为空值的行,sal+comm 均为空值。 ---- 四、空值和函数 ---- 1 .空值和度量函数 ---- 对于度量函数,如果给定的参数为空值,则其(NVL、TRANSLATE 除外)返回值为空值。如下例中的ABS(COMM),如果COMM 为空值,ABS(COMM) 为空值。 SQL > select ename,sal,comm,abs(comm) from emp where sal< 1500; ENAME SAL COMM ABS(COMM) ---------- --------- --------- --------- SMITH 800 WARD 1250 500 500 MARTIN 1250 1400 1400 ADAMS 1100 JAMES 950 MILLER 1300 ---- 2. 空值和组函数 ---- 组函数忽略空值。在实际应用中,根据需要可利用nvl 函数用零代替空值。例: SQL >select count(comm),sum(comm),avg(comm) from emp; COUNT(COMM) SUM(COMM) AVG(COMM) ----------- --------- --------- 4 2200 550 SQL >select count(nvl(comm,0)),sum(nvl(comm,0)),avg(nvl(comm,0)) from emp; COUNT(NVL(COMM,0)) SUM(NVL(COMM,0)) AVG(NVL(COMM,0)) ------------------ ---------------- ---------------- 14 2200 157.14286 ---- 第一个SELECT 语句忽略COMM 为空值的行,第二个SELECT 语句使用NVL 函数统计了所有的COMM,所以它们统计的个数、平均值都不相同。另外需要注意的是,在利用组函数进行数据处理时,不同的写法具有不同的不同含义,在实际应用中应灵活掌握。例如: SQL >select deptno,sum(sal),sum(comm), sum(sal+comm),sum(sal)+sum(comm),sum(nvl(sal,0)+nvl(comm,0)) from emp group by deptno; DEPTNO SUM(SAL) SUM(COMM) SUM(SAL+COMM) SUM(SAL) +SUM(COMM) SUM(NVL(SAL,0)+NVL(COMM,0)) --------- --------- --------- ------------- ------- 10 8750 8750 20 10875 10875 30 9400 2200 7800 11600 11600 ---- 可以看到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 > select ename,comm from emp where deptno='30' order by comm; ENAME COMM ---------- --------- TURNER 0 ALLEN 300 WARD 500 MARTIN 1400 BLAKE JAMES ---- 2. 空值不能被索引。虽然在某列上建立了索引,但是对该列的空值查询来说,因为空值没有被索引,所以不能改善查询的效率。例如下面的查询不能利用在MGR 列上创建的索引。 SQL >select ename from emp where mgr is null; ENAME ---------- KING ---- 另外正是因为空值不被索引,所以可在含有空值的列上建立唯一性索引(UNIQUE INDEX)。例如,可以在EMP 表 的COMM 列上建立唯一性索引: SQL > create unique index emp_comm on emp(comm); Index created. sqlPlus中的空值 sqlPlus中的空值 当将版本提高到O r a c l e 8 i时,应当特别注意不可避免的“ n u l l”字符。 当表中的表列值未知时,就置它为空值( n u l l )。 在O r a c l e 8 i中,含有空值的表列长度为零, 但要注意, 今后的版本中不一定是这样。 比较操作中的空值问题 保存空值数据的唯一办法是使用关键字“ is null”和“is not null”,如下所示: select count(*) from mytab where cola is null; select count(*) from mytab where cola is not null; 查询语句“select count(*),count_amt from comm group by comm_amt;”返回以下输出结果: COUNT(*) COMM_AMT - - - - - - - - - - - - - - - - - - - 12 1200 7 1700 8 对于n u l l比较操作,当采用其他结构时很快会遇到麻烦。 假设表中的c o m m _ a m t表列包含以下数据: Column Value Number of Rows N u l l 8 1 2 0 0 1 2 1 7 0 0 7 当执行S Q L语句“select count(*) from comm where comm_amt<1700;”时, 其记数结果为1 2,这个结果来自于c o m m _ a m t表列中包含的非空值1 2 0 0的个数。而值为空的数据行没有被记数。这是因为O r a c l e不明白那 些为空的c o m m _ a m t表列的值,空没有意义且不确定,所以空的表列值的数据行没有被返回记数。下面将讨论怎样使用空的表列值进行比较操作。 使用N V L进行空值比较操作 n v l函数是解决空表列值比较的方法,下面列出了从c o m m表中检索包含空值记数的语句: SQL>select count(*),nvl(comm_amt,0) from comm group by nvl(comm_amt,0); COUNT(*) NVL (COMM_AMT,0) ------- ---------------- 8 0 1 2 1 2 0 0 7 1 7 0 0 n v l函数用“ 0”值置换c o m m _ a m t表列中值为空的所有数据行, 使用这个新功能让我们运行上述查询, 检索有多少个c o m m _ a m t表列值小于1 7 0 0的行。 SQL>select count(*) from comm where nvl (comm_amt,0)<1700; COUNT (*) - - - - - - - - - 2 0 下面再来讨论一下字符串空表列值的比较操作的正确与错误的方法。随着处理空表列值 能力的提高,将有助于用户更好地理解空值数据。 The NULL=NULL比较的异常情况 如果不使用语法“is null”和“is not null”,将带来麻烦,下面的语句对这一点做出了说明: SQL> select 12 from dual where null=''; 沒有任何資料列被選取 SQL> select 12 from dual where ''=''; 沒有任何資料列被選取 SQL> select 12 from dual where null=null; 沒有任何資料列被選取 SQL> select 12 from dual where null is null; 12 ---------- 12 SQL> select 12 from dual where nvl(null,'x')=nvl(null,'x'); 12 ---------- 12 O r a c l e 8 i这种方法处理空值,许多开发者和数据库管理员有时会有一 种受到 挫折的感觉。很显然,除了熟悉对空值的约定外别无它法,只有熟练地掌握这方面的知识,才能保证查询结果的正确。 警惕三值逻辑: 在pl/sql中 if a>=b then s1; else s2; end if; 和 if a < b then s2; else s1; end if; 因为null的问题而不等价。 bitmap index 包含null If a string is null, you can't really compare it with anything. However, we can still combine a null string with non-ull strings and get a sensible result. DECLARE empty VARCHAR2(2000); -- defaults to null the_enemy VARCHAR2(200); BEGIN the_enemy := 'blue' || empty || 'meanies'; END; If a number is null, though, sometimes it is just ignored, and sometimes it acts like a neutron bomb (destroys values but leaves variables standing). In SQL, aggregate function like AVG and SUM ignore nulls. In PL/SQL, though, numeric operations on null values yield null results! DECLARE qty_on_hand NUMBER; -- defaults to null qty_sold NUMBER := 451; tot_qty NUMBER := 0; BEGIN tot_qty := qty_on_hand + qty_sold; END; To get a more common-sense result, you can use the built-in NVL function, which is a convenient way to provide a proxy value if the actual value is null. Logic tests (AND, OR) may not appear to behave consistently with respect to nulls. TRUE AND NULL => NULL FALSE AND NULL => NULL NOT NULL => NULL TRUE OR NULL => TRUE FALSE OR NULL => NULL A logic test only 'passes' if it's really true. NULL is not really true (nor is false). select * from dual where null is null; And if you use this sentence, you will get the result. 空值 列可以接受或拒绝空值。在数据库内 NULL 是特殊值,代表未知值的概念。NULL 不同于空字符或 0。空字符实际上是有效字符,0 是有效数字。而 NULL 只是表示该值未知这一概念。NULL 也不同于零长度字符串。如果列定义中包含NOT NULL 子句,则不能为该行插入含有 NULL 值的行。如果列定义中仅包含NULL 关键字,则接受 NULL 值。 在列内允许 NULL 值会增加使用该列的逻辑比较的复杂性。SQL-92 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 规定 关于下班后关闭电源的规定党章中关于入党时间的规定公务员考核规定下载规定办法文件下载宁波关于闷顶的规定 :对 NULL 值的任何比较都不取值为 TRUE 或 FALSE,而是取值为 UNKNOWN。此规定在比较运算符中引入了三值逻辑,而要正确运用该逻辑很困难。[摘自Microsoft SQLServer联机丛 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf ]