[개미의 걸음 SQLD 2과목] SQL내장함수① 단일행(문자열, 숫자형, 날짜형, 형변환, NULL)함수

함수[Function] 

내장함수[표준함수]	SQL에서 기본적으로 내장하고 있어 표준으로 제공하는 함수
사용자 정의함수	SQL에서 사용자가 임의로 만들어서 사용하는 함수

 

내장 함수[BUILT-IN Function]

SQL에서 기본적으로 내장하고 있어 표준으로 제공하는 함수

• DBMS 벤더별로 약간의 차이가 있지만 거의 비슷한 방법으로 사용 가능
• 단일행 함수(Single-Row Function)과 다중행 함수(Multi-Row Function)이 있음
      단일행 함수 : 단일행 값이 입력되는 함수[문자형, 숫자형, 날짜형, 변환형, NULL관련 함수 등]
      다중행 함수 : 다중행 값이 입력되는 함수[집계 함수, 그룹함수, 윈도우 함수 등]

 

단일행 함수

단일행 값이 입력되는 함수[문자형, 숫자형, 날짜형, 변환형, NULL관련 함수 등]

1. SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용 가능

2. 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작 [각각의 행에 대한 조작 결과를 리턴]         → 1:M 조인 시, M쪽에서 출력된 행들이 하나씩 단일행 함수의 입력값으로 사용됨

3. 여러 인자(Arguments)를 입력해도 단 하나의 결과만 리턴

4. 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능         → 하나의 인자를 가지는 경우도 있지만 여러 개의 인자를 가질 수 있음         →단, 여러 개의 인자를 입력해도 단 하나의 결과만 리턴!

5. 특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능

 

단일행 함수1. 문자열 함수

문자를 입력하면 문자나 숫자 값을 반환하는 함수

• 함수를 중첩해서 사용 가능

문자열 함수	함수 설명
ASCⅡ(문자)	문자나 숫자를 ASCⅡ코드번호로 변환     → 대문자 A를 기준
CHR/CHAR(ASCⅡ번호)	ASCⅡ 코드 번호를 문자나 숫자로 변환
LOWER(문자열)	문자열의 알파벳 문자를 소문자로 변환
UPPER(문자열)	문자열의 알파벳 문자를 대문자로 변환
CONCAT(문자열1, 문자열2)	문자열1과 문자열2를 결합     → Oracle, My SQL에서 유효한 함수     → "| |"(Oracle)나 "+"(SQL Server)를 사용하기도 함
SUBSTR/SUBSTRING (문자열, m, n)	문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 변환     → n 생략 시 마지막 문자까지     → 공백도 count하여 위치 계산     → m에 음수를 사용하면 문자열의 마지막에서부터 m위치부터 n개 문자 길이를 반환
LENGTH/LEN(문자열)	문자열의 길이를 숫자값으로 변환     → 공백이나 줄바꿈도 count!!!     → REPLACE(칼럼명, CHR(길이))를 사용하면 줄바꿈이 제거됨
LTRIM(문자열, 지정문자)	문자열의 왼쪽부터 확인해서 지정 문자가 나타나면 해당 문자 제거     → 지정 문자 생략시 공백 값이 Default     → SQL Server에서는 LTRIM 함수에 지정문자 사용 불가[공백만 제거]
RTRIM(문자열, 지정문자)	문자열의 오른쪽부터 확인해서 지정 문자가 나타나면 해당 문자 제거     → 지정 문자 생략시 공백 값이 Default     → SQL Server에서는 RTRIM 함수에 지정문자 사용 불가[공백만 제거]
TRIM( [leading | trailing | both ] 지정문자 FROM 문자열)	문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정문자 제거     → [leading | trailing | both ] 생략시 both 값이 Default     → SQL Server에서는 TRIM 함수에 지정문자 사용 불가[공백만 제거]

※ Oracle함수/SQL Serve함수  ["/"가 없는 것은 공통함수]

 

단일행 함수2. 숫자형 함수

숫자를 입력하면 숫자 값을 반환하는 함수

숫자형 함수	함수 설명
ABS(숫자)	숫자의 절대값 반환
SIGN(숫자)	숫자를 양수,음수,0으로 구분하여 반환
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지를 반환   → %연산자로 대체 가능
CEIL/CEILING(숫자)	숫자보다 크거나 같은 최소 정수 반환
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수 반환
ROUND(숫자, m)	숫자를 소수점 m자리에서 반올림하여 반환     → m 생략 시, Default값은 0
TRUNC(숫자, m)	숫자를 소수점 m자리에서 잘라서 버림     → m 생략 시, Default값은 0     → SQL Server에서 TRUNC 함수는 제공되지 않음
SIN, COS, TAN .....	숫자의 삼감함수 값을 반환
EXP(), POWER(), SQRT(), LOG(), LN()	숫자의 지수, 거듭제곱, 제곱근, 자연로그 값을 리턴

