前面几篇文章已经接触过一系列SQL函数,本文重点介绍文本和字符串处理函数,日期和时间处理函数,数值处理函数,便于用户编写sql处理格式化、过滤和数据。同时注意,虽然每种数据库管理系统都提供各种函数,但是具体实现差异都比较大,不可移植。我们学习函数,第一有可能很多查询不用函数无法实现,第二虽然很多函数不可移植或者通用,但是每种数据库系统都提供类似功能的函数。
一、日期处理函数
一般数据库中的日期时间类型有date、time、datetime和timestamp,相关函数的主要功能就是处理相关格式转化以及和字符串的转化。
SELECT NOW(),YEAR(NOW()),MONTH(NOW()),DAY(NOW());
SELECT NOW(),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
SELECT DAYNAME('2019-05-03'),MONTHNAME('2019-05-03'),QUARTER('2019-05-03');
DAYOFWEEK(date):返回日期date的星期索引(1=星期天,2=星期一, …7=星期六)。
SELECT NOW(),DAYOFWEEK(NOW()),DAYOFMONTH(NOW()),DAYOFYEAR(NOW());
DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作,可以精确到秒
DATE_SUB(date,INTERVAL expr type) ,进行日期减少的操作,可以精确到秒
SELECT DATE_SUB(CURDATE(), INTERVAL 31 DAY), CURDATE(),DATE_ADD(CURDATE(),INTERVAL 2 DAY);
SELECT NOW(),DATE_ADD(NOW(), INTERVAL "1:1" HOUR_MINUTE), DATE_ADD(NOW(), INTERVAL "3" HOUR);
SELECT CURTIME(),CURDATE(),NOW();
DATE_FORMAT(date,format)函数用于把日期转化各种格式,下表是相关格式说明。
SELECT DATE_FORMAT(NOW(),'%m-%d-%Y'),DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
str_to_date('2019-10-16 18:30:24','%Y-%m-%d %H:%i:%s')函数类似和dateformat效果。
时间戳timestamp也是sql常用的一种时间类型。
select from_unixtime(1488160428,'%Y-%m-%d %T'),unix_timestamp(now());
timestampdiff(interval, datetime1,datetime2),返回时间差,结果单位由interval参数给出,interval有second,minute,hour,day,month,year,week,quarter等。
SELECT TIMESTAMPDIFF(HOUR,'2019-10-01','2020-01-13'), DATEDIFF('2019-10-01','2020-01-13');
二、字符串处理
拼接字段:CONCAT(str1,str2,...):返回来自于参数连结的字符串,可以有多个参数。
SELECT CONCAT(NAME,'(',department,')') FROM student;
SELECT LOWER('TEST'), UPPER('test') FROM student;处理大小写转化。
注意:sql关键词本身大小写不敏感,也就是selete,from等大小写不影响。
Length返回字符串长度:
SELECT NAME,LENGTH(NAME),department,LENGTH(department) FROM student;
trim(char from x) 删除首部、尾部字符,相应的LTRIM和RTRIM处理首部和尾部。
SELECT LTRIM(' student '),TRIM(' student '),RTRIM(student ');
substr(字符串,start [,length]) 取子串
SELECT SUBSTR(NAME,1,1),NAME,SUBSTR(department,1,LENGTH(department)/3-1), department FROM student;
replace(x,search_string,replace_string) 字符替换
SELECT REPLACE(department,'系','学院'),department FROM student;
三、数值处理函数
Abs:返回绝对值
Exp:值e的n次方,自然对数的底
Sqrt:返回非负数n的平方根
Pow:返回值x的y次幂
Mod或者%:返回N被M除的余数
Ceiling:不小于X的最小整数值
Round:四舍五入的一个整数
SELECT ABS(-45),CEILING(1.34),ROUND(5.67);
SELECT POW(3,2),SQRT(4),EXP(2),MOD(5,2);
其他函数还有汇总函数,条件函数,可以参考前面几篇文章。
SQL函数包括大量的可用函数,包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数可以简化用户的操作,使用时可以查询相关资料和手册。