8.2 DML, DATA DICTIONARY,

-- 데이터 조작어 (DML)

-- 테이블을 생성하는 DDL
CREATE TABLE DEPT_TEMP
    AS SELECT * FROM DEPT;
SELECT * FROM DEPT_TEMP;

-- 테이블에 데이터를 추가 INSERT
-- INSERT INTO 테이블 이름 (열이름1, 열이름2, ....)
-- VALUES (열이름1에 들어갈 데이터,)

INSERT INTO DEPT_TEMP (deptno, DNAME, LOC)
VALUES (50,'DATABASE','SEOUL');
SELECT * FROM DEPT_TEMP;
DESC DEPT_TEMP;
-- 에러 상황 : 데이터타입에 맞지 않거나, 길이가 맞지 않거나, 열보다 데이터를 많이 넣거나
--INSERT INTO DEPT_TEMP(deptno, DNAME, LOC)
--    VALUES (123, 'DATABASE','SEOUL','ABCD');

-- 열이름 없이도 insert문이 수행되지만 생략하지 않는 편이 좋다.
INSERT INTO DEPT_TEMP 
VALUES (60,'NETWORK','BUSAN');
SELECT * FROM DEPT_TEMP;
-- NULL 값 넣기
INSERT INTO DEPT_TEMP 
VALUES (70,'WEB',NULL);
SELECT * FROM DEPT_TEMP;
-- 실무에서는 데이터베이스에 익숙하지 않은 사람이 보아도 헷갈리지 않게
-- 명시적으로 NULL을 사용한다.
INSERT INTO DEPT_TEMP VALUES (80,'MOBILE','');
SELECT * FROM DEPT_TEMP;
-- 열 값을 입력하지 않으면 NULL이 채워진다.
INSERT INTO DEPT_TEMP (DEPTNO, DNAME) VALUES (90,'MOBILE');
SELECT * FROM DEPT_TEMP;

--EMP 테이블 생성
CREATE TABLE EMP_TEMP
    AS SELECT * FROM EMP WHERE 1 != 1;
SELECT * FROM emp_temp;

DESC EMP_TEMP;
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (9999,'홍길동','PRESIDENT',NULL,'2001/01/01',5000,1000,10);
-- DATE문을 삽입할 때 형식'YYYY/MM/DD', 'YYYY-MM-DD'
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (1111,'성춘향','MANAGER',9999,'2001-01-05',4000,NULL,20);
-- 날짜 데이터 입력할 때 주의점 'DD/MM/YYYY' <- 이렇게 순서를 바꾸면 오류가 발생한다.
-- 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);
-- 시스템 날짜 입력(지금 시점)
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (3111,'심청이','MANAGER',9999,SYSDATE,4000,NULL,30);

-- SUB QUERY문으로 다중 행 삽입하기
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
    AND S.GRADE = 1;
    
INSERT INTO emp_temp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
        SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
        FROM EMP E, SALGRADE S
        WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
            AND S.GRADE = 1;
-- 서브 쿼리문 INSERT 주의점
-- VALUES 절 사용하지 않음
-- 데이터가 추가되는 열과 서브쿼리 열의 개수와 자료형이 일치해야한다.

 

SELECT * FROM dept_temp2;
-- DML UPDATE 데이터 수정

CREATE TABLE dept_temp2
    AS SELECT * FROM DEPT;

SELECT * FROM dept_temp2;

-- UPDATE문의 문법
--UPDATE [테이블]
--SET [변경할 열1] = [변경할 값1], [변경할 열2] = [변경할 값2], ...
--WHERE 변경할 대상을 선별하는 조건

UPDATE DEPT_TEMP2
SET LOC='SEOUL';
-- 조건절을 주지 않을 경우 선택한 열의 모든 데이터가 수정된다.
SELECT * FROM dept_temp2;
-- 수정한 내용을 되돌리고 싶을때;
ROLLBACK; -- TCL

UPDATE DEPT_TEMP2
SET DNAME='DATABASE', LOC='SEOUL'
WHERE DEPTNO=40;
SELECT * FROM DEPT_TEMP2;

-- 서브쿼리 사용하기
SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 40;
-- SET 절에서 사용하는 방법
UPDATE DEPT_TEMP2
SET (DNAME, LOC) = (SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 40)
WHERE DEPTNO=40;
SELECT * FROM DEPT_TEMP2;

-- WHERE 절에서 사용하는 방법
UPDATE DEPT_TEMP2
SET  LOC = 'SEOUL'
WHERE DEPTNO = (SELECT DEPTNO FROM dept_temp2 WHERE dname = 'OPERATIONS');

