MySQL 규칙 만들기 방법 - IF, CASE, CREATE FUNCTION

MySQL을 공부하고 정리하고 있습니다. 오늘은 MySQL IF, CASE, CREATE FUNCTION을 사용하여 규칙 만들기에 대해서 정리해 보겠습니다. 조건을 만들어서 새로운 열을 추가하는 경우 또는 내가 직접 함수를 만들어서 사용하는 경우 규칙 만들기가 필요합니다.

---

조건 만들기 (IF)

IF

데이터를 다루다 보면 "구매 금액 10억 이상 고객은 VVIP로 1억 이상이면 VIP로 고객 등급 뽑아주세요" "상품 매출이 1억 이상이면 0으로 미만이면 X로 데이터 뽑아주세요"와 같이 조건에 따른 값이 필요한 경우가 있습니다. 조건을 만들 때 사용하는 함수는 IF입니다.

 

IF의 특징

• IF(조건식, 참일 때 값, 거짓일 때 값) 형식으로 사용합니다.
• 주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환합니다.

IF 사용 예제

mypokemon 테이블에서 일단 name 가져옵니다. attack값이 60보다 크거나 같으면 strong 아니면 weak의 값을 가지는 attack_class 열을 반환합니다.

SELECT name, IF(attack >= 60, 'strong', 'weak') AS attack_class
FROM pockmon.mypokemon;

 

IFNULL

IFNULL은 데이터가 NULL인지 아닌지를 확인해 NULL이라면 새로운 값을 반환하는 함수입니다. 참고로 IS NULL은 데이터가 NULL인지 아닌지를 확인하는 연산자입니다.

 

IFNULL의 특징

• IFNULL([컬럼 이름], NULL일 때 값) 형식으로 사용합니다.
• 해당 컬럼의 값이 NULL인 로우에서 NULL일 때 값을 반환합니다.
• 주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환합니다.

IFNULL 사용 예제

mypokemon테이블에서 우선 name 열을 가져옵니다. 만약 name열의 값이 NULL이면 unknown을 반환하고 NULL이 아니면 name을 반환하는 full_name 열을 반환합니다.

SELECT name, IFNULL(name, 'unknown') AS full_name
FROM pokemon.mypokemon;

---

여러 조건 한 번에 만들기 (CASE)

조건을 여러 개 만들 때 사용하는 문법은 CASE입니다. CASE의 형식은 두 가지가 있습니다. 형식 1(검색형 CASE 표현식 구문)은 컬럼 지정하지 않고 조건식에 따라서 결과값을 반환해 줍니다. 형식 2(단순형 CASE 표현식 구문)는 컬럼 이름을 지정해주고 조건값에 따라서 결과값을 반환해 줍니다. CASE는 주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환합니다. ELSE 문장을 생략 시 NULL 값을 반환합니다. 단순형은 지정한 컬럼의 값이 WHEN 절 이하의 값과 같은 경우만 체크할 수 있는 반면, 검색형은 WHEN절에서 조건식을 사용할 수 있어 다양한 조건 비교가 가능해 그 활용 범위가 더 넓습니다. 조건식을 사용할 수 있다는 것은 단순히 값이 같은지 뿐만 아니라 기준 값이 비교 값과 크거나 작은지 등 다양한 연산자를 사용해 비교 범위를 넓힐 수 있다는 의미입니다. 이런 이유로 단순형보다 검색형 구문을 더 많이 사용합니다.

#형식1 - 검색형 CASE 표현식 구문
CASE
    WHEN 조건식1 THEN 결과값1
    WHEN 조건식2 THEN 결과값2
    ELSE 결과값3
END

#형식2 - 단순형 CASE 표현식 구문
CASE [컬럼 이름]
    WHEN 조건값1 THEN 결과값1
    WHEN 조건값2 THEN 결과값2
    ELSE 결과값3
END

CASE 사용 예제(1) - 형식1

my pokemon 테이블에서 name열을 우선 가져옵니다. attack이 100보다 크면 very stong, 60보다 크면 strong을 나머지는 weak를 반환하는 attack_class열을 가져옵니다.

