SQL 정리 - 2

====================================================

#ANY는 OR로 연결한 것과 같다.

SELECT * FROM emp
 WHERE sal >ANY ( 2916, 1566, 2175 ) ;

SELECT * FROM emp
 WHERE sal > 2916
   OR  sal > 1566
   OR  sal > 2175 ;

 SELECT * FROM emp
 WHERE sal > ( SELECT MIN(AVG(sal))
               FROM emp
               GROUP BY deptno ) ;

 SELECT * FROM emp
 WHERE sal >ALL ( SELECT AVG(sal)
                  FROM emp
                  GROUP BY deptno ) ;

 SELECT * FROM emp
 WHERE sal > 2916
   AND sal > 1566
   AND sal > 2175 ;

 SELECT * FROM emp
 WHERE sal > ( SELECT MAX(AVG(sal))
                  FROM emp
                  GROUP BY deptno ) ;

====================================================
 # Multiple Columns Subquery
  - IN 연산자 사용

 -- 각 부서별 최소 급여를 가지는 사원 정보 검색

 SELECT *
 FROM emp
 WHERE (deptno, sal) IN (SELECT deptno, MIN(sal)
                         FROM emp
                         GROUP BY deptno) ;

 -- 아래 오류는 비슷해보이지만 문제가 있음

 SELECT *
 FROM emp
 WHERE deptno IN (SELECT deptno FROM emp
                  GROUP BY deptno)
   AND sal    IN (SELECT MIN(sal)
                  FROM emp
                  GROUP BY deptno) ;

====================================================

 # Subquery 와 NOT IN
 - NOT IN 연산을 Subquery의 리턴 결과로 비교할 때는
   반드시 !!!! NULL이 리턴되지 못하게 할 것 !!!!!
 - 사용하지 마세요. 대신 EXISTS 연산자 !!! 사용

 SELECT *
 FROM DEPT
 WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP
                      WHERE DEPTNO IS NOT NULL) ;

====================================================
# EXISTS 연산자
- Subquery의 조건에 만족하는 행이 존재하면 TRUE
- TRUE 상황일 때는 더 이상의 탐색 진행 안함
- 존재 유무를 확인할 때 사용
- Semi Join 이라고도 함
- Correlated Subqery (Scalar Subquery)
  - Subquery 에서 Main Query의 컬럼을 참조하는 Subquery
  - 상관 하위 서브쿼리
  - Main Query의 후보행 개수 만큼 Subquery의 재실행 가능

SELECT *
FROM EMP E
WHERE EXISTS ( SELECT 1(어떤 값이 들어와도 상관 없음)
               FROM EMP
               WHERE MGR = E.EMPNO ) ;

SELECT *
FROM EMP E
WHERE NOT EXISTS ( SELECT *
                   FROM EMP
                   WHERE MGR = E.EMPNO ) ;

 

====================================================
#SQL
 - Query : SELECT
 - DML   : MERGE, UPDATE, INSERT, DELETE
 - DDL   : CREATE, DROP, ALTER, TRUNCATE, RENAME, COMMENT ...
 - DCL   : GRANT, REVOKE 
 - TCL   : COMMIT, ROLLBACK, SAVEPOINT 
=======================================================
# DML (Data Manipulation Language)
 - INSERT : 행 입력

   INSERT INTO TABLE_NAME(COLUMN_NAME,...)
   VALUES (......) ;

   ex) INSERT INTO EMP(EMPNO,ENAME,SAL,DEPTNO)
       VALUES (1234,'RYU',NULL,30) ;

   INSERT INTO TABLE_NAME
   SELECT ....

   ex) INSERT INTO EMP
       SELECT * FROM copy_emp
       WHERE DEPTNO = 40 ;
  
 - UPDATE : 행 수정
  
   UPDATE TABLE_NAME
   SET COL_NAME = VALUE,
       COL_NAME = VALUE
  [WHERE .....        ]
 
  ex) UPDATE EMP
      SET SAL = 4000, COMM = 1000
      WHERE EMPNO = 7788 ;
    
      UPDATE EMP
      SET SAL = 4000, COMM = 1000 ;

      UPDATE EMP
      SET SAL = (SELECT SAL FROM EMP
                 WHERE EMPNO = 7839)
      WHERE EMPNO = 7788 ;

 - DELETE : 행 삭제

   DELETE TABLE_NAME
   [WHERE .....     ]

  ex) DELETE EMP
      WHERE EMPNO = 7788 ;

      DELETE EMP  ;

 - MERGE  : 집합의 병합
   (INSERT, UPDATE, DELETE 동시 수행 가능)
 
   MERGE INTO TABLE_NAME A
   USING TABLE_NAME|VIEW|SUBQUERY B
     ON A.KEY = B.KEY       -- 조인 조건식
   WHEN MATCHED THEN
   UPDATE
   SET A.COL_NAME = B.COL_NAME,
       A.COL_NAME = B.COL_NAME
   WHEN NOT MATCHED THEN
   INSERT
   VALUES (B.COL_NAME, .... ) ;

