혼공SQL 3주차

PDJ4·2023년 7월 23일
혼공혼공S혼공SQL혼공학습단
0

Chapter 4

<데이터 형식>

특이사항 :

  • CHAR 고정길이 문자형. VARCHAR 가변길이 문자형. VARCHAR(10)에 '가나다라' 저장 시 4자리만 사용. VARCHAR가 더 효율적 공간 운영 가능하지만, CHAR가 MySQL 내부적 빠른 성능.
  • TEXT는 소설이나 영화 대본 같은 내용 저장에 필요. BLOB Binary Long Object 의 약자로 글자가 아닌 이미지, 동영상 등의 데이터.

<변수의 사용>

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

첫 라인이 변수의 선언, 두 번째 라인이 출력.

본 코드에 대한 각 라인 실행 결과로 5/15/'가수이름'과 '소녀시대' 부터 '여자친구' 까지 위와 같이 나오게 된다.

LIMIT에 변수를 사용할 수 없기에, 이를 해결하는 것이 PREPARE 과 EXECUTE문.

< 데이터 형 변환>
형 변환 Type Conversion 이라고 하며, 직접 함수를 사용해 변환하는 명시적 변환 Explicit Conversion, 별도 지시 없이 자연스레 변환되는 암시적 변환 Implicit Conversion이 존재.

  • 명시적 변환 : CAST / CONVERT
