DB -10) 데이터를 추가, 수정, 삭제하는 데이터 조작어

DataBase

10-1 테이블에 데이터 추가하기

테이블 생성하기

회원가입, 새 글 쓰기, 새로운 이체 내역 등 새로운 데이터가 발생하는 기능은 관련 테이블에 새 데이터를 추가해 줌으로써

구현할 수 있습니다. 이처럼 특정 테이블에 데이터를 새로 추가할 때 INSERT문을 사용합니다.

 

테이블을 지울 때는

DROP TABLE 테이블명;

테이블에 데이터를 추가하는 INSERT문

테이블에 데이터를 추가하는 데 사용하는 INSERT문은 다음과 같이 기본형태로 작성합니다. INSERT INTO절 뒤에 데이터를 추가할 테이블 이름을 명시하고, 해당 테이블의 열을 소괄호로 묶어 지정한 후 VALUES절에는 지정한 열에 입력할 데이터를 작성합니다.

INSERT INTO 테이블명 [(열1, 열2, ... ,열N)]
VALUES (열1에 들어갈 데이터, 열2에 들어갈 데이터, .... , 열N에 들어갈 데이터);

키워드	필수 요소	선택 요소	설명
INSERT INTO	테이블 이름	테이블 열	새로운 데이터를 입력할 대상 테이블과 열을 입력합니다.
VALUES	입력할 데이터	-	INSERT INTO에서 지정한 테이블의 열 순서와 자료형에 맞는 데이터를 지정합니다.

 

만약 INSERT문에서 지정한 열 개수와 각 열에 입력할 데이터 개수가 일치하지 않거나 자료형이 맞지 않는 경우 또는 열

길이를 초과하는 데이터를 지정하는 경우에는 INSERT문에 오류가 발생하여 실행되지 않으므로 주의해야 합니다.

 

INSERT문에 지정하는 열은 생략할  수도 있습니다. 열 지정을 생략하면 해당 테이블을 만들 때 설정한 열 순서대로 모두 나열되어 있다고 가정하고 데이터를 작성해야 합니다. 당연히 이 방법을 사용할 때에도 테이블을 구성하는 열 개수나

자료형 및 길이는 반드시 맞춰 주어야 합니다.

 

INSERT문으로 새로운 데이터를 추가할 때 특정 열에 들어갈 데이터가 확정되지 않았거나 굳이 넣을 필요가 없는

데이터인 경우에는 NULL을 사용합니다. NULL을 INSERT문에 지정하는 방법은 NULL을 직접 명시적으로 입력해 주는

방법과 대상 열을 생략하여 암시적으로 NULL이 입력되도록 유도하는 방식이 있습니다.

해당 열의 자료형이 문자열 또는 날짜형일 경우 빈 공백 문자열('')을 사용해도 NULL을 입력할 수 있습니다.

 

반면에 NULL의 암시적 임력 방식은 INSERT문에 NULL이 들어가야 할 열 이름을 아예 입력하지 않는 것입니다.

 

날짜 데이터를 입력할 때 유의할 점은 년/월/일 순서와 반대로 일/월/년 순서로 데이터를 입력하면 오류가 발생하고 데이터가 입력되지 않습니다. 오류가 발생하는 이유는 오라클이 설치되어 있는 운영체제(OS)의 종류나 사용하는 기본 언어군에

따라 날짜 표기방식이 다르기 때문입니다. 따라서 날짜 데이터를 INSERT문으로 입력할 때는 문자열로 날짜를 입력하지 않고 TO_DATAE 함수를 사용하는 것이 좋습니다.

 

현재 시점으로 날짜를 입력할 경우에는 SYSDATE를 지정하여 간단히 처리할 수 있습니다. SYSDATE방식은 데이터 입력

시점을 정확히 입력할 수 있어서 자주 사용됩니다.

서브 쿼리를 사용하여 한 번에 여러 데이터 추가하기

INSERT문에 서브쿼리를 사용하면 SELECT문으로 한 번에 여러 행의 데이터를 추가할 수 잇습니다.

INSERT문에서 서브쿼리를 사용할 때 유의할 점은 다음과 같습니다.

• VALUES절은 사용하지 않습니다.
• 데이터가 추가되는 테이블의 열 개수와 서브쿼리의 열 개수가 일치해야 합니다
• 데이터가 추가되는 테이블의 자료형과 서브쿼리의 자료형이 일치해야 합니다.

