Posted on 2007-07-16 22:32
kooyee 阅读(479)
评论(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