DML(Data Manipulation Language)

DB/SQL

DML(Data Manipulation Language)

제주니어 2022. 9. 4. 01:43

DML(Data Manipulation Language)

데이터 조작 언어로 테이블에 값을 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하는 구문이다.

INSERT

테이블에 새로운 행을 추가하는 구문이다.

-- 테스트에 사용할 테이블 생성
CREATE TABLE EMP_01 (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(30) NOT NULL,
    DEPT_TITLE VARCHAR2(30),
    HIRE_DATE DATE DEFAULT SYSDATE
);

표현법 1)

1) INSERT INTO 테이블명 VALUES(값, 값, 값, ..., 값);

- 테이블의 모든 컬럼에 대한 값을 INSERT 할 때 사용한다. - 컬럼 순번을 지켜서 VALUES에 값을 나열해야 한다.

INSERT INTO EMP_01
VALUES(100, '박현진', '서비스 개발팀', DEFAULT);

-- 모든 컬럼에 값을 지정하지 않아서 에러 발생
INSERT INTO EMP_01
VALUES(200, '홍길동', '개발 지원팀');

-- 에러는 발생하지 않지만 데이터가 잘못 저장된다. 
INSERT INTO EMP_01
VALUES(300, '유아지원팀','고길동', DEFAULT);

-- 컬럼 순서와 데이터 타입이 맞지 않아서 에러 발생
INSERT INTO EMP_01
VALUES('유아지원팀', 300, '고길동', DEFAULT);

SELECT * FROM EMP_01;

표현법 2)

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

- 테이블에 내가 선택한 컬럼에만 값을 INSERT 할 때 사용한다.
- 선택이 안된 컬럼들은 기본적으로 NULL 값이 들어간다.
(단, NOT NULLL 제약조건이 걸려있는 컬럼은 반드시 선택해서 값을 INSERT해야 한다.)
- 기본값(DEFAULT)이 지정되어 있으면 NULL이 아닌 기본값이 들어간다.

-- 컬럼 순서에 맞게 데이터의 값을 넣어준다.
INSERT INTO EMP_01(EMP_ID, EMP_NAME, DEPT_TITLE, HIRE_DATE)
VALUES(400, '공유', '인사팀', NULL);

INSERT INTO EMP_01(EMP_NAME, EMP_ID, DEPT_TITLE, HIRE_DATE)
VALUES('이몽룡', 500, '보안팀', SYSDATE);

INSERT INTO EMP_01(EMP_ID, EMP_NAME)
VALUES(600, '성춘향');

-- EMP_NAME의 NOT NULL 제약조건 때문에 에러 발생
INSERT INTO EMP_01(EMP_ID, DEPT_TITLE)
VALUES(700, '마케팅팀'); 

-- EMP_NAME의 값을 넣어 줘야 한다
INSERT INTO EMP_01(EMP_ID, EMP_NAME, DEPT_TITLE)
VALUES(700, '심청이', '마케팅팀');

SELECT * FROM EMP_01;

표현법 3)

3) INSERT INTO 테이블명(컬럼, 컬럼,...,) (서브 쿼리);

- VALUES를 대신해서 서브 쿼리로 조회한 결과값을 통채로 INSERT한다.
- 서브 쿼리의 결과값이 INSERT 문에 지정된 테이블 컬럼의 개수와 데이터 타입이 같아야 한다.

-- EMPLOYEE 테이블에서 직원의 사번, 이름, 부서명, 입사일을 조회해서 EMP_01 테이블에 INSERT 하시오.
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, E.HIRE_DATE
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);

INSERT INTO EMP_01 (
    SELECT E.EMP_ID, 
           E.EMP_NAME, 
           D.DEPT_TITLE, 
           E.HIRE_DATE
    FROM EMPLOYEE E
    INNER JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
);

-- 테이블 컬럼의 순서와 맞지 않아서 에러 발생
INSERT INTO EMP_01 (
    SELECT E.EMP_NAME, 
           E.EMP_ID, 
           D.DEPT_TITLE, 
           E.HIRE_DATE
    FROM EMPLOYEE E
    INNER JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
);

-- 서브 쿼리로 조회한 데이터의 컬럼의 개수가 테이블의 컬럼 개수보다 많아서 에러 발생
INSERT INTO EMP_01 (
    SELECT E.EMP_ID, 
           E.EMP_NAME, 
           D.DEPT_TITLE, 
           E.HIRE_DATE,
           E.DEPT_CODE
    FROM EMPLOYEE E
    INNER JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
);


DELETE FROM EMP_01; -- 모든 데이터 삭제

-- EMPLOYEE 테이블에서 직원들의 사번, 직원명을 조회해서 EMP_01 테이블에 INSERT 하시오.
INSERT INTO EMP_01(EMP_ID, EMP_NAME) (
    SELECT EMP_ID, EMP_NAME
    FROM EMPLOYEE
);

SELECT * FROM EMP_01;

INSERT ALL

