0. 학습 목표
- 다양한 데이터 형식에 대해 이해
- 두 테이블을 연결하는 조인을 이해하고 활용
- SQL에서 일반 프로그래밍 기능을 구현
0. 요약
- 정수형은 소수점이 없는 숫자이며 TINYINT, SMALLINT, INT, BIGINT가 있음
- 문자형은 고정형 문자형인 CHAR과 가변형 문자형인 VARCHAR이 있음
- 실수형은 소수점 아래 7자리까지 표현되는 FLOAT와 15자리까지 표현되는 DOUBLE가 있음
- 날짜형은 날짜DATE, 시간TIME, 날짜시간DATETIME이 있음
- MySQL에서 제공되는 변수 앞에는 @를 붙임
- 데이터 형식을 변경하는 형 변환 함수에는 CAST(), CONVERT()가 있음
- 일대다 관계란 한쪽 테이블에는 하나의 값만, 다른 쪽 테이블에는 여러 개의 값이 존재할 수 있는 관계
- 조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들내는 것
- 내부 조인은 두 테이블을 조인할 때 두 테이블에 모두 지정한 열의 데이터가 있어야 함
- 외부 조인은 두 테이블을 조인할 때 1개의 테이블에만 데이터가 있어도 결과을 얻을 수 있음
- 상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인
- 자체 조인은 자신이 자신과 조인된다는 의미로 1개의 테이블만 쓰임
- IF문은 조건식이며 참은 수행 아닌경우는 ELSE로 거짓일 때 수행할 수 있도록 쓰임
- 변수는 DECLARE로 선언하고 SET으로 값을 대입
- CASE문은 2가지 이상일 때 처리 가능(다중 분기)
- WHILE 문은 조건식이 참인 동안에는 계속 반복 실행, WHILE문을 계속실행하는 ITERATE와 빠져나가는 LEAVE도 있음
- PREPARE는 SQL문을 실행하지 않고 준비, EXECUTE는 준비한 SQL을 실행 -> 동적 SQL
3. SQL 프로그래밍
※핵심 키워드: IF문, 변수, CASE 문, WHILE문, 동적 SQL
MySQL에서 프로그래밍 기능을 가진 스토어드 프로시저는 다음과 같은 구조를 가진다.
SELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
## 이부분에 SQL 프로그래밍 코딩
END $$
DELIMITER ;
CALL 스토어드_프로시저_이름()3-1. IF 문
IF문의 기본 형식
IF문은 조건식이 참이라면 SQL문장을 실행하고 아니라면 넘어간다.
IF <조건식> THEN
SQL 문장들
END IF;간단한 예로 100=100이라는 조건으로 SQL문장을 실행하도록 하자. 주의할 점은 BEGIN ~ END는 두 문장이상의 SQL문장을 사용할 때 쓰는데 습관적으로 사용하도록 하자.
DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
IF 100 = 100 THEN
SELECT '100은 100과 같다';
END IF
END $$
DELIMITER;
CALL ifProc1();
# 100은 100과 같다
'100은 100과 같다'IF ~ ELSE 문
IF ~ ELSE문은 조건식이 참이라면 SQL문장을 실행하고 아니라면 ELSE에 선언된 SQL 문장을 실행한다.
DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
DECLARE myNum INT;
SET myNum = 200;
IF myNum = 100 THEN
SELECT '100입니다';
ELSE
SELECT '100이 아닙니다';
END IF;
END $$
DELIMITER ;
CALL ifProc2();
# 100이 아닙니다
'100이 아닙니다'IF문의 활용
아이디가 APN인 회원의 데뷔일자가 5년이 넘었는지 확인하고 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 CONCAT ('데뷔한지 ', days, '일밖에 안되었네요. 핑순이들 화이팅!');
END IF;
END $$
DELIMITER ;
CALL ifProc3();
# CONCAT ('데뷔한지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!')
'데뷔한지 4006일이나 지났습니다. 핑순이들 축하합니다!'3-2. CASE 문
CASE문의 기본 형식
IF문은 조건식이 참 거짓만있기 때문에 2중 분기문이라는 용어를 사용한다. CASE 문은 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 다중 분기라고 부른다.
우선 CASE문의 형식을 보자.
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로 나눠서 CASE문을 사용해 보자.
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();
# CONCAT ('취득점수==>', point), CONCAT('학점==>', credit)
'취득점수==>88', '학점==>B'CASE문의 활용
이번에는 CASE문을 활용하여 인터넷 마켓 데이터베이스의 회원들의 총 구매액을 계산하여 회원의 등급을 나눠보자.
| 총 구매액 | 회원 등급 |
| 1500이상 | 최우수 고객 |
| 1000 ~ 1499 | 우수 고객 |
| 1 ~ 999 | 일반 고객 |
| 0 이하(구매한적 없음) | 유령 고객 |
다음과 같이 진행할 예정이다.
- 구매 테이블(buy)에서 회원별로 총 구매액을 구함(GROUP BY, ORDER BY)
- 위 구한 값에 회원 이름을 추가(member 테이블과 조인), 단 한 번도 구매하지 않은 사용자도 구해야 함
- 구매액에 따라 '회원 등급'열 및 값을 추가(CASE문)
우선 첫 번째를 구해 보자.
- 구매 테이블(buy)에서 회원별로 총 구매액을 구함(GROUP BY, ORDER BY), 단 한 번도 구매하지 않은 사용자도 구해야 함
SELECT B.mem_id, SUM(price*amount) '총 구매액'
FROM buy B
GROUP BY B.mem_id
ORDER BY SUM(price*amount) DESC;
# mem_id, 총 구매액
'MMU', '1950'
'BLK', '1210'
'APN', '295'
'GRL', '75'두 번째를 구해 보자.
- 위 구한 값에 회원 이름을 추가(member 테이블과 조인), 단 한 번도 구매하지 않은 사용자도 구해야 함
주의 할점은 단 한 번도 구매한적이 없는 사용자도 출력해야 하므로 member를 기준으로 조인해야하며 GROUP BY도 member.mem_id로 해야한다는 점이다.
SELECT B.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;세 번째를 구해 보자.
- 구매액에 따라 '회원 등급'열 및 값을 추가(CASE문)
이번에 주의할 점은 CASE 문을 통해 '회원 등급' 열을 추가한다는 것이다. 그리고 끝이 날 때 END를 꼭 써줘야 한다.
SELECT B.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;
# mem_id, mem_name, 총 구매액, 회원등급
'MMU', '마마무', '1950', '최우수 고객'
'BLK', '블랙핑크', '1210', '우수 고객'
'APN', '에이핑크', '295', '일반 고객'
'GRL', '소녀시대', '75', '일반 고객'
NULL, '잇지', NULL, '유령 고객'
NULL, '오마이걸', NULL, '유령 고객'
NULL, '레드벨벳', NULL, '유령 고객'
NULL, '우주소녀', NULL, '유령 고객'
NULL, '트와이스', NULL, '유령 고객'
NULL, '여자친구', NULL, '유령 고객'3-3. WHILE 문
WHILE의 기본 형식
WHILE 문은 조건식이 참인 동안 SQL문장들을 계속 반복 실행한다.
WHILE <조건식> DO
SQL 문장들
END WHILE;1~100까지 더하는 WHILE문을 작성해 보자.
DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
DECLARE i INT;
DECLARE hap INT;
SET i = 1;
SET hap = 0;
WHILE (i <= 100) DO
SET hap = hap + i;
SET i = i + 1;
END WHILE;
SELECT '1부터 100까지의 합 = ', hap;
END $$
DELIMITER ;
CALL whileProc();
# 1부터 100까지의 합 = , hap
'1부터 100까지의 합 = ', '5050'WHILE문의 응용
이번에는 1 ~ 100까지의 합에서 4의 배수를 제외하고 합이 1000이 넘는 순간 더하는 것을 그만두도록 해보자.
이런 경우 ITERATE와 LEAVE 문을 활용할 수 있다. INTERATE는 CONTINUE, LEAVE는 BREAK와 비슷하다. 즉 ITERATE는 지정한 레이블로 가서 계속하고 LEAVE는 WHILE문을 빠져 나간다.
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();
# 1부터 100까지의 합(4의 배수 제외), 1000이넘으면 종료 = , hap
'1부터 100까지의 합(4의 배수 제외), 1000이넘으면 종료 = ', '1014'3-4. 동적 SQL
동적 SQL은 내용 변경이 필요할 때 동적 SQL을 사용하여 변경된느 내용을 실시간으로 사용할 수 있다.
PREPARE와 EXECUTE
PREPARE는 SQL문을 실행하지 않고 준비만 하는 것이고 EXECUTE는 준비한 SQL을 실행하는 것이다. 그리고 실행 후에는 PREPARE가 계속 남아있지 않도록 DEALLOCATE PREPARE로 문장을 해제해 주는 것이 좋다.
한 예제로 아래는 mem_id가 BLK인 것을 준비하고 있다가 실행하는 것이다.
USE market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'동적 SQL의 활용
PREPARE에서 ?로 지정하여 향후에 입력될 값을 비워 놓고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있다.
예를 들어 출입문에서 출입한 내역을 테이블에 기록해 보자. 이때 출입증을 태그하는 순간의 날짜와 시간이 INSERT문으로 입력되도록 만들자.
- gate_table을 만든다.(id는 AUTO_INCREMENT & PRIMARY KEY, entry_time은 태그하는 시간)
- 현재 시간을 구하는 CURRENT_TIMESTAMP()함수의 실행 값을 넣을 변수(curDate)를 지정한다
- PREPARE로 어느 시간에 출입증이 태그될지 모르니 출입증이 태그되는 시간을 ?으로 지정한다
- 태그되는 순간을 USING을 사용하여 위에서 만든 curDATE를 지정한다
- DEALLOCATE로 PREPARE를 해제한다.
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;
# id, entry_time
'1', '2022-01-29 22:10:25'
'혼자공부하는 SQL' 카테고리의 다른 글
| 5. 테이블과 뷰-2 (0) | 2022.02.03 |
|---|---|
| 5. 테이블과 뷰-1 (0) | 2022.02.03 |
| 4. SQL 고급 문법-2 (0) | 2022.01.28 |
| 4. SQL 고급 문법-1 (0) | 2022.01.24 |
| 3. SQL 기본 문법-3 (0) | 2022.01.21 |
