MySQL知识整理6—函数

主要分类：

数学函数，字符串函数，日期和时间函数，条件判断函数，系统信息函数和加密函数。

一、数学函数

ABS(x)            返回x的绝对值
BIN(x)            返回x的二进制（OCT返回八进制，HEX返回十六进制）
CEILING(x)        返回大于x的最小整数值
EXP(x)            返回值e（自然对数的底）的x次方
FLOOR(x)          返回小于x的最大整数值
GREATEST(x1,x2,...,xn)   返回集合中最大的值
LEAST(x1,x2,...,xn)      返回集合中最小的值
LN(x)             返回x的自然对数
LOG(x,y)          返回x的以y为底的对数
MOD(x,y)          返回x/y的模（余数）
PI()              返回pi的值（圆周率）
RAND()            返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
ROUND(x,y)        返回参数x的四舍五入的有y位小数的值
SIGN(x)           返回代表数字x的符号的值
SQRT(x)           返回一个数的平方根
TRUNCATE(x,y)     返回数字x截短为y位小数的结果

mysql中的示例：

select abx(5.1), abx(-9);  
select sqrt(4),sqrt(20);  
SELECT MOD(2,3),-10%4;    
select ceil(3.35), ceiling(-3.35);  
select floor(3.35),floor(-3.35);  
select rand(),rand(10),rand(10),rand(11); 
select round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2);
select truncate(1.31,1),truncate(1.99,1),truncate(1.99,0),truncate(19.99,-1);
select sign(-21),sign(0),sign(21);
select pow(2,2),power(2,2),pow(2,-2),power(2,-2);
select exp(3),exp(-3),exp(0);
select log(3),log(-3),log10(2),log10(100),log10(-100); 
select sin(1),asin(0.8),cos(1),cos(pi()),acos(0.5);
select tan(1),tan(pi()/4),atan(1),atan(0.3);
select cot(1),cot(pi()/4);

二、字符串函数

分为几类：计算字符串长度函数，合并函数，替换函数，比较函数，查找指定位置函数等

ASCII(char)            返回字符的ASCII码值
BIT_LENGTH(str)        返回字符串的比特长度
CONCAT(s1,s2...,sn)    将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn) 将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr)  将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list)  分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x)            返回字符串str中最左边的x个字符
LENGTH(s)              返回字符串str中的字符数
LTRIM(str)             从字符串str中切掉开头的空格
POSITION(substr in str)   返回子串substr在字符串str中第一次出现的位置
locate(str1,str)          与position相同
instr(str,str1)           与position相同
QUOTE(str)             用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)  返回字符串str重复x次的结果
REVERSE(str)           返回颠倒字符串str的结果
RIGHT(str,x)           返回字符串str中最右边的x个字符
RTRIM(str)             返回字符串str尾部的空格
STRCMP(s1,s2)          比较字符串s1和s2
TRIM(str)              去除字符串首部和尾部的所有空格
UCASE(str)             或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
mid(s,n,len)           获取从字符串s中的第n个位置开始长度为len的字符串
substring(s,n,len)     与mid函数相同
elt(n,str1,str2,...)   返回第n个字符串
field(s,s1,s2...)      返回字符串s在字符串中第一次出现的位置
find_in_set(s1,s2)     返回s1在列表s2中的位置，与field()函数类似

　　

mysql中的示例：

select char_length('date'),char_length('aaa');
select length('date'),length('aaa');
select concat('my','sql','8.0'),concat('my',null,'sql');
select concat_ws('-','2019','03','06'),
       concat_ws('*','1st',null,'3rd');
select insert('quest',2,4,'what'),
       insert('quest',-1,4,'what'),
       insert('quest',3,100,'what');
