日期、時間函數(shù)：
select * from project;
針對日期型：
1. add_months: 增加月份
select * from task,startdate,enddate,original_end,add_months(enddate,2) from project;
select task tasks_shorter_than_one_month from project where add_months(startdate,1) > enddate;
2. last_day: 最后一天
select enddate,last_day (enddate) from project;
select distinct last_day('1-FEB-95') NON_LEAP,LAST_DAY('1-FEB-96') LEAP FROM PROJECT;
3. months_between: 中間有幾個月
select task,startdate,enddate,months_between(enddate,startdate) duration from project;
select * from project where months_between('19 MAY 95',STARTDATE) > 0;
4. new_time: 調(diào)整時區(qū)
select enddate edt,new_time(enddate,'edt','pdt') from project;
5. next_day: 返回與指定日期同一星期或下一星期的，星期幾的確切日期；
select startdate,next_day(startdate,'friday') from project;
6. sysdate:系統(tǒng)日期和時間
select distinct dysdate from project;
select * from project where startdate > sysdate;
數(shù)學(xué)函數(shù)：
1. ABS : 絕對值
select ABS(A) absolute_value from numbers;
2. CELL (比參數(shù)大的最小整數(shù)) 和 FLOOR(比參數(shù)小的最大整數(shù))
select B，cell(B) ceiling from numbers;
select A，floor(A) floor from numbers;
3. COS COSH SIN SINH TAN TANH 三角函數(shù)，默認(rèn)參數(shù)認(rèn)定為弧度制；
4. EXP : 返回給定參數(shù)為指數(shù)，e為底數(shù)的冪值；
select A,EXP(A) from numbers;
5. LN : 返回給定參數(shù)的自然對數(shù)；
6. LOG: 返回結(jié)合參數(shù)的對數(shù)；
7. MOD: 取模；
SELECT A B MOD A B FROM NUMBERS
8. POWER: 冪數(shù)；
SELECT A B POWER A B FROM NUMBERS；
9. SIGN：如果參數(shù)的值為負(fù)數(shù)那么SIGN 返回-1 如果參數(shù)的值為正數(shù)那么SIGN 返回1
如果參數(shù)為零那么SIGN 也返回零；
SELECT A SIGN A FROM NUMBERS
10. SQRT：開方
SELECT A SQRT A FROM NUMBERS
字符串函數(shù)：
1. concat : 連接，等同“|| ”;
2. initcap：首字母轉(zhuǎn)大寫
select firstname before,initcap(firstname) after from characters;
3. upper,lower
select firstname,upper(firstname),lower(firstname) from characters
4. lpad,rpad 填充字符串 ， 至少兩個參數(shù)
SQL> select lastname,lpad(lastname,20,'*') from characters;
LASTNAME LPAD(LASTNAME,20,'*')
------------------------------------ ----------------------------------------
PURVIS **************PURVIS
TAYLOR **************TAYLOR
CHRISTINE ***********CHRISTINE
ADAMS ***************ADAMS
COSTALES ************COSTALES
KONG ****************KONG
6 rows selected
SQL> select lastname,rpad(lastname,20,'*') from characters;
LASTNAME RPAD(LASTNAME,20,'*')
------------------------------------ ----------------------------------------
PURVIS PURVIS**************
TAYLOR TAYLOR**************
CHRISTINE CHRISTINE***********
ADAMS ADAMS***************
COSTALES COSTALES************
5. ltrim,rtrim 截取字符串 ，至少一個參數(shù)
SQL> select lastname,rtrim(lastname) from characters;
LASTNAME RTRIM(LASTNAME)
------------------------------------ ------------------------------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG
6 rows selected
6. replace : 替換
SQL> select lastname,replace(lastname,'ST','') replacement from characters;
LASTNAME REPLACEMENT
------------------------------------ ------------------------------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES
KONG KONG
6 rows selected
SQL> select lastname,replace(lastname,'ST',null) replacement from characters;
LASTNAME REPLACEMENT
------------------------------------ ------------------------------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES
KONG KONG
6 rows selected
SQL> select lastname,replace(lastname,'ST','*') replacement from characters;
LASTNAME REPLACEMENT
------------------------------------ ------------------------------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRI*INE
ADAMS ADAMS
COSTALES CO*ALES
KONG KONG
6 rows selected
7. substr ： 截取字串
SQL> select firstname,substr(firstname,2,3) from characters;
FIRSTNAME SUBSTR(FIRSTNAME,2,3)
------------------------------------ ---------------------
kelly ell
CHUCK HUC
LAURA AUR
FESTER EST
ARMANDO RMA
MAJOR AJO
6 rows selected
SQL> select firstname,substr(firstname,3) from characters;
FIRSTNAME SUBSTR(FIRSTNAME,3)
------------------------------------ --------------------------------------------------------------------
kelly lly
CHUCK UCK
LAURA URA
FESTER STER
ARMANDO MANDO
MAJOR JOR
6 rows selected
8. translate : 翻譯函數(shù) ，三個參數(shù)，對大小寫敏感
select firstname,translate(firstname,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA')
2 from characters;
FIRSTNAME TRANSLATE(FIRSTNAME,'012345678
------------------------------------ ------------------------------------------------------------------------
kelly kelly
CHUCK AAAAA
LAURA AAAAA
FESTER AAAAAA
ARMANDO AAAAAAA
MAJOR AAAAA
6 rows selected
9. instr 搜索函數(shù)
SQL> select lastname, instr(lastname,'O',2,1)from characters;
LASTNAME INSTR(LASTNAME,'O',2,1)
------------------------------------ -----------------------
PURVIS 0
TAYLOR 5
CHRISTINE 0
ADAMS 0
COSTALES 2
KONG 2
6 rows selected
10 lenght : 返回字符串的長度
SQL> select firstname,length(rtrim(firstname)) from characters;
FIRSTNAME LENGTH(RTRIM(FIRSTNAME))
------------------------------------ ------------------------
kelly 5
CHUCK 5
LAURA 5
FESTER 6
ARMANDO 7
MAJOR 5
6 rows selected
轉(zhuǎn)換函數(shù)：
1. to_char: 轉(zhuǎn)換成字符串；
select testnum,length(to_char(testnum)) from conversions;
TESTNUM LENGTH(TO_CHAR(TESTNUM))
------- ------------------------
95 2
23 2
68 2
2. to_number: 轉(zhuǎn)換成數(shù)字；
select name,testnum,testnum*to_number(name) from conversions;
NAME TESTNUM TESTNUM*TO_NUMBER(NAME)
--------------- ------- -----------------------
40 95 3800
13 23 299
74 68 5032
3. greatest , least
SQL> select greatest('ALPHA','BRAVO','FOXTROT','DELTA') FROM CONVERSIONS;
GREATEST('ALPHA','BRAVO','FOXT
------------------------------
FOXTROT
FOXTROT
FOXTROT
SQL> select least(34,567,3,45,1090) from conversions;
LEAST(34,567,3,45,1090)
-----------------------
3
3
3
4. user 查詢表所屬的用戶；
SQL> select user from characters;
USER
------------------------------
oracle