MySQL案例 1.3.3 实践篇

作者: SQL开发者

几种因为null的存在而需要注意的地方

比较谓词与null: 排中律不存在

准备数据:

create table students(
    name text not null,
    age int
);
insert into students(name, age)
        values('布朗', 22),
            ('拉里', 19),
            ('约翰', null),
            ('伯杰', 21);
/*
 name | age
------+-----
 布朗 |  22
 拉里 |  19
 约翰 |

伯杰 | 21 */

**用sql语句查找所有的数据:**

select *

from students

where age = 21 or age <> 21; / name | age ——+—– 布朗 | 22 拉里 | 19 伯杰 | 21 /

😱我们发现并没有查到约翰;
因为null的存在,导致排中律在sql中并不存在
### 比较谓词与null: case表达式和null
使用下面的sql语句

select case col_1 when 1 then '√'

            when null then '×'

from …

会发现永远不可能出现 "×", 因为上面的sql等价于

select case when col_1 = 1 then '√'

        when col_1 = null then '×'

from …

   -->等价于<--

select case when col_1 = 1 then '√'

        when unknown then '×'

from …

所以永远不可能执行
### not in 和not exist不是等价的
 结论:
 在sql中in与exists是等价的, 但是not in与not exists不是等价的
#### 准备数据:

create table class_a(

name text not null,
age int not null,
city text not null

); insert into class_a(name, age, city)

        values('布朗', 22, '东京'),
               ('拉里', 19, '琦玉'),
                ('伯杰', 21, '千叶');

/ name | age | city ——+—–+—— 布朗 | 22 | 东京 拉里 | 19 | 琦玉 伯杰 | 21 | 千叶 / create table class_b(

name text not null,
age int,
city text not null

); insert into class_b(name, age, city)

        values('齐藤', 22, '东京'),
                ('田尻', 23, '东京'),
                ('山田', null, '东京'),
                ('和泉', 18, '千叶'),
                ('武田', 20, '千叶'),
                ('石川', 19, '神奈川');       

/ name | age | city ——+—–+——– 齐藤 | 22 | 东京 田尻 | 23 | 东京 山田 | | 东京 和泉 | 18 | 千叶 武田 | 20 | 千叶 石川 | 19 | 神奈川 /

### 背景问题:
从class_a中查找与class_b中住在东京但是年龄不同的学生
**使用谓词not in查找居然查不到!**

select *

from class_a

where age not in (select age

                from class_b
            where city = '东京');

/ name | age | city ——+—–+—— (0 rows) /

**使用not exists却可以查到:**

select *

from class_a

where not exists (select *

                from class_b
            where city = '东京'
                and class_a.age = class_b.age);

/ name | age | city ——+—–+—— 拉里 | 19 | 琦玉 伯杰 | 21 | 千叶 /

### 为何会存在上述问题:
 全都是null的锅
#### 上述not in等价于:

select *

from class_a

where age not in (select age

                from class_b
            where city = '东京');
     -->等价于<--

select *

from class_a

where age not in (22, 23, null);

     -->等价于<--

select *

from class_a

where not((age = 22)

    or (age = 23)
    or (age = null));
    -->等价于🤔(为啥)<--

select *

from class_a

where not(age = 22)

    and not(age = 23)
    and not(age = null);
    -->等价于<--

select *

from class_a

where ((age <> 22)

    and (age <> 23)
    and (age <> null));
    -->等价于<--

select *

from class_a

where (age <> 22)

    and (age <> 23)
    and unknown;
    -->等价于<--

select *

from class_a

where unknown; – 难怪最终得不到任何值😱

#### not exists处理null的方式:

select *

from class_a

where not exists (select *

                from class_b
            where city = '东京'
                and class_a.age = class_b.age);
    -->等价于<--

select *

from class_a

where not exists (select *

                from class_b
            where city = '东京'
                and class_a.age = null);
    -->等价于<--

select *

from class_a

where not exist (select *

                from class_b
            where city = '东京'
                and unknown);
    -->等价于<--

select *

from class_a

where not exist (select *

                from class_b
            where unknown);
    -->等价于<--

select *

from class_a

where not false;

    -->等价于<--

select *

from class_a

where true;

### 限定谓词和NULL
#### 限定谓词ANY与ALL作用
**any:**
 Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
 You must place an =, <>, <, >, <=, or >= operator before ANY.

SELECT * FROM employee – WHERE salary > ANY (2000, 3000, 4000); 这样写会报错 因为any后面要跟一个数组, 但是这个不是数组 WHERE salary > ANY values((2000), (3000), (4000));

**all:**
 Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
 You must place an =, <>, <, >, <=, or >= operator before ALL.

SELECT * FROM employee – WHERE salary > ALL (2000, 3000, 4000) 这样写会报错 因为any后面要跟一个数组, 但是这个不是数组 WHERE salary > ALL values((2000), (3000), (4000));

#### 限定谓词ANY与ALL vs IN:
* IN 等价于 = ANy
* NOT IN 等价于 <> ALL
#### ALL 与 NULL 引发的问题:
**背景问题:**
从class_a中找出所有年龄比class_b中在东京居住的小的学生

class_a name | age | city ——+—–+—— 布朗 | 22 | 东京 拉里 | 19 | 琦玉 伯杰 | 21 | 千叶 class_b name | age | city ——+—–+——– 齐藤 | 22 | 东京 田尻 | 23 | 东京 山田 | | 东京 和泉 | 18 | 千叶 武田 | 20 | 千叶 石川 | 19 | 神奈川

