MySQL 查询以选择特定月份的所有条目
mysqlmysqli database更新于 2024/12/28 21:32:17
要在 MySQL 中选择特定月份的所有条目，请使用 monthname() 或 month() 函数。
语法如下。
select *from yourTableName where monthname(yourColumnName)='yourMonthName';
为了理解上述语法，让我们创建一个表。创建表的查询如下
mysql> create table selectAllEntriesDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingDate datetime -> ); Query OK, 0 rows affected (0.63 sec)
使用 insert 命令在表中插入一些记录。
查询如下
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2019-01-21');
Query OK, 1 row affected (0.24 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2018-02-24');
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2010-10-22');
Query OK, 1 row affected (0.20 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2011-04-12');
Query OK, 1 row affected (0.12 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2013-02-10');
Query OK, 1 row affected (0.18 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2014-02-15');
Query OK, 1 row affected (0.16 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2016-06-14');
Query OK, 1 row affected (0.18 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2017-02-14');
Query OK, 1 row affected (0.51 sec)
mysql> insert into selectAllEntriesDemo(ShippingDate) values('2015-03-29');
Query OK, 1 row affected (0.19 sec)
使用 select 语句显示表中的所有记录。
查询如下。
mysql> select *from selectAllEntriesDemo;
以下是输出。
+----+---------------------+ | Id | ShippingDate | +----+---------------------+ | 1 | 2019-01-21 00:00:00 | | 2 | 2018-02-24 00:00:00 | | 3 | 2010-10-22 00:00:00 | | 4 | 2011-04-12 00:00:00 | | 5 | 2013-02-10 00:00:00 | | 6 | 2014-02-15 00:00:00 | | 7 | 2016-06-14 00:00:00 | | 8 | 2017-02-14 00:00:00 | | 9 | 2015-03-29 00:00:00 | +----+---------------------+ 9 rows in set (0.00 sec)
以下是选择特定月份的所有条目的查询:
mysql> select *from selectAllEntriesDemo where monthname(ShippingDate)='February';
这是输出。
+----+---------------------+ | Id | ShippingDate | +----+---------------------+ | 2 | 2018-02-24 00:00:00 | | 5 | 2013-02-10 00:00:00 | | 6 | 2014-02-15 00:00:00 | | 8 | 2017-02-14 00:00:00 | +----+---------------------+ 4 rows in set (0.00 sec)
这是另一个查询。
mysql> select *from selectAllEntriesDemo where month(ShippingDate)=2;
以下是输出。
+----+---------------------+ | Id | ShippingDate | +----+---------------------+ | 2 | 2018-02-24 00:00:00 | | 5 | 2013-02-10 00:00:00 | | 6 | 2014-02-15 00:00:00 | | 8 | 2017-02-14 00:00:00 | +----+---------------------+ 4 rows in set (0.04 sec)
相关文章
有用资源
mysql 参考教程 - 该教程包含有关 mysql 的更多信息:https://www.cainiaomax.com/mysql/
