DML
(Data Manupulation Language) 데이터 조작 언어
INSERT, UPDATE, DELETE, SELECT
테이블에 값을 삽입하거나, 수정하거나, 삭제하거나, 조회하는 언어
1. INSERT
새로운 행을 추가하는 구문이다. 테이블의 행 갯수가 증가한다.
- 테이블의 일부 컬럼에 INSERT할 때
INSERT INTO 테이블명 (컬럼명, 컬럼명, ...) VALUES (데이터, 데이터, ...);
- 테이블의 모든 컬럼에 INSERT할 때
INSERT INTO 테이블명 VALUES (데이터, 데이터, ...);
하지만 모든 컬럼에 INSERT할 때에도 컬럼명을 기술하는 것이 의미 파악에 더 좋다.
INSERT INTO EMPLOYEE E (
E.EMP_ID, E.EMP_NAME, E.EMP_NO, E.EMAIL, E.PHONE
, E.DEPT_CODE, E.JOB_CODE, E.SAL_LEVEL, E.SALARY, E.BONUS
, E.MANAGER_ID, E.HIRE_DATE, E.ENT_DATE, E.ENT_YN
)
VALUES (
'900', '장도현', '901123-208503', 'jang_ch@greedy.com', '01055678904'
, 'D1', 'J7', 'S3', 40000000, '0.5'
, '200', SYSDATE, NULL, DEFAULT
);
- INSERT시에 VALUES 대신 서브쿼리를 이용할 수 있다.
INSERT
INTO 테이블명1
VALUES (
컬럼명, 컬럼명, ..
)
/* 서브쿼리 */
(SELECT
FROM
WHERE
);
실습 )
EMPLOYEE 테이블에서 값을 가져와 저장할 테이블을 하나 만든다.
CREATE TABLE EMP_01(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR(20)
);
서브쿼리를 사용하여 EMPLOYEE 테이블의 컬럼으로 EMP_01 테이블의 컬럼 행을 추가할 수 있다.
INSERT INTO EMP_01 A
(
A.EMP_ID,
A.EMP_NAME,
A.DEPT_TITLE
)
(
SELECT E.EMP_ID
, E.EMP_NAME
, D.DEPT_TITLE
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE= D.DEPT_ID)
);
1-1. INSERT ALL
INSERT시에 사용하는 서브쿼리가 같은 경우
두개 이상의 테이블에 INSERT ALL을 이용하여 한 번에 데이터를 삽입할 수 있다.
단, 각 서브쿼리의 조건절이 같아야 한다.
[표현식]
INSERT ALL
INTO 테이블명1
VALUES (
컬럼명, 컬럼명, ..
)
INTO 테이블명2
VALUES (
컬럼명, 컬럼명, ...
)
/* 서브쿼리 */
(SELECT
FROM
WHERE
);
실습 )
우선 D1 부서의 직원들의 정보만 복사할 EMP_DEPT_D1 테이블을 만든다.
CREATE TABLE EMP_DEPT_D1
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE 1=0; -- 무조건 FALSE인 조건( 데이터의 구조만 복사)
다음으로는 매니저의 ID를 저장할 EMP_MANAGER 테이블도 만들어 준다.
CREATE TABLE EMP_MANAGER
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE 1 =0;
문제 )
-- EMP_DEPT_D1 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을
-- 조회해서 사번, 이름, 소속부서, 입사일을 삽입하고,
-- EMP_MANAGER 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을
-- 조회해서 사번, 이름, 관리자 사번을 삽입하세요.
INSERT INTO EMP_DEPT_D1 A
(
A.EMP_ID,
A.EMP_NAME,
A.DEPT_CODE,
A.HIRE_DATE
)
(SELECT E.EMP_ID,
E.EMP_NAME,
E.DEPT_CODE,
E.HIRE_DATE
FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1'
);
INSERT INTO EMP_MANAGER A
(
A.EMP_ID
, A.EMP_NAME
, A.MANAGER_ID
)
(SELECT E.EMP_ID
, E.EMP_NAME
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1'
);
이렇게 두 개의 쿼리문을 작성할 수 있다.
하지만 INSERT ALL로 코드의 반복작성을 줄일 수 있다.
이 때 서브쿼리의 WHERE 조건절이 같아야 한다.
INSERT ALL
INTO EMP_DEPT_D1
VALUES
(
EMP_ID
, EMP_NAME
, DEPT_CODE
, HIRE_DATE
)
INTO EMP_MANAGER
VALUES
(
EMP_ID
, EMP_NAME
, MANAGER_ID
)
(SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.HIRE_DATE
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1'
);
2. UPDATE
테이블에 기록 된 컬럼의 값을 수정하는 구문이다. 테이블의 전체 행 갯수는 변화가 없다.
[표현식]
UPDATE 테이블명 SET 컬럼명 = 바꿀값, 컬럼명 = 바꿀값, ...
[WHERE 컬럼명 비교연산자 비교값]
WHERE 조건이 맞는 행만 수정이 된다.
조건이 없을 경우 전체 값이 변경된다.
UPDATE
DEPARTMENT D
SET D.DEPT_TITLE = '전략기획팀'
WHERE D.DEPT_ID= 'D9';
UPDATE시에도 서브쿼리를 사용할 수 있다.
UPDATE 테이블명
SET 컬럼명 = (서브쿼리)
문제 )
--평상시 송은희 사원을 부러워하던 김쑥 사원의
-- 급여와 보너스율을 송은희 사원과 동일하게 변경해주기로 했다.
-- 이를 반영하는 UPDATE 문을 작성해보세요.
UPDATE EMP_SALARY ES
SET ES.SALARY = (SELECT E1.SALARY
FROM EMPLOYEE E1
WHERE E1.EMP_NAME = '송은희'
)
, ES.BONUS = (SELECT E2.BONUS
FROM EMPLOYEE E2
WHERE E2.EMP_NAME = '송은희'
)
WHERE ES.EMP_NAME = '김쑥';
- 다중열 서브쿼리를 이용한 UPDATE문
문제 )
-- 김쑥 사원의 급여 인상 소식을 전해 들은 다른 직원들이 단체로 파업을 진행했다.
-- 안연미, 심봉선, 길신영 사원의 급여와 보너스를
-- 송은희 사원의 급여와 보너스를 같게 변경하는 UPDATE문 작성
UPDATE
EMP_SALARY ES
SET (ES.SALARY, ES.BONUS) = (SELECT E1.SALARY, E1.BONUS
FROM EMPLOYEE E1
WHERE E1.EMP_NAME = '송은희'
)
WHERE ES.EMP_NAME IN ('안연미','심봉선','길신영');
- 다중행 서브쿼리를 이용한 UPDATE
문제 )
EMP_SALARY 테이블에서 아시아 근무 지역에 근무하는 직원의 보너스를 0.5로 변경하세요
UPDATE EMP_SALARY ES
SET ES.BONUS = 0.5
WHERE ES.EMP_ID IN (SELECT E1.EMP_ID
FROM EMPLOYEE E1
JOIN DEPARTMENT D ON (E1.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
WHERE L.LOCAL_NAME LIKE 'ASIA%'
);
- UPDATE시 변경 값은 해당 컬럼에 대한 제약 조건에 위배되지 않아야 함
실습 )
UPDATE
EMPLOYEE E
SET E.DEPT_CODE = '65'
WHERE E.DEPT_CODE = 'D6';
▲ FOREIGN KET 제약 조건 위배됨 (부모 키가 없습니다.)
UPDATE
EMPLOYEE E
SET E.EMP_NAME = NULL
WHERE E.EMP_ID = '200';
▲ NOT NULL 제약 조건 위배됨 (NULL로 업데이트할 수 없습니다.)
UPDATE
EMPLOYEE E
SET E.EMP_NO = '901123-208503'
WHERE E.EMP_ID = '200';
▲ UNIQUE 제약 조건 위배됨 (무결성 제약조건에 위배됩니다.)
3. DELETE
테이블의 행을 삭제하는 구문이다. 테이블의 행의 개수가 줄어든다.
[표현식]
DELETE FROM 테이블명 WHERE 조건설정
만약 WHERE 조건을 설정하지 않으면 모든 행이 다 삭제된다.
위에서 INSERT한 '장도현' 직원의 행을 삭제해보자.
DELETE
FROM EMPLOYEE E
WHERE E.EMP_NAME = '장재현';
FOREIGN KEY 제약조건이 설정되어 있는 경우
참조되고 있는 값에 대해서는 삭제할 수 없다.
DELETE
FROM DEPARTMENT D
WHERE D.DEPT_ID = 'D1';
▲ 잠조 무결성 제약 조건에 위배됨 (자식 레코드가 발견되었습니다)
FOREIGN KEY 제약조건이 설정되어 있어도
참조되고 있지 않은 값에 대해서는 삭제 가능
DELETE
FROM DEPARTMENT D
WHERE D.DEPT_ID = 'D3'; --부서코드 D3의 자식레코드가 없으므로 삭제가능
4. MERGE
구조가 같은 두 개의 테이블을 하나로 합치는 기능을 한다.
테이블에서 지정하는 조건의 값이 존재하면 UPDATE
조건의 값이 없으면 INSERT됨
[표현식]
MERGE
INTO 테이블명 A
USING 합칠 테이블명 B
ON ( A.컬럼명 = B.컬럼명)
WHEN MATCHED THEN -- 조건이 같을 경우 UPDATE
UPDATE
SET 컬럼명
WHEN NOT MATCHED THEN -- 조건이 다른 경우 INSERT
INSERT
(
컬럼명, 컬럼명, ..
)
VALUES
(
컬럼명, 컬럼명, ...
);
실습)
EMPLOYEE 테이블을 서브쿼리로 활용한 두 개의 테이블을 먼저 생성해본다.
CREATE TABLE EMP_M01
AS
SELECT E.*
FROM EMPLOYEE E;
CREATE TABLE EMP_M02
AS
SELECT E.*
FROM EMPLOYEE E
WHERE E.JOB_CODE = 'J4';
EMP_M01 테이블에는 모든 직원의 정보를,
EMP_M02 테이블에는 직급코드가 'J4'인 직원의 정보만 담았다.
그리고 EMP_M02의 직원 한명을 더 INSERT로 추가하고,
UPDATE로 SALARY 급여의 값을 0 으로 만들었다. (코드 생략)
달라진 두 테이블을 가지고
EMP_M01 테이블에 EMP_M02 테이블을 합쳐본다.
MERGE
INTO EMP_M01 M1
USING EMP_M02 M2
ON (M1.EMP_ID = M2.EMP_ID)
WHEN MATCHED THEN
UPDATE
SET M1.EMP_NAME = M2.EMP_NAME
, M1.EMP_NO = M2.EMP_NO
, M1.EMAIL = M2.EMAIL
, M1.PHONE = M2.PHONE
, M1.DEPT_CODE = M2.DEPT_CODE
, M1.JOB_CODE = M2.JOB_CODE
, M1.SAL_LEVEL= M2.SAL_LEVEL
, M1.SALARY = M2.SALARY
, M1.BONUS = M2.BONUS
, M1.MANAGER_ID = M2.MANAGER_ID
, M1.HIRE_DATE = M2.HIRE_DATE
, M1.ENT_DATE = M2.ENT_DATE
, M1.ENT_YN = M2.ENT_YN
WHEN NOT MATCHED THEN
INSERT
(
M1.EMP_ID, M1.EMP_NAME, M1.EMP_NO, M1.EMAIL, M1.PHONE
, M1.DEPT_CODE, M1.JOB_CODE, M1.SAL_LEVEL, M1.SALARY, M1.BONUS
, M1.MANAGER_ID, M1.HIRE_DATE, M1.ENT_DATE, M1.ENT_YN
)
VALUES
(
M2.EMP_ID, M2.EMP_NAME, M2.EMP_NO, M2.EMAIL, M2.PHONE
, M2.DEPT_CODE, M2.JOB_CODE, M2.SAL_LEVEL, M2.SALARY, M2.BONUS
, M2.MANAGER_ID, M2.HIRE_DATE, M2.ENT_DATE, M2.ENT_YN
);
컬럼명을 모두 기술하여 긴 코드가 완성되었다. 헥헥.
EMP_M01 테이블을 확인해보면
J4 직급코드를 가진 직원의 SALARY의 값이 0이 되고 (UPDATE)
EMP_M02에만 추가한 직원이 추가되어 있다 (INSERT)
'백엔드 과정 > Oracle(SQL)' 카테고리의 다른 글
[Oracle] Chap09 TCL 트랜잭션 (0) | 2022.01.25 |
---|---|
[Oracle] Chap08 DDL (0) | 2022.01.25 |
[Oracle] Chap06 테이블 생성 및 제약조건 (0) | 2022.01.24 |
[Oracle] SQL문제풀이 / SUBQUERY (0) | 2022.01.22 |
[Oracle] Chap05 SUBQUERY (0) | 2022.01.21 |