MySQL做数据统计SQL语句整理大全（原理+实战）

领域：Java后端开发

---

---

收录专栏： MySQL原理与实战
个人主页：BreezAm
Gitee：https://gitee.com/BreezAm
✨个人标签：【后端】【大数据】【前端】【运维】

文章目录

• • 介绍
  • • 一、前置知识
    • • 1.1 date_sub() 函数
      • 1.2 now() 函数
      • 1.3 date_format() 函数
      • 1.4 date()、year()、month()、day()、quarter() 函数
      • 1.5 ifnull() 函数
      • 1.6 curdate()
      • 1.7 datediff() 函数
      • 1.8 date_add() 函数
      • 1.9 curtime() 函数
    • 二、实战
    • • 2.1 统计7天前的数据
      • 2.2 统计最近7天每天有多少数据量
      • 2.3 统计最近7天每天有多少数据量，没有用0填充
      • 2.4 统计昨天的记录总数
      • 2.5 统计当天的记录总数
      • 2.6 统计两个时间段内的每天记录数（传入两个日期）
    • 总结
  • 参考文献

介绍

先发一张大屏展示图。

---

一、前置知识 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中还有很多的函数。

参考文献

• [1] W3school

收录专栏：MySQL原理与实战