두 개 이상의 테이블에 각각 INSERT 하는데 동일한 서브 쿼리가 사용되는 경우 INSERT ALL을 이용해서 여러 테이블에 한 번에 데이터 삽입이 가능하다.

표현법 1)

-- 서브 쿼리의 결과를 테이블1, 테이블2에 각각 INSERT 한다. 
INSERT ALL
INTO 테이블명1[(컬럼, 컬럼, ...)] VALUES(값, 값, ...)
INTO 테이블명2[(컬럼, 컬럼, 컬럼, ...)] VALUES(값, 값, 값, ...)
서브 쿼리;

-- 표현법 1)번을 테스트할 테이블 만들기
CREATE TABLE EMP_DEPT
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
    FROM EMPLOYEE
    WHERE 1 = 0; -- 구조만 가져올 때, WHERE절에 무조건 FALSE가 나오게 설정한다.
    
    
CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, MANAGER_ID
    FROM EMPLOYEE
    WHERE 1 = 0;
    
-- EMP_DEPT 테이블에는 EMPLOYEE 테이블의 부서 코드가 D1인 직원들의 사번, 이름, 직원명, 부서 코드, 입사일을 삽입하고
-- EMP_MANAGER 테이블에는 EMPLOYEE 테이블의 부서 코드가 D1인 직원들의 사번, 직원명, 관리자 사번을 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1'; -- 서브쿼리

INSERT ALL -- 메인쿼리
INTO EMP_DEPT VALUES (EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE)
INTO EMP_MANAGER VALUES (EMP_ID, EMP_NAME, MANAGER_ID)
    SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
    FROM EMPLOYEE
    WHERE DEPT_CODE = 'D1';

SELECT * FROM EMP_DEPT;
SELECT * FROM EMP_MANAGER;

표현법 2)

-- 서브 쿼리의 결과를 조건을 만족하는 테이블에 INSERT 한다. 
INSERT ALL
WHEN 조건1 THEN 
INTO 테이블명1[(컬럼, 컬럼, ...)] VALUES(값, 값, ...)
WHEN 조건2 THEN     
INTO 테이블명2[(컬럼, 컬럼, ...)] VALUES(값, 값, ...)
서브 쿼리;

-- 표현법 2)번을 테스트할 테이블 만들기
CREATE TABLE EMP_OLD
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
    FROM EMPLOYEE
    WHERE 1 = 2;

CREATE TABLE EMP_NEW
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
    FROM EMPLOYEE
    WHERE 1 = 2;

-- EMPLOYEE 테이블에서 입사일 기준으로 2000년 1월 1일 이전에 입사한 사원의 정보는 EMP_OLD 테이블에 삽입하고
-- 2000년 1월 1일 이후에 입사한 사원의 정보는 EMP_NEW 테이블에 삽입한다. 
SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE; -- 서브쿼리

INSERT ALL 
    WHEN HIRE_DATE < '2000/01/01' 
    THEN INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
    WHEN HIRE_DATE > '2000/01/01'
    THEN INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
    SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
    FROM EMPLOYEE;

SELECT * FROM EMP_OLD;
SELECT * FROM EMP_NEW;

UPDATE

테이블의 행의 값을 수정하는 구문이다.
SET 절에서 여러 개의 컬럼을 콤마(,)로 나열해서 값을 동시에 변경할 수 있다.
WHERE 절을 생략하면 모든 행의 데이터가 변경된다.

UPDATE 테이블명
SET 컬럼명 = 변경하려는 값,
    컬럼명 = 변경하려는 값, 
    ...
[WHERE 조건];

-- 테스트를 진행할 테이블 생성
CREATE TABLE DEPT_COPY
AS SELECT *
    FROM DEPARTMENT;
    
CREATE TABLE EMP_SALARY
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
    FROM EMPLOYEE;

-- DEPT_COPY 테이블에서 DEPT_ID가 D9인 부서명을 전략기획팀으로 수정
UPDATE DEPT_COPY
SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_ID = 'D9';

-- EMP_SALARY 테이블에서 노옹철 사원의 급여를 1,000,000원으로 변경
UPDATE EMP_SALARY
SET SALARY = 1000000
WHERE EMP_NAME = '노옹철';

-- EMP_SALARY 테이블에서 선동일 사장의 급여를 7,000,000원으로, 보너스를 0.2로 변경
UPDATE EMP_SALARY
SET SALARY = 7000000, BONUS = 0.2
WHERE EMP_NAME = '선동일';

-- 모든 사원의 급여를 기존 급여에서 10프로 인상한 금액으로 변경
UPDATE EMP_SALARY
SET SALARY = SALARY * 1.1; -- SET절에 연산식도 가능하다

-- UPDATE 할 때, 변경할 값은 해당 컬럼에 대한 제약조건을 위배하면 안 된다. 
-- EMP_SALARY 테이블에 사번이 200인 사원의 사번을 NULL로 변경
UPDATE EMP_SALARY
SET EMP_ID = NULL
WHERE EMP_ID = 200;

