SQL提高及优化

234cfds1

　　一基础单表查询
　　1.1查询表结构
　　desc 表名
　　SQL> desc emp
　　Name                                      Null?    Type
　　EMPNO                                     NOT NULL NUMBER(4)
　　ENAME                                              VARCHAR2(10)
　　JOB                                                VARCHAR2(9)
　　MGR                                                NUMBER(4)
　　HIREDATE                                           DATE
　　SAL                                                NUMBER(7,2)
　　COMM                                               NUMBER(7,2)
　　DEPTNO                                             NUMBER(2)
　　1.2查找空值
　　使用 is null
　　SQL> select empno from emp where comm is null;
　　

EMPNO　　

　　

  7369　　7566
　　7698
　　7782
　　7839
　　7900
　　7902
　　7934
　　

　　8 rows selected.
　　1.3 将空值转换成实际值，推荐使用coalesce
　　SQL> select empno,nvl(comm,0) from emp where comm is null;
　　

EMPNO NVL(COMM,0)　　

　　

  7369           0　　7566           0
　　7698           0
　　7782           0
　　7839           0
　　7900           0
　　7902           0
　　7934           0
　　

　　8 rows selected.
　　SQL> select empno,nvl2(comm,comm,0) from emp where comm is null;
　　

EMPNO NVL2(COMM,COMM,0)　　

　　

  7369                 0　　7566                 0
　　7698                 0
　　7782                 0
　　7839                 0
　　7900                 0
　　7902                 0
　　7934                 0
　　

　　8 rows selected.
　　SQL> select empno,nullif(0,comm) from emp where comm is null;
　　

EMPNO NULLIF(0,COMM)　　

　　

  7369              0　　7566              0
　　7698              0
　　7782              0
　　7839              0
　　7900              0
　　7902              0
　　7934              0
　　

　　8 rows selected.
　　SQL> select empno,coalesce(comm,0) from emp where comm is null;
　　

EMPNO COALESCE(COMM,0)　　

　　

  7369                0　　7566                0
　　7698                0
　　7782                0
　　7839                0
　　7900                0
　　7902                0
　　7934                0
　　

　　8 rows selected.
　　NVL(expr1,expr2)
　　如果expr1和expr2的数据类型一致，则：
　　如果expr1为空(null),那么显示expr2，
　　如果expr1的值不为空，则显示expr1。
　　NVL2(expr1,expr2, expr3)
　　如果expr1不为NULL，返回expr2； expr1为NULL，返回expr3。
　　expr2和expr3类型不同的话，expr3会转换为expr2的类型，转换不了，则报错。
　　NULLIF(expr1,expr2)
　　如果expr1和expr2相等则返回空(NULL)，否则返回expr1。
　　coalesce(expr1, expr2, expr3….. exprn)
　　返回表达式中第一个非空表达式，如果都为空则返回空值。
　　所有表达式必须是相同类型，或者可以隐式转换为相同的类型，否则报错。
　　Coalese函数和NVL函数功能类似，只不过选项更多。
　　1.4 在SELECT语句中使用条件逻辑
　　SQL> select empno,
　　2       ename,
　　3       sal,
　　4       case
　　5         when sal=4000 then '过高'
　　7         else 'OK'
　　8       end as status
　　9    from emp
　　10  where deptno=10;
　　

EMPNO ENAME             SAL STATUS　　

　　

  7782 CLARK            2450 OK　　7839 KING             5000 过高
　　7934 MILLER           1300 过低
　　

　　1.5限制返回行数
　　SQL> select empno from emp where rownum select empno,ename from (select empno,ename from emp order by dbms_random.value()) where rownum select empno,ename from (select empno,ename from emp order by dbms_random.value()) where rownum select TRANSLATE('ab 你好 abcdef','abcdef','123456') as newstring from dual;

NEWSTRING
　　12 你好 123456
　　SQL> select TRANSLATE('ab 你好 abcdef','abcdef','1234') as newstring from dual;

