一、时间
1)查询时间段-->昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
-->7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
-->近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
-->本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
-->上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1复制代码2)根据日期查找对应数据--->直接查询时间
select * from 表名 where date='1999-10-01';
--->查询时间范围
select * from 表名 where date<'1999-01-01';
--->使用函数查询 YEAR(),MONTH(),DAYOFMONTH()
select * from 表名 where MONTH(date)=3; 查询时间为三月的数据
select * from 表名 where MONTHNAME(date)='March'; MONTHNAME(date)函数返回的是月份全名
--->查询今天过生日的人员 MONTH(CURRENT_DATE)指的是当前会话时间的月份
select * from 表名 where MONTH(date)=MONTH(CURRENT_DATE) AND DAYOFMONTH(date)=DAYOFMONTH(CURRENT_DATE)
3)TIMESTAMPDIFF()函数
(1) datediff函数,返回值是相差的天数,不能定位到小时、分钟和秒。
(2) TIMESTAMPDIFF函数,有参数设置,可以精确到天(DAY)、小时(HOUR),分钟(MINUTE)和秒(SECOND),使用起来比datediff函数更加灵活。对于比较的两个时间,时间小的放在前面,时间大的放在后面。
SELECT TIME_TO_SEC(TIMEDIFF('2018-09-30 19:38:45', '2018-08-23 10:13:01')) AS DIFF_SECOND1, -- 秒
UNIX_TIMESTAMP('2018-09-30 19:38:45')-UNIX_TIMESTAMP('2018-08-23 10:13:01') AS DIFF_SECOND2, -- 秒
TIMESTAMPDIFF(SECOND,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_SECOND3, -- 秒
TIMESTAMPDIFF(MINUTE,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_MINUTE, -- 分
TIMESTAMPDIFF(HOUR,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_HOUR, -- 小时
TIMESTAMPDIFF(DAY ,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_DATE1, -- 天
DATEDIFF('2018-09-30 19:38:45','2018-08-23 10:13:01') AS DIFF_DATE2, -- 天
TIMESTAMPDIFF(MONTH,'2018-08-23 10:13:01','2018-09-25 19:38:45') AS DIFF_MONTH, -- 月
TIMESTAMPDIFF(YEAR,'2018-08-23 10:13:01','2020-07-25 19:38:45') AS DIFF_YEAR -- 年
FROM
test
二、求和函数
1)使用 WITH ROLLUP WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
±-------±-------------+
| name | singin_count |
±-------±-------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
±-------±-------------+
4 rows in set (0.00 sec)
其中记录 NULL 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:select coalesce(a,b,c);
SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
±-------------------------±-------------+
| coalesce(name, ‘总数’) | singin_count |
±-------------------------±-------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
±-------------------------±-------------+
4 rows in set (0.01 sec)