SQL 200 학습 시작! ③-(081~120)

prana·2023년 4월 8일
0

database

목록 보기
35/38
post-thumbnail

📕 초보자를 위한 SQL 200제 (PL/SQL)

3. 081~120

081 COMMIT, ROLLBACK

데이터 저장 및 취소하기 (COMMIT, ROLLBACK)

COMMIT : COMMIT 이전에 수행했던 DML 작업들을 데이터베이스에 영구히 반영하는 TCL이다.
ROLLBACK : 마지막 COMMIT 명령어를 수행한 이후 DML문을 취소하는 TCL

  • SCOTT의 월급을 4000으로 변경한 UPDATE문이 취소된다.
  • SELECT * FROM EMP;
  • INSERT INTO EMP(empno, ename, sal, deptno) VALUES(1123, 'JANE', 2500, 21);
  • COMMIT; -> 커밋 완료
  • SELECT * FROM EMP;
  • UPDATE EMP SET SAL = 4000 WHERE ENAME ='JANE';
  • SELECT * FROM EMP WHERE ENAME = 'JANE';
  • ROLLBACK;
  • SELECT * FROM EMP WHERE ENAME = 'JANE';
  • 👇 4000이었다가 2500으로 바뀌는 것을 확인할 수 있다.
  • 전체

TCL (Transaction Control Language)

종류설명
COMMIT모든 변경사항을 데이터베이스에 반영
ROLLBACK모든 변경사항을 취소
SAVEPOINT특정 지점까지의 변경을 취소

082 데이터 입력, 수정, 삭제 한 번에 하기 (MERGE)

데이터 입력, 수정, 삭제 한 번에 하기 (MERGE)

---082 
ALTER TABLE emp
   ADD loc varchar2(10); --- 사원 테이블에 부서 위치 컬럼 추가


MERGE INTO emp e --MERGE 대상이 되는 TARGET 테이블명 작성
USING dept d --- SOURCE 테이블명. DEPT로부터 데이터를 읽어와, 
				DEPT 테이블의 데이터로 EMP 테이블을 MERGE한다. 
ON (e.deptno = d.deptno) --- TARGET 과 SOURCE 테이블을 조인하는 구문.
						--- 조인에 성공하면 MERGE UPDATE절 실행, 
                        	실패하면 MERGE INSERT절 실행 
WHEN MATCHED THEN  -- MERGE UPDATE절
UPDATE set e.loc = d.loc 
WHEN NOT MATCHED THEN  -- MERGE INSERT절
INSERT (e.empno, e.deptno, e.loc) VALUES (1111,d.deptno, d.loc) ;

--- 부서 테이블을 이용해 해당 사원의 부서 위치로 값이 갱신되도록 함
--- 만약 부서 테이블에는 존재하는 부서이지만 사원 테이블에 없는 부서번호라면, 새롭게 사원 테이블로 입력

083 락(lock) 이해하기

락 (LOCK)

  • sqlplus를 연다

  • 창 두개를 띄워서 로그인 해둔다. 

터미널 1
SQL> UPDATE emp
  2    SET sal = 3000
  3    WHERE ename='JONES';
1 row updated.

UPDATE를 실행하게 되면 행 전체를 잠그기 때문에, JONES의 월급 뿐만 아니라 다른 컬럼들의 데이터도 변경할 수 없고 WAITING하게 된다.

  • 터미널2창에서 실행
- 터미널 2
SQL> UPDATE emp
  2    SET sal = 9000
  3    WHERE ename='JONES';
  커서 깜박이기만 함

UPDATE문을 수행하면, UPDATE 대상이 되는 행(ROW)을 잠궈(LOCK)버린다.

  • 터미널 창 1에 접속한 세션이 JONES의 행을 갱신하고,
    아직 COMMIT이나 롤백을 수행하지 않았기 때문에 해당 행이 잠겨 있기 때문이다.
  • 터미널 1에서 
SQL> COMMIT;

Commit complete.

