包括数学函数、字符串函数、日期和时间函数、条件函数、系统信息函数、加密函数等等

一、数学函数

常见的数学函数有：绝对值函数、三减函数（正弦、余弦、正切、余切）、对数函数、随机数函数。

有错误产生时，数学函数会返回空值NULL

1、ABS(X)：返回绝对值

eg>select abs(-100);

PI():返回圆周率

eg>select pi();

2、SQRT(X)返回非负数X的二次方根

eg>select sqrt(9),sqrt(0),sqrt(-100)

MOD(X,Y)：返回x除以y的余数

eg>select mod(100,9),mod(-100,9),mod(100,-9),mod(-100,-9);

3、获取整数的函数

CEIL(X) == CEILING(X) :返回一个不小于X的最小整数值，即在数轴上靠右的最小整数

eg>select ceil(-3.14),CEIL(3.14),CEILING(-3.14),CEILING(3.14);

FLOOR(X):返回一个不大于X的最大整数值，即在数轴上靠左的最大整数

eg>select floor(-3.14),floor(3.14);

4、获取随机数的函数

RAND():不带参数，每次产生的随机数值不一样

eg>select rand(),rand(),rand();

RAND(X):参数x相同时，产生相同的随机数；参数x不同时，产生不同的随机数

eg>select rand(3.14),rand(3.14),rand(3);

5、四舍五入函数

ROUND(X)：对x四舍五入为整数

eg>select round(-1.11),round(-1.77),round(1.11),round(1.77);

ROUND(X,Y)：四舍五入，保留到小数点后y位；y为负数则将保留到小数点左边y位，左边的相应位数直接保存为0

eg>select round(3.14,1),round(3.1415926,5),round(233.2345,-2)

TRUNCATE(X,Y):返回舍去小数点后y位的数字x。y==0，则不带小数；若y<0，则截除（归零）小数点左起第y位开始后面所有低位的值

eg>SELECT TRUNCATE(3.14,1),TRUNCATE(-13.14,-1),TRUNCATE(3.14,0);

6、符号函数SIGN(X),返回参数的符号

eg>select sign(11),sign(0),sign(-11);

7、幂函数运算

POWER(X,Y) == POW(X,Y)：返回x的y次幂

EXP(X):返回e的x次幂

eg>select power(10,2),pow(10,-2),exp(pi());

8、对数函数

LOG(X):返回x相对于基数e的对数（自然对数）

LOG10(X):返回x的相对于基数10的对数

eg>select log(100),log10(100),log10(-1),log(23.14);

9、角度、弧度互转函数

RADIANS(X):角度--弧度

DEGREES(X):弧度--角度

eg>select radians(180),radians(90),degrees(3.1415926);

10、三角函数

正弦函数：SIN(X)

反正弦函数：ASIN(X)

余弦函数：COS(X)

反余弦函数：ACOS(X)

正切函数：TAN(X)

反正切函数：ATAN(X)

余切函数：COT(X)

eg> SELECT sin(pi()),asin(1),cos(0),acos(1),tan(1),atan(1),cot(-1);

二、字符串函数

包括计算字符串长度函数、字符串合并函数、字符串比较函数、查找指定字符串位置函数

1、字符数、字节数

CHAR_LENGTH(str):返回字符个数

length(str):返回字符串字节数汉字是3个字节

>>> select char_length('张三111a'),length('张三111a');

2、字符串合并函数

concat(s1,s2,...):任意一个参数为null，则返回null

concat_ws(x,s1,s2):第一个参数x是其他参数的分隔符，分隔符null，则返回值null；函数会忽略分隔符参数后的null值

>>> select concat('1',' ',',','2'),concat(1,null),concat_ws(',','a','b','1'),concat_ws(null,'a','b');

3、替换字符串的函数

insert(s1,x,len,s2):s1从x（x从1开始），后面的len字符，被s2替换。即'mysql5.24'，从第二位'y'开始4位字符【ysql】被【oracle】替换，最后成为【moracle5.24】

>>> select insert('mysql5.24',2,4,'oracle')

4、大小写转换

LOWER(S1) == LCASE(S1) :所有字母字符转换为小写

UPPER(S1) == UCASE(S1):所有字母字符转换为大写

>>> select upper('Xsas**ak'),ucase('[email protected]'),lower('安徽MOU'),lcase('wangJie11');

5、获取指定长度的字符串函数

LEFT(S,N)：返回字符串s从左边开始的n个字符串

RIGHT(S,N):返回字符串从右边开始的n个字符串

>>> select left('mysql',4),right('oracle',3);

6、填充字符串的函数

LPAD(S1,LEN,S2):返回字符串s1，其左边由s2填充，若s1长度大于len，则返回值缩短至len字符

RPAD(S1,LEN,S2):返回字符串s1，其右边由s2填充，若s1长度大于len，则返回值缩短至len字符

