手表網(wǎng)站有哪個比較好刷外鏈網(wǎng)站
一、新邏輯狀態(tài):未知
- 對于大多數(shù)其他語言的邏輯判斷,一般只有兩種結(jié)果:
真(TURE)
或假(FALSE)
- 但在SQL中,還會有第三種判斷結(jié)果:
未知(UNKNOWN)
,表示無法判斷出真或者假。
未知狀態(tài)會影響傳統(tǒng)邏輯運算(與或非等)的結(jié)果,總結(jié)如下:
1. 邏輯與操作
AND/&& | 真 | 假 | 未知 |
---|---|---|---|
真 | 真 | ||
假 | 假 | 假 | |
未知 | 未知 | 假 | 未知 |
特別注意:
- 對于AND運算符,只有當兩邊的運算結(jié)果都為真時,最終結(jié)果才為真
- 真 AND 未知 = 未知, 并不是真
2. 邏輯或操作
OR | 真 | 假 | 未知 |
---|---|---|---|
真 | 真 | ||
假 | 真 | 假 | |
未知 | 真 | 未知 | 未知 |
- 對于OR運算符,只要兩邊的運算有一個為真時,最終結(jié)果就為真,否則最終結(jié)果為假或者未知
3. 邏輯非操作
NOT | 運算結(jié)果 |
---|---|
真 | 假 |
假 | 真 |
未知 | 未知 |
二、SQL中"未知"狀態(tài)的判斷影響
SQL語句中的WHERE、HAVING、CASE表達式,只返回邏輯運算結(jié)果為真的數(shù)據(jù),不返回為假或者未知的數(shù)據(jù)
三、NULL空值
在數(shù)據(jù)庫中,空值NULL
是一個特殊值,表示缺失或者未知
在SQL語句中,任何數(shù)據(jù)與空值進行算術(shù)比較的結(jié)果是未知,而非真或非假.。所以空值NULL無法通過 “WHERE c1 = NULL” 來判斷,需要寫成 WHERE c1 IS NULL
,才能作為條件篩選出查詢字段為NULL的數(shù)據(jù)
1. NULL判斷的特殊性
即使兩個未知數(shù)據(jù)進行比較,運算結(jié)果也是未知的,比如下面的比較,都得不出TRUE,而是未知。以下例子則判斷為未知
NULL = 0
NULL != 0
NULL = ‘’ (空字符串)
NULL = !‘’
NULL = NULL
NULL = !NULL
因此在WHERE語句中進行判斷時,務(wù)必要注意查詢列或者查詢條件(即等號兩邊的數(shù)據(jù))都有沒有可能為NULL,如果有則要用IS NULL
來判定
替代方案
Mysql提供了<=>
運算符,即可等值比較,也可空值比較;
-- mysql
SELECT 1 <=> 1, NULL <=> NULL;
PostgreSQL提供的是:IS [NOT] DISTINCT FROM
-- postgreSQL
SELECT 1 IS DISTINCT FROM 1, NULL IS DISTINCT FROM NULL;
2. NULL對IN() 運算符的影響
IN運算符為判斷所給條件是否在某個集合中。內(nèi)部實際使用等值運算符=
來判斷是否和集合中的每個元素相等,再用OR串聯(lián)起來得到最后的邏輯結(jié)果。
SELECT *
FROM student
WHERE name IN("LiLei", "HanMeimei") -- 等同于 WHERE name = "LiLei" OR name = "HanMeimei"
因此如果想通過IN() 運算符中加NULL元素來將被查字段中的NULL值也篩選出來,實際是無法生效的。比如想實現(xiàn)以下SQL篩選出學生姓名為NULL的,不會有效
-- 無法篩選出name為NULL的記錄
SELECT *
FROM student
WHERE name IN("LiLei", "HanMeimei", NULL)
/* 等同于 WHERE name = "LiLei" OR name = "HanMeimei" or name = NULL 對于真正name為NULL的數(shù)據(jù),此表達式最終的結(jié)果為未知,不會被篩選出來 */
在NOT IN() 中使用NULL,影響會更大,使得判斷無法篩選出任何記錄
-- 無法篩選出任何記錄
SELECT *
FROM student
WHERE name NOT IN("LiLei", "HanMeimei", NULL)
/* 因為原句等同于: WHERE name != "LiLei" and name != "HanMeimei" and name != NULL. 任何值在最后一句中的判斷結(jié)果都會為"UNKOWN",使得整個判斷變?yōu)槲粗?#xff0c;被過濾掉 */
3. NULL對子查詢語句中,ALL()/ANY() 運算符的影響
子查詢中,可以通過比較運算符(=、!=、<、<=、>、>=)與ALL、ANY的組合,來表示等于、不等于、大于…集合中的全部數(shù)據(jù)
SELECT *
FROM student
WHERE class =ANY ( -- 查找屬于1年級的學生SELECT class FROM teacherWHERE grade = 1
)
ALL
運算符相當于:對其中每個選項進行比較運算符計算,并用AND運算符串聯(lián)
IN
運算符相當于:對其中每個選項進行=運算符計算,并用OR運算符串聯(lián)
ANY
與IN類似,也是由OR運算符串聯(lián),比較運算符寫于ANY之前;如果是=ANY
,則與IN相同
對于ALL、ANY等運算符,后面加上NULL不會成功篩選出想要的NULL數(shù)據(jù),相反會導致比較離譜的運算結(jié)果
總結(jié)
- 使用IN/NOT IN/ALL/ANY時,切記不要在選項中設(shè)置NULL。對于子查詢做以上匹配結(jié)果時,也要注意務(wù)必過濾下NULL數(shù)據(jù)
4. 空值處理
COALESCE函數(shù)
COALESCE(exp1, exp2, exp3, …)接收一個輸入列表,返回第一個非NULL的參數(shù);若都為空,則返回NULL
SELECT COALESCE(yuwen_score, shuxue_score, yingyu_score)
FROM student
可以用COALESCE將NULL轉(zhuǎn)換為別的默認值,類似于CASE WHEN
SELECT COALESCE(yuwen_score, 0) -- 若語文成績?yōu)镹ULL, 則記為0分
FROM student
NULLIF函數(shù)
NULLIF(exp1, exp2)接收兩個入?yún)?#xff1a;若相等則返回NULL;若不等則返回exp1
SELECT NULLIF(yuwen_score, 0) -- 若語文成績?yōu)?, 則記為NULL;不為0,則取此成績
FROM student
NULLIF函數(shù)最大的目的是被用來防止除零錯誤
SELECT AVG(yuwen_score)/NULLIF(yuwen_score, 0) --若某同學語文成績?yōu)?,則分母為NULL(不是0),此時不會報錯
FROM student
IFNULL函數(shù)
MYSQL與SQLite才有,入?yún)⒅挥袃蓚€,功能是返回兩個入?yún)⒅械谝粋€非空的值(可視為入?yún)⒐潭閮蓚€的COALESCE函數(shù))。注意與NULLIF區(qū)分。
SELECT IFNULL(yuwen_score, 0) -- 若語文成績?yōu)镹ULL, 則記為0
FROM student