SQL常用函数，数学函数，日期函数以及相互转换

常用函数

一、字符串常用函数

1.大小写转换

#测试表DUAL
#求出1+1
SELECT 1+1 FROM DUAL;
#UPPER(小写转大写)
SELECT UPPER('Abcd')FROM DUAL;
SELECT UPPER(p.pname) FROM product p;
#LOWER(大写转小写)
SELECT LOWER(p.pname) FROM product p;

2.追加字符串concat

#CONCAT(str1,str2, )追加
SELECT CONCAT('H','ello') FROM DUAL;
SELECT CONCAT(p.pname,'-->',p.price) FROM product p;

3.截取substr

#substr（“字符串”，截取位置，长度）默认索引位置从1开始
SELECT SUBSTR('Hello',1,2) FROM DUAL;

4.获取长度length

#length（字符串）  获取字符长度   英文字母占一个字节。
#中文所占字节和编码格式有关
SELECT LENGTH(c.cname) FROM category c;

5.填充lpad/rpad

#lpad(str,len,padstr)   左填充字符串
#如果第一个参数不足第二个参数的长度，就把第三个三叔填充到第一个参数的左边。
#如果第一个参数超出第二个参数指定的长度就截取第一个参数
SELECT LPAD('aaaa',5,'A') FROM DUAL;

#RPAD（str,len,padstr）右填充
SELECT RPAD('aabb',2,'A') FROM DUAL;

6.替换insert

#insert（str,substr）相当于Java中的replace方法
#第一个参数：原字符串  2.从哪个位置开始替换 3.要替换的长度  4.替换成的字符串
SELECT INSERT('Hello',1,3,'aa') FROM DUAL;

7.去除trim

#trim   剔除字符串中的某个字符串  trim（“子串” from “大串”）   
#trim（“  字符串  ”） 去除首尾空格
SELECT TRIM('     Hello    ') FROM DUAL;
SELECT TRIM('H' FROM 'Hello') FROM DUAL;

二、数学函数

#round(num)  四舍五入 
#ROUND(-12.5) 在Java里面取和自己相邻的整数.
#如果说在中间的话就取相邻的较大的那个数
#在mysql里面就是标准的四舍五入
SELECT ROUND(-12.5) FROM DUAL;
#保留多少位小数，如果长度不足不会补零，如果长度超出就截取
#保留小数会进行四舍五入
SELECT ROUND(12.35544,2) FROM DUAL;

#truncate(x,d)  截取X保留D位
#直接截取小数位数，不会四舍五入
SELECT TRUNCATE(12.377777,2) FROM DUAL;

#MOD（N，M）求余数
SELECT MOD(10,2) FROM DUAL;

三、日期函数

#now()获取当前时间 yyyy-MM-dd  HH:mm:ss
SELECT NOW() FROM DUAL;

#curdate()     年月日
SELECT CURDATE() FROM DUAL;

#current()_date  年月日
SELECT CURRENT_DATE() FROM DUAL;

#current_time()  时分秒
SELECT CURRENT_TIME() FROM DUAL; 

#curtime()              时分秒 
SELECT CURTIME() FROM DUAL;

#current_timestamp  时间戳   年月日时分秒
SELECT CURRENT_TIMESTAMP() FROM DUAL;

#SYSDATE() 年月日时分秒  
SELECT SYSDATE() FROM DUAL;

#DATE_ADD(CURRENT_DATE(),INTERVAL 1 YEAR)  当前时间加1年
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR) FROM DUAL;
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH) FROM DUAL;
SELECT DATE_ADD(NOW(),INTERVAL 1 WEEK) FROM DUAL;

#DATEDIFE(CURDATE,"2008-5-12");  日期相减
SELECT DATEDIFF(CURDATE(),'2008-5-12') FROM DUAL;

#TIMEDIFF(TIME1,TIME2)时间相减   CURRENT_TIME,"12:00:00";
SELECT TIMEDIFF(CURTIME(),'12:00:00') FROM DUAL;

#TIMESTAMP("2018-01-11","01:01:01")   拼接时间字符串
SELECT TIMESTAMP('2020-03-01','11:39:00') FROM DUAL;

#TIMESTAMPDIFF(YEAR,"2008-01-01 12:12:12","2010-01-01 12:12:12")
SELECT TIMESTAMPDIFF(WEEK,'2008-01-01 12:12:12','2020-03-01 12:12:12') FROM DUAL;

四、数字字符串转换

# concat 数字转字符串
SELECT CONCAT(8,1) FROM DUAL;

#CAST('123' AS SIGNED) 字符串转数字
#如果遇到非数字就停止转换了
SELECT CAST('123AA4' AS SIGNED) FROM DUAL;

五、日期和字符串的相互转换

m(数字的月)/M(英文表示的月)

format y(两位数的年)/Y(四位数的年)

d(数字表示的日)/D(英文表示的日)

h(12进制小时)/H(24进制小时)

i表示分钟

S/s表示秒

#DATE_FROMAT(date,format)
SELECT DATE_FORMAT(NOW(),'%Y-%M-%D %H:%i:%s') FROM DUAL;

#字符串转时间
#STR_TO_DATE(str,format)
#STR_TO_DATE(‘2018-5-23’，‘%Y-%m-%d’);  
SELECT STR_TO_DATE('2018-05-23','%Y-%m-%d') FROM DUAL;

六、其他

#IFNULL(EX1,EX2)      EX2为空就返回EX2
#条件运算？结果1：结果2
#COALESCE（'','',''）从左到右返回第一个非空的
SELECT IFNULL('走我','走你') FROM DUAL;
SELECT COALESCE(null,'这里','在这里') FROM DUAL;

#case条件表达式
#相当于 switch语句
SELECT *,
CASE
WHEN p.cno=1 THEN '手机数码'
WHEN p.cno=2 THEN '鞋子包包'
ELSE '其他'
END as 商品分类
FROM product p;