[Mysql] NULL过滤数据

练习案例数据

DROP TABLE IF EXISTS purchase_info; 
CREATE TABLE purchase_info( 
commodity_id VARCHAR(8), 
category VARCHAR(16), 
colour VARCHAR(16),
purchase_quantity INT, 
purchase_date DATE 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
purchase_info 
(commodity_id,category,colour,purchase_quantity,purchase_date) 
VALUE ('c001','clothing','black',150,'2020-04-25') 
,('c002','clothing','white',50,'2020-04-05') 
,('c003','shoes','white',500,'2020-03-23') 
,('c004','shoes','red',200,'2020-04-07') 
,('c005','clothing','blue',120,'2020-04-15')
,('c007','clothing',NULL,NULL,'2020-04-15');

purchase_info表(商品进货信息表)

在数据表中，如果某个字段的某个值为空白，则在SQL中以NULL代替，它与字段包含0、空字符串或仅仅包含空格不同

语法格式

-- IS NULL
SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;
 
-- IS NOT NULL
SELECT column_name(s)
FROM table_name
WHERE column_name IS NOT NULL;

问题1:筛选出colour字段为NULL的数据

SELECT *
FROM purchase_info
WHERE colour = NULL;

结果展示:

查询结果为空

由于NULL的特殊性，无法使用等号=来定位，如果想要查询NULL的记录，则需要使用IS关键字，代码如下:

SELECT *
FROM purchase_info
WHERE colour IS NULL;

结果展示:

 问题2:查询不包含colour字段为NULL的记录

SELECT *
FROM purchase_info
WHERE colour IS NOT NULL;

结果展示:

NULL过滤数据易错点 

在默认情况下，MySQL排序显示NULL最小

问题3:对包含NULL的purchase_quantity字段进行升序排序

SELECT *
FROM purchase_info
ORDER BY purchase_quantity ASC;

结果展示:

问题扩展:如何将NULL值排在最后?

添加负号

SELECT *
FROM purchase_info
ORDER BY -purchase_quantity DESC;

结果展示:

问题4:对包含NULL的purchase_quantity字段进行降序排序

SELECT *
FROM purchase_info
ORDER BY purchase_quantity DESC;

结果展示: