[MySQL] SQL 고급 문법

MySQL 의 데이터 형식

테이블을 만들 때에는 데이터 형식을 설정해야 합니다. 데이터 형식에는 크게 숫자형, 문자혀으 날짜형이 있습니다.

각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 저장할 수 있습니다.

데이터 형식

SQL에서 데이터 형식은 아래와 같이 나눌 수 있습니다.

• 정수형
• 문자형
• 실수형
• 날짜형

정수형 부터 살펴보겠습니다. 정수형에는 TINYINT, SMALLINT, INT, BIGINT 가 있고, BIT과 MEDIUMINT도 있으나 잘 사용하지 않는다고 합니다.

데이터 형식	바이트 수	숫자 범위
TINYINT	1	-128~127
SMALLINT	2	-32,768~32,767
INT	4	-2147483648~2147483647
BIGINT	8	약-900경~900경

표현할 수 있는 숫자 범위를 넘어갔을 때에는 Out of range 라는 오류가 발생합니다.

UNSIGNED

UNSIGNED는 값의 범위가 0부터 시작되게 하는 예약어 입니다.

CREATE TABLE test(
weight TINYINT,
height TINYINT UNSIGNED 
)

위 코드에서 weight와 height의 차이가 보이시나요?

weight 에는 -128~128의 수가 입력 될 수 있고, UNSIGNED 예약어를 사용한 height는

0~256까지 입력될 수 있습니다.

문자형은 글자를 저장하기 위해 사용하며 입력할 최대 글자 수를 정해야 합니다.

데이터 형식	바이트 수
CHAR(개수)	1~255
VARCHAR(개수)	1~16383

CHAR는 고정길이 문자형입니다. 예를 들어 CHAR(10)에 "딸기" 두 글자만 저장해도 10자리를 확보합니다.

이와 달리 VARCAHR는 가변길이 문자형으로 VARCHAR(10)에 "딸기" 두 글자를 저장하면 두 자리만 사용합니다.

데이터가 숫자여도 더하기/빼기 등의 연산에 의미가 없거나 크다/작다 또는 순서의 의미가 없다면 문자형으로 지정하는 것이 좋습니다. ex) 전화번호

대량의 데이터에 대한 데이터 형식도 존재합니다. TINYTEXT, MEDIUMTEXT, TINYBLOB, MEDIUMBLOB 등 도 있지만 잘 사용하지 않습니다.

데이터 형식		바이트 수
TEXT 형식	TEXT	1~65535
LONGTEXT	1~4294967295
BLOB 형식	BLOB	1~65535
LONGBLOB	1~4294967295

BLOB은 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상 등의 데이터 입니다. 이미지와 동영상 데이터를 저장하려면 LONGBLOB으로 데이터 형식을 지정해야 합니다.

실수형은 소수점이 있는 숫자를 저장할 때 사용합니다.

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리까지 표현
DOUBLE	8	소수점 아래 15자리까지 표현

날짜형은 날짜 및 시간을 저장할 때 사용합니다.

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장, YYYY-MM-DD 형식
TIME	3	시간만 저장, HH:MM:SS 형식
DATETIME	8	날짜 및 시간을 저장 YYYY-MM-DD HH:MM:SS 형식

변수의 사용

SQL 도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있습니다.

변수의 선언과 값의 대입은 다음 형식을 따릅니다.

SET @변수이름 = 변수의 값; -- 변수의 선언 및 값 대입
SELECT @변수이름 -- 변수의 값 출력

변수는 MySQL 워크벤치를 재시작할 때까지는 유지되지만, 종료하면 없어집니다. 그러므로 임시로 사용한다고 생각한다고 생각하면 됩니다.

PREPARE, EXECUTE

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

변수를 사용하여 행의 개수를 제한하려고 했지만, 위 쿼리를 실행하면 오류가 발생합니다.

이 때 사용는 것이 PREPAR와 EXECUTE 입니다.

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

PREPARE는 SELECT문을 실행하지 않고 mySQL이라는 이름으로 준비만 해놓습니다. LIMIT 다음에 오는 물음표는 현재는 모르지만 나중에 채워지는 값이라고 생각하시면 좋습니다.

EXECUTE로 MySQL문을 실행할 때 USING으로 물음표에 @count 변수의 값을 대입하여 PREPARE가 준비한 SELECT문을 실행기킵니다.

데이터 형 변환

데이터 형 변환은 말 그대로 서로 다른 데이터의 형식을 변환하는 것입니다.

형 변환에는 함수를 사용하여 변환하는 명시적 형 변환과

별도의 지시 없이 데이터 형이 변환되는 암시적 형 변환이 있습니다.

명시적 형 변환의 형식은 다음과 같습니다. CAST()와 CONVERT()는 형식만 다를 뿐 동일한 기능을 합니다.

CAST ( 값 AS 데이터형식 (길이)) -- (길이)는 생략 가능
CONVERT ( 값, 데이터형식 (길이)) -- (길이)는 생략 가능

CAST()나 CONVERT()에 올 수 있는 데이터 형식으로는 CHAR, SIGNED, UNSIGNED, DATE, TIME, DATATIME이 있습니다. SIGN은 부호가 있는 정수, UNSIGNED는 부호가 없는 정수 입니다.

