mysql el函数_MySQL（三）常用函数

一、与数值相关的函数

(一) abs函数：求参数的绝对值

select abs(-3);

select abs(0);

(二) ceil函数/ceiling函数：对数据进行向上取整，取最小整数

select ceil (3.14);

select ceiling (3.14);

(三) floor函数：向下取整，返回最大的整数

select floor (3.14);

select floor (3.64);

(四) mod函数：结果的符合和被除数相同，另外如果被除数小于除数，结果为被除数

select mod(5,3);

select mod(-5,3); -2

select 5 mod 3;

select 5 mod 0;

select mod(5,0);

(五) pow函数/power函数：select pow(m，n)，求m的n次方

select pow(3,2);

select power(3,2);

(六) rand函数：生成0-1间的随机数，包含0，但是不包含1

select rand();

select floor(rand()*(10+1-1)+1);

select floor(rand()*(最大值+1-最小值)+最小值)；

(七) round函数：

round(x)，返回参数X的四舍五入的一个整数

round(x,d)，返回参数X的四舍五入的有 D 位小数的一个数字

select round(3.1415926);

select round(3.6415926,0);

select round(345678.123456,-2);

(八) sqrt函数：求x的算术平方根

select sqrt(4);

(九) truncate函数：对x的小数位进行截取

select truncate(3.1415926,2);

select truncate(3.1415926,3); 只截不进

select truncate(123456.12345,3);

二、 与字符串相关的函数

(一) length函数：返回字符串的长度，以字节为单位，汉字占三个字节

select length('abc');

select length('abc123');

select length('abc123中国'); 12

(二)char_length函数：返回字符串的长度，以字符个数为单位

select char_length('abc123中国'); 8

(三)concat函数：连接字符串，返回连接后的字符串。参数可以是字符串、数值、布尔值，如果是布尔值，true为1，false为0，如果参数由null，那么结果为null

select concat('a','b','c');

select concat('a','b','c','123');

select concat('a','b','c','123',456);

select concat('a','b','c','123',456,null);

(四) concat_ws函数：连接字符串，并且可以指定分隔符

select concat_ws('-','a','b');

(五) insert函数：select insert(str,x,y,insert)，从str的x位置开始，替换y长度的字符串为insert

select insert('helloworld',5,3,'*');

select instr('helloworld','d');

select instr('helloworld','l'); 从左往右，找第一个

(六) left函数：left(str,x)；返回str左边x个字符，x为null则返回null

select left('helloworld',3);

select left('helloworld',0);

select left('helloworld',30);

select left('helloworld',char_length('helloworld'));

(七) right函数：返回str右边x个字符，x为null则返回null

select right('helloworld',3);

(八) lower函数/lcase函数：将字符串转换为小写

select lower('ABCabc');

select lcase('ABCabc');

(九) upper函数/ucase函数：将字符串转换为大写

select upper('ABCabc');

select ucase('ABCabc');

(十) replace函数：replace(str,a,b)；

select replace('helloworld','o','*');

select replace('helloworld','l','*');

select replace('helloworld','el','*');

replace(str,a,b) 在字符串str中用字符串b替换所有的字符串a，参数a可以是一个字符也可以是多个字符

(十一) substr函数/substring函数： substr(str, x，y)； 在字符串str中从x开始进行截取y个字符

select substr('helloworld',2,3); 第二个开始，取三

select substr('helloworld',5,3);

select substr('helloworld',5);

select substring('helloworld',5,3); 同

select substring('helloworld',5);

select substr('helloworld'from 3 for 4);

(十二) trim函数：去掉字符串两端的空格

注意：trim还可以有如下作用

trim(子串 from str)：表示将字符串str两侧的指定子串删除select trim('ab' from 'abcdefab');结果为cdef

trim(both 子串 from str)：作用同上

trim(leading 子串 from str)：删除左侧子串

trim(trailing 左侧 from str)：删除右侧子串

select trim(' abc ');

select concat ('---',trim(' abc '),'---');

select concat ('---',trim(' ab c '),'---');

select trim();

select trim('aa'from'aa123aa');

select trim(both'aa'from'aa123aa');

select trim(leading'aa'from'aa123aa'); 删除左侧子串

select trim(trailing'aa'from'aa123aa'); 删除右侧子串

(十三) ltrim函数：ltrim(字符串)，去掉字符串左侧空格

rtrim函数：去掉字符串右侧空格

select concat('----',ltrim(' abc '),'---');

select concat('----',rtrim(' abc '),'---');

(十四) strcmp函数：

select strcmp('a','b'); 前比后小，-1 (比较的是ASCII码)

select strcmp('b','a'); 后比前大，1

select strcmp('ad','ab'); 多字符拿对应位置字符比较

select 'a'>'b';

select 'a'

