oracle基础复习总结

 

1.  插入新记录 （P55）

l  默认值的列用“ default 关键字”代替字段值，最后一列除外（可以为空，系统默认获取默认值）

         如：insert  into  test(a1,a2,a3,a4) values (default,值二, 值三,值四);

                               

2.  阻止对表某些列插入数据 （P56）

l  通过用VIEW (视图) 控制

注意：通过view新增数据，默认值字段不能再使用关键字default 赋值

 

3.  复制表的定义及数据 （P57）

l  复制表定义及表数据

语句：create table test2 as select * from tset ;

l  只是复制表定义

语句：create table test2 as select * from test where 1=2 ；

注意：复制的表不包括默认值等约束信息，使用这样方式复制表后，需要重新建

      默认值及索引和约束等信息

 

4.  多表插入数据语句 （P58）

                 创建测试表：

                 语句：create table emp1 as select empno,ename,job from emp where 1=2 ;

           create table emp2 as select empno,ename,deptno from emp where 1=2 ;

l  无条件INSERT

语句insert all

                     into emp1 (empno,ename,job) values(empno,ename,job)

                        into emp2 (empno,ename,deptno) values(empno,ename,deptno)

             select empno,ename,job, deptno from emp where deptno in (10,20) ;

注意：因为没有加条件，所有会同时向两表插入相同条数的数据

 

l  有条件 INSERT ALL

                                 语句：insert all

                                                    when job in ('salesman','manager') then

                    into emp1 values(empno,ename,job)

                 when deptno in ('20','30') then

                                                                               insert emp2 values(empno,ename,deptno)

                                                 select empno,ename,job,deptno from emp ;

                                注意：当加上条件后，就会按符合条件的数据插入，并且是两个条件都会去判断是                                          插入数据， 雷同两个if判断条件

                               

l  有条件INSERT FIRST

              语句：insert first

                                when job in ('salesman','manager') then  

                                      into emp1 values(empno,ename,job)

                                when deptno in ('20','30') then  

                                      insert emp2 values(empno,ename,deptno)

                           select empno,ename,job,deptno from emp ;

    注意：insert first 语句中，当第一个表符合条件后，第二个表将不再插入对应的行数据，雷同两个if... else 判断条件

l  转置 INSERT

 语句： insert all 

                                    into t2 (d,des) values ('周一',d1)  

                                    into t2 (d,des) values ('周一',d2)  

                                     into t2 (d,des) values ('周一',d3)                     

                                        into t2 (d,des) values ('周一',d4)  

                                                into t2 (d,des) values ('周一',d5)  

                                 select d1,d2,d3,d4,d5 from t1;  

     注意：转置INSERT 的实质就是把不同列的数据插入到一列的不同行中

               

5.  删除名称重复的记录数据 （P71）

l  通过NAME 相同、ID不同判断 (方法一)

         如：delete from dupes a where exists (selete null from dupes b where b.name=a.name

                                                and b.id > a.id ) ; 

