[SQL]DML(SELECT문)(10) - INSERT, UPDATE, DELETE

Database/Oracle

[SQL]DML(SELECT문)(10) - INSERT, UPDATE, DELETE

Ma_Sand 2022. 3. 25. 18:16

INSERT INTO

: 테이블에 새로운 행을 추가한다.

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

: 해당 테이블에서 모든 컬럼에 추가하고자 하는 값을 제시하여 한 행을 INSERT할 때 사용한다.

- 컬럼의 순서와 자료형의 개수를 맞춰서 VALUES에 값을 나열해야 한다.

- 만약, 값을 부족하게 제시할 경우 'NOT ENOUGH VALUE'라고 오류가 뜨고,

값을 더 제시할 경우 'TOO MANY VALUES'라고 오류가 뜬다.

- EMPLOYEE 테이블에 정보를 추가하기

-- 순서: 사번, 이름, 주민번호, 이메일, 폰번호
--     , 부서코드, 직급코드, 급여등급, 보너스, 사수 사번, 입사일, 퇴사일, 퇴사 여부
INSERT INTO EMPLOYEE VALUES(159, '최충헌', '921034-1123456', 'korea@kor.com', '01012345678'
                          , 'D1', 'J1', 'S6', 2340000, 0.3, 110, SYSDATE, NULL, DEFAULT);

여기서 값을 더 추가하거나 덜 추가하면 오류가 난다. 그리고 정해져있는 데이터 타입과 해당 바이트를 맞추지 않아도 오류가 난다. EMPLOYEE 테이블의 폰번호의 데이터 타입은 VARCHAR2(12 BYTE)인데, 숫자는 한 글자 당 1 BYTE이므로, 위의 폰번호는 총 11 BYTE로 알맞게 작성되었다. 그런데 '010-1234-5678'로 작성하려고 하면 총 13 BYTE가 되므로 'value too large for column "KOR"."EMPLOYEE"."PHONE" (actual: 13, maximum: 12)' 이라는 오류가 뜬다.

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

: 해당 테이블에서 특정 컬럼만 선택하여 추가할 값을 제시할 때 사용한다.

- 선택하지 않은 컬럼은 기본값 NULL이 들어가며, DEFAULT 설정이 돼 있으면 DEFAULT 설정값이 들어간다.

- 주의! NOT NULL 제약조건이 걸려 있는 컬럼은 반드시 선택하여 값을 넣어야 한다.

단, DEFAULT 설정이 돼 있으면, DEFAULT 설정값이 기본값이 되므로, NOT NULL 제약조건이 걸려 있어도

선택하지 않아도 된다.

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, DEPT_CODE, JOB_CODE, SAL_LEVEL, HIRE_DATE) 
VALUES(300, '오필승', '8801234-2103456', 'D9', 'J5', 'S4', SYSDATE);

전체 컬럼 중 일부만 선택하여 해당 컬럼에 들어갈 값을 입력하였다. 선택하지 않은 EMAIL과 PHONE, SALARY 등의 컬럼들을 NULL값으로 들어간다. 그리고 ENT_YN(퇴사여부) 컬럼은 'N'으로 DEFAULT 설정되어 있어 따로 값을 추가하지 않으면 자동으로 'N'이 들어간다.

(3) INSERT INTO 테이블명(서브쿼리)

: VALUES()로 값을 직접 기입하는 것이 아니라 서브쿼리로 조회한 결과값을 한번에 INSERT한다.

즉, 여러 행을 한번에 INSERT할 수 있다.

INSERT INTO EMP (SELECT EMP_NAME, EMP_NO, DEPT_TITLE
                 FROM EMPLOYEE, DEPARTMENT
                 WHERE DEPT_CODE = DEPT_ID);

(1)과 (2)에선 VALUES()를 사용했는데 이번엔 VALUES()를 사용하지 않고 서브쿼리를 넣어 값을 한번에 조회한다.

INSERT ALL

: 두 개 이상의 테이블에 각각 INSERT할 때 사용한다. 이때 사용되는 서브쿼리가 동일해야 한다.

(1) INSERT ALL

INTO 테이블명1 VALUES(컬럼명1, 컬럼명2, ...)

INTO 테이블명2 VALUES(컬럼명1, 컬럼명2, ...)

서브쿼리;

-- 새로운 테이블 만들기
-- 1) 급여가 300만 원 이상인 사원들의 사번과 사원명, 직급명을 보관할 테이블 만들기
CREATE TABLE EMP_JOB
AS SELECT EMP_ID, EMP_NAME, JOB_NAME
   FROM EMPLOYEE E, JOB J
   WHERE E.JOB_CODE = J.JOB_CODE
   AND 1 = 0; -- FALSE
   

-- 2) 급여가 300만 원 이상인 사원들의 사번과 사원명, 부서명을 보관할 테이블 만들기
CREATE TABLE EMP_DEPT
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE
   FROM EMPLOYEE, DEPARTMENT
   WHERE DEPT_CODE = DEPT_ID
   AND 1 = 0;  -- FALSE
   
   
-- 3) 테이블을 서브쿼리로 만들고 INSERT ALL을 사용해서 데이터 넣기
INSERT ALL
    INTO EMP_JOB VALUES(EMP_ID, EMP_NAME, JOB_NAME)
    INTO EMP_DEPT VALUES(EMP_ID, EMP_NAME, DEPT_TITLE)
    SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE
    FROM EMPLOYEE E, JOB J ,DEPARTMENT
    WHERE E.JOB_CODE = J.JOB_CODE
    AND DEPT_CODE = DEPT_ID
    AND SALARY >= 3000000;
    
    
