#6 [DML] MySQL에서 내장함수 중 단일행 함수 사용하기, CASE절이란?

본 포스트는 개인 스터디에 대한 정리 및 기록의 용도로써, 오개념이 존재 할 수 있습니다. 글은 상시 수정되며, 지적사항에 대해서 검토 후 수정하겠습니다.

본 포스트는 한국데이터진흥원 발간, SQL 전문가 가이드 2013Edition을 참고하였습니다.

일부 자료에서 국민대학교 김혁만 교수님의 수업자료를 발췌하여 사용했습니다.

---

이번 6에서는 MySQL에서의 내장함수들을 공부해 볼거다. 이번 6-1에서는 내장함수중 단일행 함수들에 대해서 실습해보고 사용하는 방법을 함께 알아가 보자. 

 

내장함수

내장함수는 SQL을 더욱 편리하고 다채롭게 이용해주기 위해 도움을 주는 함수들이다. 각 벤더별로 지원하는 함수가 다르기 때문에 사용에 유의해야한다. 가능하다면 SQL DBMS들이 공통적으로 제공하는 함수를 사용하는것을 추천한다. 내장함수는 아래와 같이 크게 나눠어 진다. 

내장 함수

단일행 함수

-단일행 함수의 특징

• 함수의 입력이 단일행
• SELECT, WHERE, ORDER BY절에서 사용 가능
• 각 행들에 대해 개별적으로 적용하여 데이터 값들을 조작하고, 각각의 행에 대한 결과를 리턴
• 여러인자를 입력해도 하나의 결과만 리턴함.
• 하나의 인자를 가지는 경우도 있지만, 여러개의 인자를 가질 수도 있음
• 함수 중첩 가능

---

 

1. 문자형 함수

-CONCAT()

앞장에서 사용해봤던 CONCAT 함수는 단일행 함수이다. 공배했던거처럼 colum의 내용을 합쳐줄때 사용한다.

SELECT PLAYER_ID, CONCAT(PLAYER_NAME, ' 선수님') AS 선수명
FROM PLAYER;

 

선수이름 옆에 '선수님'이 추가 됐다.

 

 

-LENGTH()

문자열의 길이를 구해준다.

SELECT LENGTH('SQL SERVER') AS ColumLength;

'SQL SERVER'은 공백포함 10글자

-UPPER(), LOWER()

대문자 혹은 소문자로 바꿔준다.

--대문자 변환
SELECT UPPER('SQL SERVER') AS UPPER;

--소문자 변환
SELECT LOWER('SQL SERVER') AS LOWER;

실행 결과

 

이외에도 공백을 제거하는 TRIM(),LTRIM(),RTRIM(), LOCATE(), SUBSTRING() 등 다양한 함수가 존재한다.

---

 

2. 숫자형 함수

숫자형 함수는, 숫자들을 가공하는데 사용한다. 예를 들면 반올림을 한다던지 절대값을 구해준다는지, 입력받은 수에 대해서 가공처리를 해준다. 이번에도 많이 쓰이는 함수들 몇개만 실습해보겠다.

 

-ROUND(), TRUNCATE()

SELECT ROUND(SUM(HEIGHT)/COUNT(HEIGHT),1) AS '선수 평균키(소수 둘째자리에서 반올림)',
	TRUNCATE(SUM(HEIGHT)/COUNT(HEIGHT),1) AS '선수 평균키(소수 둘째자리에서 버림)',
    	TRUNCATE(SUM(HEIGHT)/COUNT(HEIGHT),2) AS '선수 평균키(소수 둘째자리까지 표기)'
FROM PLAYER;

두개를 비교해보기 위해 179.31까지 했는데, 4이하이기 때문에 ROUND()에서 내림이 되서 같은 결과값을 보여준다.

SUM()과 COUNT는 뒤에서 배울 다중행 함수이지만, 맥락상 파악이 가능할것 같다. 모든선수의 키를 더해주고 그 값을 선수들 총합으로 나눠준다. 그리고 그값을 ROUND(     ,1)를 통해 소수 n자리까지 반올림한다.

TRUNCATE()는 TRUNCATE TABLE 에서 봤었던 단어(?)이다. 지운다라는 뜻을 가지고 있는거 같다. 

 

-ABS()

절대값을 구해주는 함수이다.

--절대값 구하기
SELECT ABS(-123) AS 'ABS()';

실행 결과

이 외에도 나머지를 구하는 MOD(), n제곱을 구하는 POWER(), 로그를 구하는 LOG() 등 다양한 숫자형 함수가 있으니, 찾아보고 사용하면 될거 같다.

---

3.날짜형 함수

-DBMS 벤더마다 날짜형 함수의 기능차이가 매우 크다.

-날짜는 내부적으로 하나의 숫자 형식으로 변환하여 저장하기 떄문에, 출력시에는 알맞게 변화하여 추출한다.

 

-SYSDATE(), NOW() 함수

