4월 5일 (2) DB - CASE, IF, 문자열 등 기타 함수

제어용 구문 : CASE, IF(), IFNULL(), NULLIF()

CASE 연산자 : 자바스크립트의 switch와 비슷함

 

문법 2종류

 

1. CASE value 

    WHEN [비교값] THEN 실행값

    [WHEN [비교값] THEN 실행값

    ...... 

    ELSE 실행값]

END;

 

SELECT CASE 0
	WHEN 0 THEN '영'
    WHEN 1 THEN '일'
    ELSE '몰라'
END 결과;

SELECT CASE 1
	WHEN 0 THEN '영'
    WHEN 1 THEN '일'
    ELSE '몰라'
END 결과;

SELECT CASE 5
	WHEN 0 THEN '영'
    WHEN 1 THEN '일'
    ELSE '몰라'
END 결과;

 

2. CASE

    WHEN [조건식] THEN 실행값

    [WHEN [조건식] THEN 실행값

    ......

   ELSE 실행값]

END;

-- 2번 형식 예)
-- 급여에 따라 직원의 등급을 부여하시오.
-- 2000 이상 'high', 2000 미만 'low'
USE  dev_db;
SELECT ename,
	CASE 
		WHEN sal >= 2000 THEN 'high'
        WHEN sal < 2000 THEN 'low'
	END grade
FROM emp;

-- 등급에 해당하는 인원수 조회(위의 쿼리문을 서브쿼리로 사용)
SELECT ec.grade, count(ec.grade) 인원수
FROM (
	SELECT ename,
		CASE 
			WHEN sal >= 2000 THEN 'high'
			WHEN sal < 2000 THEN 'low'
		END grade
	FROM emp
) ec
GROUP BY ec.grade;

 

IF(수식, true_case, false_case)

SELECT ename, IF(sal >= 2000, 'high', 'low') grade
FROM emp;

 

 

IFNULL(수식1, 수식2)
수식1의 값이  NULL이면 수식2를 출력,  NULL이 아니면 수식1을 출력

SELECT empno, ename, comm FROM emp;
-- null을 0으로 바꾸기
SELECT empno, ename, IFNULL(comm, 0) 'comm'
FROM emp;
-- null을 없음으로 바꾸기
SELECT empno, ename, IFNULL(comm, '없음') 'comm'
FROM emp;

 

NULLIF(수식1, 수식2)
수식1과 수식2가 같으면  NULL, 같지 않으면 수식1
서로 값이 같을 때나 다를 때를 비교할 때 사용 

SELECT NULLIF(5, 5) 결과;
SELECT NULLIF(5, 3) 결과;

 

 

FORMAT(실수숫자, 소수점자릿수) : 실수 숫자를 문자열로 변환
지정된 소숫점 자릿수까지 표현(반올림)

SELECT FORMAT(3.141592, 3) 파이;

 

수학함수 : CEILING(실수), FLOOR(실수), ROUND(실수, 자릿수)

CEILING 무조건 올림

FLOOR 내림, 버림, 절삭

ROUND 반올림. 자릿수가 음수면 정수 부분의 자리수에 따라 반올림

 

2진수, 8진수, 16진수로 표현

SELECT bin(10) 2진수, hex(10) 16진수, oct(10) 8진수;

문자열 관련 함수
length(문자열) : 문자열 또는 칼럼을 입력, 문자열의 길이 출력

SELECT  length(ename) len, ename FROM emp;

 

insert(문자열, 위치, 길이, 대체문자열)

SELECT insert('abcdefghi', 3, 4, '@@') 결과;
SELECT insert(dname, 3, 4, '@@') 결과, dname FROM dept;

 

left / right(문자열, 개수) : 주어진 문자열(컬럼)에서 개수만큼 추출

SELECT ename, left(ename, 3) 'left', right(ename, 3) 'right' FROM emp;

lpad / rpad(문자열, 길이, 채울문자열)

SELECT lpad(ename, 10, '-') `lpad`, rpad(ename, 10, '-') `rpad`
FROM emp;

숫자가 출력 글자의 개수보다 많아야 한다.

출력 글자의 개수를 지정하고, 개수보다 문자열(데이터)이 적을 경우 '채울문자열'로 나머지를 채운다.

 

 

trim, ltrim, rtrim : 문자열의 시작과 끝부분에 포함된 공백 제거

데이터가 전송될 때 노이즈로 위와 같은 공백이 발생할 수 있음.

SELECT trim('                          trim                       m  ')`trim`;

 

repeat(반복), replace(대체), reverse(역순)

SELECT repeat('MySQL', 4) `repeat`,
       replace('이것이 MySQL', '이것이', 'This is') `replace`,
       reverse('가나다라마바사') `reverse`;

substring_index(문자열, 구분자, 횟수)
횟수가 음수일 경우 뒤에서부터 추출

SELECT substring_index('www.naver.com', '.', 1) sb;
SELECT substring_index('www.naver.com', '.', -2) sb;

 

날짜와 시간 함수
일요일 1, 토요일 7    

 

SELECT ename, year(hiredate) `년`, month(hiredate) `월`, day(hiredate)`일`, hiredate
FROM emp;

SELECT ename, hiredate
FROM emp
WHERE year(hiredate) > '2020';

SELECT now() `현재`,
dayofyear(now())`연중일`,
        dayofmonth(now())`월중일`,
        dayofweek(now())`주중일`,
        monthname(now())`월이름`;
        
SELECT ename, monthname(hiredate)`입사월`
FROM emp;

SELECT date(now()) 날짜, time(now());