>>> select lpad('hello',4,'???'),lpad('hello',10,'>>'),rpad('hello',4,'??'),rpad('hello',10,'>>');

7、删除空格函数

LTRIM(S):删除左边的空格

RTRIM(S)：删除右边的空格

TRIM(S)：删除左右两边的空格

>>> select concat('[',ltrim('  jay  '),']'),concat('[',rtrim('  jay  '),']'),concat('[',trim('  jay  '),']');

8、删除指定字符串

TRIM(S1 FORM S2):删除字符串s1两端的所有子字符串s2

>>> select trim('mj' from 'mjmj*mj**mjmj')

9、重复生成字符串的函数

REPEAT(S,N)，重复的字符串s组成的字符串，若s或n为null，返回null；若n<=0返回一个空字符串

>>> select repeat('jackma',10)

10、空格函数SPACE(N):返回n个空格组成的字符串

REPLACE(s,s1,s2):使用字符串s2替代s中的所有s1

>>> select concat('[',space(4),']'),replace('abcdefgg1g^g#g','g','$');

11、字符串比较大小函数

STRCMP(S1,S2):字符串相等返回0；字符串s1>s2,返回1；s1<s2,返回-1

>>> select strcmp('ttt','ttt'),strcmp('张安顺','张安顺1'),strcmp('张安顺1','张安顺');

12、获取子串的函数

SUNSTRING(S,N,LEN) == MID(S,N,LEN):从字符串s返回一个长度为len的子串，起始位置n，n<0，则起始位置为倒数n开始

>>> select substring('microsoftSqlserver',10,9),mid('oracle',-3,2);

13、匹配子串开始位置的函数

LOCATE(STR1,STR)

POSITION(STR1 IN STR)

INSTR(STR,STR1)

三个函数作用一致；返回str1在str中的起始位置

>>> select locate('mj','scmj1999'),instr('scmj1999','mj'),position('mj' in 'scmj1999');

14、字符串翻转函数

REVERSE(x)

>>> SELECT REVERSE('abcdefg');

15、返回指定位置的字符串

ELT(N,STR1,STR2,...,STRN),返回第n个字符串，若n<1或者大于参数数目，返回null

>>> select elt(2,'mj','jm','sajd'),elt(4,'zs','ls');

16、返回指定字符串位置的函数

FIELD(S,S1,S2,...,):返回字符串s在字符串列表sn的第一次出现的位置。s为null，返回0；找不到s，返回0

>>> select field('a','abc','ac'),field('a','b','a')

17、返回子串位置的函数FIND_IN_SET(S1,S2)

返回s1在字符串s2中出现的位置，s2是多个字符串以‘，’分割组成的列表

>>> select find_in_set('mj','as,22,44,mj,66,mj1,mj');

 

18、选取字符串的函数

make_set(x,s1,s2,...):返回由x的二进制指定的相应位的字符串组成的字符串。NULL不会添加到结果中。

>>> select make_set(1|4 ,'a','b','c','d','e')

1|4  --->二进制数0101，从右到左第1、3位为1，返回第一个和第三个字符串组成的字符串。

 

三、日期时间函数

基础：UTC：世界标准时间

GMT(Greenwich mean time) 格林尼治标注时间

1、获取当前日期YYYY-MM-DD YYYYMMDD

CURDATE()

CURRENT_DATE()

>>> select curdate(),current_date(),current_date+0,curdate()+0;

获取当前时间 HH:MM:SS HHMMSS

CURTIME()

CURRENT_TIME()

+0返回数值类型

>>> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0,CURTIME()+0;

2、获取当前日期时间 YYYY-MM-DD HH:MM:SS YYYYMMDDHHMMSS

CURRENT_TIMESTAMP()

NOW()

LOCALTIME()

SYSDATE()

>>> SELECT CURRENT_TIMESTAMP,LOCALTIME,NOW(),SYSDATE();

3、Unix时间戳

UNIX_TIMESTAMP(DATE)

>>> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();

FROM_UNIXTIME(DATE):把unix时间戳转换为普通格式的时间戳

>>> select from_unixtime(1556775790);

4、UTC日期 时间

UTC_DATE()

UTC_TIME()

>>> SELECT UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0;

5、

获取月份：MONTH(DATE)---[1-12]

获取月份英文名称：MONTHNAME(DATE)

获取星期英文名称：DAYNAME(D)

获取一周的索引，1=日，2=一，7=星期六：DAYOFWEEK(D)

获取工作日索引,0=星期一，1=星期二，6=星期日：WEEKDAY(D)

>>> select month(now()),monthname(now()),weekday(now()),dayname(now()),dayofweek(sysdate());

获取星期数：WEEK(D),第一个参数是时间，第二个参数是模式，代表星期起始时间是星期几

位于一年的第几周，1-53，相当于WEEK(D,3),一周的第一天是周一:  WEEKOFYEAR(D)

