MySQL基础入门系列之——字符与日期数据处理

今天这一篇分享MySQL中的字符串处理工具与日期时间处理,这一部分内容虽然看似不多,但是往往是输出处理中的的痛点。

我的MySQL数据库中已经建好了一个包含两列日期与一列薪资范围字段。(深感通过R或者Python建表然后写入MySQL的效率要比使用MySQL自己的编辑器写方便!)

MySQL中的日期与时间操作涉及到的函数相当多,实际应用需要按照需要和场景来选择该使用哪一类函数,这一篇我只挑选了三类重要的日期与时间函数进行总结。

1、日期格式化:

因为本地文本文件中存储的日期导入数据库,很多情况下是当做文本的,当需要使用日期参与计算的时候需要使用日期格式化函数进行格式转化。

MySQL默认支持的日期格式是YYYY-MM-DD或者YYYY/MM/DD,所以当导进去的日期是此种格式的话,直接使用date函数即可执行。

date()

select date('2017-05-01') as date_d; 
2017-05-01
select date('2017/05/01') as date_d;
2017-05-01

str_to_date()

当日期/时间格式比较复杂或者与常用的日期结构差异较大时,可以使用str_to_date函数进行原始日期格式的声明

select str_to_date('12/08/2017','%m/%d/%Y')
2017-12-08
select str_to_date('20170422154706','%Y%m%d%H%i%s')
2017-04-22 15:47:06
select str_to_date('2017-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
2017-04-22 15:47:06

函数中第二个参数的声明方式要严格与输入的日期格式对应。

DATE_FORMAT()

如果你已经得到了一个标准日期格式的字段,想要自定义日期显示的格式,可以使用DATE_FORMAT函数,

SELECT DATE_FORMAT(date('2017-04-22 15:47:06'),'%m-%Y-%d')
04-2017-22
SELECT DATE_FORMAT('2017-04-22 15:47:06','%m-%Y')
04-2017
SELECT DATE_FORMAT(date('2017-04-22 15:47:06'),'%M')
April

FROM_UNIXTIME()

如果你拿到的原始数据是10位数值的时间戳,需要使用这个函数来进行标准时间的转化。

SELECT FROM_UNIXTIME(1504567890, '%Y-%m-%d %H:%i:%S')
2017-09-05 07:31:30

2、日期计算

date_add()  -- 加
date_sub()  -- 减
date_diff() -- 日期差值
select start_date,date_add(start_date,INTERVAL 10 DAY) as add_date from db1.str_date;
select start_date,date_sub(start_date,INTERVAL 10 DAY) as sub_date from db1.str_date;
SELECT start_date,end_date,DATEDIFF(end_date,start_date) AS DiffDate from db1.str_date;

3、日期元素的输出:

关于日期其中部分元素的输出,除了使用上面那个DATE_FORMAT()函数之外,可以直接使用单个元素函数进行提取。

year month day

select start_date,year(start_date) as date_year from db1.str_date;
select start_date,month(start_date) as date_month from db1.str_date;
select start_date,day(start_date) as date_year from db1.str_date;

quarter

weekday

select start_date,quarter(start_date) as date_quarter from db1.str_date;
select start_date,weekday(start_date) as date_weekday from db1.str_date;

hour

minute

second

select '2017-04-22 15:47:06',hour('2017-04-22 15:47:06') as date_hour;
2017-04-22 15:47:06    15
select '2017-04-22 15:47:06',minute('2017-04-22 15:47:06') as date_minute;
2017-04-22 15:47:06    47
select '2017-04-22 15:47:06',second('2017-04-22 15:47:06') as date_second;
2017-04-22 15:47:06    6

第二部分:

字符串格式化函数:

LENGTH()-- 计算字符长度
RTRIM() -- 去空格
LTRIM() -- 去左空格
TRIM()  -- 去两边的空格
UPPER() -- 首字母大写
LOWER() -- 首字符小写
SELECT LENGTH('SPACE   ') as len1,LENGTH(RTRIM('SPACE   ')) as len2;
len1   len2
8    5
SELECT LENGTH('   SPACE') as len1,LENGTH(LTRIM('SPACE   ')) as len2;
len1   len2
8    5
SELECT LENGTH('   SPACE   ') as len1,LENGTH(TRIM('   SPACE   ')) as len2;
len1   len2
11    5
SELECT LOWER('LOWER') as lows;
lower
SELECT UPPER('upper') as ups;
UPPER

字符串操纵函数:

concate()                 -- 合并与拼接字符串
CONCAT_WS()               -- 按照某种固定分隔符分割字符串
substring_index()         -- 按照固定模式分割字符串
SELECT CONCAT('2017','12','10') as date_now;
20171210
SELECT CONCAT_WS('-','2017','12','10') as date_now;
2017-12-10
SELECT CONCAT_WS('/','2017','12','10') as date_now;
2017/12/10

可以看到CONCAT_WS允许自定义拼接的间隔符,所以实用性更强一些。

substring_index函数运行我们按照特定字符出现的位置进行截取,很多时候可以起到关键作用。

使用substring_index函数处理日期元素输出:

SELECT start_date,substring_index(start_date,'-',1) as date_year from db1.str_date; -- 截取年份
SELECT start_date,substring_index(substring_index(start_date,'-',1),'-',1) as date_month from db1.str_date; -- 截取月份
SELECT start_date,substring_index(start_date,'-',-1) as date_year from db1.str_date; -- 截取天

使用substring_index函数处理带有字母的薪资区间字段:

SELECT slary,substring_index(substring_index(slary,'-',1),'k',1) as slary_low from db1.str_date; -- 截取薪资下限
SELECT slary,substring_index(substring_index(slary,'-',-1),'k',1) as slary_low from db1.str_date; -- 截取薪资上限

以下还剩余两类字符串处理函数,分别是字符串定位函数,字符串截取函数

locate()                          -- 定位特定字符位置
left()/right()/mid(Start,Length)  -- 从特定位置截取字符串
substring(string,start,length)    -- 特定位置截取固定长度字符串
replace()                         -- 字符串替换函数

locate函数可以给出字符串中某个特定字符串相对于整个字符串长度的精确位置。 left()/right()/mid()函数则可以分别从左侧、右侧或者中间某个特定位置截取固定长度的字符内容。 substring()函数则是根据某个特定字符串位置开始截取固定长度字符内容。

使用locate函数+left()/right()/mid()函数处理薪资上下限问题。

SELECT
    slary,
    LEFT (slary, LOCATE('k', slary) - 1) AS salary_low,
    replace(
        RIGHT (
            slary,
            LENGTH(slary) - LOCATE('-', slary)
        ),'k',''
    ) AS salary_upFROM
    db1.str_date;

使用substring函数处理薪资上下限问题:

SELECT
    slary,
    SUBSTRING(
        slary,1,locate('k', slary) - 1
    ) AS salary_low,
     SUBSTRING(
        slary,locate('-', slary) + 1,
        length(slary) - locate('-', slary) - 1
    ) AS salary_upFROM
    db1.str_date;

以上便是本篇关于MySQL日期/时间与字符串处理的相关学习笔记与心得总结,分享给大家,希望能够一起学习、一起进步!

往期案例数据请移步本人GitHub: https://github.com/ljtyduyu/DataWarehouse/tree/master/File

原文发布于微信公众号 - 数据小魔方(datamofang)

原文发表时间:2017-12-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏前端儿

一种排序

现在有很多长方形,每一个长方形都有一个编号,这个编号可以重复;还知道这个长方形的宽和长,编号、长、宽都是整数;现在要求按照一下方式排序(默认排序规则都是从小到大...

10620
来自专栏黑白安全

Mysql索引类型Btree和Hash的区别以及使用场景

遇到单表数据量大的时候很多开发者都会想到给相对的字段建立索引来提高性能(mysql索引的使用),但很少会去关注索引的类型该如何选择,在mysql中支持有两种类型...

26330
来自专栏机器学习入门

LWC 56:718. Maximum Length of Repeated Subarray

LWC 56:718. Maximum Length of Repeated Subarray 传送门:718. Maximum Length of Repea...

21960
来自专栏乐沙弥的世界

PL/SQL 集合的初始化与赋值

    对于集合类型,与单一的数据类型相比较而言,应该以一个整体的观念来考虑集合,即是一批类型相同的数据组合而非单一的数据。因此集 合类型集合的声明、赋值、初...

13150
来自专栏Python爱好者

判断是否为小米MIUI系统

191120
来自专栏逸鹏说道

C# 温故而知新:Stream篇(二)

TextReader 和StreamReader 目录: 为什么要介绍 TextReader? TextReader的常用属性和方法 TextReader 示例...

34950
来自专栏xingoo, 一个梦想做发明家的程序员

剑指OFFER之合并有序链表(九度OJ1519)

题目描述: 输入两个单调递增的链表,输出两个链表合成后的链表,当然我们需要合成后的链表满足单调不减规则。 (hint: 请务必使用链表。) 输入: 输入可能包含...

20080
来自专栏Laoqi's Linux运维专列

SQLAlchemy总结+

37130
来自专栏杨建荣的学习笔记

MySQL数据类型(r3笔记第87天)

今天在本地装了一个MySQL的学习环境,简单的熟悉了一下。准备开始好好学习MySQL了。 学习编程语言我都是从数据类型入手。每种编程语言的数据类型都有自己的特点...

300100
来自专栏C#

奇妙的NULL值,你知道多少

《NULL值的多义性分析》       谈到NULL值,很多人都是很熟悉,但是深入了解后,又感觉到陌生,对其含义和用法,都无法很准确的理解。NULL在数据库和...

21650

扫码关注云+社区

领取腾讯云代金券