长治县网站建设,杭州手机网站,微网站和h5有什么区别,网站设计实验目的一、新逻辑状态#xff1a;未知
对于大多数其他语言的逻辑判断#xff0c;一般只有两种结果#xff1a;真(TURE)或假(FALSE)但在SQL中#xff0c;还会有第三种判断结果#xff1a;未知(UNKNOWN)#xff0c;表示无法判断出真或者假。
未知状态会影响传统逻辑运算#x…一、新逻辑状态未知
对于大多数其他语言的逻辑判断一般只有两种结果真(TURE)或假(FALSE)但在SQL中还会有第三种判断结果未知(UNKNOWN)表示无法判断出真或者假。
未知状态会影响传统逻辑运算与或非等的结果总结如下
1. 逻辑与操作
AND/真假未知真真假假假未知未知假未知
特别注意
对于AND运算符只有当两边的运算结果都为真时最终结果才为真真 AND 未知 未知 并不是真
2. 逻辑或操作
OR真假未知真真假真假未知真未知未知
对于OR运算符只要两边的运算有一个为真时最终结果就为真否则最终结果为假或者未知
3. 逻辑非操作
NOT运算结果真假假真未知未知
二、SQL中未知状态的判断影响
SQL语句中的WHERE、HAVING、CASE表达式只返回逻辑运算结果为真的数据不返回为假或者未知的数据
三、NULL空值
在数据库中空值NULL是一个特殊值表示缺失或者未知 在SQL语句中任何数据与空值进行算术比较的结果是未知而非真或非假.。所以空值NULL无法通过 “WHERE c1 NULL” 来判断需要写成 WHERE c1 IS NULL 才能作为条件筛选出查询字段为NULL的数据
1. NULL判断的特殊性
即使两个未知数据进行比较运算结果也是未知的比如下面的比较都得不出TRUE而是未知。以下例子则判断为未知 NULL 0 NULL ! 0 NULL ‘’ (空字符串) NULL !‘’ NULL NULL NULL !NULL 因此在WHERE语句中进行判断时务必要注意查询列或者查询条件即等号两边的数据都有没有可能为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运算符为判断所给条件是否在某个集合中。内部实际使用等值运算符来判断是否和集合中的每个元素相等再用OR串联起来得到最后的逻辑结果。
SELECT *
FROM student
WHERE name IN(LiLei, HanMeimei) -- 等同于 WHERE name LiLei OR name HanMeimei因此如果想通过IN() 运算符中加NULL元素来将被查字段中的NULL值也筛选出来实际是无法生效的。比如想实现以下SQL筛选出学生姓名为NULL的不会有效
-- 无法筛选出name为NULL的记录
SELECT *
FROM student
WHERE name IN(LiLei, HanMeimei, NULL)
/* 等同于 WHERE name LiLei OR name HanMeimei or name NULL 对于真正name为NULL的数据此表达式最终的结果为未知不会被筛选出来 */在NOT IN() 中使用NULL影响会更大使得判断无法筛选出任何记录
-- 无法筛选出任何记录
SELECT *
FROM student
WHERE name NOT IN(LiLei, HanMeimei, NULL)
/* 因为原句等同于 WHERE name ! LiLei and name ! HanMeimei and name ! NULL. 任何值在最后一句中的判断结果都会为UNKOWN使得整个判断变为未知被过滤掉 */3. NULL对子查询语句中ALL()/ANY() 运算符的影响
子查询中可以通过比较运算符(、!、、、、)与ALL、ANY的组合来表示等于、不等于、大于…集合中的全部数据
SELECT *
FROM student
WHERE class ANY ( -- 查找属于1年级的学生SELECT class FROM teacherWHERE grade 1
)ALL运算符相当于对其中每个选项进行比较运算符计算并用AND运算符串联 IN运算符相当于对其中每个选项进行运算符计算并用OR运算符串联 ANY与IN类似也是由OR运算符串联比较运算符写于ANY之前如果是ANY则与IN相同 对于ALL、ANY等运算符后面加上NULL不会成功筛选出想要的NULL数据相反会导致比较离谱的运算结果
总结
使用IN/NOT IN/ALL/ANY时切记不要在选项中设置NULL。对于子查询做以上匹配结果时也要注意务必过滤下NULL数据
4. 空值处理
COALESCE函数
COALESCE(exp1, exp2, exp3, …)接收一个输入列表返回第一个非NULL的参数若都为空则返回NULL
SELECT COALESCE(yuwen_score, shuxue_score, yingyu_score)
FROM student可以用COALESCE将NULL转换为别的默认值类似于CASE WHEN
SELECT COALESCE(yuwen_score, 0) -- 若语文成绩为NULL 则记为0分
FROM studentNULLIF函数
NULLIF(exp1, exp2)接收两个入参若相等则返回NULL若不等则返回exp1
SELECT NULLIF(yuwen_score, 0) -- 若语文成绩为0 则记为NULL不为0则取此成绩
FROM studentNULLIF函数最大的目的是被用来防止除零错误
SELECT AVG(yuwen_score)/NULLIF(yuwen_score, 0) --若某同学语文成绩为0则分母为NULL不是0此时不会报错
FROM studentIFNULL函数
MYSQL与SQLite才有入参只有两个功能是返回两个入参中第一个非空的值可视为入参固定为两个的COALESCE函数。注意与NULLIF区分。
SELECT IFNULL(yuwen_score, 0) -- 若语文成绩为NULL 则记为0
FROM student
