Mysql 25道练习题——时间字符串相互转换（工作必备）

年目标：完成7周入门数据工程师系列文章

建表语句在文末
如果你想查看MYSQL时间函数，可以看本篇文章—mysql时间与字符串之间相互转换。

在学习SQL语言时，我们一般都是从MYSQL开始学习，并且对查询语言掌握得很好，但很多人遇见时间格式转换时却无从下手，有时半天都不能解决一个问题。

如果你刚学习SQL把本篇文章一定要好好练习；如果你已进入行业一定要掌握文章内容，最好自己总结一下。

目录
一、Mysql中经常用来存储日期的数据类型
二、工作中常见的时间存储格式
三、30道时间练习题
四、建表语句及数据插入

一、Mysql中常用来存储日期的数据类型

在学习时间类型转换之前，先了解下Mysql中经常用来存储日期的数据类型,主要有有三种：Date、Datetime、Timestamp。

Mysql中常用来存储日期的数据类型

1、Date数据类型：用来存储没有时间的日期。获取和显示这个类型的格式为“YYYY-MM-DD”。支持的时间范围为“1000-00-00”到“9999-12-31”
2、Datetime类型：存储既有日期又有时间的数据。存储和显示的格式为 “YYYY-MM-DD HH:MM:SS”。支持的时间范围是“1000-00-00 00:00:00”到“9999-12-31 23:59:59”。
3、Timestamp类型：也是存储既有日期又有时间的数据。存储和显示的格式跟Datetime一样,为 “YYYY-MM-DD HH:MM:SS”。支持的时间范围是“1970-01-01 00:00:01”到“2038-01-19 03:14:07”

备注：当然啦，有些表中还会用文本数据类型来存储时间。所以我们在进行时间格式转换之前，首先一定要知道时间是用什么数据类型来存储。

在MYSQL中，desc 表名，可以查看表结构

二、工作中常见的时间存储形式

学习该部分内容的时候，只需要看时间字段是用什么数据类型存储，及时间在 表中显示的格式。

1、以字符串的数据形式存储时间
问题：不方便时间查询和时间范围筛选

select  *  from test_payment_two；
desc test_payment_two；

时间的数据类型为字符串

2、以字符串的数据形式存储时间
问题：不方便时间查询和时间范围筛选

select * from test_payment_three;
desc test_payment_three
时间的数据类型为字符串

3、时间分别存放在年、月、日、小时4个字段中
问题：不方便时间查询和时间范围筛选

时间的数据类型为字符串

4、时间的数据类型为date
问题，只能存储年月日，不能存储小时。可以进行时间范围的查找。

select  *  from test_payment_four;
desc test_payment_four

时间的数据类型为date

5、时间的数据类型为datetime
时间的数据类型为datetime

三、25道时间练习题

01、时间转换函数

在练习之前先看下MYSQL中的时间格式转化的函数，我们在做练习的时候就是这两个函数的反复运用。
第一次练习的时候一定要把这些题练完，在以后工作中记住相关的函数即可

1.时间转字符串
DATE_FORMAT(日期，格式字符串)，也可以将字符串转化为字符串的格式
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

2.字符串转时间
STR_TO_DATE(字符串，日志格式)
SELECT STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s');

02、使用的表

test_payment
test_payment_two
test_payment_three
test_payment_four
test_payment_five

03、练习题

1、将test_payment中year、month day hour四个字段拼接在一起，显示为“YYYY-MM-DD HH:MM:SS”的时间格式。
2、将test_payment中year、month day四个字段拼接在一起，显示为“YYYYMMDD”的字符串格式。
3、查询test_payment中2019-5-1以后的数据
4、查询test_payment中2019-5-1至2019-8-1之间的数据
5、将表test_payment_two的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的时间格式。
6、将表test_payment_two的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的字符串格式。
7、将表test_payment_two的paytime字段显示为“YYYY-MM-DD”的的时间格式。
8、将表test_payment_two的paytime字段显示为“YYYYMMDD”的的字符串格式。
9、将表test_payment_two的paytime字段显示为“YYYYMMDD”的的字符串格式。
10、查询表test_payment_two中 “2019-5-1 9点” 以后的数据
11、查询表test_payment_two中“2019-5-1”至“2019-8-1”之间的数据
12、将表test_payment_three的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的时间格式。
13、将表test_payment_three的paytime字段显示为“YYYY-MM-DD”的时间格式。
14、将表test_payment_three的paytime字段显示为“YYYYMMDD”的字符串格式。
15、将表test_payment_three的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的字符串格式。
16、查询表test_payment_three中 “2019-3-1” 以后的数据
17、查询表test_payment_three中“2019-5-1”至“2019-8-1”之间的数据
18、将表test_payment_four的paytime字段显示为“YYYY-MM-DD”的字符串格式。
19、将表test_payment_four的paytime字段显示为“YYYYMMDD”的字符串格式。
20、查询表test_payment_four中 “2019-3-1” 以后的数据
21、查询表test_payment_four中“2019-5-1”至“2019-8-1”之间的数据
22、将表test_payment_five的paytime字段显示为“YYYY-MM-DD  HH:MM:SS”的字符串格式。
23、将表test_payment_five的paytime字段显示为“YYYYMMDD”的字符串格式。
24、查询表test_payment_five中 “2019-3-1” 以后的数据
25、查询表test_payment_five中“2019-5-1”至“2019-8-1”之间的数据

