[DB] SQL 문법

SELECT ~ FROM ~ WHERE

SELECT는 구축이 완료된 테이블에서 데이터를 추출하는 기능을 한다. 아무리 많이 사용해도 기존 데이터가 변하지는 않는다.

가장 기본 형식은 SELECT ~ FROM ~ WHERE의 형태이며, 각 절의 역할은 다음과 같다.

SELECT: 뒤에 열 이름이 온다.
FROM: 뒤에 테이블 이름이 온다.
WHERE: 뒤에 조건문이 온다.

SELECT ~ FROM ~ WHERE 기본 구조

USE 문

SELECT 문을 실행하려면 먼저 사용할 데이터베이스를 지정해야 한다. 이때 USE를 사용한다.

SELECT 문의 전체 형식은 다음과 같다.

SELECT 열 이름
FROM 테이블 이름
WHERE 조건식
GROUP BY 열 이름
HAVING 조건식
ORDER BY 열이름
LIMIT 숫자

중복된 결과 제거: DISTINCT

DISTINCT는 조회된 결과에서 중복된 데이터를 1개만 남긴다.

GROUP BY 절

GROUP BY는 말 그대로 데이터를 그룹으로 묶어주는 역할을 한다.

집계 함수

함수명	설명
Sum()	합계를 구한다
Avg()	평균을 구한다
Min()	최솟값을 구한다
Max()	최댓값을 구한다
Count()	행의 개수를 구한다
Count(DISTINCT)	행의 개수를 센다 (중복은 1개만 인정)

HAVING 절

결과 중 총 구매액이 1000 이상인 회원에게만 사은품을 주려면 어떻게 해야 할까?

조건을 포함한 WHERE 절만으로는 안 된다. 집계 함수는 WHERE 절 안에 나올 수 없기 때문이다.

SELECT mem_id "회원 아이디", SUM(price*amount) "총 구매 금액"
   FROM buy
   GROUP BY mem_id   
   HAVING SUM(price*amount) > 1000 ;

이렇게 GROUP BY로 묶은 뒤 HAVING과 집계 함수를 사용해야 한다. (WHERE 절 대신 HAVING을 쓴다.)

정리

ORDER BY는 결과가 출력되는 순서를 조절한다.
LIMIT은 출력하는 개수를 제한하며, 주로 ORDER BY와 함께 사용한다.
DISTINCT는 조회된 결과에서 중복된 것을 1개만 남기며, 열 이름 앞에 붙인다.
GROUP BY는 데이터를 그룹으로 묶어주는 기능을 한다.
HAVING은 집계 함수와 관련된 조건을 제한하며, GROUP BY 다음에 나온다.

데이터 변경을 위한 SQL 문

데이터 입력: INSERT

테이블에 행 데이터를 입력하는 기본 SQL 문은 INSERT다.

INSERT INTO 테이블[(열1, 열2 ...)] VALUES (값1, 값2)

자동으로 증가하는 AUTO_INCREMENT

열을 정의할 때부터 1씩 증가하는 값을 자동으로 입력해준다. 주의할 점은, AUTO_INCREMENT(AI)로 지정한 열은 반드시 Primary Key로 지정해줘야 한다는 것이다.

덤) 시스템 변수

시스템 변수란 MySQL에서 자체적으로 가지고 있는, 설정값이 저장된 변수다. 주로 MySQL 환경과 관련된 내용이 저장되어 있으며, 그 개수는 500개 이상이다.

시스템 변수는 앞에 @@가 붙는 것이 특징이며, 값을 확인하려면 SELECT @@시스템변수를 실행하면 된다. 전체 시스템 변수를 보려면 SHOW GLOBAL VARIABLES를 실행한다.

다른 테이블의 데이터를 한 번에 입력: INSERT INTO ~ SELECT

많은 양의 데이터를 직접 타이핑하면 오래 걸린다. 다른 테이블에 이미 데이터가 입력되어 있으면 INSERT INTO ~ SELECT 구문으로 해당 테이블의 데이터를 가져와 한 번에 입력할 수 있다.