l  用ROWID 来代替其中的ID

         如：delete from dupes a where exists ( select /*+ hash_sj * / null from dupes b where

    b.name = a.name and b.rowid >a.rowid ) ;

l  通过分析函数根据 NAME 字段分组生成序号，然后删除序号大于1的数据

             如：delete from dupes where rowid in (

                         select rid from (

                                 select rowid as rid,

                                           row_number() over(partition by name order by id) as seq

                                     from dupes)

                          where seq >1); 

                               

6.  数据库遍历字符串（P75）

l  CONNECT BY 树形遍历 ( level：伪列，表示树形中的级别层次，通过level >= 3 循环3次 )

如：select level from dual connect by level <=3 ;

  LEVEL  

---------------

   1

   2

   3

3 rows selected

 

7.         字符串文字中包含引号（P76）

l  Oracle 10 g  q-quote 特性

?  q-quote 界定符可以除了 TAB 、空格 、回车外的任何单字节或多字节字符

?  界定符可以是 {}、[]、<>、(),而且必须成对出现  

                 如：select 'xu''zhid' from dual; 

         select q'{xu'zhid}' from dual; 

        select q'<xu'zhid>' from dual; 

                    

8.  计算分隔符的个数 （P77）

l  单个字符的分隔符

?  Oracle 11 g 新函数 REGEXP_COUNT

  如：select regexp_count('123,456,78,9',',') as v from dual;

?  函数REGEXP_REPLACE 迂回求值

                              如：select length(regexp_replace('12,34,56,7,8','[^,]')) as v from dual;

?  函数 translate

                              如：select length(translate('12,34,56,7,9',','||'12,34,56,7,9',',')) as a from dual;

                                               

l  多个字符的分隔度 （先计算出字符串长度，再除于分隔符的长度）

?  函数 regexp_count ：不用考虑长度

如：select regexp_count('12,a#,a#6,a#9','a#') as A from dual;  

                                      

9.  从字符串中删除不需要的字符（P78）

例题：把员工名字中有元音字母(AEIOU)，要求把元音字母去掉。

l  方法一：先把所有员工名字中有元音字母的字母全部替换成a ，然后去掉

?  函数 replace('A1','A','') : 将A1里的A 删除

?  函数 translate('A1', AEIOU,'KKKK') : 将A1里的AEIOU全替换成K  

        如：select replace(translate('FGAPUW','AEIOU','aaaaa'),'a','') as A from dual ;

 

l  方法二：

?  函数TRANSLATE(string,from_str,to_str) 

     如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符; to_str 不能为空

如：select  translate('EFGAPUW','1AEIOU','1')  as A from dual ;

l  方法三：

?  正则函数 REGEXP_PEPLACE  :直接把 [ ] 内列举的字符替换为空(删除)

如：select regexp_replace('EFGAPUW','[AEIOU]') as A from dual;  

 

?  函数replace('将要更改的字符串','被替换掉的字符串','替换字符串')

 

10. 将字符和数据 数据分离 (P79)

l  函数REGEXP_REPLACE('将要分离的字符串','分离正则条件') 
   如：select regexp_replace('EFGAPUW123','[0-9]') as A, regexp_replace('EFGAPUW123','[^0-9]') as B from dual;  

         注意：正则 [0-9] 是一种表示方式，代表 [0123456789] ,还代表 [[ :digit:]] ;

               正则 [^0-9] 中'^' 符号表示否定的意思，代表 [0-9] 的外集 ;

               如果 '^' 符号不在方括号内(如直接写为 '^hell'),则表示字符串的开始位置              

 

11. 查询只包含字母或者数字类型的数据 (P80)

l  函数 REGEXP_LIKE(字段名,'[ 模糊查询的条件 ]')

?  regexp_like对应普通的like

?  regexp_like(date,'[A,B,C]')就相当于(date like '%A% or date like '%B% or date like '%C% ')

?  regexp_like(date,'[0-9a-zA-Z]+')就相当于(date like '%数字%' or date like '%小写字母%' or date like '%大写字母%' )

注意：“$”符号在方括号外面，表示字符串的结束符

?  regexp_like(date,'A') 对应普通的 like '%A%'

?  前面加 "^" regexp_like(date,'^A')对应普通的 like 'A%',没有了前模糊查询

?  后面加 "$" regexp_like(date,'A') 对应普通的 like '%A',没有了后模糊查询

?  前后加 "^%" regexp_like(date,'A') 对应普通的 like 'A' ,变成了精确查询  

 

?  “+”与“*”解析

?  "+" 表示匹配前面的子表达式一次或多次

    regexp_like(date,'16+') 加号前的子表达式是 "6" ,至少匹配6一次，也就相当于 ( like '16%' or like '166%' or . . . ) ,等价于 like '16%' 

?  "*" 表示匹配前面的子表达式零次或多次 

    regexp_like(date,'16*) 星号前的子表达式是 "6" ,至少匹配 6 零次，也就是相当于(like '1%' or like '16%' or like '166%' or . . . ) ,等价于 like '1%'   

                                                                 

12.     根据表的行创建一个分隔列表 (P88)

l  聚集函数 LISTAGG(str,'分隔符')  within group (order by 排序字段)

 如：select deptno ,listagg(ename,',') within group (order by ename) as total_name

          from emp group by deptno ;

l  表函数：WMSYS.WM_CONCAT --(10 g已经提供该函数)

                                 如：select deptno, WMSYS.WM_CONCAT(ename) as A from scott.emp group by  deptno;

               

13.     提取第几个分隔符的子串 (P88)

l  函数 REGEXP_SUBSTR(string,'分隔符', 开始分隔符, 结束分隔符)

                                 如：select regexp_substr('123,112,323,11','[^,]+',1,2) as A from dual;

                                注意：参数1：需要进行正则处理的字符串

             参数2: 进行匹配的正则表达式。

                                                     "^" 表示否的意思；"+" 表示匹配一次以上；'[^,]+' 表示匹配不                              包含逗号的多个字符，也就是本节中的各个子串  

                                             参数3：起始位置，从第几个字符开始正则表达式匹配（默认为1）

              参数4：标识第几个匹配组，默认为1   

               

14.     分析IP地址(拆分字符)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30028528/viewspace-1990747/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30028528/viewspace-1990747/