[MySQL] SQL 프로그래밍

IF 문

조건식이 참이면 ‘SQL문장’을 실행하고, 그렇지 않으면 그냥 넘어간다.

IF <조건식> THEN
      SQL문장
END IF;

 

📌 SQL 문장이 두 문장 이상이라면 BEGIN ~ END 로 묶어주어야 한다

DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
  IF 100 = 100 THEN
     SELECT '100은 100과 같습니다.';
  END IF;
END $$
DELIMITER; 
CALL ifProc1();

 

 

 

 

IF ~ ELSE 문

조건에 따라 다른 부분을 수행한다.

조건식이 참이면 ‘SQL문장1’을 실행, 그렇지 않으면 ‘SQL문장2’를 실행한다.

 

DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN 
   DECLARE myNum INT; // DECLARE 예약어를 사용해 INT 형의 myNum 변수 선언
   SET myNum = 200;
   IF myNum = 100 THEN
      SELECT '100입니다.';
   ELSE
      SELECT '100이 아닙니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc2();

 

 

 

 

IF 문의 활용

- ‘아이디가 ‘APN’인 회원의 데뷔일자가 5년이 넘었으면 축하 메시지 출력하기’

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
   DECLARE debutDate DATE; // 데뷔 일자
   DECLARE curDate DATE; // 오늘
   DECLARE days INT; // 활동한 일 수
     SELECT debut_date INTO debutDate
       FROM market_db.member
       WHERE mem_id = 'APN';
     
     SET curDate = CURRENT_DATE();
     SET days = DATEDIFF(curDate, debutDate);

     IF (days/365) >= 5 THEN
        SELECT CONCAT('데뷔한 지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
     ELSE
        SELECT '데뷔한 지 ' + days + '일밖에 안되었네요. 핑순이들 화이팅~';
     END IF;
END $$
DELIMITER ;
CALL ifProc3();

 

MySQL에서 제공하는 날짜 관련 함수

• CURRENT_DATE() : 오늘 날짜 출력
• CURRENT_TIMESTAMP() : 오늘 날짜 및 시간 출력
• DATEDIFF(날짜1, 날짜2) : 날짜2부터 날짜 1까지 일수로 며칠인지 출력

 

 

 

CASE 문

IF 문은 참 / 거짓 2가지만 있기 때문에 ‘2중 분기’라고 한다.

CASE 문은 2가지 이상의 경우를 처리할 수 있기 때문에 ‘다중 분기’라고 한다.

CASE
  WHEN 조건1 THEN
    SQL문장1
  WHEN 조건2 THEN
    SQL문장2
  WHEN 조건3 THEN
    SQL문장3
  ELSE
    SQL문장4
END CASE;

 

 

- 90점 이상은 A, 80점 이상은 B, 70점 이상은 C, 60점 이상은 D, 60점 미만은 F로 나누기

DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
   DECLARE point INT;
   DECLARE credit CHAR(1);
   SET point = 88;

   CASE
     WHEN point >= 90 THEN
        SET credit = 'A';
     WHEN point >= 80 THEN
        SET credit = 'B';
     WHEN point >= 70 THEN
        SET credit = 'C';
     WHEN point >= 60 THEN
        SET credit = 'D';
     ELSE
        SET credit = 'F';
   END CASE;
   SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

 

 

 

 

CASE 문의 활용

- ‘회원들의 총 구매액을 계산해서 회원 등급을 다음과 같이 4단계로 나누기’

총 구매액	회원 등급
1500 이상	최우수고객
1000 ~ 1499	우수고객
1 ~ 999	일반고객
0 이하 (구매 X)	유령고객

 

- (1) 구매 테이블에서 회원별 총 구매액 구하기

SELECT mem_id, SUM(price*amount) "총 구매액"
   FROM buy
   GROUP BY mem_id;

 

- (2) 총 구매액이 큰 순서대로 정렬하기

SELECT mem_id, SUM(price*amount) "총 구매액"
   FROM buy
   GROUP BY mem_id
   ORDER BY SUM(price*amount) DESC;

 

- (3) JOIN을 사용해 회원 테이블에 있는 회원 이름과 함께 출력하기

SELECT B.mem_id, M.mem_name,
       SUM(price*amount) "총 구매액"
    FROM buy B
         INNER JOIN member M
         ON B.mem_id = M.mem_id
    GROUP BY B.mem_id
    ORDER BY SUM(price*amount) DESC;

 

- (4) OUTER JOIN을 사용해 구매하지 않은 회원의 이름과 아이디도 함께 출력하기

SELECT M.mem_id, M.mem_name,
       SUM(price*amount) "총 구매액"
    FROM buy B
         RIGHT OUTER JOIN member M
         ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(price*amount) DESC;

 

- (5) CASE 문을 사용해 회원 등급을 구분하는 열 추가하기

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총 구매액",
       CASE 
          WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
          WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
          WHEN (SUM(price*amount) >= 1) THEN '일반고객'
          ELSE '유령고객'
       END "회원등급"
    FROM buy B
         RIGHT OUTER JOIN member M
         ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(price*amount) DESC;

 

 

 

 

WHILE 문

조건식이 참인 동안 ‘SQL문장’을 계속 반복한다.

WHILE <조건식> DO
     SQL문장
END WHILE;

 

 

- 1 ~ 100 까지의 값을 모두 더하기

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
   DECLARE i INT; // 1에서 100까지 증가할 변수
   DECLARE hap INT; // 더한 값을 누적할 변수
   SET i = 1;
   SET hap = 0;

   WHILE (i <= 100) DO
     SET hap = hap + i; // hap의 원래 값에 i를 더해서 다시 hap에 넣으라는 의미
     SET i = i + 1; // i의 원래 값에 1을 더해서 다시 i에 넣으라는 의미
   END WHILE;

   SELECT '1부터 100까지의 합==>', hap;
END $$
DELIMITER ;
CALL whileProc();

 

 

 

 

WHILE 문의 응용

• ITERATE [레이블] : 지정한 레이블로 가서 계속 진행
• LEAVE [레이블] : 지정한 레이블을 빠져 나감. WHILE 문이 종료됨

 

- ‘1부터 100까지의 합을 구한다. 이때 4의 배수를 제외하고 합이 1000이 넘으면 종료한다.’

DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;

    myWhile:
    WHILE (i <= 100) DO
       IF (i % 4 = 0) THEN
          SET i = i + 1;
          ITERATE myWhile;
       END IF;
       SET hap = hap + i;
       IF (hap > 1000) THEN
          LEAVE myWhile;
       END IF;
       SET i = i + 1;
    END WHILE;

    SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>', hap;
END $$
DELIMITER ;
CALL whileProc2();

 

 

 

 

동적 SQL

동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있다.

 

PREPARE : SQL 문을 실행하지는 않고 미리 준비만 해놓음

EXECUTE : 준비한 SQL 문을 실행

→ 실행 후에는 DEALLOCATE PREPARE로 문장을 해제하기

 

use market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

 

 

 

 

동적 SQL의 활용

PREPARE 문에서는 ?로 향후 입력될 값을 비워놓는다.

EXECUTE에서 USING으로 ?에 값을 전달한다.

→ 실시간으로 필요한 값을 전달해서 동적으로 SQL이 실행된다.

 

 

- ‘출입 내역을 확인하는 테이블 만들기’

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP();

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;