자격증/SQLD

SQLD (4)

사탕맛사과 2023. 4. 4. 22:20

▶내장형 함수 (BUILT-IN-Function)
-형변환 함수,  문자열 함수,  숫자형 함수,  날짜형 함수

→명시적(Explicit) / 암시적 (Implicit)  형변환
-형변환
        두 개의 데이터의 데이터 타입이 일치하도록 변환하는 것

-명시적 형변환
        -함수를 사용해서 데이터 타입을 일치시킴
        -개발자가 SQL을 사용할 때 사용

        →INDEX 사용할려면 명시적 형변환 사용
     ex)
        SELECT *
        FROM EMP
        WHERE EMPNO = TO_NUMBER('100');

-암시적 형변환
        -데이터베스 관리 시스템이 자동으로 함 (내부적으로 형변환 수행)
      ex)
        SELECT *
        FROM EMP
        WHERE EMPNO = '100';        // 100 : 암시적 형변환


▶내장형 함수 (BUILT-IN-Function)
-형변환 함수,  문자열 함수,  숫자형 함수,  날짜형 함수

*형변환 함수
-TO_NUMBER (문자열)
        문자열 → 숫자

-TO_CHAR (숫자 or 날짜, [FORMAT])
        숫자 or 날짜 → FORMAT의 문자로

-TO_DATE (문자열, FORMAT)
        문자열 → FORMAT의 날짜형으로 YYYYMMDD

*인덱스 칼럼에 형변환을 수행하면 인덱스 사용 x
-인덱스는 기본적으로 변형이라는 것이 발생하면 인덱스 사용 x
  ∴ 인덱스 칼럼에 형변환이 발생하면 인덱스 사용 x

*문자형 함수
-ASCII (문자)
        문자 or 숫자 → ASCII 코드값으로 변환

-CHAR ( ASCII 코드값)
        ASCII 코드값 → 문자

-SUBSTR (문자열, m, n)
        문자열 m번째 위치부터 n개를 자름

-CONCAT (문자열1, 문자열2)
        -문자열 1과 2를 결합
        -oracle은 ||, MS-SQL은 +

-LOWER (문자열)
        영문자 소문자로

-UPPER (문자열)
        영문자 대문자로

-LENGTH or LEN (문자열)
        공백 포함 문자열 길이

-LTRIM (문자열, 지정문자)
        왼쪽에서 지정문자 삭제 (공백도 삭제) → 지정문자 삭제 후 생긴 공백

-RTRIM (문자열, 지정문자)
        오른쪽에서 지정된 문자 삭제 (공백도 삭제)

-TRIM (문자열, 지정문자)
        왼, 오에서 지정된 문자 삭제 (공백도 삭제)

*숫자형 함수
-ABS (숫자)
        절댓값

-SIGN (숫자)
        양(1),  0,  음(-1)  구별

-MOD (숫자1, 숫자2)
        1 ÷ 2 의 나머지 (% 사용 o)

-CEIL /  CEILING (숫자)
        숫자보다 작거나 같은 최대 정수

-ROUND (숫자, m)        // m : 기본값 o
        소수점 m 자리에서 반올림

-TRUNC (숫자, m)        // m : 기본값 o
        소수점 m 자리에서 절삭

*날짜형 함수
-SYSDATE
        오늘 날짜를 날짜 타입으로 알려주무

-EXTRACT ('날짜요소' from 컬럼)
        날짜 정보추출
        날짜요소 (year, month, day, hour, minute, second)

SELECT SYSDATE,
        EXTRACT(YEAR FROM SYSDATE),
        EXTRACT(YEAR FROM SYSDATE)
FROM DUAL;

SYSDATE EXTRACT(YEAR FROM SYSDATE) EXTRACT(YEAR FROM SYSDATE)
18/06/26 2018 20180626

→DUAL 테이블
-Oracle엔 기본적으로 DUAL 테이블이라는 Dumm 테이블이 존재 o
-임시로 사용자가 사용할 수 o 테이블로 내장형 함수 실행 시도 사용 o
-Oracle 모든 사용자가 사용 o

▶DECODE
DECODE (컬럼, 숫자, 'TRUE', 'FALSE')
        컬럼 = 숫자  면  TRUE
                ≠           면 FALSE

▶CASE
-조건문처럼 사용 o
-WHERE 구 조건, THEN은 참, ELSE는 거짓

CASE
        WHEN  조건문  THEN  ~
         . . .
        ELSE ~
END (AS ~ )

▶ROWNUM
-최종 결과집합에 대한 순번
-최종적인 결과집합에 부여되는 가상의 일렬번호
-논리적인 일렬번호 부여
-조회되는 행 수를 제한 할 때 많이 사용
-데이터 출력 시 부여되는 논리적 순번
-if) 페이지 단위 출력 시 인라인 뷰 사용 o