데이터 수정: UPDATE

행 데이터를 수정해야 하는 경우 사용한다.

WHERE가 없는 UPDATE 문

UPDATE에서 WHERE 절은 문법상 생략할 수 있지만, WHERE가 없으면 테이블의 모든 행의 값이 변경된다.

update city
set city_name = "서울"

위 쿼리를 실행하면 모든 도시가 서울로 바뀐다.

데이터 삭제: DELETE

행 데이터를 삭제하는 경우 사용한다.

대용량 테이블의 삭제

몇억 건의 데이터가 있는 테이블이 더 이상 필요 없어지면 어떻게 해야 할까? 삭제 방법은 크게 세 가지가 있다.

DELETE: 행 단위로 삭제하며 속도가 느리다. 몇억 건이면 훨씬 오래 걸린다. (하나 삭제에 2초라고 가정하면 엄청난 시간이 걸린다.)
DROP: 테이블 자체를 삭제한다.
TRUNCATE: DELETE와 동일한 효과(행 삭제)를 내지만 속도가 무척 빠르다.

DROP은 테이블이 아예 사라지지만, DELETE와 TRUNCATE는 빈 테이블을 남긴다.

정리

INSERT 문은 테이블에 데이터를 입력하는 명령어다.
AUTO_INCREMENT는 1부터 증가하는 값을 자동으로 입력해준다. 해당 열은 Primary Key로 지정해야 한다.
INSERT INTO ~ SELECT는 다른 테이블의 데이터를 가져와 한 번에 대량으로 입력한다.
UPDATE는 기존에 입력된 값을 수정하며, 주로 WHERE와 함께 사용한다.
DELETE는 행 단위로 삭제하며, WHERE가 없으면 전체 행이 삭제된다.

한글 용어	약자	설명
NULL		아무것도 없는 값. AUTO_INCREMENT 열에 입력
Primary Key	PK	기본 키. AUTO_INCREMENT 열은 기본 키로 지정해야 한다
Alter Table		테이블 구조를 변형하는 SQL
시스템 변수		MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수
@@Auto_increment_increment		AUTO_INCREMENT의 증가값을 지정하는 시스템 변수
Describe	DESC	테이블 구조를 확인하는 SQL
Truncate		DELETE와 비슷하지만 전체 행 삭제 시 사용

MySQL의 데이터 형식

테이블을 만들 때는 데이터 형식을 설정해야 한다. 데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있으며 세부적으로 더 나뉘기도 한다.

이렇게 다양한 데이터 형식이 있는 이유는 실제 저장될 데이터의 형태가 다양하기 때문이다.

데이터 형식

MySQL에서 제공하는 데이터 형식은 수십 개이고, 각 형식마다 크기나 표현할 수 있는 숫자의 범위가 다르다. 이를 모두 외울 필요는 없으나 자주 사용하는 것은 알아둬야 한다.

변수의 사용

SQL도 다른 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다. 변수의 선언과 값의 대입은 다음 형식을 따른다.

set @변수이름 = 변수의 값; -> 변수의 선언 및 값의 대입
select @변수 이름; -> 변수의 값 출력

MySQL에서는 @변수이름으로 변수를 만들고 SET 문으로 값을 대입한다.

그렇다면 LIMIT에도 변수를 쓰려면 어떻게 할까?

SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

이렇게 LIMIT에 변수를 바로 쓰면 에러가 난다. 이를 해결하는 것이 PREPARE와 EXECUTE다.

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

데이터 형변환

문자를 정수형으로 바꾸거나 정수를 문자로 바꾸는 것을 데이터의 형변환이라 부른다. 형변환에는 직접 함수를 사용해 변환하는 명시적 형변환과, 별도의 지시 없이 자연스럽게 변환되는 암시적 형변환이 있다.

함수를 이용한 명시적 형변환

데이터 형식을 변환하는 함수는 CAST(), CONVERT()다. 둘은 형식만 다를 뿐 동일한 기능을 한다.

