[sql] 9. DML (INSERT/UPDATE/DELETE/MERGE)

1. DML - 데이타 조작어

• DATA MANIPULATION LANGUAGE
• 테이블 행에 대한 삽입,변경,삭제 기능을 제공하는 SQL 명령
• DML 명령 실행 후 COMMIT명령(DML 명령의 적용) 또는 ROLLBACK명령(DML 명령의 취소)을 실행하는 것을 권장
• 그래야지 실제테이블에 적용이 됨

 

 

 

 

 

2. INSERT - 행 삽입

• 테이블에 행을 삽입하는 명령

1) 형식

INSERT INTO 테이블명 VALUES(컬럼값, 컬럼값,...)

2) 기본적인 행 삽입법

-- DEPT테이블에 행 삽입하기 - 50

-- 테이블에 삽입될 행의 컬럼값은 테이블 속성에 맞게 차례대로 나열하여 전달되도록 작성해야함
-- 테이블 속성에 맞게 = 테이블의 순서나 자료형의 맞게
DESC DEPT;

-- 테이블의 속성 순서대로 자료형에 맞는 컬럼값을 생략없이 차례대로 전달되어야함
--DEPT테이블에 새로운 행(부서정보) 삽입
--순서)
--DEPTNO:NUMBER(2) -> DANAME:VARCHAR2(14) -> LOC:VARCHAR2(13)

INSERT INTO DEPT VALUES(50, '회계부', '서울시'); --삽입완료
SELECT * FROM DEPT;
COMMIT; --저장완료

3) 행 삽입시 주의사항

• 삽입행으로 전달된 컬럼값의 갯수가 테이블의 컬럼 갯수와 맞지 않을 경우 에러 발생

--전달값이 충분하지 않아 ERROR 발생 (컬럼값이 3개여야 삽입 가능)
INSERT INTO DEPT VALUES(50, '총무부'); --ERROR

--전달값이 너무 많아 ERROR 발생 (컬럼값이 3개여야 삽입 가능)
INSERT INTO DEPT VALUES(50, '총무부','인천시','031-1234-5678');  --ERROR

• 삽입행으로 전달된 컬럼값이 테이블의 컬럼 자료형과 맞지 않거나 자료형의 크기보다 큰 경우 에러 발생

--컬럼의 자료형과 맞지 않은 값을 전달하여 에러 발생
INSERT INTO DEPT VALUES('육십', '총무부', '인천시'); --ERROR

--컬럼 자료형의 크기보다 큰 컬럼값을 전달하여 에러 발생
INSERT INTO DEPT VALUES(60, '총무부', '인천시 월미구'); --ERROR

--자동형변환되어 삽입이 됨..!
--오라클은 자료형의 자동형변환이 자유로운편
INSERT INTO DEPT VALUES('60', '총무부', '인천시'); --OK이니 주의하자!

• 테이블의 컬럼에 부여된 "제약조건"을 위반하는 값이 전달될 경우 에러 발생

-- 🔥 PK(PRIMARY KEY)제약조건 : 테이블에 저장된 기존행의 컬럼값과 중복된 값이 저장되지 않도록 설정하는 제약조건

--EX) DEPT테이블의 'DEPTNO컬럼'에는 PK 제약조건이 설정되어 있음
SELECT * FROM DEPT;
SELECT DISTINCT DEPTNO FROM DEPT; --검색결과: 10, 20, 30, 40, 50


--PK제약조건을 위반한 컬럾값을 전달하여 에러 발생 (무결성 제약 조건에 위배)
-- 데이타 무결성 유지 : 테이블 속성과 컬럼의 제약조건을 위반하지 않는 값을 전달해야만 행 삽입이 가능함
-- 데이타 무결성 : 테이블에 잘못된 값이 저장되지 않도록 하여 정상적인 결과가 되도록 하는 성질
INSERT INTO DEPT VALUES(50, '총무부', '인천시'); --ERROR


--PK제약조건이 부여된 컬럼이라 NULL 삽입 불가능
--(PK제약조건이 부여된 컬럼에는 자동으로 NOT NULL이 설정되어 있음)
INSERT INTO DEPT VALUES(NULL, '총무부', '인천시');  --ERROR

4) 데이타 무결성 유지법

