제수기 > SQL > DML/DQL > 함수 BUILT IN FUNCTIONS

Eunbi Jo·2024년 12월 24일

제수기

목록 보기

11/90

'제수기' - '제발 수업내용을 기억해라'

-- SQL 함수는 반드시 하나의 리턴값을 가진다.
-- api 참조

# 함수의 유형
-- 1. 단일행 함수 : 각 행별로 처리되는 함수
-- - 문자함수
-- - 날짜함수
-- - 숫자함수
-- - 기타함수
-- - 형변환함수
-- 2. 그룹함수 : 각 그룹별로 처리되는 함수

API 링크

문자 함수

문자열 길이

char_length 문자열 길이 반환
length 문자열 byte수 반환

연결 concat, concat_ws

concat('문자', '문자') 이렇게 쓰면 문자문자 이렇게 합쳐서 주는데,
concat_ws(', ', '문자', '문자') 이렇게 쓰면 문자, 문자 이렇게 콤마랑 띄어쓰기까지 중간 중간에 넣어서 반환해준다.

검색(인덱스) instr

instr(대상문자열, 검색문자열) -> 인덱스

숫자형식 format

format
세자리마다 콤마, 소수점 이하 반올림 처리
소수점 넣으면 문자열이 됨. 출력된 결과에서 'menu_price'가 문자열처럼 왼쪽에 배열된 걸 볼 수 있음.

채우기 lpad, rpad

lpad(문자열, 길이, 채울문자) : 왼쪽에 채워줌
rapad(문자열, 길이, 채울문자) : 오른쪽에 채워줌

대체 replace

replace(대상문자열, old문자열, new문자열)

잘라내기 substring

substring(문자열, 시작인덱스, 길이)
길이를 안 주면 잘라내려는 시작 인덱스부터 문자 끝까지 출력해줌

✏️이메일에서 아이디 추출하기 - substring + instr

-💥 메일주소를 문자열 자리에 넣고, 시작인덱스는 1로 두고, instr로 @가 위치한 곳을 길이 자리에 넣었는데 맨 뒤에 -1은 뭐지?

chatGPT 답변

-1의 역할:

-1은 @ 기호가 포함되지 않도록 하기 위해 길이를 조정합니다.
instr로 찾은 위치값은 '@'의 위치를 포함하므로,
이를 제외하기 위해 -1을 적용하여 '@' 이전의 문자열만 잘라냅니다.

✏️이메일에서 아이디 추출하기 -substring_index

substring_index + 기준 삼을 문자 + 시작할 곳 지정
기준 삼을 문자 앞이나 뒤로 다 지우고 출력 (1) (-1)
substring + index 보다 더 간편하게 할 수 있다.

정규표현식 관련

regexp_replace : [^0-9] - 숫자만 뽑아달라. 💥맨 뒤에 '' 역할은?
regexp_substr : [0-9]+ - 잘라내기 💥여기서 + 역할은?
regexp_instr : 인덱스 반환
regexp_like
- ^.{5}$ 5개짜리 문자
- [국밥빵]$ 국or밥or빵으로 끝나는 문자

숫자 함수

ceil
- 올림 함수
- 소수점 자리를 올려서 가장 가까운 정수로 반환
- ceil(1234.56)의 결과는 1235
floor
- 내림 함수
- 소수점 자리를 내려서 가장 가까운 정수로 반환
- 예: floor(1234.56)의 결과는 1234
round
- 기본적으로 소수점 첫째 자리에서 반올림하여 정수로 반환
- 두 번째 인자를 사용하여 반올림할 자릿수를 지정할 수 있다.
- 예: round(1234.56)의 결과는 1235 (소수점 첫째 자리 반올림). round(1234.56, 2)의 결과는 1234.56 (소수점 둘째 자리 반올림, 그대로 유지).
truncate
- 버림 함수
- 지정한 소수점 자리까지만 값을 남기고, 그 뒤를 버린다.
- 예: truncate(1234.56, 1)의 결과는 1234.5 (소수점 첫째 자리까지 유지, 둘째 자리 이하 제거). truncate(1234.56, 0)의 결과는 1234 (정수만 남김).

난수생성함수

rand()
- 0.0 이상 1.0 미만의 실수형 난수 반환.
floor(rand() * n + m)
- 특정 범위의 정수형 난수를 생성하는 공식.
- 공식:
rand() * 경우의 수 + 시작값
floor()를 사용해 소수점을 버리고 정수로 만든다.

- + 1: 시작값을 1로 설정.
cast(rand() * n + m AS signed)
- 정수형으로 변환된 난수.
- CAST를 사용해 특정 데이터 타입으로 변환
- rand() * 10 + 1: 1.0 ~ 11.0 사이의 실수 생성.
- CAST AS signed: 정수형으로 변환 (소수점 제거).

날짜 함수

현재 날짜, 시각

now : 지금 날짜, 시각
curdate : 날짜
curtime : 시각

더하기/빼기 날짜연산함수

addate 더하기
subdate 빼기

기간

interval + 값 + 단위

연산자 사용

+, - 사용 가능

단위별 추출

year
month
dayofmonth
dayofweek : (1 : 일요일 - 7 : 토요일)
hour
minute
second

날짜차이계산

datediff
timediff

날짜형식 date_format

MySQL DATE_FORMAT() Function

날짜 파싱 str -> date

str_to_date

형변환 함수

cast ('날짜/시간' as date/time)

기타 함수

null처리 함수

ifnull (expression, replacement)
NULL일 경우 대체값을 반환
첫 번째 인자가 NULL이면 두 번째 인자를 반환하고, 그렇지 않으면 첫 번째 인자를 반환

테이블에서 NULL 값 처리

category_code가 NULL인 경우 '미분류'로 표시
컬럼의 자료형은 딱 하나만 처리한다. 숫자, 문자가 같이 들어가면 그냥 모두 다 문자 처리를 해버림. '미분류'가 있어야 하니까 category_code를 다 문자 처리로 함.
coalesce : NULL이 아닌 최초의 값을 반환
ifnull + coalesce : 모두 NULL 값이면 두 번째 인자(기본값)으로 지정한 값을 반환함

삼항연산자 if

if (조건, 참, 거짓)
조건: orderable_status = 'Y'
- 만약 orderable_status가 'Y'라면 빈 문자열'' 반환. 그렇지 않으면 '주문불가' 반환

선택함수 case

조건식을 여러개 쓸 수 있다.

1️⃣ CASE (Type 1): 조건식 사용 : 여러 조건을 순차적으로 평가하며, 첫 번째로 참(TRUE)인 조건의 값을 반환

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ...
    [ELSE 기본값]
END

🔍 동작 원리

조건식 평가:
- menu_price < 10000: 메뉴 가격이 1만 원 미만일 때 '만원이하' 반환.
- menu_price BETWEEN 10000 AND 20000: 메뉴 가격이 1만 원 이상 2만 원 이하일 때 '만원대' 반환. 그 외 모든 경우에는 '이만원 이상' 반환.
ELSE 처리: 모든 조건이 거짓(FALSE)인 경우 ELSE에 정의된 기본값 반환.

2️⃣ CASE (Type 2): 표현식 + 값 매칭 : 특정 표현식의 값과 매칭해서 처리한다.

CASE 표현식
    WHEN 값1 THEN 결과1
    WHEN 값2 THEN 결과2
    ...
    [ELSE 기본값]
END