[혼공SQL] 3주차 - Ch4 SQL 고급 문법

1) MySQL의 데이터 형식

데이터 형식

MySQL 에서 제공하는 데이터 형식 기준이다.

1. 정수형

정수형은 소수점이 없는 숫자 ➡️ 주로 가격, 수량 등에 사용

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

이해에 어려움 X, 영어 그대로 사이즈가 달라지는 것

정수의 표현, 2의 보수, UNSIGNED 에 대한 정리 ➡️ 예시로 TINYINT 설명

• TINYINT는 1바이트로 8개의 비트를 가진 데이터 형식
• 즉, 2^8 = 256 까지 표현 가능
• 음수를 어떻게 포함했나요????? ➡️ 2의 보수

2의 보수에 대해 GPT 엉님에게 설명해달라 했다

➡️ 신장(키) 같은 데이터는 양수만 필요하니 UNSIGNED 사용

더 자세히 공부하고 싶다면 이 블로그를 참고

[예시]

USE market_db;
CREATE TABLE hongong4(
	tinyiny_col TINYINT,
    smallint_col SMALLINT,
    int_col INT UNSIGNED, -- 양수만 ( 0~255 )
    bitint_col BIGINT );

INSERT INTO hongong4 VALUES(127, 32767, 214783647, 900000000000);

만약, 범위 밖의 정수를 대입하면 아래와 같은 오류가 발생하니 주의

2. 문자형

문자형은 글자 저장용, 최대 글자수 지정 필수

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

CHAR 🆚 VARCHAR

자료형	장점	단점
CHAR(개수)	내부 성능(속도)면에서 더 효율적	고정형이라 메모리 이슈 발생 가능
VARCHAR(개수)	가변형이라 메모리 관리에 효율적	CHAR에 비해 느림

관습적으로 VARCHAR 형은 VARCHAR(255)를 많이 사용한다

한번도 왜인지 생각 안해봤는데 배우는 김에 검색해봤다
➡️ VARCHAR는 만약 20바이트를 저장하겠다 하면 문자의 길이를 저장하기 위해 1바이트를 더 할당한다 -> 21바이트 저장한단 의미
그런데 256바이트 부터는 2바이트를 할당해야하기 때문에 1바이트를 더 할당해야한다 -> 메모리 + 관습(MySQL 이전 버전)으로 인해 255로 사용
출처

[예시]

CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY, --회원 아이디(PK)
 mem_name VARCHAR(10) NOT NULL, --이름
 mem_number TINYINT NOT NULL, --인원수
 addr CHAR(2) NOT NULL, --주소 (2글자만 입력 가능)
 phone1 CHAR(3) --연락처의 국번
 phone2 CHAR(8) --연락처의 나머지 (하이픈 - 제외)
 height TINYINT UNSIGNED --키

아아아아아아아아아니 왜 전화번호가 CHAR인가요?????????

• 정수형 앞에 0이 붙으면 0은 사라진다
• 연산에 의미 X : 전화번호로 더하기 빼기 할 이유 없음
• 순서에 의미 X : 전화번호 순으로 나열할 일이 적음

이러한 이유로 전화번호는 주로 VARCHAR, CHAR를 사용.

3. 대량의 데이터 형식

영화 자막 같은 대량 데이터는 어떻게 저장하나요

데이터 형식(열 2개 병합)	바이트 수
TEXT 형식(행2개 병합)	TEXT	1~65535
LONGTEXT	1~4294967295
BLOB 형식(행2개 병합)	BLOB	1~65535
LONGBLOB	1~4294967295

• TEXT : CHAR,VARCHAR보다 많이 표현 가능 (자막, 연설문 등)
• BLOB : Binary Long OBject의 약자. 이진 데이터를 저장할 때 사용 (사진, 동영상 등)

[예시]

CREATE DATABASE netflix_db;
USE netflix_db;
CREATE TABLE movie
  (movie_id		  INT,
   movie_title    VARCHAR(30),
   movie_director VARCHAR(20),
   movie_star     VARCHAR(20),
   movie_script	  LONGTEXT,
   movie_film     LONGBLOB
)