** 두 테이블을 묶는 조인**

JOIN이란 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말합니다.

내부 조인

두 테이블을 연결할 때 가장 많이 사용되는 것이 내부 조인입니다.

내부조인의 형식은 다음과 같습니다.

SELECT <열 목록>
FROM <첫번째 테이블>
    INNER JOIN <두번째 테이블>
    ON <조인될 조건>
WHERE <검색 조건> -- 생략 가능

ex)

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

노란색으로 표시한 것이 buy의 열, 파란색으로 표시한 것이 member의 열 입니다

조인을 통하여 두 개의 테이블을 불러왔습니다.

위 코드의 4번째 줄을 보시면

<테이블이름.열이름> 으로 조인 조건을 썼습니다.

이 때 테이블 이름을 간결하게 표현할 수 있습니다.

위 코드처럼 <테이블이름> <줄인 이름>

을 통해 테이블 이름을 간결하게 표현할 수 있습니다

중복된 결과 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;

중복된 값 제거

외부 조인

외부 조인은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있습니다.

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

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
    ON <조인될 조건>
WHERE <검색 조건> -- 생략 가능

LEFT OUTER JOIN 의 의미는 첫 번째 테이블의 내용은 모두 출력되어야 한다는 의미입니다. RIGHT는 오른쪽, FULL은 양 쪽 어디든 한쪽에 들어있는 내용이면 출력합니다.

기타 조인

CROSS JOIN (상호조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능입니다.

상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 됩니다.

상호 조인의 형식은 다음과 같습니다

SELECT <열 목록>
FROM <첫 번째 테이블>
    CROSS <두 번째 테이블>

member 테이블의 행 개수는 10개 입니다.

따라서 buy 테이블의 한 행 당 10개의 행과 결합되어 총 120개(buy 테이블의 행이 12개)의 행이 만들어졌습니다.

num이 1인 값이 10개 존재하는 것을 보실 수 있습니다.

상호조인은

• ON 구문을 사용할 수 없습니다
• 결과의 내용은 의미가 없습니다. 왜냐하면 랜덤으로 조인하기 때문입니다.
• 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때 입니다.

SELF JOIN(자체조인)은 자신이 자신과 조인한다는 의미입니다.

자체 조인의 형식은 다음과 같습니다.

SELECT <열 목록>
FROM <테이블> 별칭1
    INNER JOIN <테이블> 별칭2
    ON <검색될 조건>
WHERE <검색 조건> -- 생략 가능

SQL 프로그래밍

스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체입니다.

SQL프로그래밍은 기본적으로 스토어드 프로기저 안에 만들어야 합니다.

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

DELIMETER $$
CREATE PROCEDUR 스토어드_프로스저_이름()
BEGIN
    -- 이 부분에 SQL 프로그래밍 코딩
END $$ -- 스토어드 프로시저 종료
DELIMETER ; -- 종료 문자를 다시 세미콜론으로 변경
CALL 스토어드_프로시저_이름(); -- 스토어드 프로시저 실행

IF문

IF문은 조건식이 참이라면 SQL문장들을 실행하고, 그렇지 않으면 그냥 넘어갑니다.

IF문의 형식은 다음과 같습니다.

IF <조건식> TEHN
    SQL문장들 -- 조건이 참
ELSE
    SQL문장들 -- 조건이 거짓 (생략 가능)
END IF;

ex)

결과로 100이 아닙니다를 출력합니다.

CASE문

여러가지 조건 중 선택해야 하는 경우 CASE 문을 사용합니다.

CASE 문의 형식은 다음과 같습니다.

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

ex)

취득점수 : 88 학점 : B

를 출력합니다.

WHILE문

WHILE 문은 조건식이 참인 동안에 SQL문장들을 계속 반복합니다.

WHILE 문의 형식은 다음과 같습니다.

WHILE 조건식
    SQL 문장들
END WHILE;

1부터 100까지의 값을 모두 더하는 간단한 기능을 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 -- i가 100 이하일 때 까지 반복
        SET hap = hap + i;
        SET i = i + 1;
    END WHILE;

    SELECT '1부터 100까지의 합 : ', hap;
END $$
DELIMITER ;

CALL whileProc();

다음 예제를 실행시키면 1부터 100까지의 합 : 5050이 출력됩니다

미션

기본미션

다음 SQL은 회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록입니다. 빈칸에 들어갈 가장 적합한 것은?

SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
FORM member M
    LEFT OUTER JOIN buy B
    ON M.mem_id = B.mem_id
    -- 빈칸 --
ORDER BY M.mem_id;

1. JOIN B.prod_name IS NULL
2. LIMIT B.prode_name IS NULL
3. HAVING B.prod_name IS NULL
4. WHERE B.prod_name IS NULL

빈칸에 들어갈 SQL문은 4번 입니다. 4번 문으로 B.prod_name 이 NULL인 것만 조회하면 한 번도 구매한 적이 없는 회원의 목록을 조회할 수 있습니다.

선택미션

선택미션은 위 중복된 결과 1개만 출력하기에서 더 자세하게 설명하였습니다.

DISTINCT 를 이용하여 중복된 항목 1개만 조회할 수 있습니다.