※ Oracle함수/SQL Serve함수  ["/"가 없는 것은 공통함수]

 

 

단일행 함수3. 날짜형 함수

날짜 데이터 타입의 값을 연산하는 함수

날짜형 함수	함수 설명
STSDATE/GETDATE()	현재 날짜와 시각을 반환
EXTRACT('YEAR' | 'MONTH' | 'DAY' from d)/ DATEPART('YEAR' | 'MONTH' | 'DAY' ,d)	날짜 데이터에서 년/월/일 데이터를 반환 (시간/분/초도 가능) ex1> EXTRACT(MONTH FROM DATE '2020-12-20') → 12이 출력 ex2> EXTRACT(MONTH FROM DATE '2020-01-01') → 01이 아닌 1이 출력
TO_NUMBER(TO_CHAR(d,'YYYY'))/YEAR(d)	날짜 데이터에서 "년" 데이터를 반환[d는 날짜 데이터]
TO_NUMBER(TO_CHAR(d,'MM'))/MONTH(d)	날짜 데이터에서 "월" 데이터를 반환[d는 날짜 데이터]
TO_NUMBER(TO_CHAR(d,'DD'))/DAY(d)	날짜 데이터에서 "일" 데이터를 반환[d는 날짜 데이터]

※ Oracle함수/SQL Serve함수  ["/"가 없는 것은 공통함수]

※ "특정날짜 +1"은 하루를 더한다는 의미
        → 1/24/60은 24시간으로 나누고 60분으로 나눴으므로 1분을 의미
        → 1/24/(60/10)은 괄호연산부터 먼저하면 1/24/6과 동일하므로 10분을 의미

 

단일행 함수4. 형변환 함수

두 개의 데이터의 데이터 타입이 일치하도록 변환하는 작업

• 명시적[Explicit] 형변환과 암시적[Implicit] 형변환이 있음
      → 명시적 형변환 : 개발자가 형변환 함수를 사용해서 데이터 타입을 일치시키는 것
          암시적 형변환 : 개발자가 형변환을 하지 않은 경우, DBMS에서 자동으로 데이터 타입을 일치시키는 것

형변환 함수	설    명
TO_NUMBER(문자)	문자 데이터 타입을 숫자 데이터 타입으로 변환
TO_CHAR(숫자 혹은 날짜, [format])	숫자 혹은 날짜 데이터 타입을 지정된 format의 문자 데이터 타입으로 변환
TO_DATE(문자열, format)	문자 데이터 타입을 지정된 format의 날짜 데이터 타입으로 변환

 

단일행 함수5. NULL관련 함수

NVL / ISNULL 함수	NULL값을 다른 값으로 바꾸는 함수 표현식1의 결과값이 null이면 표현식 2의 값을 출력     ex1> NVL(표현식1, 표현식2)  →  NULL이면 표현식2로 반환     ex2> ISNULL( 표현식1, 표현식2)
NVL2 함수	NVL함수와 DECODE함수를 하나로 만든 함수     ex> NVL2(칼럼명,표현식1,표현식2)                →  NULL이 아니면 표현식1, NULL이면 표현식2을 반환
NULLIF 함수	두 개의 값이 같으면 NULL을, 같지 않으면 첫 번째 값을 반환     ex> NULLIF(value1, value2)  →  value1과 value2가 같으면 NULL을 같지 않으면 VALUE1 반환
COALESCE 함수	NULL이 아닌 최초의 인자 값을 반환     ex> COALESCE(value1, value2, value3, ...)  →  value1부터 NULL값이 아닌 최초의 인자를 반환

※ Oracle함수/SQL Serve함수  ["/"가 없는 것은 공통함수]

※ NULL은 다른 값과 산술 연산시, NULL이 출력됨

※ 데이터베이스에서 NULL은 등호(=)로 표현 불가!!!
          → IS NULL과 IS NOT NULL로 비교

IS NULL	특정 열 또는 연산의 결과 값이 NULL인 데이터만 조회	COMM IS NULL
IS NOT NULL	특정 열 또는 연산의 결과 값이 NULL이 아닌 데이터만 조회	COMM IS NOT NULL

 

NULL

NULL은 값이 0이라는 의미가 아니라 데이터의 값이 완전히 '비어있는' 상태를 말함!

NULL의 의미

값이 존재하지 않음

해당 사항 없음

노출할 수 없는 값

확정되지 않은 값

• ORACLE에서 "INSERT INTO 테이블명 VALUES ('777','');"입력시 ''는 NULL로 입력됨
      → "SELECT * FROM 테이블명 WHERE 칼럼명2 IS NULL;"로 입력해야 조회됨
• SQL SERVER에서 "INSERT INTO 테이블명 VALUES ('777','');"입력시 ''는 공집합으로 입력됨
      → "SELECT * FROM 테이블명 WHERE 칼럼명2 ='';"로 입력해야 조회됨[''로 조회]

# AND, OR 연산에서 NULL값

AND 연산	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

OR 연산	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL