關于oracle中的空值

          Posted on 2007-10-11 11:14 優雅Dě頽廢 閱讀(428) 評論(0)  編輯  收藏 所屬分類: 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 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 均 為 空 值。

          ---- 四、 空 值 和 函 數

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

          posts - 13, comments - 1, trackbacks - 0, articles - 0

          Copyright © 優雅Dě頽廢

          主站蜘蛛池模板: 克拉玛依市| 历史| 调兵山市| 门头沟区| 德昌县| 怀柔区| 常熟市| 华容县| 来凤县| 三明市| 吉安县| 九台市| 个旧市| 佛山市| 额尔古纳市| 东丰县| 南江县| 徐州市| 镇雄县| 丹东市| 翁源县| 扎囊县| 象山县| 甘肃省| 白沙| 盐城市| 茌平县| 黔江区| 昭平县| 道孚县| 玛多县| 哈密市| 西丰县| 包头市| 新沂市| 精河县| 楚雄市| 潍坊市| 乌海市| 仁寿县| 汶上县|