🎨领域:Java后端开发
🔥收录专栏: MySQL原理与实战 🐒个人主页:BreezAm 💖Gitee:https://gitee.com/BreezAm ?个人标签:【后端】【大数据】【前端】【运维】
💖介绍
先发一张大屏展示图。
🌳一、前置知识
🌲1.1 date_sub() 函数
date_sub() 函数的作用是从某一日期减去指定的时间间隔 后得到的日期,语法如下:
date_sub(date,INTERVAL expr type)
参数介绍:
- date 参数是
合法的日期表达式 - expr 参数是您希望添加的
时间间隔 - type可以是:year、month、week、day、hour、second,查看更多>
举例:
- 注意:看下面的列子前,请先记得我测试时的日期是:
2022-05-13 !!!。 curdate() 函数用于获取当前的日期,下面会详细介绍。
例子1:获取间隔两周的日期:
select DATE_SUB(curdate(),INTERVAL 2 week) as '日期'
运行如下图所示: 例子2:获取间隔7天的日期:
select DATE_SUB(curdate(),INTERVAL 7 day) as '日期'
例子3:获取间隔1年的日期:
select DATE_SUB(curdate(),INTERVAL 1 year) as '日期'
运行如下图所示:
🌲1.2 now() 函数
now() 函数返回的是当前的日期和时间,例子如下:
select now() as '当前时间'
运行结果如下图所示,注意获取到的日期时间格式 。
🌲1.3 date_format() 函数
date_format() 函数用于以不同的格式显示日期/时间 数据,语法如下:
date_format(date,format)
参数:
- date:一个日期/时间
字符串 。 - format:日期/时间的输出
格式 ,format的格式常用的有如下几个:
🐒例子1:格式化日期时间(格式1):
select DATE_FORMAT('2022-05-13 12:48:39','%Y/%m/%d %H:%i:%s') as '日期'
运行结果如下图所示: 🐒例子2:格式化日期时间(格式2):
select DATE_FORMAT('2022-05-13 12:48:39','%Y-%m-%d %H:%i:%s') as '日期'
运行结果如下图所示: 🐒例子3:格式化时间(格式1):
select DATE_FORMAT('2022-05-13 12:48:39','%T') as '当前时间'
运行结果如下图所示: 🐒例子4:格式化时间(格式2):
select DATE_FORMAT('2022-05-13 12:48:39','%H:%i:%s') as '时间'
运行结果如下图所示:
🌲1.4 date()、year()、month()、day()、quarter() 函数
date() 函数的作用是用于提取日期时间中的日期部分 ,语法如下:
date(datetime_str)
参数:
- datetime_str:表示一个含有
日期时间 字符串。 例子如下:
select date('2022-05-13 12:01:31') as '日期'
运行如下图所示: 除此之外还有几个与date()相似的函数,如下所示:
- year(date):截取
年 - month(date):截取
月 - day(date):截取
日 - quarter(date):截取
季度
例子如下:
SELECT
DAY( '2022-05-13 12:01:31' ) '日',
MONTH ( '2022-05-13 12:01:31' ) '月',
QUARTER ( '2022-05-13 12:01:31' ) '季度',
YEAR ( '2022-05-13 12:01:31' ) '年'
运行如下图所示:
🌲1.5 ifnull() 函数
ifnull() 函数的的语法如下:
ifnull(v1,v2)
他的含义是如果v1 参数为NULL就返回v2,如果v1参数不为NULL ,就将v1 返回,这个函数在数据统计 中具有很大的用处,例子如下:
select ifnull(NULL,0) as '返回值'
运行如下图所示:
🌲1.6 curdate()
curdate() 函数用于获取当前的日期,例子如下:
select curdate() as '日期'
运行结果如下图所示,注意获取到的日期格式 。
🌲1.7 datediff() 函数
datediff() 函数用于返回两个日期之间的天数 ,语法如下:
datediff(date1,date2)
🐒例子如下:
select datediff('2022-05-13 12:48:39','2022-05-11 11:20:39') as '相差天数'
运行结果如下图所示:
🌲1.8 date_add() 函数
date_add() 函数的作用是向日期添加指定的时间间隔 ,语法如下:
date_add(date,INTERVAL expr type)
注:参数和上文介绍的date_format() 函数一样。
🐒例子:将日期向后添加45 天。
SELECT DATE_ADD('2022-05-13 12:48:39',INTERVAL 45 DAY) AS '日期'
运行结果如下图所示:
🌲1.9 curtime() 函数
该函数的作用是返回当前的时间,例子如下:
select curtime() as '当前时间'
运行结果如下图所示:
三个获取当前日期、时间函数的对比,例子如下:
SELECT NOW() as '当前日期时间',CURDATE() as '当前日期',CURTIME() as '当前时间'
运行结果如下图所示:
🌳二、实战
??在上一节中介绍完了使用MySQL做数据统计时常用的一些函数,本节将介绍一些开发中常见的数据统计场景。
在开始之前先创建一张用户表 用于测试,表语句如下:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
`sort` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
往里面插入一些测试数据,结果如下图所示:
🌲2.1 统计7天前的数据
统计七天前的数据并且按照创建日期降序 排列。
SELECT
*
FROM
USER
WHERE
DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( create_time )
ORDER BY
create_time DESC
运行结果如下图所示: 注 :以上是统计7天前的,可以按照自己的需求改变,比如一个月前、一年前、一周前等等。
🌲2.2 统计最近7天每天有多少数据量
SELECT
date_format( create_time, '%Y-%m-%d' ) AS '日期',
count( 1 ) AS '数量'
FROM
user
WHERE
create_time >= date(now()) - INTERVAL 7 DAY
GROUP BY
DAY ( create_time );
运行结果如下图所示:
🌲2.3 统计最近7天每天有多少数据量,没有用0填充
SELECT
a.date,
IFNULL( b.count, 0 ) AS count
FROM
(
SELECT
CURDATE() AS date UNION ALL
SELECT
DATE_SUB( CURDATE(), INTERVAL 1 DAY ) AS date UNION ALL
SELECT
DATE_SUB( CURDATE(), INTERVAL 2 DAY ) AS date UNION ALL
SELECT
DATE_SUB( CURDATE(), INTERVAL 3 DAY ) AS date UNION ALL
SELECT
DATE_SUB( CURDATE(), INTERVAL 4 DAY ) AS date UNION ALL
SELECT
DATE_SUB( CURDATE(), INTERVAL 5 DAY ) AS date UNION ALL
SELECT
DATE_SUB( CURDATE(), INTERVAL 6 DAY ) AS date
) a
LEFT JOIN (
SELECT
date_format( create_time, '%Y-%m-%d' ) AS date,
count( 1 ) AS count
FROM
USER
WHERE
create_time >= date(
now()) - INTERVAL 7 DAY
GROUP BY
DAY ( create_time )
) b ON a.date = b.date
ORDER BY
a.date DESC
运行结果如下图所示:
🌲2.4 统计昨天的记录总数
SELECT
count( 1 ) AS '数量',
date( create_time ) AS '日期'
FROM
USER
WHERE
date( create_time )= date_sub( curdate(), INTERVAL 1 DAY )
运行结果如下图所示:
🌲2.5 统计当天的记录总数
SELECT
count( 1 ) AS '数量',
curdate() AS '日期'
FROM
USER
WHERE
date( create_time )= curdate()
运行结果如下图所示:
🌲2.6 统计两个时间段内的每天记录数(传入两个日期)
特别注意 :该SQL语句,如果时间段内没有数据时,不会显示任何记录,也就是没有数据不会用0填充。
SELECT
date_format( create_time, '%Y-%m-%d' ) AS '日期',
count( 1 ) AS '数量'
FROM
USER
WHERE
date( create_time )
BETWEEN date( '2022-05-08 12:48:39' ) AND date( '2022-05-15 12:48:39' )
GROUP BY
DAY ( create_time )
CSDN统计某个时间段内的数据,如下图所示:
持续更新中…
💖总结
在做数据统计时,函数发挥着巨大的作用,在本文中主要介绍了几个,在MySQL中还有很多的函数。
💖参考文献
🔥收录专栏:MySQL原理与实战
|