Mysql 25道練習題——時間字符串相互轉換（工作必備）

年目標：完成7周入門數(shù)據(jù)工程師系列文章

建表語句在文末
如果你想查看MYSQL時間函數(shù)，可以看本篇文章—mysql時間與字符串之間相互轉換。

在學習SQL語言時，我們一般都是從MYSQL開始學習，并且對查詢語言掌握得很好，但很多人遇見時間格式轉換時卻無從下手，有時半天都不能解決一個問題。

如果你剛學習SQL把本篇文章一定要好好練習；如果你已進入行業(yè)一定要掌握文章內(nèi)容，最好自己總結一下。

目錄
一、Mysql中經(jīng)常用來存儲日期的數(shù)據(jù)類型
二、工作中常見的時間存儲格式
三、30道時間練習題
四、建表語句及數(shù)據(jù)插入

一、Mysql中常用來存儲日期的數(shù)據(jù)類型

在學習時間類型轉換之前，先了解下Mysql中經(jīng)常用來存儲日期的數(shù)據(jù)類型,主要有有三種：Date、Datetime、Timestamp。

Mysql中常用來存儲日期的數(shù)據(jù)類型

1、Date數(shù)據(jù)類型：用來存儲沒有時間的日期。獲取和顯示這個類型的格式為“YYYY-MM-DD”。支持的時間范圍為“1000-00-00”到“9999-12-31”
2、Datetime類型：存儲既有日期又有時間的數(shù)據(jù)。存儲和顯示的格式為 “YYYY-MM-DD HH:MM:SS”。支持的時間范圍是“1000-00-00 00:00:00”到“9999-12-31 23:59:59”。
3、Timestamp類型：也是存儲既有日期又有時間的數(shù)據(jù)。存儲和顯示的格式跟Datetime一樣,為 “YYYY-MM-DD HH:MM:SS”。支持的時間范圍是“1970-01-01 00:00:01”到“2038-01-19 03:14:07”

備注：當然啦，有些表中還會用文本數(shù)據(jù)類型來存儲時間。所以我們在進行時間格式轉換之前，首先一定要知道時間是用什么數(shù)據(jù)類型來存儲。

在MYSQL中，desc 表名，可以查看表結構

二、工作中常見的時間存儲形式

學習該部分內(nèi)容的時候，只需要看時間字段是用什么數(shù)據(jù)類型存儲，及時間在 表中顯示的格式。

1、以字符串的數(shù)據(jù)形式存儲時間
問題：不方便時間查詢和時間范圍篩選

select  *  from test_payment_two；
desc test_payment_two；

時間的數(shù)據(jù)類型為字符串

2、以字符串的數(shù)據(jù)形式存儲時間
問題：不方便時間查詢和時間范圍篩選

select * from test_payment_three;
desc test_payment_three
時間的數(shù)據(jù)類型為字符串

3、時間分別存放在年、月、日、小時4個字段中
問題：不方便時間查詢和時間范圍篩選

時間的數(shù)據(jù)類型為字符串

4、時間的數(shù)據(jù)類型為date
問題，只能存儲年月日，不能存儲小時?？梢赃M行時間范圍的查找。

select  *  from test_payment_four;
desc test_payment_four

時間的數(shù)據(jù)類型為date

5、時間的數(shù)據(jù)類型為datetime
時間的數(shù)據(jù)類型為datetime

三、25道時間練習題

01、時間轉換函數(shù)

在練習之前先看下MYSQL中的時間格式轉化的函數(shù)，我們在做練習的時候就是這兩個函數(shù)的反復運用。
第一次練習的時候一定要把這些題練完，在以后工作中記住相關的函數(shù)即可

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以后的數(shù)據(jù)
4、查詢test_payment中2019-5-1至2019-8-1之間的數(shù)據(jù)
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點” 以后的數(shù)據(jù)
11、查詢表test_payment_two中“2019-5-1”至“2019-8-1”之間的數(shù)據(jù)
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” 以后的數(shù)據(jù)
17、查詢表test_payment_three中“2019-5-1”至“2019-8-1”之間的數(shù)據(jù)
18、將表test_payment_four的paytime字段顯示為“YYYY-MM-DD”的字符串格式。
19、將表test_payment_four的paytime字段顯示為“YYYYMMDD”的字符串格式。
20、查詢表test_payment_four中 “2019-3-1” 以后的數(shù)據(jù)
21、查詢表test_payment_four中“2019-5-1”至“2019-8-1”之間的數(shù)據(jù)
22、將表test_payment_five的paytime字段顯示為“YYYY-MM-DD  HH:MM:SS”的字符串格式。
23、將表test_payment_five的paytime字段顯示為“YYYYMMDD”的字符串格式。
24、查詢表test_payment_five中 “2019-3-1” 以后的數(shù)據(jù)
25、查詢表test_payment_five中“2019-5-1”至“2019-8-1”之間的數(shù)據(jù)

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以后的數(shù)據(jù)

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之間的數(shù)據(jù)

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點” 以后的數(shù)據(jù)

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”之間的數(shù)據(jù)

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” 以后的數(shù)據(jù)

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”之間的數(shù)據(jù)

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” 以后的數(shù)據(jù)

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”之間的數(shù)據(jù)

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” 以后的數(shù)據(jù)

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”之間的數(shù)據(jù)

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')

四、建表語句及數(shù)據(jù)插入

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；
最后編輯于 ：
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

友情鏈接更多精彩內(nèi)容