농부지기

☞ DML 종류

1. CREATE TABLE : 새로운 데이블을 생성
2. DROP TABLE : 기존 테이블의 구조 및 모든 행을 삭제 
3. ALTER TABLE : 기존 테이블을 변경 - 컬럼의 추가,수정,삭제, 제약조건 추가,삭제,활성화,비활성화
4. TRUNCATE : 기존 테이블의 구조는 남기고 모든 행을 삭제
5. RENAME : object의 이름을 바꿈
6. COMMENT : 테이블이나 컬럼에 주석문 달기

※ 이러한 명령어는 모두 데이터 정의 명령어(DDL)로서 실행 시 자돈 COMMIT되고 ROLLBACK 될 수
   없으므로 주의해서 실행

☞ DROP TABLE

형식 : DROP TABLE 테이블명 [CASCADE CONSTRAINTS] ;
            - CASCADE CONSTRAINTS 옵션은 종속된 제약조건을 삭제

☞ ALTER TABLE

(1) 칼럼에 대한 변경
ㅇ 칼럼의 추가 : ALTER TABLE 테이블명 ADD (컬럼 datatype [DEFAULT 형식][CONSTRAINT 정의]
                                                                  [,컬럼 datatype]...);
ㅇ 칼럼의 수정 : 기존에 존재하는 컬럼의 크기와 데이터 타입의 변경
               : ALTER TABLE 테이블명 MODIFY(컬럼 datatype [DEFAULT 형식]
                                                   [NOT NULL 제약조건][,컬럼datatype]...);
ㅇ 칼럼의 삭제 : ALTER TABLE 테이블명 CROP COLUMN 컬럼
※ 한번에 하나씩, PK 칼럼을 삭제할 경우 CASCADE CONSTRAINTS 옵션을 사용할 것

ㅇ 사용불가 상태로 전환 : ALTER TABLE 테이블명 SET UNUSED [ COLUMN ] 칼럼 ;
    -  SET UNUSED 상태의 칼럼 제거 : ALTER TABLE 테이블명 DROP UNUSED COLUMNS ;
                                   : DROP의 시간 소요를 단축할 수 있음.

(2) Constraint에 대한 변경
ㅇ 제약조건의 추가 : ALTER TABLE 테이블명 ADD [CONSTRAINT 제약 조건명] 제약조건유형 (칼럼명);
   ※ NOT NULL 제약조건은 테이블 레벨 제약조건으로 기술 불가, MODIFY절 활용
ㅇ 제약조건의 삭제 : ALTER TABLE 테이블명 DROP CONSTRAINT 제약 조건명 [CASCADE] ;
ㅇ 제약조건의 활성화{비활성화} : ALTER TABLE 테이블명 ENABLE{DISABLE} CONSTRAINT 제약 조건명;
ㅇ CASCADE 옵션 : 참조하는 CHILD까지도 같이 수정 및 삭제시 활용
ㅇ NOVALIDATE CONSTRAINT 옵션 : 기존에 입력된 데이터에 대해서는 제약 조건을 Check하지 않고
                                향후 수정 혹은 새로 입력되는 데이터에 대해서만 제약 조건을
                                Check하여 수행력 향상
                              : ALTER TABLE 테이블명 ENABLE NOVALIDATE CONSTRAINT 제약 조건명;

☞ TRUNCATE

TRUNCATE TABLE 테이블명 ;

☞ RENAME

RENAME 이전이름 TO 새로운 이름

☞ SQL문장의 종류

1. RETRIEVE(Query) 
   - 데이터 검색 및 조회 SQL 문장
   - SELECT : 원하는 데이터를 검색하기 위해 사용되는 SQL 문

2. DML (Data Manipulation Language )
   - 데이터 조작어
   - 종류 : INSERT / UPDATE / DELETE / MERGE
   - 정의 : 새로운 행을  입력하거나, 기존의 행을 수정하거나 원치않는 데이터를 삭제하는 등
            데이터 조작에 관한 명령어

3. DDL (Data Definition Language)
   - 의미 : 데이터 정의어
   - 종류 : CREATE / ALTER / DROP/ RENAME / TRUNCATE / COMMENT / RENAME 등
   - 정의 : 구조를 만든다거나, 구조변경, 삭제 등 데이터 구조에 관한 명령어

