點(diǎn)擊上方“數(shù)據(jù)管道”，選擇“置頂星標(biāo)”公眾號

干貨福利，第一時(shí)間送達(dá)

什么是NULL

DROP?TABLE?IF?EXISTS?t_sample_null;
CREATE?TABLE?t_sample_null (
????id?INT(11) unsigned?NOT?NULL?AUTO_INCREMENT COMMENT?'自增主鍵',
????name?VARCHAR(50) NOT?NULL?COMMENT?'名稱',
????remark VARCHAR(500) COMMENT?'備注',
????primary key(id)
) COMMENT?'NULL樣例';

INSERT?INTO?t_sample_null(name, remark)
VALUES('zhangsan', '張三'),('李四', NULL);

-- SQL 不報(bào)錯(cuò)，但查不出結(jié)果
SELECT?* FROM?t_sample_null WHERE?remark = NULL;

DROP?TABLE?IF?EXISTS?t_student;
CREATE?TABLE?t_student (
????id?INT(11) unsigned?NOT?NULL?AUTO_INCREMENT COMMENT?'自增主鍵',
????name?VARCHAR(50) NOT?NULL?COMMENT?'名稱',
????age INT(3) COMMENT?'年齡',
????remark VARCHAR(500) NOT?NULL?DEFAULT?''?COMMENT?'備注',
????primary key(id)
) COMMENT?'學(xué)生信息';

INSERT?INTO?t_student(name, age)
VALUE('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', NULL),('boss', 18);

SELECT?* FROM?t_student;

-- 1. 約翰年齡是 NULL (未知的 NULL !)
SELECT?*
FROM?t_student
WHERE?age = NULL
OR?age <> NULL;

-- 2. 對 NULL 使用比較謂詞后，結(jié)果為unknown
SELECT?*
FROM?t_student
WHERE?unknown
OR?unknown;

-- 3．unknown OR unknown 的結(jié)果是unknown (參考三值邏輯的邏輯值表)
SELECT?*
FROM?t_student
WHERE?unknown;

DROP TABLE IF?EXISTS t_student_A;
CREATE TABLE t_student_A (
????id INT(11) unsigned NOT NULL?AUTO_INCREMENT COMMENT '自增主鍵',
????name VARCHAR(50) NOT NULL?COMMENT '名稱',
????age INT(3) COMMENT '年齡',
????city VARCHAR(50) NOT NULL?COMMENT '城市',
????remark VARCHAR(500) NOT NULL?DEFAULT?''?COMMENT '備注',
????primary key(id)
) COMMENT '學(xué)生信息';

INSERT INTO t_student_A(name, age, city)
VALUE
('zhangsan', 25,'深圳市'),('wangwu', 60, '廣州市'),
('bruce', 32, '北京市'),('yzb', NULL, '深圳市'),
('boss', 43, '深圳市');

DROP TABLE IF?EXISTS t_student_B;
CREATE TABLE t_student_B (
????id INT(11) unsigned NOT NULL?AUTO_INCREMENT COMMENT '自增主鍵',
????name VARCHAR(50) NOT NULL?COMMENT '名稱',
????age INT(3) COMMENT '年齡',
????city VARCHAR(50) NOT NULL?COMMENT '城市',
????remark VARCHAR(500) NOT NULL?DEFAULT?''?COMMENT '備注',
????primary key(id)
) COMMENT '學(xué)生信息';

INSERT INTO t_student_B(name, age, city)
VALUE
('馬化騰', 45, '深圳市'),('馬三', 25, '深圳市'),
('馬云', 43, '杭州市'),('李彥宏', 41, '深圳市'),
('年輕人', 25, '深圳市');

?* FROM t_student_B;

-- 1. 執(zhí)行子查詢，獲取年齡列表
SELECT?* FROM?t_student
WHERE?age NOT?IN(43, NULL, 25);

-- 2. 用NOT 和IN 等價(jià)改寫NOT IN
SELECT?* FROM?t_student
WHERE?NOT?age IN?(43, NULL, 25);

-- 3. 用OR 等價(jià)改寫謂詞IN
SELECT?* FROM?t_student
WHERE?NOT?( (age = 43) OR?(age = NULL) OR?(age = 25) );

-- 4. 使用德· 摩根定律等價(jià)改寫
SELECT?* FROM?t_student
WHERE?NOT?(age = 43) AND?NOT(age = NULL) AND?NOT?(age = 25);

-- 5. 用<> 等價(jià)改寫 NOT 和 =
SELECT?* FROM?t_student
WHERE?(age <> 43) AND?(age <> NULL) AND?(age <> 25);

-- 6. 對NULL 使用<> 后，結(jié)果為 unknown
SELECT?* FROM?t_student
WHERE?(age <> 43) AND?unknown?AND?(age <> 25);

-- 7．如果 AND 運(yùn)算里包含 unknown，則結(jié)果不為true（參考三值邏輯的邏輯值表）
SELECT?* FROM?t_student
WHERE?false?或 unknown;

-- 1. 在子查詢里和 NULL 進(jìn)行比較運(yùn)算，此時(shí) A.age 是 NULL
SELECT?* FROM?t_student_B B
WHERE?NOT?EXISTS?(
????SELECT?* FROM?t_student_A A
????WHERE?B.age = NULL
????AND?A.city = '深圳市'?
);

-- 2. 對NULL 使用“=”后，結(jié)果為 unknown
SELECT?* FROM?t_student_B B
WHERE?NOT?EXISTS?(
????SELECT?* FROM?t_student_A A
????WHERE?unknown
????AND?A.city = '深圳市'?
);

-- 3. 如果AND 運(yùn)算里包含 unknown，結(jié)果不會是true
SELECT?* FROM?t_student_B B
WHERE?NOT?EXISTS?(
????SELECT?* FROM?t_student_A A
????WHERE?false?或 unknown
);

-- 4. 子查詢沒有返回結(jié)果，因此相反地，NOT EXISTS 為 true
SELECT?* FROM?t_student_B B
WHERE?true;