TIL 81일차_SQL: 두가지 key와 group by

본격적으로 DB를 배우기 시작하면서 마주한 SQL!
공부하면서 정리가 필요하다고 생각했던 두가지 key와 group by 문법에 대해 정리해보려고 한다.

두가지 key, Primary Key와 Foreign Key

Key는 기본적으로 테이블에서의 중복된 데이터가 삽입되는 것을 방지하기 위한 제한조건이다.
이 Key에는 Primary Key 와 Foreign Key 두가지가 있다.

Primary Key가 하나의 테이블에서의 고유한 값이라면,
Foreign Key는 여러개의 테이블을 다룰 때 테이블간의 관계를 참조해주는 Key라고 할 수 있다.

<customer 테이블>

필드명	특성
sid	기본키
last_name
first_name

<orders 테이블>

필드명	특성
order_id	기본키
order_date
customer_sid	외래키
amount

위의 에제에서, orders 테이블에 있는 customer_sid 필드는
customer 테이블의 sid필드를 가리키는(참조하는) 외래 키이다.

MySQL에서 테이블을 만들 때, 외래키를 지정하는 방법

1. 처음에 테이블을 만들 때 지정하는 방법

CREATE TABLE orders ( 
    order_id INTEGER, 
    order_date DATE,
    customer_sid INTEGER,
    amount DOUBLE,
    PRIMARY KEY (order_id), 
    FOREIGN KEY (customer_sid) REFERENCES customer (sid) );

2. 이미 만들어진 orders테이블에 외래키 지정하는 방법

ALTER TABLE orders 
ADD FOREIGN KEY (customer_sid) REFERENCES customer (sid);

group by

group by는 특정 속성을 기준으로 그룹화하여 검색 할 때, 그룹화 할 속성을 지정한다.

보통 Avg, Count와 같은 함수를 사용할 때 같이 사용해주는데,
같은 부서인 사원들의 수를 count 하고싶다. 이럴 때에는 group by 부서 를 적어주면 된다.

예시를 보는게 도움이 될 것 같으니,
예시를 넣고 헷갈릴 때마다 보자!

1. 사원 테이블에서 부서별 기본급의 평균을 구하시오

SELECT 부서, Avg(기본급) as 평균
FROM 사원
GROUP BY 부서;

2. 사원 테이블에서 부서별 튜플 수를 검색하시오

SELECT 부서, Count(*) as 사원수
FROM 사원
GROUP BY 부서;

3. 사원 테이블에서 기본급이 100 이상인 사원이 2명 이상인 부서의 튜플 수를 구하시오

SELECT 부서, Count() as 사원수
FROM 사원
GROUP BY 부서
HAVING Count() >=2;

그룹합수의 조건으로는 having을 사용한다.
where과 역할은 동일하지만, 일반 조건은 where문에, 그룹 함수 조건은 having에 넣어주면 된다.

Q: 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다.
09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.
이때 결과는 시간대 순으로 정렬해야 합니다.

A :
SELECT distinct hour(datetime) as HOUR, COUNT(hour(datetime)) as COUNT
FROM ANIMAL_OUTS
GROUP BY hour
HAVING hour between 9 and 19
ORDER BY hour;

참고: dongwon kim, dankim