NEWSTRING
　　12 你好 1234
　　SQL> select TRANSLATE('ab 你好 abcdef','acdef','1234') as newstring from dual;

NEWSTRING
　　1b 你好 1b234
　　SQL> select TRANSLATE('ab 你好 abcdef','acdef','') as newstring from dual;

N
　　替换值为空，返回空
　　SQL> select TRANSLATE('ab 你好 abcdef','1abcdef','1') as newstring from dual;

NEWSTRING
　　你好
　　替换wei位置没有字符则删除
　　1.8 混合字符串按字母排序
　　SQL> set line 100
　　SQL> col TRANSLATE(EMPNO||''||ENAME,'-1234567890','-') format A40
　　SQL> select empno||' '||ename as data,translate(empno||' '||ename,'- 1234567890','-') from emp e order by 2 ;

DATA                                                TRANSLATE(EMPNO
''
ENAME,'-1234567890'　　7499 ALLEN                                          ALLEN
　　7698 BLAKE                                          BLAKE
　　7782 CLARK                                          CLARK
　　7902 FORD                                           FORD
　　7900 JAMES                                          JAMES
　　7566 JONES                                          JONES
　　7839 KING                                           KING
　　7654 MARTIN                                         MARTIN
　　7934 MILLER                                         MILLER
　　7369 SMITH                                          SMITH
　　7844 TURNER                                         TURNER

DATA                                                TRANSLATE(EMPNO
''
ENAME,'-1234567890'　　7521 WARD                                           WARD
　　12 rows selected.
　　SQL> select empno||' '||ename as data from emp e order by translate(empno||' '||ename,'- 1234567890','-') ;

DATA
　　7499 ALLEN
　　7698 BLAKE
　　7782 CLARK
　　7902 FORD
　　7900 JAMES
　　7566 JONES
　　7839 KING
　　7654 MARTIN
　　7934 MILLER
　　7369 SMITH
　　7844 TURNER

DATA
　　7521 WARD
　　12 rows selected.
　　1.9 NULL排序使用NULLS FIRST/LAST
　　1.10按条件区不同列中值来排序
　　SQL> select empno,
　　2        ename,
　　3        sal
　　4    from emp
　　5  where deptno=30
　　6  order by Case
　　7             when sal>=1000 and sal  select empno,
　　2        ename,
　　3        sal
　　4    from emp
　　5  where deptno=30
　　6  order by Case
　　7             when sal>=1000 and sal  select 'a' as c1 from dual
　　2  union all
　　3  select '' as c1 from dual;

C
　　a
　　2.2 union与or
　　SQL> select empno,ename from emp where empno=7782 or ename='WARD';
　　

EMPNO ENAME　　

　　

  7521 WARD　　7782 CLARK
　　

　　SQL> select empno,ename from emp where empno=7782
　　2  union
　　3  select empno,ename from emp where ename='WARD';
　　

EMPNO ENAME　　

　　

  7521 WARD　　7782 CLARK
　　


　　SQL>>
　　Session>　　SQL> explain plan for select empno,ename from emp where empno=7782 or ename='WARD';
　　Explained.
　　SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
　　Plan hash value: 3956160932

|>
|   0 | SELECT STATEMENT  |      |     2 |    20 |     3   (0)| 00:00:01 |
　　|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    20 |     3   (0)| 00:00:01 |

Predicate Information (identified by operation>
PLAN_TABLE_OUTPUT
　　1 - filter("EMPNO"=7782 OR "ENAME"='WARD')
　　13 rows selected.
　　SQL> explain plan for select empno,ename from emp where empno=7782
　　2  union
　　3  select empno,ename from emp where ename='WARD';
　　Explained.
　　SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
　　Plan hash value: 1027572458

　　|>　　me     |

PLAN_TABLE_OUTPUT
　　|   0 | SELECT STATEMENT              |        |     2 |    20 |     6  (34)| 00
　　:00:01 |
　　|   1 |  SORT UNIQUE                  |        |     2 |    20 |     6  (34)| 00
　　:00:01 |

2
UNION-ALL　　|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00
　　:00:01 |

PLAN_TABLE_OUTPUT
　　|*  4 |     INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00
　　:00:01 |
　　|*  5 |    TABLE ACCESS FULL          | EMP    |     1 |    10 |     3   (0)| 00
　　:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation>　　4 - access("EMPNO"=7782)
　　5 - filter("ENAME"='WARD')
　　18 rows selected.
　　实际上ENAME也可以建索引那样更快
　　需要注意的
　　SQL> select deptno from emp where EMPNO=7698 or job='SALESMAN' ORDER BY 1;
　　

DEPTNO　　

　　

    30　　30
　　30
　　30
　　30
　　

　　SQL> select deptno,empno from emp where EMPNO=7698 or job='SALESMAN' ORDER BY 1;
　　

DEPTNO      EMPNO　　

　　

    30       7499　　30       7521
　　30       7844
　　30       7698
　　30       7654
　　SQL> select deptno from emp where EMPNO=7698
　　

　　2  union
　　3  select deptno from emp where job='SALESMAN';
　　

DEPTNO　　

　　

    30　　

　　避免这样问题出现可以用唯一列，主键列或rowid
　　SQL> select deptno,empno from emp where EMPNO=7698
　　2  union
　　3  select deptno,empno from emp where job='SALESMAN';
　　

DEPTNO      EMPNO　　

　　

    30       7499　　30       7521
　　30       7654
　　30       7698
　　30       7844
　　

　　SQL> with
　　2    e as (select rownum as sn,deptno,empno,job from emp)
　　3  select deptno
　　4    from
　　5    (
　　6       select sn,deptno from e where  EMPNO=7698
　　7       union
　　8       select sn,deptno from e where job='SALESMAN'
　　9     )
　　10   order by 1;
　　

DEPTNO　　

　　

    30　　30
　　30
　　30
　　30
　　

　　2.3 组合相关的行
　　SQL> select e.empno,e.ename,d.dname,d.loc
　　2  from emp e
　　3  inner join dept d on (e.deptno=d.deptno)
　　4  where e.deptno =10;
　　

EMPNO ENAME      DNAME          LOC　　

　　

  7782 CLARK      ACCOUNTING     NEW YORK　　7839 KING       ACCOUNTING     NEW YORK
　　7934 MILLER     ACCOUNTING     NEW YORK
　　
SQL> select e.empno,e.ename,d.dname,d.loc
　　

　　2  from emp e
　　3  inner join dept d using(deptno)
　　4  where deptno =10;
　　

EMPNO ENAME      DNAME          LOC　　

　　

  7782 CLARK      ACCOUNTING     NEW YORK　　7839 KING       ACCOUNTING     NEW YORK
　　7934 MILLER     ACCOUNTING     NEW YORK
　　

　　2.4 IN,EXISTS和INNER JOIN

　　SQL>>　　alter session set"_b_tree_bitmap_plans"=false
　　*
　　ERROR at line 1:
　　ORA-12571: TNS:packet writer failure
　　SQL> conn scott/tiger@clonepdb_plug
　　Connected.

　　SQL>>
　　Session>　　SQL> explain plan for select empno,ename,job,deptno,sal
　　2  from emp
　　3  where (empno,ename,sal) in (select empno,ename,sal from emp )
　　4  ;
　　Explained.
　　SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
　　Plan hash value: 3956160932

|>
|   0 | SELECT STATEMENT  |      |    12 |   300 |     3   (0)| 00:00:01 |
　　|*  1 |  TABLE ACCESS FULL| EMP  |    12 |   300 |     3   (0)| 00:00:01 |

Predicate Information (identified by operation>
PLAN_TABLE_OUTPUT
　　1 - filter("ENAME" IS NOT NULL AND "SAL" IS NOT NULL)
　　13 rows selected.
　　SQL> explain plan for select empno,ename,job,deptno,sal
　　2  from emp a
　　3  where exists (select null
　　4                from emp b
　　5                where b.ename=a.ename
　　6                and b.job=a.job
　　7                and b.sal=a.sal);
　　Explained.
　　SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
　　Plan hash value: 977554918

|>
|   0 | SELECT STATEMENT   |      |    12 |   516 |     6   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN SEMI    |      |    12 |   516 |     6   (0)| 00:00:01 |
　　|   2 |   TABLE ACCESS FULL| EMP  |    12 |   300 |     3   (0)| 00:00:01 |
　　|   3 |   TABLE ACCESS FULL| EMP  |    12 |   216 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation>　　1 - access("B"."ENAME"="A"."ENAME" AND "B"."JOB"="A"."JOB" AND
　　"B"."SAL"="A"."SAL")
　　16 rows selected.
　　SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
　　Plan hash value: 3638257876

|>
|   0 | SELECT STATEMENT   |      |    12 |   516 |     6   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN         |      |    12 |   516 |     6   (0)| 00:00:01 |
　　|   2 |   TABLE ACCESS FULL| EMP  |    12 |   300 |     3   (0)| 00:00:01 |
　　|   3 |   TABLE ACCESS FULL| EMP  |    12 |   216 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation>　　1 - access("B"."JOB"="A"."JOB" AND "B"."ENAME"="A"."ENAME" AND
　　"B"."SAL"="A"."SAL")
　　16 rows selected.
　　SQL> explain plan for select a.empno,ename,job,sal,a.deptno
　　2  from emp a inner join emp b using(job,ename,sal)
　　3  ;
　　Explained.
　　SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
　　Plan hash value: 3638257876

|>
|   0 | SELECT STATEMENT   |      |    12 |   516 |     6   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN         |      |    12 |   516 |     6   (0)| 00:00:01 |
　　|   2 |   TABLE ACCESS FULL| EMP  |    12 |   300 |     3   (0)| 00:00:01 |
　　|   3 |   TABLE ACCESS FULL| EMP  |    12 |   216 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation>　　1 - access("A"."SAL"="B"."SAL" AND "A"."ENAME"="B"."ENAME" AND
　　"A"."JOB"="B"."JOB")
　　16 rows selected.
　　2.5 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN区别
　　INNER JOIN 返回必配数据
　　LEFT JOIN 左表为主，右表只返回左表匹配数据，右表没有显示的为空  等同于右(+)
　　RIGHT JOIN与上面相反等同于左(+)
　　FULL JOIN 左右表均返回索引数据，匹配的显示一行
　　2.6 自关联
　　SQL> run/
　　1  select a.empno as "员工编号",
　　2       a.ename as "员工姓名",
　　3       a.job as "职位",
　　4       b.empno as "主管编号",
　　5       b.ename  as "主管姓名"
　　6    from emp a
　　7    left join emp b on(a.mgr=b.empno)
　　8* order by 1
　　员工编号 员工姓名   职位        主管编号 主管姓名
　　

  7369 SMITH      CLERK           7902 FORD　　7499 ALLEN      SALESMAN        7698 BLAKE
　　7521 WARD       SALESMAN        7698 BLAKE
　　7566 JONES      MANAGER         7839 KING
　　7654 MARTIN     SALESMAN        7698 BLAKE
　　7698 BLAKE      MANAGER         7839 KING
　　7782 CLARK      MANAGER         7839 KING
　　7839 KING       PRESIDENT
　　7844 TURNER     SALESMAN        7698 BLAKE
　　7900 JAMES      CLERK           7698 BLAKE
　　7902 FORD       ANALYST         7566 JONES
　　

　　员工编号 员工姓名   职位        主管编号 主管姓名
　　

  7934 MILLER     CLERK           7782 CLARK　　

　　12 rows selected.
　　2.7 NOT IN、NOT EXISTS和 LEFT JOIN
　　SQL> select count(*) from emp where deptno =40;

COUNT(*)
　　

     0　　

　　SQL> select * from dept where deptno not in (select deptno from emp where deptno is null);
　　

DEPTNO DNAME          LOC　　

　　

    10 ACCOUNTING     NEW YORK　　20 RESEARCH       DALLAS
　　30 SALES          CHICAGO
　　
SQL> select * from dept where not exists (select null from emp where emp.deptno=dept.deptno );
　　

　　no rows selected
　　SQL> select dept.* from dept left join emp  on dept.deptno=emp.deptno where emp.deptno is null;
　　no rows selected
　　2.8 外连接的条件不能乱放
　　SQL> select dept.* from dept left join emp  on(dept.deptno=emp.deptno and emp.deptno is null);
　　

DEPTNO DNAME          LOC　　

　　

    10 ACCOUNTING     NEW YORK　　20 RESEARCH       DALLAS
　　30 SALES          CHICAGO
　　


　　SQL>>
　　Session>　　SQL> explain plan for select dept.* from dept left join emp  on(dept.deptno=emp.deptno and emp.deptno is null);
　　Explained.
　　SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
　　Plan hash value: 2251696546

　　|>　　me     |

PLAN_TABLE_OUTPUT
　　|   0 | SELECT STATEMENT             |         |     3 |    69 |     6  (17)| 00
　　:00:01 |
　　|   1 |  MERGE JOIN OUTER            |         |     3 |    69 |     6  (17)| 00
　　:00:01 |
　　|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     3 |    60 |     2   (0)| 00
　　:00:01 |
　　|   3 |    INDEX FULL SCAN           | PK_DEPT |     3 |       |     1   (0)| 00
　　:00:01 |

PLAN_TABLE_OUTPUT
　　|*  4 |   SORT JOIN                  |         |     1 |     3 |     4  (25)| 00
　　:00:01 |
　　|*  5 |    TABLE ACCESS FULL         | EMP     |     1 |     3 |     3   (0)| 00
　　:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation>　　4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
　　filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
　　5 - filter("EMP"."DEPTNO"(+) IS NULL)
　　19 rows selected.
　　SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
　　Plan hash value: 1353548327

　　|>　　me     |

PLAN_TABLE_OUTPUT
　　|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00
　　:00:01 |
　　|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00
　　:00:01 |
　　|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     3 |    60 |     2   (0)| 00
　　:00:01 |
　　|   3 |    INDEX FULL SCAN           | PK_DEPT |     3 |       |     1   (0)| 00
　　:00:01 |

PLAN_TABLE_OUTPUT
　　|*  4 |   SORT UNIQUE                |         |    12 |    36 |     4  (25)| 00
　　:00:01 |
　　|   5 |    TABLE ACCESS FULL         | EMP     |    12 |    36 |     3   (0)| 00
　　:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation>　　4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
　　filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
　　18 rows selected.
　　2.9 检查两个表中数据及对应数据条数是否相等
　　SQL> run
　　1  select a.empno,a.ename,b.empno,b.ename
　　2  from emp a
　　3  full join emp b on(b.empno=a.empno)
　　4* where b.empno is null or b.empno is null
　　no rows selected
　　SQL> 4
　　4 where b.empno is null or b.empno is null
　　SQL> del
　　SQL> run
　　1  select a.empno,a.ename,b.empno,b.ename
　　2  from emp a
　　3 full join emp b on(b.empno=a.empno)
　　

EMPNO ENAME           EMPNO ENAME　　

　　

  7369 SMITH            7369 SMITH　　7499 ALLEN            7499 ALLEN
　　7521 WARD             7521 WARD
　　7566 JONES            7566 JONES
　　7654 MARTIN           7654 MARTIN
　　7698 BLAKE            7698 BLAKE
　　7782 CLARK            7782 CLARK
　　7839 KING             7839 KING
　　7844 TURNER           7844 TURNER
　　7900 JAMES            7900 JAMES
　　7902 FORD             7902 FORD
　　

　　EMPNO ENAME           EMPNO ENAME
　　

　　

  7934 MILLER           7934 MILLER　　

　　12 rows selected.
　　2.10多表查询的空值处理
　　比ALLEN提成低的
　　SQL> select a.ename,a.comm
　　2     from emp a
　　3  where coalesce(a.comm,0) create table test(
　　2  c1 varchar2(10) default '默认1',
　　3  c2 varchar2(10) default '默认2',
　　4  c3 varchar2(10) default '默认3',
　　5  c4 date default sysdate
　　6  );
　　Table created.
　　SQL> insert into test(c1,c2,c3) values(default,null,'test');
　　1 row created.
　　SQL> select * from test
　　2  ;
　　C1         C2         C3         C4
　　默认1                 test       2017-12-26 09:46:20
　　3.1阻止对某几列插入
　　SQL> create or replace view v_test as select c1,c2,c3 from test;
　　View created.
　　SQL> insert into V_TEST values ('手输1',null,'不改4');
　　1 row created.
　　SQL> select * from test;
　　C1         C2         C3         C4
　　默认1                 test       2017-12-26 09:46:20
　　手输1                 不改4      2017-12-26 09:57:36
　　SQL> insert into V_TEST values (default,null,'不改4');
　　insert into V_TEST values (default,null,'不改4')
　　*
　　ERROR at line 1:
　　ORA-32575: Explicit column default is not supported for modifying views
　　3.2复制表定义与结构
　　SQL> create table test1 as select * from test where 1=2;
　　Table created.
　　SQL> select * from test1;
　　no rows selected
　　SQL> create table test2 as select * from test;
　　Table created.
　　SQL> select * from test2;
　　C1         C2         C3         C4
　　默认1                 test       2017-12-26 09:46:20
　　手输1                 不改4      2017-12-26 09:57:36
　　3.3利用with check option限制数据输入

　　SQL>>
　　Table>　　SQL> create or replace view v_test1 as select c1,c2,c3 from test with check option;
　　View created.
　　SQL> insert into V_TEST1 values ('test',null,null);
　　insert into V_TEST1 values ('test',null,null)
　　*
　　ERROR at line 1:
　　ORA-01400: cannot insert NULL into ("SCOTT"."TEST"."C3")
　　3.4多表插入语句
　　无条件insert
　　SQL> insert all
　　2    into test1(c1,c2,c3) values ('1','2','3')
　　3    into test2(c1,c2,c3) values ('1','2','3')
　　4    into test(c1,c2,c3) values ('1','2','3')
　　5  select from test1 ;
　　插入次数取决于select 行数
　　要一行的话建议用select  from dual;
　　有条件insert
　　SQL> run
　　1  insert all
　　2    when job in ('CLERK','SALESMAN') then
　　3     into test (c1,c2,c3) values (ENAME,JOB,mgr)
　　4    when job='MANAGER' then
　　5     into test1 (c1,c2,c3) values (ENAME,JOB,mgr)
　　6    else
　　7     into test2 (c1,c2,c3) values (ENAME,JOB,mgr)
　　8 select from emp
　　12 rows created.
　　SQL> select * from test
　　2  ;
　　C1         C2         C3         C4
　　默认1                 test       2017-12-26 09:46:20
　　手输1                 不改4      2017-12-26 09:57:36
　　1          2          3          2017-12-26 10:29:31
　　SMITH      CLERK      7902       2017-12-26 10:39:54
　　ALLEN      SALESMAN   7698       2017-12-26 10:39:54
　　WARD       SALESMAN   7698       2017-12-26 10:39:54
　　MARTIN     SALESMAN   7698       2017-12-26 10:39:54
　　TURNER     SALESMAN   7698       2017-12-26 10:39:54
　　JAMES      CLERK      7698       2017-12-26 10:39:54
　　MILLER     CLERK      7782       2017-12-26 10:39:54
　　10 rows selected.
　　SQL> select * from test1;
　　C1         C2         C3         C4
　　11         12         13
　　JONES      MANAGER    7839
　　BLAKE      MANAGER    7839
　　CLARK      MANAGER    7839
　　SQL> select * from test2;
　　C1         C2         C3         C4
　　默认1                 test       2017-12-26 09:46:20
　　手输1                 不改4      2017-12-26 09:57:36
　　21         22         23
　　KING       PRESIDENT
　　FORD       ANALYST    7566
　　SQL> insert first
　　2    when job in ('CLERK','SALESMAN') then
　　3     into test (c1,c2,c3) values (ENAME,JOB,mgr)
　　4    when empno in (7900,7934,7566) then
　　5     into test1 (c1,c2,c3) values (ENAME,JOB,mgr)
　　6    else
　　7     into test2 (c1,c2,c3) values (ENAME,JOB,mgr)
　　8  select job,ename,mgr,empno from emp;
　　12 rows created.
　　SQL> select * from test;
　　C1         C2         C3         C4
　　SMITH      CLERK      7902       2017-12-26 10:53:18
　　ALLEN      SALESMAN   7698       2017-12-26 10:53:18
　　WARD       SALESMAN   7698       2017-12-26 10:53:18
　　MARTIN     SALESMAN   7698       2017-12-26 10:53:18
　　TURNER     SALESMAN   7698       2017-12-26 10:53:18
　　JAMES      CLERK      7698       2017-12-26 10:53:18
　　MILLER     CLERK      7782       2017-12-26 10:53:18
　　7 rows selected.
　　SQL> select * from test1;
　　C1         C2         C3         C4
　　JONES      MANAGER    7839
　　SQL> select * from test2;
　　C1         C2         C3         C4
　　BLAKE      MANAGER    7839
　　CLARK      MANAGER    7839
　　KING       PRESIDENT
　　FORD       ANALYST    7566
　　3.5Merge into用法总结
　　MERGE INTO table_name alias1
　　USING (table|view|sub_query) alias2
　　ON (join condition)
　　WHEN MATCHED THEN
　　UPDATE table_name
　　SET col1 = col_val1,
　　col2 = col_val2
　　WHEN NOT MATCHED THEN
　　INSERT (column_list) VALUES (column_values);
　　严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数”。
　　3.6删除重复记录
　　SQL> insert into test values (1,2,3,default)
　　2  ;
　　1 row created.
　　SQL> insert into test values (1,2,3,default);
　　1 row created.
　　SQL> select * from test;
　　C1         C2         C3         C4
　　1          2          3          2017-12-26 11:08:14
　　1          2          3          2017-12-26 11:08:18
　　SQL> select rowid as rid,
　　2        c1,
　　3        row_number() over(partition by c1 order by c4) as seq
　　4  from test
　　5  order by 2,3;
　　RID                C1                SEQ
　　AAASXpAALAAAACuAAA 1                   1
　　AAASXpAALAAAACuAAB 1                   2
　　SQL> delete
　　2    from test
　　3   where rowid in (select rid
　　4         from (select rowid as rid,
　　5                row_number() over(partition by c1 order by c4) as seq
　　6               from test)
　　7         where seq>1);
　　1 row deleted.
　　SQL> select * from test;
　　C1         C2         C3         C4
　　1          2          3          2017-12-26 11:08:14
　　SQL> delete
　　2     from test a
　　3   where exists(select /+hash_sj/ null from test b where b.c1=a.c1 and b.rowid>a.rowid);
　　保留最新的 select * from test;
　　C1         C2         C3         C4
　　1          2          3          2017-12-26 13:32:18
　　第四字符串
　　4.1 遍历字符串
　　SQL> select level from dual  connect by level select "拼音",level,substr("拼音",level,1) from (select 'TTXS' as "拼音" FROM DUAL) connect by level  select q'[g'day mate]' qmarks from dual;

QMARKS
　　g'day mate
　　SQL> select q'{g'day mate}' qmarks from dual;

QMARKS
　　g'day mate
　　SQL> select q'