자막은 LONGTEXT로 영상은 LONGBLOB으로 데이터 형식을 정함

4. 실수형

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

주로 FLOAT 사용, 과학 데이터 같이 소수점이 정말 길지 않은 이상 FLOAT로 모두 커버가 가능함

5. 날짜형

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

별 내용 없지만, 예전 프로젝트 진행할 때 에러 발생했던 기억에 다시 정리

자바의 자료형 중에 LocalDate 와 LocalDateTime이 있는데 설정한 자료형에 따라 MySQL의 자료형도 맞춰줘야한다!!!!!

---

변수의 사용

MySQL도 다른 프로그래밍 언어처럼 변수 선언 및 사용이 가능

SET @myVar1 = 5;
SET @myVar2 = 5.15;

SELECT @myVar1 + @myVar2; --10.150000000

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

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

LMIT 과 함께 사용하면 에러 발생 -> 동적 쿼리 사용

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

• 변수 count에 3 할당
• mySQL 이라는 이름의 쿼리를 PREPARE(준비)
  • '?'는 EXECUTE(실행)할 때 변수를 넣어달란 의미
• mySQL 이라는 이름의 쿼리를 EXECUTE(실행)
  • '?'에 변수 @count를 USING(사용)

동적 SQL은 자주 유용하게 사용되기 때문에 후에 스토어드 프로시저, 동적 SQL 에서 더 자세히 다룸

---

데이터 형 변환

명시적 변환 vs 암시적 변환

Java의 명시적 형 변환, 자동 형 변환과 똑같음

• 명시적 형 변환 : 말 그대로 사용자가 명시해주는 형으로 변환
  ex) 함수를 이용한 명시적 변환

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

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

이런 식으로 형 변환을 통해 실수를 정수로 표현 가능

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

이 예제는 CONCAT을 이용해 문자열을 합치는데 정수형인 price와 amount를 CHAR로 형변환해 합쳤다.

사용법의 차이일 뿐 CAST 와 CONVERT는 같은 명시적 형 변환이다.
물론 차이점은 있다.

CAST 함수는 SQL 표준의 일부로, 대부분의 주요 RDBMS 시스템에서 지원된다.
CONVERT 함수는 MySQL 특정한 함수로, MySQL에서 사용할 수 있으며 일부 다른 RDBMS 시스템에서는 지원되지 않을 수 있다.

• 암시적 형 변환

💡정수로 표현된 숫자와 문자형으로 표현된 숫자를 더하면 어떻게 될까?

-- 문자형의 더하기 
SELECT '100' + '200' ; -- 300

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

-- 문자형 + 정수형
SELECT '100' + 200 ; --300

MySQL 에서 정수형은 문자형보다 우선순위가 높다

따라서 CONCAT을 제외하고는 문자형으로 정의된 숫자는 연산식에서 정수형으로 암시적 형 변환이 일어난다.

➡️ PostgreSQL 로 이관하는 등의 일이 있을 때 문제가 발생할 수 있으니 암시적(묵시적) 형 변환에 유의하는 것이 좋다.

---

2) 두 테이블을 묶는 조인

💡 조인은 두 테이블을 엮어서 정보를 추출하는 것을 말한다

집합으로 보면 더 쉽게 이해할 수 있다. 파란색으로 색칠한 부분을 추출하는 연산이 조인 연산이다.

내부 조인

1. 일대다 관계의 이해

일대다 (1:N) : 두 테이블에서 한 쪽에는 하나의 값만 존재하지만 다른 테이블에는 여러 개가 존재할 수 있는 관계

1명의 고객은 여러 개의 물건을 살 수 있다, 1명의 학생은 여러 강의를 듣는다, 한 학교에는 여러 명의 학생이 있다 ➡️ 모두 일대다 관계.

하나의 값을 그 테이블의 기본키(PRIMARY KEY)라고 하며 다른 테이블에서는 기본키가 아닌 외래키(FOREIGN KEY)
라고 한다.

2. 내부 조인의 기본

일반적으로 조인이라 하면 내부 조인을 의미

• 기본 형식 (where 생략 가능)

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

