软件测试|SQL中的null值，该如何理解？

深入理解SQL中的Null值：处理缺失数据的重要概念

简介

Null值在SQL中是用于表示缺失或未知数据的特殊值。本文将深入探讨Null值的概念、处理方法和注意事项，以帮助读者更好地理解和处理SQL中的缺失数据。

在SQL数据库中，Null值是一种特殊的值，用于表示缺失或未知的数据。它与其他具体的数值、字符串或日期不同，Null值表示该字段在特定记录中没有有效值。下面我们将深入探讨Null值的重要性、处理方法和注意事项。

Null值的重要性：

Null值在数据库中具有重要的作用。它能够表示缺失数据、未知数据或未适用的数据情况。Null值使数据库能够处理现实世界中的不完整信息，避免在未知情况下做出错误的假设。同时，Null值也可以帮助区分空字符串、零值和未定义值之间的差异。

处理方法：

• 查询和比较：在SQL查询中，Null值的处理需要特别注意。Null值不能通过常规的比较运算符（如等于、大于或小于）进行比较。相反，应使用特殊的运算符（如IS NULL和IS NOT NULL）来检查字段是否为Null值。

• 函数和运算：在处理包含Null值的数据时，SQL提供了一些函数和运算符来处理Null值。例如，COALESCE函数可以用于返回一系列值中的第一个非Null值，NVL函数可以在Null值的情况下提供默认值。此外，使用ISNULL、IFNULL和NULLIF等函数也能对Null值进行处理。

• 聚合函数：在使用聚合函数（如SUM、AVG、COUNT等）进行计算时，Null值的处理需要特别注意。聚合函数通常会忽略Null值，因此在对包含Null值的数据进行计算时，需要考虑Null值的影响，并选择合适的处理方式。

Null值的注意事项：

• 索引和唯一约束：在设计数据库表时，应谨慎使用包含Null值的字段作为索引或唯一约束。因为Null值不会被索引或唯一约束考虑，可能导致查询性能下降或数据冗余。

• 外键约束：在使用外键约束时，需要注意与Null值相关的引用完整性。当父表中的主键值为Null时，应确保在子表中可以处理或限制Null值的引用。

• 数据逻辑和业务需求：Null值的处理需要根据具体的数据逻辑和业务需求进行决策。有时，Null值可能是有效的数据状态，而在其他情况下，它可能表示错误或缺失数据。因此，在设计数据库模型和编写查询时，应根据具体场景合理处理Null值。

语法

1. 在创建数据表时可以指定某个字段是否允许为 NULL，基本语法如下：

CREATE TABLE Customers (
    id      INT              NOT NULL   AUTO_INCREMENT,
    name    VARCHAR(20)      NOT NULL,
    age     TINYINT UNSIGNED NOT NULL,
    city    INT UNSIGNED     NOT NULL,
    Consumption amount      FLOAT                       DEFAULT '0',
    country CHAR(3)          NOT NULL   DEFAULT '',
    email   VARCHAR(30),
    PRIMARY KEY (`id`)
);

NOT NULL 关键字表示不允许该字段为空值，在插入或者更新记录时必须为该字段指定一个具体的值。Consumption amount和email字段没有使用 NOT NULL 关键字，这意味着它们保持默认，也即允许为 NULL。注意，Consumption amount字段指定了默认值 0，如果不为该字段提供值，它的值将是 0，email 字段没有指定默认值，如果不为该字段提供值，它的值将是 NULL。

2. 在选取数据时，NULL 值可能会导致一些问题，因为 NULL 值和其它任何值比较的结果都是未知的，所以包含 NULL 值的记录始终不能被筛选。我们不能使用 =、<、> 等比较运算符来检测 NULL 值，而必须使用 IS NULL 或者 IS NOT NULL 关键字来检测 NULL 值。

现在有一个包含如下记录的 Customers 表：

+----------+----------------+---------+-------------+---------+-----------+----------------+
|CustomerId|CustomerName    | Country |   City      |   Age   |Consumption|email           |
|00001     |Muller Cheng    | China   | Shanghai    |   25    |2000       |cheng@qq.com    |
|00002     |Kevin Durant    | USA     | Phoenix     |   34    |3000       |kevin@qq.com    |
|00003     |Libin Tian      | China   | Kaifeng     |   31    |0          |tian@163.com    |
|00004     |Junye  Li       | China   | Guangzhou   |   32    |1500       |junye@qq.com    |
|00005     |Leborn James    | USA     | LosAngles   |   39    |4000       |null            |
|00006     |Stephen Curry   | USA     | SanFrancisco|   35    |3500       |curry@qq.com    |
|00007     |Humphrey Appleby| UK      | London      |   56    |3000       |humpy@qq.com    |
|00008     |Jim Hacker      | UK      | London      |   60    |0          |null            |
|00009     |Thomas Muller   | Germany | Munich      |   34    |2500       |muller@gmail.com|
|00010     |Miro Klose      | Germany | Berlin      |   45    |1500       |null            |

下面是 IS NOT NULL 关键字的用法：

SELECT  CustomerId, CustomerName, Country
FROM Customers
WHERE email IS NOT NULL;

查询结果如下：

+----------+----------------+---------+
|CustomerId|CustomerName    | Country |  
|00001     |Muller Cheng    | China   | 
|00002     |Kevin Durant    | USA     | 
|00003     |Libin Tian      | China   | 
|00004     |Junye  Li       | China   | 
|00006     |Stephen Curry   | USA     | 
|00007     |Humphrey Appleby| UK      | 
|00009     |Thomas Muller   | Germany |

下面是 IS NULL 关键字的用法：

SELECT  CustomerId, CustomerName, Country
FROM Customers
WHERE email IS  NULL;

查询结果如下：

+----------+----------------+---------+
|CustomerId|CustomerName    | Country |   
|00005     |Leborn James    | USA     | 
|00008     |Jim Hacker      | UK      | 
|00010     |Miro Klose      | Germany | 

总结

Null值在SQL数据库中是处理缺失或未知数据的重要概念。通过理解Null值的概念、处理方法和注意事项，可以更好地处理包含Null值的数据，确保数据分析和查询的准确性和一致性。在使用Null值时，我们应该谨慎处理，合理运用相关函数和运算符，以确保数据处理的准确性和可靠性。同时，我们还应根据具体的数据逻辑和业务需求，选择合适的Null值处理策略，以确保数据库中的数据质量和完整性。