INSERT문은 ALL또는 FIRST등의 옵션으로 한 번에 여러 테이블을 대상으로 데이터를 추가하거나 특정 조건에 따라 다른 테이블에 데이터를 추가하는 등 다양하게 사용할 수 있습니다.

또 MERGE문을 사용하면 같은 열 구조를 가지는 여러 테이블 또는 서브쿼리의 결과 데이터를 한 테이블에 병합하여 추가할 수도 있습니다.

10-2. 테이블에 있는 데이터 수정하기

회원 정보 변경, 결제 계좌 변경, 내가 쓴 글의 내용 수정 등의 기능을 수행하려면 데이터베이스 테이블에 저장된 데이터를 변경해야 합니다. 오라클에서는 특정 테이블에 저장되어 있는 데이터 내용을 수정할 때 UPDATE문을 사용합니다.

UPDATE문의 기본 사용법

UPDATE문은 기본적으로 다음과 같이 UPDATE 키워드 이후에 변경할 테이블 이름을 지정하고 SET절에

 '변경할 열이름 = 변경할 데이터'를 지정합니다. 그리고 여러 열의 데이터를 수정할 경우 쉼표(,)로 구분합니다.

데이터를 변경해야 할 행이 정해져 있다면 SELECT문에서 사용한 것과 마찬가지로 WHERE절 및 조건식을 추가하여

변경 대상 행을 지정할 수 있습니다.

UPDATE [변경할 테이블명]
SET [변경할 열1]=[데이터], [변경할 열2]=[데이터], ... , [변경할 열N]=[데이터]
[WHERE 데이터를 변경할 대상 행을 선별하기 위한 조건];

키워드	필수 요소	선택 요소	설명
UPDATE	테이블 이름	-	데이터를 수정할 테이블을 지정
SET	변경할 열의 이름과 데이터	-	변경할 열을 선택하고 변경할 데이터를 입력합니다.
WHERE	-	변경 데이터를 선별하기 위한 조건식	테이블의 변경할 데이터 선별 조건식을 지정합니다. 생략할 경우 테이블 내 지정된 모든 열의 데이터가 변경됩니다.

 

실수로 UPDATE문을 실행했을 때 UPDATE문 실행을 취소하기 위해 ROLLBACK 명령문을 사용할 수 있습니다.

ROLLBACK 명령어는 정해진 시점 이후에 실행된 DML 명령어 즉 INSERT, UPDATE, DELETE의 실행을 취소하는

명령어입니다. COMMIT 명령어와는 반대 의미입니다.

 

UPDATE문에서 수정 대상 행을 선별하기 위해 WHERE절과 조건식을 사용합니다. 사용 방법은 SELECT문에서 사용한

WHERE절과 같습니다.

서브쿼리를 사용하여 데이터 수정하기

INSERT문과 마찬가지로 UPDATE문에서도 서브쿼리를 활용할 수 있습니다. 열 하나하나에 서브쿼리를 적용하는 것도

가능합니다. 여기서 주의해서 살펴봐야 할 점은 변경 열의 개수에 따라 서브쿼리에서 지정한 열 개수도 변한다는 것입니다.

당연히 서브쿼리에 나열한 열과 UPDATE문으로 변경할 열 개수나 자료형은 일치해야 합니다.

또 UPDATE문의 WHERE절에도 서브쿼리를 사용할 수 있습니다.

UPDATE문 사용할 때 유의점

UPDATE문과 DELETE문은 테이블에 이미 존재하는 데이터를 수정하거나 삭제하는 기능을 수행하므로 SELECT문이나

INSERT문에 비해 위험성이 큰 명령어입니다. 

 

UPDATE에 사용하는 WHERE 조건식이 정확한 데이터를 대상으로 하는지 꼼꼼히 따져 보지 않고 수행한 후 애를 먹는 상황이 발생할 수 있습니다. 따라서 UPDATE문을 실행하기 전에 UPDATE문의 WHERE절을 검증하는 작업이 반드시 필요합니다. 변경해야 하는 행만 정확하게 선정해서 수정하는지 확인해야 합니다. 이를 위해 해당 WHERE절을 UPDATE문에

넣어 실행하기 전에 SELECT문에서 먼저 사용해 보는 것만으로도 간단하게 확인할 수 있습니다.

 

