SQL基本函数

数值函数
select ceil(44.775) from dual;–45
select ceil(44.112) from dual;–45
select floor(44.775) from dual;–44
select floor(44.112) from dual;–44
select abs(-10) from dual;–10
select abs(10) from dual;–10
select sqrt(4) from dual;–2
select power(2,3) from dual;–8
select power(3,2) from dual;–9
select sin(0),cos(0) from dual;–??
select sign(-200) from dual;–????-1??1
select mod(10,4) from dual;–10%4
select round(23.4366,2) from dual;–23.44
select round(23.4366,3) from dual;–23.437
select round(55.341,-2) from dual;-100

字符函数
select upper(‘ab’) from dual;–AB
select lower(‘ATTT’) from dual;–attt
select lower(‘ads’) from dual;–ads
select initcap(‘rervdfds’) from dual;–Rervdfds
–substr(char,position,substr_length)
select substr(‘abcdefgh’, 0,1) from dual;–a
select substr(‘abcdefgh’, 4) from dual;–defgh
select substr(‘abcdefgh’, -5,2) from dual;–de
select substr(‘abcdefgh’, -1) from dual;–h
select length(‘12121 ‘) from dual;–7 ????
select length(‘??’) from dual;–2
select concat(‘sun ‘, ‘is beautiful’) from dual;– is beautiful
select ‘sun’||’ is beautiful’ from dual;–sun is beautiful
select trim(‘a’ from ‘asasasasa’) from dual;–
select ltrim(‘2323’, ‘1’) from dual;–2323
select ltrim(‘2323’, ‘2’) from dual;–323
select ltrim(‘22323’,’2’) from dual;-323
select rtrim(‘232333’,’3’) from dual;–232
select trim(’ eweqwe ‘) from dual;–eweqwe
select ltrim(’ eweqwe ‘) from dual;–eweqwe
select rtrim(’ eweqwe ‘) from dual;– eweqwe
select replace(‘abdsda’,’a’,’A’) from dual;–AbdsdA
select replace(‘abdsda’,’a’) from dual;–bdsd
select replace(‘abcabcabcddf’,’abc’,’null’) from dual;–nullnullnullddf
日期函数
–系统时间
select sysdate from dual;–格式’Day-month-year’;
–对日期操作的函数
–返回在指定日期上添加的月份add_months(date,i)
–如果i是小数，取整数部分，如果是负数，从后面开始减
select add_months(sysdate, 2) from dual;–Month5 to Month7
select add_months(sysdate, -1) from dual;–Month5 to Month3
–next_day返回制定日期的下周？是哪一天
select next_day(sysdate,’monday’) from dual;–24-APR-17
select next_day(sysdate,’Tuesday’) from dual;–25-APR-17
–last_day(date)返回date所在日期的最后一天
select last_day(sysdate) from dual;–30-APR-17
–months_between(date1, date2),date1-date2之间相差的月份,返回值可正可负
select months_between(‘1-Apr-2017’, ‘30-Apr-2017’) from dual;—0.935483
–extract(date from datetime)
select extract(day from sysdate) from dual;–19
select extract(month from sysdate) from dual;–4
select extract (year from sysdate) from dual;–2017
–利用时间戳
select extract(hour from timestamp’2017-4-19 22:47:45’) from dual; –22
select extract(minute from timestamp’2017-4-19 22:47:45’) from dual; –47
select extract(second from timestamp’2017-4-19 22:47:45’) from dual; –45
转换函数
–日期类型转换成字符型
–to_char(date,[,fmt[,params]])
–date是将要转换的日期
–fmt转换的格式：默认是dd-mm-rr
–YY YYYY YEAR
–MM MONTH
–DD DAY
–HH24 HH12
–MI SS
–param日期的语言
select to_char (sysdate, ‘yyyy-mm-dd HH24:MI:ss’) from dual;–2017-04-19 23:59:58
select to_char (sysdate, ‘yy-mm-dd HH24:MI:ss’) from dual;–17-04-19 23:59:19
select to_char (sysdate, ‘year-mm-dd HH24:MI:ss’) from dual;–twenty seventeen-04-20 00:00:12
–字符型转换为日期型
–to_date(char,[,fmt[,params]])
–params用于指定日期的语言
select to_date(‘217-04-20’,’YYYY-MM-DD’) from dual;–20-APR-17，依然是按照默认格式输出
–数字转换成字符
–to_char(number[,fmt])
–9:显示数字忽略前面的0,0显示数字，位数不足，用0补齐
–.或D显示小数点,或G显示千位符$显示美元符号S加正负号前后都可以 select to_char(12345.678,’$99,999.999') from dual;-- $12,345.678 select to_char(00011222.3213,’S99999.9999’) from dual–+11222.3213 –字符转换成数字 –to_number(char[,fmt]) select to_number(‘$1,000','$9999’) from dual;–1000