[SQL/MySQL] 다양한 함수 (문자열, 수학, 집계, 시간, 조건...)

🟢 문자열

✅ 문자열 길이

 ▫️ LENGTH( 문자열 )

     - 문자열의 BYTE 길이를 가져옴 (→ 한글은 모음/자음 분리해 카운팅 한 값)

      🔽 LENGTH ( 'naga') = 4, LENGTH('나가') = 4 

 ▫️ CHAR_LENGTH( 문자열 )

     - 문자 단위 개수 가져옴 (영어는 알파벳, 한글은 글자 기준)

       🔽 CHAR_LENGTH ( 'naga') = 4,  CHAR_LENGTH('나가') = 2

✅ 문자열 합치기

 ▫️ CONCAT(str1, str2, str3 ... )       

     - str1, str2, ... 합쳐서 하나의 문자열 반환

      🔽 LENGTH ( 'abc','ddd', 'zza') = 'abcdddzza' 

 ▫️ CONCAT(col1, '/' , col2) : str 대신 컬럼을 매개변수로 넣어 열 합병 가능함

      🔽 col1 {1, 2, 3, 4} , col2{10, 20, 30, 40} 인 경우,

             ⇒ {1/10}, {2/20}, {3/30}, {4/40} 이렇게 각 열에 속한 튜플끼리 합쳐짐

✅ 문자열 자르기

 ▫️ LEFT( 문자열{컬럼},  길이)

     - 왼쪽 처음부터 [길이]만큼 자름

      🔽 LEFT('HELLO', 3) = 'HEL' , LEFT(COL1, 4) = COL1 값들 앞 4글자만 출력

 ▫️ SUBSTRING( 문자열{컬럼},  시작점, 길이 )

     - 시작점 포함 길이만큼 자름 (❗ 1부터 시작)

       🔽 SUBSTRING('HELLO' , 1,  2) = 'HE'

 ▫️ RIGHT( 문자열{컬럼},  길이)

     - 오른쪽 끝부터 [길이]만큼 자름

      🔽 RIGHT('HELLO', 3) = 'LLO' , LEFT(COL1, 4) = COL1 값들 뒤 4글자만 출력

 ▫️ SUBSTRING_INDEX (문자열, 구분자, 횟수) 

     - 구분자가 횟수만큼 나오기 전까지 자름 (N번째 구분자 기준 왼쪽에 있는 모든 문자열 리턴; 구분자 포함!!)

      🔽 SUBSTRING('AA:BB:CC:DD' , ':', 2 ) = 'AA:BB'   ⇒ 두번째 ':' 기준으로 자름

     - 횟수가 음수일 경우(-N) , 마지막부터 N번째 있는 구분자 기준 오른쪽 문자열 리턴

      🔽 SUBSTRING('AA:BB:CC:DD' , ':', -2 ) = 'CC:DD'   ⇒ N번째 포함 X

✅ 그 외

 ▫️ UPPER(str) : 모두 대문자로 변환

 ▫️ LOWER(str) : 모두 소문자로 변환

 ▫️ TRIM(str) : 문자열 앞/뒤 공백 제거 {LTRIM (앞 공백 제거), RTRIM (뒷 공백 제거)} 

 ▫️ REPLACE (str, old, new) : 문자열에서 특정 부분(old)을 다른 문자열(new)로 교체

 ▫️ LOCATE (substr, str, [pos]) : str에 있는 일부(substr)의 위치를 정수로 반환 (없는 경우 0을 반환)

      LOCATE( 'ball', 'eyeball') => 4 (4번째부터 시작)

      LOCATE( 'ball', 'eyeballeyeball', 5) => 8 ( pos부터 시작해서 처음 나오는 위치 출력)

 ▫️ REVERSE(str) : 문자 역순으로 변환

 ▫️ FORMAT(number, k) : 숫자 형식화 (####### -> #,###,###), 소수점 아래 k자리 까지 표현

      FORMAT(123456789, 4) = 123,456,789.0000 (4는 소수점 아래 4자리 표시)

 ▫️ LPAD(str, length, padstr) : 문자열 왼쪽으로 패딩 / RPAD(str, length, padstr) : 오른쪽 패딩

      LPAD('ABC', 3, 'a') = aaaABC

 ▫️ INSTR(str, substr) : 문자열에서 특정 문자열(substr)이 있는지 확인, 있다면 시작 인덱스 반환 | 없다면 0 반환

---

🟢 수학, 집계

 ▫️ ROUND(num, k) : k+1번째 자리에서 반올림 (디폴트 : k = 0)

      ROUND(1.254, 1) = 1.3

 ▫️ TRUNCATE(num, k) :k 번째 아래로 버림

      ROUND(1.254, 1) = 1.2

 ▫️ ABS(num) : 절댓값 변환

 ▫️ CEIL(num) : 올림 / FLOOR(num) : 내림

 ▫️ SUM(col), AVG(col), MIN(col), MAX(col) : 열의 합, 평균, 최소, 최대 

 ▫️ COUNT(col) : 개수

 ▫️ GROUP_CONCAT(col) : 그룹 내 문자열로 모두 결합

---

 

🟢 시간

 ▫️ DATE_FORMAT(date, format) : DATE형 STRING으로 형식에 맞춰 변환

 ▫️ DATEDIFF(date1, date2) : date1 - date2 일 수 차이 계산

🟢 조건

 ▫️ IF(조건, 참인 경우, 거짓인 경우) 

 ▫️ CASE

            WHEN 조건 THEN 수행

             ... ELSE 디폴트

    END : 스위치문

---

💡기타 편의

🔸 SET : 변수 설정 가능 (상수/ 변수) 

      SET @VAR = 1;

SET @HOUR = -1;
SELECT @HOUR := @HOUR +1 AS HOUR,
       (SELECT COUNT(*)
       FROM ANIMALS
       WHERE @HOUR = HOUR(DATETIME)) AS COUNT
FROM ANIMALS
WHERE @HOUR < 23;

 

 

 

🔸 WITH : 서브쿼리 별도로 지정

      🔸 WITH RECURSIVE

WITH RECURSIVE H AS (
	SELECT 0 AS LEVEL -- 초기값 설정
   	UNION ALL
    	SELECT NUM + 1 FROM H
   	WHERE NUM < 23) -- 반복 조건

출처

http://chongmoa.com/sql/9155

https://techblog-history-younghunjo1.tistory.com/146