조금 귀찮더라도 반드시 UPDATE문과 DELETE문을 실행하기 전에 SELECT문으로 WHERE절의 조건식이 정확한지 확인하는 습관을 가져야 합니다.

10-3. 테이블에 있는 데이터 삭제하기

DELETE문은 테이블에 있는 데이터를 삭제할 때 사용합니다. DELETE문의 기본 형식은 다음과 같습니다.

DELETE [FROM] [테이블명]
[WHERE 삭제할 대상의 행을 선별하기 위한 조건식];

키워드	필수 요소	선택 요소	설명
DELETE	테이블 이름	FROM	데이터를 삭제할 테이블을 지정합니다.
WHERE	-	삭제 데이터를 선별하는 조건식	테이블의 삭제할 데이터를 선별하는 조건식을 지정합니다. 생략할 경우 테이블의 모든 데이터를 삭제합니다.

 

DELETE FROM 또는 DELETE 키워드 뒤에 데이터를 삭제할 대상 테이블 이름을 지정합니다. DELETE문 역시

UPDATE문과 마찬가지로 삭제할 대상이 될 데이터를 선정하기 위해 WHERE절 및 조건식을 지정할 수 있습니다.

DELETE문에서 WHERE절을 사용하지 않으면 테이블 전체 데이터가 모두 삭제됩니다. 따라서 특정 행 데이터를 삭제하고

싶다면 WHERE절에 적절한 조건식을 명시해 주어야 합니다.

서브쿼리를 사용하여 데이터 삭제하기

DELETE문 역시 WHERE절에 서브쿼리를 사용하는 것이 가능합니다. 테이블을 조인한 서브쿼리의 결과 값을 활용하여

DELETE문의 WHERE절 조건식에 적용할 수 있습니다.

 

DELETE문은 기존에 존재하는 데이터를 삭제하는 명령이므로 앞에서 살펴본 UPDATE문처럼 사용할 때 특별히 주의해야합니다. DELETE문이나 WHERE절을 사용한다면 WHERE절의 조건식이 정확히 삭제할 대상을 선택하고 있는지 SELECT문을 사용하여 반드시 검증르 거친 후에 실행하는 습관을 가져야 합니다.

잊기 전에 한 번 더

Q1. 오른쪽과 같이 CHAP10HW_DEPT 테이블에 50, 60, 70, 80번 부서를 등록하는 SQL문을 작성하세요.

INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC) VALUES(50, 'ORACLE', 'BUSAN');
INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC) VALUES(60, 'SQL', 'ILSAN');
INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC) VALUES(70, 'SELECT', 'INCHEON');
INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC) VALUES(80, 'DML', 'BUNDANG');

Q2. 다음과 같이 CHAP10HW_EMP 테이블에 다음 8명의 사원 정보를 등록하는 SQL문을 작성해보세요.

INSERT INTO CHAP10HW_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES(7201,'TEST_USER1','MANAGER',7788,20160102,4500,50);

INSERT INTO CHAP10HW_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(7204,'TEST_USER4','SALESMAN',7201,20160531,2700,300,60);

Q3. CHAP10HW_EMP에 속한 사원 중 50번 부서에서 근무하는 사원들의 평균 급여보다 많은 급여를 받고 있는 사원들을 70번 부서로 옮기는 SQL문을 작성하세요.

UPDATE CHAP10HW_EMP SET DEPTNO = 70 WHERE SAL > (SELECT AVG(SAL) FROM CHAP10HW+EMP);

Q4. CHAP10HW_EMO에 속한 사원 중 60번 부서의 사원 중에 입사일이 가장 빠른 사원보다 늦게 입사한 사원 급여를 10% 인상하고 80번 부서로 옮기는 SQL문을 작성해 보세요.

UPDATE CHAP10HW_EMP
SET SAL = SAL*1.1,
	DEPTNO = 80
WHERE DEPTNO = 60 AND HIREDATE >(
    SELECT MIN(HIREDATE)
    FROM CHAP10HW_EMP
    WHERE DEPTNO = 60
);

Q5. CHAP10HW_EMP에 속한 사원 중 급여 등급이 5인 사원을 삭제하는 SQL문을 작성하세요.

DELETE FROM CHAP10H_EMP
WHERE EMPNO IN(SELECT EMPNO FROM CHAP10HW_SALGRADE WHERE GRADE = 5);