현재 시간을 알려주는 함수이지만, SYSDATE() 함수가 호출 될때의 시간값을 보여주는 반면, NOW는 SELECT가 실행되는 순간의 시간값이라 중복 사용하게 되면 같은 값을 보여준다. 아래 예제를 참고해주면 좋을거 같다.

-- NOW()는 SELECT가 실행될때의 값이 담긴다.
SELECT NOW(), SLEEP(5), NOW();

-- SYSDATE()는 SELECT가 함수가 실행되는 시점의 데이터가 담긴다.
SELECT SYSDATE(), SLEEP(5), SYSDATE();

5초전과 5초후의 시간변화가 없을을 알 수 있다.

5초후의 시간이 변화하였다.

CREATE TABLE study_temp(
	create_time DATETIME NOT NULL DEFAULT NOW()
);

NOW()를 응용해서 사용하면 테이블을 생성할때 해당 열의 DEFAULT 조건으로 NOW()를 주게 되면, 행이 생겨날때의 시간이 담기게 된다.

 

-TIMESTAMP()

--TIMESTAMP()
SELECT TIMESTAMP(NOW()) AS 'TIMESTAME_NOW';

실행 결과

-DATE() 와 날짜 관련 함수

SELECT DATE(NOW()), 
	YEAR(NOW()), 
	MONTH(NOW()),
	DAY(NOW()), 
	MONTHNAME(NOW()), 
	DAYNAME(NOW()), 
	WEEKDAY(NOW());

실행 결과

-TIME()

SELECT TIME(NOW()) AS CurrentTime, 
	HOUR(NOW()) AS Hour, 
	MINUTE(NOW()) AS Minute, 
	SECOND(NOW()) AS Second;

실행 결과

- 날짜형 함수 활용 & EXTRACT() 

EXTRACT(unit FROM date) 함수 또한 DATE 값을 가진 열에서 입력하는 원하는 유닛을 추출해내는 함수이다.

--날짜형 함수 활용하여 데이터 추출하기
SELECT PLAYER_NAME, 
	YEAR(BIRTH_DATE) 출생년도,
	MONTH(BIRTH_DATE) 출생월,
	DAY(BIRTH_DATE) 출생일 
FROM PLAYER;
    
--EXTRACT(unit FROM date) 사용하기
SELECT PLAYER_NAME,
	EXTRACT(YEAR FROM BIRTH_DATE) 출생년도, 
	EXTRACT(MONTH FROM BIRTH_DATE) 출생월, 
	EXTRACT(DAY FROM BIRTH_DATE) 출생일
FROM PLAYER;

두 함수의 실행결과가 같다.

-DATEDIFF(end,begin) , TIMEDIFF(end,begin), TIMESTAMPDIFF(unit,begin,end)

두 인자의 타입이 다르면 NULL을 반환하는 점에 대해 유의하자.

날짜 차이나, 시간차이를 구해주는 함수들이다. 

SELECT DATEDIFF('2021-03-01','2020-03-01') AS 'DATEDIFF',
	TIMEDIFF('2021-02-01 00:00:00', '2021-01-01 00:00:01') AS TIMEDIFF,
	TIMESTAMPDIFF(HOUR, '2021-01-01 00:00:00', '2021-03-01 00:00:00') AS TIMESTAMEPDIFF;

실행 괄과

unit 에는 MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH , QUARTER, YEAR 사용할수 있다.

 

이번에는 이 함수들을 사용하여 나이를 구해보자.

--만 나이
SELECT TIMESTAMPDIFF(YEAR,'1999-05-16 03:00:00', DATE(NOW())) 'MY years';

만 나이가 구해졌다.

 

-DATE_FORMAT(date, format), GET_FORMAT( date, format)

입력받은 데이터 값을 어떻게 해석해야하는지에 대해서 DATE_FORMAT을 통해 사용하여 출력하고, 이러한 정해져있는 틀이 GET_FORAMT에 있는데 이틀을 DATE_FORAMT에 format에 넣어주어 사용한다.

 

SELECT PLAYER_NAME, 
	DATE_FORMAT(BIRTH_DATE, '%Y-%m-%d'), 
	DATE_FORMAT(BIRTH_DATE, '%D %M %Y')
FROM PLAYER;

이런식으로 출력을 정해준다.

 

GET_FORMAT은 아래와 같이 국가별 표준 포맷을 미리 가지고 있다.

국민대학교 김혁만 교수님의 강의자료를 발췌음을 밝힌다.

아래와 같이 조금 더 편하게 날짜들을 정의 할 수 있다.

-- GET_FORMAT() 사용하기
SELECT PLAYER_NAME, 
	POSITION,
	DATE_FORMAT(BIRTH_DATE, GET_FORMAT(DATE, 'ISO')) AS BIRTH_DATE
FROM PLAYER;

실행결과

---

4. 변환형 함수

-CAST() 함수

데이터의 형식을 변환 해준다, 예를 들어 STINRG -> UNSIGNED INT 로 형변화를 시켜서 정수끼리의 합이 가능해 지게 한다.

 

-CONVERT() 함수

