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

  • 时间:
  • 浏览:0
  • 来源:1分时时彩官网_1分时时彩正规平台_1分时时彩平台网址

前言

  开心一刻 

     没办法 中国小孩参加国外的脱口秀节目,意味着语言不通,于是找了没办法 翻译。

    主持人问:“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