CAST(값 AS 데이터 형식 [(길이)])
CONVERT(값, 데이터 형식 [(길이)])

SELECT CAST(AVG(price) AS SIGNED)  '평균 가격'  FROM buy ;
-- 또는
SELECT CONVERT(AVG(price) , SIGNED)  '평균 가격'  FROM buy ;

SQL 결과를 원하는 형태로 표현할 때도(SELECT 절) 사용할 수 있다. 예를 들어 가격과 수량을 곱한 실제 구매액을 표시하는 SQL을 작성할 수 있다.

암시적 형변환

CAST()나 CONVERT() 함수를 쓰지 않고도 자연스럽게 형이 변환되는 것을 말한다.

데이터 형식 정리

정수형은 소수점이 없는 숫자이며, TINYINT, SMALLINT, INT, BIGINT 등이 있다.
문자형은 고정형 문자형인 CHAR와 가변형 문자형인 VARCHAR가 있다.
실수형은 소수점 아래 7자리까지 표현하는 FLOAT과 소수점 아래 15자리까지 표현하는 DOUBLE이 있다.
MySQL에서 제공되는 변수 앞에는 @를 붙인다.

두 테이블을 묶는 조인

**조인(Join)**이란 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어내는 것을 말한다. 두 테이블이 연결되어야 원하는 형태가 나오는 경우가 많다. 인터넷 마켓의 회원 테이블과 구매 테이블이 그 예다.

내부 조인

두 테이블을 연결할 때 가장 많이 사용되는 조인이다. 일반적으로 그냥 “조인”이라 부르면 내부 조인을 의미한다.

일대다 관계의 이해

두 테이블을 조인하려면 테이블이 일대다(one to many) 관계로 연결되어야 한다.

데이터 테이블은 하나로 구성하기보다 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다. 이렇게 분리된 테이블은 서로 관계를 가진다. 대표적인 사례가 인터넷 마켓 데이터베이스의 회원 테이블과 구매 테이블이다.

회원 테이블의 아이디와 구매 테이블의 아이디는 일대다 관계다. 일대다란 한쪽 테이블에는 하나의 값만 존재하지만, 연결된 다른 테이블에는 여러 값이 존재할 수 있는 관계를 말한다.

예를 들어 회원 테이블에서 블랙핑크 아이디 BLK는 1명(1, one)밖에 없다. 그래서 회원 테이블의 아이디를 기본 키(PK)로 지정했다. 반면 구매 테이블의 아이디에서는 BLK를 3개 찾을 수 있다.

즉, 회원은 1명이지만 이 회원은 구매를 여러 번(many) 할 수 있다. 그래서 구매 테이블의 아이디는 기본 키가 아닌 외래 키(Foreign Key)로 설정했다.

회원-구매 테이블의 일대다 관계

일대다 관계는 주로 기본 키(PK)와 외래 키(FK)로 맺어져 있다. 그래서 일대다 관계를 PK-FK 관계라 부르기도 한다.

꼭 기본 키-외래 키 관계가 아니어도 가능한 조인이 있다. 상호 조인이 대표적이다. 상호 조인 이외의 조인은 기본 키-외래 키가 핵심 요소다.

내부 조인의 기본

일반적으로 조인이라 부르는 것은 내부 조인을 말하며, 조인 중 가장 많이 사용된다. 조인은 3개 이상의 테이블로도 가능하지만 대부분 2개로 조인한다.

SELECT 열 목록
FROM 첫번쨰 테이블
  INNER JOIN 두번쨰 테이블
  ON 조인될 조건
WHERE 검색조건

INNER JOIN을 그냥 JOIN이라고만 써도 INNER JOIN으로 인식한다.

SELECT *
   FROM buy
     INNER JOIN member
     ON buy.mem_id = member.mem_id
   WHERE buy.mem_id = 'GRL';

내부 조인 기본 실행 결과

내부 조인의 활용

