神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !

  • 时间:
  • 浏览:0
  • 来源:大发uu快3_uu快3平台客服_大发uu快3平台客服

前言

  开心一刻 

     三个白多多多多多中国小孩参加国外的脱口秀节目,或者语言不通,于是找了三个白多多多多多翻译。

    主持人问:“Who is your favorite singer ?”

    翻译:”你最喜欢哪个歌手啊 ?”

    小孩兴奋地回答:”Michael Jackson”

    翻译转身对主持人说:”迈克尔-杰克逊”

    主持人看着翻译:"你说哪此哪此 ?"

    电视机前的观众:"我为什么会么会一阵一阵蒙?" 

NULL

  NULL 用于表示缺失的值或遗漏的未知数据,总要这名具体类型的值。数据表中的 NULL 值表示该值存在的字段为空,值为 NULL 的字段这样值,尤其要明白的是:NULL 值与 0 或者空字符串是不同的。

  这名 NULL

    这名 说法朋友或者会着实很奇怪,或者 SQL 里只存在这名 NULL 。然而在讨论 NULL 时,朋友一般总要将它分成这名类型来思考:“未知”(unknown)和“不适用”(not applicable,inapplicable)。

    以“真不知道戴墨镜的人眼睛是哪此颜色”这名 清况 为例,这名 人的眼睛肯定是有颜色的,或者或者他不摘掉眼镜,别人就真不知道他的眼睛是哪此颜色。这就叫作未知。而“真不知道冰箱的眼睛是哪此颜色”则属于“不适用”。或者冰箱根本就这样眼睛,无需 “眼睛的颜色”这名 属性难能可贵适用于冰箱。“冰箱的眼睛的颜色”这名 说法和“圆的体积”“男性的分娩次数”一样,总要这样意义的。平时,朋友习惯了说“真不知道”,或者“真不知道”也分无需 种。“不适用”这名 清况 下的 NULL ,在语义上更接近于“无意义”,而总要“不选折 ”。这里总结一下:“未知”指的是“着实现在真不知道,但再加其他条件后就还时要知道”;而“不适用”指的是“无论为什么会么会努力都无法知道”。

    关系模型的伟大的伟大的发明者 E.F. Codd 最先给出了这名 分类。下图是他对“丢失的信息”的分类

  为哪此时要写成“IS NULL”,而总要“= NULL”

    我相信不少人三个白多多多多多多的困惑吧,尤其是相信刚学 SQL 的小伙伴。朋友来看个具体的案例,假设朋友有如下表以及数据

DROP TABLE IF EXISTS t_sample_null;
CREATE TABLE t_sample_null (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name VARCHAR(60

) NOT NULL COMMENT '名称',
    remark VARCHAR(60

0) COMMENT '备注',
    primary key(id)
) COMMENT 'NULL样例';

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

    朋友要查询备注为 NULL 的记录(为 NULL 这名 叫法这名是不对的,无需 朋友日常中或者叫习惯了,具体往下看),为什么会么会查,无需 新手会写出三个白多多多多多的 SQL

-- SQL 不报错,但查没哟结果
SELECT * FROM t_sample_null WHERE remark = NULL;

    执行时不报错,或者查没哟朋友我你要的结果, 这是为哪此了 ? 这名 难题图片朋友先放着,朋友往下看

三值逻辑

  这名 三值逻辑总要三目运算,指的是三个白多多多多多逻辑值,许多人或者有难题图片了,逻辑值总要不还都还都可不都可以真(true)和假(false)吗,哪来的第三个白多多多多多? 说这话时朋友时要注意存在的环境,在主流的编程语言中(C、JAVA、Python、JS等)中,逻辑值着实不还都还都可不都可以 2 个,但在 SQL 中却存在第三个白多多多多多逻辑值:unknown。这名阵一阵类似于 于朋友平时所说的:对、错、真不知道。

  逻辑值 unknown 和作为 NULL 的这名的 UNKNOWN (未知)是不同的东西。前者是明确的布尔型的逻辑值,后者既总要值也总要变量。为了便于区分,前者采用小写字母 unknown ,后者用大写字母 UNKNOWN 来表示。为了让朋友理解两者的不同,朋友来看三个白多多多多多 x=x 三个白多多多多多的简单等式。x 是逻辑值 unknown 时,x=x 被判断为 true ,而 x 是 UNKNOWN 时被判断为 unknown 

-- 这名

是明确的逻辑值的比较
unknown = unknown → true

-- 这名

