如何查找一个为NULL的MYSQL字段

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

前言：在做这个题目 https://www.cnblogs.com/pipihao/p/13786304.html

因为之前 我好像没有接触过什么 为NULL字段的查询，细节不小

WHERE 字段 IS NULL

WHERE 字段 IS NOT NULL

# 这是数据查询的SQL
SELECT
s.*,(s1.`s_score`+s2.`s_score`+s3.`s_score`)/3 平均成绩
FROM
student s
LEFT JOIN score s1 ON s1.`s_id` = s.`s_id` AND s1.`c_id` = 01
LEFT JOIN score s2 ON s2.`s_id` = s.`s_id` AND s2.`c_id` = 02
LEFT JOIN score s3 ON s3.`s_id` = s.`s_id` AND s3.`c_id` = 03


# 这是我自己之前写的SQL
SELECT
s.*,(s1.`s_score`+s2.`s_score`+s3.`s_score`)/3 平均成绩
FROM
student s
LEFT JOIN score s1 ON s1.`s_id` = s.`s_id` AND s1.`c_id` = 01
LEFT JOIN score s2 ON s2.`s_id` = s.`s_id` AND s2.`c_id` = 02
LEFT JOIN score s3 ON s3.`s_id` = s.`s_id` AND s3.`c_id` = 03
WHERE (s1.`s_score`+s2.`s_score`+s3.`s_score`)/3 < 60


# 这是完善之后 的SQL 加了IS NULL 可以判断字段是否为NULL
SELECT
s.*,(s1.`s_score`+s2.`s_score`+s3.`s_score`)/3 平均成绩
FROM
student s
LEFT JOIN score s1 ON s1.`s_id` = s.`s_id` AND s1.`c_id` = 01
LEFT JOIN score s2 ON s2.`s_id` = s.`s_id` AND s2.`c_id` = 02
LEFT JOIN score s3 ON s3.`s_id` = s.`s_id` AND s3.`c_id` = 03
WHERE
(s1.`s_score`+s2.`s_score`+s3.`s_score`)/3 < 60
OR
(s1.`s_score`+s2.`s_score`+s3.`s_score`)/3 IS NULL