4-5일차 : DML(Data Manipulation Language)

오늘 중요한 부분 : 서브쿼리(Subquery) 꼭 알아야하고 인서트, 업데이트, 딜리트 문법 알아야 한다

지난 포스팅 : '1일차 - Select'

지난 포스팅 : '4일차 - 서브쿼리(Subquery)'

지난 포스팅 : '4일차 - SET연산자'

DML(DATA MANIPLATION LANGUAGE)

데이터 조작어 : INSERT, UPDATE, DELETE, MERGE 4가지

• 이 파트 외우자! ***
  테이블의 행에 대한 삽입,변경,삭제 기능을 제공하는 SQL 명령
  DML 명령 실행 후 COMMIT(DML 명령 적용) 또는 ROLLBACK(DML 명령 취소)

* INSERT : 테이블에 행을 삽입하여 저장하는 명령어***

• 형식 외우기***
  형식)INSERT INTO 테이블명 VALUES(컬럼값,컬럼값,...)

테이블에 삽입될 행의 컬럼값은 테이블 속성에 맞게 차례대로 나열하여 저장되도록 작성
-> 테이블에 선언된 컬럼 순서대로 자료형에 맞는 값을 생략없이 전달하여 저장

--테이블 속성 확인 컬럼과 자료형 확인 
--형식)DESC 테이블명
DESC DEPT;

--DEPT 테이블에 새로운 행(부서정보) 삽입
INSERT INTO DEPT VALUES(50,'회계부','서울');
COMMIT;
SELECT * FROM DEPT;

질의 결과 - DEPT 테이블에 새로운 행(부서정보) 삽입

에러 발생사례

--에러 : 삽입행에 전달될 컬럼값의 갯수가 테이블의 컬럼 갯수와 맞지 않을 경우 에러 발생 
INSERT INTO DEPT VALUES(60,'총무부');
INSERT INTO DEPT VALUES(60,'총무부','인천','02-1234-5678');

--에러 : 삽입행에 전달될 컬럼 값의 자료형이 테이블의 컬럼 자료형과 맞지 않거나 자료형의 크기보다 큰 경우 에러 발생
INSERT INTO DEPT VALUES('육십','총무부','인천'); --'60'은 된다. ''붙이면 숫자값으로 바뀜 
INSERT INTO DEPT VALUES(60,'총무부','인천시 월미구'); --자료형의 크기보다 큰 값 전달해서 에러

--테이블의 컬럼에 부여된 제약조건을 위반하는 값이 전달될 경우 에러 발생 
--NOT NULL 제약조건 : 컬럼에 NULL이 저장되지 않도록 설정하는 제약조건 
INSERT INTO DEPT VALUES(NULL,'총무부','인천');

PK(PRIMARY KEY) 제약조건

: 기존 행의 컬럼값과 같은 값이 저장되지 않도록 설정하는 제약조건

SELECT * FROM DEPT WHERE DEPTNO=50;
INSERT INTO DEPT VALUES(50,'총무부','인천'); --에러 : 이미 위에 다른 값 저장되어있다

FK(FOREIGN KEY) 제약조건

: 부모테이블에 저장된 컬럼값만 자식 테이블에 저장되도록 설정하는 제약조건

--EMP 테이블(자식 테이블) 속성 확인 - DEPTNO 컬럼
DESC EMP;

--DEPT 테이블(부모 테이블) 속성 확인 - DEPTNO 컬럼
DESC DEPT;
SELECT DEPTNO FROM DEPT;

--EMP 테이블에 새로운 행(사원정보)을 삽입
--에러 : DEPT 테이블에(부모 테이블)에 35인 부서번호가 저장되어 있지 않아 EMP 테이블(자식 테이블)에 행 삽입시 에러 발생 
INSERT INTO EMP VALUES(9000,'KIM','MANAGER',7298,'00/12/10',3500,1000,35); --에러 발생

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

질의 결과

--테이블 속성과 제약조건을 위반하지 않는 컬럼값을 전달해야만 행 삽입 
INSERT INTO DEPT VALUES(60,'총무부','인천');
COMMIT;
SELECT * FROM DEPT;