전체 회원의 아이디, 이름, 제품, 주소를 출력해보자.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM buy B --별칭 부여
     INNER JOIN member M  --별칭 부여
     ON B.mem_id = M.mem_id
   ORDER BY M.mem_id;

내부 조인은 두 테이블 모두에 데이터가 있을 경우에만 조인된다. 한쪽에만 내용이 있는 경우까지 조인하려면 외부 조인을 사용해야 한다.

중복된 결과 1개만 출력하기

내부 조인이 양쪽 모두 나와서 유용한 경우도 있다. 만약 우리 사이트에서 한 번이라도 구매한 회원에게 감사장을 발송한다면, 내부 조인으로 추출한 회원에게만 안내문을 보내면 된다.

이때 중복된 이름은 필요 없으므로 DISTINCT 문으로 회원 주소를 조회할 수 있다.


SELECT DISTINCT M.mem_id, M.mem_name, M.addr
   FROM buy B
     INNER JOIN member M
     ON B.mem_id = M.mem_id
   ORDER BY M.mem_id;

외부 조인

내부 조인은 두 테이블에 모두 데이터가 있어야 결과가 나온다. 이와 달리 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

외부 조인의 기본

**외부 조인(Outer Join)**은 두 테이블을 조인할 때 필요한 내용이 한 테이블에만 있어도 결과를 추출할 수 있다. 자주 쓰지는 않지만 가끔 사용되므로 알아두면 유용하다.

외부 조인의 형식은 다음과 같다.

SELECT 열 목록
FROM 첫번쨰 테이블(LEFT 테이블)
  <LEFT|RIGHT|FULL> OUTER JOIN <두번째 테이블(RIGHT 테이블)>
  ON <조인 될 조건>
[WHERE 검색조건];

내부 조인과 사용법은 비슷하다. 내부 조인에서 해결하지 못한 “전체 회원의 구매 기록(구매 기록이 없는 회원 정보도 함께)” 출력을 외부 조인으로 만들 수 있다.


SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM member M
     LEFT OUTER JOIN buy B
     ON M.mem_id = B.mem_id
   ORDER BY M.mem_id;

LEFT OUTER JOIN은 줄여서 LEFT JOIN으로 써도 된다.

LEFT OUTER JOIN은 “왼쪽 테이블의 내용은 모두 출력되어야 한다”라고 해석하면 기억하기 쉽다.

RIGHT OUTER JOIN으로 동일한 결과를 출력하려면 단순히 왼쪽과 오른쪽 테이블의 위치만 바꿔주면 된다.


SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM buy B
     RIGHT OUTER JOIN member M  --left 조인에서 위치만 걍 바꾼거 ㅇㅇ
     ON M.mem_id = B.mem_id
   ORDER BY M.mem_id;

외부 조인의 활용

앞서 내부 조인으로 구매 기록이 있는 회원 목록을 추출해 감사문을 보냈다. 이번에는 반대로 회원가입은 했지만 한 번도 구매한 적이 없는 회원을 추출해보자.


SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
   FROM member M
     LEFT OUTER JOIN buy B
     ON M.mem_id = B.mem_id
   WHERE B.prod_name IS NULL
   ORDER BY M.mem_id;

한 번도 구매하지 않았으므로 조인된 결과의 물건 이름(prod_name)이 비어 있을 것이다. IS NULL 구문으로 NULL 값인지 비교하면, 한 번도 구매하지 않은 6명의 회원이 나온다.

구매 기록이 없는 회원 추출 결과

FULL OUTER JOIN은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이다. 왼쪽이든 오른쪽이든 한쪽에 들어간 내용이면 출력한다. 자주 사용하지는 않는다.

기타 조인

내부 조인이나 외부 조인처럼 자주 쓰지는 않지만 가끔 쓰는 조인으로 상호 조인과 자체 조인이 있다.

상호 조인

**상호 조인(Cross Join)**은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다.

상호 조인 결과의 전체 행 개수는 두 테이블 각 행 개수의 곱이 된다.

