MySQL日期时间函数 CURDATE() 用法
时间:2021-12-06 作者:匿名
MYSQL CURDATE()用于获取当前日期,返回的日期的格式是 'YYYY-MM-DD' 或 YYYYMMDD。
语法
CURDATE();
实例
mysql> SELECT CURDATE(); +------------+ | CURDATE() | +------------+ | 2021-07-11 | +------------+ 1 row in set (0.00 sec)
带有参数+0的示例
mysql> SELECT CURDATE() +0; +--------------+ | CURDATE() +0 | +--------------+ | 20211206 | +--------------+ 1 row in set (0.00 sec)
带有参数+12的示例,作用是将12天添加到当前日期
mysql> SELECT CURDATE()+12; +--------------+ | CURDATE()+12 | +--------------+ | 20211218 | +--------------+ 1 row in set (0.00 sec)
我们还可以使用此函数从当前日期中减去所需的天数
mysql> SELECT CURDATE()-365; +-----------------+ | CURDATE()-22213 | +-----------------+ | 20201206 | +-----------------+ 1 row in set (0.00 sec)
数据库中的操作实例
让我们使用 CREATE 语句在 MySQL 数据库中创建一个名为 MyPlayers 的表,如下所示 -
mysql> CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );
现在,我们将使用 INSERT 语句在 MyPlayers 表中插入 7 条记录:
mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'); mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');
以下查询以天为单位计算球员的年龄:
mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, DATEDIFF(CURDATE(),Date_Of_Birth) as Age_In_Days FROM MyPlayers; +------------+------------+---------------+-------------+-------------+ | First_Name | Last_Name | Date_Of_Birth | Country | Age_In_Days | +------------+------------+---------------+-------------+-------------+ | Shikhar | Dhawan | 1981-12-05 | India | 14463 | | Jonathan | Trott | 1981-04-22 | SouthAfrica | 14690 | | Kumara | Sangakkara | 1977-10-27 | Srilanka | 15963 | | Virat | Kohli | 1988-11-05 | India | 11936 | | Rohit | Sharma | 1987-04-30 | India | 12491 | | Ravindra | Jadeja | 1988-12-06 | India | 11905 | | James | Anderson | 1982-06-30 | England | 14256 | +------------+------------+---------------+-------------+-------------+ 7 rows in set (0.11 sec)
相关函数
CURRENT_DATE
语法
CURRENT_DATE();
作用
MYSQL CURRENT_DATE()函数是CURDATE()的同义词。它用于获取当前日期。结果值是基于上下文的字符串或数值,返回的日期将采用 'YYYY-MM-DD' 或 YYYYMMDD 格式。