如何使用 MySQL 日期格式在单引号中插入日期?
mysqlmysqli database
要使用日期格式插入日期，请使用 str_to_date() 函数，并将日期放在单引号中。以下是语法 −
insert into yourTableName values(Value1,value2,......ValueN,str_to_date(‘anyDate’,’%Y-%m-%d’));
以下是 MySQL 中的日期格式 −
| Format | Description |
|---|---|
| %a | Abbreviated weekday name (Sun to Sat) |
| %b | Abbreviated month name (Jan to Dec) |
| %c | Numeric month name (0 to 12) |
| %D | Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...) |
| %d | Day of the month as a numeric value (01 to 31) |
| %e | Day of the month as a numeric value (0 to 31) |
| %f | Microseconds (000000 to 999999) |
| %H | Hour (00 to 23) |
| %h | Hour (00 to 12) |
| %I | Hour (00 to 12) |
| %i | Minutes (00 to 59) |
| %j | Day of the year (001 to 366) |
| %k | Hour (0 to 23) |
| %l | Hour (1 to 12) |
| %M | Month name in full (January to December) |
| %m | Month name as a numeric value (00 to 12) |
| %p | AM or PM |
| %r | Time in 12 hour AM or PM format (hh:mm:ss AM/PM) |
| %S | Seconds (00 to 59) |
| %s | Seconds (00 to 59) |
| %T | Time in 24 hour format (hh:mm:ss) |
| %U | Week where Sunday is the first day of the week (00 to 53) |
| %u | Week where Monday is the first day of the week (00 to 53) |
| %V | Week where Sunday is the first day of the week (01 to 53). Used with %X |
| %v | Week where Monday is the first day of the week (01 to 53). |
| %W | Weekday name in full (Sunday to Saturday) |
| %w | Day of the week where Sunday = 0 and Saturday = 6 |
| %X | Year for the week where Sunday is the first day of the week. |
| %x | Year for the week where Monday is the first day of the week |
| %Y | Year as a numeric, 4-digit value |
| %y | Year as a numeric, 2-digit value |
首先我们创建一个表 −
mysql> create table BookDateDemo −> ( −> BookName varchar(200), −> BookIssueDate datetime −> ); Query OK, 0 rows affected (0.44 sec)
现在实现我们上面讨论的语法，以 MySQL 日期格式插入日期。这里我们将日期设置为单引号 −
mysql> insert into BookDateDemo values('Let us C',str_to_date('2018-11-28','%Y-%m-%d'));
Query OK, 1 row affected (0.11 sec)
mysql> insert into BookDateDemo values('The C++ Programming',str_to_date('2018-11-29','%Y-%m-%d'));
Query OK, 1 row affected (0.16 sec)
mysql> insert into BookDateDemo values('C in Depth',str_to_date('2018-11-20','%Y-%m-%d'));
Query OK, 1 row affected (0.23 sec)
使用 select 语句显示所有记录。显示所有记录的查询如下 −
mysql> select *from BookDateDemo;
以下是输出 −
+---------------------+---------------------+ | BookName | BookIssueDate | +---------------------+---------------------+ | Let us C | 2018-11-28 00:00:00 | | The C++ Programming | 2018-11-29 00:00:00 | | C in Depth | 2018-11-20 00:00:00 | +---------------------+---------------------+ 3 rows in set (0.00 sec)