SELECT name,
CASE
    WHEN attack>=100 THEN 'very strong'
    WHEN attack>=60 THEN 'strong'
    ELSE 'weak'
END AS attack_class
FROM pokemon.mypokemon;

CASE 사용 예제(2) - 형식2

my pokemon 테이블에서 name열을 우선 가져옵니다. attack이 100보다 크면 very stong, 60보다 크면 strong을 나머지는 weak를 반환하는 attack_class열을 가져옵니다.

SELECT name,type
CASE type
    WHEN 'bug' THEN 'grass'
    WHEN 'electric' THEN 'water'
    WHEN 'grass' THEN 'bug'
END AS rival_type
FROM pokemon.mypokemon;

---

함수 만들기 (CREATE FUNCTION)

함수 f(x) = y 식에서 인풋 x를 함수 f(x)에 넣으면 y 아웃풋을 얻을 수 있습니다. 자판기와 비슷한 개념입니다. 동전을 넣고(X) 자판기(함수)를 거치면 물건(Y)이 나오는 것과 유사한 개념입니다. SQL에서는 내장된 함수가 여러 개 있지만 원하는 함수를 직접 만들 수도 있습니다. 자주 쓰는 기능을 미리 만들어 놓고 사용하면 필요할 때마다 일일이 기능을 구현하지 않아도 되므로 SQL문 작성이 편리해집니다.

 

함수 만들기 쿼리 문법

CREATE FUNCTION을 통해 함수를 만들고 DROM FUNCTION을 사용해 함수를 지울 수 있습니다. 참고로 MySQL workbench에서 함수 생성할 때는 주의할 점이 있습니다. 사용자 계정에 function create 권한을 생성해야 하고 함수의 시작과 끝을 지정해 주어야 합니다.

#함수 만들기
CREATE FUNCTION [함수 이름]([입력값 이름][데이터 타입],...)
    RETURNS [결과값 데이터 타입]
BEGIN
    DECLARE [임시값 이름][테이터 타입];
    SET [임시값 이름] = [입력값 이름];
    쿼리;
    RETURN 결과값
END

#함수 지우기
DROP FUNCTION [함수 이름];

#MySQL Workbench에서 함수 생성 시 주의할 점
SET GLOBAL log_bin_trust_function_creators=1; #사용자 계정에 function create 권한 생성
DELIMITER // #함수의 시작 지정
CREATE FUNCTION [함수 이름]([입력값 이름][데이터 타입],...)
    RETURNS [결과값 데이터 타입]
BEGIN
    DECLARE [임시값 이름][테이터 타입];
    SET [임시값 이름] = [입력값 이름];
    쿼리;
    RETURN 결과값
END
// DELIMITER; #함수의 끝 지정

함수 만들기 예시

아래 함수는 공격력과 방어력의 합을 가져오는 함수를 만드는 함수입니다. 첫 줄 attack과 defense는 입력값 이름이고 INT는 데이터 타입입니다. 둘째 줄 INT는 결괏값의 데이터 타입을 의미합니다. BEGIN 키워드 뒤에는 이 함수가 어떤 기능을 하는지 보여줍니다. 먼저 DECLARE라는 키워드를 통해서 임시 값과 임시 값의 데이터 타입을 선언해 줍니다. SET 키워드를 통해 입력값을 임시 값에 넣어줍니다. 다음으로 SELECT 쿼리문이 옵니다. a+b를 하여 ability에 데이터를 넣어줍니다. RETURN 키워드를 통해 ability값을 반환합니다.

CREATE FUNCTION getAbility(attack INT, defense INT)
    RETURNS INT
BEGIN
    DECLARE a INT;
    DECLARE b INT;
    DECLARE ability INT;
    SET a=attack;
    SET b=defense;
    SELECT a+b INTO ability;
    RETURN ability;
END

---

이상으로 MySQL IF, CASE, CREATE FUNCTION을 사용하여 규칙 만들기에 대한 정리 마치겠습니다.