(3주차) Chapter 04

내용 정리

Chapter 04. SQL 고급 문법

04-1. MySQL의 데이터 형식

 

1) 데이터 형식

1)-1. 정수형

 

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

- Out of range : 입력값의 범위를 벗어남.

- UNSIGNED : 0이상의 양수

 

1)-2. 문자형

 

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

- CHAR, VARCHAR로만 쓰면 CHAR(1), VARCHAR(1)과 동일

- 데이터가 숫자 형태라도 연산이나 크기에 의미가 없다면 문자형으로 지정하는 것이 좋음.

 

1)-3. 대량의 데이터 형식

 

데이터 형식		바이트 수
TEXT 형식	TEXT	1 ~ 65,535
	LONGTEXT	1 ~ 4,294,967,295
BLOB 형식	BLOB	1 ~ 65,535
	LONGBLOB	1 ~ 4,294,967,295

- BLOB(Binary Long Object) : 글자가 아닌 이미지, 동영상 등의 데이터라고 생각, 이진(binary) 데이터라고도 부름.

 

1)-4. 실수형

 

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

 

1)-5. 날짜형

 

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

 

2) 변수의 사용

 

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

 

* SELECT문에서 행의 개수를 제한하는 LIMIT에도 변수를 사용해봤을 때 오류가 나타남.

LIMIT에는 변수를 사용할 수 없기 때문에 문법상 오류임.

이를 해결하는 것이 PREPARE과 EXECUTE임.

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

 

3) 데이터 형 변환

3)-1. 명시적인 변환

 

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

 

3)-2. 암시적인 변환

 

SELECT '100'+'200';
SELECT 100+'200';
-- 300

SELECT CONCAT('100', '200');
SELECT CONCAT(100, '200');
-- 100200

 

---

 

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

 

조인(join) : 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 의미

 

1) 내부 조인

1)-1. 일대다(one to many) 관계

 : 기본키(PK)-외래키(FK) 관계라고도 함.

 : PK-FK 관계가 아니어도 가능한 조인이 있음. 상호 조인이 대표적.

 

1)-2. 내부 조인

 : 일반적으로 조인이라고 부르는 것은 내부 조인을 말하는 것임.

 : 3개 이상의 테이블로도 할 수 있지만 대부분은 2개로 조인함.

 

1)-3. 내부 조인 문법

 

SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블>
ON <조인될 조건>
[WHERE 검색 조건]

 

1)-4. 약칭(alias) 사용

 

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

 

1)-5. 중복된 결과 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;

 

2) 외부 조인

 : 내부 조인은 두 테이블에 모두 있는 내용만 조인하는 방식

 : 양쪽 중에 한곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 함.

 

2)-1. 외부 조인 문법

 

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

 

2)-2. LEFT(RIGHT) OUTER JOIN의 의미

 : LEFT(RIGHT)에 있는 내용은 모두 출력되어야 한다.

 

2)-3. FULL OUTER JOIN의 의미

 : 왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력

 

2)-4. IS NULL

 

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;

 

3) 기타 조인

3)-1. 상호 조인(cross join, cartesian product)

 : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 함.

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

 

(1) ON 구문을 사용할 수 없음.

(2) 결과의 내용은 의미가 없음. 랜덤으로 조인하기 때문.

(3) 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때임.

 

3)-2. 상호 조인 문법

 

SELECT <열 이름>
FROM <테이블1>
CROSS JOIN <테이블2>;

 

3)-3. CREATE TABLE ~ SELECT

 : SELECT 구문을 사용해서 TABLE 생성 시 사용.

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

 

3)-4. 자체 조인(self join)

 : 자신이 자신과 조인한다는 의미

 : 테이블이 1개지만 다른 별칭을 사용해서 서로 다른 것처럼 사용하면 됨.

 : 자체 조인 문법

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

 

---

 

04-3. SQL 프로그래밍

 

1) 스토어드 프로시저(Stored procedure)

 : MySQL에서 프로그래밍 기능이 필요할 때 사용하는 DB 개체

 : SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 함.

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

 

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	이 부분에 SQL 프로그래밍 코딩
END $$
DELIMITER ;

CALL 스토어드_프로시저_이름();

 

2) IF

2)-1. IF문 문법

 : SELECT 뒤에 문자가 나오면 그냥 화면에 출력해줍니다. 다른 언어의 print()와 비슷한 기능을 함.

 

-- 조건이 하나일 경우
IF <조건식> THEN
	SQL 문장들
END IF;

-- 조건이 여러 개일 경우
-- BEGIN ~ END로 묶어줘야 함.

DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
IF 100=100 THEN
	SELECT '100은 100과 같습니다.';
END IF;
END $$
DELIMITER ;

CALL ifProc1();

 

2)-2. IF문의 활용

 : SELECT INTO는 결과를 print하는 것이 아닌 변수에 저장

 : DECLARE는 변수 선언에 사용됨.

 : CURRENT_DATE()는 오늘 날짜를 알려줌.

 : CURRENT_TIMESTAMP()는 오늘 날짜 및 시간을 함께 알려줌.

 : DATEDIFF(날짜1, 날짜2)는 날짜2부터 날짜1까지 일수로 몇 일인지 알려줌. 

 

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 '데뷔한 지 ' + days + '일 밖에 안되었네요. 핑순이들 화이팅~';
END IF;
END $$
DELIMITER ;
CALL ifProc3();

 

3) CASE

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

 

3)-1. CASE문 문법

 

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

 

3)-2. 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();

 

3)-3. CASE문의 활용2 : 우수고객

 

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 "회원등급"

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;

 

4) WHILE

4)-1. WHILE문 문법

 

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

 

4)-2. WHILE문의 활용 : 1~100까지 더하기

 

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

 

4)-3. ITERATE, LEAVE

 : ITERATE는 프로그래밍 언어의 continue와 LEAVE는 BREAK와 비슷함.

 

-- 1부터 100까지의 합(4의 배수 제외), 1000이 넘으면 종료

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

 

5) 동적 SQL

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

 : 미리 SQL을 준비한 후에 나중에 실행하는 것을 동적 SQL이라고 함.

 

5)-1. PREPARE, EXECUTE

 : PREPARE는 SQL문을 실행하지는 않고 미리 준비만 해놓고, EXCUTE는 준비한 SQL문을 실행함.

 : 실행 후 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직함.

 

USE market_db;
PREPARE myQuery FROM 'SELECT * FROM member FROM mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

 

5)-2. 동적 SQL의 활용

 : 일반 SQL에서 변수는 @변수명으로 지정하는데 별도의 선언은 없어도 됨.

 : 스토어드 프로시저에서 변수는 DECLARE로 선언한 후에 사용해야 함.

 

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;

 

기본 미션

 

p. 195 확인문제 4번 풀고 인증하기

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

 

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

WEHRE B.prod_name IS NULL

ORDER BY M.mem_id;

 

선택 미션

 

p. 183 [좀 더 알아보기] 손코딩 실행하고 결과화면 인증하기