SELECT DEPTNO FROM dept_temp2 WHERE dname = 'OPERATIONS';
--UPDATE문에서 서브쿼리를 사용할 때 주의점
--꼭 SELECT문으로 한 번 확인하고, UPDATE문으로 작성할 것.

--DML : DELETE
CREATE TABLE emp_temp2
    AS SELECT * FROM EMP;

--DELETE 문법
--DELETE FROM  테이블명
--(WHERE)삭제할 대상 행을 선별하기 위한 조건식

DELETE FROM emp_temp2
WHERE JOB = 'MANAGER';

SELECT * FROM emp_temp2;

--서브쿼리문 사용하기
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 deptno = 30
                AND s.grade = 3);

SELECT empno 
FROM emp_temp2 e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
    AND deptno = 30
    AND s.grade = 3;
    
-- 테이블 전체 데이터 삭제하기
DELETE FROM emp_temp2;
-- 꼭 주의해서 WHERE절을 사용하자!
SELECT * FROM DEPT_TCL;



-- 커밋 전 : 세션 1에서 삭제한 데이터가
-- 그대로 존재
SELECT * FROM DEPT_TCL;
-- 커밋 후 : 세션1에서 조작한 데이터 반영
SELECT * FROM DEPT_TCL;

-- 읽기 일관성(read consistency)
-- 데이터를 직접 조작하는 세션 외
-- 다른 세션에서는 내용이 일관적으로
-- 조회, 출력, 검색 되는 특성

SELECT * FROM DEPT_TCL;

-- 세션 1에서 조작중인 데이터를
-- 세션 2가 접근하게 될 때
-- 명령을 실행하면 멈추게 된다. (LOCK)
UPDATE DEPT_TCL SET DNAME='DATABASE'
WHERE DEPTNO = 30;
-- 다른 세션의 데이터 조작이 완료될때까지
-- 기다리는 현상 : HANG
SELECT * FROM DEPT_TCL;
-- 30번 데이터는 세션2에 의해 LOCK된 상태
COMMIT;

SELECT * FROM DEPT_TCL;


-- TRANSACTION
-- 하나의 단위로 데이터를 처리
-- 관계형 데이터베이스에서 하나의 작업 또는 밀접하게 연관된 작업스행을
-- 나눌 수 없는 단위(원자성)로 수행하는 최소 수행단위

-- A계좌(100만원) B계좌(0원)로 송금을 하는 SQL문
[1] UPDATE ACCOUNT SET BALANCE = 0
WHERE ACCNO = 'A계좌번호';
[2] UPDATE ACCOUNT SET BALANCE = 1000000
WHERE ACCNO = 'B계좌번호';

-- 만약 [1] SQL만 수행되고 [2] SQL이 수행되지 않는 상황(정전 등)이 발생하면
-- 금전적 사고가 발생한다.
-- 'ALL OR NOTHING' 모든 SQL문이 수행이 되거나, 아니면 아예 수행이 되지 않아야 한다.

-- 트랜잭션 시작
[1] INSERT ...
[2] UPDATE ...
[3] DELETE ...
<TCL>;
-- 트랜잭션 종료

-- 실습용 테이블 만들기
CREATE TABLE DEPT_TCL
    AS SELECT * FROM DEPT;

SELECT * FROM DEPT_TCL;

-- 50번 부서에 'DATABASE', 'SEOUL' 추가
INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');
-- 40번 부서의 위치를 'BUSAN'으로 변경
UPDATE DEPT_TCL SET LOC='BUSAN' WHERE DEPTNO=40;
-- RESARCH 부서를 삭제
DELETE FROM DEPT_TCL WHERE DNAME='RESEARCH';
SELECT * FROM DEPT_TCL;

-- 트랜잭션 단위로 작업을 취소하고 싶을 때 ROLLBACK;
ROLLBACK;
SELECT * FROM DEPT_TCL;

-- 50번 부서에 NETWORK, SEOUL 추가
INSERT INTO DEPT_TCL VALUES (50, 'NETWORK', 'SEOUL');
-- 20번 부서 위치를 'BUSAN'으로 변경
UPDATE DEPT_TCL SET LOC  = 'BUSAN' WHERE DEPTNO = 20;
-- 40번 부서를 삭제해 주세요.
DELETE FROM DEPT_TCL WHERE DEPTNO = 40;

SELECT * FROM DEPT_TCL;
-- 트랜잭션 명령으로 영구히 반영하고 싶을 때 COMMIT
COMMIT;
ROLLBACK;

