--插入、更新与删除 --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 EMP WHERE 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 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 * FROM EMP1; EMPNO ENAME JOB ----- ---------- --------- SMITH CLERK JONES MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT ADAMS CLERK FORD ANALYST MILLER CLERK SELECT * FROM EMP2; EMPNO ENAME DEPTNO ----- ---------- ------ SMITH 20 JONES 20 CLARK 10 SCOTT 20 KING 10 ADAMS 20 FORD 20 MILLER 10 --4.2.有条件INSERT ALL DELETE 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 SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER TURNER SALESMAN SELECT * FROM EMP2; EMPNO ENAME DEPTNO ----- ---------- ------ SMITH 20 ALLEN 30 WARD 30 JONES 20 MARTIN 30 BLAKE 30 SCOTT 20 TURNER 30 ADAMS 20 JAMES 30 FORD 20 --4.3.有条件INSERT FIRST DELETE 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 SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER TURNER SALESMAN SELECT * FROM EMP2; EMPNO ENAME DEPTNO ----- ---------- ------ SMITH 20 SCOTT 20 ADAMS 20 JAMES 30 FORD 20 /*INSERT ALL与INSERT FIRST不同的是：当第一个表符合条件后，第二个表将不再插入对应的行，表二中不再有与表一相同的数据EMPNO=7654*/ --4.4.转置INSERT DROP TABLE T1; DROP TABLE T2; CREATE TABLE T2(D VARCHAR2(10),DES VARCHAR2(50)); CREATE TABLE T1 AS SELECT '陇西行四首·其二' AS D1, '誓扫匈奴不顾身' AS D2, '五千貂锦丧胡尘' AS D3, '可怜无定河边骨' AS D4, '犹是春闺梦里人' AS D5 FROM 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.DEPTNO AND 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 E USING (SELECT SCOTT.DEPT.DEPTNO,SCOTT.DEPT.DNAME FROM SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO IN ('10','20')) D ON (E.DEPTNO=D.DEPTNO) WHEN MATCHED THEN UPDATE SET E.DNAME=D.DNAME; SELECT * FROM SCOTT.EMP; /*建议使用MERGE方法*/ MERGE INTO SCOTT.EMP E USING (SELECT SCOTT.DEPT.DEPTNO,SCOTT.DEPT.DNAME FROM SCOTT.DEPT ) D ON (E.DEPTNO=D.DEPTNO) WHEN MATCHED THEN UPDATE SET E.DNAME=D.DNAME WHERE E.DEPTNO IN ('10','20') DELETE WHERE (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_DEPT ALTER 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_DEPTNO ALTER 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 A WHERE 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 1 AAAVRMAAIAAAdJ4AAE CATHERINE 2 AAAVRMAAIAAAdJ4AAB DYNAMITE 1 AAAVRMAAIAAAdJ4AAC DYNAMITE 2 AAAVRMAAIAAAdJ4AAG ILLIDAN STORMRAGE 1 AAAVRMAAIAAAdJ4AAF MALFURION 1 AAAVRMAAIAAAdJ4AAD 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) ;