[복습] 고급 SQL 프로그래밍

1. SQL 프로그래밍

 -SQL 에서도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있음

 -SQL에서도 다른 프로그래밍 언어와 비슷한 분기, 흐름제어, 반복의 기능이 있음

 

2. MySQL 데이터 형식

1) 숫자

자료형	바이트	크기
TINYINT	1	2=2=256
SMALLINT	2	2=2=65536
MEDIUMINT	3	2=2=약1600만
INT	4	2=2=약40억
BIGINT	8	2=2=약1800경
DECIMAL	가변	최대 65자리 수
FLOAT	4바이트	유효숫자 10자리
DOUBLE	8바이트	유효숫자 17자리
BIT	가변	최대 64비트
REAL	DOUBLE이 적용됨
BOOL BOOLEAN	TINIYINT(1)이 적용됨
SERIAL	BIGINT(20)이 적용됨

 

2)문자

데이터 형식	바이트 수	설명
CHAR(n)	1~255	-고정길이 문자형 -n을 1~255까지 지정 -그냥 CHAR=CHAR(1)
VARCHAR(n)	1~65535	-가변길이 문자형 -n 사용시 1~65535까지 지정
BINARY(n)	1~255	고정길이 이진 데이터 값
VARBIANRY(n)	1~255	가변길이 이진 데이터 값
TINYTEXT	1~255	255크기의 TEXT 데이터 값
TEXT	1~65535	n크기의 TEXT 데이터 값
MEDIUMTEXT	1~16777215	16777215크기의 TEXT 데이터 값
LONGTEXT	1~4294967295	최대 4GB 크기의 TEXT 데이터 값

 

3)날짜와 시간

데이터 형식	바이트 수	설명
DATE	3	YYYY-MM-DD
TIME	3	HH:MM:SS
DATETIME	8	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4	YYYY-MM-DDD HH:MM:SS -time_zone시스템 변수와 관련 -UTC 시간대로 변환하여 저장
YEAR	1	YYYY(1901~2155) -1901년~2155년 범위 외의 연도 표기 불가

4)기타

 -BLOB(Binary Large Object)데이터

  ☞사진파일, 동영상 파일, 문서 파일 등의 대용량 이진 데이터를 저장하는데 사용

데이터 형식	바이트 수	설명
TINYBLOB	1~255	255크기의 BLOB 데이터 값
BLOB	1~65535	N 크기의 BLOB 데이터 값
MEDIUMBLOB	1~16777215	167777215 크기의 BLOB 데이터 값
LONGBLOB	1~429467295	최대 4GB 크기의 BLOG 데이터 값

 -ENUM

  ☞열거형 데이터 쓸 때 사용(ex. 요일: 월, 화, 수, 목,..)

  ☞최대 65535개 열거형 데이터 값 

  ☞1 또는 2 바이트

 

 -SET

  ☞최대 64개 서로 다른 데이터 값

  ☞입력은 최대 64개 서로 다른 2개씩 세트로 데이터를 저장

  ☞1,2,3,4,8 바이트

 

 -GEOMETRY

  ☞공간 데이터 형식

  ☞선, 점, 다각형같은 공간데이터 객체 저장하고 조작

 

 -JSON

  ☞JSON 문서 저장

  ☞8바이트

 

 

3. 변수의 사용

 1)변수의 선언 및 사용

  -변수의 선언 및 값 대입

SET @변수이름 = 변수의 값;

  -변수의 값 출력

SET @변수이름;

예시)

SET @ExVar1 = 1212; //정수
SET @ExVar2 = 1.23; //실수
SET @ExVar3 = 'SQL Exercise Number = '; //문자열
 
SELECT @ExVar1; //1212 출력

 2)PREPARE / EXECUTE

  -쿼리문을 준비해놓고 변수 값을 대입하여 추후에 실행하도록 하는 구문

  -EXECUTE로 실행 후 재사용 하지 않을 경우 'DELLOCATE PREPARE'로 쿼리 할당 해제 필요=메모리 낭비 방지

 

-PREPARE

 PREPARE[쿼리이름] FROM[쿼리문]

  ☞쿼리이름+쿼리문만 입력

  ☞쿼리문에 '?' 로 나중에 입력될 값을 비워놓는다.

  ☞EXECUTE를 수행시켰을 때 실행된다.

 

-EXECUTE

EXECUTE[쿼리이름] USING@[변수이름]

  ☞쿼리문 '?'에 변수가 대입됨

 

예시)

SET @ExVar1 = 1212; //정수
 
PREPARE exQuery
FROM 'SELECT Attr1, Attr2 FROM ExTbl ORDER BY Attr2 LIMIT ?';
EXECUTE exQuery USING @ExVar1;

 

 

4. SQL JOIN

 -내부조인(INNER JOIN)

 ☞가장 많이 사용되는 조인

 ☞조인하는 릴레이션 사이의 교집합

 

 -왼쪽(오른쪽)조인(LEFT or RIGHT JOIN)

 ☞왼쪽(혹은 오른쪽) 테이블의 것은 모두 출력됨

 ☞매칭되지 않는 것은 NULL로 출력

LEFT

RIGHT

 

 -상호조인(FULL OUTER/CROSS JOIN)

 ☞한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인 시킴

 ☞테이블 조인 시 발생할 수 있는 모든 경우의 수 출력

 

 

