[23/08/03] SQL (2)

📖 4. SQL 고급 문법

📌 4-1. MySQL의 데이터 형식

1. 정수형

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

자바의 byte, short, int, long 데이터 타입과 비슷함
타입을 지정할 때 UNSIGNED 예약어를 사용하면 숫자 범위가 0부터 시작됨

ex) TINYINT UNSIGNED 타입일 경우 0~255의 숫자 범위를 가짐

2. 문자형

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

CHAR는 고정길이 문자형, VARCHAR는 가변길이 문자형

ex) CHAR(10) 으로 지정된 컬럼에 3글자의 값을 저장하면 7자리는 남겨둠
VARCHAR(10) 으로 지정된 컬럼에 같은 값을 저장하면 3자리만 사용, 길이가 변경됨

VARCHAR가 CHAR보다 공간을 효율적으로 운영하지만, MySQL의 성능(속도)면에서는 CHAR를 사용하는 것이 좋음

2-1. 대량의 문자형

TEXT 타입 - TEXT (최대 65535자), LONGTEXT (최대 약 42억자)
BLOB 타입 - BLOB (최대 65535자), LONGBLOB (최대 약 42억자)
소설, 영화 대본 / 사진, 동영상 등의 데이터를 저장할 때 사용하는 타입

3. 실수형

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

자바의 float, double 데이터 타입과 비슷함

4. 날짜형

데이터 형식	바이트 수	설명
DATE	3	YYYY-MM-DD 형식
TIME	3	HH:MM:SS 형식
DATETIME	8	YYYY-MM-DD HH:MM:SS 형식

5. 변수

다른 프로그래밍 언어처럼 SQL에서도 변수를 선언해 사용 가능

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

-- 변수 선언하고 사용하기
SET @txt = '가수 이름 : ';
SET @height = 166;
SELECT @txt, mem_name FROM member WHERE height > @height;

6. 데이터 형변환 : CAST(), CONVERT() 함수 사용

CAST(값 AS 데이터타입[(길이)]);
CONVERT(값, 데이터타입[(길이)]);
-- 변경할 타입은 CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등 사용 가능

CONCAT() : 여러 개의 값을 이어주는 함수

SELECT num,
	CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=') '가격X수량',
    price*amount '구매액'
FROM buy;

암시적 형변환
함수를 사용하지 않아도 자동으로 형변환되는 것

SELECT '100'+'200';
-- 문자와 문자를 더할 수 없으므로 숫자로 자동 변환하여 덧셈 수행

SELECT CONCAT('100','200');
-- '100200'으로 만들기 위해서는 CONCAT() 함수 사용해야 함

📌 4-2. 두 테이블을 묶는 조인

조인(JOIN) 이란?
두 개의 테이블을 묶어서 하나의 결과를 만들어내는 것

두 테이블의 조인을 위해서는 테이블이 일대다 관계로 연결되어야 함
ex) A테이블의 기본 키가 B테이블의 외래 키의 참조 대상으로 사용되는 경우

1. 내부 조인 (INNER JOIN)

일반적인 조인은 내부 조인을 뜻함

SELECT 컬럼명
FROM 기준 테이블
	INNER JOIN 다른 테이블
    ON 조인될 조건
[WHERE 검색 조건]

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

buy 테이블(기준 테이블) 에서 아이디가 GRL인 레코드를 검색해 출력하되,
member 테이블(붙일 테이블)의 아이디 컬럼 값 중 buy 테이블의 아이디(GRL) 와 컬럼 값이 같은 레코드가 존재하면, 해당 레코드의 내용을 가져와 출력될 buy 테이블 옆에 붙여서 출력함

위 SQL에서 WHERE 절을 생략하면, buy 테이블의 모든 레코드가 member 테이블의 아이디가 같은 레코드와 결합하여 출력됨

이름이 같은 컬럼이 두 테이블에 모두 존재하는 경우,
어떤 테이블의 컬럼인지 명시해야 함
이 때 FROM, JOIN 뒤의 테이블명에 별칭을 지정해
테이블 이름이 오는 자리에 대신 쓸 수 있음

SELECT
	B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
FROM buy B
	INNER JOIN member M
    ON B.mem_id = M.mem_id;

buy 테이블에 B, member 테이블에 M이라는 별칭을 지정해 테이블명 대신 사용

내부 조인으로 만든 테이블은 두 테이블에 모두 있는 내용만 출력됨
위 테이블의 경우 buy 테이블에 구매 기록이 있는 member의 정보만 출력되고 있음
구매하지 않은 회원 정보도 검색하기 위해서는 외부 조인을 사용해야 함

2. 외부 조인 (OUTER JOIN)

내부 조인과 달리, 외부 조인은 한쪽에만 데이터가 있어도 결과가 나옴

