MySQL 기초부터 훑어보기4(ForeignKey, Join)

 

 

https://dev.mysql.com/

 

 

 

 

안녕하십니까 다제입니다. 

오늘도 SQL에 대해서 공부해보도록 하겠습니다. 

 

 

오늘은 Foreign Key와 join에 대해서 배워보도록 하겠습니다. 

 

 

 

---

 

 

1. Foreign Key

 -. 관계형 데이터베이스에서 외래 키(외부 키, Foreign Key)는 한 테이블의 필드(attribute) 중 다른 테이블의 행(row)을 식별할 수 있는 키를 의미합니다. 

https://www.tutorialrepublic.com/lib/images/foreign-key-relationship-diagram.png

 

 -. 생성하는 방법 

#테이블 생성
CUSTOMER_TABLE = """
CREATE TABLE Customer (
    customer_id INTEGER NOT NULL PRIMARY KEY, 
    customer_name VARCHAR(32) NOT NULL, 
    customer_age INTEGER NULL
);
"""

#테이블 생성
PACKAGE_TABLE = """
CREATE TABLE Package (
    package_id INTEGER NOT NULL PRIMARY KEY, 
    package_name VARCHAR(32) NOT NULL, 
    package_date DATE
);
"""

#테이블 생성 및 foreign key 생성 
CUSTOMER_PACKAGE_TABLE = """
CREATE TABLE Customer_Package (
    cp_id INTEGER NOT NULL PRIMARY KEY, 
    customer_id INTEGER,
    package_id INTEGER, 
    FOREIGN KEY (customer_id) REFERENCES Customer (customer_id),
    FOREIGN KEY (package_id) REFERENCES Package (package_id)
);

 

 -. 삭제하는 방법 

alter table [테이블명] drop foreign key [제약조건명];

 * alter은 테이블을 변경할 때 사용하는 명령어이고

 * 제약조건명은 foreign Key의 이름이라고 생각하시면 됩니다. 

 

 

2. Join

1) LEFT, RIGHT join

SELECT 
    item.id,
    item.name,
    stock.item_id,
    stock.invetory_count
FROM item LEFT OUTER JOIN stock
ON item.id = stock.item_id

 -. LEFT는 LEFT의 왼쪽에 기재된 item table를 기준으로 table이 만들어진다. 
 -. RIGHT는 RIGHT의 왼쪽에 기재된 item table를 기준으로 table이 만들어진다. 

 -. 기존 가지고 있는 정보들이 업데이트가 되었을 경우, 무엇이 누락되었는지, 무엇이 신상으로 추가되었는지를 LEFT, RIGHT JOIN으로 쉽게 알 수 있다. 

 

2) 테이블에 alias 붙이기 

SELECT 
    i.id,
    i.name,
    s.item_id,
    s.invetory_count
FROM item AS i LEFT OUTER JOIN stock AS s
ON i.id = s.item_id

 -. table에도 alias를 붙힐 수 있고, 붙히게 되면 FROM을 제외한 파트는 모두 alias로 변경해주어야 한다. 

 

3) inner join

SELECT 
    i.id,
    i.name,
    s.item_id,
    s.invetory_count
FROM item AS i INNER JOIN stock AS s
ON i.id = s.item_id

 -. inner join은 양쪽 테이블에 모두 존재하는 값들만 합쳐서 테이블을 만들게 된다. 

 -. 만약 조인 조건으로 쓰인 두 컬럼의 이름이 같으면 ON 대신 USING을 사용할 수 있습니다. 

 

4) inner join + COALESCE

SELECT 
    pizza_price_cost.name,
    COALESCE(sales_volume, "판매량 정보 없음") AS "판매량"
FROM pizza_price_cost LEFT OUTER JOIN sales
ON pizza_price_cost.id = sales.menu_id

 -. 이렇게 COALESCE와 AS를 이용하여 JOIN을 할 수도 있음 

 

5) UNION

SELECT * FROM item 
UNION
SELECT * FROM item_new;

 -. 중복된 값은 1개만 적어주면서 두 table을 합칠 수 있는 명령어 입니다. 

 -. 만약 feature수가 다른 table을 UNION할 때는 공통된 feature를 써주고 해야 오류가 나지 않는다. 

 -. 혹시 우연에 일치로 합치고자하는 데이터가 일치하는 경우, UNION은 중복으로 이를 판명하고 데이터를 지우게 되는데요 이를 방지하기 위해 UNION ALL이라는 명령어로 수정해주면 됩니다. 상황에 맞게 잘 사용할 필요가 있을 거 같습니다. 

 

6) table 3개 합치기

SELECT *
FROM
    item AS i LEFT OUTER JOIN review AS r
        ON r.mem_id = i.id
    LEFT OUTER JOIN member AS m
        ON r.mem_id = m.id

 -. LEFT JOIN을 한 후 다시 LEFT JOIN을 하게 되면 테이블을 3개 합칠 수 있음 

 -. 이렇게 2개이상 합치게 되면 더 많은 insight를 얻을 수 있음 

 

7) 여성이면서 벌점을 제일 높게 준 상품 찾기 

SELECT i.id, i.name, AVG(star)
FROM
    item AS i LEFT OUTER JOIN review AS r
        ON r.mem_id = i.id
    LEFT OUTER JOIN member AS m
        ON r.mem_id = m.id
WHERE m.gender = "f"
GROUP BY i.id, i.name
ORDER BY AVG(star) DESC;

 

오늘도 MySQL을 조금이나 알게되어 기쁩니다. 

 

방문해주셔서 감사드립니다.