flyleer

一 常用的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 如果值為空時默認值.
   

三 null的那些事

1. 在order 中，簡單把null認為是最大
2. 與null的運算，返回null
   SQL> select 1 + null from dual;
   
       1+NULL
   ----------
3. 與null的字符串合并，忽略null
   SQL> select 'Hi'||null from dual;
   
   'HI'||NULL
   ----------
   Hi
4. Null的查詢為is null
5. Count(field)，不包括null
6. 如果索引條目全為null，則索引不記錄null
7. In/not in與null
8. Exists/not exists與null
   SQL> select * from t1;
   
   A          B
   ---------- ----------
   1          1
   2          
   3          
   
   SQL> select * from t2;
   
   A          B
   ---------- ----------
   1          1
   2          
   
   SQL> select * from t1 where b in (select B from t2);
   
   A          B
   ---------- ----------
   1          1
   
   SQL> select * from t1 where b not in (select B from t2);
   
   A          B
   ---------- ----------
   
   SQL> select * from t1 where exists (select * from t2 where t2.b = t1.b);
   
   A          B
   ---------- ----------
   1          1
   
   SQL> select * from t1 where not exists (select * from t2 where t2.b = t1.b);
   
   A          B
   ---------- ----------
   3          
   2          
   
   exists主要用于片面的,有滿足一個條件的即可,  所以速度快很多.    in   主要用于具體的集合操作,   有多少滿足條件.

四 索引與分頁--怎么樣SQL運行的更快

1. 正確的使用索引
   Where條件落在索引上
   不要在where的=前使用函數，否則無法使用索引
   Is Null可能無法使用索引
   不正確的隱式轉換可能不能使用索引
   如果能在索引獲得數據，就不要回表
   如果是復合索引，注意第2個字段以后，可能使用不到索引
2. 正確的使用hint
   如果有別名，一定要有別名
   格式如/*+ index(t index_name) */
3. 無需回表查詢的分頁寫法
   存在以下表T1(A,B,C,D) T1上有索引字段(B,C) .如果只是查B,C兩個字段則:
   select *
     from (select tt.b, tt.c, rownum as rn
             from (select t.b, t.c from t1 t where c = 2 order by t.c) tt
            where rownum < 3)
    where rn > 1
4. 需回表查詢的分頁寫法
   
   select /*+ ordered use_nl(t,t1) */ 
         * 
          from (select rid from (
             select rownum rn,rid from (
               select rowid rid from t1
                where c=2 
                order by c desc) 
             where rownum <= 50) 
          where rn >=1) t,
          t1
   where t.rid=t1.rowid;