1、将test_payment中year、month day hour四个字段拼接在一起，显示为“YYYY-MM-DD HH:MM:SS”的时间格式。

select  STR_TO_DATE(concat(concat_ws('-',year,month,day),' ',hour), '%Y-%m-%d %H:%i:%s')paytime,product_code,money
  from test_payment


2、将test_payment中year、month day四个字段拼接在一起，显示为“YYYYMMDD”的字符串格式。

select  DATE_FORMAT(STR_TO_DATE(concat_ws('-',year,month,day),
'%Y-%m-%d'),'%Y%m%d')paytime,product_code,money
  from test_payment

image.png

3、查询test_payment中2019-5-1以后的数据

select *  from test_payment
where STR_TO_DATE(concat_ws('-',year,month,day),'%Y-%m-%d')>='2019-5-1'

4、查询test_payment中2019-5-1至2019-8-1之间的数据

select *  from test_payment
where STR_TO_DATE(concat_ws('-',year,month,day),'%Y-%m-%d')between '2019-5-1' and '2019-8-1'

5、将表test_payment_two的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的时间格式。

select STR_TO_DATE(paytime, '%Y-%m-%d %H:%i:%s')paytime ,product_code,money from test_payment_two

6、将表test_payment_two的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的字符串格式。显示为24小时的格式

select DATE_FORMAT(paytime, '%Y-%m-%d %H:%i:%S')paytime ,product_code,money 
from test_payment_two

7、将表test_payment_two的paytime字段显示为“YYYY-MM-DD”的的时间格式。

select STR_TO_DATE(paytime, '%Y-%m-%d')paytime ,product_code,money from test_payment_two

8、将表test_payment_two的paytime字段显示为“YYYYMMDD”的的字符串格式。

select DATE_FORMAT(paytime, '%Y%m%d')paytime ,product_code,money from test_payment_two

9、将表test_payment_two的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的的时间格式。

select STR_TO_DATE(paytime , '%Y-%m-%d %H:%i:%s')paytime ,product_code,money 
from test_payment_two

10、查询表test_payment_two中 “2019-5-1 9点” 以后的数据

select * from test_payment_two where STR_TO_DATE(paytime , '%Y-%m-%d %H:%i:%s')>='2019-5-1 9'

11、查询表test_payment_two中“2019-5-1”至“2019-8-1”之间的数据

select * from test_payment_two
where STR_TO_DATE(paytime , '%Y-%m-%d %H:%i:%s')between '2019-5-1' and '2019-8-1'

12、将表test_payment_three的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的时间格式。

