Mysql的函数

1. 时间函数
oracle中的sysdate/add_months()/next_day()/trunc()/months_between方法，在mysql中均不支持，但都有对应的替换写法。
dual是一张虚拟的表，这个表可以提供一些函数的基本测试。
now()代表当前系统时间，select now() from dual可以查看当前系统时间，包括“年月日时分秒”（Mysql中的from dual可以省略），对应的还有curdate()获取当前系统时间中的"年月日"部分，curtime()获取当前系统时间中的"时分秒"部分。

select curdate(),now(),curtime()
select curdate(),now(),curtime() from dual

[时间函数常用方法列表]

[date_add(date0,interval n day) 和date_sub(date0,interval n day)]
– 向指定日期date0中添加/减去指定的时间间隔n day

select now(),date_add(now(),interval 10 day)
select now(),date_sub(now(),interval 10 day)

[type值列表]

[datediff(date1,date2)]
– 返回两个日期之间相差的天数

select datediff(‘2018-08-08’,‘2008-08-08’);-- 字符串可以自动转为date格式

[date_format(date,format)]
以不同的格式显示日期/时间数据

select date_format(now(),’%Y-%m-%d %T’)

[extract(year from date0)]
– 返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

select extract(year from ‘2016-09-09’);-- 参数中的字符串会自动按照默认格式转为日期类型数据
select year(now()); – 也可以直接使用from前的范围作为函数名来进行截取

[参数列表]

[last_day(date0)]
– 获取指定日期中月份的最后一天

select last_day(now());

2. 字符函数
2.1 与Oracle共用的字符函数

[常用字符函数]

tips：在位置的查找上，oracle都是从1开始的，如果没有找到返回的是0；

[length(str0)]
– 返回str0的字节数
– mysql中的length()求的是字节数，oracle中的length()求的是字符数
– mysql不支持lengthB()函数

select length(‘helloworld’),length(“你好”);-- oracle一个字符占3字节

[ascii(str0)]
– 返回str0的ascii码的字符串形式

select ascii(‘a’);-- a-97 A-65
select ascii(‘banana’);-- 如果是多个字符，那么会返回第一个字符的ascii
select ascii(’’),ascii(null);-- 如果是空串返回0，如果str是null，返回null

[lower(str0)/upper(str0)]
– 返回str0的全小写/全大写的格式
– mysql不支持initcap()函数

select lower(‘HelloWorld’),upper(‘HelloWorld’);
select lcase(‘HelloWorld’),ucase(‘HelloWorld’); --等效写法

[replace(str0,str1,str2)]
– 将str0中的str1换成str2

select ename,replace(ename,‘J’,‘O’) from emp;

[instr(str0,str1)]
– 在str0中查找str1第一次出现的位置

select ename,instr(‘Perfect’,‘Pe’);-- 数据库是从1开始的
select instr(‘Perfect’,‘pe’) ;-- 如果没有找到返回0

[trim(str0)/ltrim(str0)/rtrim(str0)]
– 将str0两端/左/右去空格