=======================================================
# TCL (Transaction Control Language)
 - Transaction :
     - 일관된 데이터 변경을 위한 명령문의 묶음
     - DML 명령문의 묶음
     - 논리적인 하나의 작업 단위

 - 트랜잭션 구성
   - 하나 이상의 DML 명령문
   - 1개의 DDL, DCL 명령문
 - 트랜잭션은 COMMIT, ROLLBACK 명령문 수행 시 종료

 # 트랜잭션을 제어하는 명령어

 - COMMIT    : 변경 사항 저장
 - ROLLBACK  : 변경 사항 취소
 - SAVEPOINT : 트랜잭션 중간에 지정된 포인트

 # 트랜잭션의 자동 종료
   - DDL, DCL 명령문 수행 시 AUTO COMMIT
   - 비정상 종료 시 AUTO ROLLBACK

 # Lock
   - 유효한 DML 명령문이 수행되는 도중
     ROW Level로 자원 보호를 목적으로 구현된 잠금장치
   - 트랜잭션이 종료될때까지 보유 함
   - 자동 관리되고 있고,
     DML 작업이 완료되면 빠른 COMMIT, ROLLBACK 수행

# Read Consistency (읽기 일관성)
 - SELECT 시점에 COMMIT 된 데이터만 검색

# SAVEPOINT 사용
 - 트랜잭션 중간에 저장된 포인트를 이용하여 부분적인 ROLLBACK 구현
   SAVEPOINT A ;
   ...
   SAVEPOINT B ;
   ...
   ROLLBACK TO B ;

=======================================================

=======================================================
# DDL (Data Definition Language)
 - CREATE    : OBJECT 생성
 - DROP      : OBJECT 삭제
 - ALTER     : OBJECT 수정
 - TRUNCATE  : 테이블, 연관된 인덱스의 데이터 삭제
 - RENAME    : OBJECT 이름 변경
 - COMMENT   : OBJECT 주석 생성

=======================================================
# 이름 지정 규칙
 - 첫 글자는 문자로 시작
 - 최대 30 BYTE 까지 가능
 - A~Z, 0~9, 특수문자 "$, #, _" 만 사용 가능
   ex) EMP10, EMP$
 - 동일 이름 불가능 (EMP 테이블 2개?)
 - 예약어 불가능

=======================================================
# Data Types 

 #문자
 - CHAR      : 고정 길이 문자 (~2000 BYTE)
 - VARCHAR2  : 가변 길이 문자 (~4000 BYTE)
 - LONG      : ~ 2GB 문자
 - CLOB      : ~ 최대 128TB 문자

 #숫자

 - NUMBER    : 수치형 (~22 BYTE)
               1<=P<=38 , -84<=S<=127

   CREATE TABLE T2 
   (C1   NUMBER,
    C2   NUMBER(4,2),        99.99
    C3   NUMBER(4,0),      9999.
    C4   NUMBER(4,4),         0.9999
    C5   NUMBER(3,-1)) ;   9990.

#날짜

 - DATE      : 날짜 (YYYY/MM/DD HH:MM:SS) 범위
 - TIMESTAMP : 날짜 (YYYY/MM/DD HH:MM:SS.123456789) 범위

