sql内置函数

一、MySQL

1.MySQL和Oracle都可以用的函数

1.1数学函数

select abs(-100) --(求绝对值，结果：100)
select mod(10,3) --（10除以3取余，结果：1）
select ceil(1.23) --(向上取整，结果：2)
select round(5.4) --(四舍五入，结果：5)
select round((5.445),2) --(四舍五入，结果保留两位小数，结果：5.45)
select floor (1.23) --(向下取整，结果：1)
select power(2,3) --(2的3次方，结果：8)

1.2字符串函数

select concat('abc','cde') --(合并，结果：abccde)
select lower('AaBbCd') --(转小写，结果：aabcd)
select upper('AbcdAf') --(转大写，结果：ABCDAF)
select length('abdce') --(字符串的长度，结果：5)
select lpad('10',2,'0') --(在10的左边加一个0，使之成为两位数，但10本身就是两位数，所以结果不变，结果：10)
select lpad('9',2,'0') --(在9的左边加一个0，使之成为两位数，结果：09)
select rpad('9',3,'0') --(在9的右边加凉的个0，使之成为三位数，结果：900)
select replace('hello world','hello','hehe') --(替换，结果：hehe world)
select substr('abcde',2,3) --(从第2个位置开始，向后取3个，结果：bcd;不写“3”，就表示后面的全部取)
select reverse('hello') --(翻转，结果：olleh)

2.MySQL独有的函数

2.1 数学函数

select rand()--(随机数；如果在括号内写上数字，那么只有第一次运行会产生随机数，后面再运行时，生成的数不变)
select truncate(0.009,2) --(小数点后截取2位，后面的“2”必须写，结果：0.00)

2.2 字符串函数

select instr('abcd ab ab','ab') --('ab'第一次出现的位置，结果：1)
select ltrim ('    abcd     ') --(只能删空格，不能删其他的，结果：abcd)
select left('abcd',2)  --(从最左边开始，截取2个，结果：ab)
select right('abcd',2)  --(从最右边开始，截取2个，结果：cd)
select insert('whatareyou',5,3,'is') --(从第五个开始向后数3个，这三个数被is取代，结果：whatisyou)

2.3 日期函数

select sysdate() --(返回当前日期，把sysdate换成now也一样)
select date_add(sysdate(),interval 3 month) --(在当前月的基础上，加3个月，month可以改成year/day)
select last_day(now()) --(当前月的最后一天)
select last_day('2002-2-4') --(2002年2月的最后一天)
select datediff(sysdate(),'2002-2-4')--(当前日期与2002年2月4号之间，隔了多少天)
select day(now()) --(今天几号)
select week(now()) --(本周是今年的第几周)
select dayofweek(sysdate()) --(今天是本周的第几天；dayofweek可以改成monthofweek/yearofweek)
select quarter(sysdate()) --(现在是第几季度)

2.4 其他函数

select cast('123' as signed) --(字符串转int)
select inet_aton('192.168.1.100') --(地址转数字，192*255^3+168*255^2+1*255^1+100*255^0)
select ifnull('abc','sss') --(逗号前面不是null，返回逗号前的字段，结果：abc)
select ifnull(null,'sss') --(逗号前面是null，返回逗号后的字段，结果：sss)
select if(null,123,456) --(第一个是null,返回第三个字段，结果：456)
select if(1,123,456) --(第一个是null,返回第二个字段，结果：123；第一个“1”只是条件，可以换成其他的数字)
select if(1<3,123,456) --(第一个是条件结果是true,返回第二个字段，结果：123;反之，第一个是条件结果是false,返回第三个字段，结果：456)
select case 
           when 1<1 then 'abc'
           when 2<3 then 'cde'
           when 3<4 then 'fff'
           else 'kkk'
       end --(碰到第一个成立的条件，就会退出，不会碰到第二个成立的条件，结果：cde)
select case 1
           when 1 then 'abc'
           when 2 then 'cde'
           when 3 then 'fff'
           else 'kkk'
       end --(结果：abc)

二、Oracle

1.数学函数

select dbms_random.value(1,100) from dual --(在1到100之间取一个随机数)
select dbms_random.string('p',5) from dual --(随机打印5个字符,“p”表示打印)
select trunc(0.139，2) from dual --(截取，结果：0.13；如果后面的“2”不写，结果就是小数点前面的数)

2.2 字符串函数

select initcap('hello world') from dual --(首字母大写，结果：Hello World)
select instr('abcd ab ab',1,3) from dual --(从第一个开始搜索，找到“ab”第三次出现的位置)
select ltrim('***abc','*') from dual --(删掉左边的*，结果：abc)
selecr trim('   abc   ') from dual --(删掉左两边的空格，结果：abc；只能删空格)
select rtrim(ltrim('***abc','*'),'*') from dual --(先删掉左边的*，再删掉右边的*，结果：abc)
select translate('hello,world','ho','a')  from dual --(“h”换成“a”,“o”没有换的，所以没了，结果：aell,wrld)
select translate('hello,world','ho','ab')  from dual --(“h”换成“a”,“o”换成“b”，结果：aellb,wbrld)

2.3 日期函数

select sysdate from dual --(返回当前日期)
select add_months(sysdate,3) from dual --(在当前月上加3个月)
select sysdate+1 from dual --(在当前时间上加一天；+1/24是加1个小时)
select last_day(sysdate) from dual --(当前月的最后一天)
select to_date('1999-2-3','yyyy_MM-dd') from dual --(将字符串按指定格式转为日期，结果：1999/2/3 16:42:33)
select to_date('1999-2-3 16:42:33','yyyy_MM-dd hh24:mi:ss') from dual --(将字符串按指定格式转为日期，结果：1999/2/3)
select last_day(to_date('1999-2-3','yyyy_MM-dd')) from dual --(1999年2月的最后一天)
select months_between(sysdate,to_date('2000-2-3','yyyy_MM-dd')) from dual --(当前日期与2000年2月3日之间隔了258个月)
select sysdate-to_date('2000-2-3','yyyy_MM-dd') from dual --(两个日期隔了多少天)
select round(sysdate) from dual --(最近的0点的日期，注意：是日期，不是时间)
select round(sysdate,'day') from dual --(最近周日的日期，国外周日是每个礼拜的第一天；“day”换成“month”，结果是最近月初的日期)
select next_day(sysdate,2) from dual --(当前日期的下个周一，国外的周一是一周的第二天)
select extract(Day from sysdate) from dual --(当前几号；DAY可以换成MONTH/YEAR)
select cast('123' as int) from dual --(字符串转int)
select to_char(1234,'9,999') from dual --(int转char,“9,999”表示输出的格式，前面有几个数字，后面的“9”的数量不能比它少)
select to_char(sysdate,'d') from dual --(“dd”表示每月第几天，“ww”表示每年第几周，(“q”表示第几季度)
select nvl(null,123) from dual --(逗号前面是null，返回逗号后的字段,结果：123)
select nvl(456,123) from dual --(逗号前面不是null，返回逗号前的字段，结果：456)
select nvl2(null,456,789) from dual --(第一个是null,返回第三个字段，结果：789)
select nvl2(1,456,789) from dual --(第一个不是null,返回第二个字段，结果：456)
select case 
           when 1<1 then 'abc'
           when 2<3 then 'cde'
           when 3<4 then 'fff'
           else 'kkk'
       end from dual --(碰到第一个成立的条件，就会退出，不会碰到第二个成立的条件，结果：cde)
select decode(6,1,'abc',2,'cde',3,'def','ggg') from dual --(case的简化版，其中的“6”、“1”，“2”、“3”可以换成列名，结果：ggg)