-- DEPT테이블에 행 삽입하기 - 60

INSERT INTO DEPT VALUES(60, '총무부', '인천시'); --삽입
SELECT * FROM DEPT;
COMMIT; --저장

5) 강제 NULL 삽입법

-- DEPT테이블에 행 삽입하기 - 70

-- 명시적 NULL 사용 : 테이블의 행 삽입 시 컬럼에 값을 저장하고 싶지 않은 경우 NULL 전달하여 삽입 처리
INSERT INTO DEPT VALUES(70,'영업부',NULL); --명시적 NULL 사용
SELECT * FROM DEPT;
COMMIT; --저장

6) 특정컬럼값만 삽입하는 법

-- DEPT테이블에 행 삽입하기 - 80

-- 특정 테이블의 컬럼에 값을 전달하여 행 삽입 가능
-- 오라클에서 사용 잘 안함
-- 컬럼을 생략해서 행을 삽입하고 싶을 때
INSERT INTO 테이블명 VALUES(컬럼값, 컬럼값,...) VALUES(컬럼값, 컬럼값,...)


-- 테이블에 나열된 컬럼 순서대로 값을 전달하여 행 삽입
INSERT INTO DEPT(LOC,DEPTNO,DNAME) VALUES('수원시',80,'자재부');
SELECT * FROM DEPT;
COMMIT; --저장

7) 자동 NULL 삽입법

-- DEPT테이블에 행 삽입하기 - 90

--묵시적 NULL 사용
--테이블의 컬럼이 생략되면 생략된 컬럼에는 컬럼의 기본값이 자동으로 전달되어 삽입 처리
--오라클에서 사용 잘 안함
--테이블 생성 또는 테이블 구조 변경시 컬럼 기본값(COLUMN DEFAULT VALUES) 설정 가능
--컬럼 기본값을 미설정시 NULL을 기본값으로 자동 설정
INSERT INTO DEPT(DEPTNO,DNAME) VALUES(90,'인사부'); --LOC 컬럼 생략 : NULL 전달되어 삽입 - 묵시적 NULL 사용
SELECT * FROM DEPT;
COMMIT; --저장

8) SYSDATE키워드를 이용해 행 삽입법

-- EMP테이블에 행 삽입하기 - 9000, 9001

DESC EMP;

INSERT INTO EMP VALUES(9000,'KIM','MANAGER',7298,'00/12/10',3500,1000,40);
SELECT * FROM EMP;

--날짜형 컬럼에는 날짜값 대신 SYSDATE키워드를 사용하여 값을 전달하여 삽입 처리
INSERT INTO EMP VALUES(9001,'LEE','ANALYST',9000,SYSDATE,2000,NULL,40);

SELECT * FROM EMP;
COMMIT;

9) 행 복사법

--  BONUS테이블에 행 삽입하기
-- INSERT 명령에 서브쿼리(SUBQUERY)를 사용하여 행 삽입 가능

-- 형식
-- 서브쿼리의 검색결과를 이용해 테이블에 행 삽입 가능
-- 다른 테이블의 행을 검색하여 현재 테이블에 행을 삽입할 수 있도록 함 - 테이블 행 복사

INSERT INTO 테이블명 SELECT 검색대상,... FROM 테이블명 [WHERE 조건식]

 

주의)

-- [행이 삽입될 테이블의 속성]과 [서브쿼리의 검색대상에 대한 속성]이 동일해야함
-- [서브쿼리의 검색대상에 대한 속성] : (컬럼명을 제외한 검색대상의 갯수, 자료형, 크기)


--1)
--BONUS테이블의 속성 확인및 행 검색
DESC BONUS;
SELECT * FROM BONUS;


--2)
--EMP테이블에서 성과급이 존재하는 사원을 검색하여 BONUS테이블에 삽입 처리
INSERT INTO BONUS SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;
SELECT * FROM BONUS;
COMMIT;

1)

2)

 

 

 

 

 

 

 

3. UPDATE - 행 변경

• 테이블에 저장된 행의 컬럼값을 변경하는 명령

1) 형식

UPDATE 테이블명 SET 컬럼명=변경값, 컬럼명=변경값,... [WHERE 조건식]

2) 행 변경시 주의사항

