윈도우 - 설정 - 연결 - 연결 유형
Auto-commit by default체크 해제 (COMMIT/ROLLBACK연습용
DML : 데이터 조작 언어
테이블에 값을 삽입하거나(INSERT), 수정하거나(UPDATE), 삭제(DELETE) 하는 구문
CREATE TABLE 생성할테이블명 AS 쿼리문
-
테스트용 테이블 생성
CREATE TABLE EMPLOYEE2 AS SELECT * FROM EMPLOYEE;
CREATE TABLE DEPARTMENT2 AS SELECT * FROM DEPARTMENT; -
조회
SELECT * FROM EMPLOYEE2;
SELECT * FROM DEPARTMENT2;
1. INSERT
테이블에 새로운 행을 추가하는 구문
INSERT INTO 테이블명 VALUES(데이터, 데이터, 데이터......)
테이블에 모든 컬럼에 대한 값을 INSERT 할 때 사용
INSERT 하고자 하는 컬럼이 모든 컬럼인 경우 컬럼명 생략 가능.
단, 컬럼의 순서를 지켜서 VALUES에 값을 기입해야 함.
INSERT INTO EMPLOYEE2
VALUES(900, '장채현', '901123-1080503', 'jang_ch@kh.or.kr', '01055569512', 'D1', 'J8', 'S3',
4300000, 0.2, '200', SYSDATE, NULL, 'N');
SELECT * FROM EMPLOYEE2
WHERE EMP_ID = 900; -- 장채현이 추가 되었음!
ROLLBACK;
DELETE FROM EMPLOYEE2
WHERE EMP_ID = 900;
COMMIT;
INSERT INTO 테이블명(컬럼명, 컬럼명...) VALUES(데이터1, 데이터2...)
테이블에 내가 선택한 컬럼에 대한 값만 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', 'TEST@TEST.COM', '01012341234', 'D1', 'J7', 'S3', '4500000');
SELECT * FROM EMPLOYEE2
WHERE EMP_ID = 900;
(참고용) INSERT 시 VALUES 대신 서브쿼리 사용 가능
INSERT INTO 테이블명(서브쿼리);
- 테이블 생성
CREATE TABLE EMP_01(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR2(20)
);
서브쿼리용 SELECT 구문
이 SELECT 구문을 그대로 INSERT 구문을 통해서
EMP_01 이라는 테이블에 넣을 수 있다!
/
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE2
LEFT JOIN DEPARTMENT2 ON(DEPT_CODE = DEPT_ID);
/
--> 서브쿼리(SELECT) 결과를 EMP_01 테이블에 INSERT
--> SELECT 조회 결과의 데이터 타입, 컬럼 개수가
-- INSERT 하려는 테이블의 컬럼과 일치해야함
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;
2. UPDATE
테이블에 기록된 컬럼의 값을 수정하는 구문 (내용을 바꾸던가 추가해서 최신화함)
UPDATE 테이블명 SET 컬럼명 = 바꿀값 WHERE 컬럼명 = 조건값
[WHERE 컬럼명 비교연산자 비교값];
WHERE 조건 중요!
UPDATE할 컬럼과 조건을 WHERE 절에서 정하기 때문에
-
DEPARTMENT2 테이블에서 DEPT_ID가 '09'인 부서 정보 조회
SELECT * FROM DEPARTMENT2
WHERE DEPT_ID = 'D9'; -- D9 총무부 -
DEPARTMENT2 테이블에서 DEPT_ID가 '09'인 행의 DEPT_TITLE을 '전략기획'으로 수정
UPDATE DEPARTMENT2
SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_ID = 'D9'; -
UPDATE 확인
SELECT * FROM DEPARTMENT2
WHERE DEPT_ID = 'D9';
--D9 전략기획팀으로 수정됨을 확인!
- EMPLOYEE2 테이블에서 BONUS를 받지 않는 사원의 BONUS 0.1로 변경
UPDATE EMPLOYEE2
SET BONUS = 0.1
WHERE BONUS IS NULL;
SELECT EMP_NAME, BONUS FROM EMPLOYEE2;
// 사원명, 변경된 보너스 조회
ROLLBACK;
// 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 삭제하고
마지막 COMMIT 상태(시점)로 돌아감
조건절을 설정하지 않고 UPDATE 구문 실행 시 모든 행의 컬럼값이 변경.
SELECT * FROM DEPARTMENT2;
UPDATE DEPARTMENT2
SET DEPT_TITLE = '기술연구팀';
ROLLBACK;
여러 컬럼을 한번에 수정할 시 콤마(,)로 컬럼을 구분하면 됨.
UPDATE DEPARTMENT2
SET DEPT_ID = 'D0',
DEPT_TITLE = '전략기획2팀'
WHERE DEPT_ID = 'D9'
AND DEPT_TITLE = '총무부';
SELECT * FROM DEPARTMENT2;
UPDATE시에도 서브쿼리 사용 가능
UPDATE 테이블명 SET 컬럼명 = (서브쿼리)
EMPLOYEE2 테이블에서
평상시 유재식 사원을 부러워하던 방명수 사원의
급여와 보너스율을 유재식 사원과 동일하게 변경해 주기로 했다.
이를 반영하는 UPDATE문을 작성하시오.
-
유재식 급여 조회
SELECT SALARY FROM EMPLOYEE2 WHERE EMP_NAME = '유재식'; -- 3400000 -
유재식 보너스 조회
SELECT BONUS FROM EMPLOYEE2 WHERE EMP_NAME = '유재식'; -- 0.2 -
방명수 급여 조회
SELECT SALARY FROM EMPLOYEE2 WHERE EMP_NAME = '방명수'; -- 1380000 -
방명수 보너스 조회
SELECT BONUS FROM EMPLOYEE2 WHERE EMP_NAME = '방명수'; -- NULL -
방명수 급여, 보너스 수정
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 e
WHERE EMP_NAME IN('유재식', '방명수'); -- 둘다 3400000 / 0.2
MERGE(병합) (참고만 하세요!)
-- 구조가 같은 두 개의 테이블을 하나로 합치는 기능.
-- 테이블에서 지정하는 조건의 값이 존재하면 UPDATE
-- 조건의 값이 없으면 INSERT됨
CREATE TABLE EMP_M01
AS SELECT * FROM EMPLOYEE; -- EMPLOYEE를 본떠 만듦
CREATE TABLE EMP_M02
AS SELECT * FROM EMPLOYEE -- EMPLOYEE를 본떠 만들었으나 JOB_CODE 가 J4인애만 본떠 만듦
WHERE JOB_CODE = 'J4';
SELECT FROM EMP_M01;
SELECT FROM EMP_M02;
INSERT INTO EMP_M02
VALUES (999, '곽두원', '561016-1234567', 'kwack_dw@kh.or.kr',
'01011112222', 'D9', 'J4', 'S1', 9000000, 0.5, NULL,
SYSDATE, NULL, DEFAULT);
SELECT FROM EMP_M01; -- 23명
SELECT FROM EMP_M02; -- 5명(기존4명 + 신규 1명)
-- (송은희, 임시환, 이중석, 유하진) + 곽두원
UPDATE EMP_M02 SET SALARY = 0;
SELECT * FROM EMP_M02;
ROLLBACK;
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; -- EMP_M02테이블의 SALARY가 0이 된 상태에서 위 구문을 실행할 경우
-- 송은희, 임시환, 이중석, 유하진 등 SALARY 가 0으로 조회됨
DELETE
테이블에 행을 삭제하는 구문
DELETE FROM 테이블명 WHERE 조건설정
만약에 WHERE 조건을 설정하지 않으면, 모든 행이 다 삭제됨
- EMPLOYEE2 테이블에서 '장채현' 사원 정보 조회
SELECT * FROM EMPLOYEE2
WHERE EMP_NAME = '장채현';
-
EMPLOYEE2 테이블에서 '장채현' 사원 정보 삭제
DELETE FROM EMPLOYEE2
WHERE EMP_NAME = '장채현'; -
삭제 확인
SELECT * FROM EMPLOYEE2
WHERE EMP_NAME = '장채현';
--> 조회 결과 없음
ROLLBACK;
- EMPLOYEE2 테이블 전체 삭제
DELETE FROM EMPLOYEE2; -- 24개행 전체 삭제
TRUNCATE (DDL 입니다. DML 아님)
테이블의 전체 행을 삭제하는 DDL
DELETE보다 수행속도가 더 빠르다.
ROLLBACK을 통해 복구할 수 없다.
-
TRUNCATE 테스트용 테이블 생성
CREATE TABLE EMPLOYEE3
AS SELECT * FROM EMPLOYEE2; -
생성 확인
SELECT * FROM EMPLOYEE3; -
DELETE로 모든(행) 데이터 삭제
DELETE FROM EMPLOYEE3;
ROLLBACK;
- 복구 확인
SELECT * FROM EMPLOYEE3;
-
TRUNCATE로 삭제
TRUNCATE TABLE EMPLOYEE3; -
삭제 확인
SELECT * FROM EMPLOYEE3;
ROLLBACK;
- 복구 확인 -- 롤백 후 복구확인(복구 안됨을 확인했다)
SELECT * FROM EMPLOYEE3;
-- DELETE : 휴지통 버리기(행 삭제 / ROLLBACK 가능)
-- TRUNCATE : 완전 삭제(행 삭제 / ROLLBACK 불가)
TCL(TRANSACTION CONTROL LANGUAGE) : 트랜잭션 제어 언어
COMMIT(트랜잭션 종료 후 저장), ROLLBACK(트랜잭션 취소), SAVEPOINT(임시저장)
DML(INSERT, UPDATE, DELETE) 관련 되어 있다.
TRANSACTION 이란?
데이터베이스 논리적 연산 단위
데이터베이스의 상태를 변화 시키기 위해서, 수행하는 작업 단위
데이터 변경 사항을 묶어 하나의 트랜잭션에 담아 처리함.
트랜잭션의 대상이 되는 데이터 변경 사항 : INSERT, UPDATE, DELETE(DML) + MERGE
EX) INSERT 수행 -----------------------> DB 반영 (X)
INSERT 수행 ---> 트랜잭션에 추가 --> COMMIT ---> DB반영(O)
INSERT 10번 수행 --> 1개 트랙잭션에 1개 추가 --> ROLLBACK --> DB 반영 안됨
1) COMMIT : 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 DB에 반영
2) ROLLBACK : 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 삭제하고
마지막 COMMIT 상태(시점)로 돌아감. (DB에 변경 내용 반영 X)
3) SAVEPOINT : 메모리 버퍼(트랜잭션)에 저장 지점을 정의하여 ROLLBACK 수행 시 전체 작업을 삭제하는 것이 아닌 저장 지점까지만 일부 ROLLBACK (COMMIT이 아니다)
[SAVEPOINT 사용법]
SAVEPOINT 포인트명1;
...
SAVEPOINT 포인트명2;
...
ROLLBACK TO 포인트명1;
// 포인트 1 지점까지 데이터 변경사항 삭제
SAVEPOINT 예시
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'); -
INSERT 확인
SELECT * FROM DEPARTMENT2;
--> DB에 반영된 것 처럼 보이지만
-- SQL 수행 시 트랜잭션에 임시 저장된 상태
-- (실제로 아직 DB 반영 X)
ROLLBACK;
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');
SELECT * FROM DEPARTMENT2;
COMMIT;
SELECT * FROM DEPARTMENT2;
ROLLBACK;
SELECT * FROM DEPARTMENT2; --> 롤백 안됨!
- SAVEPOINT 확인
INSERT INTO DEPARTMENT2 VALUES('T4', '개발4팀', 'L2');
SAVEPOINT SP1; -- SAVEPOINT 지정
SELECT * FROM DEPARTMENT2;
INSERT INTO DEPARTMENT2 VALUES('T5', '개발5팀', 'L2');
SAVEPOINT SP2;
SELECT * FROM DEPARTMENT2;
INSERT INTO DEPARTMENT2 VALUES('T6', '개발6팀', 'L2');
SAVEPOINT SP3;
SELECT * FROM DEPARTMENT2;
ROLLBACK TO SP1;
SELECT * FROM DEPARTMENT2;