COMMIT을 수행하게 되면, UPDATE가 실행되고 행에 걸렸던 잠금은 해제된다.

  • 터미널 2에서 
SQL>
SQL> UPDATE emp
  2    SET sal = 9000
  3    WHERE ename='JONES';
1 row updated.

터미널1의 행에 걸린 잠금을 해제하였기 때문에, 터미널 창2는 수정이 바로 된다.

UPDATE 시 LOCK을 거는 이유

  • 데이터의 일관성을 보장

  • 터미널 창1은 자기가 변경한 데이터를 커밋하기 전까지 일관되게 유지해야 한다.

  • 커밋하기 전까지는 자기가 변경한 데이터에 대해 일관성이 보장되어야 한다.

  • 그래서 오라클에서는 LOCK을 사용하여 UPDATE문을 수행하면 해당 행에 LOCK을 건다.

084 SELECT FOR UPDATE절 이해하기

  • 터미널창 1

  • JONES의 데이터를 검색하는데, 행에 자동으로 락이 걸린다.

  • 터미널창 2

  • UPDATE 변경이 안되고, WAITING하게 된다.

  • 터미널창 1

  • COMMIT을 수행

  • 터미널창 2

  • 터미널창1에서 COMMIT을 하게 되면, 터미널창 2에서 UPDATE문이 수행된다.

085 서브쿼리를 사용하여 데이터 입력하기

  • EMP 테이블의 구조를 그대로 복제한 EMP2테이블에 부서 번호가 10번인 사원들의 사원 번호, 이름, 월급, 부서 번호를 한 번에 입력 
CREATE TABLE emp2
    as
       SELECT *
          FROM emp
          WHERE 1=2;

          
INSERT INTO emp2(empno, ename, sal, deptno)
 SELECT empno, ename, sal, deptno ---VALUES 대신 입력하고자 하는 서브 쿼리문을 기술.
 									INSERT문의 컬럼 순서와 맞춘다. 
    FROM emp
    WHERE deptno = 10;

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

-- 086 
UPDATE EMP
SET SAL = (SELECT SAL 
                FROM EMP
                WHERE ENAME ='ALLEN')
WHERE JOB = 'SALESMAN';
UPDATE문서브 쿼리 가능 여부
UPDATE절서브 쿼리 사용 가능
SET절서브 쿼리 사용 가능
WHERE절서브 쿼리 사용 가능

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

--- 087 
DELETE FROM EMP
WHERE SAL > (SELECT SAL   
                FROM EMP 
                WHERE ENAME='SCOTT');

SELECT * FROM EMP;

088 서브쿼리를 사용하여 데이터 합치기 

--- 088 서브쿼리를 사용하여 데이터 합치기
alter table dept
add sumsal  number(10);


--- 사원 테이블을 이용하여 SUMSAL 컬럼의 데이터를 부서 테이블의 부서 번호별 토탈 월급으로 갱신 
MERGE INTO dept d
USING (SELECT deptno, sum(sal) sumsal
             FROM emp
             GROUP BY deptno) v
ON (d.deptno = v.deptno)
WHEN MATCHED THEN
UPDATE set d.sumsal = v.sumsal;

093 일반 테이블 생성하기

테이블 명과 컬럼명 지정 시 규칙

  • 반드시 문자로 시작
  • 이름의 길이는 30자 이하여야 한다.
  • 대문자 알파벳과 소문자 알파벳과 숫자를 포함할 수 있다.
  • 특수문자는 $, _, #만 포함할 수 있다.

주요 데이터 유형

유형설명
CHAR고정 길이 문자 데이터 유형, 최대 길이는 2000
VARCHAR2가변 길이 문자 데이터 유형, 최대 길이 4000
LONG가변 길이 문자 데이터 유형, 최대 2GB의 문자 데이터를 허용한다.
CLOB문자 데이터 유형, 최대 4GB의 문자 데이터를 허용
BLOB바이너리 데이터 유형, 최대 4GB 바이너리 데이터를 허용
NUMBER숫자 데이터 유형, 십진 숫자의 자리수는 최대 38자리까지 허용 가능, 소숫점 이하 자리는 -84 ~127까지 허용한다.
DATE날짜 데이터 유형, 기원전 4712년 01월 01일부터 기원후 9999년 12월 31일까지의 날짜를 허용