SELECT * FROM DEPT_TCL;
-- 세션 1에서 데이터를 조작(삭제)해도 
-- 트랜잭션 종료(COMMIT) 되기 전에는
-- 데이터베이스 또는 다른 세션에 반영되지 않는다.
DELETE FROM DEPT_TCL WHERE DEPTNO = 50;
SELECT * FROM DEPT_TCL;
-- 트랜잭션 종료
COMMIT;
SELECT * FROM DEPT_TCL;

-- LOCK : 잠금현상
-- 특정 세션에서 조작 중인 데이터는
-- 다른 세션에서 조작할 수 없는 상태가 된다.

SELECT * FROM DEPT_TCL;
UPDATE DEPT_TCL SET LOC='SEOUL'
WHERE DEPTNO = 30;
SELECT * FROM DEPT_TCL;
-- 다른 세션의 HANG 상태가 종료되려면
-- 트랜잭션 명령어를 사용하여 단위를 종료
COMMIT;
SELECT * FROM DEPT_TCL;

SELECT * FROM DEPT_TCL;

-- LOCK LEVEL
-- 행(ROW) 레벨 LOCK
-- 테이블(TABLE) 레벨 LOCK


-- DML을 사용하고 DDL을 사용하면 커밋이 되어 영구히 데이터 베이스에 반영 

DESC EMP_DDL;
SELECT * FROM EMP_DDL;

-- AS문을 사용하여 기본 테이블 구조와 일부 데이터를 복사해서 생성하기
CREATE TABLE EMP_DDL_30
    AS SELECT * FROM EMP WHERE DEPTNO = 30;
SELECT * FROM EMP_DDL_30;

CREATE TABLE EMP_ALTER
    AS SELECT * FROM EMP;
    
SELECT * FROM EMP_ALTER;

-- [ALTER ADD : 열(컬럼) 추가]
ALTER TABLE EMP_ALTER   -- 테이블 명
ADD HP VARCHAR2(20);    -- 추가할 컬럼 명 데이터타입

SELECT * FROM EMP_ALTER;

-- [ALTER RENAME : 열(컬럼)이름 변경]
ALTER TABLE EMP_ALTER
RENAME COLUMN HP TO TEL;

SELECT * FROM EMP_ALTER;

-- [ALTER MODIFY : 열의 자료형을 변경]
ALTER TABLE EMP_ALTER
MODIFY EMPNO NUMBER(5);
DESC EMP_ALTER;
--[ALTER DROP : 열을 삭제하기]
ALTER TABLE EMP_ALTER
DROP COLUMN TEL;

SELECT * FROM EMP_ALTER;

-- 테이블 이름 변경 RENAME
RENAME (테이블명) TO 변경할테이블명;
RENAME EMP_ALTER TO EMP_RENAME;
SELECT * FROM EMP_RENAME;

-- 테이블 데이터 삭제하기 TRUNCATE
TRUNCATE TABLE EMP_RENAME; -- DDL이기 때문에 COMMIT이 포함되어 롤백 불가.
ROLLBACK;

-- 테이블 자체를 삭제할 때 DROP
DROP TABLE EMP_RENAME;

---

-- OBJECT
-- DATA DICTIONARY
-- 특수 테이블(데이터베이스 생성할 때 자동으로 만들어지는 테이블)
-- 데이터베이스 운영에 중요한 데이터가 보관되어 있습니다.

-- 접두어에 따른 분류
-- USER_    : 사용자가 소유한 객체 정보
-- ALL_     : 사용 허가가 있는 모든 객체정보
-- DBA_     : 데이터베이스 관리 권한을 가진 정보
-- V$       : 데이터베이스 성능 관련 정보

-- 데이터 딕셔너리 확인
SELECT * FROM DICTIONARY;

-- 사용자가 사용할 수 있는 테이블 확인
SELECT TABLE_NAME FROM USER_TABLES;
-- 접근 가능한 모든 테이블 확인
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;
-- DBA 관리권한 테이블 확인(SYSTEM계정에서 접근 가능)
SELECT * FROM DBA_TABLES;

 

---

 

-- INDEX
-- 인덱스 : 책의 목차
-- 데이터 검색 방식
    -- 1. table full scan
    -- 2. index scan
-- 모든 데이터를 읽는 것보다 데이터 검색의 성능 향상이 필요할 때 INDEX를 사용한다.

-- 사용자 계정이 소유한 인덱스 정보 확인
SELECT * FROM USER_INDEXES;

-- 사용자 계정이 소유한 인덱스 컬럼 확인
SELECT * FROM USER_IND_COLUMNS;

-- 사용자가 INDEX를 만들 수 있지만, 컬럼이 기본키(PK) 고유키(UK)일때 자동 생성
--인덱스 생성하기
CREATE INDEX 인덱스이름
    ON 테이블이름(열이름1 [ASC/DESC],
                열이름2 [ASC/DESC], ... );