-(오라클) SELECT  *  FROM  테이블명
                WHERE  ROWNUM <= 10;

-(SQL Server) SELECT  TOP(10)  FROM  테이블명;

-(MY SQL) SELECT * FROM  테이블명  LIMIT  10;

ex)
  SELECT  *
  FROM  ( SELECT  ROWNUM  list,  ENAME        // list : 별칭
                 FROM  EMP)
  WHERE  list  BETWEEN  5  AND  10;                →특정 행만 조회

▶ROWID
-ORACLE 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값
-SELECT 문으로 확인 o
-데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 o

*ROWID 구조

오브젝트 번호 1 ~ 6 오브젝트 별로 유일한 값을
가지고 있으며 해당 오브젝트가
속해 있는 값
상대 파일 번호 7 ~ 9 테이블 스페이스에 속해 있는
데이터 파일에 대한
상대 파일 번호
블록 번호 10 ~ 15 데이터 파일 내부에서 어느
블록에 데이터가 있는지 알려줌
데이터 번호 16 ~ 18 데이터 블록에 데이터가
저장되어 있는 순서를 의미


SELECT  ROWID  칼럼명
FROM  테이블명;

*모든 테이블은 rowid를 가지고 o

▶WITH 구문
-서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 o 구문
-서브쿼리 블록에 별칭 지정 o
-옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단

WITH  테이블명  AS
        (SELECT  칼럼  FROM  테이블 ...)        // 서브쿼리 사용해서 임시 테이블 만듬
SELECT  칼럼  FROM  테이블  ~  ;

▶GRANT (DCL)
-데이터베이스 사용자에 권한을 부여
-데이터베이스 사용을 위해서는 권한이 필요

GRANT  권한(privileges)  ON  테이블명  TO  사용자 지정(user);

*Privilegees (권한)
SELECT        지정된 테이블에 SELECT 권한 부여
INSERT         insert 권한 부여
UPDATE        update 권한 부여
DELETE        delete  권한 부여
REFERENCES        참조 제약 조건 생성 권한 부여
ALTER           수정 할 수 o, 권한 부여
INDEX            인덱스 생성 o, 권한 부여
ALL                 테이블 모든 권한 부여

*WITH GRANT OPTION
-WITH GRANT OPTION
      -특정 사용자에게 권한을 부여할 수 o 권한 부여
      -권한을 A가 → B에게 B가 → C에게 부여했을 때, A가 권한을 취소 (Revoke)하면 모든 권한 회수
                A → B,  B → C
                A → x
                A  o,  B  x, C  x
-WITH ADMIN OPTION
     -테이블에 대한 모든 권한을 부여
    -권한을 A가 → B에게 B가 → C에게 부여했을 때, A가 권한을 취소 (Revoke)하면 B만 권한 취소
                A → B,  B → C
                A → x
                A  o,  B  x, C  o

GRANT  권한(privileges)  ON  테이블명  TO  사용자(user)  WITH GRANT OPTION;
        // WITH GRANT OPTION : 권한을 부여할 수 o 권한


▶REVOkE (DCL)
-데이터베이스 사용자에게 부여된 권한을 회수함

REVOKE  권한  ON  테이블명
FROM  사용자;

REVOKE  권한  FROM  테이블;

▶COMMIT (TCL)
-insert, update, delete 문으로 변경한 데이터를 데이터베이스에 반영
-변경 전 데이터는 잃어버림
-다른 모든 데이터베이스 사용자는 변경된 데이터를 볼 수 o
-Commit이 완료되면 데이터베이스 변경으로 인한 LOCK이 해제 된 (unLock)
-Commit이 완료되면 다른 모든 DB 사용자는 변경된 데이터를 조작 할 수 o
-Commit을 실행하면 하나의 트랜잭션 관정을 종료 함

-COMMIT;
-SET AUTOCOMMIT ON;
        └ SQL PLUS 프로그램이 정상적으로 종료되면 자동 commit이 됨
        └ DDL 및 DCL을 사용하는 경우 자동 commit 됨

▶ROLLBACK (TCL)
-rollback 실행 시 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료
-Insert, Update, Delete 문의 작업을 모두 취소
-단, 이전에 commit 한 곳까지만 복구
-rollback을 실행하면 Lock이 해제되고, 다른 사용자도 데이터베이스 행을 조작 o

ROLLBACK;

▶SAVEPOINT (TCL)
-트랜잭션을 작게 분할하여 관리하는 것으로 savepoint 사용하면 지정된 위치 이후의 트랜잭션만 rollback o
-rollback을 실행하면 savepoint와 관계없이 데이터의 모든 변경사항을 저장하지 x

SAVEPOINT  세이브포인트 명;
ROLLBACK TO  세이브포인트 명;

저작자표시 (새창열림)