select 'a'='b';

select 'ab'='ac';

select 'ab'

字符串比较时也可以使用比较运算符符直接进行比较，如’a’>’b’

select '2'>'1';

(十五) lpad函数：用pad对字符串str从最左边进行填充，直到总长度达到n

select lpad('hello',10,'*');

select lpad('hello',3,'*');

(十六) rpad函数：用pad对字符串str从最右边进行填充，直到总长度达到n

select rpad('hello',10,'*');

select rpad('hello',3,'*'); 同l结果

如果长度n的值小于字符串str现有长度，那么会对字符串进行截取(从左往右截)

三、 日期和时间函数

(一) curdate函数：获取日期，如果在后面+0，则将日期转为数值型

select curdate();

select curdate()+0; +0，则将日期转为数值型

(二) curtime函数：

select curtime();

(三) now函数

select now();

(四) unix_timestamp函数

select unix_timestamp('2018-11-18');

select unix_timestamp(now()); 当前时间距离1970年1月1号的毫秒值

(五) from_unixtime函数：返回时间戳对应的日期和时间

select from_unixtime(123456789);

select year(now());

select month(now());

select day(now());

select hour(now());

select minute(now());

select second(now());

select monthname(now());

(六) date_format函数

date_format函数：以不同的格式显示日期/时间数据，select date_format(日期时间，‘格式’);

select date_format(now(),'%Y-%M-%D %H:%I:%S %W');

select date_format(now(),'%y-%M-%D %H:%I:%S %W');

select date_format(now(),'%y-%M-%D %H:%I:%S %w');

(七) date_add函数：向日期添加指定的时间间隔。date_add(date,interval expr type)，date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

select date_add(now(),interval 2 day);

select date_add(now(),interval 2 minute);

select date_add(now(),interval -2 minute);

select date_add(now(),interval -2 day);

(八)datediff函数：返回两个日期之间的时间。select datediff('2008-12-29','2008-12-28');返回结果为1，如果第一个日期比第二个日期大，返回结果为正数，否则返回结果为负数

select datediff('2018-1-1','2018-2-1');

select datediff('2018-2-1','2018-1-1');

(九) period_add函数：对日期P加或减N个月份

select period_add('201805',-3); period_add(P，N)；P的格式为“YYYYMM” 或者 “YYMM

select period_add('201805',13);

四、 信息函数

(一) version函数

1、 作用：获取数据库的版本

2、 格式：select version();

(二) database函数/schema函数

1、 作用：获取当前正在访问的数据库的名称

2、 格式：select database();

(三) user函数

1、 作用：获取当前用户

2、 格式：select user();

show databases;

select database();

select schema();

select user();

五、 高级函数

(一) if函数

格式：if(表达式1，表达式2，表达式3)；

作用：如果表达式1成立，结果为表达式2的值，否则结果为表达式3的值

select if(3>2,'yes','no');

select if(3<2,'yes','no');

select degree,if(degree>=90,'优秀',if(degree>=80,'良好',if(degree>=60,'合格','不合格'))) from score;

select degree as 分数,if(degree>=90,'优秀',if(degree>=80,'良好',if(degree>=60,'合格','不合格')))as 等级 from score;

(二) ifnull函数

格式：ifnull(表达式1，表达式2)；

作用：判断表达式1是否为空，如果为空，结果为表达式2，否则结果为表达式1

select ifnull(null,'hehe'); 判断表达式1和表达式2是否相等，如果相等，结果为null，否则结果为表达式1

select ifnull(12,'hehe');

select degree from score where id=1;

select degree,ifnull(degree,'成绩为空') from score where id=1;

(三) nullif函数

格式：nullif(表达式1，表达式2)；

作用：判断表达式1和表达式2是否相等，如果相等，结果为null，否则结果为表达式1

select nullif(3,3);

select nullif(3,13); 表达式1和表达式2是否相等，如果相等，结果为null，否则结果为表达式1

(四) isnull函数

格式：isnull(表达式)；

作用：如果表达式的值为null，那么结果为1，否则为0

select isnull(null);

select isnull('null');

alter table score drop id;

delete from score where degree is null;

(五) case：类似于JavaScript中的switch语句：

select degree,case degree

when 86.0 then 'A'

when 88.0 then 'B'

when 85.0 then 'C'

when 81.0 then 'D'

else '不是80-89之间的数值'

end from score;

select degree as 分数,case degree

when 86.0 then 'A'

when 88.0 then 'B'

when 85.0 then 'C'

when 81.0 then 'D'

else '不是80-89之间的数值'

end as 评语 from score;

select degree as 分数,case 两种格式，上面case后加degree，后面则放下面

when degree>=90 then '优秀'

when degree>=80 then '良好'

when degree>=60 then '合格'

else '不合格'

end as 等级 from score;