-- 주의 1 ) WHERE의 조건식이 참인 행만 검색하여 컬럼값 변경하는 것을 권장
-- 만약 WHERE가 생략된 경우 테이블에 저장된 모든 행의 컬럼값을 동일하게 변경됨!!
UPDATE 테이블명 SET 컬럼명=변경값, 컬럼명=변경값,...  --모든 행 통일한 값으로ㅠㅠ

-- 주의 2) WHERE에서 사용되는 조건식의 컬럼은 PK 제약조건이 부여된 컬럼을 사용하는 것을 권장
-- 왜? 단일행을 검색하여 컬럼값을 변경하기 위해서
-- 변경이 되면 FK제약조건(PK참조하는 제약조건)에 의한 데이타 무결성을 위반할 수 있으므로
-- PK제약조건이 부여된 컬럼값은 변경하지 않는 것을 권장
UPDATE 테이블명 SET 컬럼명=변경값, 컬럼명=변경값,...  WHERE PK 제약조건의 컬럼을 이용한 조건식

3) UPDATE 예시

--1)
--DEPT테이블에서 부서번호가 50인 부서정보 검색
SELECT * FROM DEPT WHERE DEPTNO=50; --부서이름: 회계부, 부서위치: 서울시



--2)
--DEPT테이블에서 부서번호가 50인 부서의 부서이름을 경리부로 변경하고 부서위치를 부천시로 변경
UPDATE DEPT SET DNAME='경리부', LOC='부천시' WHERE DEPTNO=50;
SELECT * FROM DEPT WHERE DEPTNO=50; --부서이름: 경리부, 부서위치: 부천시
COMMIT;




--3)
--컬럼의 변경값은 컬럼의 자료형, 크기, 제약조건에 맞는 경우에만 변경 가능
--변경값의 크기가 LOC 컬럼의 크기보다 커서 에러 발생
UPDATE DEPT SET DNAME='부천시 원미구' WHERE DEPTNO=50; --error




--4)
-- UPDATE 명령에서 서브쿼리를 사용 가능 - 변경값 대신 서브쿼리 사용
--DEPT테이블에서 부서 이름이 영업부인 부서위치를 총무부의 부서위치와 같도록 변경
SELECT * FROM DEPT;
UPDATE DEPT SET LOC=(SELECT LOC FROM DEPT WHERE DNAME='총무부') WHERE DNAME='영업부';
SELECT * FROM DEPT;
COMMIT;




--5)
-- UPDATE 명령에서 서브쿼리를 사용 가능
-- 조건식의 비교값 대신 서브쿼리 사용
--BONUS 테이블에서 사원이름이 KIM인 사원보다 성과급이 적은 사원의 성과급을 100 증가되도록 변경
SELECT * FROM BONUS;
--6)
UPDATE BONUS SET COMM=COMM+100 WHERE COMM<(SELECT COMM FROM BONUS WHERE ENAME='KIM');
SELECT * FROM BONUS;
COMMIT;

1)

2)

4) update 전

4)

4) update 후

5)

6)

 

 

 

 

 

 

 

 

 

 

 

 

 

4. DELETE - 행 삭제

• 테이블에 저장된 행을 삭제하는 명령

1) 형식

DELETE FROM 테이블명 [WHERE 조건식]

2) 행 삭제시 주의사항

-- 주의 1) WHERE의 조건식이 참인 행만 검색하며 삭제 처리
-- WHERE가 생략된 경우 테이블에 저장된 모든 행 삭제

-- 주의 2) WHERE에서 사용되는 조건식의 컬럼은 PK 제약조건이 부여된 컬럼을 사용하는 것을 권장
-- 왜? 단일행을 검색하여 삭제하기 때문

3) DELETE 예시

--1)
--DEPT테이블에서 부서번호가 90인 부서정보 삭제
SELECT * FROM DEPT;
DELETE FROM DEPT WHERE DEPTNO=90;
SELECT * FROM DEPT;
COMMIT;