CREATE INDEX IDX_EMP_SAL
    ON EMP(SAL);    -- 정렬 옵션 미지정시 기본 ASC

SELECT * FROM USER_IND_COLUMNS;

-- WHERE 조건에 SAL 컬럼(INDEX) 지정한 컬럼이 있을 경우 검색 속도 빨라짐.

-- 서비스에서 자주 조회하는 컬럼이 있을 때 인덱스를 생성하면 속도 향상
-- 인덱스를 지정한다고 데이터 조회가 반드시 빨라지는 것은 아님.
-- 인덱스를 지정할 열의 선정 : 데이터 구조, 데이터 분포, 서비스 이용 고려

-- 인덱스 삭제
DROP INDEX 인덱스이름;

DROP INDEX IDX_EMP_SAL;

SELECT * FROM USER_IND_COLUMNS;

-- 인덱스를 생성할 때 정확한 데이터 분석이 필요
-- 무분별한 인덱스 생성은 오히려 성능을 떨어뜨리는 원인이 되기도 함.
-- SQL 튜닝

-- VIEW
-- 가상 테이블, 셀렉트 문을 저장한 객체 <-> 물리적 데이터
SELECT * FROM(SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO=20);

-- 뷰의 사용 목적
-- 1. 편리성 : 복잡도를 완화하기 위해
-- 2. 보안성 : 테이블의 특정 열을 노출하고 싶지 않을 경우

-- 권한이 없을 경우 SYSTEM 계정으로부터 권한을 부여받아야 한다.
-- DCL 권한 부여(GRANT/REVOKE)
GRANT CREATE VIEW TO SCOTT; -- 뷰 생성 권한 부여

CREATE VIEW VW_EMP20
    AS (SELECT EMPNO,ENAME, JOB,DEPTNO FROM EMP WHERE DEPTNO=20);

SELECT * FROM USER_VIEWS;
SELECT * FROM VW_EMP20;

-- VIEW 삭제
DROP VIEW VW_EMP20;

-- SEQUENCE
-- 순서, 순번을 생성하기 위한 객체
CREATE SEQUENCE 시퀀스이름
[INCREMENT BY n]            -- 얼마씩 증가할 것인지
[START WITH n]              -- 몇번부터 시작할 것인지
[MAXVALUE n | NO...]        -- 최대값을 얼마로 할거냐
[MINVALUE n | NO...]        -- 최소값을 얼마로 할거냐
[CYCLE | NOCYCLE]           -- 최대값에 도달했을 때 처음부터 다시 시작할 것인지
[CACHE n | NOCACHE]         -- 메모리에 할당한 수를 쓸 것인지

CREATE TABLE DEPT_SEQ
    AS SELECT * FROM DEPT WHERE 1 !=1;

SELECT * FROM DEPT_SEQ;

CREATE SEQUENCE SEQ_DEPT_SEQUENCE
    INCREMENT BY 10
    START WITH 10
    MAXVALUE 90
    MINVALUE 0
    NOCYCLE
    CACHE 2;
    
SELECT * FROM USER_SEQUENCES;
-- 시퀀스를 사용하여 데이터 삽입하기
INSERT INTO DEPT_SEQ (DEPTNO,DNAME,LOC)
    VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');
SELECT * FROM DEPT_SEQ;
-- 가장 마지막으로 생성된 시퀀스 확인하기
SELECT SEQ_DEPT_SEQUENCE.CURRVAL;
-- 시퀀스 생성 확인하기
INSERT INTO DEPT_SEQ (DEPTNO,DNAME,LOC)
    VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');
    
SELECT * FROM DEPT_SEQ;

ALTER SEQUENCE SEQ_DEPT_SEQUENCE
    INCREMENT BY 3
    MAXVALUE 99
    CYCLE;

SELECT * FROM USER_SEQUENCES;

INSERT INTO DEPT_SEQ (DEPTNO,DNAME,LOC)
    VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');
    
-- 시퀀스 삭제 : 시퀀스가 삭제되어도 시퀀스로 생성된 순서 데이터는 남아있다.
DROP SEQUENCE SEQ_DEPT_SEQUENCE;

-- SYNONYM 동의어
-- 테이블 이름이 너무 길 때, 객체(뷰, 인덱스, 시퀀스) 등의 이름을 간단하고
-- 짧게 사용하고 싶을 때 만들어준다.

CREATE SYNONYM 동의어이름
FOR [사용자.][객체이름];
--권한이 없을 시 DCL
GRANT CREATE SYNONYM TO SCOTT;

CREATE SYNONYM E
    FOR EMP;
    
SELECT * FROM E;
-- 동의어 삭제
DROP SYNONYM E;