Posted on 2007-07-16 22:32
kooyee 閱讀(489)
評論(0) 編輯 收藏 所屬分類:
Database數據庫技術
在Database中" "沒有值(空白)并不是等于NULL.
空格也是一個值, 而不是NULL
如果說給一個column賦值為空(不是空格,也不含空格), <null>
set column = '' //(單引號中間沒空格)
而不是
set column = NULL
數據庫中, 當一個字段沒有任何資料時, 便是NULL
當你在處理由數據庫中所取出的 NULL 的資料時, 你必須要很注意, 因為NULL 所代表的是不合法的資料, 當某些函數在處理數學運算時,NULL 可能會制造一些麻煩, 你應先用 IsNull() 來判斷字段是否為NULL, 再做適當的處理,
SELECT *
FROM test
WHERE test1 IS NULL;
NOTE: Null In Oracle is an absence of information. A null can be assigned but it can not be equated with anything: Even itself.
While this behavior is ANSI compliant it is not similar to the behavior in many other commercial RDBMS products. |
| A simple SELECT statement to use for demonstrating the properties of NULL |
SELECT COUNT(*)
FROM all_tables
WHERE 1 = 1; |
| A NULL is not equal to a NULL |
SELECT COUNT(*)
FROM all_tables
WHERE NULL = NULL; |
| A NULL cannot be not equal to a NULL |
SELECT COUNT(*)
FROM all_tables
WHERE NULL <> NULL; |
| A NULL is does not equal an empty string |
SELECT COUNT(*)
FROM all_tables
WHERE NULL = ''; |
| A NULL can be used in an INSERT |
CREATE TABLE test (
test1 NUMBER(10),
test2 VARCHAR2(20));
INSERT INTO test
(test1, test2)
VALUES
(1, NULL);
INSERT INTO test
(test1, test2)
VALUES
(NULL, 'A');
SELECT *
FROM test; |
| |
| NOTE: Null is a state of being that can be interrogated as to whether it does or does not exist. |
| A simple SELECT based on a column with a NULL |
SELECT *
FROM test
WHERE test1 IS NULL;
SELECT *
FROM test
WHERE test1 IS NOT NULL |
| A NULL can be used in an UPDATE |
UPDATE test
SET test1 = '2'
WHERE test2 IS NULL;
SELECT *
FROM test;
UPDATE test
SET test2 = 'B'
WHERE test2 IS NOT NULL;
SELECT *
FROM test; |
| A column can be updated to not contain a value |
UPDATE test
SET test1 = NULL
WHERE ROWNUM = 1;
SELECT *
FROM test; |
| NULL can be used as part of the WHERE clause criteria in a DELETE Statement |
DELETE FROM test
WHERE test1 IS NULL;
SELECT *
FROM test; |
Understand the implications of NULL |
CREATE TABLE t (
col1 NUMBER(3),
col2 NUMBER(3),
col3 NUMBER(3));
desc t
INSERT INTO t
(col1, col2, col3)
VALUES
(1, NULL, NULL);
INSERT INTO t
(col1, col2, col3)
VALUES
(NULL, 2, NULL);
INSERT INTO t
(col1, col2, col3)
VALUES
(NULL, NULL, 3);
INSERT INTO t
(col1, col2, col3)
VALUES
(4, 4, 4);
COMMIT;
SELECT *
FROM t;
SELECT SUM(RESULT_TMP) RESULT
FROM (
SELECT col1 - (col2 + col3) RESULT_TMP
FROM t);
SELECT SUM(col1) - (SUM(col2) + SUM(col3)) RESULT
FROM t;
Note: For any row that has one of the values null, the entire row sums to null and is not included in the second query but the other columns in the row contribute to the sums in the query. So the first query includes more terms than the second. |
http://www.psoug.org/reference/null.html