大慨NULL = NULL
UNKNOWN = UNKNOWN → unknown

   三值逻辑的逻辑值表

    NOT

    AND

    OR

    图中浅紫色累积是三值逻辑中独有的运算,这在二值逻辑中是这样的。其余的 SQL 谓词完全都能由这三个白多多多多多逻辑运算组合而来。从这名 意义上讲,这名 好多个逻辑表还时要说是 SQL 的母体(matrix)。

    NOT 说说,或者逻辑值表比较简单,无需 很好记;或者对于 AND 和 OR,或者组合出来的逻辑值较多,无需 完全记住非常困难。为了便于记忆,请注意这三个白多多多多多逻辑值之间有下面三个白多多多多多的优先级顺序。

      AND 的清况 : false > unknown > true

      OR 的清况 : true > unknown > false

    优先级高的逻辑值会决定计算结果。类似于 true AND unknown ,或者 unknown 的优先级更高,无需 结果是 unknown 。而 true OR unknown 说说,或者 true 优先级更高,无需 结果是 true 。记住这名 顺序后就能更方便地进行三值逻辑运算了。一阵一阵时要记住的是,当 AND 运算暗含有 unknown 时,结果肯定无需是 true (反之,或者AND 运算结果为 true ,则参与运算的双方时要都为 true )。

-- 假设 a = 2, b = 5, c = NULL,下列表达式的逻辑值如下

a < b AND b > c  → unknown
a > b OR b < c   → unknown
a < b OR b < c   → true
NOT (b <> c)     → unknown

  “IS NULL” 而非 “= NULL”

    朋友再回到难题图片:为哪此时要写成“IS NULL”,而总要“= NULL”

    对 NULL 使用比较谓词后得到的结果老是 unknown 。而查询结果只会暗含 WHERE 子句里的判断结果为 true 的行,无需暗含判断结果为 false 和 unknown 的行。不无需 等号,对 NULL 使用其他比较谓词,结果也总要一样的。无需 无论 remark 是总要 NULL ,比较结果总要 unknown ,这样永远这样结果返回。以下的式子总要被判为 unknown

-- 以下的式子总要被判为 unknown
= NULL
> NULL
< NULL
<> NULL
NULL = NULL

    这样,为哪此对 NULL 使用比较谓词后得到的结果永远不或者为真呢?这是或者,NULL 既总要值也总要变量。NULL 无需 三个白多多多多多表示“这样值”的标记,而比较谓词只适用于值。或者,对难能可贵值的 NULL 使用比较谓词三个白多多多多多无需 这样意义的。“列的值为 NULL ”、“NULL 值” 三个白多多多多多的说法这名无需 错误的。或者 NULL总要值,无需 没哟定义域(domain)中。相反,或者许多人认为 NULL 是值,这样朋友还时要倒过来想一下:它是哪此类型的值?关系数据库中存在的值必然属于这名类型,比如字符型或数值型等。无需 ,我希望 NULL 是值,这样它就时要属于这名类型。

    NULL 容易被认为是值的愿因三个白多多多多多多。第三个白多多多多多是高级编程语言里边,NULL 被定义为了三个白多多多多多常量(无需 语言将其定义为了整数0),这愿因了朋友的混淆。或者,SQL 里的 NULL 和其他编程语言里的 NULL 是完全不同的东西。第五个愿因是,IS NULL 三个白多多多多多的谓词是由三个白多多多多多单词构成的,无需 朋友容易把 IS 当作谓词,而把 NULL 当作值。一阵一阵是 SQL 里还有 IS TRUE 、IS FALSE 三个白多多多多多的谓词,朋友由此类推,从而三个白多多多多多认为也总要这样道理。或者正如讲解标准 SQL 的书里提醒朋友注意的那样,朋友应该把 IS NULL 看作是三个白多多多多多谓词。或者,写成 IS_NULL 三个白多多多多多你说哪此更大慨。

温柔的陷阱

  比较谓词和 NULL

    排中律不成立

      排中律指同三个白多多多多多思维过程中,三个白多多多多多相互矛盾的思想不还都还都可不都可以同假,必有一真,即“要么A要么非A”

      假设朋友有学生表:t_student

DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name VARCHAR(60

) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(60

0) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

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

SELECT * FROM t_student;
View Code

      表中数据 yzb 的 age 是 NULL,就是 要 说 yzb 的年龄未知。在现实世界里,yzb 是 20 岁,或者总要 20 岁,二者必居其一,这毫无难题图片是三个白多多多多多真命题。这样在 SQL 的世界里了,排中律还适用吗? 朋友来看三个白多多多多多 SQL 

SELECT * FROM t_student
WHERE age = 20 OR age <> 20;

      咋一看,这不无需 查询表中完全记录吗? 朋友来看下实际结果

      yzb 没查出来,这是为哪此了?朋友来分析下,yzb 的 age 是 NULL,这样这条记录的判断步骤如下

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

-- 2. 对 NULL 使用比较谓词后,结果为unknown
SELECT *
FROM t_student
WHERE unknown
OR unknown;

-- 3.unknown OR unknown 的结果是unknown (参考三值逻辑的逻辑值表)
SELECT *
FROM t_student
WHERE unknown;

      SQL 说说的查询结果里不还都还都可不都可以判断结果为 true 的行。要想让 yzb 出现在结果里,时要再加下面三个白多多多多多的 “第 3 个条件”