select str_to_date(DATE_FORMAT(paytime , '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')paytime,product_code,money 
from test_payment_three

13、将表test_payment_three的paytime字段显示为“YYYY-MM-DD”的时间格式。

select str_to_date(DATE_FORMAT(paytime , '%Y-%m-%d'), '%Y-%m-%d')paytime,product_code,money 
from test_payment_three

14、将表test_payment_three的paytime字段显示为“YYYYMMDD”的字符串格式。

select DATE_FORMAT(paytime , '%Y%m%d')paytime,product_code,money 
from test_payment_three

15、将表test_payment_three的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的字符串格式。

select DATE_FORMAT(paytime , '%Y-%m-%d %H:%i:%s')paytime,product_code,money 
from test_payment_three

16、查询表test_payment_three中 “2019-3-1” 以后的数据

select * from test_payment_three
where paytime  >=DATE_FORMAT('2019-3-1' , '%Y%m%d')

17、查询表test_payment_three中“2019-5-1”至“2019-8-1”之间的数据

select * from test_payment_three where paytime  between DATE_FORMAT('2019-5-1' , '%Y%m%d')
and DATE_FORMAT('2019-8-1' , '%Y%m%d')

18、将表test_payment_four的paytime字段显示为“YYYY-MM-DD”的字符串格式。

select DATE_FORMAT(paytime , '%Y-%m-%d')paytime,product_code,money 
 from test_payment_four

19、将表test_payment_four的paytime字段显示为“YYYYMMDD”的字符串格式。

select DATE_FORMAT(paytime , '%Y%m%d')paytime,product_code,money 
 from test_payment_four

20、查询表test_payment_four中 “2019-3-1” 以后的数据

select * from test_payment_four where paytime > STR_TO_DATE('2019-3-1', '%Y-%m-%d')

21、查询表test_payment_four中“2019-5-1”至“2019-8-1”之间的数据

select * from test_payment_four where paytime between  STR_TO_DATE('2019-3-1', '%Y-%m-%d')
and STR_TO_DATE('2019-8-1', '%Y-%m-%d')

22、将表test_payment_five的paytime字段显示为“YYYY-MM-DD HH:MM:SS”的字符串格式。

select DATE_FORMAT(paytime, '%Y-%m-%d %H:%i:%s')paytime,product_code,money  from test_payment_five

23、将表test_payment_five的paytime字段显示为“YYYYMMDD”的字符串格式。

select DATE_FORMAT(paytime, '%Y%m%d')paytime,product_code,money  from test_payment_five

24、查询表test_payment_five中 “2019-3-1” 以后的数据

select *  from test_payment_five where paytime > STR_TO_DATE('2019-3-1', '%Y-%m-%d')

25、查询表test_payment_five中“2019-5-1”至“2019-8-1”之间的数据

select *  from test_payment_five where paytime between  STR_TO_DATE('2019-3-1', '%Y-%m-%d')
and STR_TO_DATE('2019-8-1', '%Y-%m-%d')

四、建表语句及数据插入

create table test_payment(
    year varchar(10),
    month varchar(10),
    day varchar(10),
    hour varchar(10),
    product_code varchar(30),
    money varchar(20)
)；
CREATE TABLE test_payment_two  ( 
    paytime         varchar(20) NULL,
    product_code    varchar(30) NULL,
    money           varchar(20) NULL 
    )；
CREATE TABLE test_payment_three  ( 
    paytime         varchar(20) NULL,
    product_code    varchar(30) NULL,
    money           varchar(20) NULL 
    )；
create table test_payment_four(
    paytime date,
    product_code varchar(30),
    money varchar(20)
)；  
create table test_payment_five(
    paytime datetime,
    product_code varchar(30),
    money varchar(20)
)；

insert  into test_payment values('2019','1','3','12','M2202','89');
insert  into test_payment values('2019','2','4','13','M2201','90');
insert  into test_payment values('2019','3','5','14','M2201','91');
insert  into test_payment values('2019','4','3','12','M2202','89');
insert  into test_payment values('2019','5','4','13','M2201','90');
insert  into test_payment values('2019','5','4','16','M2201','99');
insert  into test_payment values('2019','6','5','14','M2201','91');
insert  into test_payment values('2019','7','5','14','M2201','91');
insert  into test_payment values('2019','8','3','12','M2202','89');
insert  into test_payment values('2019','8','24','12','M2201','89');
insert  into test_payment values('2019','9','4','13','M2201','90');

insert into test_payment_two
select concat_ws(' ',concat_ws('-',year,month,day),hour)paytime,product_code,money 
from test_payment；

insert into test_payment_three
SELECT 
DATE_FORMAT(STR_TO_DATE(concat_ws('-',year,month,day), '%Y-%m-%d'), '%Y%m%d') paytime,
product_code,money from test_payment；
 
insert into test_payment_four
SELECT 
STR_TO_DATE(concat_ws('-',year,month,day), '%Y-%m-%d') paytime,
product_code,money from test_payment；
 
insert into test_payment_five
SELECT 
STR_TO_DATE(concat(concat_ws('-',year,month,day),' ',hour), '%Y-%m-%d %H:%i:%s') paytime,
product_code,money from test_payment；
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 215,723评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,003评论 3赞 391
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 161,512评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,825评论 1赞 290
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,874评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,841评论 1赞 295
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,812评论 3赞 416
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,582评论 0赞 271
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,033评论 1赞 308
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,309评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,450评论 1赞 345
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,158评论 5赞 341
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,789评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,409评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,609评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,440评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,357评论 2赞 352