select trim(’ hi world ‘),ltrim(’ hi world ‘),rtrim(’ hi world ');

[lpad(str0,n,str1)/rpad(str0,n,str1)]
– 将str0用str1左/右补齐到n字节

select lpad(‘a’,4,‘b’),rpad(‘a’,4,‘b’);
select lpad(‘abcd’,3,‘e’); – 如果str0本身已经超过了n字节，就变成了截取到n字节

[concat(str1,str2,…)]
– 字符串连接函数，结果为连接参数产生的字符串

select concat(‘a’,22);-- 一个数字参数被变换为等价的字符串形式
select concat(‘a’,null);-- 如有任何一个参数为null，则返回值为null
select concat(‘a’,‘b’,‘c’);-- 可以有超过2个的参数，Oracle中只能写两个

2.2 mysql独占字符函数

[concat_ws(str0,str1…)]
– 带连接符的字符连接，可以按照指定的分隔符进行字符串的连接，是concat()的特殊形式

select concat_ws("-",“a”,“b”,“c”);-- 第一个参数str0就是其它参数的连接符
select concat_ws(null,“a”,“b”,“c”);-- 如果分隔符为null，则结果为null

[repeat(str0,n)]
– 返回将str0复制n次后的字符串

select repeat(“love”,5);
select repeat(“love”,-9);-- 如果n <= 0，返回一个空字符串
select repeat(null,-9);-- 如果str0或n是null，返回null

[char(n，…)]
– 将n解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串
– oracle中使用的chr()函数，mysql中不支持chr()函数

select char(97,null,98,99); – null值被忽略

[position(str1 in str0)]
– 返回子串str1在字符串str0中第一次出现的位置

select position(“o” in “joezhou”);
select position(“x” in “joezhou”);-- 如果str1不是在str0里面，则返回0

[left(str0,n)/right(str0,n)]
– 返回str0的最左/右面n个字符

select left(‘perfect’, 5),right(‘perfect’, 5); – 无论left或者right，都是从左到右的顺序截取

[substring(str0,n1,n2)]
– 从第n1（包括n1）个位置开始向后截取n2个字符串
– oracle中使用的是substr()函数，mysql不支持

select substring(‘perfect’ from 3);-- 如果没有第三个参数代表截取到最后
select substring(‘perfect’,3);-- 简写形式
select substring(‘perfect’,3,2);

[space(n)]
– 返回由n个空格字符组成的一个字符串

select space(6);

[reverse(str0)]
– 将str0颠倒字符顺序并返回

select reverse(‘banana’);

[insert(str0,n1,n2,newstr)]
– 将str0的n1位置开始的n2个字符替换为str1，并返回

select insert(‘Hiva’,3,0,‘Ja’);
select insert(‘HiJava’,3,4,‘Oracle’);

[elt(n,str1,str2,str3,…)]
– 如果n=1，返回str1，如果n= 2，返回str2…
– elt()是field()反运算

select elt(1, ‘a’, ‘b’, ‘c’, ‘d’);
select elt(6, ‘a’, ‘b’, ‘c’, ‘d’);-- 如果n小于1或大于参数个数，返回null

[field(str,str1,str2,str3,…)]
– 返回str在str1, str2, str3, …列表中的位置
– field()是elt()反运算

select field(‘a’, ‘a’, ‘b’, ‘c’, ‘d’);
select field(‘a’, ‘as’, ‘bs’, ‘cs’, ‘ds’);-- 如果str没找到，返回0

3. 数学函数

[常用数学函数]

select abs(-10) ;-- abs(x)：求绝对值;
select ceil(1.6)/floor(1.6) ; – ceil(x)/floor(x):向上/下取整;
select power(2,3) ;-- power(x,y):返回x的y次幂;
select mod(5,3) ;-- mod(x,y):返回x除以y取余;
select round(5.5234,1) ;-- round(x,y):返回x四舍五入之后的值,y为小数点保留位数;
select greatest(10,20)/least(10,20) ;-- greatest(x,y)/least(x,y)：返回x和y的最大最小值，mysql允许多值比较;

4. 其他函数

– mysql支持case when then end，和oracle同样的用法。

select ename,(
case
when sal between 800 and 1500 then ‘穷人’
when sal between 1501 and 3000 then ‘小康’
else ‘富人’
end
) 水平 from emp order by sal

– ifnull(字段，result1)：等同于oracle中的nvl()

select ifnull(comm,0) from emp;-- ifnull(字段,值)：如果字段为null，则输出值；

– if(条件，result1，result2)函数：判断条件是否成立或为null，如果成立或为null，输出result1，否则输出result2

select if(deptno=20,‘20部门’,‘非20部门’) from emp;
select deptno, if(deptno=10,‘10部门’,
if(deptno=20,‘20部门’,
if(deptno=30,‘30部门’,‘40部门’)
)
) 部门 from emp;-- if可以嵌套使用
select if(comm,‘有补助’,‘无补助’) from emp;-- 条件可以直接判断是否为null，真输出p2，假输出p3

---