>>> select week(now()),week(now(),3),weekofyear(now());

获取一年中的第几天,1-366：DAYOFYEAR(D)

获取一个月中的第几天1--31：DAYOFMONTH(D)

>>> SELECT DAYOFYEAR(NOW()),DAYOFMONTH(NOW());

获取年份(1970-2069)：YEAR(DATE)

获取季度（1-4）：QUARTER(DATE)

获取分钟（0-59）：MINUTE(TIME)

获取秒数（0-59）:SECOND(TIME)

>>> SELECT YEAR(NOW()),QUARTER(NOW()),MINUTE(NOW()),SECOND('12:59:59')

6、获取日期指定值函数

extract(TYPE FROM DATE):从日期中提取一部分

select extract(year from now());

7、时间和秒转换

TIME_TO_SEC(TIME)

>>> SELECT TIME_TO_SEC(CURTIME());

8、计算日期和时间的函数

DATEADD(DATE ,INTERVAL expr type)

ADDDATE()

DATE_SUB()

SUBDATE()

ADDTIME()

SUBTIME()

DATE_DIFF()

常用的日期和时间type值：

SECOND MINUTE  HOUR  DAY  WEEK  MONTH  QUARTER  YEAR  

>>> select now(),date_add(now(),INTERVAL  1 second ),adddate(now(),interval 1 second );

>>> select  now(),date_sub(now(),interval  1 hour),subdate(now(),interval 1 hour)

>>> select addtime(curtime(),'1:1:1'),now(),subtime(now(),'1:1:1'),datediff(now(),'1999-01-01');

9、日期和时间格式化

DATE_FORMAT(DATE  ,FORMAT)

TIME_FORMAT(TIME,FORMAT)

GET_FORMAT(VAL_TYPE,FORMAT_TYPE)：返回日期时间字符串的显示格式

常见：

>>> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),DATE_FORMAT(NOW(),'%Y-%m-%d');

>>> select get_format(date,'USA'),GET_FORMAT(DATE,'EUR');

四、条件函数

1、IF(EXPR,V1,V2):如果expr==true，返回v1，否则返回v2

>>> select if(true,curdate(),'11') as m1,if(false,'11','22') m2;

2、IFNULL(V1,V2):V1!=NULL,返回V1，V1==NULL，则返回V2

>>> select ifnull(1/0,'2'),ifnull(sysdate(),curtime());

3、CASE EXPR WHEN V1 THEN R1 [WHEN V2 THEN R2] [RLSE RN] END

>>> SELECT CASE 2 WHEN 1 THEN 'ONE' WHEN 2 THEN 'TWO' WHEN 3 THEN 'THREE' ELSE 'NAN' END;

>>> select case when 1=2 then 'aa' when 2 is not null then 'bb' else 'cc' end;

5、系统信息函数

获取mysql版本号：VERSION()

MYSQL服务器当前连接的次数：CONNECTION_ID()

查看线程:SHOW PROCESSLIST()

查看所有线程：SHOW FULL PROCESSLIST()

使用utf8字符集的默认（当前）数据库名：DATABASE() SCHEMA()

获取用户名：USER() CURRENT_USER() SYSTEM_USER(),SESSION_USER()

获取字符串的字符集：CHARSET(STR)

获取字符串的排序方式：COLLATION()

获取最后一个自动生成ID值的函数,与table无关：LAST_INSERT_ID()

>>> select version(),connection_id(),database(),schema(),user(),current_user(),system_user(),session_user();

>>> show full processlist ;

>>> select charset('abc'),charset('123'),collation('abc');

6、加密函数

MYSQL服务加密，单向不可逆：PASSWORD(STR)，修改用户密码用到

MD5(str):

加密函数ENCODE(STR,PWD_STR)  PWD_STR是秘钥

解密函数DECODE(STR,PWD_STR)

>>> select password('123456'),md5('123456'),encode('aa','sec'),decode(encode('aa','sec'),'sec');

7、其他函数

1、格式化函数，四舍五入保留小数点后n位：FORMAT(X,N)

2、进制转换：CONV(N,FROM,TO),将一个FROM进制的数转换为TO进制的数

3、IP地址与数字相互转换：

INET_ATON(EXPR)：ip---数字

INET_NTON(EXPR):数字---ip

4、改变字符集：CONVERT(... USING ...)

5、改变数据类型的函数：cast(X,AS TYPE)   cast(X,TYPE)

>>> SELECT FORMAT(100.9897922,5),CONV('A',16,10),INET_ATON('192.168.1.22'),charset(CONVERT('JAY' USING latin1)),cast(100 as char(2)),convert('1999-01-11 11:23:12',TIME )

重复执行制定操作的函数:    BENCHMARK(COUNT,EXPR)

>>> SELECT BENCHMARK(50000000,PASSWORD('123456'));