CAST (값 AS 데이터_형식 [(길이)];
CONVERY ( 값, 데이터_형식 [(길이)];
  • 암시적 변환 : 함수를 사용하지 않고 자연스럽게 형이 변환.
SELECT 100+200;
SELECT CONCAT('100','200');
SELECT CONCAT(100,'200');
SELECT 100 + '200';

위 4라인에 대한 실행 결과는 300/100200/100200/300.
CONCAT 함수를 통해 숫자 100이 문자 '100'으로 변환되어 연결된 것을 확인 가능. 숫자와 문자를 연산할 때, CONCAT()을 사용하면 숫자가 문자롭 변하고, 더하기만 사용하면 문자가 숫자로 변환 후에 연산됨.

<'JOIN'>

조인 : 두 테이블을 엮어 정보를 추출하는 것.

조인의 조건 : 테이블의 일대다 one to many 관계 연결.
일대다 관계 One to Many Relation : 한쪽 테이블에는 하나의 값만 존재하고, 연결된 다른 테이블에는 여러개의 값이 존재할 수 있는 관계. 회원 테이블에 TWC라는 아이디는 PK Primary Key 기본 키로 하나밖에 없지만, 구매 테이블에서는 TWC 아이디가 여러개를 구매하면 그 만큼 TWC가 구매 테이블 내 아이디 Column에 찍히게 됨. 이에 구매 테이블에서 '아이디' column은 FK Foreign Key 외래키로 설정.
(이후 공부할 상호 조인 외의 조인은 PK-FK 관계가 필수적.)

<내부 조인>

SELECT <Columns>
FROM <1st Table>
INNER JOIN <2nd Table>
ON <조인 Condition>
[WHERE 검색 조건]

상단 코드가 기본적인 내부 조인의 형식이며 INNER JOIN을 그냥 JOIN이라 써도 INNER JOIN으로 인식.

구매 Table에는 물건을 구매한 회원의 아이디와 물건의 정보만 있고, 이 물건을 배송하기 위해선 구매한 회원의 주소 및 연락처를 알아야 하고 이는 회원 Table에 있음.

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

내부 조인에 대해 필자가 공부할 시 가장 중요시 느꼈던 것은 같은 것을 찾고 이를 타고 테이블을 넘어가는 '감각'이다.
2개 이상의 테이블을 활용한다면 필연적으로 두 테이블 사이의 공통된 것을 기반으로 한 커넥션을 활용할 수 밖에 없고, 위 코드에서 이는 '조인 Condition'인 ON buy.mem_id = member.mem_id로 나타내진다.

구매 테이블에서 mem_id column에서 GRL을 찾아내고, 이와 동일한 값을 갖는 것을 회원 테이블 mem_id column에서 검색하고, 그 아이디를 찾았다면 두 테이블의 해당 Row를 결합(JOIN)한다.

참고로 두 개의 테이블을 조인할 시 동일한 열 이름이 존재하면 꼭 테이블이름.열이름 형식으로 표기해야한다. 이에 따라 buy.mem_id 와 member.mem_id로 표기.
추가적인 내용으로, 2개 이상의 테이블을 활용할 경우 테이블이름과 열의 이름을 같이 쓰는 것이 좋은데 이를 위해 SQL문 내에 'buy B', 'member M' 같이 별칭을 붙여도 상관없다.하지만 별칭을 선언한 후에 별칭을 쓰는건 문제가 없지만, 별칭을 선언하기 전에 먼저 쓸 경우 문제가 된다.
EX)

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;

본 SQL문에서 별칭은 총 Buy-> B / Member -> M / '연락처' 3개가 존재하는 것을 알 수 있다.

내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식으로, 한 곳이라도 내용이 있을 경우에 조인하려면 외부 조인을 사용해야 함.

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

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

기본적인 외부 조인의 형식이며, 앞서 전체( 구매 기록이 없는 회원의 정보도 함께) 출력하는 것을 외부 조인으로 만들어보자.

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;

LEFT OUTER JOIN은 LEFT JOIN이라고만 써도 된다.
왼쪽에 있는 회원 테이블을 기준으로 외부 조인했다. LEFT OUTER JOIN은 '왼쪽 테이블(member)의 내용은 모두 출력되어야한다'로 해석하면 기억하기쉽다.

먼저 언급된 테이블이 왼쪽, 이후가 오른쪽 테이블로 본다.
따라서 

select M.mem_id, M.mem_name, B.prod_name, M.addr
from buy B
right outer join member M
on M.mem_id = B.mem_id
order by M.mem_id;

이렇게 작성해도 같은 결과가 나온다.

반대로 회원가입만 하고, 한 번도 구매한 적이 없는 회원의 목록은

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 테이블에서는 NULL값을 가진다는 의미가 된다.

<기타 조인>

  • 상호 조인 Cross Join : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 의미. 그렇기에 상호 조인 결과 전체 row 개수는 두 테이블의 # of row 의 곱셈값.

    일부 결과창.
    상호 조인의 특징 : 1. ON 구문 사용 불가. 2. 랜덤으로 조인하기에 결과 내용은 의미 없음. 3. 주 용도는 테스트용 대용량 데이터 생성.

  • 자체 조인 Self join : 앞선 조인들과 다르게 자신 자신과 조인한다는 의미. 

SELECT <Column list>
FROM <Table> 별칭A
INNER JOIN <Table> 별칭B
ON < 조인 condition >
[WHERE 검색 조건]

기본 형식.

[혼공SQL 3주차 기본 미션 : p.195 확인 문제 4번 풀고 인증하기]

회원가입만 하고, 한 번도 구매한 적이 없는 회원의 목록을 추출해주는 SQL코드이며, 설명은 다음과 같다.
member M 테이블과, buy B 테이블을 같이 살펴 볼 예정으로 조인을 사용. M 테이블에서 LEFT 외부 조인을 통해 B와 연결될 거싱며, 서로에서 아이디가 같은 것을 확인하려 한다. 여기서 구매 내역이 없으면 B 테이블에서 prod_name column에 대해 데이터가 없어야 하므로 IS NULL을 찾으면 된다.
따라서 빈칸만 따로 재언급하면 정답은

Having B.prod_name IS NULL

가 된다.

<SQL 프로그래밍>
STORED PROCEDURE 스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 DB 개체.
기본 구조는 아래와 같다.

DELIMITER $$
CREATE PROCEDURE #스토어드_프로시저_이름()
BEGIN
# SQL 프로그래밍 코딩 부분.
END $$ #스토어드프로시저 종료.
DELIMITER ; #종료문자를 다시 세미콜론(;)으로 변경.
CALL 스토어드_프로시저_이름();

'$$' 대신에 /,&,@ 등 사용 가능. 다른 기호와 중복될 수 있으니 기호 2개를 연속해서 사용하는 것이 좋다.

  • IF문 : 조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나.
IF <조건식> THEN
	SQL문장들
END IF;

SQL문장들이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 함. 습관적인 BEGIN~END를 권장.

위 코드의 흐름은 다음과 같다.
기존 ifproc1()을 만든 적이 있다면 삭제 -> 스토어드 프로시저 활용 후 이름 지정. -> 조건식 작성 -> 조건식이 True 이니 다음 행이 실행. ( IF 문은 조건식이 참일때 실행. ) -> CALL로 호출해서 실행.

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

조건식이 참일 경우 IF로, 거짓일 경우 ELSE로 간 것을 알 수 있다.

Lines 68-70 : 변수 생성.
Lines 71-73 : APN의 데뷔 일자를 추출하는 select 문. INTO 의 경우 결과를 변수에 저장.
Lines 74-75 : currentdate함수로 현재 날짜를 curDate에 저장하고, datediff 함수로 데뷔 일자부터 현재 날짜까지 일수를 days에 저장.
Lines 77-80 : 일자가 저장된 days를 365로 나눠 연으로 계산해 조건을 5로하여 분기를 나눔.

날짜 관련 함수 3가지.
CURRENT_DATE() : 오늘 날짜를 알려줌.
CURRENT_TIMESTAMP() : 오늘 날짜 및 시간을 함께 알려줌.
DATEDIFF(날짜1, 날짜2) : 날짜2부터 날짜1까지 일수로 몇일인지 알려줌.

간단 요약 : 조건식 = 참 -> if / 조건식 = 거짓 -> else.

  • CASE문 : 여러 조건 중에 선택하는 경우에 활용하며, IF문은 2중 분기이며, CASE문은 2가지 이상의 여러 경우일 때 처리를 가능하게 하여 다중 분기라고 부름.
CASE
when 조건1 then
SQL문장1
when 조건2 then
SQL문장2
when 조건3 then
SQL문장3
else 
SQL문장4
END CASE;

기본 형식.


보면 마지막에 '회원등급'이라는 새로운 컬럼이 추가되고, 총 구매액에 따라 회원이 분류됨.

  • WHILE문 : 조건식이 참인 동안 SQL문장들을 계속 반복.
while <조건식> DO
	SQL문장들
END WHILE;

1부터 100까지 값을더하는 while문

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

while 문의 응용 : iterative[레이블] : 지정 레이블로 가서 계속 진행 / leave[레이블] : 지정 레이블을 빠져나감, 즉 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 (i <= 100) DO
    	IF (i%4 = 0) THEN
        	SET i = i+1;
        	iterate myWHile;
        EMD 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();

i가 4의 배수면 i를 1 증가시키고, iterate를 만나 다시 위로 올라가 while문을 진행. i가 4의 배수가 아니라면 hap에 누적시켜서 만약 hap가 1000을 초과하게 되면 leave를 만나 myWhile레이블을 빠져나가는 논리.

  • 동적 SQL
    prepare는 sql문을 실행하지 않고 미니 준비만 해놓고, execute는 준비한 sql문을 실행함. 그리고 실행 후에는 DEALLOCATE PREPARE 문장을 해제하는 것이 바람직.
use market_db;
prepare myQuery from 'select * from member where mem_id = "BLK";
execute myQuery;
deallocate prepare myQuery;

prepare문에서는 '' 이하를 준비만해놓고, 실행이 필요한 시점에서 execute myQuery문으로 실행.
이렇게 미리 SQL을 준비한 후에 나중에 실행하는 것을 동적SQL.

profile
PDJ4
STFOAJDI
이전 포스트

혼공SQL 2주차

다음 포스트

Colab에 구글 드라이브 csv파일 마운트하기

0개의 댓글