4. TCL (Transaction Control Language)
   - 의미 : 트랜잭션 제어어
   - 종류 : COMMIT/ROLLBACK SAVEPOINT 등
   - 정의 : 논리적인 작업의 단위로 DML에 의해 조작된 결과를 다루는 명령어

5. DCL (Data Control Language)
   - 의미 : 데이터 제어어
   - 종류 : GRANT / REVOKE 등
   - 정의 : 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 받는 명령어 

☞ 결과가 월

다음월 

select to_char(add_months(to_date('20020201', 'yyyymmdd'), 1),'yyyymm')
from   dual;

millisecond 보기

select to_char(current_timestamp, 'yyyy.mm.dd HH24:MI:SS-XFF') from dual;

select to_char(sysdate, 'yyyy.mm.dd HH:MI:SS')   from  dual;

         select to_char(sysdate, 'yyyy.mm.dd HH24:MI:SS')   from  dual;

to_date에서 (일자, 'YYYYMMDDHH24MI') 일 경우
00:00 시 ~ 23:59  까지만 가능하다. 즉, 일자에  '200409202400'  으로 하게 되면

      out_return final-->-1850,ORA-01850: hour must be between 0 and 23  오류가 발생한다.

날수/월수/년수 구하기

두날짜 사이의 시간구하기

TRUNC((to_date(종료일자,'YYYYMMDDHH24MI')
     - to_date(시작일자,'YYYYMMDDHH24MI')) * 24, 2);

두날짜 사이의 월수구하기

MONTHS_BETWEEN((TO_DATE('종료일자') + 1,
                TO_DATE('시작일자')));

년수 구하기
       

TRUNC(MONTHS_BETWEEN(TO_DATE('종료일자') + 1,                      TO_DATE('시작일자')) / 12, 0)

타입 변경

문자 타입을 --> 날짜 타입으로 변경 : TO_DATE(문자형 날짜, '형태')
                                                             (형태 예, 'YY/M/DD')

날자 타입을 --> 문자 타입으로 변환  : TO_CHAR(sysdate, 'DD/MM/YY')

☞ 일 / 월 / 년  올림  (참고, 절사일 경우는 TRUNCATE 로 하면 된다.)

12시 이후면 올림 :  SELECT ROUND(SYSDATE, 'DAY') FROM DUAL ;

15일 이후면 올림 :  SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL ;

6월 이후면 올림  :  SELECT ROUND(SYSDATE, 'YEAR') FROM DUAL ;

기타

요일 구하기  :  SELECT LAST_DAY(DATE) FROM DUAL ;

☞ 시한편

(12) 기타

메쏘드명          설명
========================
daysBetween    날짜간 일수구하기
addDays        일수 더하기
whichDay       요일구하기
monthsBetween  날짜간 월수구하기
addMonths      월수 더하기
lastDayOfMonth 그 달의 마지막날짜 구하기
addYears       년수 더하기

☞ Analytic 함수 List  

 1) AVG* 함수
 2) CORR* CORR* 함수
 3) COUNT* 함수
 4) COVAR_SAMP 함수
 5) CUME_DIST 함수
 6) DENSE_RANK 함수
 7) FIRST 함수
 8) FIRST_VALUE 함수
 9) LAG 함수
 10) LAST_VALUE 함수
 11) LEAD 함수
 12) NTILE 함수
 13) RATIO_TO_REPORT 함수
 14) ROW_NUMBER 함수

☞ LAG 함수

♧ 정의 : self join하지 않고 하나의 테이블에서 동시에 한 행(row)이상을 접근하여 자료를 가져
          올 수 있다. 즉, n개의 Record를 조회할 때 현재 Record와 이전Record의 값을 한Record로
          보여줄 수 있다.
          - 반대로 하위 행을 찾는 함수는 LEAD 함수 이다.

【Version】 ORACLE 8.1.6