--- 094 임시 테이블 생성하기 CREATE TEMPORARY TABLE

임시 테이블 생성하기

옵션설명
ON COMMIT DELETE ROWS임시 테이블에 데이터를 입력하고, COMMIT 할 때까지만 데이터를 보관
ON COMMIT PRESERVE ROWS임시 테이블에 데이터를 입력하고 세션이 종료될 때까지 데이터를 보관
  • 만든 임시 테이블은 COMMIT을 하면 데이터가 사라진다.

095 복잡한 쿼리를 단순하게 하기

VIEW

---095
CREATE VIEW EMP_VIEW --- VIEW이름
AS --- VIEW를 통해 보여줘야할 쿼리를 작성 
SELECT EMPNO, ENAME, SAL, JOB, DEPTNO
    FROM EMP
    WHERE JOB ='SALESMAN';
  • EMP 테이블의 모든 컬럼을 보는 것이 아니라, 일부의 컬럼들만 볼 수 있다.
  • 그래서 VIEW는 보안상 공개하면 안 되는 데이터들이 있을 때 유용하다.
  • 👉 예를 들어 사원 테이블에서 커미션은 공개하면 안 된다고 하면,
    커미션만 빼고 나머지 컬럼들로만 VIEW를 생성하고, EMP 테이블 대신 VIEW를 제공하면 된다.

VIEW를 변경하면 실제 테이블도 변경이 될까?

  • VIEW는 데이터를 가지고 있지 않고, 단순히 테이블을 바로 보는 객체이다.

  • VIEW를 쿼리하면, 뷰를 만들 때 작성했던 쿼리문이 수행되면서, 실제 EMP 테이블을 쿼리한다.

  • UPDATE문도 마찬가지로, EMP_VIEW를 갱신하면 실제 테이블인 EMP의 데이터가 갱신된다.

096 복잡한 쿼리 단순하게 하기

  • 뷰의 쿼리문에 그룹 함수 사용
  • 🌟 뷰 생성 시 함수나 그룹 함수를 작성할 때는 반드시 컬럼 별칭을 사용해야 한다.
  • 뷰에 함수나 그룹함수가 포함되어 있으면, 복합 뷰라고 한다.
단순 VIEW복합 VIEW
테이블의 개수1개2개 이상
함수 포함 여부포함 안 함포함
데이터 수정 여부수정 가능수정 불가능 할 수 있음

복합 VIEW

  • 그룹 함수를 쿼리하는 복합 뷰는 수정이 되지 않는다.
  • 큰 장점은 바로 복잡한 쿼리를 단순화 시킬 수 있다는 점.

097 데이터 검색 속도 높이기 INDEX

--097 INDEX
CREATE INDEX EMP_SAL
    ON EMP(SAL); -- 인덱스를 생성하고자 하는 테이블과 컬럼명
-- 세션창이 하나만 있어야 쿼리문 실행 가능

SELECT ENAME, SAL
    FROM EMP
    WHERE SAL = 1600;

  • 인덱스가 없을 때는 테이블을 FULL SCAN한다.

  • 인덱스는 컬럼값과 ROWID로 구성된다.

  • ROWID : 데이터가 있는 행의 물리적 주소

  • 컬럼값은 내림차순으로 정렬.

① 인덱스가 월급을 내림차 순으로 정렬하고 있으므로, 바로 월급 1600을 찾는다.
② 인덱스의 ROWID로 테이블의 해당 ROWID 찾아 이름과 월급을 조회한다.

  • 검색할 데이터만 바로 스캔

098

    
-- 098 절대로 중복되지 않는 번호 만들기
CREATE SEQUENCE SEQ1
START WITH 1
INCREMENT BY 1
MAXVALUE 100
NOCYCLE;