5.저장프로시저(Stored Procedure)

 -쿼리문의 집합

 -어떠한 동작을 일괄 처리하기 위한 용도로 사용 

DELIMITER$$ --코드 삽입-- END$$

 ☞저장 프로시저의 코딩할 부분을 묶어줌

 ☞DELIMITER;로 코딩 부분 종료 알려줌

 

CALL 저장프로시저이름()

 ☞CREATE PROCEDURE로 생성한 저장 프로시저 호출하여 실행함

 ☞함수와 같은 역할

 

 -저장 프로시저 수정, 삭제

   ☞수정: ALTER PROCEDURE

   ☞삭제: DROP PROCEDURE

 

-매개 변수의 사용

 IN

 ☞원본 값은 저장 프로시저가 끝난 뒤에도 유지되어짐

 ☞지역변수 개념

 

 OUT 

 ☞저장 프로시저 내에서 변경될 수 있음

 ☞프로그램 시작시에 OUT 매개변수의 초기 값에 접근할 수 없음

 ☞초기값 접근 없이 변수값 변경용

 

 INOUT

 ☞IN과 OUT 매개변수를 결합

 ☞저장 프로시저는 INOUT 매개변수를 수정 한 뒤 새로운 값 다시 전달

 ☞초기값에도 접근 가능

 

프로시저 사용 예시)

DELIMITER $$ 
CREATE PROCEDURE 저장프로시저이름(IN 또는 OUT 파라미터) 
BEGIN
 
SQL 프로그래밍 코드 //실행할 코드 작성
 
END $$ 
DELIMITER ;
 
CALL 저장프로시저이름() 

 

 

6. IF...ELSE

-조건문

IF <BOOLEAN 표현식> THEN 
SQL 문장들 1 
ELSE SQL 문장들 2 
END IF;

-참, 거짓의 두 가지를 가지고 분기하게 되어 이중분기라 한다.

-문장이 두개 이상일 경우

  ☞BEGIN ... END로 묶어준다

-프로시저 안에서 IF ELSE문 사용시

  ☞변수를 정의하는 경우 @를 붙이지 않는다

    (프로시저 안에서 선언된 변수는 그 프로시저나 함수의 지역변수이기 때문)

 

예시)

DROP PROCEDURE IF EXISTS exProc: //기존에 만든적 있다면 삭제
DELIMITER $$
CREATE PROCEDURE exProc() 
BEGIN
 DECLARE ExVar1 INT; //변수선언 시 '@' 없음
 SET ExVar1 = 2019;
 
  IF ExVar1 = 2019 THEN
  SELECT 'BYE BYE 2019'
  ELSE
  SELECT 'WHERE ARE YOU FROM?'
  END IF;
 
END $$
DELIMITER;
 
CALL exProc(); //생성한 프로시저 호출

 

 

7. CASE

 -다중 분기

  ☞세 가지 이상 분기할 경우를 다중 분기

 -조건이 맞는 WHEN이 여러개더라도 먼저 조건이 만족하는 WHEN으로 처리된다

CASE 
 
WHEN 조건식 THEN
 SQL 구문 1 
WHEN 조건식 THEN 
 SQL 구문 2 
…..
WHEN 조건식 THEN 
 SQL 구문 100 
ELSE 
 SQL 구문 101 
 
END CASE;

 

예시)

DROP PROCEDURE IF EXISTS scoreProc: //기존에 만든적 있다면 삭제
DELIMITER $$
CREATE PROCEDURE scoreProc()
BEGIN
 DECLARE scoreVar1 INT; //변수선언 시 '@' 없음
 DECLARE credit CHAR(1);
 SET scoreVar1 = 99;
 CASE
   WHEN scoreVar1 >=90 THEN
   SET credit = '뚜뚠';
   WHEN scoreVar1 >=80 THEN
   SET credit = '뚜두둔';
   WHEN scoreVar1 >=70 THEN
   SET credit = '뚜두두뚠';
 END CASE;
 SELECT CONCAT('몇점?==>', scoreVar1), CONCAT('그렇다면==>', credit);
END $$
DELIMITER;
CALL scoreProc(); //생성한 프로시저 호출

 

 

8. WHILE과 ITERATE/LEAVE

 -WHILE문

  ☞조건식이 참인 동안에 계속 반복되는 반복문

WHILE 조건식 DO 
  SQL 명령문들 
END WHILE;

 

 -ITERATE/LEAVE

  ☞건너뛰고 싶거나, 반복문을 빠져나오고 싶은 경우 사용

  ☞ITERATE는 다른 프로그래밍 언어의 COUNTINUE와 비슷하다

  ☞LEAVE는 BREAK문과 비슷하다

 

예시)

DROP PROCEDURE IF EXISTS whileProc: //기존에 만든적 있다면 삭제
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
 DECLARE a INT; //1에서 100까지 증가할 변수
 DECLARE sum INT; //더한 값을 누적할 변수
 SET a = 1;
 SET hap = 0;
 
 myWhile: WHILE(a<<=100) DO
 IF(a%7=0)THEN
  SET a=a+1;
  ITERATE myWhile;
 END IF;
   SET sum = sum+i;
   IF(sum>1000) THEN
   LEAVE myWhile;
 END IF;
   SET a=a+1;
 END WHILE;
 
 SELECT sum;
 
END $$
DELIMITER;
CALL whileProc(); //생성한 프로시저 호출