MYSQL之随机数生成、保留小数位、获取年龄 |
您所在的位置：网站首页 › random保留一位小数 › MYSQL之随机数生成、保留小数位、获取年龄 |
|
目录
一、随机数1.1、rand函数1.2、随机整数1.3、随机小数1.4、更新随机值
二、保留小数位2.1、round函数2.2、convert函数2.3、cast函数2.4、format函数
三、获取年龄3.1、方法一3.2、方法二3.3、方法三（ 推荐 ）3.4、方法四
四、时间段差异
一、随机数
1.1、rand函数
RAND() 函数返回 0（包括）和 1（不包括）之间的随机数。 语法如下： select rand(),rand(),rand();结果如下： mysql> select rand(),rand(),rand(); +-------------------+--------------------+--------------------+ | rand() | rand() | rand() | +-------------------+--------------------+--------------------+ | 0.778827545027088 | 0.4953759373122602 | 0.1403970822136822 | +-------------------+--------------------+--------------------+ 1 row in set (0.00 sec) 1.2、随机整数比如生成 [10,100] 的随机数，计算公式为：round(rand() * (m - n) + n) 语法如下： SELECT round(rand() * (100 - 10) + 10);结果如下： mysql> SELECT -> round(rand() * (100 - 10) + 10) as '随机整数1', -> round(rand() * (100 - 10) + 10) as '随机整数2', -> round(rand() * (100 - 10) + 10) as '随机整数3'; +---------------+---------------+---------------+ | 随机整数1 | 随机整数2 | 随机整数3 | +---------------+---------------+---------------+ | 45 | 19 | 88 | +---------------+---------------+---------------+ 1 row in set (0.01 sec)有些小伙伴可能会用到 floor 函数，在计算闭包区间时，比如生成 [50,200] 的随机数，计算公式为：floor(rand() * (m - n + 1) + n)，不要漏掉那个 +1 了，不然你取不到最大值。 语法如下： SELECT floor(rand() * (200 - 50 + 1) + 50); 1.3、随机小数比如生成 [60,100] 范围内的随机小数，计算公式为：round(rand() * (m - n) + n, 2)，后面的数字 2 就是保留几位小数。 语法如下： SELECT round(rand() * (100 - 60) + 60, 2);结果如下： mysql> SELECT -> round(rand() * (100 - 60) + 60, 2) as '随机数1', -> round(rand() * (100 - 60) + 60, 2) as '随机数2', -> round(rand() * (100 - 60) + 60, 2) as '随机数3'; +------------+------------+------------+ | 随机数1 | 随机数2 | 随机数3 | +------------+------------+------------+ | 60.10 | 99.76 | 98.52 | +------------+------------+------------+ 1 row in set (0.00 sec) 1.4、更新随机值比如随机给优惠券金额生成 [5,10] 范围内的随机小数 UPDATE tb_inf_coupon set freeAmount=round(rand() * (10 - 5) + 5); 二、保留小数位假设我们有们的表数据如下： mysql> select total_amount,pay_amount,free_amount from tb_coupon; +--------------+------------+-------------+ | total_amount | pay_amount | free_amount | +--------------+------------+-------------+ | 10086.21 | 1000 | 86.21 | | 520.98 | 50000 | 20.98 | | 19.88 | 1900 | 0.88 | +--------------+------------+-------------+ 3 rows in set (0.00 sec)这里的三个金额 total_amount 为 double 型，单位是元 pay_amount 为 int 型，单位是分 free_amount 为 decimal 型，单位是元这里只是为了演示，实际工作中绝对不会这样的，精度不高的情况下都是按分存取，就使用 BigInt 类型，如果精度高的就使用 decimal 类型。 2.1、round函数查询语句 SELECT round(total_amount, 2) as 'round处理double型', round(pay_amount / 100.0, 2) as 'round处理int型', round(free_amount, 2) as 'round处理decimal型' FROM tb_coupon;查询结果 +----------------------+-------------------+-----------------------+ | round处理double型 | round处理int型 | round处理decimal型 | +----------------------+-------------------+-----------------------+ | 10086.21 | 10.00 | 86.21 | | 520.98 | 500.00 | 20.98 | | 19.88 | 19.00 | 0.88 | +----------------------+-------------------+-----------------------+ 3 rows in set (0.00 sec) 2.2、convert函数查询语句 select convert(total_amount, DECIMAL(10, 2)) as 'convert处理double型', convert(pay_amount/100.0, DECIMAL(10, 2)) as 'convert处理int型', convert(free_amount, DECIMAL(10, 2)) as 'convert处理decimal型' from tb_coupon;查询结果 +------------------------+---------------------+-------------------------+ | convert处理double型 | convert处理int型 | convert处理decimal型 | +------------------------+---------------------+-------------------------+ | 10086.21 | 10.00 | 86.21 | | 520.98 | 500.00 | 20.98 | | 19.88 | 19.00 | 0.88 | +------------------------+---------------------+-------------------------+ 3 rows in set (0.00 sec) 2.3、cast函数查询语句 select cast(total_amount as DECIMAL(10,2)) as 'cast处理double型', cast(pay_amount/100.0 as DECIMAL(10,2)) as 'cast处理int型', cast(free_amount as DECIMAL(10,2)) as 'cast处理decimal型' from tb_coupon;查询结果 +---------------------+------------------+----------------------+ | cast处理double型 | cast处理int型 | cast处理decimal型 | +---------------------+------------------+----------------------+ | 10086.21 | 10.00 | 86.21 | | 520.98 | 500.00 | 20.98 | | 19.88 | 19.00 | 0.88 | +---------------------+------------------+----------------------+ 3 rows in set (0.00 sec) 2.4、format函数查询语句 select format(total_amount, 2) as 'format处理double型', format(pay_amount/100.0, 2) as 'format处理int型', format(free_amount, 2) as 'format处理decimal型' from tb_coupon;查询结果 +-----------------------+--------------------+------------------------+ | format处理double型 | format处理int型 | format处理decimal型 | +-----------------------+--------------------+------------------------+ | 10,086.21 | 10.00 | 86.21 | | 520.98 | 500.00 | 20.98 | | 19.88 | 19.00 | 0.88 | +-----------------------+--------------------+------------------------+ 3 rows in set (0.00 sec)从上面的结果我们可以看到当位数超过3位时就会以 逗号 分隔，并且返回的结果是string类型的，所以我们可以优化下，使用 REPLACE 函数把逗号替换为空。 优化查询语句 select REPLACE(format(total_amount, 2),',','') as '优化format处理double型', REPLACE(format(pay_amount/100.0, 2),',','') as '优化format处理int型', REPLACE(format(free_amount, 2),',','') as '优化format处理decimal型' from tb_coupon;查询结果 +-----------------------------+--------------------------+------------------------------+ | 优化format处理double型 | 优化format处理int型 | 优化format处理decimal型 | +-----------------------------+--------------------------+------------------------------+ | 10086.21 | 10.00 | 86.21 | | 520.98 | 500.00 | 20.98 | | 19.88 | 19.00 | 0.88 | +-----------------------------+--------------------------+------------------------------+ 3 rows in set (0.00 sec) 三、获取年龄假设我们有们的表数据如下，先算出他们的年龄。 mysql> SELECT user_code,user_name,birthday FROM tb_student WHERE user_code BETWEEN 6070 AND 6072; +-----------+-----------+------------+ | user_code | user_name | birthday | +-----------+-----------+------------+ | 6070 | 唐静珊 | 2010-05-13 | | 6071 | 吴恬美 | 2009-02-17 | | 6072 | 谢骊艳 | 2009-07-08 | +-----------+-----------+------------+ 3 rows in set (0.00 sec) 3.1、方法一查询语句 SELECT user_code, user_name, birthday, YEAR(FROM_DAYS(DATEDIFF(NOW(), birthday))) AS '方法一age' FROM school.tb_student WHERE user_code BETWEEN 6070 AND 6072;语法解析 NOW() :得到当前日期和时间 DATEDIFF(NOW(), birthday) ：计算当前日期到出生日期的间隔天数 n FROM_DAYS(n) ：计算从 0000 年 1 月 1 日开始 n 天后的日期，比如得到： 0012-10-24 、 0014-01-17 、 0013-08-29 YEAR() ：获取到年数，即年龄查询结果 +-----------+-----------+------------+--------------+ | user_code | user_name | birthday | 方法一age | +-----------+-----------+------------+--------------+ | 6070 | 唐静珊 | 2010-05-13 | 12 | | 6071 | 吴恬美 | 2009-02-17 | 14 | | 6072 | 谢骊艳 | 2009-07-08 | 13 | +-----------+-----------+------------+--------------+ 3 rows in set (0.00 sec) 3.2、方法二查询语句 SELECT user_code, user_name, birthday, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)),'%Y') + 0 AS '方法二age' FROM school.tb_student WHERE user_code BETWEEN 6070 AND 6072;语法解析 NOW() :得到当前日期和时间 TO_DAYS(NOW()) :把当前日期转成距离 0000 年 1 月 1 日的天数 m TO_DAYS(birthday) :把出生日期转成距离 0000 年 1 月 1 日的天数 n FROM_DAYS(m,n) :计算从 0000 年 1 月 1 日开始 m-n 天后的日期 diff ，比如得到： 0012-10-24 、 0014-01-17 、 0013-08-29 DATE_FORMAT(diff,‘%Y’) :格式化获取年份，比如得到： 0012 、 0014 、 0013 加上 0 自动转为数字年龄查询结果 +-----------+-----------+------------+--------------+ | user_code | user_name | birthday | 方法二age | +-----------+-----------+------------+--------------+ | 6070 | 唐静珊 | 2010-05-13 | 12 | | 6071 | 吴恬美 | 2009-02-17 | 14 | | 6072 | 谢骊艳 | 2009-07-08 | 13 | +-----------+-----------+------------+--------------+ 3 rows in set (0.00 sec) 3.3、方法三（ 推荐 ）查询语句 SELECT user_code, user_name, birthday, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS '方法三age' FROM school.tb_student WHERE user_code BETWEEN 6070 AND 6072;语法解析 CURDATE() :得到当前日期 TIMESTAMPDIFF(YEAR, birthday, CURDATE()) :出生日期和当前日期的年份差值即为年龄查询结果 +-----------+-----------+------------+--------------+ | user_code | user_name | birthday | 方法三age | +-----------+-----------+------------+--------------+ | 6070 | 唐静珊 | 2010-05-13 | 12 | | 6071 | 吴恬美 | 2009-02-17 | 14 | | 6072 | 谢骊艳 | 2009-07-08 | 13 | +-----------+-----------+------------+--------------+ 3 rows in set (0.00 sec) 3.4、方法四查询语句 SELECT user_code, user_name, birthday, FLOOR(DATEDIFF(CURDATE(), birthday)/365.2422) as '方法四age' FROM school.tb_student WHERE user_code BETWEEN 6070 AND 6072;语法解析 CURDATE() :得到当前日期 DATEDIFF(CURDATE(), birthday) :计算当前日期和出生日期的差值整数天 n FLOOR(n/365.2422) :现代人测算得出一年是365.2422日，计算年数后向下取整得到年龄查询结果 +-----------+-----------+------------+--------------+ | user_code | user_name | birthday | 方法四age | +-----------+-----------+------------+--------------+ | 6070 | 唐静珊 | 2010-05-13 | 12 | | 6071 | 吴恬美 | 2009-02-17 | 14 | | 6072 | 谢骊艳 | 2009-07-08 | 13 | +-----------+-----------+------------+--------------+ 3 rows in set (0.00 sec) 四、时间段差异 timestampdiff(unit,datetime_expr1,datetime_expr2) 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差，unit的值可以为：year、month、day、hour、minute、second查询语句 SELECT payTime as '支付时间', noticeTime as '通知时间', timestampdiff(second,payTime,noticeTime) as '时间间隔' FROM `tb_biz_pay_notice` WHERE payTime BETWEEN '2023-03-06 13:30:00' AND '2023-03-06 13:30:05';查询结果 +---------------------+---------------------+--------------+ | 支付时间 | 通知时间 | 时间间隔 | +---------------------+---------------------+--------------+ | 2023-03-06 13:30:00 | 2023-03-06 13:30:01 | 1 | | 2023-03-06 13:30:00 | 2023-03-06 13:30:08 | 8 | | 2023-03-06 13:30:01 | 2023-03-06 13:30:02 | 1 | | 2023-03-06 13:30:01 | 2023-03-06 13:30:01 | 0 | | 2023-03-06 13:30:03 | 2023-03-06 13:30:04 | 1 | | 2023-03-06 13:30:03 | 2023-03-06 13:30:05 | 2 | +---------------------+---------------------+--------------+ 6 rows in set (0.00 sec)一般适合定位两个时间的问题，比如上述有一笔交易支付时间和通知时间差了8秒，就可以去查查是什么原因。或者是用于统计下这些慢通知的比例等。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |