mysql 对null 的三种处理方式

最新推荐文章于 2024-07-10 18:10:01 发布
最新推荐文章于 2024-07-10 18:10:01 发布
阅读量5.2k 收藏 3
点赞数 2
分类专栏： mysql 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38807606/article/details/123924177
版权 



select * from girl ;

# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |1     |
# |3 |C   |3     |
# |4 |C   |3     |
# |5 |C   |3     |
# |6 |D   |NULL  |
# |7 |D   |NULL  |
# |8 |C   |2     |
# +--+----+------+


# null 不参与比较的
select * from girl where boy_id !=3   ;

# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |1     |
# |8 |C   |2     |
# +--+----+------+


select * from girl where boy_id !=3 or  boy_id is null   ;


+--+----+------+
|id|name|boy_id|
+--+----+------+
|1 |A   |1     |
|2 |B   |1     |
|6 |D   |NULL  |
|7 |D   |NULL  |
|8 |C   |2     |
+--+----+------+


# 对 null 常用的处理方法
# 方法1：coalesce(A,B),如果A为空则返回B,不为空则返回A
# 方法2：IF(A IS NULL ,B,C)如果A为空则返回B，不为空则返回C
# 方法3： case when then else end
#
#  考察点:
#  count(*) 是统计 总计（可能是 count（主键））, count(score) 不统计  score 为 null 的数据  count(score)/ count(*) 就可以得出 需要的值
#  原因： count(score)  的时候 加了个过滤条件 ： select count（主键）   from A where   score is  not null ;
#   count(*) 是统计 总计（可能是 count（主键）） ，  count(score) 不统计  score 为 null 的数据

# format(小数,小数点位数) 来精确小数点位数  ，等同于  round(小数，小数点位数)



#
select score from exam_record;
# +-----+
# |score|
# +-----+
# |80   |
# |NULL |
# |87   |
# |20   |
# |89   |
# |NULL |
# |90   |
# +-----+

select
   count(*)
FROM
    exam_record ;

# +--------+
# |count(*)|
# +--------+
# |7       |
# +--------+

select
   count(score)
FROM
    exam_record ;

# +------------+
# |count(score)|
# +------------+
# |5           |
# +------------+


# avg() 总数会不会算入 null 的 ,不会算入 的 ： avg(score) = sum (score)/count(score) 而不是： sum (score)/count(*)
select
   avg(score)
FROM
    exam_record ;

# +----------+
# |avg(score)|
# +----------+
# |73.2000   |
# +----------+


select

   sum(score)/count(*)
FROM
    exam_record ;

# +-------------------+
# |sum(score)/count(*)|
# +-------------------+
# |52.2857            |
# +-------------------+



select
   sum(score)/count(score)
FROM
    exam_record ;

# +-----------------------+
# |sum(score)/count(score)|
# +-----------------------+
# |73.2000                |
# +-----------------------+




drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-09-02 12:01:01', null, null);

select * from exam_record;

# +--+----+-------+-------------------+-------------------+-----+
# |id|uid |exam_id|start_time         |submit_time        |score|
# +--+----+-------+-------------------+-------------------+-----+
# |1 |1001|9001   |2020-01-02 09:01:01|2020-01-02 09:21:01|80   |
# |2 |1001|9001   |2021-05-02 10:01:01|2021-05-02 10:30:01|81   |
# |3 |1001|9001   |2021-09-02 12:01:01|NULL               |NULL |
# +--+----+-------+-------------------+-------------------+-----+

# 请统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate。由示例数据结果输出如下：
# exam_id	incomplete_cnt	complete_rate
# 9001	       1	             0.333


#
# 思路① 筛选出 为 null 的数据 , 然后分组 ，求完成率 (SELECT FORMAT(100.7654,3)  函数将 100.7654 保留为 三位数  )
select exam_id,
       count(*)                                                                                    incomplete_cnt,
       FORMAT(count(*) / (select count(1) from exam_record er0 where er0.exam_id = er.exam_id), 3) complete_rate
from exam_record er
where start_time is not null
  and submit_time is null
group by exam_id order by exam_id;


# 思路② sum 求和 ，但是 如果 不是对表里某个字段处理，也可以用来当   count 处理