--2)
-- 자식테이블에서 참조되는 부모테이블의 컬럼값은 FK제약조건에 의해 변경이나 삭제 처리 불가능
-- 🔥 FK(FOREIGN KEY) 제약조건 : 자식테이블의 컬럼값이 부모테이블의 컬럼값을 참조하여 저장되도록 설정하는 제약조건
-- EMP테이블(자식테이블)의 부서번호(DEPTNO)는 DEPT테이블(부모테이블)의 부서번호(DEPTNO)를 참조하여 저장되도록 FK 제약조건 부여
-- 즉, 자식테이블에는 DEPT테이블(부모테이블)의 부서번호(DEPTNO)가 있음
--DEPT테이블에서 부서번호가 10인 부서정보 삭제 - FK제약조건을 위반하여 에러 발생
DELETE FROM DEPT WHERE DEPTNO=10;  --ERROR


--3)
-- DEPT테이블에서는 EMP테이블이 참조하지 않는 부서정보만 삭제 가능
-- EMP테이블(자식테이블)에서 모든 사원의 중복되지 않은 유일한 부서번호 검색
SELECT DISTINCT DEPTNO FROM EMP; -- 검색결과: 10, 20 , 30, 40


--4)
-- DEPT테이블(부모테이블)에서 부서번호가 20인 부서정보 삭제
DELETE FROM DEPT WHERE DEPTNO=20; --ERROR , FK제약조건을 위반하여 에러 발생 (참조하고 있음)
-- DEPT테이블(부모테이블)에서 부서번호가 80인 부서정보 삭제
DELETE FROM DEPT WHERE DEPTNO=80;  --OK
SELECT * FROM DEPT;
COMMIT;


--5)
-- 서브쿼리이용
-- DELETE 명령에서 서브쿼리 사용 가능
-- WHERE의 조건식에 비교값 대신 서브쿼리 사용
--DEPT테이블에서 부서이름이 영업부인 부서와 같은 위치의 부서정보 삭제 - 영업부 포함
SELECT * FROM DEPT;
DELETE FROM DEPT WHERE LOC=(SELECT LOC FROM DEPT WHERE DNAME='영업부');
SELECT * FROM DEPT;
COMMIT;

1)

3)

4)

5)

 

 

 

 

 

 

 

 

 

 

5. MERGE - 행 병합

• 오라클에만 있음
• 원본 테이블의 행을 검색하여 타겟 테이블에 행을 삽입하거나 저장행의 컬럼값을 변경하는 명령

1) 형식

MERGE INTO 타켓테이블명 USING 원본테이블명 ON (조건식)
WHEN MATCHED THEN UPDATE SET 타겟컬럼명=원본컬럼명, 타겟컬럼명=원본컬럼명,...
WHEN NOT MATCHED THEN INSERT(타켓컬럼명,타켓컬럼명,...) VALUES (원본컬럼명,원본컬럼명,...)

2) MERGE 예시

--DEPT테이블과 동일한 속성의 MERGE_DEPT 테이블 생성
--1)
DESC DEPT;
CREATE TABLE MERGE_DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13));
DESC MERGE_DEPT;


--2)
INSERT INTO MERGE_DEPT VALUES(30,'총무부','서울시');
INSERT INTO MERGE_DEPT VALUES(60,'자재부','수원시');
SELECT * FROM MERGE_DEPT;
COMMIT;



--DEPT테이블(원본테이블)에 저장된 모든 부서정보를 검색하여
--MERGE_DEPT테이블(타겟테이블)에 행을 삽입하거나
--MERGE_DEPT테이블에 저장된 행의 컬럼값을 변경
--3)
SELECT * FROM DEPT; --원본테이블
--4)
SELECT * FROM MERGE_DEPT; --타겟테이블


--5)
-- M.DEPTNO=D.DEPTNO 라면 -> UPDATE SET M.DNAME=D.DNAME, M.LOC=D.LOC 해주세요.
-- 같지 않다면 -> INSERT(M.DEPTNO,M.DNAME,M.LOC) VALUES(D.DEPTNO,D.DNAME,D.LOC)해주세요.
MERGE INTO MERGE_DEPT M USING DEPT D ON (M.DEPTNO=D.DEPTNO)
    WHEN MATCHED THEN UPDATE SET M.DNAME=D.DNAME, M.LOC=D.LOC
    WHEN NOT MATCHED THEN INSERT(M.DEPTNO,M.DNAME,M.LOC) VALUES(D.DEPTNO,D.DNAME,D.LOC);
    
    
    
 --6)   
SELECT * FROM MERGE_DEPT; --타겟테이블

1)

2)

3)

4)

6)