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

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

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

猜你喜欢

众泰人才网站CMS系统|众泰人才网站CMS系统 v8.04下载

---众泰人才网V8.04介绍---1)修正后台中管理去除了无意义的“最近访问日期”2)当时人简历管理去除了无意义的“简历激活”“撤回激活”完整性改为前台注册会员后即可自动激活

2020-02-19

苹果CEO库克向慈善组织捐赠近500万美元股票

北京时间8月22日上午消息,美国证券交易委员会的文件显示,iPhone6手机手机CEO蒂姆·库克(TimCook)本周向一家未具名的慈善组织捐赠23215股iPhone6手机手

2020-02-19

支持802.11n 华为AR1200

【IT168北京行情】AR1100系列企业路由器是华为面向中小企业推出的多合一路由器,提供包括有线和无线的Internet接入、专线接入,PBX、融合通信及安全等功能,AR11

2020-02-19

Apple Watch蜂窝版即将支持中国电信

苹果6手机手机苹果6手机手机苹果6手机手机在AppleWatchSeries3上结速英文提供蜂窝网络版本,不过中国内地地区对于AppleWatcheSIM卡服务的支持却似乎两个

2020-02-19

特拉斯卡拉VSPD坎昆免费视频直播,特拉斯卡拉VSPD坎昆比赛集锦,特拉斯卡拉VSPD坎昆录像,特拉斯卡拉VSPD坎昆首发阵容

首页新闻视频直播数据APP懂球号直播君广告合作者者特拉斯卡拉02-2401:00墨乙1-2已开始英语 英文PD坎昆直播君|分析|集锦暂无数据近期比赛阿森纳英超4-0纽卡斯尔联

2020-02-19