[SQL] 조건 만들기(IF,CASE, 사용자 정의 함수)

IF

조건을 만들 때 사용하는 함수

주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환

IFNULL

데이터가 NULL인지 아닌지를 확인해 NULL이라면 새로운 값을 반환하는 함수

IFNULL([컬럼 이름], NULL일 때 값)
-> 해당 컬럼의 값이 NULL인 로우에서 NULL일 때 값을 반환

CASE

조건을 여러 개 만들 때 사용하는 문법

주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환

ELSE 문장을 생략 시 NULL 값을 반환

함수 만들기

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 WorkBench 함수 생성시 주의점

코드 예시

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(10),
attack int,
defense int
);
INSERT INTO mypokemon (number, name, type, attack, defense)
VALUES (10, 'caterpie', 'bug', 30, 35),
(25, 'pikachu', 'electric', 55, 40),
(26, 'raichu', 'electric', 90, 55),
(125, 'electabuzz', 'electric', 83, 57),
(133, 'eevee', 'normal', 55, 50),
(137, 'porygon', 'normal', 60, 70),
(152, 'chikoirita', 'grass', 49, 65),
(153, 'bayleef', 'grass', 62, 80),
(172, 'pichu', 'electric', 40, 15),
(470, 'leafeon', 'grass', 110, 130);

// 함수 만들기

SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER 
	CREATE FUNCTION isStrong(attack INT, defense INT)
		RETURNS VARCHAR(20)
	BEGIN
		DECLARE a INT;
        DECLARE isStrong VARCHAR(20);
        SET a = attack + defense;
      
        IF a > 120 THEN
			SET isStrong = 'very strong';
		ELSEIF a > 90 THEN
			SET isStrong = 'strong';
		ELSE 
			SET isStrong = 'not strong';
        END IF;
        
        RETURN isStrong;
	END
DELIMITER ; // 공백 1칸 있어야 함!

SELECT name,isStrong(attack,defense) AS isStrong
FROM mypokemon;

SELECT name, IF(number >= 150, 'new', 'old') AS age
FROM mypokemon;

SELECT name, If(attack + defense >= 100, 'strong', 'weak') AS ability
FROM mypokemon;

SELECT type, IF(AVG(attack) > 60, 1, 0) AS is_strong_type
FROM mypokemon
GROUP BY type;

SELECT name, IF(attack > 100 and defense > 100, 1, 0) AS ace
FROM mypokemon;

SELECT name,
	CASE
		when number < 100 THEN '<100'
        when number < 200 THEN '<200'
        when number < 500 THEN '<500'
	END AS 'number bin'
FROM mypokemon;

SELECT name,
	CASE
		WHEN number >= 150 THEN IF(attack >= 50, 'new_strong', 'new_weak')
		ELSE IF(attack >= 50, 'old_strong', 'old_weak')
	END AS age_attack
FROM mypokemon;

SELECT type,
	CASE
		WHEN COUNT(type) = 1 THEN 'solo'
        WHEN COUNT(type) < 3 THEN 'minor'
        ELSE 'major'
	END
FROM mypokemon
GROUP BY type;

CASE 로 함수 만들기

DELIMITER 
	CREATE FUNCTION isStrong(attack INT, defense INT)
		RETURNS VARCHAR(20)
	BEGIN
		DECLARE a INT;
        DECLARE b INT;
        DECLARE isStrong VARCHAR(20);
        SET a = attack;
        SET b = defense;
        SELECT CASE 
        	when a + b > 120 THEN 'very Strong'
            when a + b > 90 THEN 'strong'
            ELSE 'not Strong'
            END INTO isStrong;
        RETURN isStrong;
	END
DELIMITER ;