神奇的 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(400) NOT NULL COMMENT '名称',
    remark VARCHAR(4000) 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(400) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(4000) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

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

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