当使用如下sql语句:

select *

from class_a

where age < all(select age

                from class_b
            where city = '东京');

/ name | age | city ——+—–+—— (0 rows) /

**为啥啥都查不到?**
 null的锅!
因为all就是and的语法糖,
所以在all的处理null的时候等价于:

select from *** where true and false and unknown;

等价于

select from where unknown;

### 限定谓词与极致函数是不等价的
**极致函数(max或者min)会过滤掉null**

select *

from class_a

where age < all(select age

                from class_b
            where city = '东京');

/ name | age | city ——+—–+—— (0 rows) / select * from class_a where age < (select min(age) from class_b where city = '东京'); name | age | city / ——+—–+—— 拉里 | 19 | 琦玉 伯杰 | 21 | 千叶 (2 rows) /

**在查询不到任何列时, 极致函数max或min会返回null:**

select *

from class_a

where age < all (select age

                from class_b
            where city <> '东京'
                and city = '东京');

/ name | age | city ——+—–+—— 布朗 | 22 | 东京 拉里 | 19 | 琦玉 伯杰 | 21 | 千叶 (3 rows) / select *

from class_a

where age < (select max(age)

            from class_b
        where city <> '东京'
            and city = '东京');

/ name | age | city ——+—–+—— (0 rows) /

### 聚合函数与NULL
 聚合函数中除了count, 其他的输入空集都会返回null

select *

from class_a

where age < (select avg(age)

            from class_b
        where false);

/ name | age | city ——+—–+—— (0 rows) /

更多推荐

更多
  • MySQL实战宝典-27分布式事务:我们到底要不要使用2PC? 27 分布式事务:我们到底要不要使用 2PC?计。但是我们一直在回避分布式数据库中最令人头疼的问题,那就是分布式事务。 今天,我们就来学习分布式事务的概念,以及如何在海量互联网业务中实现它。 分布式事务概念 事务的概念相信你已经非
  • MySQL实战宝典-11索引出错:请理解CBO的工作原理 11 索引出错:请理解 CBO 的工作原理的使用已经有了一定的了解。 而在实际工作中,我也经常会遇到一些同学提出这样的问题:MySQL 并没有按照自己的预想来选择索引,比如创建了索引但是选择了全表扫描,这肯定是 MySQL 数据库的
  • MySQL实战宝典-21数据库备份:备份文件也要检查! 21 数据库备份:备份文件也要检查!据库可以进行切换(比如 20 讲基于复制或者 InnoDB Cluster 技术的高可用解决方案)。 除了高可用设计外,对架构师来说,还要做好备份架构的设计。因为我们要防范意外情况的发生,比如黑客删
  • MySQL实战宝典-15MySQL复制:最简单也最容易配置出错 15 MySQL 复制:最简单也最容易配置出错结构设计、索引设计。对业务开发的同学来说,掌握这些内容已经能很好地面向业务逻辑进行编码工作了。 但是业务需要上线,所以除了表和索引的结构设计之外,你还要做好高可用的设计。因为在真实的生产环
  • MySQL实战宝典-07表的访问设计:你该选择SQL还是NoSQL? 07 表的访问设计:你该选择 SQL 还是 NoSQL?的访问选型。这样一来,字段类型选择 + 物理存储设计 + 表的访问设计,就完成了表结构设计的所有内容。 前面 6 讲,我演示的都是通过 SQL 的方式对表进行访问,但从 MySQ
  • MySQL实战宝典-17高可用设计:你怎么活用三大架构方案? 17 高可用设计:你怎么活用三大架构方案?是为了铺垫 MySQL 数据库的高可用架构设计。因为复制是高可用的基础,但只用复制同步数据又远远不够,你还要结合自己的业务进行高可用设计。 同时,高可用也不仅仅是数据库的事情,你要从业务的全流
  • MySQL实战宝典-06表压缩:不仅仅是空间压缩 06 表压缩:不仅仅是空间压缩用户表、订单表。既然我们已经掌握了表的逻辑设计,那这一讲就继续学习不同业务表的物理存储设计。 据我观察,很多同学不会在表结构设计之初就考虑存储的设计,只有当业务发展到一定规模才会意识到问题的严重性。而物理
  • MySQL实战宝典-19高可用套件:选择这么多,你该如何选? 19 高可用套件:选择这么多,你该如何选?求,通过无损半同步复制的方式进行三园区的同城容灾设计,以及三地务中心的跨城容灾设计。 但是当数据库发生宕机时,MySQL 的主从复制并不会自动地切换,这需要高可用套件对数据库主从进行管理。
  • MySQL实战宝典-09索引组织表:万物皆索引 09 索引组织表:万物皆索引步深入了解 MySQL 的 B+ 树索引的具体使用,这一讲我想和你聊一聊 MySQL InnoDB 存储引擎的索引结构。 InnoDB 存储引擎是 MySQL 数据库中使用最为广泛的引擎,在海量大并发的 OL
  • MySQL实战宝典-12JOIN连接:到底能不能写JOIN? 12 JOIN 连接:到底能不能写 JOIN?优工作。但除了单表的 SQL 语句,还有两大类相对复杂的 SQL,多表 JOIN 和子查询语句,这就要在多张表上创建索引,难度相对提升不少。 而很多开发人员下意识地认为 JOIN 会降低
  • 近期文章

    更多
    文章目录

      推荐作者

      更多