MYSQL-常用的单行函数：数值函数与字符串函数

数值函数

ABS(X)

• 返回参数的绝对值

SELECT ABS(-5.36),ABS(98.23),ABS(0); # -5.36 98.23 0

SIGN(X)

• 返回参数的符号，x 的值为负、零、正和NULL时返回结果依次为 -1、0 和、1和NULL；

SELECT SIGN(-56.25),SIGN(0),SIGN(62),SIGN(NULL); # -1 0 1 NULL

PI()

• π值

SELECT PI();

CEIL(X)/CEILING(X)

• 向上取整，返回大于等于括号内该值的最大整数

SELECT CEIL(56.23),CEILING(-56.69); # 57 -56

FLOOR(X)

• 向下取整：返回小于等于括号内该值的最大整数

SELECT FLOOR(23.56),FLOOR(99.36); # 23 99

MOD()

• 取模，返回余数

SELECT 13 MOD 5,MOD (12, 5),12 % 5;# 3 2 2

RAND()

• 取0-1之间的随机数

SELECT RAND(),RAND(10),RAND(-1) FROM DUAL;
SELECT (RAND()*5)+5 FROM DUAL;# 返回一个5-10之间的随机数
select lpad(floor(rand() * 1000000), 6, '0');# 返回六位随机数

ROUND()

• 四舍五入，且可以小数位四舍五入

SELECT ROUND(-23.56),ROUND(-56.36,1),ROUND(1548.965,2) FROM DUAL;# -24 -56.4 1548.97

TRUNCATE()

• 截断数据，可以根据设置的小数位数进行截断

SELECT TRUNCATE(789.412,0),TRUNCATE(785.496,1),TRUNCATE(859.43,-1),TRUNCATE(-859.43,1),TRUNCATE(-859.43,0);
SELECT TRUNCATE(ROUND(123.456,2),0)

其他函数

#三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1));

#指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2);
SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4);

#进制间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8);

字符串函数

ASCII(str)

• 返回与指定字符对应的十进制整数

SELECT ASCII('HELLO');

CHAR_LENGTH()

• 返回值为字符串string或者对应字段长度

SELECT CHAR_LENGTH('hello'),CHAR_LENGTH('我们');# 5 2

LENGTH()

• utf8字符集编码下，一个汉字是算三个字符，一个数字或字母算一个字符。
• 其他编码下，一个汉字算两个字符， 一个数字或字母算一个字符。

SELECT LENGTH('hello'),LENGTH('我们');# 5 6

UPPER(str)

SELECT UPPER('refine');# REFINE

LOWER(str)

SELECT LOWER('REFINE')

CONCAT(str1,str2,…)

• 连接字符串

SELECT CONCAT('hello','world','hi','shanghai');# helloworldhishanghai

CONCAT_WS(separator,str1,str2,…)

• 用连接符连接字符串

SELECT CONCAT_WS(' ','hello','world','hi','shanghai');# hello world hi shanghai

REPLACE(str,from_str,to_str)

• 用to_str替换str中的from_str

SELECT REPLACE('refine','e','UU');# rUUfinUU

SUBSTR()

• 截取字符串

SELECT SUBSTR('refine',2);# efine
SELECT SUBSTR('refine',2,4);# efin

LEFT(str,len)

• 获取字符串左边len个字符组成的字符串

SELECT LEFT('yupaochangjian',5); # yupao

RIGHT(str,len)

• 获取字符串右边len个字符组成的字符串

SELECT RIGHT('yupaochangjian',9); # changjian

MID(str,pos,len)

• 获取字符串中从pos(第几个)位置开始，长度为len的字符串

SELECT MID('yupaochangjian',6,5);# chang

LTRIM(str)

• 去掉字符串左边的空格

SELECT LTRIM('   yupaochangjian   '); #yupaochangjian   ;

RTRIM(str)

• 去掉字符串右边的空格

SELECT RTRIM('   yupaochangjian   '); #   yupaochangjian

TRIM([remstr FROM] str)

• 去掉字符串左右空格

SELECT TRIM('   yupaochangjian   '); #yupaochangjian

INSTR(str,substr)

• 查找一个子字符串是否在指定的位置上出现

SELECT INSTR("refine","re"),INSTR("refine","12");# 1 0

LPAD(str,len,padstr)

• 实现左对齐效果

SELECT employee_id,last_name,salary,LPAD(salary,12,"-") FROM employees;
# ----24000.00

RPAD(str,len,padstr)

• 实现右对齐效果

SELECT employee_id,last_name,salary,RPAD(salary,12,"-") FROM employees;
# 24000.00----

REPEAT(str,count)

SELECT REPEAT("refine",5);# refinerefinerefinerefinerefine

SPACE(N)

• 返回N个空格

SELECT LENGTH(SPACE(7));

STRCMP(expr1,expr2)

• STRCMP()函数用于比较两个字符串。
• 如果两个字符串相同，则返回0
• 如果根据定义的顺序第一个参数小于第二个参数，则返回-1
• 如果第二个参数小于第一个参数时，返回1

SELECT STRCMP('refine','refinf'),STRCMP('refine','refine'),STRCMP('refine','refind');
# -1 0 1

LOCATE(substr,str)

• 判断str字符串是否包含substr字符串，如
• Locate(str,sub) > 0，表示sub字符串包含str字符串；
• Locate(str,sub) = 0，表示sub字符串不包含str字符串。

SELECT LOCATE('ef','refine'),LOCATE('rt','refine');

NULLIF(expr1,expr2)

• 如果expr1等于expr2，则返回NULL，否则返回expr1。

SELECT NULLIF("refine","hello"),NULLIF('refine','refine');# hello NULL

ELT(N,str1,str2,str3,…)

• 返回后面的多个字符串中第N个字符串的值
• 当索引的值小于1或者大于后面参数的个数时，函数返回null

SELECT ELT(2,'refine','lily','jim','tom');# lily

FIELD(str,str1,str2,str3,…)

• 返回str在后面的多个字符串中出现的位置

SELECT FIELD('dd','gg','jj','mm','dd','mm');# 4

• 可以用来对SQL中查询结果集进行指定顺序排序

SELECT
	*
FROM
	employees
WHERE
	last_name IN (
		"Kochhar",
		"De Haan",
		"Pataballa"
	)
ORDER BY
	FIELD(
		last_name,
		"Kochhar",
		"Pataballa",
		"De Haan"
	) ASC;

FIND_IN_SET(str,strlist)

• 返回str在strlist中的位置
• 如果str不在strlist 或strlist 为空字符串，则返回值为 0
• 如果任意一个参数为NULL，则返回值为 NULL

SELECT
	FIND_IN_SET('gg', 'gg,mm,jj,dd,mm,NULL'),
	FIND_IN_SET('tt', 'gg,mm,jj,dd,mm,NULL'),
	FIND_IN_SET('NULL', 'gg,mm,jj,dd,mm,NULL'),
	FIND_IN_SET(NULL, 'gg,mm,jj,dd,mm,NULL'),
	FIND_IN_SET('gg', NULL)
	# 1 0 6 NULL NULL