--插入、更新与删除
--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('','')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('','') 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 ('',''))
WHERE EXISTS (
SELECT SCOTT.DEPT.DNAME FROM SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO=SCOTT.EMP.DEPTNO
AND SCOTT.DEPT.DEPTNO IN ('',''));
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 ('','')) 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 ('','')
DELETE
WHERE (E.DEPTNO='');
--注意：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,'' 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) ;