![#6 [DML] MySQL에서 내장함수 중 단일행 함수 사용하기, CASE절이란?](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdna%2FJK0NO%2Fbtq53u92IZn%2FAAAAAAAAAAAAAAAAAAAAAK9uRuVVy480QAEzx8PdVSAJ_fFjBMrNiOHKr1XDB-C3%2Fimg.png%3Fcredential%3DyqXZFxpELC7KVnFOS48ylbz2pIh7yKj8%26expires%3D1759244399%26allow_ip%3D%26allow_referer%3D%26signature%3DC2psviMKgVQgRm3p5Y2cIkty4iU%253D)
본 포스트는 개인 스터디에 대한 정리 및 기록의 용도로써, 오개념이 존재 할 수 있습니다. 글은 상시 수정되며, 지적사항에 대해서 검토 후 수정하겠습니다.
본 포스트는 한국데이터진흥원 발간, 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;
-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;
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();
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과 값이 없는건 다르다는것에 유의하자
오늘은 단일 함수에 대해서 알아봤다. 다음 포스트에서는 다중행 함수에 대해서 알아보고 더욱 상세한 조건을 달아가며 원하는 데이터를 뽑아 내보자.
'• 데이터베이스(DB) > __SQL__' 카테고리의 다른 글
| [node.js & MySQL] 서버에서 사용하는 CRUD 알아보기 ( CREATE, READ, UPDATE, DELETE ) 게시판에 사용되는 CRUD (0) | 2022.01.22 |
|---|---|
| #7 [DML] MySQL의 다중행 함수중 GROUP BY, HAVING, ORDER BY, LIMIT 사용하기 (0) | 2021.05.31 |
| #5 [DML] MySQL에서 WHERE 조건식 사용해보기 IN,LIKE,IS NULL,BETWEEN (0) | 2021.05.25 |
| #4 [DML] MySQL에서 SELECT 사용해보기 (0) | 2021.05.25 |
| #3[DML] MySQL 에서 INSERT, DELETE, UPDATE 문을 사용해보기, sql_Safe_Updates모드 (0) | 2021.05.25 |