SELECT MAX(EMPNO) FROM EMP;

INSERT INTO EMP(EMPNO, ENAME, SAL, JOB, DEPTNO)
    VALUES (8300, 'JAMES', 3500, 'ANALYST', 23);


CREATE TABLE EMP02
( EMPNO   NUMBER(10),
 ENAME    VARCHAR2(10),
 SAL  NUMBER(10) );

INSERT INTO EMP02 VALUES( SEQ1.NEXTVAL, 'JACKK', 3500);
INSERT INTO EMP02 VALUES( SEQ1.NEXTVAL, 'JAMESS', 4500);

SELECT *
FROM emp02;
  • 시퀀스를 사용하여 데이터를 입력한다. SEQ1.NEXTVAL
  • 시퀀스의 다음 번호를 출력 또는 확인할 때는 시퀀스이름.NEXTVAL 사용한다.

099 실수로 지운 데이터 복구하기

  • 백업을 복구하지 않고, 과거 시점의 데이터를 조회하는 방법을 학습한다. 
SELECT SYSTIMESTAMP FROM DUAL;

SELECT SYSTIMESTAMP - INTERVAL '5' MINUTE FROM DUAL; --현재 시간에서 5분을 뺀 시간
  • 이 시간은 데이터베이스의 파라미터인 undo_retention으로 확인해볼 수 있다. (대문자로는 조회안됨)
  • 테이블을 플래쉬백 할 수 있는 골든타임은 기본이 15 분이다. 900은 900초를 의미한다.

100 실수로 지운 데이터 복구하기 FLASHBACK TABLE

  • 실수로 데이터를 delete 하고 commit 하였을 경우 복구 방법을 학습한다. 
--- 100 실수로 데이터를 delete 하고 commit 하였을 경우 복구 방법을 학습한다. 
ALTER TABLE EMP ENABLE ROW MOVEMENT; --플래쉬백이 가능한 상태로 설정

FLASHBACK TABLE EMP TO TIMESTAMP(SYSTIMESTAMP - INTERVAL '5' MINUTE);
-- 플래쉬백은 백업을 가지고 복구하는 것이 아니라, 5분 전부터 현재까지 수행했던 DML 작업을 반대로 수행하면서 과거로 되돌린다. 

---플래쉬백 확인
SELECT ROW_MOVEMENT 
    FROM user_tables WHERE table_name = 'EMP'; 
 
---
SELECT *
  FROM emp
  WHERE ename='KING';

  • 5분 전부터 현재까지 수행한 작업 중 DELETE가 있었다면, 반대로 INSERT를 수행하고 INSERT가 있었다면, 반대로 DELETE를 수행한다.

  • 성공적으로 플래쉬백이 되었다면 데이터를 확인한 후, COMMIT을 해야 변경된 상태가 데이터베이스에 영구히 반영된다.

  • 👉 지정된 과거 시점부터 현재 시점 사이에 DDL(Data Definition Language)문이나, DCL(Data Control Langage)문을 수행하였다면 FLASHBACK명령어가 수행되지 않고 에러가 발생하게 된다.

101 FLASHBACK DROP

  • 실수로 테이블을 DROP 하였을 경우 복구하는 방법 
--- 101 FLASHBACK DROP 
DROP TABLE EMP;

-- DROP 후 휴지통에 존재하는지 확인하는 방법
SELECT original_name, droptime
  FROM user_recyclebin;

FLASHBACK TABLE EMP TO BEFORE DROP;

--- 휴지통에서 복구할 때 테이블명을 다른 이름으로 변경하려면 다음과 같이 수행 
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP2;

102 실수로 지운 데이터 복구하기 FLASHBACK VERSION QUERY

  • 과거부터 현재까지 테이블의 데이터가 어떻게 변경되어 왔는지 이력을 확인하는 방법
--- 102 
SELECT SYSTIMESTAMP FROM DUAL; 

SELECT ename, sal, deptno
  FROM emp
  WHERE ename='KING';
  
UPDATE emp
  SET sal = 8000
  WHERE ename='KING'; 

