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

  • 时间:
  • 浏览:0
  • 来源:大发快三_快三最新版_大发快三最新版

前言

  开心一刻 

     有有有四个多多中国小孩参加国外的脱口秀节目,不可能 语言不通,于是找了有有有四个多多翻译。

    主持人问:“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(500) NOT NULL COMMENT '名称',
    remark VARCHAR(5000) 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(500) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(5000) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student(name, age)
VALUE('zhangsan', 25),('wangwu', 500),('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(500) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(500) NOT NULL COMMENT '城市',
    remark VARCHAR(5000) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student_A(name, age, city)
VALUE
('zhangsan', 25,'深圳市'),('wangwu', 500, '广州市'),
('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(500) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(500) NOT NULL COMMENT '城市',
    remark VARCHAR(5000) 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