一、MySQL
1.MySQL和Oracle都可以用的函数
1.1数学函数
select abs(-100)
select mod(10,3)
select ceil(1.23)
select round(5.4)
select round((5.445),2)
select floor (1.23)
select power(2,3)
1.2字符串函数
select concat('abc','cde')
select lower('AaBbCd')
select upper('AbcdAf')
select length('abdce')
select lpad('10',2,'0')
select lpad('9',2,'0')
select rpad('9',3,'0')
select replace('hello world','hello','hehe')
select substr('abcde',2,3)
select reverse('hello')
2.MySQL独有的函数
2.1 数学函数
select rand()
select truncate(0.009,2)
2.2 字符串函数
select instr('abcd ab ab','ab')
select ltrim (' abcd ')
select left('abcd',2)
select right('abcd',2)
select insert('whatareyou',5,3,'is')
2.3 日期函数
select sysdate()
select date_add(sysdate(),interval 3 month)
select last_day(now())
select last_day('2002-2-4')
select datediff(sysdate(),'2002-2-4')
select day(now())
select week(now())
select dayofweek(sysdate())
select quarter(sysdate())
2.4 其他函数
select cast('123' as signed)
select inet_aton('192.168.1.100')
select ifnull('abc','sss')
select ifnull(null,'sss')
select if(null,123,456)
select if(1,123,456)
select if(1<3,123,456)
select case
when 1<1 then 'abc'
when 2<3 then 'cde'
when 3<4 then 'fff'
else 'kkk'
end
select case 1
when 1 then 'abc'
when 2 then 'cde'
when 3 then 'fff'
else 'kkk'
end
二、Oracle
1.数学函数
select dbms_random.value(1,100) from dual
select dbms_random.string('p',5) from dual
select trunc(0.139,2) from dual
2.2 字符串函数
select initcap('hello world') from dual
select instr('abcd ab ab',1,3) from dual
select ltrim('***abc','*') from dual
selecr trim(' abc ') from dual
select rtrim(ltrim('***abc','*'),'*') from dual
select translate('hello,world','ho','a') from dual
select translate('hello,world','ho','ab') from dual
2.3 日期函数
select sysdate from dual
select add_months(sysdate,3) from dual
select sysdate+1 from dual
select last_day(sysdate) from dual
select to_date('1999-2-3','yyyy_MM-dd') from dual
select to_date('1999-2-3 16:42:33','yyyy_MM-dd hh24:mi:ss') from dual
select last_day(to_date('1999-2-3','yyyy_MM-dd')) from dual
select months_between(sysdate,to_date('2000-2-3','yyyy_MM-dd')) from dual
select sysdate-to_date('2000-2-3','yyyy_MM-dd') from dual
select round(sysdate) from dual
select round(sysdate,'day') from dual
select next_day(sysdate,2) from dual
select extract(Day from sysdate) from dual
select cast('123' as int) from dual
select to_char(1234,'9,999') from dual
select to_char(sysdate,'d') from dual
select nvl(null,123) from dual
select nvl(456,123) from dual
select nvl2(null,456,789) from dual
select nvl2(1,456,789) from dual
select case
when 1<1 then 'abc'
when 2<3 then 'cde'
when 3<4 then 'fff'
else 'kkk'
end from dual
select decode(6,1,'abc',2,'cde',3,'def','ggg') from dual