COMMIT;

UPDATE emp
  SET deptno = 20
  WHERE ename='KING'; 

COMMIT;
--- alter table EMP enable row movement ;
--- KIN의 데이터 변경 이력 정보를 확인한다. 
SELECT ename, sal, deptno, versions_starttime, versions_endtime, versions_operation
  FROM emp
  VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2023/04/08 22:47:40','RRRR-MM-DD HH24:MI:SS')
                AND MAXVALUE
  WHERE ename='KING'
  ORDER BY versions_starttime;
-- 쿼리의 날짜와 시간은 앞에서 미리 확인한 날짜와 시간으로 작성하여 수행하여야 한다.

103 FLASHBACK TRANSACTION QUERY

  • 특정 테이블을 과거의 특정 시점으로 되돌리기 위한 DML문을 추출하는 방법을 학습

  • 사원 테이블의 데이터를 5분 전으로 되돌리기 위한 DML문을 출력

  • SQL PLUS에서 작업

  • DB를 정상 종료한다. SHUTDOWN IMMEDIATE;

  • 데이터베이스를 마운트 상태로 올린다.

  • STARTUP MOUNT

  • TRANSACTION QUERY 결과를 보기 위해서는 데이터베이스 모드를 아카이브 모드로 변경해야 한다.

  • 아카이브 모드로 변경하겠다는 것은 장애가 발생하였을 때 DB를 복구할 수 있는 로그 정보를 자동으로 저장하게 하는 모드

  • 👉 아카이브 모드로 변경하기 위해서는 DB를 한 번 내렸다 올려야 한다.
    ❌ - 시도했다가 번거로워서 진행 중단하기로 했다.

104 primary key 생성

105 데이터의 품질 높이기 unique
--- null값을 입력할 수 있다.

  • 테이블의 특정 컬럼에 중복된 데이터가 입력되지 않게 제약을 걸 수 있다.
  • ex: DNAME VARCHAR2(14) CONSTRAINT DEPT3_DNAME_UN UNIQUE,
  • 제약 이름을 테이블명컬럼명제약종류 로 기술, 제약 이름 다음에 UNIQUE 키워드를 기술한다.
  • 테이블 생성 후, 제약을 생성하는 방법 

CREATE TABLE DEPT4
( DEPTNO  NUMBER(10),
  DNAME   VARCHAR2(13),
  LOC   VARCHAR2(10) );

ALTER TABLE DEPT4
  ADD CONSTRAINT DEPT4_DNAME_UN UNIQUE(DNAME);

107 CHECK

  • 사원 테이블을 생성하는데, 월급이 0에서 6000 사이의 데이터만 입력되거나 수정될 수 있도록 제약
--- 107 CHECK
CREATE TABLE EMP6
( EMPNO     NUMBER(10), 
 ENAME      VARCHAR(20),
 SAL        NUMBER(10) CONSTRAINT EMP6_SAL_CK
 CHECK (SAL BETWEEN 0 AND 6000)
);

INSERT  INTO emp6 VALUES (7839, 'KING', 5000);
INSERT  INTO emp6 VALUES (7698, 'BLAKE', 2850);
INSERT  INTO emp6 VALUES (7782, 'CLARK', 2450);
INSERT  INTO emp6 VALUES (7839, 'JONES', 2975);
COMMIT;

SELECT  * FROM emp6;
  • 체크 제약 조건 위배❗❗❗
  • 체크 제약 조건 삭제

108 FOREIGN KEY

  • PK : 부모 테이블, FK(REFERENCES): 자식 테이블
    • EMP7 테이블의 DEPTNO가 DEPT7 테이블의 DEPTNO를 참조하고 있어서,
    • EMP7 테이블의 DEPTNO에 데이터를 입력 또는 수정 시
    • DEPT7 테이블의 DEPTNO에 존재하는 부서 번호에 대해서만 입력 또는 수정이 가능하게 된다.
  • 제약을 삭제하려면 이렇게 하면 오류가 난다.
  • CASCADE 옵션을 붙여주자. 이때 EMP7 테이블의 FOREIGN KEY 제약도 같이 삭제되는 것을 알 수 있다.

    ALTER TABLE DEPT7
    DROP CONSTRAINT DEPT7_DEPTNO_PK CASCADE;

