MySQ基础入门系列之——字符与日期数据处理
来源:互联网 发布:java api 1.6英文版 编辑:程序博客网 时间:2024/06/05 16:50
今天这一篇分享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-01select date('2017/05/01') as date_d;2017-05-01str_to_date()
当日期/时间格式比较复杂或者与常用的日期结构差异较大时,可以使用str_to_date函数进行原始日期格式的声明
select str_to_date('12/08/2017','%m/%d/%Y')2017-12-08select str_to_date('20170422154706','%Y%m%d%H%i%s')2017-04-22 15:47:06select 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-22SELECT DATE_FORMAT('2017-04-22 15:47:06','%m-%Y')04-2017SELECT 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 len28 5SELECT LENGTH(' SPACE') as len1,LENGTH(LTRIM('SPACE ')) as len2;len1 len28 5SELECT LENGTH(' SPACE ') as len1,LENGTH(TRIM(' SPACE ')) as len2;len1 len211 5SELECT LOWER('LOWER') as lows;lowerSELECT UPPER('upper') as ups;UPPER
字符串操纵函数:
concate() -- 合并与拼接字符串CONCAT_WS() -- 按照某种固定分隔符分割字符串substring_index() -- 按照固定模式分割字符串SELECT CONCAT('2017','12','10') as date_now;20171210SELECT CONCAT_WS('-','2017','12','10') as date_now;2017-12-10SELECT 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,startlength -- 特定位置截取固定长度字符串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日期/时间与字符串处理的相关学习笔记与心得总结,分享给大家,希望能够一起学习、一起进步!
在线课程请点击文末原文链接:
Hellobi Live | R语言可视化在商务场景中的应用
往期案例数据请移步本人GitHub: https://github.com/ljtyduyu/DataWarehouse/tree/master/File
转载地址: https://zhuanlan.zhihu.com/p/31899865?group_id=923529632306143232
- MySQ基础入门系列之——字符与日期数据处理
- mysq日期
- 海量数据处理系列——十道海量数据处理面试题与十个方法大总结
- Jenkins入门系列之—— Jenkins安装与配置
- Java基础系列之日期时间处理
- Jenkins入门系列之— Jenkins安装与配置
- Jenkins入门系列之—Jenkins安装与配置
- Java入门基础之【日期时间】14
- c#入门系列——基础篇
- java基础系列—入门篇
- MySQ中CHAR与VARCHAR之争
- python入门系列5―——时间日期
- Marlin固件之—:基础入门与测试
- mysq 数据库 日期函数
- mysq 数据库 日期函数
- mysq 日期相减
- IC设计基础系列之低功耗篇1:(数字IC)低功耗设计入门(一)——低功耗设计目的与功耗的类型
- IC设计基础系列之低功耗篇3:数字IC)低功耗设计入门(三)——系统与架构级低功耗设计
- 40张技术图谱,架构师阶梯 (附高清下载)
- 这7个不可错过的数据可视化技术,能让地图惊喜跃动
- 阿里机器人都会批改作文了!深圳无人驾驶公交车试运行;360 开源深度学习平台 XLearning;
- set()赋值 get()得到该属性
- MySql导出表结构(语句导出,navicat工具即可)
- MySQ基础入门系列之——字符与日期数据处理
- Mac下nginx安装和配置
- Ehcache 学习笔记
- redis5种数据结构讲解及使用场景
- dedecms自定义表单发送到邮箱--163邮箱发送
- CentOS下安装JDK的三种方法
- nohup 命令详解
- 列表禁止点击的效果样式
- Flume断点续传深入研究