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

--插入、更新与删除--1.插入新纪录--1.1.建立测试表DROP TABLE TEST;CREATE TABLE TEST(C1 VARCHAR2(10) DEFAULT '默认1',C2 VARCHAR2(10) DEFAULT '默认2',C3 VARCHAR2(10) DEFAULT '默认3',C4 DATE DEFAULT SYSDATE);--1.2.增加数据INSERT INTO TEST(C1,C2,C3) VALUES(DEFAULT,NULL,'手输值');--1.3.查询SELECT * FROM TEST;--1.4.总结/** *1.如果INSERT INTO 语句中没有包含有默认值的列，如C4，则会插入默认值； *2.如果有包含默认值的列，则应该用DEFAULT关键字才能添加默认值，如C1； *3.如果已显示设定了NULL或其他值，则不会产生默认值，如C2和C3。 */--2.复制表及数据CREATE TABLE TEST2 AS SELECT * FROM TEST;--或者先复制表结构，然后再插入数据CREATE TABLE TEST2 AS SELECT * FROM TEST WHERE 1=2;/*复制的表不包含默认值等约束条件，使用这种方式复制表后，需要重建默认值及索引和约束等信息*/--3.用WITH CHECK OPTION限制数据录入INSERT INTO (SELECT EMPNO,ENAME,HIREDATE FROM EMPWHERE 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 TABLE EMP1;DROP TABLE EMP2;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 ALLINSERT 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 * FROM EMP1;EMPNO ENAME      JOB----- ---------- ---------SMITH      CLERKJONES      MANAGERCLARK      MANAGERSCOTT      ANALYSTKING       PRESIDENTADAMS      CLERKFORD       ANALYSTMILLER     CLERKSELECT * FROM EMP2;EMPNO ENAME      DEPTNO----- ---------- ------SMITH          20JONES          20CLARK          10SCOTT          20KING           10ADAMS          20FORD           20MILLER         10--4.2.有条件INSERT ALLDELETE EMP1;DELETE EMP2;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 FROM EMP;SELECT * FROM EMP1;EMPNO ENAME      JOB----- ---------- ---------ALLEN      SALESMANWARD       SALESMANJONES      MANAGERMARTIN     SALESMANBLAKE      MANAGERCLARK      MANAGERTURNER     SALESMANSELECT * FROM EMP2;EMPNO ENAME      DEPTNO----- ---------- ------SMITH          20ALLEN          30WARD           30JONES          20MARTIN         30BLAKE          30SCOTT          20TURNER         30ADAMS          20JAMES          30FORD           20--4.3.有条件INSERT FIRSTDELETE EMP1;DELETE EMP2;INSERT FIRST       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 FROM EMP;SELECT * FROM EMP1;EMPNO ENAME      JOB----- ---------- ---------ALLEN      SALESMANWARD       SALESMANJONES      MANAGERMARTIN     SALESMANBLAKE      MANAGERCLARK      MANAGERTURNER     SALESMANSELECT * FROM EMP2;  EMPNO ENAME      DEPTNO----- ---------- ------SMITH          20SCOTT          20ADAMS          20JAMES          30FORD           20 /*INSERT ALL与INSERT FIRST不同的是：当第一个表符合条件后，第二个表将不再插入对应的行，表二中不再有与表一相同的数据EMPNO=7654*/    --4.4.转置INSERTDROP TABLE T1;DROP TABLE T2;CREATE TABLE T2(D VARCHAR2(10),DES VARCHAR2(50));CREATE TABLE T1 ASSELECT '陇西行四首·其二' AS D1,       '誓扫匈奴不顾身' AS D2,       '五千貂锦丧胡尘' AS D3,       '可怜无定河边骨' AS D4,       '犹是春闺梦里人' AS D5FROM DUAL;/*转置 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 FROM T1;SELECT * FROM T1; D1                      D2                    D3                    D4                    D5----------------------- --------------------- --------------------- --------------------- ---------------------陇西行四首·其二         誓扫匈奴不顾身        五千貂锦丧胡尘        可怜无定河边骨        犹是春闺梦里人SELECT * FROM T2;D          DES---------- --------------------------------------------------诗名       陇西行四首·其二第一句     誓扫匈奴不顾身第二句     五千貂锦丧胡尘第三句     可怜无定河边骨第四句     犹是春闺梦里人--5.用其他表中的值更新--对emp表新增字段，然后把dept.dname更新到emp中ALTER TABLE SCOTT.EMP ADD DNAME VARCHAR2(50) DEFAULT 'NONAME';--传统方式（UPDATE .. EXISTS)UPDATE SCOTT.EMP SET SCOTT.EMP.DNAME=(SELECT SCOTT.DEPT.DNAME FROM SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO=SCOTT.EMP.DEPTNO AND SCOTT.DEPT.DEPTNO IN ('10','20')) WHERE EXISTS (SELECT SCOTT.DEPT.DNAME FROM SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO=SCOTT.EMP.DEPTNOAND SCOTT.DEPT.DEPTNO IN ('10','20'));SELECT * FROM SCOTT.EMP;http://www.cnblogs.com/yw0219/p/5981097.html--升级方式(MERGE INTO .. USING .. ON .. WHEN MATCHED THEN .. WHEN NOT MATCHED THEN ..)ROLLBACK;MERGE INTO SCOTT.EMP EUSING (SELECT SCOTT.DEPT.DEPTNO,SCOTT.DEPT.DNAME FROM SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO IN ('10','20')) DON (E.DEPTNO=D.DEPTNO)WHEN MATCHED THENUPDATE SET E.DNAME=D.DNAME;SELECT * FROM SCOTT.EMP;/*建议使用MERGE方法*/MERGE INTO SCOTT.EMP EUSING (SELECT SCOTT.DEPT.DEPTNO,SCOTT.DEPT.DNAME FROM SCOTT.DEPT ) DON (E.DEPTNO=D.DEPTNO)WHEN MATCHED THENUPDATE SET E.DNAME=D.DNAMEWHERE E.DEPTNO IN ('10','20')DELETEWHERE (E.DEPTNO='30');--注意：THEN后面的语句中增加了DELETE子句，它在UPDATE之后，它所对应的全集是UPDATE语句中指定的范围，例子中UPDATE的范围不包含30部门，因此DELETE失效了，若改为10或20则有效ALTER TABLE SCOTT.EMP DROP COLUMN DNAME;--6.删除违反参照完整性约束--6.1.建立测试条件--初始化ALTER TABLE SCOTT.EMP DROP CONSTRAINTS FK_DEPTNO;ALTER TABLE SCOTT.DEPT DROP CONSTRAINTS PK_DEPT;--为DEPT表添加主键PK_DEPTALTER TABLE SCOTT.DEPT ADD CONSTRAINTS PK_DEPT PRIMARY KEY (DEPTNO);--为EMP表中添加一条数据，DEPTNO不在DEPT表的范围内，如：47 INSERT INTO SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)SELECT 9999 AS EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,'47' AS DEPTNO FROM SCOTT.EMP WHERE ROWNUM<=1;--为EMP表添加外键FK_DEPTNOALTER TABLE SCOTT.EMP ADD CONSTRAINTS FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT(DEPTNO);--此时会报错，因为EMP表中的外键对应的是DEPT表中的主键，而DEPT表中没有DEPTNO='47'的数据ORA-02298: cannot validate (SCOTT.FK_DEPTNO) - parent keys not found--6.2.删除字表EMP中的非法数据DELETE FROM SCOTT.EMP WHERE NOT EXISTS(      SELECT NULL FROM SCOTT.DEPT      WHERE SCOTT.DEPT.DEPTNO=SCOTT.EMP.DEPTNO);--效果与如下语句等效：DELETE FROM SCOTT.EMP WHERE EMPNO=9999;--7.删除名称重复的记录--7.1.建立测试条件DROP TABLE DUPES;CREATE TABLE DUPES (ID INTEGER,NAME VARCHAR2(20));INSERT INTO DUPES VALUES (1,'CATHERINE');INSERT INTO DUPES VALUES (2,'DYNAMITE');INSERT INTO DUPES VALUES (3,'DYNAMITE');INSERT INTO DUPES VALUES (4,'SERLINA');INSERT INTO DUPES VALUES (5,'CATHERINE');INSERT INTO DUPES VALUES (6,'MALFURION');INSERT INTO DUPES VALUES (7,'ILLIDAN STORMRAGE');--可以看到，CATHERINE和DYNAMITE两个人的数据有重复--方法一：DELETE FROM DUPES A WHERE EXISTS (SELECT NULL FROM DUPES B WHERE A.ID>B.ID AND A.NAME=B.NAME);SELECT * FROM DUPES;                                     ID NAME--------------------------------------- --------------------                                      1 CATHERINE                                      2 DYNAMITE                                      4 SERLINA                                      6 MALFURION                                      7 ILLIDAN STORMRAGE--利用这种方式需要建立组合索引CREATE INDEX IDX_ID_NAME ON DUPES(ID,NAME);--方法二：DELETE FROM DUPES AWHERE EXISTS (SELECT NULL FROM DUPES B WHERE B.NAME=A.NAME AND B.ROWID>A.ROWID);SELECT * FROM DUPES;                                     ID NAME--------------------------------------- --------------------                                      3 DYNAMITE                                      4 SERLINA                                      5 CATHERINE                                      6 MALFURION                                      7 ILLIDAN STORMRAGE--方法三：SELECT ROWID AS RID,NAME,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ID) AS SEQ FROM DUPES ORDER BY 2,3;   RID                NAME                        SEQ------------------ -------------------- ----------AAAVRMAAIAAAdJ4AAA CATHERINE                     1AAAVRMAAIAAAdJ4AAE CATHERINE                     2AAAVRMAAIAAAdJ4AAB DYNAMITE                      1AAAVRMAAIAAAdJ4AAC DYNAMITE                      2AAAVRMAAIAAAdJ4AAG ILLIDAN STORMRAGE             1AAAVRMAAIAAAdJ4AAF MALFURION                     1AAAVRMAAIAAAdJ4AAD SERLINA                       1--删除SEQ为2的数据DELETE FROM DUPES WHERE ROWID IN (SELECT ROWID FROM (SELECT ROWID AS RID,NAME,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ID) AS SEQ FROM DUPES)WHERE SEQ > 1) ;