데이터를 다른 charcter set으로 변환시킨다.

 

간단하기 때문에 예제로 다루지 않겟다.

---

5. CASE 절

• IF - THEN - ELSE 의 논리 흐름을 가진다.
• SQL의 비교연산의 기능을 보완한다.
• 중첩이 가능하다.
• Simple case expression과 Searched case expression으로 나뉜다.

#포지션을 한글로 바꿔주기

--포지션의 이름을 한글로 바꿔주기

--Searched Case
SELECT PLAYER_NAME,
	CASE
		WHEN POSITION='FW' THEN '공격수'
		WHEN POSITION='DF' THEN '수비수'
		WHEN POSITION='MF' THEN '미드필더'
		WHEN POSITION='GF' THEN '골키퍼'
		ELSE '포지션 없음'
	END AS '포지션'
FROM PLAYER;


--Simple Case
SELECT PLAYER_NAME,
	CASE POSITION
		WHEN 'FW' THEN '공격수'
		WHEN 'DF' THEN '수비수'
		WHEN 'MF' THEN '미드필더'
		WHEN 'GF' THEN '골키퍼'
		ELSE '포지션 없음'
	END AS '포지션'
FROM PLAYER;

동일한 실행 결과를 가진다.

Simple Case는 CASE절 내 WHEN절에서 비교 연산자가 '=' 이면 사용 할 수 있다.

 

#신장에 따른 등급 분류

--신장 점수
-- CASE절과 중첩 CASE절 비교
SELECT PLAYER_NAME, HEIGHT AS '신장',
	CASE
		WHEN HEIGHT >= 185 THEN 'A'
		WHEN HEIGHT >= 175 THEN 'B'
		WHEN HEIGHT < 175 THEN 'C'
		WHEN HEIGHT IS NULL THEN 'UNDECIDED'
	END AS '신장 점수'
FROM PLAYER;  


--중첩 CASE절 사용하기
SELECT PLAYER_NAME, HEIGHT AS '신장',
	CASE 
		WHEN HEIGHT >= 185 THEN'A'
		ELSE (
				CASE
					WHEN HEIGHT >= 175 THEN 'B'
					WHEN HEIGHT < 175 THEN 'C'
					WHEN HEIGHT IS NULL THEN 'UNDECIDED'
				END
			)
		END AS '신장 점수'
FROM PLAYER;

같은 결과 값을 가진다.

---

6. NULL 관련 함수

NULL 값과의 계산과 비교에서, 수치 계산은 NULL을 값과의 비교에서는 FALSE를 리턴하며, 이러한 이유로 질의를 만들때 고려 요소이다.

각 벤더마다 NULL을 처리해주는 함수가 있지만 이식성을 위해 사용을 지양하고, 공통적으로 사용 가능한 COALESCE() 함수를 사용하는거를 권장한다.

 

-COALESCE()

COALESCE(a,b,c) NULL 값이 아닌 최초의 값을 사용한다, 만약 a가 NULL 이면 b, b가 NULL 이면 c 를 사용하고, 모두가 NULL 이라면 NULL 을 출력한다. 이것을 막기 위해 맨 마지막에는 COALESCE(a,b,c, '*****')로 사용 할 수있다.

--사용법
SELECT PLAYER_NAME, 
	E_PLAYER_NAME, NICKNAME, 
	COALESCE(E_PLAYER_NAME, NICKNAME) AS 별칭
FROM PLAYER;



--CASE를 이용하여 COALESCE()와 같은 값 출력해보기
SELECT PLAYER_NAME, E_PLAYER_NAME, NICKNAME, 
CASE
	WHEN E_PLAYER_NAME IS NOT NULL THEN E_PLAYER_NAME
	ELSE ( CASE
			WHEN NICKNAME IS NOT NULL THEN NICKNAME
			ELSE NULL 
            END
		)
END AS 별칭 FROM PLAYER;

 

#예제

SELECT PLAYER_NAME,
	COALESCE(POSITION,'*****') AS POSITION,
	COALESCE(HEIGHT,0) AS HEIGHT
FROM PLAYER
WHERE TEAM_ID = 'K08';


-- COALESCE를 적용하지 않았을 때
SELECT PLAYER_NAME,
	POSITION AS POSITION,
	POSITION AS HEIGHT
FROM PLAYER
WHERE TEAM_ID = 'K08';

NULL 값들은 *****로 대체 됐다, 그냥 출력하면 NULL 값이 출련된다.

사용에 있어 주의 해야할 점이 한가지 있다. 조건에 맞는 튜플이 없는경우, ( 해당 열에 찾고자 하는 값이 없는경우)는 집단 함수를 적용하여 인자의 결과값을 NULL 로 만들고 출력해야한다. NULL과 값이 없는건 다르다는것에 유의하자

 

---

오늘은 단일 함수에 대해서 알아봤다. 다음 포스트에서는 다중행 함수에 대해서 알아보고 더욱 상세한 조건을 달아가며 원하는 데이터를 뽑아 내보자.