MySQL中常见的时间日期函数及其使用、表的复制、向表中插入select语句的查询结果
常见的时间日期函数及其使用
处理日期操作的时候，能用相关的日期函数，就用。
1、获取当前的日期和时间 now()
#例如
select now();
2022-01-14 20:19:33
2、获取年份 year()
#例如
select year(now());
2022
3、获取月份 month()
#例如
select month(now());
1
4、获取星期 week()
#例如
select week(now());
2
5、获取日 day()
#例如
select day(now());
14
6、获取时 hour()
#例如
select hour(now);
20
7、获取分 minute()
#例如
select minute(now());
19
8、获取秒 second()
#例如
select second(now());
33
DATE类型和STRING类型的相互转换
1、date-->string
定义和用法
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
语法
DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。
可以使用的格式有：
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（00-12） |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒（00-59） |
| %s | 秒（00-59） |
| %T | 时间, 24-小时（hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-53）星期一是一周的第一天 |
| %V | 周（01-53）星期日是一周的第一天，与 %X 使用 |
| %v | 周（01-53）星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天（0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
实例
下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
结果如下所示：
Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243
2、string-->date
下面说明了STR_TO_DATE()函数的语法：
STR_TO_DATE(str,fmt);
STR_TO_DATE()根据fmt格式字符串将str字符串转换为日期值。 STR_TO_DATE()函数可能会根据输入和格式字符串返回DATE，TIME或DATETIME值。 如果输入字符串是非法的，则STR_TO_DATE()函数返回NULL。
STR_TO_DATE()函数扫描输入字符串来匹配格式字符串。格式字符串可能包含以百分比(%)字符开头的文字字符和格式说明符。 查看格式说明符列表的DATE_FORMAT函数。
STR_TO_DATE()函数在数据迁移中非常有用，涉及从外部格式到MySQL时间数据格式的时间数据转换。
MySQL STR_TO_DATE示例
我们来看一些使用STR_TO_DATE()函数将字符串转换成日期和/或时间值的例子。
以下语句将字符串转换为DATE值，如下所示
mysql> SELECT STR_TO_DATE('21,5,2018','%d,%m,%Y');
+-------------------------------------+
| STR_TO_DATE('21,5,2018','%d,%m,%Y') |
+-------------------------------------+
| 2018-05-21 |
+-------------------------------------+
1 row in set
根据格式字符串格式'％d，％m，％Y'，STR_TO_DATE()函数扫描'21,5,2018'输入字符串。
- 首先，它尝试在输入字符串中找到
%d格式说明符的匹配项，这是一个月的日期(01 … 31)。 因为21号与%d说明符相匹配，所以该函数将21作为日期值。 - 第二步，因为格式字符串中的逗号(
，)字符串与输入字符串中的逗号匹配，所以函数继续检查第二个格式说明符%m，这是一个月(01 … 12)，并且发现数字5与%m格式说明符匹配。以数字5为月份。 - 第三步，在匹配第二个逗号(，)之后，STR_TO_DATE()函数会继续找到第三个格式说明符％Y的匹配项，它是四位数的年份，例如2012,2013等，它的编号为2013 年值。
STR_TO_DATE()函数在根据格式字符串解析输入字符串时忽略输入字符串末尾的额外字符。 请参阅以下示例：
mysql> SELECT STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y');
+------------------------------------------------------+
| STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y') |
+------------------------------------------------------+
| 2013-05-21 |
+------------------------------------------------------+
1 row in set
STR_TO_DATE()将输入字符串不提供的所有不完整的日期值设置为零。 请参阅以下示例：
mysql> SELECT STR_TO_DATE('2018','%Y');
+--------------------------+
| STR_TO_DATE('2018','%Y') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set
因为输入字符串只提供年值，所以STR_TO_DATE()函数返回一个日期值，它的月和日设置为零。
以下示例将时间字符串转换为TIME值：
mysql> SELECT STR_TO_DATE('113005','%h%i%s');
+--------------------------------+
| STR_TO_DATE('113005','%h%i%s') |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set
类似于未指定的日期部分，STR_TO_DATE()函数将未指定的时间部分设置为零，请参见以下示例：
mysql> SELECT STR_TO_DATE('11','%h');
+------------------------+
| STR_TO_DATE('11','%h') |
+------------------------+
| NULL |
+------------------------+
1 row in set
以下示例将字符串转换为DATETIME值，因为输入字符串提供日期和时间部分。
mysql> SELECT STR_TO_DATE('20130101 1130','%Y%m%d %h%i') ;
+--------------------------------------------+
| STR_TO_DATE('20130101 1130','%Y%m%d %h%i') |
+--------------------------------------------+
| 2013-01-01 11:30:00 |
+--------------------------------------------+
1 row in set
DATE类型和TIMESTAMP类型的相互转换
时间戳适合日期的加减操作
1、date-->timestamp(时间戳)
UNIX_TIMESTAMP函数：获取UNIX时间戳
UNIX_TIMESTAMP(date) 若无参数调用，返回一个无符号整数类型的 UNIX 时间戳（'1970-01-01 00:00:00'GMT之后的秒数）。
若用 date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00'GMT后的秒数的形式返回。
【实例】使用 UNIX_TIMESTAMP() 函数返回 UNIX 格式的时间戳，输入 SQL 语句和执行结果如下。
mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW() |
+------------------+-----------------------+---------------------+
| 1551251270 | 1551251270 | 2019-02-27 15:07:50 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)
2、timestamp(时间戳)-->date
表的复制
1、create table ... like ...
#创建一个新表复制旧表(被复制的表)的结构
create table if not exists 表名 like 被复制的表名;
2、create table ... as ...
#创建一个新表并将select语句的查询结果保存到新表中
create table if not exists 表名 as select语句;
向表中插入select语句的查询结果
1、insert into table ...
#将select语句的查询结果追加插入到表中
insert into 表名 select语句;
#将into换成overwrite可以将select语句的查询结果覆盖到表中
insert overwrite table 表名 select语句;
