如何在MySQL中的两个日期之间查询？
您可以借助BETWEEN语句在日期之间查询。语法如下-
select *from yourTableName where yourColumnName between ‘yourStartingDate’ and curdate().
使用curdate()或now()，这两个功能都将起作用。要了解上述语法，让我们创建一个表-
mysql> create table BetweenDateDemo −> ( −> StartDate datetime −> );
在以下查询的帮助下在表中插入一些记录-
mysql> insert into BetweenDateDemo values(date_add(now(),interval -1 year)); mysql> insert into BetweenDateDemo values(date_add(now(),interval -2 year)); mysql> insert into BetweenDateDemo values(date_add(now(),interval -3 year)); mysql> insert into BetweenDateDemo values(date_add(now(),interval 1 year)); mysql> insert into BetweenDateDemo values(date_add(now(),interval 2 year)); mysql> insert into BetweenDateDemo values(date_add(now(),interval 3 year));
现在，您可以在select语句的帮助下显示表中的所有记录。查询如下-
mysql> select *from BetweenDateDemo;
以下是输出-
+---------------------+ | StartDate | +---------------------+ | 2017-12-08 11:45:47 | | 2016-12-08 11:45:56 | | 2015-12-08 11:46:01 | | 2019-12-08 11:46:05 | | 2020-12-08 11:46:11 | | 2021-12-08 11:46:15 | +---------------------+ 6 rows in set (0.00 sec)
现在使用上面讨论的语法在日期之间执行选择-
mysql> select *from BetweenDateDemo where StartDate between '2014-8-12' and curdate();
以下是输出-
+---------------------+ | StartDate | +---------------------+ | 2017-12-08 11:45:47 | | 2016-12-08 11:45:56 | | 2015-12-08 11:46:01 | +---------------------+ 3 rows in set (0.00 sec)
使用ORDERBY获取排序顺序。查询如下-
mysql> select *from BetweenDateDemo where StartDate between '2014-8-12' and curdate() order by StartDate;
以下是输出-
+---------------------+ | StartDate | +---------------------+ | 2015-12-08 11:46:01 | | 2016-12-08 11:45:56 | | 2017-12-08 11:45:47 | +---------------------+ 3 rows in set (0.00 sec)