Oracle SQL培训笔记[开发人员][一]

近日公司的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 如果值为空时默认值.