select lower('HELLO'),lcase('Well');
select upper('HELLO'),ucase('Well');
select left('world',3),right('world',3);
select lpad('hello',4,'?'),lpad('hello',10,'?');
select rpad('hello',4,'?'),rpad('hello',10,'?');
select ltrim(' (book) '),concat('(',ltrim('  book  '),')');
select rtrim(' (book) '),concat('(',rtrim('  book  '),')');
select trim(' (book) '),concat('(',trim('  book  '),')');
select trim('xy' from 'xyxboxyokxxyxy');
select repeat('mysql',3);
select concat('(',space(6),')');
select replace('xxx.mysql.com','x','w');
select substr('breakfast',5),substr('breakfast',5,3),
       substr('lunch',-3),substr('lunch',-5,3);
select mid('breakfast',5),mid('breakfast',5,3),
       mid('lunch',-3),mid('lunch',-5,3);
select position('ball' in 'football'),
locate('ball','football'),instr('football','ball');
select reverse('abc');
select elt(3,'abc','123','red'),elt(3,'abc','123');
select field('he','hi','op','he'),field('he','hi','op','12');
select find_in_set('he','she,and,he');

 

三、日期和时间函数

CURDATE()或CURRENT_DATE()       返回当前的日期
CURTIME()或CURRENT_TIME()       返回当前的时间
DATE_ADD(date,INTERVAL int keyword)
                  返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),
                  如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt)           依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword)
                  返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),
                  如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~
DAYOFMONTH(date)  返回date是一个月的第几天(
DAYOFYEAR(date)   返回date是一年的第几天(1~366)
DAYNAME(date)     返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time)        返回time的小时值(0~23)
MINUTE(time)      返回time的分钟值(0~59)
MONTH(date)       返回date的月份值(1~12)
MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW()             返回当前的日期和时间
QUARTER(date)     返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date)        返回日期date为一年中第几周(0~53)
YEAR(date)        返回日期date的年份(1000~9999)

　　

mysql中的示例：

/*获取当前日期、时间的函数*/
select curdate(),current_date(),curdate()+0;
select curtime(),current_time(),curtime()+0;
/*获取当前日期和时间的函数*/
select current_timestamp(),localtime(),now(),sysdate();
/*获取当前月份的函数month(date)*/
select month('2019-03-06'),monthname('2019-03-06');
/*获取当前星期的函数*/
select dayname('2019-03-06'),dayofweek('2019-03-06'),weekday('2019-03-06');
/*获取星期数的函数*/
select week('2019-03-06'),week('2019-03-06',1);
select week('2019-03-06',3),weekofyear('2019-03-06');
/*获取天数的函数*/
select dayofmonth('2019-03-06'),dayofyear('2019-03-06');
/*获取年份、季度、小时、分钟和秒钟的函数*/
select year('2019-03-06'),year('19-03-06'),year('99-03-06');
select quarter('2019-03-06');
select minute('2019-03-06 15:17:30');
select second('2019-03-06 15:17:30');
/*时间转换位秒的函数*/
select time_to_sec('23:23:00');

　　

四、条件判断函数

 

select if(1>2,2,3),if(1<2,'yes','no');
select if(strcmp('text','text1'),'yes','no');
select ifnull(1,2),ifnull(null,10),ifnull(1/0,'wrong');
select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;
select case when 1<2 then 'ture' else 'flase' end;

　

五、信息系统函数

select version();  /*版本号*/
select connection_id(); /*查看当前用户的连接数*/
show processlist;  /*输出当前用户的连接信息*/
select database(),schema(); /*查看当前使用的数据库*/

select user(),current_user(),system_user(); /*查看当前登录用户名称*/
select charset('abc'),charset(convert('abc' using latin1)),
charset(version()); /*返回字符串使用的字符集*/

select password('123'); /*加密函数*/
select md5('abc'); /*加密字符串*/
select encode('secret','ha'); /*encode(str,ps_str)使用ps_str加密str*/
select decode(encode('secret','ha'),'ha'); /*解密函数*/

select cast(100 as char(2)),convert('2019-03-06 17:19:30',time);
/*改变数据类型函数*/
/*cast(x as type)*/
/*convert(x,type)*/