업무에 바로 쓰는 SQL 활용 실습 - 2일차

<요청으로 파일 삭제>

실습파일

SQL 초기설정 DDL

 

<문자함수 - 1>

LOWER - 문자열을 소문자로 변환

UPPER - 문자열을 대문자로 변환

INITCAP - 문자 열중 앞에 있는 첫 문자만을 대문자로 변환하고 나머지는 소문자

CONCAT - 문자열 이어 붙이는 함수('||' 쓰는 게 더 낫다)

SUBSTR - 문자열 중 지정한 위치에서 지정한 길이만큼의 문자열 추출

LENGTH - 문자열의 길이 출력

LENGTHB - 문자열의 BYTE수 출력

INSTR(A, B, 1, 1) - A문자열에서 B문자열을 찾는 함수, 첫 번째 자리부터 첫 번째로 나오는 B를 찾음

 

영어는 한 글자당 1BYTE를 차지한다, 하지만 한글은 2BYTE 또는 3BYTE로 차지하는 Multi Byte라고 한다.

DB에 어떤 Setting을 했느냐에 따라서 2BYTE 또는 3BYTE로 읽어지게 된다.

 

한글 BYTE SETTING 확인하기

SELECT parameter, value

FROM nls_database_parameters

WHERE parameter = 'NLS_CHARACTERSET';

 

VALUES

KO16KSC5601, KO16MSWIN949 -> 2BYTE

AL32UTF8 -> 3BYTE

 

▲2BYTE로 환경설정이 되어 있는 경우에는 위와 같이 BYTE로 자르면 '기'가 나온다.

 

▲2BYTE 기준에서는 위와 같이 자음 모음 1BYTE씩 차지하게 된다. 그래서 5번째 BYTE부터 3BYTE를 출력하면 '기'가 나오게 된다.

 

 

<문자함수 - 2>

LPAD - 입력되어 있는 문자열을 제외한 왼쪽 공간을 지정한 문자로 채움

RPAD - 입력되어 있는 문자열을 제외한 오른쪽 공간을 지정한 문자로 채움

LTRIM - 대상 문자열의 왼쪽부터 지정한 문자들을 제거

RTRIM - 대상 문자열의 오른쪽부터 지정한 문자들을 제거

TRIM - 앞, 뒤에 나오는 특정 문자를 제거

(TRIM 함수 앞에는 LEADING(=LTRIM), TRAILING(=RTRIM), BOTH(기본)을 사용할 수도 있다)

TRANSLATE(A, B, C) - A문자열에서 B문자열을 찾아서 C로 바꿈
ex) TRANSLATE('FISH AND CHIPS' , 'ISH', 'is*') -> Fis* AND C*IPs

ex) TRANSLATE('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') == LOWER 함수

(TRANSLATE는 1대1 변환으로 위에서 I는 i로, S는 s로, H는 *로 바뀌는 것)

REPLACE - 특정 문자열을 다른 문자열로 대체

ex) REPLACE('JACK and JUE', 'JA', 'BLA') -> BLACK and JUE

 

▲PAD함수는 한글을 2BYTE로 처리. '대한'(4BYTE)을 제외한 나머지 6BYTE를 '*'로 채우게 된다.

 

<숫자 함수>

ROUND(N, M) - N이라는 숫자에서 M으로 지정된 자리로 반올림 수행

ex) ROUND(121.788) = 122, ROUND(121.788, 2) = 121.79, ROUND(121.788, -2) = 100

TRUNC(N, M) - N이라는 숫자에서 M으로 지정된 자리에서 버림 수행

ex) TRUNC(121.788) = 121, TRUNC(121.788, 2) = 121.78, TRUNC(121.788, -2) = 100

MOD(N, M) - N을 M으로 나눈 나머지(C언어에서 %와 같다)

ABS - 절대값

FLOOR(N) - 바닥 함수

ex) FLOOR(4.2) = 4, FLOOR(4.9) = 4

CEILING(N) - 천장 함수

ex) CEILING(4.2) = 5, CEILING(4) = 4

POWER(N, M) - N^M

SQRT(N) - 루트 N

SIGN - 숫자가 양수이면 1, 음수이면 -1, 0이면 0

(단순 부호를 가져오기보다는 대소 관계를 파악하기 위해서 자주 쓰임)

ASCII(C) - C문자의 ASCII값을 반환하게 된다.(문자 하나만 리턴한다)

ex) ASCII('A') -> 65, ASCII('ABC') -> 65('ABC'를 넣어도 'A'의 아스키코드 값만을 리턴한다)

 

 

<날짜 함수 - 1>

날짜 데이터에도 산술 연산을 할 수 있다.

DATE + NUMBER - 날짜에 일수를 더함

DATE - NUMBER - 날짜에 일수를 뺌

DATE1 - DATE2 - 두 날짜 사이의 경과 일수

DATE + NUMBER/24 - 날짜에 해당 시간을 더함

DATE + NUMBER/24/60 - 날자에 해당 분을 더함

 

1) DATE형 - 세기, 년, 월, 일시, 시 , 분, 초(대표 함수 sysdate)

2) TIMESTAMP형 - DATE형에 밀리세컨드가 추가됨(대표 함수 localtimestamp)

3) TIMESTAMP WITH TIMEZONE형 - TIMESTAMP형에 TIME ZONE(지역대 시간)이 추가됨(systimestamp)

 

▲위와 같이 날짜형 데이터를 출력해보았다

sysdate에서 시간, 분, 초도 보고 싶다 하면 현재 세션에서의 NLS_DATE_FORMAT을 변경해주면 된다.