• 예제 1
  • buy 테이블과 member 테이블의 id가 같은 것을 모두 골라주세요
  • 근데 buy의 mem_id가 GRL인
  • WHERE 절 생략 시 buy.mem_id와 member.mem_id 가 같은 모든 것을 도출

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

3. 내부 조인의 간결한 표현

위의 예제 1을 더 간결하게 표현해보자 (열 선택, 각 테이블 명칭 바꾸기)

• 예제 2
  • SELECT 할 때, mem_id는 buy와 member 테이블 모두 있으므로 어느 테이블의 mem_id인지 명시해야함
  • 각 테이블의 별칭을 추가함으로써 간단하게 표현 가능
  • 어느 테이블에 속한 열인지 명시해주는 것이 좋음

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
  FRom buy B --buy를 B로
  	INNER JOIN member M --member를 M으로
    ON B.mem_id = M.mem_id

4. 내부 조인의 활용

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과 예제 2와 마찬가지로 12개의 목록이 나온다
• 구매를 한번도 하지 않은 회원도 조회하고 싶다면?
  • 두 테이블 모두 있는 내용만 조인 ➡️ 내부 조인
  • 양쪽 중에 한곳이라도 내용이 있으면? ➡️ 외부 조인

---

외부 조인

1. 외부 조인의 기본

• 기본 형식 (WHERE 생략 가능)

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

• (LEFT or RIGHT) OUTER JOIN
  

  • 예제) - LEFT ver.

    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의 모든 내용이 같이 출력된 것을 확인 가능

• FULL OUTER JOIN
  

  • 예제)

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

    
    MySQL은 FULL 외부 조인을 지원하지 않는다.
    ➡️ LEFT 외부 조인과 RIGHT 외부 조인을 UNION 연산해 사용함!

2. 외부 조인의 활용

만약 구매를 한번도 진행하지 않은 회원을 출력하고 싶으면?

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;

외부 조인 + WHERE 조건문 ( 두 테이블의 교집합 제외 ) 을 사용하면 된다!

---

기타 조인

1. 상호 조인

테스트를 위한 대용량 데이터 생성을 위해 사용

• 기본 형식

SELECT 컬럼 리스트
FROM <테이블 이름 1>
CROSS JOIN <테이블 이름 2>;

• 특징
  • ON 구문 사용 X
  • 결과 내용에 의미 X, 랜덤 조인이기 때문
  • 테스트를 위한 대용량 데이터 생성을 위해 사용
  • 대용량이기 때문에 따로 테이블을 생성해서 사용하는 것이 좋다
• 예제)

CREATE TABLE cross_table
SELECT *
FROM sakila.actor  -- 200건
CROSS JOIN world.country;  -- 239건
-- 생성된 행의 개수 : 47,800
SELECT * FROM cross_table LIMIT 5;

2. 자체 조인

지금까지 배운 JOIN은 2개의 테이블을 조인했다. 자체 조인은 자신이 자신과 조인

실무에서 잘 안쓰이지만 알아두자

• 기본 형식

SELECT <열 목록>
FROM <테이블> 별칭A  -- 같은 테이블이지만 다른 테이블인 것처럼
  INNER JOIN <테이블> 별칭 B -- 같은 테이블이지만 다른 테이블인 것처럼
  ON <검색될 조건>
[WHERE 검색 조건]

3) SQL 프로그래밍

GPT가 알려주는 스토어드 프로시저

완벽한 정리....나의 패배....

JAVA나 C, PYTHON 등의 프로그래밍 언어와 마찬가지로 자주 사용하는 기능을 미리 구현해둔 객체가 스토어드 프로시저

IF 문

1. IF 문의 기본 형식

IF <조건식> THEN
	SQL 문장들  -- 두 문장 이상이라면 BEGIN~END로 묶어줘야 함
END IF;

• 예제)

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

• DROP PROCEDURE IF EXISTS ifProc1 : ifProc1이 존재하면 삭제
• 세미콜론(;)이 SQL의 끝인데 구분이 힘드니 $$로 두겠다 = DELIMITER $$
• DELIMITER ; = 다시 세미콜론으로 SQL의 끝을 정하겠다
• CALL ifProc1(); : 내가 만든 프로시저 호출

