Simple query by date number of records in a table in MySQL

To test the table table structure:
Mysql> Show CREATE TABLE Dr_stats\g
1. Row

       Table:dr_statscreate table:create Table ' dr_stats ' (' ID ' bigint () unsigned not NULL auto_increment, ' views ' Int (ten) NOT null default ' 0 ' COMMENT ' impressions ', ' num ' Mediumint (8) NOT null default ' 0 ' COMMENT ' billable ', ' advnum ' int (ten) not  Null default ' 0 ' COMMENT ' advertiser billing ', ' clicks ' mediumint (8) Not NULL default ' 0 ' COMMENT ' clicks ', ' Do2click ' mediumint (8) not Null default ' 0 ' COMMENT ' two hits ', ' Day ' date not NULL default ' 0000-00-00 ' COMMENT ' billing date ', ' Planid ' mediumint (8) Not NU LL default ' 0 ' COMMENT ' program ID ', ' uid ' mediumint (8) NOT null default ' 0 ' COMMENT ' webmaster id ', ' SiteID ' mediumint (8) NOT NULL DE FAULT ' 0 ' COMMENT ' site ID ', ' zoneid ' mediumint (8) NOT null DEFAULT ' 0 ' COMMENT ' ad bit id ', ' adstypeid ' mediumint (8) NOT NULL  COMMENT ' Ad type id ', ' deduction ' mediumint (8) NOT null default ' 0 ' COMMENT ' buckle ', ' Sumprofit ' decimal (10,4) NOT NULL default  ' 0.0000 ' COMMENT ' deduction amount ', ' Sumpay ' decimal (10,4) not NULL DEFAULT ' 0.0000 ' COMMENT ' billable amount ', ' Sumadvpay ' decimal (10,4) not NULL DEFAULT ' 0.0000 ' COmment ' Total billing amount ', ' status ' tinyint (1) NOT null default ' 0 ' COMMENT ' is settled ', ' Dosage ' mediumint (8) NOT null default ' 0 ' COM ment ' complement ', ' Sumdosage ' decimal (10,4) NOT null default ' 0.0000 ' COMMENT ' complement amount ', ' pclick ' int (8) NOT null default ' 0 ', PR Imary key (' id '), UNIQUE key ' Day ' (' Day ', ' Planid ', ' uid ', ' SiteID ', ' ZoneID ', ' Adstypeid '), key ' Planid_uid ' (' Planid ', ' UI d ')) Engine=innodb auto_increment=9298495886 DEFAULT Charset=utf8 comment= ' site settlement ' 1 row in Set (0.00 sec)

Query the data for the year April in a single table:

mysql -uroot -p‘ZykJ7‘ -S /tmp/mysql.sock -e ‘use drnew;select * from dr_stats where 1 and month(day)=04;‘查询一张表中一年的中5月份有多少条记录：[[email protected] ~]# time mysql -uroot -p‘ZykJ7‘ -S /tmp/mysql.sock -e ‘use drnew;select count(*) from dr_stats where 1 and month(day)=05;‘Warning: Using a password on the command line interface can be insecure.+----------+| count(*) |+----------+|  1071903 |+----------+[[email protected] ~]# time mysql -uroot -p‘ZykJ7‘ -S /tmp/mysql.sock -e ‘use drnew;select count(*) from dr_stats where 1 and month(day)=05 and  day(day)  between 1 and 31;‘Warning: Using a password on the command line interface can be insecure.+----------+| count(*) |+----------+|  1071903 |+----------+

Query the number of data records for the Dr_stats table from April 1, 2016 to April 21, 2016:

time mysql -uroot -p‘ZykJ7‘ -S /tmp/mysql.sock -e ‘use drnew;select count(*) from dr_stats where 1 and year(day)=2016 and month(day)=04 and  day(day)  between 1 and 21;‘ Warning: Using a password on the command line interface can be insecure.+----------+| count(*) |+----------+|        0 |+----------+

April 1, 2016 to April 21, 2016 This dr_stats table is free of data.

Query the number of data records for the Dr_stats table from April 1, 2016 to April 22, 2016:

time mysql -uroot -p‘ZykJ(5678%$#@!)cpv17‘ -S /tmp/mysql.sock -e ‘use drnew;select count(*) from dr_stats where 1 and year(day)=2016 and month(day)=04 and  day(day)  between 1 and 22;‘ Warning: Using a password on the command line interface can be insecure.+----------+| count(*) |+----------+|       95 |+----------+

Check the Dr_stats form from April 22, 2016 to April 23, 2016 for data:
Time Mysql-uroot-p ' ZykJ7 '-s/tmp/mysql.sock-e ' use drnew;select * from Dr_stats where 1 and year (day) =2016 and month (d ay) =04 and day between; ' >>/root/txt04

Check the Dr_stats form from April 22, 2016 to April 22, 2016 for data:

[[email protected] www]# time mysql -uroot -p‘ZykJ7‘ -S /tmp/mysql.sock -e ‘use drnew;select count(*) from dr_stats where day>="2016-04-22" and day<="2016-04-22";‘Warning: Using a password on the command line interface can be insecure.+----------+| count(*) |+----------+|       95 |+----------+

Check the Dr_stats form from April 21, 2016 to April 22, 2016 for data:

time mysql -uroot -p‘ZykJ7‘ -S /tmp/mysql.sock -e ‘use drnew;select count(*) from dr_stats where day>="2016-04-21" and day<="2016-04-22";‘Warning: Using a password on the command line interface can be insecure.+----------+| count(*) |+----------+|       95 |+----------```+

Simple query by date number of records in a table in MySQL