【형식】LAG(value_expr [,offset] [,default] ) OVER
           (PARTITION BY [partition_clause] ORDER BY [order_by_clause] )

        . value_expr       : 이전,이후 레코드의 찾고자하는 컬럼. 반드시 컬럼 한 개만 필요
        . order_by_clause  : 이전,이후레코드를 알기 위한 정렬
        . offset           : 1 - 이전 첫 번째 레코드를 찾아옴
                             2 - 이전 두 번째 레코드를 찾아옴
                             즉, 해당 offset 숫자만큼 이전 레코드를 찾는다. 반드시 양수임
        . default          : ??
        . partition_clause : Group by 와 같은 의미 

【예제】SQL> SELECT name,salary,LAG(salary,1,0)  OVER (ORDER BY salary)
             FROM employees;

【결과】 NAME           SALARY    LAG(SALARY,1,0)OVER(ORDERBYSALARY)
         ---------- ----------    ----------------------------------
         jijoe             220                                  0
         Joe               240                                220
         Cho               250                                240
         kim               250                                250  

☞ NVL

1. NULL값을 특정한 값으로 전환
2. 사용예) SELECT  NVL('', 'NULL') FROM DUAL;
     결과) NULL    

☞ NULLIF

1. 정의 : 두값을 비교하여 같으면 NULL을 돌려주고, 다르면 첫 번째 인자값을 돌려주는 함수
2. 사용예) SELECT  NULLIF('kim', 'YONG') FROM DUAL;
     결과)  kim
   사용예) SELECT  NULLIF('kim', 'kim') FROM DUAL;
     결과)  

☞ COALESCE  

1. 정의 : NVL함수를 일반화한 것이다.
          여러 행중에서 첫 번째인자가 NULL이 아니면 첫 번째값을
                        첫 번째인자가 NULL이면      두 번째값을
                        첫 번째, 두 번째인자 모두 NULL이면 세 번째 값을 돌려주는 함수
2. 형식 : COALESCE(첫번째값, 두번째값, 세번째값)  
3.   문제) 사원급여가 존재시 사원급여를,  사원급여가 null이면 기본급을, 둘다 null이면 100만원을
           RETURN시켜라  
   사용예) SELECT  COALESCE(PAY_MON, BASE_MON, 1000000) FROM 급여_TABLE ;
     결과)  
4. 참고) COALESCE(expr1,expr2,expr3,…)
       = CASE WHEN expr1 IS NOT NULL
              THEN expr1
              ELSE COALESCE(expr2,expr3,…)
         END;

☞ DECODE

1. 정의 : 값을 =(equal)로 비교하여 해당값하고 같을 경우 해당값을 RETURN
2. 사용예) SELECT  DECODE('KIM', 'KIM', '성씨', '이름') FROM DUAL;
     결과) 성씨    

☞ CASE

1. 정의 : 값을 =(equal), LIKE, <> 등 비교 연산을 하여 치환값을 RETURN
2. 사용예) SELECT  CASE WHEN  직위  like '%부장%' THEN 기본급 * 1.5%
                        WHEN  직위  like '%차장%' THEN 기본급 * 1.3%
                        WHEN  직위  like '%과장%' THEN 기본급 * 1.2%
                        ELSE  기본급
           FROM 급여_TABLE ;
     결과) 

☞ RANK() OVER

1. 정의 : 순위를 구하는 함수
2. 형식1: RANK() OVER  (ORDER BY 컬럼1 ) 
          - 컬럼1을 기준으로 ORDER BY 후 순위가 부여된다.
   형식2: RANK() OVER  ([PARTITION BY 컬럼1] ORDER BY 컬럼2) 
          - 컬럼1을 기준으로 GROUP 하고 컬럼2로 ORDER BY 후 순위가 부여 된다.
            그래서 컬럼1별로 순위가 부여 된다.
            즉, 만약, 부서별로 급여 순위를 부여하고 싶을 경우 아래와 같다.
3. 사용예) SELECT  RANK() OVER (PARTITION BY 부서 ORDER BY 급여액 DESC)
           FROM    급여_TABLE ;
     결과)  

☞ 

1.

☞ 전각을 반각으로 변환

1. 정의 : HOST에서 사용하는 전각문자를 WIN에서 사용하는 반각문자로 변환
2. 형식 : TO_SINGLE_BYTE()

☞ 반각을 전각으로 변환

1. 정의 : HOST에서 사용하는 전각문자를 WIN에서 사용하는 반각문자로 변환
2. 형식 : TO_MULTI_BYTE()

☞

내용 입력