[MySQL] 다대일 관계의 DB: join, on, left join, having(group by, like), foreign key, index_unique

많이 사용하는 DB:

 

1. 관계형 데이터베이스 RDBMS

• 안정적인 데이터처리를 할수있다.
• 데이터가 많아질수록, 분산처리가 어렵다 (클러스터링)

 

2. NoSQL

• 빠르게 저장하고 빠르게 읽어온다.
• 데이터 안정성이 떨어진다.

그 중 관계형 데이터베이스는 테이블을 이용하여 데이터처리한다.

 

테이블과 테이블의 관계가 하나의 코드로 연결되었다. (다대일 관계)

 

먼저 고객 테이블을 생성하자

 

 

 

이제 주문 테이블 생성하자

 

 

 

foreign key로 들어가서 두 테이블의 id를 연결한다.

 

# foreign key이름을 설정하고 리퍼런스 하는 테이블을 고른다.

# 해당 테이블의 열결되는 컬럼을 정하고 연결할 테이블의 컬럼을 정한다.

 

 

join과 on을 사용해서 주문테이블과 고객테이블을 합쳐서 보여주자

 

in:

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

out:

# join에는 연결할 테이블을 입력한다.

# on에는 연결되는 컬럼들을 입력한다.

 

 

테이블 이름을 줄여서 사용할수도 있다.

 

in:

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

out:

# 테이블 옆에 축약어로 줄일수 있다.

# orders 는 o, customers는 c로 정했다.

 

 

컬럼의 이름이 중복되는경우, 컬럼의 이름 앞에 테이블 명을 붙여서 가져와야 한다.

 

in:

select c.id as customer_id, c.first_name,c.email, o.id as order_id, o.amount
from customers c
join orders o
on c.id = o.customer_id;

out:

 

 

지금까지의 join은 두개테이블에 공통으로 들어있는 데이터만 가지고 온것이다.

left join으로 모든 고객테이블을 가져오되 주문정보가 없는 고객데이터도 가져오자

 

in:

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

out:

# left join 으로 해당 컬럼의 왼쪽을 전부 표시할수있다.

 

 

주문금액이 600달러보다 큰 사람의 데이터를 가져오자

in:

select order_date, o.id as order_id, o.amount, c.id as customer_id, c.first_name,c.email
from customers c 
join orders o
on c.id = o.customer_id
where o.amount > 600
order by order_date;

out:

# where와 order by를 적용했다.

 

 

주문금액이 600보다 큰 사람의 이메일을 가져오자

 

in:

select distinct c.email
from customers c 
join orders o
on c.id = o.customer_id
where o.amount > 600
order by c.email;

out:

# distinct를 사용해서 중복을 제거했다.

 

 

group by를 사용하여 각 고객별로 주문 수를 나타내자

 

in:

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

out:

 

 

각 고객별로 주문 금액 평균이 300달러 이상인 데이터만 가져오자

 

in:

select order_date, o.id as order_id, avg(o.amount) as average_amount, c.id as customer_id, c.first_name,c.email
from customers c 
join orders o
on c.id = o.customer_id
group by c.id having average_amount >= 300;

out:

# group by 로 엮어서 가공 한 컬럼의 정확한 수 이상, 미만 등을 알아볼때 having 을 사용한다.

 

 

각 고객별로 주문금액 최대값이 600 달러 이상인 데이터만 가져와서 내림차순으로 정렬하자.

 

in:

select order_date, o.id as order_id, max(o.amount) as max_amount, c.id as customer_id, c.first_name,c.email
from customers c 
join orders o
on c.id = o.customer_id
group by c.id having max_amount >= 600
order by max_amount desc;

out: