mysql随笔

因为要做定时任务跑定时脚本，查询某时间的数据需要用sql函数来解决。

我把我用到的mysql函数和函数怎么使用的记录下来。

1.MySQL查询今天，本周，上周，本月，上个月，本季度，上季度，今年，去年的数据的sql

如果你的字段存的数据是2018-12-17 15:17:34这种格式或者是20181217这种都可以用：

今天：select * from `you_table` where to_days(`column`) = to_days(now());

昨天：select * from `you_table` where to_days(now()) – to_days(`column`) <= 1;

七天内：select * from `you_table` where date_sub(curdate(), INTERVAL 7 DAY) <= date(`column`);

30天内：select * from `you_table` where date_sub(curdate(), INTERVAL 30 DAY) <= date(`column`);

本月：select * from `you_table` where date_format(`column`, '%Y%m') = date_format(curdate() , '%Y%m');

上个月：select * from `you_table` where period_diff(date_format(now() , '%Y%m') , date_format(`column`, '%Y%m')) =1;

PERIOD_DIFF(P1,P2)

返回周期P1和P2之间的月数。 P1和P2格式为YYMM或YYYYMM。注意周期参数 P1 和 P2 都不是日期值。

mysql> SELECT PERIOD_DIFF(1812,201811);

#返回的是相差几个月，上面这个返回1

select * from `you_table` where period_diff(date_format(now() , '%Y%m') , date_format(`column`, '%Y%m')) =1;这句sql的意思就是距离当前一个月的数据。

 

TO_DAYS(date)

基于日期参数 date，返回一个天数（自年份0开始的天数）。

mysql> SELECT TO_DAYS(950501);
+---------------------------------------------------------+
| TO_DAYS(950501)                                         |
+---------------------------------------------------------+
| 728779                                                  |
+---------------------------------------------------------+

 

SUBDATE(date,INTERVAL expr unit) 与 SUBDATE(expr,days)

当第二个参数采用 INTERVAL 格式时，SUBDATE() 等同于 DATE_SUB()。要想详细了解 INTERVAL 单元参数，请参考 DATE_ADD()。

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DATE_FORMAT(date,format)

该函数会根据 format 字符串来格式化 date 值。

下表中列出了一些可用于 format 字符串的标识符。格式标识符第一个字符必须是%字符。

格式标识符	说明
%a	一星期中每天名称的缩写（Sun...Sat）
%b	月份的缩写（Jan...Dec）
%c	月份的数字表现形式（0...12）
%D	带有英语后缀的一个月中的每一天的名称（0th、1st、2nd、3rd）
%d	用数字形式表现的每月中的每一天（00...31）
%e	用数字形式表现的每月中的每一天（0...31）
%f	毫秒（000000...999999）
%H	24时制显示的小时（00...23）
%h	12时制显示的小时（01...12）
%I	12时制显示的小时（01...12）
%i	以数字形式表现的分钟数（00...59）
%j	一年中的每一天（001...366）
%k	24时制小时的另一种表现格式（0...23）
%l	12时制小时的另一种表现格式（1...12）
%M	用完整英文名称表示的月份（January...December）
%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	一星期中各日名称（Sunday...Saturday）
%w	一星期中各日名称（0代表星期日，6代表星期六，以此类推）
%X	某星期所处年份。其中，星期天是每星期的开始日，采用4位数字形式表现，和 %V一起使用
%x	某星期所处年份。其中，星期一是每星期的开始日，采用4位数字形式表现，和 %V 一起使用
%Y	4位数字表示的年份
%y	2位数字表示的年份
%%	符号%的字面值
%x（x为斜体）	字符x的字面值，x指以上未列出的任何字符

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y')          |
+---------------------------------------------------------+
| Saturday October 1997                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
    -> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00.......                 |
+---------------------------------------------------------+
|  22 22 10 10:23:00 PM 22:23:00 00 6                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

 2.MySQL查询当前时间2个月前，15天前，15天后，一个月后的数据的sql

下面这个可以查询准确的距离当前时间一个月后那天的数据，不是一个月内的数据

SELECT * FROM `you_table` WHERE (`column` = date_sub(CURDATE(),interval 2 month));//2个月前

SELECT * FROM `you_table` WHERE (`column` = date_sub(CURDATE(),interval 15 day));//15天前

SELECT * FROM `you_table` WHERE (`column` = date_add(CURDATE(),interval 15 day)) ;//15天后

SELECT * FROM `you_table` WHERE (`column` = date_add(CURDATE(),interval 1 month));//1个月后

DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔

3.按月查询每个月的收支

SELECT SUM(money) AS money,FROM_UNIXTIME(date/1000,'%Y-%m') AS months,CASE WHEN type='1' THEN '支出' WHEN type='2' THEN '收入' END AS '收支'  FROM asset_flow GROUP BY months,type