질의 결과

테이블에 행 삽입시 컬럼값을 저장하고 싶지 않은 경우 NULL을 전달하여 저장

INSERT INTO DEPT VALUES(70,'영업부',NULL); --명시적 NULL 사용 
COMMIT;
SELECT * FROM DEPT;

테이블의 원하는 속성에만 컬럼값을 전달하여 행을 삽입하여 저장 가능

형식)INSERT INTO 테이블명(컬럼명,컬럼명,...) VALUES(컬럼값,컬럼값,...)
INSERT 명령의 테이블에 나열된 컬럼 순서대로 컬럼값을 전달하여 행을 삽입

INSERT INTO DEPT(LOC,DEPTNO,DNAME) VALUES('수원',80,'자재부');
COMMIT;
SELECT * FROM DEPT;

INSERT 명령의 테이블에 나열된 컬럼 생략 가능
-> 생략된 컬럼에는 컬럼 기본값(DEFAULT VALUE)이 자동 저장
-> 테이블 생성 또는 테이블 구조 변경시 컬럼 기본값을 설정하지 않은 경우 컬럼 기본값은 자동으로 NULL로 설정

INSERT INTO DEPT(DEPTNO,DNAME) VALUES(90,'인사부'); --LOC 칼럼 생략 : NULL 저장 - 묵시적 NULL 사용 
COMMIT;
SELECT * FROM DEPT;

질의 결과

날짜형 컬럼에는 날짜값 대신 SYSDATE 키워드를 이용하여 컬럼값 저장

INSERT INTO EMP VALUES(9001,'LEE','ANALYST',9000,SYSDATE,2000,NULL,40);
COMMIT;
SELECT * FROM EMP WHERE EMPNO=9001;

질의 결과

INSERT 명령에서 SUBQUERY 사용 가능 : 테이블의 행을 검색하여 검색결과를 다른 테이블의 행으로 삽입 - 테이블 행 복사

형식)INSERT INTO 테이블명 SELECT 검색대상,... FROM 테이블명 [WHERE 조건식]
행이 삽입될 테이블의 속성과 서브쿼리의 검색대상에 대한 구조(갯수,자료형) - 컬럼명 제외

BONUS 테이블의 속성 확인 및 행 검색

DESC BONUS;
SELECT * FROM BONUS;

스크립트 출력
질의 결과

EMP 테이블에서 성과급이 NULL이 아닌 사원을 검색하여 검색행을 BONUS 테이블에 삽입

INSERT INTO BONUS SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;
COMMIT;
SELECT * FROM BONUS;

질의 결과

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

• 형식도 외우기
  형식)UPDATE 테이블명 SET 컬럼명=변경값,컬럼명=변경값,... WHERE 조건식
  --PK 제약조건이 설정된 컬럼값은 변경하지 않는 것을 권장
  --WHERE 구문이 생략된 경우 테이블에 저장된 모든 행의 컬럼값을 동일하게 변경
  --조건식에서 사용될 컬럼은 PK 제약조건이 설정된 컬럼을 사용하는 것을 권장

DEPT 테이블에서 부서번호가 50인 부서를 검색
--부서이름 : 회계부, 부서위치 : 서울

SELECT * FROM DEPT WHERE DEPTNO=50;

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

질의 결과 - 스크립트 출력 - 질의 결과

변경값은 컬럼의 자료형,크기,제약조건에 맞는 경우에만 변경 가능

--EMP 테이블에서 사원번호가 9000인 사원의 부서번호를 35로 변경 
UPDATE EMP SET DEPTNO=35 WHERE EMPNO=9000; --FK 제약조건 위반하여 에러 발생, DEPNO 35는 없음 

에러 내용 : 
UPDATE EMP SET DEPTNO=35 WHERE EMPNO=9000
오류 보고 -
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

UPDATE 명령에서 서브쿼리 사용 가능 : 변경값 또는 조건식의 비교값 대신 서브쿼리 사용