-- EMPLOYEE 테이블에 노옹철 사원의 부서코드 'D0'으로 변경
-- 해당값(D0)이 없어서 에러 발생
UPDATE EMPLOYEE
SET DEPT_CODE = 'D0'
WHERE EMP_NAME = '노옹철';

서브쿼리 이용방법

-- 방명수 사원의 급여와 보너스를 유재식 사원과 동일하게 변경
SELECT SALARY, BONUS
FROM EMP_SALARY
WHERE EMP_NAME = '유재식'; -- 서브쿼리

-- 1) 단일행 서브 쿼리를 사용하는 방법
UPDATE EMPLOYEE
SET SALARY = (
    SELECT SALARY
    FROM EMP_SALARY
    WHERE EMP_NAME = '유재식'
    ), 
    BONUS = (
    SELECT BONUS
    FROM EMP_SALARY
    WHERE EMP_NAME = '유재식'
    )
WHERE EMP_NAME = '방명수';

-- 2) 다중열 서브 쿼리를 사용해서 SALARY, BONUS 컬럼을 한 번에 변경
UPDATE EMP_SALARY
SET (SALARY, BONUS) = (
    SELECT SALARY, BONUS
    FROM EMP_SALARY
    WHERE EMP_NAME = '유재식'
    )
WHERE EMP_NAME = '방명수';

실습

-- EMP_SALARY 테이블에서 노옹철, 전형돈, 정중하, 하동운 사원들의 급여와 보너스를 유재식 사원과 동일하게 변경
SELECT SALARY, BONUS
FROM EMP_SALARY
WHERE EMP_NAME = '유재식';

UPDATE EMP_SALARY
SET (SALARY, BONUS) = (
    SELECT SALARY, BONUS
    FROM EMP_SALARY
    WHERE EMP_NAME = '유재식'
    )
WHERE EMP_NAME IN('노옹철','전형돈','정중하','하동운');

SELECT * FROM EMP_SALARY;

-- EMP_SALARY 테이블에서 아시아 지역에 근무하는 직원들의 보너스를 0.3으로 변경
SELECT *
FROM EMP_SALARY E
INNER JOIN DEPT_COPY D ON (E.DEPT_CODE = D.DEPT_ID)
INNER JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
WHERE L.LOCAL_NAME LIKE 'ASIA%';

UPDATE EMP_SALARY
SET BONUS = 0.3
WHERE EMP_ID IN (
    SELECT EMP_ID
    FROM EMP_SALARY E
    INNER JOIN DEPT_COPY D ON (E.DEPT_CODE = D.DEPT_ID)
    INNER JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
    WHERE L.LOCAL_NAME LIKE 'ASIA%');

SELECT * FROM DEPT_COPY;
SELECT * FROM EMP_SALARY;

DELETE

테이블의 행을 삭제하는 구문이다. (행 단위)
WHERE 절을 생략하면 모든 행의 데이터가 삭제된다.

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

-- EMP_SALARY 테이블에서 선동일 사장의 데이터를 지우기
DELETE FROM EMP_SALARY
WHERE EMP_ID = 200;

-- EMP_SALARY 테이블에서 DEPT_CODE가 D5인 직원들을 삭제
DELETE
FROM EMP_SALARY
WHERE DEPT_CODE = 'D5';

TRUNCATE

테이블의 전체 행을 삭제하는 구문이다.
DELETE보다 수행 속도가 빠르고 ROLLBACK을 통해 복구 불가능하다.

TRUNCATE TABLE 테이블명;

CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;

-- 테이블의 행 전체 삭제 (WHERE 조건 안줬을 경우)
DELETE FROM DEPT_COPY; 

-- DELETE : ROLLBACK 사용 가능
ROLLBACK; 

-- 테이블의 행 전체 삭제
TRUNCATE TABLE DEPT_COPY; 

-- TRUNCATE : ROLLBACK 사용 불가능
ROLLBACK;

저작자표시 비영리 동일조건

'DB > SQL' 카테고리의 다른 글

DCL(Data Control Language) (0)	2022.09.06
TCL(Transaction Control Language) (0)	2022.09.05
DDL(Data Definition Language) (0)	2022.09.03
SUBQUERY (1)	2022.09.02
INNER JOIN & OUTER JOIN (0)	2022.09.01

현재글DML(Data Manipulation Language)

바켠진로그

현재 백엔드 개발자로 활동하고 있습니다.

pattern, hiberanate, jpa, @creationtimestamp, java, columnDefinition,

Today :
Yesterday :

일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

바켠진로그

DML(Data Manipulation Language)

DML(Data Manipulation Language)

표현법 1)

표현법 2)

표현법 3)

표현법 1)

표현법 2)

서브쿼리 이용방법

실습

'DB > SQL' 카테고리의 다른 글

'DB/SQL'의 다른글

티스토리툴바

DML(Data Manipulation Language)

DML(Data Manipulation Language)

표현법 1)

표현법 2)

표현법 3)

표현법 1)

표현법 2)

서브쿼리 이용방법

실습

'DB > SQL' 카테고리의 다른 글

'DB/SQL'의 다른글

관련글

티스토리툴바