-- 再加 3 个条件:年龄是20 岁,或者总要20 岁,或者年龄未知
SELECT * FROM t_student
WHERE age = 20 
    OR age <> 20
    OR age IS NULL;

    CASE 表达式和 NULL

      简单 CASE 表达式如下

CASE col_1
    WHEN = 1 THEN 'o'
    WHEN NULL THEN 'x'
END

      这名 CASE 表达式一定无需返回 ×。这是或者,第五个 WHEN 子句是 col_1 = NULL 的缩写形式。正如朋友所知,这名 式子的逻辑值永远是 unknown ,或者 CASE 表达式的判断依据与 WHERE 子句一样,只认可逻辑值为 true 的条件。正确的写法是像下面三个白多多多多多使用搜索 CASE 表达式

CASE WHEN col_1 = 1 THEN 'o'
    WHEN col_1 IS NULL THEN 'x'
END

  NOT IN 和 NOT EXISTS 总要等价的

    朋友在对 SQL 说说进行性能优化时,老是用到的三个白多多多多多技巧是将 IN 改写成 EXISTS ,这是等价改写,并这样哪此难题图片。或者,将 NOT IN 改写成 NOT EXISTS 时,结果难能可贵一样。

    朋友来看个例子,朋友有如下两张表:t_student_A 和 t_student_B,分别表示 A 班学生与 B 班学生 

DROP TABLE IF EXISTS t_student_A;
CREATE TABLE t_student_A (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name VARCHAR(60

) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(60

) NOT NULL COMMENT '城市',
    remark VARCHAR(60

0) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

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(60

) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(60

) NOT NULL COMMENT '城市',
    remark VARCHAR(60

0) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student_B(name, age, city)
VALUE
('马化腾', 45, '深圳市'),('马三', 25, '深圳市'),
('马云', 43, '杭州市'),('李彦宏', 41, '深圳市'),
('年轻人', 25, '深圳市');

SELECT * FROM t_student_A;
SELECT * FROM t_student_B;
View Code

    需求:查询与 A  班住在深圳的学生年龄不同的 B 班学生,就是 要 查询出 :马化腾 和 李彦宏,这名 SQL 该怎样才能写,像三个白多多多多多?

-- 查询与 A  班住在深圳的学生年龄不同的 B 班学生 ?
SELECT * FROM t_student_B
WHERE age NOT IN (
    SELECT age FROM t_student_A 
    WHERE city = '深圳市'
);

    朋友来看下执行结果

    朋友发现结果是空,查询不还都还都可不都可以任何数据,这是为哪此了 ?这里 NULL 又之前 刚开始英文作怪了,朋友一步一步来看看究竟存在了哪此

    还时要看出,在进行了一系列的转换后,这样两根绳子 记录在 WHERE 子句里被判断为 true 。就是 要 说,或者 NOT IN 子查询中用到的表里被选折 的列中存在 NULL ,则 SQL 说说整体的查询结果永远是空。这是很可怕的难题图片!

    为了得到正确的结果,朋友时要使用 EXISTS 谓词

-- 正确的SQL 说说:马化腾和李彦宏将被查询到
SELECT * FROM t_student_B B
WHERE NOT EXISTS ( 
    SELECT * FROM t_student_A A
    WHERE B.age = A.age
    AND A.city = '深圳市' 
);

    执行结果如下

    同样地,朋友再来一步一步地看看这段 SQL 是怎样才能处理年龄为 NULL 的行的

    就是 要 说,yzb 被作为 “与任何人的年龄总要同的人” 来处理了。EXISTS 只会返回 true 或者false,永远无需返回 unknown。或者总要了 IN 和 EXISTS 还时要互相替换使用,而 NOT IN和 NOT EXISTS 却不还时要互相替换的混乱难题图片。

  还有其他其他的陷阱,比如:限定谓词和 NULL、限定谓词和极值函数总要等价的、聚合函数和 NULL 等等。

总结

  1、NULL 用于表示缺失的值或遗漏的未知数据,总要这名具体类型的值,不还都还都可不都可以对其使用谓词

  2、对 NULL 使用谓词后的结果是 unknown,unknown 参与到逻辑运算时,SQL 的运行会和预想的不一样

  3、 IS NULL 整个是三个白多多多多多谓词,而总要:IS 是谓词,NULL 是值;类似于 的还有 IS TRUE、IS FALSE

  4、要想处理 NULL 带来的各种难题图片,最佳依据应该是往表里再加 NOT NULL 约束来尽力排除 NULL

    我的项目暗含个硬性规定:所有字段时就是 NOT NULL,建表的之前 就再加此约束

参考

  《SQL进阶教程》

navicat

  https://gitee.com/youzhibing/tools/blob/master/NavicatforMySQL.rar