Oracle中null的使用详解

gelyon 2010-11-15 09:32:25 


--ps:今晚看到这篇好文章，讲的比较详细，这里贴出来大家再记忆深刻点

--其实很多东西看起来是很简单的，但是恰恰是它简单，才容易被我们忽视！



问：什么是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)　 --月份

sellnumber(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的空值就是这么的用法，我们最好熟悉它的约定，以防查出的结果不正确
...全文
1120 28 打赏 收藏 转发到动态 举报
写回复
用AI写文章
28 条回复
切换为时间正序
请发表友善的回复…
发表回复
gelyon 2011-10-20
  • 打赏
  • 举报
 回复
[Quote=引用 29 楼 formyra 的回复:]
update table1 set 列1=NULL where 列1 is not null;
这个我运行怎么不行呢？
oracle的
[/Quote]

语句没问题，具体看你那报错信息是什么
formyra 2011-10-19
  • 打赏
  • 举报
 回复
update table1 set 列1=NULL where 列1 is not null;
这个我运行怎么不行呢？
oracle的
C736059314 2011-08-15
  • 打赏
  • 举报
 回复
不错~~~
gys007 2010-11-29
  • 打赏
  • 举报
 回复
讲的不错，学习了
[Quote=引用楼主 gelyon 的回复:]
SQL code

--ps:今晚看到这篇好文章，讲的比较详细，这里贴出来大家再记忆深刻点
--其实很多东西看起来是很简单的，但是恰恰是它简单，才容易被我们忽视！

问：什么是NULL？
答：在我们不知道具体有什么数据的时候，也即未知，可以用NULL，
我们称它为空，ORACLE中，含有空值的表列长度为零。

ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：……
[/Quote]
gelyon 2010-11-29
  • 打赏
  • 举报
 回复
嗯，官网的资料哇，谢谢，把这个知识补全。。。[Quote=引用 24 楼 minitoy 的回复:]
记录个小知识点
To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column
Nulls are not indexed, except when the cluster key column value is ……
[/Quote]
minitoy 2010-11-29
  • 打赏
  • 举报
 回复
记录个小知识点
To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column
Nulls are not indexed, except when the cluster key column value is null or the index is a bitmap index.
badboy_playboy 2010-11-24
  • 打赏
  • 举报
 回复
说的很不错～
minitoy 2010-11-18
  • 打赏
  • 举报
 回复
哈哈，比我了解的全面多了。先收藏，慢慢看。[Quote=引用 21 楼 gelyon 的回复:]
null 这个东西，曾经只是知道，有些细节还真不太注意，要多学习。
[/Quote]
gelyon 2010-11-18
  • 打赏
  • 举报
 回复
null 这个东西，曾经只是知道，有些细节还真不太注意，要多学习。
minitoy 2010-11-18
  • 打赏
  • 举报
 回复
number的字段也是可以使用''来插入null的.[Quote=引用 13 楼 gelyon 的回复:]
引用 12 楼 minitoy 的回复:
顶。不过空字符串在oracle跟null没区别。


SQL code

如果不区分数据类型的话，空字符串''就是NULL，NULL就是空字符串''
如果需要区分数据类型的话，空字符串''一定是NULL，空数字也是NULL，其他空数据类型也是NULL
[/Quote]
suhenhappy 2010-11-18
  • 打赏
  • 举报
 回复
观看中。。。
狂龙骄子 2010-11-17
  • 打赏
  • 举报
 回复
感谢LZ分享
minitoy 2010-11-16
  • 打赏
  • 举报
 回复
顶。不过空字符串在oracle跟null没区别。
gelyon 2010-11-16
  • 打赏
  • 举报
 回复 




----二、空值的测试



----因为空值表示缺少数据，所以空值和其它值没有可比性，即不能用等于、不等于、大于或小于和其它数值比较，当然也包括空值本身（但是在decode中例外，两个空值被认为是等价）。

----测试空值只能用比较操作符ISNULL和ISNOTNULL。如果使用带有其它比较操作符的条件表达式，并且其结果依赖于空值，那么其结果必定是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（NULLAND 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 AND NULL

NULL



C BETWEEN 15 AND NULL

FALSE



A NOT BETWEEN 5 AND NULL

NULL



C NOT BETWEEN 5 AND NULL

NULL



C NOT BETWEEN 15 AND 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均为空值。
gelyon 2010-11-16
  • 打赏
  • 举报
 回复 


----在数据库中，空值用来表示实际值未知或无意义的情况。在一个表中，如果一行中的某列没有值，那么就称它为空值（NULL）。

----任何数据类型的列,只要没有使用非空（NOTNULL）或主键（PRIMARYKEY）完整性限制，都可以出现空值。

----在实际应用中，如果忽略空值的存在，将会造成造成不必要的麻烦。



----例如，在下面的雇员表（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是不同的。

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

----*空值不能被索引。
gelyon 2010-11-16
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 minitoy 的回复:]
顶。不过空字符串在oracle跟null没区别。
[/Quote]


如果不区分数据类型的话，空字符串''就是NULL，NULL就是空字符串''

如果需要区分数据类型的话，空字符串''一定是NULL，空数字也是NULL，其他空数据类型也是NULL
gelyon 2010-11-16
  • 打赏
  • 举报
 回复 


----再补充点：



----四、空值和函数



----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



----另外正是因为空值不被索引，所以可在含有空值的列上建立唯一性索引（UNIQUEINDEX）。例如，可以在EMP表的COMM列上建立唯一性索引：



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

Index created.
心中的彩虹 2010-11-16
  • 打赏
  • 举报
 回复
[Quote=引用楼主 gelyon 的回复:]
SQL code

--ps:今晚看到这篇好文章，讲的比较详细，这里贴出来大家再记忆深刻点
--其实很多东西看起来是很简单的，但是恰恰是它简单，才容易被我们忽视！

问：什么是NULL？
答：在我们不知道具体有什么数据的时候，也即未知，可以用NULL，
我们称它为空，ORACLE中，含有空值的表列长度为零。

ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：……
[/Quote]
很好 不过''跟null就是一样差不多的
null与运算符合在一起 都为false
gelyon 2010-11-15
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 zhuomingwang 的回复:]
强烈要求版主推荐下
[/Quote]
明天我还要补充点，在家里没环境做测试文档，远端连接公司又特慢，明天做好文档再来发表下，想把这个问题整理好。
真的不知道取什么名 2010-11-15
  • 打赏
  • 举报
 回复
强烈要求版主推荐下
加载更多回复（8）
oracle null使用详解
在我们不知道具体有什么数据的时候，也即未知，可以用NULL，我们称它为空，ORACLE中，含有空值的表列长度为零
Oracle中的分析函数详解
NULL 博文链接：https://yangkai.iteye.com/blog/754447
Oracle表分区详解
NULL 博文链接：https://flyshark.iteye.com/blog/1166724
ORACLE EXPLAIN PLAN的总结
NULL 博文链接：https://babydeed.iteye.com/blog/1567772
Oracle_XE安装、卸载详解.rar
NULL 博文链接：https://codsoul.iteye.com/blog/1531079
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章