[ORACLE] 기본 문법 4 _데이터 조작어
# 데이터 조작어 (DML:Data Manipulation Language)테이블에 데이터를 추가`변경`삭제할 때 사용하는 명령어 |
> DEPT 테이블을 복사해서 DEPT_TEMP 테이블 만들기
CREATE TABLE DEPT_TEMP
AS SELECT * FROM DEPT;
> EMP 테이블을 복사해서 EMP_TEMP 테이블 만들기
* WHERE 1<>1 : EMP 테이블과 같은 열 구조를 가지지만 데이터는 복사하고 싶지 않을 때 사용
CREATE TABLE EMP_TEMP
AS SELECT * FROM EMP
WHERE 1 < >1;
| INSERT문 (1) 정의 테이블에 데이터 추가 (2) 기본 형식 INSERT INTO 테이블 이름 [(열1, 열2, ..., 열N)] VALUES (열1에 들어갈 데이터, 열2에 들어갈 데이터, ..., 열N에 들어갈 데이터); |
사용 예시
> 열을 지정하여 INSERT문 사용하기
DEPT_TEMP 테이블에 부서번호(50), 부서이름(DATABASE), 지역(SEOUL)인 데이터 추가하기
INSERT INTO DEPT_TEMP (DEPTNO, DNAME, LOC)
VALUES(50, 'DATABASE', 'SEOUL');
테이블에 NULL 데이터 입력하는 방법 3가지
1. NULL 직접 작성(선호하는 방식*)
INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
VALUES(70 , 'WEB', NULL);
SELECT * FROM DEPT_TEMP;
2. 빈 공백 문자열 작성
INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
VALUES(80, 'MOBILE', '');
SELECT * FROM DEPT_TEMP;
3. NULL이 들어가야 할 열 이름을 입력하지 않기
INSERT INTO DEPT_TEMP(DEPTNO, LOC)
VALUES(90 , 'INCHEON');
SELECT * FROM DEPT_TEMP;
날짜 데이터 입력하기
1. 날짜 사이에 / 입력
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(9999, '홍길동', 'PRESIDENT', NULL, '2001/01/01', 5000, 1000, 10);
SELECT * FROM EMP_TEMP;
2. 날짜 사이에 - 입력
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(1111, '성춘향', 'MANAGER', 9999, '2001-01-05', 4000, NULL, 20);
SELECT * FROM EMP_TEMP;
3. 날짜 데이터 입력 순서(년/월/일)를 변경하고 싶을 때
* 임의로 날짜 데이터 입력 순서를 일/월/년 순서로 데이터를 입력하면 오류가 발생한다.
* 이땐 TO_DATE 함수를 사용하는 것이 좋다.
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(2111, '이순신', 'MANAGER', 9999, TO_DATE('07/01/2001', 'DD/MM/YYYY'), 4000, NULL, 20);
SELECT * FROM EMP_TEMP;
4. SYSDATE 사용해 날짜 데이터 입력하기
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(3111, '심청이', 'MANAGER', 9999, SYSDATE, 4000, NULL, 30);
SELECT * FROM EMP_TEMP;
★ 서브 쿼리를 사용하여 한 번에 여러 데이터 추가하기 ★
SALGRADE 테이블을 참조하여 급여 등급(SALGRADE)이 1인 사원만 EMP_TEMP 테이블에 추가하기
INSERT INTO EMP_TEMP (EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND S.GRADE = 1;
| UPDATE문 (1) 정의 테이블에 저장된 데이터 내용 수정 (2) 기본 형식 UPDATE [변경할 테이블] SET [변경할 열1] = [데이터], [변경할 열2] = [데이터], ..., [변경할 열n] = [데이터] [WHERE 데이터를 변경할 대상 행을 선별하기 위한 조건]; |
사용 예시
> 데이터 일부만 수정하기
DEPT_TEMP2 테이블에서 부서번호가 40인 사원의 부서명을 DATABASE, 지역을 SEOUL로 변경하기
UPDATE DEPT_TEMP2
SET DNAME = 'DATABASE',
LOC = 'SEOUL'
WHERE DEPTNO = 40;
> 서브쿼리를 사용해 데이터 일부분 수정하는 방법 3가지
UPDATE DEPT_TEMP2
SET (DNAME, LOC) = (SELECT DNAME, LOC
FROM DEPT
WHERE DEPTNO = 40)
WHERE DEPTNO = 40;
ROLLBACK; // 다시 되돌리기
UPDATE DEPT_TEMP2
SET DNAME = (SELECT DNAME
FROM DEPT
WHERE DEPTNO = 40),
LOC = (SELECT LOC
FROM DEPT
WHERE DEPTNO = 40)
WHERE DEPTNO = 40;
SELECT * FROM DEPT_TEMP2;
UPDATE DEPT_TEMP2
SET LOC = 'SEOUL'
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT_TEMP2
WHERE DNAME = 'OPERATIONS');
SELECT * FROM DEPT_TEMP2;
| DELETE문 (1) 정의 테이블에 저장된 데이터 삭제 (2) 기본 형식 DELETE [FROM] [테이블 이름] [WHERE 삭제할 대상 행을 선별하기 위한 조건식]; |
사용 예시
> 데이터 일부부만 삭제하기
EMP_TEMP2 테이블에서 직책이 MANAGER인 사원만 삭제하기
DELETE FROM EMP_TEMP2
WHERE JOB = 'MANAGER';
> 서브쿼리를 사용하여 데이터 삭제하기
EMP_TEMP2 테이블에서 부서번호가 30이고,
급여 등급이 3등급, 즉 급여가 1401 ~ 2000 사이에 있는 사원들만 삭제하기
* 서브 쿼리의 결과 값이 여러 개이므로 IN 연산자 사용
★이것도 잘 이해 안됨ㅠ★
DELETE FROM EMP_TEMP2
WHERE EMPNO IN (SELECT EMPNO FROM EMP_TEMP2 E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND S.GRADE = 3 AND E.DEPTNO = 30);
문제
> CHAP10HW_EMP에 속한 사원 중 50번 부서에서 근무하는 사원들의 평균 급여보다
많은 급여를 받고 있는 사원들을 70번 부서로 옮기기.
UPDATE CHAP10HW_EMP SET DEPTNO = 70
WHERE SAL > (SELECT AVG(SAL) FROM CHAP10HW_EMP
WHERE DEPTNO = 50);
> CHAP10HW_EMP에 속한 사원 중, 급여 등급이 5인 사원 삭제하기
DELETE FROM CHAP10HW_EMP
WHERE DEPTNO
IN (SELECT E.DEPTNO FROM CHAP10HW_EMP E, CHAP10HW_SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND S.GRADE = 5);
| 트랜젝션 트랜젝션이란 데이터 조작이 동시에 여러개가 진행되어야할때 여러개중 한개라도 오류가 나면 처음으로 되돌리는 방법을 말한다. 예를 들어 은행에서 송금을 한다고 하면 내계좌는 줄어들고 상대계좌는 늘어나야할텐데 그둘중 하나라도 오류가 나면 다시 처음으로 돌려놓는것이다. |
사용 예시
> 변경 => ROLLBACK; 원상복구
CREATE TABLE DEPT_TEST
AS SELECT * FROM DEPT;
CREATE TABLE DEPT_TEST
AS SELECT * FROM DEPT;
SELECT * FROM DEPT_TEST;
INSERT INTO DEPT_TEST VALUES(50, 'DATABASE' , 'SEOUL');
SELECT * FROM DEPT_TEST;
UPDATE DEPT_TEST SET LOC = 'BUSAN' WHERE DEPTNO=40;
SELECT * FROM DEPT_TEST;
DELETE FROM DEPT_TEST WHERE DNAME='RESEARCH';
SELECT * FROM DEPT_TEST;
롤백을 실행하면 원상복구 된걸 알수있다.
ROLLBACK;
SELECT * FROM DEPT_TEST;
> 변경 => COMMIT; 원상복구 불가
똑같이 진행후 COMMIT; 을하면 완전히 변경된다.
INSERT INTO DEPT_TEST VALUES(50, 'DATABASE' , 'SEOUL');
SELECT * FROM DEPT_TEST;
UPDATE DEPT_TEST SET LOC = 'BUSAN' WHERE DEPTNO=40;
SELECT * FROM DEPT_TEST;
DELETE FROM DEPT_TEST WHERE DNAME='RESEARCH';
SELECT * FROM DEPT_TEST;
아래 COMMIT; 을 하면 변경된 내용이 적용된다.
COMMIT;
SELECT * FROM DEPT_TEST;