Heim >Datenbank >MySQL-Tutorial >mysql查询当天所有数据sql语句

mysql查询当天所有数据sql语句

WBOY
WBOYOriginal
2016-06-07 16:23:101721Durchsuche

mysql查询当天的所有信息: 代码如下 select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now()) 这个有一些繁琐,还有简单的写法: 代码如下 select * from table where date(regdate) = curdate()

   mysql查询当天的所有信息:

  代码如下

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

  这个有一些繁琐,还有简单的写法:

  代码如下

  select * from table where date(regdate) = curdate();

  另一种写法没测试过

  查询当天的记录

  代码如下

  select * from hb_article_view where TO_DAYS(hb_AddTime) = TO_DAYS(NOW())

  date()函数获取日期部分, 扔掉时间部分,然后与当前日期比较即可

  补充:本周、上周、本月、上个月份的数据

  查询当前这周的数据

  代码如下

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

  查询上周的数据

  代码如下

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

  查询当前月份的数据

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

  查询距离当前现在6个月的数据

  代码如下

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

  查询上个月的数据

  代码如下

  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 下月第一天

  mysql查询多少秒内的数据

  代码如下

  SELECT count( * ) AS c, sum( if( logusertype =2, logusertype, 0 ) ) /2 AS a, sum( if( logusertype =3, logusertype, 0 ) ) /3 AS b

  FROM testlog WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP( logendtime )

  查询30秒内记录的总数,loguser等于2的记录的总数和,和 loguser等于3的记录的总数.

  if( logusertype =2, logusertype, 0 ) 如果logusetype等于2 就在logusertype上累加,否则加0。

  sum( if( logusertype =2, logusertype, 0 ) ) 把logusertype都累加起来。

  sum( if( logusertype =2, logusertype, 0 ) ) /2 AS a, 除以2是统计个数。

  UNIX_TIMESTAMP(NOW())计算当前时间的秒数,

  UNIX_TIMESTAMP( logendtime )计算logendtime的秒数

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn