[혼공S] 3주차_데이터 타입, 조인, 조건문, 동적 SQL

뚜둥! \_へ(▭-▭)✨ 오늘도 힘내 봅시다~!

 

MySQL의 데이터 형식

정수형

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

이름 귀엽ㅎㅎ

+ 잘 사용하지 않는 정수형

BIT(n)	(n + 7) / 8
MEDIUMINT	3	-8,388,608 ~ 8,388,607

 

error message

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

 

+ UNSIGNED

음수 값을 제외하고 양수 값만 저장 (동일한 바이트 크기 내에서 더 큰 양수 범위를 표현)

ex) TINYINT UNSIGNED 의 숫자 범위 : 0 ~ 255

 

문자형

데이터 형식	바이트 수	길이
CHAR(개수)	1 ~ 255	고정 길이
VARCHAR(개수)	1 ~ 16,383	가변 길이

+ 있지만 잘 사용하지 않는 문자형

BINARY	1 ~ 8,000	고정 길이
VARBINARY	1 ~ 8,000	가변 길이

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

 

대량의 데이터 형식

데이터 형식		바이트 수
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 ) : 이진 데이터. 이미지, 동영상 등

 

실수형

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

 

날짜형

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

 

---

변수의 사용

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

 

SET @txt = '가수 이름==> ';
SET @height = 166;
SELECT @txt, mem_name FROM member WHERE height > @height;

-- 가수 이름==> 소녀시대
-- 가수 이름==> 잇지
-- 가수 이름==> 트와이스

 

LIMIT 에서 변수 사용하기 

SET @count = 3;
PREPARE soriSQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
-- 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?'
EXECUTE soriSQL USING @using;
-- 'SELECT mem_name, height FROM member ORDER BY height LIMIT 3'
DEALLOCATE PREPARE soriSQL; -- SQL 쿼리를 해제

PREPARE : 'SELECT ~ LIMIT ?'문을 실행하지 않고 soriSQL에 저장하여 준비

? : 현재는 모르지만 나중에 채워짐

USING : ? 에 변수의 값을 대입

EXECUTE : soriSQL 실행

DEALLOCATE PREPARE : soriSQL 메모리 해제

---

데이터 형 변환

형 변환(type conversion) - 데이터 형식을 바꾸는 것

명시적인 변환(explicit conversion) - 직접 함수를 사용햇 변환

암시적인 변화(implicit conversion) - 별도의 지시 없이 자연수럽게 변화

 

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

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

* CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등 데이터 형식이 올 수 있다.

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

 

암시적인 변환

SELECT CONCAT(100, '200');    -- 100200
SELECT CONCAT(100, 200);      -- 100200
SELECT CONCAT('100', '200');  -- 100200
-- 문자로 변환하여 연결

SELECT 100 + '200';   -- 300
SELECT 100, 200;      -- 300
SELECT '100', '200';  -- 300
-- 자동으로 숫자로 변환하여 계산

CONCAT : 문자열을 결합

 

두 테이블을 묶는 조인

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

 

내부 조인 (inner join)

일대다 관계의 이해

→ 서로 관계를 맺고 있는 테이블의 한쪽은 하나의 값만 존재(기본 키)해야 하지만 연결된 다른 테이블에는 여러개의 값이 존재(외래 키)할 수 있는 관계.

 

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

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

→ market_db의 buy테이블을 member테이블과 조인, buy테이블이 mem_id값고 member테이블의 mem_id값이 같은 행 끼리, buy테이블의 mem_id가 'GRL'인 것만

 

내부 조인의 간결한 표현

-- 아이디/이름/구매 물품/주소/연락처만 출력

SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) '연락처'
-- buy테이블의 mem_id를 명확히 지정
    FROM buy
        INNER JOIN member
        ON buy.mem_id = member.mem_id;

-- 별칭을 붙여서 사용
-- buy = B

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

---

외부 조인 (outer join)

* 한쪽에만 데이터가 있어도 결과나 나온다.

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

 

LEFT OUTER JOIN : 왼쪽 테이블의 내용을 출력

RIGHT OUTER JOIN : 오른쪽 테이블의 내용을 출력

FULL OUTER JOIN : 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것

---

기타 조인

상호 조인(cross join)

→ 한쪽 테이블의 모든 행마다 다른 쪽 테이블의 모든 행을 조인시키는 기능

 

SELECT *
    FROM buy
        CROSS JOIN member;
-- buy테이블의 모든 행에 member테이블을 연결

* ON 구문을 사용할 수 없다.

* 랜덤으로 조인하기 때문에 결과 내용에 의미가 없다.

* 대용량의 테이터를 생성할 때 테스트하기 위한 용도

 

자체 조인(self join)

→ 자신이 자신과 조인

 

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

* 별칭을 사용하여 서로 다른 것처럼 사용

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

A의 emp이 '경리부장'인 행의 manager과 B의 emp이 같은 행을 조인. A의 emp를 '직원', B의 emp를 '직속상관', B의 phone를 '직속상관연락처'로 출력

 

SQL 프로그래밍

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

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

 

IF 문

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

* 비교 연사자 = 를 사용

 

IF ~ ELSE 문

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

→ 조건식이 False 면 ELSE의 SQL 문장을 실행

 

IF 문의 활용 

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의 값을 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();

DECLARE : 예약어 선언

---

CASE 문 (다중 분기)

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

* WHEN에 해당하지 않은 조건은 ELSE를 수행

 

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;

→ buy 테이블과 member 테이블을 외부 조인 member테이블의 mem_id, mem_name, buy 테이블의 price*amount 값을 더한 총구매액, 조건에 맞는 회원등급을 출력

 

---

WHILE 문

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

DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN 
	DECLARE i INT;
	DECLARE hap INT;
	SET i = 1;
	SET hap = 0;
    
	myWhile: -- WHILE문을 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();

→ 1부터 100까지의 합(4의 배수 제외)이 1000 이상이 되면 종료

 

ITERATE [레이블] : 지정한 레이블로 가서 계속 진행(continue)

LEAVE [레이블] : 지정한 레이블을 빠져나감. WHILE문 종료(break)

 

---

동적 SQL

PREPARE myQuery FROM SQL문장;   // 준비
EXECUTE myQuery;                             // 실행
DEALLOCATE PREPARE myQuery;    // 해제

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);
SET @curDate = CURRENT_TIME();
PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;
SELECT * FROM gate_table;

→ gate_table를 만든다. 현재 날짜와 시간을 가지고 있는 변수 @curDate를 만든다. myQuery에 'INSERT INTO gate_table VALUES(NULL, ?)'를 저장하여 준비한다. ?에 @curDate의 값을 넣고 실행한다. myQuery이 메모리를 해제한다. gate_table의 모든 내용을 출력한다. 

 

---

오늘의 과제

-195p 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
    // 빈칸                                                      
    ORDER BY M.mem_id;

WHERE B.prod_name IS NULL

 

+ 추가 과제 (183p 손코딩)

DISTINCT 문을 활용해서 회원의 주소를 조회