16. MySQL 테이블 합치기 Foreign Keys ,join , left join

Database

- Rdbms : MySQL , Oracle, DB2, MS SQL Server....(관계형 데이터 베이스

- NoSQL (분산처리가 강한 데이터 베이스 예) facebook,twitter,instagram...) 

 

Foreign Keys - 하나의 테이블에서 다른 테이블의 데이터를 참조할 때 사용

한 테이블의 컬럼이 다른 테이블의 *Primary Key 또는 Unique Key 를 참조하도록 설정

 

Join - 관계형 데이터베이스에서 두 개 이상의 테이블을 결합하여 관련된 데이터를 함께 조회할 때 사용하는 SQL 연산자

 

하나의 테이블에 데이터를 모두 저장하게 되면 문제점

1. 중복된 데이터로 인한 저장 공간 낭비 - 이름, 이메일

2. 데이터 수정시, 해당 행을 모두 찾아서 수정하지 않으면 데이터 무결성이 꺠진다.

- 이메일 주소 변경하는 경우

3. 주문 데이터 삭제시, 고객 데이터도 전부 삭제될 수 있음

 

Foreign Keys 키 생성 방법

 

ordes(예) Tables -> Properties -> Creat new Foreign Keys 선택

Reference table -> customers(예) 선택 -> 하단 Columms 에서 연결 할 Columms customeor_id(예) 선택 -> ok(추가)

orders Tables -> ER Diagram 연결되어있는지 확인

 

 

MySQL Workbench 테이블 합치기

테이블을 하나로 합칠때는 join 키워드 사용

※ 합친 테이블에서 특정 테이블의 전체컬럼 가져오기 = 테이블명.*

-- 두개 테이블을 하나로 합쳐서 가져오시오.(join tables on.컬럼 = 컬럼)

SELECT *
FROM orders
join customers on orders.customer_id = customer_id;

 

SELECT *
FROM orders o
join customers c
on o.customer_id = c.id;

 

-- 특정 테이블에 전체 컬럼 가져오기

SELECT o.id as order_id,order_date,amount,customer_id, 
first_name,last_name,email
FROM orders o
join customers c
on o.customer_id = c.id;

 

테이블을 하나로 합치고 from 테이블의 데이터를 다 가져오고 싶을때

left join 키워드 사용

 

-- 회원가입은 했는데, 주문은 한번도 안한 고객의 이메일 주소를 가져오시오

SELECT c.email
FROM customers c
LEFT JOIN orders o 
ON c.id = o.customer_id
where o.id is NULL ;

 

-- 각 고객별로, 주문 수를 나타내시오.

-- 주문을 한번도 안했으면, 0으로 나와야한다.

SELECT c.id,c.first_name,c.last_name,c.email,COUNT(o.customer_id) as order_cnt
FROM customers c
left join orders o
on c.id = o.customer_id
group by c.id

 

EX)

-- grade로 정렬하세요.

SELECT s.first_name ,p.title ,p.grade 
FROM papers p
join students s 
on p.student_id = s.id 
order by p.grade desc;

 

-- 페이퍼를 작성하지 않은 사람들도 나오도록

SELECT s.first_name ,p.title ,p.grade 
FROM students s 
left join papers p 
on s.id = p.student_id

 

-- null을 MISSING,0 으로 셋팅

SELECT s.first_name ,IFNULL(p.title ,'MISSING') as 
title, IFNULL(grade ,0) as grade 
FROM students s 
left join papers p 
on s.id = p.student_id;

 

-- print this

SELECT s.first_name ,IFNULL(AVG(grade),0) as average 
FROM students s 
left join papers p 
on s.id = p.student_id
group by s.id 
order by average DESC ;

 

-- 75점 이상이면 PASSING, 아니면 FAILING

SELECT s.first_name ,IFNULL(AVG(grade),0) as average ,
if(IFNULL(AVG(grade),0) >= 75,'PASSING','FAILING')
FROM students s 
left join papers p 
on s.id = p.student_id
group by s.id 
order by average DESC ;