--DEPT 테이블에서 부서명이 영업부인 부서위치(NULL)를 총무부의 부서위치(인천)와 같도록 변경 
UPDATE DEPT SET LOC=(SELECT LOC FROM DEPT WHERE DNAME='총무부') WHERE DNAME='영업부';
COMMIT;
SELECT * FROM DEPT WHERE DNAME='영업부';

질의 결과

--BONUS 테이블에서 사원이름이 KIM인 사원보다 성과급이 적은 사원의 성과급을 100 증가되도록 변경
SELECT * FROM BONUS;
UPDATE BONUS SET COMM=COMM+100 WHERE COMM<(SELECT COMM FROM BONUS WHERE ENAME='KIM');
COMMIT;
SELECT * FROM BONUS;

질의 결과 - 100 증가전(기본 성과급) - 100 증가후 

* DELETE : 테이블에 저장된 행을 삭제하는 명령

형식)DELETE FROM 테이블명 WHERE 조건식
WHERE 구문이 생략된 경우 테이블에 저장된 모든 행 삭제
조건식에서 사용될 컬럼은 PK 제약조건이 설정된 컬럼을 사용하는 것을 권장

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

DEPT 테이블에서 부서번호가 10인 부서 삭제
->FK 제약조건에 의해 자식 테이블(EMP)이 참조하는 부모 테이블(DEPT)의 행은 삭제 불가능

DELETE FROM DEPT WHERE DEPTNO=10; --에러

--EMP 테이블(자식 테이블)에서 모든 사원의 부서번호를 중복없이 검색
SELECT DISTINCT DEPTNO FROM EMP; --검색결과 : 10,20,30,40
--EMP 테이블이 참조하는 DEPT 테이블의 부서를 제외한 나머지 부서정보는 삭제 가능 
DELETE FROM DEPT WHERE DEPTNO=20; --에러발생, 삭제 불가
DELETE FROM DEPT WHERE DEPTNO=80; --삭제 가능 
COMMIT;
SELECT * FROM DEPT;

DELETE 명령에서 서브쿼리 사용 가능 : 조건식의 비교값 대신 서브쿼리를 이용

--DEPT 테이블에서 부서이름이 영업부인 부서와 같은 부서위치의 부서정보 삭제 - 영업부 포함 
SELECT * FROM DEPT;
DELETE FROM DEPT WHERE LOC=(SELECT LOC FROM DEPT WHERE DNAME='영업부');
COMMIT;
SELECT * FROM DEPT;

질의 결과

MERGE : 소스 테이블의 행을 검색하여 타겟 테이블에 행을 삽입하거나 행의 컬럼값을 변경하는 명령 - 테이블 동기화

잘 사용하진 않으니 그냥 이런게 있다 정도
형식)MERGE INTO 타겟테이블 USING 소스테이블 ON (조건식)
WHEN MATCHED THEN UPDATE SET 타겟컬럼명=소스컬럼명,타겟컬럼명=소스컬럼명,...
-- WHEN NOT MATCHED THEN INSERT(타겟컬럼명,타겟컬럼명,...) VALUES(소스컬럼명,소스컬럼명,...)

DEPT 테이블과 동일한 속성의 MERGE_DEPT 테이블 생성

CREATE TABLE MERGE_DEPT(DEPTNO NUMBER(2),DNAME VARCHAR2(10),LOC VARCHAR2(11));
DESC MERGE_DEPT;

MERGE_DEPT 테이블에 행 삽입

INSERT INTO MERGE_DEPT VALUES(30,'총무부','서울');
INSERT INTO MERGE_DEPT VALUES(60,'자재부','인천');
COMMIT;
SELECT * FROM MERGE_DEPT;

질의 결과 

DEPT 테이블(소스 테이블)에 저장된 모든 부서정보를 검색하여 MERGE_DEPT 테이블(타겟 테이블)에 삽입 또는 변경 - 테이블 별칭 사용

SELECT * FROM DEPT; --소스 테이블
SELECT * FROM MERGE_DEPT; --타겟 테이블


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); --병합

SELECT * FROM MERGE_DEPT;

질의 결과 - 소스 테이블 - 병합