-- 조회할 때
SELECT * FROM EMP_JOB;
SELECT * FROM EMP_DEPT;

(2) INSERT ALL

WHEN 조건1 THEN

INTO

WHEN 조건2 THEN

INTO

서브쿼리;

: 조건에 맞는 값들만 넣는다.

-- 1) 2010년 기준으로 이전에 입사한 사원들의 사번과 사원명, 입사일, 급여를 담는 테이블 만들기
CREATE TABLE EMP_OLD
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
   FROM EMPLOYEE
   WHERE 1 = 0;  -- FALSE
   
-- 2) 2010년 기준으로 이후에 입사한 사원들의 사번과 사원명, 입사일, 급여를 담는 테이블 만들기
CREATE TABLE EMP_NEW
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
   FROM EMPLOYEE
   WHERE 1 = 0;  -- FALSE

-- 서브쿼리로 입력할 부분
SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE HIRE_DATE < '2010/01/01';  -- 2010년 이전
WHERE HIRE_DATE >= '2010/01/01';  -- 2010년 이후

-- INSERT ALL로 데이터 넣기
INSERT ALL
    WHEN HIRE_DATE < '2010/01/01' THEN
    INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
    WHEN HIRE_DATE >= '2010/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

: 테이블에 기록된 기존의 데이터를 수정한다.

(1) UPDATE 테이블명

SET 컬럼명 = 변경값, 컬럼명 = 변경값, ...

WHERE 조건;

- SET절에선 여러 개의 컬럼값을 동시에 변경할 수 있다.

- WHERE절은 생략할 수 있지만, 생략 시 모든 행의 데이터가 변경된다.

-- 1) 복사본 테이블 만들기
CREATE TABLE EMP_SALARY
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
   FROM EMPLOYEE;
   
-- 2) EMP_SALARY 테이블에서 온장미 사원의 급여를 1000만 원으로 변경하기
UPDATE EMP_SALARY
SET SALARY = 10000000
WHERE EMP_NAME = '온장미';

-- 3) EMP_SALARY 테이블에서 최충분 사원의 급여를 700만 원, 보너스를 0.2로 변경하기
UPDATE EMP_SALARY, BONUS
SET SALARY = 7000000, BONUS = 0.2
WHERE EMP_NAME = '최충분';

(2) UPDATE에 서브쿼리를 사용

: 서브쿼리를 수행한 결과값으로 기존값을 변경한다.

- CREATE 시에 서브쿼리 사용: 서브쿼리를 수행한 결과값을 테이블에 넣는다.

- INSERT 시에 서브쿼리 사용: 서브쿼리를 수행한 결과값을 해당 테이블에 삽입한다.

- UPDATE 테이블명

SET 컬럼명 = (서브쿼리)

WHERE 조건명; (생략 가능)

-- EMP_SALARY 테이블에서 온장미 사원의 부서코드를 최충분 사원의 부서코드로 변경하기

-- 1) 최충분 사원의 부서코드 조회하기
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '최충분';
-- 최충분 사원의 부서코드 : D9

-- 2) 온장미 사원의 부서코드를 최충분 사원의 부서코드로 변경하기
UPDATE DEPT_CODE
SET DEPT_CODE = (SELECT DEPT_CODE
                 FROM EMPLOYEE
                 WHERE EMP_NAME = '최충분')
WHERE EMP_NAME = '온장미';

-- 온장미 사원의 급여와 보너스를 최충분 사원의 급여와 보너스로 변경하기
UPDATE EMP_SALARY
SET SLALARY(SELECT SALARY
            FROM EMPLOYEE
            WHERE EMP_NAME = '최충분')
  , BONUS(SELECT BONUS
          FROM EMPLOYEE
          WHERE EMP_NAME = '최충분')
WHERE EMP_NAME = '온장미';

DELETE

: 테이블에 기록된 데이터를 행 단위로 삭제한다.

- DELETE FROM 테이블명

WHERE 조건;

- WHERE절은 생략이 가능하지만, 생략 시 모든 행이 삭제된다.

- TRUNCATE와 달리 ROLLBACK이 가능하다.

-- EMPLOYEE 테이블의 모든 행을 삭제하기
DELETE FROM EMPLOYEE;
-- 데이터만 삭제되고 테이블이 삭제되진 않는다.

ROLLBACK; -- 되돌리기. 단, COMMIT하고난 후로 돌아간다.

-- EMPLOYEE 테이블에서 김칠기와 남사려 사원의 정보를 삭제하기
DELETE FROM EMPLOYEE
WHERE EMP_NAME IN ('김칠기', '남사려');

COMMIT;  -- 지금까지 변경한 작업을 확정한다.

TRUNCATE

: 테이블의 전체 행을 모두 삭제할 때 사용한다.

- TRUNCATE TABLE 테이블명;

- DELETE 구문보다 속도가 빠르다.

- 별도의 조건 제시를 할 수 없고, ROLLBACK이 안되므로 주의해야 한다.

TRUNCATE TABLE EMP_SALARY;

ROLLBACK;  -- ROLLBACK을 해도 되돌아가지 않는다.

저작자표시

현재글[SQL]DML(SELECT문)(10) - INSERT, UPDATE, DELETE

the Rabbit_Sand, IT Blog 🍀