Dust Of Dream

近日公司的Oracle牛人給我們開發人員做了一次有關Oracle的培訓,感覺收獲頗大,故記錄下來,好他日溫習之用.

一 常用的SQL語句

1. select name,count(*) from table where .. group by ... 中能查詢的字段只能為group by的字段.

2. select * from table where rownum < 5 order by id 中查詢出來的結果不是按數據中的ID排序的,而只是將select * from table where rownum < 5 的結果集按ID排序,所以如果你要按ID排序,你需要用子查詢實現:
   select * from ( select * from table order by id ) where rownum < 5 

3. select * from table where name like 'A\_%' escape '\';將'\'后面的字符不當關鍵字來處理,這個字符可以自定義.

4. insert into test(id,name) values(9,'It''s life'); or ||chr(39)|| 如果你想插入'可以使用''或者||chr(39)||方式插入.

5. 如果你想將T1中B更新為T2中的B值,千萬要注意限定T1的范圍,否則T1的全部列將會更新,如update t1 t set t.B = (select tt.B from t2 tt where tt.A = t.A)將會t1中所有列都更新,如果t2中不存在對應值,則t1中的值則為NULL,所以應該將以上語句改造成update t1 t set t.B = (select tt.B from t2 tt where tt.A = t.A) where t.A in (select A from t2)

6. number(5,2):如果用 insert into test values(123.235)進行插入時,將會使用四舍五入的方式插入即值為123.24;如果是insert into test values(12345)則無法插入數據

二 Oracle 函數

1. 一般函數是數據庫設定的字符集來計算,現在一般的oracle都是16位,所以一個漢字長度為1,而函數后面加b則按字節來計算如:length('中國')=2 lenghtb('中國')=4 .
2. Substr與substrb 字符串截取函數,負數代表從右開始截取
   SQL> select substr('我是中國人',2) from dual;
   
   SUBSTR('我是中國人',2)
   ----------------------
   是中國人
   
   SQL> select substrb('我是中國人',2) from dual;
   
   SUBSTRB('我是中國人',2)
   -----------------------
    是中國人
   
   SQL> select substr('我是中國人',-2) from dual;
   
   SUBSTR('我是中國人',-2)
   -----------------------
   國人
   
   SQL> select substrb('我是中國人',-2) from dual;
   
   SUBSTRB('我是中國人',-2)
   ------------------------
   人
   
   Length與lengthb 長度計算函數
   
   SQL> select length('我是中國人') from dual;
   
   LENGTH('我是中國人')
   --------------------
                      5
   
   SQL> select lengthb('我是中國人') from dual;
   
   LENGTHB('我是中國人')
   ---------------------
                      10
   Instr與Instrb 字符串查找函數 instr(原字符串,查的字符串,起始位置,第幾個匹配) 返回字符串位置,找不到返回0 .
   
   SQL> select Instr('abcabcdabcdef','a',1,3) from dual;
   
   INSTR('ABCABCDABCDEF','A',1,3)
   ------------------------------
                                8
   
   Upper與lower 大小寫轉換函數
   SQL> select upper('AaBbCc') from dual;
   
   UPPER('AABBCC')
   ---------------
   AABBCC
   
   SQL> select lower('AaBbCc') from dual;
   
   LOWER('AABBCC')
   ---------------
   aabbcc
   
   Trim/Rtrim/Ltrim 字符串trim函數
   SQL> select trim(' A B ') from dual;
   
   TRIM('AB')
   ----------
   A B
   
   SQL> select rtrim('xABx','x') from dual;
   
   RTRIM('XABX','X')
   -----------------
   xAB
   
   SQL> select ltrim('xABx','x') from dual;
   
   LTRIM('XABX','X')
   -----------------
   ABx
   
   Trunc 截取函數(不進行四舍五入)
   
   SQL> select trunc(1234.123456,'-2') from dual;
   
   TRUNC(1234.123456,'-2')
   -----------------------
                      1200
   
   SQL> select trunc(1234.123456,'2') from dual;
   
   TRUNC(1234.123456,'2')
   ----------------------
                  1234.12
   
   SQL> select trunc(1234.123456,'4') from dual;
   
   TRUNC(1234.123456,'4')
   ----------------------
                1234.1234
   
   SQL> select trunc(1234.123456,'5') from dual;
   
   TRUNC(1234.123456,'5')
   ----------------------
               1234.12345
   
   SQL> select trunc(sysdate,'yy') from dual;
   
   TRUNC(SYSDATE,'YY')
   -------------------
   2007-01-01
   
   SQL> select trunc(sysdate,'mi') from dual;
   
   TRUNC(SYSDATE,'MI')
   -------------------
   2007-10-01 11:55:00
   
   SQL> select trunc(sysdate,'dd') from dual;
   
   TRUNC(SYSDATE,'DD')
   -------------------
   2007-10-01
   
   SQL> select trunc(sysdate,'day') from dual;
   
   TRUNC(SYSDATE,'DAY')
   --------------------
   2007-09-30
   Next_day與last_day
   SQL> select sysdate from dual;
   
   SYSDATE
   -----------
   2007-10-01
   
   SQL> select next_day(sysdate,'星期一') from dual;
   
   NEXT_DAY(SYSDATE,'星期一')
   --------------------------
   2007-10-08 11:57:29
   
   SQL> select next_day(sysdate,1) from dual;
   
   NEXT_DAY(SYSDATE,1)
   -------------------
   2007-10-07 11:57:42
   
   SQL> select next_day(sysdate,2) from dual;
   
   NEXT_DAY(SYSDATE,2)
   -------------------
   2007-10-08 11:57:56
   
   SQL> select last_day(sysdate) from dual;
   
   LAST_DAY(SYSDATE)
   -----------------
   2007-10-31 12:00:
   Round 四舍五入函數
   SQL> select round(123.456,2) from dual;
   
   ROUND(123.456,2)
   ----------------
             123.46
   
   SQL> select round(123.456,-2) from dual;
   
   ROUND(123.456,-2)
   -----------------
                 100
   
   SQL> select round(123.456,-1) from dual;
   
   ROUND(123.456,-1)
   -----------------
                 120
   
   Ceil與floor 取整函數
   SQL> select ceil(1.1) from dual;
   
    CEIL(1.1)
   ----------
            2
   
   SQL> select floor(9.9) from dual;
   
   FLOOR(9.9)
   ----------
            9
   Decode與nvl Decode相當于一個三元運算函數 nvl 如果值為空時默認值.