오라클 총정리

오라클 총정리

2. Oracle

    DML:

        1) SELECT

            연산자

                = IS NULL, IS NOT NULL (NULL 연산자. null인 값은 인덱스를 만들지 않는게 좋다)

                = BETWEEN ~ AND ( >= and <= )

                = LIKE (% , _ )

                = IN => OR

            내장함수

                = 문자열, SUBSTR, INSTR, RPAD, LENGTH

                = 숫자: MOD, ROUND, TRUNC

                = 날짜: SYSDATE, MONTHS_BETWEEN

                = 변환: TO_CHAR

                = 기타: NVL

                = 그룹: COUNT, MAX, MIN, AVG, SUM

            JOIN

                INNER JOIN

                = EQUI JOIN

                    (NATURAL JOIN, JOIN~USING)

                = NON-EQUI JOIN

                OUTER JOIN

                = LEFT OUTER JOIN

                = RIGHT OUTER JOIN

            SubQuery

                = 단일행 main-query = (subquery)

                = 다중행 IN, ANY, ALL => MAX, MIN

        2) INSERT

        3) UPDATE

        4) DELETE

   DDL: CREATE, DROP, ALTER(MODIFY, DROP, ADD)

   DCL: GRANT, REVOKE

   TCL: COMMIT, ROLLBACK

VIEW: 보안, 쿼리문장을 단순화

         => INLINE VIEW

SUBQUERY를 쓰는 위치: SELECT (SELECT ~),

                                    FROM (SELECT)

                                 => 서브쿼리가 Column이나 Table을 대체할 수 있다.

SEQUENCE => 자동 증가번호

PROCEDURE => SQL 중복, SQL 문장을 감출 경우

TRIGGER

제약조건

    = PRIMARY KEY : 중복비허용, NULL비허용 (UNIQUE+NOT NULL)
    = FOREIGN KEY

    = UNIQUE : 중복비허용, NULL허용

    = CHECK

    = NOT NULL => DEFAULT