(사용 중인 세션에서만 바꾸는 것이기에 다른 사용자가 조회 시에는 변화가 없다)

 

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

위와 같은 명령어를 쓰면 날짜 데이터의 포맷을 변경할 수 있다.

 

▲날짜 데이터의 포맷을 바꾸고 조회 했을때의 결과

날짜 데이터로부터 데이터를 추출하는 EXTRACT 함수가 존재하는데 EXTRACT 함수의 경우에는 년, 월, 일 밖에 추출이 안된다. EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), EXTRACT(DAY FORM SYSDATE)

그래서 시간을 추출하기 위해서는 TO_CHAR(SYSDATE, 'HH24')를 활용할 수 있다.

 

 

<날짜 함수 - 2>

ADD_MONTH(D, N) - D날짜에 N개월을 더함
MONTHS_BETWEEN(D1, D2) - D1, D2 날짜 간의 경과 개월 수

NEXT_DAY(D, N) - D날짜부터 N으로 명시된 요일에 해당되는 첫 번째 일자를 반환한다.

(N: 1 - 일요일, 2 - 월요일 ... 7 - 토요일)

LAST_DAY(D) - 해당 날짜의 마지막 일자를 반환

ROUND(D, FORMAT) - D날짜를 FORMAT 기준으로 반올림

TRUNC(D, FORMAT) - D날짜를 FORMAT 기준으로 버림

<FORMAT>

YEAR - 7월부터, Q - 두 번째 달의 16일부터, MONTH - 16일부터, DD - 오후 12시부터, HH - 30분부터, MI - 30초부터

 

 

▲TRUNC는 시,분,초를 그냥 날리면 된다. ROUND는 유의해야할것은 12:00가 되면 다음날로 넘어간다.

 

 

<변환 함수>

TO_CHAR : NUMBER, DATE -> CHAR

TO_NUMBER : CHAR -> NUMBER

TO_DATE : CHAR -> DATE

 

문제

a. char

b. number

a, b 칼럼 모두 각각 index 생성되어있음

다음 문장 중에 index를 타지 못함으로써 성능이 저하되는 문장은?

1) WHERE a = 10

2) WHERE a = '10'

3) WHERE b = 10

4) WHERE b = '10'

 

모든 NUMBER형은 CHAR가 될 수 있지만, 모든 CHAR가 NUMBER가 될 수는 없다.

그렇기 때문에 CHAR가 NUMBER보다 우선순위를 갖게 된다.

그래서 NUMBER = CHAR가 있다면 CHAR를 NUMBER로 바꾸는 작업이 우선으로 진행된다.

그렇기 때문에 아래와 같이 CHAR형을 NUMBER로 먼저 형 변환시켜주게 된다.

1) to_number(a) = 10 (V)

4) b = to_number('10') (X)

 

그리고 1일 차 수업에서 배운 것처럼 INDEX를 타기 위해서는 칼럼을 조작하면 안 된다.

그렇기 때문에 INDEX를 못 타는 1번이 가장 성능이 저하되는 문장이다.

 

▲TO_DATE에서 3번째 인자는 주로 생략하는데 '03-5월-20', '03-MAY-20' 와 같이 한글, 또는 영문으로 들어올 경우 NLS_DATE_LANGUAGE 환경설정도 생각해서 넣어주면 오류를 방지 할 수 있다.

 

 

▲MONTH를 출력해줄때 D1을 보면 뒤에 공백이 들어간다. 이것을 지워주기 위해서는 fm을 앞에 붙여서 쓰면 지울 수 있다.

 

▲2자리로 출력시 RR과 YY의 차이 결과

RR과 YY의 차이점

RR의 경우 (00~49 - 상반기 / 50~99 - 하반기)로 판단을 하게 된다.

기준이 되는 현재 연도(2020)는 상반기에 속해있는 것이기 때문에 두 자리의 연도를 RR로 받게 되면 상반기로 판단하여 1999년도로 출력이 된다.

 

<추가적인 날짜 함수>

TO_TIMESTAMP: 지정한 문자를 timestamp 날짜 데이터 유형으로 변환

예) TO_TIMESTAMP('10-09-77 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF') = 77/09/10 14:10:10.123000000

TO_DSINTERVAL: 문자열을 INTERVAL DAY TO SECOND 값으로 변환

예) TO_DATE('2020-06-23 14:00:00', 'RRRR-MM-DD HH24:MI:SS') + TO_DSINTERVAL('003 2:30:30') = 2020-06-26 16:30:30 (즉, 003 - 3일, 2:30:30 - 2시간 30분 30초를 기준 날짜에 더한 값)

TO_YMINTERVAL: 문자열을 INTERVAL YEAR TO MONTH 값으로 변환

예) TO_YMINTERVAL('01-06') = 1년 하고 6개월을 뜻한다.

 

 

<일반 함수>

NVL(N, M) - N에 입력된 값이 NULL일 경우 M으로 바뀐다.

NVL2(N, M, K) - N에 입력된 값이 NULL이 아니면 M, NULL이면 K반환

NULLIF(N, M) - N과 M이 같으면 NULL을 반환, 아니면 N을 반환

COALESCE(N, N1, ...M) - N부터 M중에서 첫 번째로 NULL이 아닌 값을 반환한다. 모든 값이 NULL이면 NULL 반환

GREATEST(N, N1 ...M) - N부터 M중에서 가장 큰 값을 반환

LEAST(N, N1 ...M) - N부터 M중에서 가장 작은 값을 반환

(GREATEST, LEAST에서 값을 반환하려 할때 NULL이 값에 존재하면 NULL이 반환됨)