회원 테이블의 첫 행은 구매 테이블의 모든 행과 조인된다. 두 번째 행도 마찬가지로 구매 테이블의 모든 행과 결합된다. 이런 식으로 회원 테이블의 모든 행이 구매 테이블의 모든 행과 결합된다.

최종적으로 회원 테이블 10개 행과 구매 테이블 12개 행을 곱해 120개의 결과가 생성된다.

상호 조인의 결합 개념

상호 조인을 카티션 곱(cartesian product)이라고 부르기도 한다.

회원 테이블과 구매 테이블의 상호 조인은 다음과 같다.

SELECT *
   FROM buy
     CROSS JOIN member ;

상호 조인 실행 결과

상호 조인은 다음과 같은 특징을 가진다.

ON 구문을 사용할 수 없다.
결과의 내용은 의미가 없다. 랜덤 조인이기 때문이다. (예: BLK를 에이핑크, 잇지, 소녀시대 등과도 조인한다.)
주 용도는 테스트를 위해 대용량의 데이터를 생성할 때다.

예를 들어 샘플 데이터인 sakila의 인벤토리 테이블에는 4,581건, world의 city 테이블에는 4,079건이 있다. 이를 상호 조인하면 4,581 × 4,079 = 18,685,899건의 데이터가 나온다.

대용량 상호 조인 결과

진짜로 대용량 테이블을 만들려면 CREATE TABLE ~ SELECT 문을 사용한다. 앞서 만든 2개의 테이블은 크기가 너무 커서 실습 시간이 오래 걸리므로, 약간 작은 테이블을 생성해서 5건을 조회해보자.


CREATE TABLE cross_table
    SELECT *
       FROM sakila.actor
          CROSS JOIN world.country;

SELECT * FROM cross_table LIMIT 5;

CREATE TABLE ~ SELECT로 생성한 테이블 조회

자체 조인

내부 조인, 외부 조인, 상호 조인은 모두 2개의 테이블을 조인했다. **자체 조인(Self Join)**은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.

별도의 문법이 있는 것은 아니고, 1개의 테이블로 조인하면 자체 조인이 된다.

자체 조인 개념

실무에서 자체 조인을 많이 쓰지는 않지만, 대표적 사례로 회사의 조직 관계를 들 수 있다. 다음은 회사의 조직 관계를 표현한 것이다.

직책을 기본 키로 둔(원래라면 사번이나 이름) 예시다.

회사 조직도

조직도를 테이블로 표시한 것으로, 관리 이사는 직원이므로 직원(EMP) 열에 속한다. 그러면서 동시에 경리부장, 인사부장의 상관이므로 직속 상관(MANAGER) 열에도 속한다.

만약 경리부장의 직속 상관인 관리 이사의 사내 연락처를 알고 싶다면 EMP 열과 MANAGER 열을 조인해야 한다.

EMP 열과 MANAGER 열의 조인

직원 테이블을 먼저 만들고, 데이터도 표와 동일하게 입력한다.

CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(8));

INSERT INTO emp_table VALUES('대표', NULL, '0000');
INSERT INTO emp_table VALUES('영업이사', '대표', '1111');
INSERT INTO emp_table VALUES('관리이사', '대표', '2222');
INSERT INTO emp_table VALUES('정보이사', '대표', '3333');
INSERT INTO emp_table VALUES('영업과장', '영업이사', '1111-1');
INSERT INTO emp_table VALUES('경리부장', '관리이사', '2222-1');
INSERT INTO emp_table VALUES('인사부장', '관리이사', '2222-2');
INSERT INTO emp_table VALUES('개발팀장', '정보이사', '3333-1');
INSERT INTO emp_table VALUES('개발주임', '정보이사', '3333-1-1');

자체 조인의 형식은 다음과 같다. 테이블이 1개지만 서로 다른 별칭을 써서 서로 다른 테이블처럼 사용한다.

SELECT <열 목록>
FROM <테이블> 별칭 A
  INNER JOIN <테이블> 별칭 B
  ON <조인될 조건>
