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;