2. IF ~ ELSE 문

DROP PROCEDURE IF EXISTS ifProc2;
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();

• DECLARE 예약어로 변수 선언, 데이터 형식 = INT
• SET 예약어로 변수에 200 대입
• IF ~ ELSE로 구분

3. IF 문의 활용

이제 기존 테이블과 함께 활용

• ID가 APN(에이핑크)인 회원
• 데뷔 일자가 5년 넘었는가?
• 5년 넘었으면 축하 메시지 출력

DROP PROCEDURE IF EXISTS P3;
DELIMITER $$
CREATE PROCEDURE P3()
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 P3;

• 변수 세 개 ➡️ 데뷔일(debutDate), 현재 날짜(curDate), 일 수(days)
• SELECT ~ INTO 변수 : debut_date (market_db.member의)를 변수에 넣음
• CURRENT_DATE() 함수로 현재 날짜 저장
• DATEDIFF() 함수로 데뷔일과 현재의 일수를 계산
• 5년 이하 = ㅎㅇㅌ, 5년 이상 = ㅊㅋ

---

CASE 문

CASE 문의 기본 형식과 활용

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

• IF문과 다르게 여러 조건을 달 수 있음
• CASE, WHEN, THEN, 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 "회원등급"
  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;

  
  • SELECT 절에 CASE를 넣어서 "회원등급"이라는 새로운 열을 생성
  • 총 구매액을 SUM을 이용해 새로운 열 생성
  • ORDER BY로 총 구매액 별로 정렬
  • 회원의 이름 출력을 위해 구매 테이블과 회원 테이블 조인 (OUTER JOIN)

---

WHILE 문

WHILE 문의 기본 형식과 활용

[레이블 이름]
WHILE <조건식> DO
	SQL 문장들
    [ITERATE [레이블]]
    [LEAVE [레이블]]
END WHILE;

• [ ]는 생략 가능
• 레이블 이름 설정 시
  • ITERATE [레이블] : 레이블로 돌아감, CONTINUE 느낌
  • LEAVE [레이블] : 레이블을 아예 종료 ➡️ WHILE문 종료, BREAK 느낌
• 예제)

  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	-- while문에 label을 지정
  		 if (i % 7 = 0) then
  					set i = i + 1;
  					iterate myWhile;	-- 지정한 while문으로 가서 계속 진행
  		 end if;
  
  			set hap = hap + i;
      if (hap > 1000) then
  				leave myWhile;			-- 지정한 while문을 떠남
  		 end if;
  			set i = i + 1;
  		end while;
  
  select hap;
  end $$
  delimiter ;
  call whileProc2();

  
  • WHILE문을 myWhile 레이블로 지정
  • i가 4의 배수라면 i를 1 증가, ITERATE를 만나서 WHILE문 시작지점으로 돌아감
  • i가 4의 배수가 아니면 hap에 누적
  • hap이 1000 초과하면 LEAVE를 만나 WHILE문 빠져나감

---

동적 SQL

1. PREPARE와 EXECUTE

이 글의 변수 부분에서 한번 살펴본 내용 PREPARE와 EXECUTE

• PREPARE는 SQL문 실행 X 미리 준비만
• EXECUTE는 준비된 SQL문 실행
• DEALLOCATE PREPARE는 준비시킨 쿼리 할당 제거

2. 동적 SQL의 활용

• 보안이 중요한 출입문에서 출입한 내역을 테이블에 기록
• 출입증을 태그하는 순간의 날짜와 시간이 INSERT 문으로 만들어져 입력되어야 함

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;

ㅎㅎ; 미리 안하는 거 들켜버렸네..

---

4) 미션

Q1) 확인 문제 4번 풀이

• 가입만 함
• 구매한 적 X

➡️ WHERE B.prod_name IS NULL : 상품 이름이 없으면 구매한 적 없는 것이기 때문

Q2)

• DISTINCT 로 중복 제거
• INNER JOIN으로 구매 내역이 있는 멤버만 선택

➡️ 우리 사이트에서 한 번이라도 구매한 기록이 있는 회원들에게 감사의 안내문 발송 성공!