MySQL查詢的方式很多，下面為您介紹的MySQL查詢實(shí)現(xiàn)的是查詢本周、上周、本月、上個月份的數(shù)據(jù)，如果您對MySQL查詢方面感興趣的話，不妨一看。

查詢當(dāng)前今天的數(shù)據(jù)

SELECT name,submittime FROM enterprise WHERE?YEARWEEK(date_format(submittime,'%Y-%m-%d')) =date_format(now(),'%Y-%m-%d');

查詢當(dāng)前這周的數(shù)據(jù)

SELECT name,submittime FROM enterprise WHERE?YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查詢上周的數(shù)據(jù)

SELECT name,submittime FROM enterprise WHERE?YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查詢當(dāng)前月份的數(shù)據(jù)

select name,submittime from enterprise ? where?date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查詢距離當(dāng)前現(xiàn)在6個月的數(shù)據(jù)

select name,submittime from enterprise where?submittime between date_sub(now(),interval 6 month) and now();

查詢上個月的數(shù)據(jù)

select name,submittime from enterprise ? where?date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from `user` where?DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())

select *

from user

where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())

select *

from [user]

where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now())

and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())

select *

from [user]

where pudate between 上月最后一天

and 下月第一天

############################

默認(rèn)情況下， 這個?yearweek、WEEK ( 日期 )? 是 每把周日，作為一周的開始。

有的是周日開始，有的是周一開始。而工作中有的時候每周是從周六開始算的，有些數(shù)據(jù)需要按周統(tǒng)計，那么那種方式比較好呢？

通過下面的研究，認(rèn)為yearweek()是適合的，它返回的是 201311這樣的格式。若要把周六當(dāng)做新的一周的開始，那么就是：

select yearweek(DATE_ADD(now(), INTERVAL 1 DAY));

mysql> select yearweek(DATE_ADD(now(), INTERVAL 1 DAY));

+-------------------------------------------+

| yearweek(DATE_ADD(now(), INTERVAL 1 DAY)) |

+-------------------------------------------+

| ???????????????????????????????????201311 |

+-------------------------------------------+

1 row in set

mysql> select yearweek(now());

+-----------------+

| yearweek(now()) |

+-----------------+

| ?????????201310 |

+-----------------+

1 row in set

mysql>

select yearweek(now() -?INTERVAL 1 DAY) ? ?從周1開始本周計算

select yearweek('2015-02-02 11:12:00' -?INTERVAL 1 DAY)

############################

摘自：http://hideto.javaeye.com/blog/255816

當(dāng)前week的第一天：

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)

當(dāng)前week的最后一天：

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) – 5 DAY)

前一week的第一天：

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY)

前一week的最后一天：

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY)

前兩week的第一天：

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY)

前兩week的最后一天：

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY)

當(dāng)前month的第一天：

SELECT concat(date_format(LAST_DAY(now()),’%Y-%m-’),’01′)

當(dāng)前month的最后一天：

SELECT LAST_DAY(now())

前一month的第一天：

SELECT concat(date_format(LAST_DAY(now() – interval 1 month),’%Y-%m-’),’01′)

前一month的最后一天：

SELECT LAST_DAY(now() – interval 1 month)

前兩month的第一天：

SELECT concat(date_format(LAST_DAY(now() – interval 2 month),’%Y-%m-’),’01′)

前兩month的最后一天：

SELECT LAST_DAY(now() – interval 2 month)

當(dāng)前quarter的第一天：

select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),’%Y-%m-’),’01′)

當(dāng)前quarter的最后一天：

select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month)

前一quarter的第一天：

select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-6 month),’%Y-%m-’),’01′)

前一quarter的最后一天：

select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-4 month)

前兩quarter的第一天：

select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-9 month),’%Y-%m-’),’01′)

前兩quarter的最后一天：

select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-7 month)

UNIX時間戳轉(zhuǎn)換為日期用函數(shù)： FROM_UNIXTIME()

select?FROM_UNIXTIME(1156219870);

日期轉(zhuǎn)換為UNIX時間戳用函數(shù)： UNIX_TIMESTAMP()

Select?UNIX_TIMESTAMP(’2006-11-04?12:23:00′);

########################

今天

select * from 表名 where to_days(時間字段名) = to_days(now());

昨天

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

#查詢本季度數(shù)據(jù)select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());#查詢上季度數(shù)據(jù)select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));#查詢本年數(shù)據(jù)select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());#查詢上年數(shù)據(jù)select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

查詢當(dāng)前這周的數(shù)據(jù)?SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查詢上周的數(shù)據(jù)SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;//國外一周是從周日到周六來算的 ?SELECT * FROM ordersrecord WHERE ? ? ? ? YEARWEEK(ordertime,1) =YEARWEEK(date_sub(curdate(),interval 7 day),1)

查詢當(dāng)前月份的數(shù)據(jù)select name,submittime from enterprise ? where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查詢距離當(dāng)前現(xiàn)在6個月的數(shù)據(jù)select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查詢上個月的數(shù)據(jù)select name,submittime from enterprise ? where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())

select * ?from user ?where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())

select * ?from [ user ] ?where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())

select * ?from [ user ] ?where pudate between 上月最后一天and 下月第一天

where ? date(regdate) ? = ? curdate();

select ? * ? from ? test ? where ? year(regdate)=year(now()) ? and ? month(regdate)=month(now()) ? and ? day(regdate)=day(now())

SELECT date( c_instime ) ,curdate( )FROM `t_score`WHERE 1LIMIT 0 , 30

總結(jié)

以上是生活随笔為你收集整理的前15天mysql_Mysql查询今天/昨天/15天前/上个月/去年/上周每日等函数的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。