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

cup-wan·2024년 1월 21일
mysqlsql혼공학습단
0

혼공학습단 11기 - SQL

목록 보기
5/11

1) MySQL의 데이터 형식

데이터 형식

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

1. 정수형

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

데이터 형식바이트 수숫자 범위
TINYINT1-126 ~ 127
SMALLINT2-32,768 ~ 32,767
INT4약 -21억 ~ +21억
BIGINT8약 -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. 실수형

데이터 형식바이트 수설명
FLOAT4소수점 아래 7자리까지 표현
DOUBLE8소수점 아래 15자리까지 표현

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

5. 날짜형

데이터 형식바이트 수설명
DATE3날짜만 저장, YYYY-MM--DD 형식으로 사용
TIME3시간만 저장, HH:MM:SS 형식으로 사용
DATETIME8날짜 + 시간, 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으로 구매 내역이 있는 멤버만 선택

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

profile
cup-wan
아무것도 안해서 유죄 판결 받음
이전 포스트

[혼공SQL] 2주차 - Ch03 SQL 기본 문법

다음 포스트

[혼공SQL] 4주차 - Ch5 테이블과 뷰

0개의 댓글