109 WITH절 사용하기 

--- 109 WITH절 사용하기 
-- 동일한 SQL이 반복되어 사용될 때 성능을 높이는 방법
-- 직업과 직업별 토탈 월급을 출력하는데 직업별 토탈 월급들의 평균값보다 더 큰 값들만 출력
WITH JOB_SUMSAL AS ( SELECT JOB, SUM(SAL) as 토탈
                       FROM EMP
                       GROUP BY JOB )  
SELECT JOB, 토탈
  FROM JOB_SUMSAL
  WHERE 토탈 > ( SELECT  AVG(토탈) 
                   FROM JOB_SUMSAL  );
  • 검색시간이 오래 걸리는 SQL이 하나의 SQL 내에서 반복되어 사용될 때 성능을 높이기 위한 방법으로 WITH절을 사용한다.

WITH절의 수행되는 원리

  • 직업과 직업별 토탈 월급을 출력하여 임시 저장 영역(Temporary Tablespace)에 테이블명을 job_sumsal로 명명지어 저장한다.

  • 임시 저장 영역에 저장된 테이블인 job_sumsal을 불러와서 직업별 토탈 월급들의 평균값보다 더 큰 직업별 토탈 월급들을 출력

  • 임시 저장 영역에 저장된 데이터를 출력하는데 많은 시간이 걸렸다면, with절은 이 시간을 반으로 줄여준다.

  • 위의 with 절을 서브쿼리문으로 수행하면 다음과 같다. 

SELECT JOB, 토탈
  FROM JOB_SUMSAL
  WHERE 토탈 > ( SELECT  AVG(토탈) 
                   FROM JOB_SUMSAL  );

  • 이는 동일한 sql을 두 번이나 사용하여 2배는 걸리게 된다. 그러나 with절로 고쳐 작성하게 되면, 5개의 데이터를 임시 저장 영역에 저장하고, 그 데이터를 job_sumsal 테이블 이름으로 불러오기만 하면 되기 때문에 시간이 절반으로 줄어든다.

  • 🚨 단, with절에서 사용한 temp 테이블은 with절 내에서만 사용 가능하다.

110 SUBQUERY FACTORING 

WITH JOB_SUMSAL AS ( SELECT JOB, SUM(SAL)  토탈
                       FROM   EMP
                       GROUP BY JOB ) , 
    -- 직업과 직업별 토탈 월급을 출력하여 JOB_SUMSAL이라는 이름으로 임시 저장 영역에 저장한다. 
        DEPTNO_SUMSAL AS ( SELECT DEPTNO, SUM(SAL) 토탈
                              FROM EMP 
                              GROUP BY DEPTNO
                              HAVING SUM(SAL) > ( SELECT AVG(토탈) + 3000
                                                    FROM JOB_SUMSAL )
                               )  
    -- 부서번호와 부서 번호별 토탈 월급을 출력하는데 
    --- JOB_SUMSAL의 토탈값의 평균에 3000을 더한 값보다 더 큰 토탈 월급을 출력. 
    -- 여기서 JOB_SUMSAL 임시 테이블을 참조하고 있다. 이러한 방법은 FROM절의 서브쿼리로는 불가능하다. 
    
SELECT DEPTNO, 토탈
  FROM DEPTNO_SUMSAL ;
  • 일반 from절로는 불가하지만, with 절을 이용하면 임시 저장 영역에 테이블을 생성하므로 참조가 가능해진다.
  • 이렇게 with절의 쿼리결과를 임시 테이블로 생성하는 것을 SUBQUERY FACTORING이라고 한다.
profile
prana
이전 포스트

SQL 200 학습 시작! ①-(001~040)

다음 포스트

Mysql 설치 방법

0개의 댓글

관련 채용 정보