SELECT 컬럼명
FROM 기준 테이블(LEFT 테이블)
	<LEFT | RIGHT | FULL> OUTER JOIN 다른 테이블(RIGHT 테이블)
    ON 조인될 조건
[WHERE 검색 조건]

-- 왼쪽 테이블(member) 을 기준으로 외부 조인
-- 왼쪽 테이블 레코드 하나하나가 ON의 조건을 체크하고 모두 출력되어야 함
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;

왼쪽 테이블 member의 레코드를 모두 출력하되,
member의 아이디가 오른쪽 테이블 buy에도 있을 경우 buy 테이블에 있는 레코드를 같이 출력함, 아이디가 같은 레코드가 여러 개일 경우 member의 동일한 레코드에 여러 번 붙여 모두 출력함

-- 구매 기록이 없는 회원 목록 추출하기
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;

기준 테이블 member의 레코드를 모두 출력하되, 오른쪽 테이블 buy를 같이 출력하고, buy 테이블의 상품명이 null인 (구매 기록이 없는) 레코드만 member의 아이디 순으로 출력

3. 기타 조인

3-1. 상호 조인

양쪽 테이블의 행끼리 모두 조인시킴, 테스트용 더미 데이터를 만들 때 사용
ex) 레코드가 10개인 A테이블과 12개인 B테이블 상호 조인 시 120개의 레코드 출력

SELECT *
FROM A테이블
CROSS JOIN B테이블;

3-2. 자체 조인

한 테이블이 자기 자신과 조인하는 것, 한 테이블에 다른 별칭을 사용해 서로 다른 테이블인 것처럼 조인함

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');
SELECT emp '직원', manager '직속상관', phone '연락처' FROM emp_table;

이 테이블에서 자체 조인을 사용해
직원, 직속상관, 직속상관의 연락처를 나란히 출력할 수 있음

SELECT A.emp '직원', B.emp '직속상관', B.phone '직속상관 연락처'
FROM emp_table A
    INNER JOIN emp_table B
    ON A.manager = B.emp;
-- 같은 테이블을 별칭만 다르게 해서 별개의 테이블인것처럼 사용해 조인

해당 테이블의 직속상관 컬럼은
직원(A.emp)의 직속상관(A.manager)과 이름이 같은 직원명(B.emp) 이고,
직속상관 연락처는 그 직원(B.emp)의 연락처(B.phone) 를 나타냄

📌 4-3. SQL 프로그래밍

스토어드 프로시저란?
MySQL에서 프로그래밍 기능을 담당하는 데이터베이스 개체 (메서드와 비슷)

DELIMITER $$ -- 시작
CREATE PROCEDURE 스토어드_프로시저_이름 -- 프로시저 만들기
BEGIN
	-- 실행할 내용
END $$
DELIMITER ; -- 끝
CALL 스토어드_프로시저_이름() -- 프로시저 실행

1. IF문

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

-- 스토어드 프로시저 만들기
DELIMITER $$
CREATE PROCEDURE ifProc1() -- 프로시저 만들기
BEGIN -- 실행할 내용
	IF 100 = 100 THEN
		SELECT '100은 100과 같다';
	END IF;
END $$ -- 실행할 내용 끝
DELIMITER ; -- 프로시저 끝
CALL ifProc1(); -- 프로시저 실행

100은 100과 같다

2. IF ~ ELSE문

-- 조건식이 참일 때와 거짓일 때 다른 내용 실행
IF <조건식> THEN
	참일 때 실행할 SQL문장
ELSE
	거짓일 때 실행할 SQL문장
END IF;

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

3. IF문의 활용

변수를 선언해 데뷔일 카운트하기

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, '일 지났습니다. 5년이 넘었습니다.') '카운트';
	ELSE
		SELECT '데뷔한 지 ' + days + '일 지났습니다. 5년이 넘지 않았습니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc3();

4. CASE문

여러 가지 조건 중에 해당되는 내용을 실행 (자바의 if-else if와 비슷)

CASE
	WHEN 조건1 THEN
    	SQL문장1
    WHEN 조건2 THEN
    	SQL문장2
    ELSE
    	SQL문장3
END 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 '회원등급'
    -- 회원등급 컬럼의 값이 될 내용을 CASE문으로 결정하기
FROM buy B
	RIGHT OUTER JOIN member M -- member 테이블 기준으로 join
    ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price*amount) DESC;

5. WHILE문

조건식이 참인 동안에 내용을 반복함

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

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

6. ITERATE, LEAVE문

ITERATE는 자바의 continue, LEAVE는 자바의 break와 비슷
반복문에 레이블 이름을 지정해 특정한 경우 지정한 레이블의 반복을 건너뛰거나 반복문을 빠져나오게 함

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