oracle 删除 优化,Oracle查询优化-插入、更新与删除

--插入、更新与删除--1.插入新纪录--1.1.建立测试表

DROP TABLETEST;CREATE TABLETEST(

C1VARCHAR2(10) DEFAULT ‘默认1‘,

C2VARCHAR2(10) DEFAULT ‘默认2‘,

C3VARCHAR2(10) DEFAULT ‘默认3‘,

C4 DATEDEFAULTSYSDATE

);--1.2.增加数据

INSERT INTO TEST(C1,C2,C3) VALUES(DEFAULT,NULL,‘手输值‘);--1.3.查询

SELECT * FROMTEST;--1.4.总结/**

*1.如果INSERT INTO 语句中没有包含有默认值的列，如C4，则会插入默认值；

*2.如果有包含默认值的列，则应该用DEFAULT关键字才能添加默认值，如C1；

*3.如果已显示设定了NULL或其他值，则不会产生默认值，如C2和C3。*/

--2.复制表及数据

CREATE TABLE TEST2 AS SELECT * FROMTEST;--或者先复制表结构，然后再插入数据

CREATE TABLE TEST2 AS SELECT * FROM TEST WHERE 1=2;/*复制的表不包含默认值等约束条件，使用这种方式复制表后，需要重建默认值及索引和约束等信息*/

--3.用WITH CHECK OPTION限制数据录入

INSERT INTO (SELECT EMPNO,ENAME,HIREDATE FROMEMPWHERE HIREDATE <= SYSDATE WITH CHECK OPTION)VALUES (9999,‘TEST‘,SYSDATE+1)

ORA-01402: view WITH CHECK OPTION where-clause violation--视图WITH CHECK OPTION的where子句违规--4.多表插入语句--建立测试表

DROP TABLEEMP1;DROP TABLEEMP2;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;--4.1.无条件INSERT ALL

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);SELECT * FROMEMP1;

EMPNO ENAME JOB----- ---------- ---------

7369SMITH CLERK7566JONES MANAGER7782CLARK MANAGER7788SCOTT ANALYST7839KING PRESIDENT7876ADAMS CLERK7902FORD ANALYST7934MILLER CLERKSELECT * FROMEMP2;

EMPNO ENAME DEPTNO----- ---------- ------

7369 SMITH 20

7566 JONES 20

7782 CLARK 10

7788 SCOTT 20

7839 KING 10

7876 ADAMS 20

7902 FORD 20

7934 MILLER 10

--4.2.有条件INSERT ALL

DELETEEMP1;DELETEEMP2;INSERT ALL

WHEN JOB IN (‘SALESMAN‘,‘MANAGER‘) THEN

INTO EMP1(EMPNO,ENAME,JOB)VALUES(EMPNO,ENAME,JOB)WHEN DEPTNO IN(‘20‘,‘30‘)THEN

INTO EMP2(EMPNO,ENAME,DEPTNO)VALUES(EMPNO,ENAME,DEPTNO)SELECT EMPNO,ENAME,JOB,DEPTNO FROMEMP;SELECT * FROMEMP1;

EMPNO ENAME JOB----- ---------- ---------

7499ALLEN SALESMAN7521WARD SALESMAN7566JONES MANAGER7654MARTIN SALESMAN7698BLAKE MANAGER7782CLARK MANAGER7844TURNER SALESMANSELECT * FROMEMP2;

EMPNO ENAME DEPTNO----- ---------- ------

7369 SMITH 20

7499 ALLEN 30

7521 WARD 30

7566 JONES 20

7654 MARTIN 30

7698 BLAKE 30

7788 SCOTT 20

7844 TURNER 30

7876 ADAMS 20

7900 JAMES 30

7902 FORD 20

--4.3.有条件INSERT FIRST

DELETEEMP1;DELETEEMP2;INSERTFIRSTWHEN JOB IN (‘SALESMAN‘,‘MANAGER‘) THEN

INTO EMP1(EMPNO,ENAME,JOB)VALUES(EMPNO,ENAME,JOB)WHEN DEPTNO IN(‘20‘,‘30‘) THEN

INTO EMP2(EMPNO,ENAME,DEPTNO) VALUES(EMPNO,ENAME,DEPTNO)SELECT EMPNO,ENAME,JOB,DEPTNO FROMEMP;SELECT * FROMEMP1;

EMPNO ENAME JOB----- ---------- ---------

7499ALLEN SALESMAN7521WARD SALESMAN7566JONES MANAGER7654MARTIN SALESMAN7698BLAKE MANAGER7782CLARK MANAGER7844TURNER SALESMANSELECT * FROMEMP2;

EMPNO ENAME DEPTNO----- ---------- ------

7369 SMITH 20

7788 SCOTT 20

7876 ADAMS 20

7900 JAMES 30

7902 FORD 20

/*INSERT ALL与INSERT FIRST不同的是：当第一个表符合条件后，第二个表将不再插入对应的行，表二中不再有与表一相同的数据EMPNO=7654*/

--4.4.转置INSERT

DROP TABLET1;DROP TABLET2;CREATE TABLE T2(D VARCHAR2(10),DES VARCHAR2(50));CREATE TABLE T1 AS

SELECT ‘陇西行四首·其二‘ ASD1,‘誓扫匈奴不顾身‘ ASD2,‘五千貂锦丧胡尘‘ ASD3,‘可怜无定河边骨‘ ASD4,‘犹是春闺梦里人‘ ASD5FROMDUAL;/*转置 INSERT ALL*/

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 FROMT1;SELECT * FROMT1;

D1 D2 D3 D4 D5----------------------- --------------------- --------------------- --------------------- ---------------------

陇西行四首·其二 誓扫匈奴不顾身 五千貂锦丧胡尘 可怜无定河边骨 犹是春闺梦里人SELECT * FROMT2;

D DES---------- --------------------------------------------------

诗名 陇西行四首·其二

第一句 誓扫匈奴不顾身

第二句 五千貂锦丧胡尘

第三句 可怜无定河边骨

第四句 犹是春闺梦里人