(MariaDB)내장함수

제어 흐름 함수

IF(수식, 참 , 거짓)

• SELECT IF(100>200 , '참' ,'거짓') // 거짓 출력

IFNULL(수식1, 수식2)

• 수식1이 NULL이면 수식2 반환
• 수식1이 NULL이 아니면 수식1 반환

NULLIF(수식1, 수식2)

• 수식1과 수식2가 같으면 NULL반환
• 다르면 수식1 반환

CASE ~ WHEN ~ ELSE ~ END

• 연산자로 분류
• 다중분기
• SELECT case 10 when 1 then '일' when 5 then '오' when 10 then '십' END;

---

문자열 함수

ASCII(아스키코드), CHAR(숫자)

SELECT ASCII('A), CHAR(65);
// 65 , 'A'

BIT_LENGTH, CHAR_LENGTH, LENGTH

• 문자개수와 바이트 개수 반환

CONCAT(문자열1,문자열2,..),CONCAT_WS(구분자,문자열1,문자열2,...)

• 문자열을 이어줌

FORMAT(숫자,소수점자리수)

SELECT FORMAT(123456.123456, 4);
//123456,1235

TRIM(문자열)

• 문자열의 앞뒤 공백을 모두 없앰

---

수학 함수

CEILING , FLOOR

올림

ROUND

반올림

MOD(숫자1, 숫자2)

• 숫자1 MOD 숫자2
• 숫자1 % 숫자2
• Modulus연산

---

날짜 및 시간함수

ADDDATE(날짜,차이) , SUBDATE(날짜,차이)

• 날짜를 기준으로 차이를 더하거나 뺸 날짜

ADDTIME(날짜/시간,시간) , SUBTIME(날짜/시간,시간)

• 날짜/시간을 기준으로 시간을 더하거나 뺀 결과

CURDATE()

• 현재 연 - 월 - 일CURTIME()
• 현재 시 : 분 : 초NOW(),SYSDATE()

YEAR(날짜) , MONTH(날짜) , DAY(날짜) , HOUR(시간) , MINUTE(시간), SECOND(시간)

• 날짜 또는 시간에서 하나씩 뽑아낼 때 사용

DATEDIFF(날짜1, 날짜2), TIMEDIFF(시간1, 시간2)

• 날짜1- 날짜2 , 시간1- 시간2

---

윈도우 함수

순위 함수

RANK()

NTILE()

• 데이터 크기 순으로 세운 뒤 그룹을 나눌 때 사용

SELECT NTILE(4) over(ORDER BY height DESC) '반번호',username, addr,height FROM usertbl;

DENSE_RANK()

ROW_NUMBER

• 행에 번호를 매긴다
• SELECT ROW_NUMBER() over(PARTITION BY addr ORDER BY height DESC),addr,height,username FROM usertbl;

분석 함수

LEAD()

• 다음행과의 차이(마지막 줄 : NULL)
• SELECT username,addr,height, height-(LEAD(height,1) over (ORDER BY height DESC)) AS "다음사람과 키차이" FROM usertbl;

LAG()

• 이전 행과의 차이(첫 줄 : NULL)

피벗과 JSON

피벗

한 열에 포함된 여러 값을 출력하고 이를 여러 열로 변환하여 집계까지 수행하는 것

SELECT uname, SUM(if(season='봄',amount,0))AS '봄',
SUM(if(season='여름',amount,0))AS '여름',
SUM(if(season='가을',amount,0))AS '가을',
SUM(if(season='겨울',amount,0))AS '겨울',
SUM(amount) AS '합계'
FROM pivottest
GROUP BY uname;

JSON

데이터를 교환하기 위한 개방형 표준 포맷이고 key과 value 쌍으로 구성

• 독립적인 데이터 포맷
• {key1 : value, key2 :value, key3:value...}
• {key1:[1,2,3,4,5]}

연습

-- Q1) 사원 테이블에서 사원이름을 첫글자는 대문자로, 나머지는 소문자로 출력하자.
SELECT CONCAT(UPPER(SUBSTRING(ename,1,1)),LOWER(SUBSTRING(ename,2))) AS '대문자+소문자'
FROM emp;
-- Q2) 사원테이블에서 사원이름을 출력하고, 이름의 두번째 글자부터 네번째 글자도 출력하자.
SELECT ename,SUBSTRING(ename,2,3)
FROM emp;
-- Q3) 사원테이블에서 각 사원 이름의 철자 개수를 출력하자.
SELECT ename, LENGTH(ename)
FROM emp;
-- Q4) 사원테이블에서 각 사원 이름의 앞 글자 하나와 마지막 글자 하나만 출력하되, 소문자로 출력하라.
SELECT ename,SUBSTRING(ename,1,1),SUBSTRING(ename,-1)
FROM emp;
-- Q5) 3456.78을 소수점 첫번째 자리에서 반올림해서 출력하자.
SELECT ROUND(3456.78,0);
-- Q6) 사원테이블에서 사원이름과 근무일수(고용일 ~ 현재 날짜)를 출력하자.(한달을 30일로 계산)
SELECT ename,DATEDIFF(NOW(),hiredate)
FROM emp;
-- Q7) 위 문제에서 근무일수를 '00년 00개월 00일' 형식으로 출력하자.
-- 예)
-- ENAME | 근무일수
-- KING | 00년 00개월 00일

SELECT ename ,concat(floor(DATEDIFF(NOW(),hiredate)/365),'년 ',floor(mod(DATEDIFF(NOW(),hiredate),365)/30),'개월 ',
mod(mod(DATEDIFF(NOW(),hiredate),365),30),'일')AS '근무일수 '
FROM emp;