[WHERE 검색 조건]

자체 조인을 활용해보자. 경리부장 직속 상관의 연락처를 알고 싶다면 다음 SQL을 사용한다. emp_table을 emp_table A, emp_table B로 별칭을 지정해 각각 별개의 테이블처럼 사용한다.

SELECT A.emp "직원" , B.emp "직속상관", B.phone "직속상관연락처"
   FROM emp_table A
      INNER JOIN emp_table B
         ON A.manager = B.emp
   WHERE A.emp = '경리부장';

자체 조인 실행 결과

즉, 다음 그림과 같이 2개의 테이블이 조인되는 것처럼 구성된다. 이렇듯 하나의 테이블에 같은 데이터가 2개 이상의 열로 존재할 때 자체 조인을 할 수 있다.

자체 조인의 구성 원리

자체 조인은 하나의 테이블에 서로 다른 별칭을 붙여서 조인하는 것이다.

정리

일대다 관계란 한쪽 테이블에는 하나의 값만, 다른 쪽 테이블에는 여러 개의 값이 존재할 수 있는 관계다.
조인은 두 개의 테이블을 서로 묶어 하나의 결과를 만들어내는 것이다.
내부 조인은 두 테이블을 조인할 때 두 테이블 모두에 지정한 열의 데이터가 있어야 한다.
외부 조인은 두 테이블을 조인할 때 1개의 테이블에만 데이터가 있어도 결과가 나온다.
상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다.
자체 조인은 자신이 자신과 조인된다는 의미로, 1개의 테이블을 사용한다.

한글 용어	설명
관계	두 테이블이 서로 연관되는 것
기본 키-외래 키 관계	두 테이블이 일대다 관계로 연결되기 위한 조건
별칭(alias)	조인에서 테이블의 이름을 짧게 표현하는 이름
DISTINCT 문	중복된 열의 값을 1개만 표현하는 구문
LEFT OUTER JOIN	왼쪽 테이블의 모든 값이 출력되는 조인
RIGHT OUTER JOIN	오른쪽 테이블의 모든 값이 출력되는 조인
FULL OUTER JOIN	왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인
CREATE TABLE ~ SELECT	SELECT의 결과가 테이블로 생성되는 구문

SQL 프로그래밍

**스토어드 프로시저(Stored Procedure)**는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 객체다. SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만든다.

스토어드 프로시저는 다음과 같은 구조를 갖는다.

스토어드 프로시저의 기본 구조

일반적으로 구분 문자(DELIMITER)는 $$를 많이 사용하지만, 원한다면 /, &, @ 등을 사용해도 된다. 다른 기호와 중복될 수 있으므로 기호 2개를 연속해서 쓰는 것이 좋다.

스토어드 프로시저는 DELIMITER $$ ~ END $$ 안에 작성하고 CALL로 호출한다.

IF 문

프로그래밍의 조건문을 그대로 쓴다고 보면 된다. 조건이 참이면 “SQL 문장들”을 실행하고, 그렇지 않으면 그냥 넘어간다.

IF 문의 흐름

기본 IF 문의 형식은 다음과 같다.

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

여기서 ‘SQL 문장들’이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상을 처리해야 한다면 BEGIN ~ END로 묶어줘야 한다. 현재 한 문장이더라도 나중에 추가될 수 있으니 습관적으로 BEGIN ~ END로 묶는 것이 좋다.


DROP PROCEDURE IF EXISTS ifProc1; -- 기존에 만든적이 있다면 삭제
DELIMITER $$  -- 세미콜론으로는 SQL의 끝인지 , 스토어드 포르시저의 끝인지 구할 수 없어서 $$를 사용한다.
CREATE PROCEDURE ifProc1()  --스토어드 프로시저의 이름을 ifProc1()로 지정
BEGIN --조건식으로 100과 100이 같은지 실행. 이 경우 참이니 아래 행들이 실행된다.
   IF 100 = 100 THEN  
      SELECT '100은 100과 같습니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc1(); --CALL로 호출하면 ifProc1()이 실행된다.

