现实中我们会遇到统计报表。比如查询当月每一天的数据数量。。。等等之类的。以下内容就是有关这方面的咯。
首先要知道几个函数
mysql> select now(); //这个是显示的当前时间
+---------------------+
| now() |
+---------------------+
| 2016-12-09 18:39:57 |
+---------------------+
1 row in set mysql> select curdate(); //这个是当前日期
+------------+
| curdate() |
+------------+
| 2016-12-09 |
+------------+
1 row in set
mysql> select date(now()); //当前时间格式化为日期
+-------------+
| date(now()) |
+-------------+
| 2016-12-09 |
+-------------+
1 row in set mysql> select month(now()); //当前时间格式化为月份
+--------------+
| month(now()) |
+--------------+
| 12 |
+--------------+
1 row in set mysql> select year(now()); //当前时间格式化为年份
+-------------+
| year(now()) |
+-------------+
| 2016 |
+-------------+
1 row in set
以上都是简单的东西，以下主要用的是DATE_FORMAT函数
MySQL DATE_FORMAT() DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。
可以使用的格式有：
格式 描述
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位 DATA_SUB函数 定义和用法
DATE_SUB() 函数从日期减去指定的时间间隔。 语法
DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。 type 参数可以是下列值： Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
更详细的参考W3cSchool：Date函数
例子1：查询一个表，从当前年份开始，向后退12年，每一年的数据量。（如果有的年份无数据就不会显示）
mysql> select count(*),DATE_FORMAT(a.article_time,'%Y') t from article a where DATE_FORMAT(a.article_time,'%Y') > DATE_FORMAT(DATE_SUB(now(),INTERVAL 12 YEAR),'%Y') group by t;
+----------+------+
| count(*) | t |
+----------+------+
| 1 | 2015 |
| 54 | 2016 |
+----------+------+
2 rows in set
例子2：查询一个表，在当前年份的每一个月数据量（如果无数据就不会显示）
mysql> select count(*),DATE_FORMAT(a.article_time,'%Y-%m') t from article a where DATE_FORMAT(a.article_time,'%Y') = DATE_FORMAT(now(),'%Y') group by t;
+----------+---------+
| count(*) | t |
+----------+---------+
| 1 | 2016-07 |
| 22 | 2016-08 |
| 26 | 2016-09 |
| 3 | 2016-10 |
| 1 | 2016-11 |
| 1 | 2016-12 |
+----------+---------+
6 rows in set
例子3：查询某个月的每一天的数据量。（如果没有数据就不会显示）
mysql> select count(*),DATE_FORMAT(a.article_time,'%Y-%m-%d') t from article a where DATE_FORMAT(a.article_time,'%Y-%m') = '2016-09' group by t;
+----------+------------+
| count(*) | t |
+----------+------------+
| 22 | 2016-09-03 |
| 2 | 2016-09-21 |
| 1 | 2016-09-22 |
| 1 | 2016-09-27 |
+----------+------------+
4 rows in set