简单按日期查询mysql某张表中的记录数
测试表表结构:
mysql> show create table testr_stats\G
*************************** 1. row ***************************
mysql -uroot -p’ZykJ7’ -S /tmp/mysql.sock -e ‘use drnew;select * from dr_stats where 1 and month(day)=04;’
查询一张表中一年的中5月份有多少条记录:
[root@cacti ~]# 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 |
±---------+
[root@cacti ~]# 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 |
±---------+
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 |
±---------+
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 |
±---------+
[root@cacti 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 |
±---------+
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 |
±---------