MYSQL - 이중테이블, foreign key, join( ) -left,right, cascade

이러한 데이터가 있을때

 

 

어떤 주문자가

자신의 이메일을 수정했다고 한다면

 

수천 수만건이 쌓여있는 이메일의 정보를 전부 수정해야한다

 

 

그건 좀 문제가 있다

 

그래서

 

 

 

테이블을 두개를 만들어

커스터머의 정보를 담은 테이블과

주문정보를 담은 테이블을 만들어

 

주문정보가 들어올때

커스터머의 정보를 가져올 수 있게 하자

 

우선 커스터머 테이블을 만들고

 

 

오더 테이블을 만드는 데 이 때

 

 

아래의 Foreign Key를 클릭하자

보통의 테이블을 만들때에는 PK 프라이머리 키가 있어 중복값이 없었지만

오더테이블에는 커스터머 테이블의 ID가 몇개씩 중복되게 된다

그러므로 Foreign Key는

쉽게 말해 두 테이블을 연결해주는 열쇠라고 할 수 있겠다

 

 

클릭해보면 설정할 수 있는게 보이는데

이때 Name은 알아서 보기좋게 설정하고

중요한건 레퍼런스 테이블인데

연결할 테이블을 선택한다

 

 

 

그리고 우측의 커스터머 테이블의 뭐와 연결할지 선택하는것이 나와

id를 선택하려 하는데

체크가 되지 않는다

 

 

 

커스터머 테이블의 아이디는

고유값 설정인 Primary Key

null이 없는 Not Null

음수가 없는 UNsigned

자동증가 Auto Increment

가 설정되어 있는 반면

 

오더테이블의 커스터머 아이디는

같은 조건으로 설정해줘야 하는데

PK =  주문을 2번 이상 할 수 있어야 하니 고유값설정 하면 안되고

NN = 이미 NN인 상태에서 연결이 될테니 안해도 되고

AI = 증가시키면 안되고

남는건 UN, 음수가 없다고 설정을 해줘야 한다

 

즉, 오더테이블의 커스터머 아이디가 UN (음수설정)이 체크가 안되었기 때문

체크를 해주고 다시 해보면

 

 

 

선택이 된다

 

이제 이 두 테이블을 합쳐서 보자 join( )

 

select *
from orders
join customers
	on orders.customer_id = customers.id;

라고 입력하여

오더 테이블에 커스터머 테이블을 조인 시키는데 이때

on 오더 테이블의 커스터머_아이디와 커스터머테이블의 아이디가

같은것만을 합치게 한다

 

그래서 이런 화면이 되는데

 

일단 문제점은

 

1. 입력할때마다 타이핑 할 것이 길고

2. 둘 다 있는 경우만 합치기 때문에

회원가입은 했는데 주문은 한 적이 없는 경우라면

그 사람의 정보는 누락되게 되며,

3. 컬럼명이 중복이 있어 가독성이 떨어진다

그 점을 보완해서 다시 입력해보자

select c.*, o.id as order_id, o.order_date, o.amount
from customers c
left join orders o
	on c.id = o.customer_id;

우선 봐야하는 순서는

1. from과 join부분에서 custmers 와 orders 를 c와 o로 줄여주고

 

2. join 할때 left 즉,

커스터머 ( 고객정보 )를 기준으로 합치기때문에

오더 ( 주문정보 ) 가 없더라도 출력되게 되고,

이때 left가 아니라 right로 하게되면 기준은 오더 테이블이 된다

✔보통 left가 많이 쓰이고 right는 left에 비해 덜 쓰인다

 

3. select에서는

c.* 커스터머 테이블의 전부를 보이고,

o.id as order_id 오더 테이블의 아이디를 오더_아이디로,

o.order_date 오더 테이블의 주문시간,

o.amount 오더 테이블의 가격

만을 보여달라고 하자

 

그렇게 되면

 

이렇게 볼 수 있다

 

각 고객별, 주문수를 나타내시오
고객의 이름과 이메일이 함께 나와야 한다

그렇다면 이런 지문은 어떨까

 

select c.*, count(c.id) as order_cnt
from customers c
left join orders o
	on c.id = o.customer_id
group by c.id;

라고 생각 할 수 있다

커스터머의 아이디는 고유값이니까

커스터머 아이디로 고객별로 그룹바이 하고

커스터머 아이디를 카운트 세면

 

틀리게 된다

 

왜냐하면

주문 정보가 없어도 고객정보에서 카운트 되기때문에

주문 정보가 없어도 커스터머 테이블의 id는 1개씩 들어있기에

다른 정보가 아니라 틀린 정보를 줄 수 있다

그러므로,

 

select c.*, count(o.id) as order_cnt
from customers c
left join orders o
	on c.id = o.customer_id
group by c.id;

카운트는 오더 테이블의 id로 해야한다

주문 정보가 없다면 그 부분은 null로 나오기 때문에

주문 정보가 없는 고객의 정보는 카운트 되지 않는다

 

 

 

on delete cascade

현재 이런 상태인데

foriegn key로 인해

페이퍼 테이블에 스튜던트아이디를 입력할때

스튜던트 테이블의 아이디가 존재하지않는다면

오류가 나는 상황인데

 

student 테이블의 id가 삭제 된다면 어떻게 될까

 

delete from students
where id = 1;

 

foreign key 때문에 안된다고 나온다 

 

하지만 삭제하고 싶다면

 

스튜던트 아이디를 참조하고 있는

페이퍼스 테이블의 설정으로 들어가

foreign key 항목으로 들어가 우측을 보면

 

 

이것을 cascade를 선택한 뒤에 어플라이 해주고 다시 해보자

 

스튜던트 테이블의 있던 스튜던트 아이디1번과

페이퍼스 테이블의 있던 스튜던트 아이디 1번 모두 삭제 되었다