오늘은 dml 과 tcl을 배웠다.
-- *** DML (Data Manipulation Language) : 데이터 조작 언어
-- 테이블에 값을 삽입하거나 (INSERT), 수정하거나(UPDATE), 삭제하는 구문(DELETE)
-- 주의 : 혼자서 COMMIT, ROLLBACK 하지 말 것!
-- 테스트용 테이블 생성
CREATE TABLE EMPLOYEE2 AS SELECT FROM EMPLOYEE;
CREATE TABLE DEPARTMENT2 AS SELECT FROM DEPARTMENT;
SELECT FROM EMPLOYEE2;
SELECT FROM DEPARTMENT2;
-- 테이블 삭제
DROP TABLE DEPARTMENT2;
-- 1. INSERT
-- 테이블에 새로운 행을 추가하는 구문
-- 1) INSERT INTO 테이블명 VALUES(데이터, 데이터, ...)
-- 테이블에 있는 모든 컬럼에 대한 값을 INSERT할 때 사용
-- INSERT 하고자 하는 컬럼이 모든 컬럼인 경우 컬럼명 생략 가능.
-- 단, 컬럼의 순서를 지켜서 VALUES에 값을 기입해야 함.
INSERT INTO EMPLOYEE2
VALUES(900, '장채헌','901230-2345678', 'JANK_CH@KH.OR.KR', '01012341234', 'D1', 'J7', 'S3', 4300000, 0.2, 200,
SYSDATE, NULL, 'N');
SELECT * FROM EMPLOYEE2
WHERE EMP_ID = 900;
ROLLBACK;
DELETE FROM EMPLOYEE2
WHERE EMP_ID = 900;
COMMIT;
--2) INSERT INTO 테이블명(컬럼명1, 컬럼명2, 컬럼명3 ... 컬럼명I)
-- VALUES(데이터1, 데이터2, 데이터3,... 데이터I)
-- 테이블에 내가 선택한 컬럼에 대한 값만 INSERT 할 때 사용
-- 선택 안된 컬럼은 값이 NULL 이 들어감(DEFAULT 존재 시 DEFAULT 값으로 삽입됨)
INSERT INTO EMPLOYEE2(EMP_ID, EMP_NAME,EMP_NO ,EMAIL, PHONE, DEPT_CODE, JOB_CODE , SAL_LEVEL , SALARY)
VALUES(900, '장채현', '901123-2345678','JANK_CH@KDH.OR.KR', '01012341234', 'D1', 'J7', 'S3', 4300000);
SELECT * FROM EMPLOYEE2 e
WHERE EMP_ID = 900;
ROLLBACK;
--(참고) INSERT 시 VALUES 대신 서브쿼리 사용 가능
CREATE TABLE EMP_01(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR2(20)
);
SELECT * FROM EMP_01;
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE2
LEFT JOIN DEPARTMENT2 ON (DEPT_CODE = DEPT_ID);
INSERT INTO EMP_01
(SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE2
LEFT JOIN DEPARTMENT2 ON (DEPT_CODE = DEPT_ID));
SELECT * FROM EMP_01;
-- 서브쿼리(SELECT) 결과를 EMP_01 테이블에 INSERT
--> SELECT 조회 결과의 데이터 타입, 컬럼 개수가 같아야 한다.
-- 2. UPDATE (내용을 바꾸거나 추가해서 최신화, 새롭게 만드는 것)
-- 테이블에 기록된 컬럼의 값을 수정하는 구문
-- [작성법]
-- UPDATE 테이블명 SET 컬럼명 = 바꿀값
-- [WHERE 컬럼명 비교 연산자 비교값];
-- DEPARTMENT2 테이블에서 DEPT_ID 'D9'인 부서 정보 조회
SELECT * FROM DEPARTMENT2
WHERE DEPT_ID = 'D9';
-- DEPARTMENT2 테이블에서 DEPT_ID가 'D9'인 행의 DEPT_TITLE을 전략기획팀 으로 수정
UPDATE DEPARTMENT2
SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_ID = 'D9';
COMMIT;
-- EMPLOYEE2 테이블에서 BONUS를 받지 않는 사원의
-- BONUS를 0.1로 변경
UPDATE EMPLOYEE2
SET BONUS = 0.1
WHERE BONUS IS NULL;
SELECT * FROM EMPLOYEE2;
-- * 조건절을 설정하지 않고 UPDATE 구문 실행 시 모든 행의 컬럼값이 변경.
SELECT * FROM DEPARTMENT2;
UPDATE DEPARTMENT2 SET
DEPT_TITLE = '기술연구팀';
ROLLBACK;
-- * 여러 컬럼을 한번에 수정 시 콤마(,)로 컬럼을 구분하면 됨.
-- D9 / 전략기획팀 -> D0 / 전략기획2팀 수정
UPDATE DEPARTMENT2 SET
DEPT_ID = 'D0', DEPT_TITLE = '전략기획2팀'
WHERE DEPT_ID = 'D9'
AND DEPT_TITLE = '전략기획팀';
-- * UPDATE 시에도 서브쿼리 사용 가능
-- [작성법]
-- UPDATE 테이블명 SET 컬럼명 = (서브쿼리)
-- EMPLOYEE2 테이블에서
-- 평소에 유재식 사원을 부러워하던 방명수 사원의
-- 급여와 보너스율을 유재식 사원과 동일하게 변경해 주기로 했다.
-- 이를 반영하는 UPDATE 문을 작성하시오.
-- 유재식 급여
SELECT SALARY FROM EMPLOYEE2 WHERE EMP_NAME = '유재식';
-- 유재식 보너스
SELECT BONUS FROM EMPLOYEE2 WHERE EMP_NAME = '유재식';
-- 방명수 급여, 보너스 수정
UPDATE EMPLOYEE2 SET
SALARY = (SELECT SALARY FROM EMPLOYEE2 WHERE EMP_NAME = '유재식'),
BONUS = (SELECT BONUS FROM EMPLOYEE2 WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';
SELECT EMP_NAME, SALARY, BONUS
FROM EMPLOYEE2
WHERE EMP_NAME IN('유재식', '방명수');
-- 3. MERGE(병합) (참고만 하세요!)
-- 구조가 같은 두개의 테이블을 하나로 합치는 기능
-- 테이블에서 지정하는 조건의 값이 존재하면 UPDATE
-- 조건의 값이 없으면 INSERT 됨
CREATE TABLE EMP_M01
AS SELECT * FROM EMPLOYEE;
CREATE TABLE EMP_M02
AS SELECT * FROM EMPLOYEE
WHERE JOB_CODE = 'J4';
SELECT FROM EMP_M01;
SELECT FROM EMP_M02;
INSERT INTO EMP_M02 VALUES (999,'곽두원', '561016-1234567', 'KK@KH.OR.KR','01011112222',
'D9','J4','S1', 9000000, 0.5, NULL, SYSDATE, NULL, DEFAULT
);
SELECT FROM EMP_M01; -- 23명
SELECT FROM EMP_M02;
UPDATE EMP_MO2 SET SALARY = 0;
SELECT * FROM EMP_M02;
MERGE INTO EMP_M01 USING EMP_M02 ON(EMP_M01.EMP_ID = EMP_M02.EMP_ID)
WHEN MATCHED THEN
UPDATE SET
EMP_M01.EMP_NAME = EMP_M02.EMP_NAME,
EMP_M01.EMP_NO = EMP_M02.EMP_NO,
EMP_M01.EMAIL = EMP_M02.EMAIL,
EMP_M01.PHONE = EMP_M02.PHONE,
EMP_M01.DEPT_CODE = EMP_M02.DEPT_CODE,
EMP_M01.JOB_CODE = EMP_M02.JOB_CODE,
EMP_M01.SAL_LEVEL = EMP_M02.SAL_LEVEL,
EMP_M01.SALARY = EMP_M02.SALARY,
EMP_M01.BONUS = EMP_M02.BONUS,
EMP_M01.MANAGER_ID = EMP_M02.MANAGER_ID,
EMP_M01.HIRE_DATE = EMP_M02.HIRE_DATE,
EMP_M01.ENT_DATE = EMP_M02.ENT_DATE,
EMP_M01.ENT_YN = EMP_M02.ENT_YN
WHEN NOT MATCHED THEN
INSERT VALUES(EMP_M02.EMP_ID, EMP_M02.EMP_NAME, EMP_M02.EMP_NO, EMP_M02.EMAIL,
EMP_M02.PHONE, EMP_M02.DEPT_CODE, EMP_M02.JOB_CODE, EMP_M02.SAL_LEVEL, EMP_M02.SALARY, EMP_M02.BONUS, EMP_M02.MANAGER_ID, EMP_M02.HIRE_DATE,
EMP_M02.ENT_DATE, EMP_M02.ENT_YN);
SELECT * FROM EMP_M01;
-- 4. DELETE
-- 테이블의 행을 삭제하는 구문
-- [작성법]
-- DELETE FROM 테이블명 WHERE 조건설정
-- 만약 WHERE 조건을 설정하지 않으면 모든 행이 다 삭제됨
SELECT * FROM EMPLOYEE2
WHERE EMP_NAME = '장채현';
-- EMPLOYEE2 테이블에서 이름이 '장채현'인 사원 정보 삭제
DELETE FROM EMPLOYEE2
WHERE EMP_NAME = '장채현';
-- 삭제 확인
SELECT * FROM EMPLOYEE2
WHERE EMP_NAME = '장채현'; -- 조회 결과 없음
ROLLBACK;
-- EMPLOYEE2 테이블 전체 삭제
DELETE FROM EMPLOYEE2;
SELECT * FROM EMPLOYEE2;
ROLLBACK;
-- 5. TRUNCATE (DML 아님. DDL임)
-- 테이블의 전체 행을 삭제하는 DDL
-- DELETE 보다 수행 속도가 더 빠르다.
-- ROLLBACK을 통해 복구할 수 없다.
-- TRUNCATE 테스트용 테이블 생성
CREATE TABLE EMPLOYEE3
AS SELECT * FROM EMPLOYEE2;
-- 생성확인
SELECT * FROM EMPLOYEE3;
-- TRUNCATE로 삭제
TRUNCATE TABLE EMPLOYEE3;
SELECT * FROM EMPLOYEE3;
ROLLBACK;
-- 롤백 후 복구 확인 -> 복구 안됨을 확인!
SELECT * FROM EMPLOYEE3;
-- DELETE : 휴지통버리기
-- TRUNCATE : 완전 삭제
-- TCL(TRANSACTION CONTROL LANGUAGE) : 트랜잭션 제어 언어
-- COMMIT(트랜잭션 종료 후 저장), ROLLBACK(트랜잭션 취소), SAVEPOINT(임시저장)
-- DML : 데이터 조작 언어로 데이터의 삽입, 수정, 삭제
--> 트랜잭션은 DML과 관련되어 있음.
======================================================================
/* TRANSACTION이란?
-
데이터베이스의 논리적 연산 단위
-
데이터 변경 사항을 묶어 하나의 트랜잭션에 담아 처리함.
-
트랜잭션의 대상이 되는 데이터 변경 사항 : INSERT, UPDATE, DELETE (DML), MERGE
EX) INSERT 수행 --------------------------------> DB 반영(X)
INSERT 수행 --> 트랜잭션에 추가 --> COMMIT --> DB 반영(O)- T INSERT 10번 수행 --> 1개 트랜잭션에 10개 추가 --> ROLLBACK --> DB 반영 안됨
1) COMMIT : 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 DB에 반영
2) ROLLBACK : 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 삭제하고
마지막 COMMIT 상태로 돌아감.(DB에 변경 내용 반영 X)
3) SAVEPOINT : 메모리 버퍼(트랜잭션)에 저장 지점을 정의하여
ROLLBACK 수행 시 전체 작업을 삭제하는 것이 아닌
저장 지점까지만 일부 ROLLBACK
[SAVEPOINT 사용법]
SAVEPOINT 포인트명1;
...
SAVEPOINT 포인트명2;
...
ROLLBACK TO 포인트명1; -- 포인트1 지점 까지 데이터 변경사항 삭제*/
SELECT * FROM DEPARTMENT2;
-- 새로운 데이터 INSERT
INSERT INTO DEPARTMENT2 VALUES('T1', '개발1팀', 'L2');
INSERT INTO DEPARTMENT2 VALUES('T2', '개발2팀', 'L2');
INSERT INTO DEPARTMENT2 VALUES('T3', '개발3팀', 'L2');
COMMIT;
SELECT * FROM DEPARTMENT2;
-- DB에 반영된 것 처럼 보이지만
-- SQL 수행 시 트랜잭션 내용도 포함해서 수행된다.
-- (실제로 아직 DB에 반영 X)
-- ROLLBACK 후 확인
ROLLBACK;
SELECT * FROM DEPARTMENT2; --> COMMIT 후 롤백 안됨!
-- SAVEPOINT
INSERT INTO DEPARTMENT2 VALUES ('T4', '개발4팀', 'L2');
SAVEPOINT SP1;
SELECT * FROM DEPARTMENT2;
INSERT INTO DEPARTMENT2 VALUES('T5', '개발5팀', 'L2');
SAVEPOINT SP2;
INSERT INTO DEPARTMENT2 VALUES('T6', '개발6팀', 'L2');
SAVEPOINT SP3;
ROLLBACK TO SP1;
SELECT * FROM DEPARTMENT2;
DELETE FROM DEPARTMENT2
WHERE DEPT_ID LIKE 'T%';
ROLLBACK TO SP2;
SELECT * FROM DEPARTMENT2;
ROLLBACK TO SP1;
뭔가 이클립스로 자바를 배울때는 이렇게 하면 되겠는데? 싶은것들이나 자동완성들이 마음에 들었는데 디비버로 오라클 작성할때는 뭔가 답답한 느낌이 든다.
연습문제 푼것 추가
CREATE TABLE USER_TEST(
ID NUMBER,
Name VARCHAR2(30),
ReserveDate DATE,
RoomNum NUMBER);
SELECT * FROM USER_TEST;
INSERT INTO USER_TEST
VALUES ('1', '홍길동', '2016-01-05', 2014);
INSERT INTO USER_TEST
VALUES ('2', '임꺽정', '2016-02-12', 2014);
INSERT INTO USER_TEST
VALUES ('3', '장길산', '2016-01-16', 2014);
INSERT INTO USER_TEST
VALUES ('4', '홍길룡', '2016-03-17', 2014);
INSERT INTO USER_TEST
VALUES ('6', '김유신', NULL, 2014);
UPDATE USER_TEST SET
ROOMNUM = 918
WHERE ID = '2';
UPDATE USER_TEST SET
ROOMNUM = 1208
WHERE ID = '3';
UPDATE USER_TEST SET
ROOMNUM = 504
WHERE ID = '4';
DELETE FROM USER_TEST
WHERE ID = '6';
SELECT * FROM USER_TEST;
UPDATE USER_TEST SET
ROOMNUM = 2002;
SELECT * FROM USER_TEST ut ;
CREATE TABLE EMPLOYEE4 AS SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE4;
UPDATE EMPLOYEE4 SET
SALARY = SALARY + 1000000
WHERE HIRE_DATE < '2000-01-01';
ROLLBACK;
-- 6. 한국 근무 보너스 0.5 변경
UPDATE EMPLOYEE4 SET
BONUS = 0.5
WHERE EMP_NAME IN (
SELECT EMP_NAME
FROM EMPLOYEE4
JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
JOIN LOCATION ON (LOCAL_CODE = LOCATION_ID)
JOIN "NATIONAL" USING (NATIONAL_CODE)
WHERE NATIONAL_NAME = '한국'
);