IF 문 실행 결과

다른 프로그래밍 언어에서는 같다는 의미로 ==를 사용하지만, SQL은 =를 사용한다. 그리고 SELECT 뒤에 문자가 나오면 그냥 화면에 출력해준다. 다른 언어의 print()와 비슷한 기능을 한다.

IF ~ ELSE 문

조건에 따라 다른 부분을 실행한다. 조건이 참이면 SQL 문장1을, 거짓이면 SQL 문장2를 실행한다.

IF ~ ELSE 문의 흐름

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE; -- 데뷰일
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수

    SELECT debut_date INTO debutDate -- debut_date 결과를 hireDATE에 대입(into를 넣었기 때문)
       FROM market_db.member
       WHERE mem_id = 'APN';

    SET curDATE = CURRENT_DATE(); -- 현재 날짜
    SET days =  DATEDIFF(curDATE, debutDate); -- 날짜의 차이, 일 단위

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

날짜 관련 함수

MySQL은 날짜에 관련된 함수를 여럿 제공한다.

CURRENT_DATE(): 오늘 날짜
CURRENT_TIMESTAMP(): 오늘 날짜와 시간을 함께 알려준다
DATEDIFF(날짜1, 날짜2): 날짜2부터 날짜1까지 며칠인지 알려준다

CASE 문

여러 조건 중 하나를 선택해야 하는 경우 CASE 문을 사용한다.

CASE 문의 흐름

SQL의 CASE 문은 다른 프로그래밍 언어의 SWITCH ~ CASE 문과 비슷한 기능을 한다.

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

CASE와 END CASE 사이에는 여러 조건을 넣을 수 있다. WHEN 다음에 조건이 나오는데, 조건이 여러 개라면 WHEN을 여러 번 반복하고, 모든 조건에 해당하지 않으면 ELSE를 수행한다.

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();

WHILE 문

조건이 참인 동안 계속 반복한다.

WHILE 문의 흐름

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

위와 같은 형태로 사용한다.


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 문을 쓸 수 있다.

ITERATE / LEAVE 문의 흐름

ITERATE [레이블]: 지정한 레이블로 가서 계속 진행한다.
LEAVE [레이블]: 지정한 레이블을 빠져나간다. 즉, WHILE 문이 종료된다.



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

    myWhile:
    WHILE (i <= 100) DO  -- While문에 label을 지정
       IF (i%4 = 0) THEN
         SET i = i + 1;     
         ITERATE myWhile; -- 지정한 label문으로 가서 계속 진행
       END IF;
       SET hap = hap + i;
       IF (hap > 1000) THEN
         LEAVE myWhile; -- 지정한 label문을 떠남. 즉, While 종료.
       END IF;
       SET i = i + 1;
    END WHILE;

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

동적 SQL

SQL 문은 내용이 고정되어 있는 경우가 대부분이다. 하지만 상황에 따라 내용을 변경해야 할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용할 수 있다.

PREPARE와 EXECUTE

PREPARE는 SQL 문을 실행하지 않고 미리 준비만 해둔다.
EXECUTE는 준비한 SQL 문을 실행한다.
실행 후에는 DEALLOCATE PREPARE로 문장을 해제하는 것이 바람직하다.

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;

정리

IF 문은 조건식이 참일 때 수행하는 IF와, 참·거짓일 때 각각 실행하는 IF ~ ELSE가 있다.
변수는 DECLARE로 선언하고 SET으로 값을 대입한다.
CASE 문은 2가지 이상의 조건을 처리할 수 있다. CASE를 다중 분기라고도 부른다.
WHILE 문은 조건식이 참인 동안 계속 반복한다. 계속 실행하는 ITERATE 문과 빠져나가는 LEAVE 문을 함께 사용할 수 있다.
PREPARE는 SQL 문을 실행하지 않고 미리 준비하고, EXECUTE는 준비한 SQL 문을 실행한다. 이러한 방식을 동적 SQL이라 한다.