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

  • 时间:
  • 浏览:0
  • 来源:大发快3_快3官方网址_大发快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(200) NOT NULL COMMENT '名称',
    remark VARCHAR(2000) 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 谓词全部都能由这另八个 多逻辑运算组合而来。从四种 意义上讲,四种 2个逻辑表前会 说是 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(200) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(2000) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

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

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