#Binary

 - LONG RAW  : ~ 2GB 이진 데이터
 - BLOB      : ~ 최대 128TB 이진 데이터

 CREATE TABLE T1
 (C1        CHAR(5 CHAR),    // 문자 5개(한글, 영어 논외). CHAR(5)은 고정길이 5byte.
  C2    VARCHAR2(5)) ;        // 가변 길이 최대 5byte.

 SELECT * FROM T1  WHERE C1 = 'ABC';   //최대 사이즈가 5byte이므로, 공백이 들어감. 하지만 값과 비교시에는 자동적으로 처리해줌.

 SELECT * FROM T1  WHERE TRIM(C1) = C2 ; //하지만 필드와 비교시에는 공백을 처리해주지 않아서 TRIM을 붙여줘야함.

=======================================================
# Object (객체) (TVSIS)
 - TABLE     : 데이터의 기본 저장 단위 (컬럼과 행으로 구성)
 - VIEW      : 저장된 Subquery (가상의 테이블, 실제 데이터 X)
 - SEQUENCE  : 번호 생성기
 - SYNONYM   : 동의어
 - INDEX     : 검색 속도를 향상화 시키기 위해 생성

 create sequence sq1 start with 8000 ;

 select sq1.nextval
 from dual ;

 insert into emp
 values ( sq1.nextval , ....  ) ;

 select * from user02.emp ;
 create synonym e for user02.emp ;
 select * from e ;

 create index emp_ix on emp(deptno) ;

 =======================================================
# DCL (Data Control Language)

 - GRANT  : 권한 부여
 - REVOKE : 권한 취소 

   GRANT 권한이름 TO 유저명 ;
   REVOKE 권한이름 FROM 유저명 ;

=======================================================

Subquery
- SQL 명령문에 안에 포함된 또다른 SELECT 명령문
- Main Query 보다 먼저 실행 가능,
  실행 결과를 Main Query에서 사용
- SQL 명령문 안에 한번 이상 사용 가능,
  SELECT 문장의 모든 형식 사용 가능
- Scalar Subquery일 경우에는 GROUP BY 절을
  제외한 모든 절에서 사용 가능

===========================================================
# Subquery 구분

 - 행의 개수
   Single row, Multiple rows Subquery
 - 컬럼의 개수
   Multiple Columns Subquery
 - 단일 값 리턴 (하나의 행, 하나의 컬럼)
   Scalar Subquery  

====================================================================
# Subquery와 SELECT 절
 - Scalar Subquery 만 가능 (Correlated Subquery 포함) 
 - Multiple rows/columns Subquery는 사용 불가능 
 ====================================================================
# Subquery와 ORDER BY절 
 - Scalar Subquery만 가능 (Correlated Subquery 포함) 

====================================================================

# TOP-n 질의
 - ROWNUM 컬럼 이용, INLINE VIEW를 사용하여 정렬된 결과에서 ROWNUM으로 행

# ROWNUM
 - 행 번호를 의미
 - 단, 실제 저장된 값이 아니다!!!!!
 - 사용 가능한 조건식
   ROWNUM = 1
   ROWNUM <= X

- 급여를 가장 많이 받는 사원 3명 검색 !!
SELECT *
FROM (SELECT *
      FROM EMP
      ORDER BY SAL DESC)
WHERE ROWNUM <= 3  ;

SELECT *
FROM (SELECT *
      FROM EMP
      ORDER BY SAL DESC)
WHERE ROWNUM = 1  ;

---1방법 (5 ~ 10번째 사람 출력하기 위해서.. MINUS이용)
SELECT *
FROM (SELECT *
      FROM EMP
      ORDER BY SAL DESC)
WHERE ROWNUM <= 10 
MINUS
SELECT *
FROM (SELECT *
      FROM EMP
      ORDER BY SAL DESC)
WHERE ROWNUM <= 5  ;

 

--2방법
SELECT ROWNUM, B.*
FROM (SELECT  ROWNUM AS RNUM, A.*
      FROM (SELECT *
            FROM EMP
            ORDER BY SAL DESC) A
      ) B
WHERE RNUM BETWEEN 5 AND 10 ;

 

-- 랭크 매기는 방법
SELECT ROWNUM, C.*
FROM ( SELECT ROWNUM AS R2, B.*
       FROM ( SELECT ROWNUM AS R1, A.*
              FROM EMP A
              ORDER BY SAL DESC ) B
     ) C
WHERE R2 = 5 ;