select exam_id,
       sum(if(score is null, 1, 0)) as incomplete_cnt,
       round(sum(if(score is null, 1, 0)) / count(start_time), 3) as incomplete_rate
from exam_record
group by exam_id
having incomplete_cnt >= 1 ;

# +-------+--------------+---------------+
# |exam_id|incomplete_cnt|incomplete_rate|
# +-------+--------------+---------------+
# |9001   |1             |0.333          |
# +-------+--------------+---------------+

# 思路③  总数-已完成的=未完成
#       count(*)-count(score)=未完成

select
    exam_id,
    count(*) - count(score) incomplete_cnt,
    round((count(*) - count(score)) / COUNT(*),3) incomplete_rate
FROM
    exam_record
WHERE
         exam_id IN(
        select
            exam_id
        from
            exam_record
        where
            score is  null
    )
GROUP BY
    exam_id ;

#   count(*) 是统计 总计（可能是 count（主键）） ，  count(score) 不统计  score 为 null 的数据
select
   count(*)
FROM
    exam_record ;

# +--------+
# |count(*)|
# +--------+
# |3       |
# +--------+

select
   count(score)
FROM
    exam_record ;

# +------------+
# |count(score)|
# +------------+
# |2           |
# +------------+




#  满级人类

drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 10, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-01-01 10:00:00'),
  (9004, '算法', 'medium', 80, '2020-01-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90);


select  * from  examination_info ;

# +--+-------+---+----------+--------+-------------------+
# |id|exam_id|tag|difficulty|duration|release_time       |
# +--+-------+---+----------+--------+-------------------+
# |1 |9001   |SQL|hard      |60      |2020-01-01 10:00:00|
# |2 |9002   |SQL|easy      |60      |2020-01-01 10:00:00|
# |3 |9004   |算法 |medium    |80      |2020-01-01 10:00:00|
# +--+-------+---+----------+--------+-------------------+

select  * from   user_info  ;

# +--+----+---------+-----------+-----+---+-------------------+
# |id|uid |nick_name|achievement|level|job|register_time      |
# +--+----+---------+-----------+-----+---+-------------------+
# |1 |1001|牛客1号     |10         |0    |算法 |2020-01-01 10:00:00|
# |2 |1002|牛客2号     |2100       |6    |算法 |2020-01-01 10:00:00|
# +--+----+---------+-----------+-----+---+-------------------+

select  * from  exam_record ;

# +--+----+-------+-------------------+-------------------+-----+
# |id|uid |exam_id|start_time         |submit_time        |score|
# +--+----+-------+-------------------+-------------------+-----+
# |1 |1001|9001   |2020-01-02 09:01:01|2020-01-02 09:21:59|80   |
# |2 |1001|9001   |2021-05-02 10:01:01|NULL               |NULL |
# |3 |1001|9002   |2021-02-02 19:01:01|2021-02-02 19:30:01|87   |
# |4 |1001|9001   |2021-06-02 19:01:01|2021-06-02 19:32:00|20   |
# |5 |1001|9002   |2021-09-05 19:01:01|2021-09-05 19:40:01|89   |
# |6 |1001|9002   |2021-09-01 12:01:01|NULL               |NULL |
# |7 |1002|9002   |2021-05-05 18:01:01|2021-05-05 18:59:02|90   |
# +--+----+-------+-------------------+-------------------+-----+


# 需求：出每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理

# 思路① 先筛选出 0级别 用户的数据 ；然后 group by 这些 零级用户 ；最后平均用时和平均得分(null 的话 用 要求的数据填充)

#
# select TIMESTAMPDIFF(MINUTE,'2008-08-01 22:00:00','2008-08-01 23:01:00') ;
#  TIMESTAMPDIFF(MINUTE,start_time,submit_time)

#  sum(TIMESTAMPDIFF(MINUTE, start_time,
#                          if(submit_time is null, select duration from examination_info where examination_info.,
#                             submit_time))) avg_score,
#        sum(score)                          avg_time_took
select er.uid,
       start_time ,
       submit_time,
       ( select duration from examination_info where examination_info.exam_id = ei.exam_id ) duration ,
       if (score is null ,0,score)

from user_info ui
         inner join exam_record er on ui.uid = er.uid and ui.level = 0
         inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard'
group by er.uid;

#  填充 null
select uid, start_time,if(submit_time is null, duration, submit_time) , score
from (select er.uid,
             start_time,
             submit_time,
             (select duration from examination_info where examination_info.exam_id = ei.exam_id) duration,
             if(score is null, 0, score)                                                         score

      from user_info ui
               inner join exam_record er on ui.uid = er.uid and ui.level = 0
               inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard') tem ;

# +----+-------------------+----------------------------------------------+-----+
# |uid |start_time         |if(submit_time is null, duration, submit_time)|score|
# +----+-------------------+----------------------------------------------+-----+
# |1001|2020-01-02 09:01:01|2020-01-02 09:21:59                           |80   |
# |1001|2021-05-02 10:01:01|60                                            |0    |
# |1001|2021-06-02 19:01:01|2021-06-02 19:32:00                           |20   |
# +----+-------------------+----------------------------------------------+-----+


select uid, start_time, if(submit_time is null,duration,TIMESTAMPDIFF(MINUTE,start_time,submit_time))   , score
from (select er.uid,
             start_time,
             submit_time,
             (select duration from examination_info where examination_info.exam_id = ei.exam_id) duration,
             if(score is null, 0, score)                                                         score

      from user_info ui
               inner join exam_record er on ui.uid = er.uid and ui.level = 0
               inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard') tem ;

# +----+-------------------+-----------------------------------------------------------------------------+-----+
# |uid |start_time         |if(submit_time is null,duration,TIMESTAMPDIFF(MINUTE,start_time,submit_time))|score|
# +----+-------------------+-----------------------------------------------------------------------------+-----+
# |1001|2020-01-02 09:01:01|20                                                                           |80   |
# |1001|2021-05-02 10:01:01|60                                                                           |0    |
# |1001|2021-06-02 19:01:01|30                                                                           |20   |
# +----+-------------------+-----------------------------------------------------------------------------+-----+


#  分组 求平均值 avg_score	avg_time_took
select tem1.uid  ,format(avg(score),0)  ,format(avg(duration),1) from (select uid, start_time, if(submit_time is null,duration,TIMESTAMPDIFF(MINUTE,start_time,submit_time)) duration  , score
from (select er.uid,
             start_time,
             submit_time,
             (select duration from examination_info where examination_info.exam_id = ei.exam_id) duration,
             if(score is null, 0, score)                                                         score

      from user_info ui
               inner join exam_record er on ui.uid = er.uid and ui.level = 0
               inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard') tem) tem1
group by tem1.uid ;



# 其他思路： coalesce(A,B),如果A不为空返回A，否则返回B  （英[ˌkəʊəˈles] 美[ˌkoʊəˈles]）
SELECT uid,
       ROUND(AVG(score_new), 0) avg_score,
       ROUND(AVG(cost_time), 1) avg_time_took
FROM (SELECT a.uid,
             start_time,
             COALESCE(score, 0)                                                                  score_new, #方法1：coalesce(A,B),如果A为空则返回B,不为空则返回A
             IF(submit_time IS NULL, b.duration,
                TIMESTAMPDIFF(minute, start_time, submit_time))                                  cost_time  #IF(A IS NULL ,B,C)如果A为空则返回B，不为空则返回C
      FROM exam_record a
               LEFT JOIN examination_info b ON a.exam_id = b.exam_id
               LEFT JOIN user_info c ON a.uid = c.uid
      WHERE difficulty = 'hard'
        AND level = '0') t1

GROUP BY uid;

guoyiguang2
关注
专栏目录
MySQL NULL 值处理
lsx202406的博客
07-03 391
在MySQL中，NULL值代表一个未知的或不确定的值。与空字符串或0不同，NULL表示数据缺失或未知。在数据库表中，一个字段被设置为允许NULL值时，该字段可以不包含任何数据。
mysql中NULL值处理
一名小测试
07-13 496
接下来。装逼开始.... NULL不是0，是不能进行比较的 NULL也可以理解是一个未知数的占位符 mysql> select * from test_class; +----------+--------------+ | class_id | class_name | +----------+--------------+ | 1 | 初一(1)班 | | 2 | 初一(2)班 | | 3 | 初一(3)班 | | ...
mysql 对null的处理
zhy_yz的专栏
06-02 963
首先null并不是空，而表示‘没有值’或‘未知值’，null不属于任何类型 1.当创建一个表，字段约束没有指定为not null时，没有添加数据时默认为null，不管是整型还是字符类型； 如.create table test(arg1 varchar(30),arg2 int); select * from test；   2.当创建一个表，而字段约束是not null，当该字段没有添加值时，整型字段默认为0，字符型字段默认为空字符。 在sql语句中要注意这两点。 select * fro
mysql select count返回null
最新发布
wangwenzhe的博客
07-10 1024
mysql select count返回null。
Mysql的null值处理
OceanSky的专栏
06-12 571
在Mysql中，null用于标识缺失的或未知的数据，处理null值需要特别小心，因为在数据库中塔可能会导致不同的结果。
MySQL中NULL对索引的影响深入讲解
09-09
MySQL实际上允许在含有`NULL`值的列上使用索引，不过使用方式和效率可能会有所不同。 首先，让我们澄清一个误区：含有`NULL`值的索引并非完全无效。根据官方文档...
详解MySQL中的NULL值
12-15
在MySQL中，正确的处理方式是使用`IS NULL`或`IS NOT NULL`这两个专门用于检查`NULL`值的运算符。 1. `IS NULL`运算符：当你想要找出某个列的值为`NULL`的所有记录时，可以使用`IS NULL`。例如，在上面的`tcount_...
MySQL NULL 值处理实例详解
01-19
MySQL NULL 值处理 我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。 为了处理这种情况，MySQL提供了三大运算符: IS ...
mysql 处理 null值_MySQL NULL 值处理
weixin_42469578的博客
02-27 117
$dbhost = ‘localhost:3306‘; //mysql服务器主机地址$dbuser = ‘root‘; //mysql用户名$dbpass = ‘123456‘; //mysql用户名密码$conn = mysqli_connect($dbhost, $dbuser, $dbpass);if(! $conn){die(‘连接失败: ‘ . ...
MySQL对NULL值处理
云原生Java Web领域技术博客
08-31 802
在MySQL中，引入NULL值表示一个未知值，以表示不可知、不确定。尽管提供了NULL值，但是在日常开发中，不建议使用NULL值，建议为其设置默认值，如空字符串或 0 等。如果必须使用NULL，需要明确其影响，避免引入业务异常。
Mysql中关于NULL值的处理
FLY的博客
09-20 2938
mysql null select count(*) innodb_stats_method
MySQL查询对NULL的处理
u014702593的专栏
06-08 399
有一个字段blist，如果查询where blist 回答：Null 值不能使用普通的算术运算符来比较，对这些它什么都不返回。 只能靠你自己的逻辑流程，在查询语句中再添加where blist 在SQL中，NULL值在于任何其他值甚至NULL值比较时总是假的（FALSE）。包含NULL的一个表达式总是产生一个NULL值，除非在包含在表达式中的运算符和函数的文档中指出。在下列例子，所有的列返回
在MySQL中如何处理NULL值？
长风破浪会有时的博客
03-27 558
处理NULL值的方法有很多，选择哪一种取决于具体的需求和场景。重要的是要理解NULL值的含义，并知道如何在查询中正确地处理它们。
mysql的null值处理
qq_27127363的博客
03-14 276
mysql的null = null是false，所以他是用 is null 或者 is not null <=>左右两个值为空的时候返回true。
mysql中null的处理经验总结
songshuguowang的专栏
12-26 574
在项目中需要比对两条数据，筛选出有字段不一致的数据 select * from t_table t1, t_table t2 where t1.key = t2.key and t1.column1 != t2.column1 其中column1是数值型且可为空，当t1中column1有值但t2中column1值为null，以上查询是不能查询出结果的 因为mysql中null不...
mysql中null的处理_mysql中null值的处理
weixin_35809056的博客
01-20 478
来源于菜鸟教程我们已经知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。为了处理这种情况，MySQL提供了三大运算符:IS NULL: 当列的值是 NULL,此运算符返回 true。IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。<=>:比较操作符(不...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值