[DB] 기초데이터베이스 개념 간단정리

기초데이터베이스(기디비)

기초적인 DB와 JPQL(SQL) 내용에 대해 정리해보려한다.

SELECT와 WHERE문은 이미 알기도하고 매우 쉬운 편이므로 제외

JOIN

두 가지 테이블에 결과를 연결하여 하나의 결과 데이터셋으로 출력하는 방법이다.

 

SELECT (조회할 컬럼) FROM (테이블명)
JOIN (테이블)
ON (JOIN 시 조건문)
WHERE (SELECT 시 조건문)

가 기본적으로 사용할 수 있는 방법이 되겠다.

---

INNER JOIN (내부 조인)

아무것도 쓰지 않은 가장 기본적인 JOIN을 INNER JOIN (내부 조인)으로 칭한다.

INNER JOIN은 밴다이어그램에서 알 수 있듯이 두 테이블의 공통된 컬럼을 기준으로 두 테이블을 합친 결과값을 보낸다.

 

 

영화에 대한 정보와 특정 상영관에 대한 상영정보가 있다고하자. (편의상 영화관의 상영관은 1개 밖에 없다고한다)

여기서 겹치는 것은 바로 영화ID. 따라서 상영정보를 불러오면서 영화정보까지 INNER JOIN으로 같이 불러올 수 있다.

 

SELECT m.id, m.title, m.releaseDate, s.startTime FROM Movie m
INNER JOIN Screening s
ON m.id = s.movieId;

그럼 이렇게 INNER JOIN문을 사용하여 특정 테이블의 값을 가져올 수 있다. 그리고 ON을 통해서 어떤 조건에 부합하는 값만 가져올지도 정해야하고. ON이 없으면 에러가 발생한다.

 

 

결과는 이렇게 나온다.

---

OUTER JOIN (외부 조인)

INNER JOIN을 제외하고는 전부 OUTER JOIN이다.

 

아까 데이터에서 아직 상영되지 않은 몇 가지 영화정보가 추가되었다.

 

SELECT m.id, m.title, m.releaseDate, s.startTime FROM Movie m
INNER JOIN Screening s
ON m.id = s.movieId;

다시 영화정보를 가져오고, 특별히 상영정보도 가져오려고한다.

 

하지만 아까 추가된 데이터는 결과값에 포함되지 않는다. 왜냐하면 INNER JOIN을 통해서 조건에 부합하는 컬럼을 가져와야하는데 새로 추가한 데이터들은 아직 상영되지 않아서 조건에 부합하지 않기 때문이다.

 

하지만 그래도 여전히 결과값에 데이터를 추가하고 싶은 경우가 있다. 즉, INNER JOIN을 사용했을 때 두 테이블의 JOIN 조건에 부합하지 않더라도 값만 있으면 값을 가져오고 싶은 경우가 있는데, 이 때 사용하는 것이 외부 조인 (OUTER JOIN)이다.

 

LEFT OUTER JOIN

JOIN 문을 기준으로 기준이 되는 테이블을 왼쪽으로하여 두 테이블을 합치는 방식이다.

기준테이블은 변형이 되지 않고, 기준테이블에 맞춰 JOIN하는 테이블만 변형되는 방식이다.

 

SELECT * FROM Movie m
LEFT OUTER JOIN Screening s
ON m.id = s.movieId;

JOIN문의 왼쪽, 즉, Movie 테이블이 기준테이블이 되어 JOIN을 수행한다.

 

이렇게 Movie 테이블을 기준으로 Screening 테이블이 덧붙여지는 형태로 JOIN된다. 이 때 매칭되는 결과가 없으면 null을 채운다.

 

RIGHT OUTER JOIN

LEFT JOIN의 반대다. 기준 테이블이 오른쪽에 존재한다.

그래서 위의 경우에는 상영정보를 기준으로 했을 때 잡히지 않는 Movie 테이블의 컬럼들은 결과값에 표출되지 않는 중이다.

 

FULL OUTER JOIN

FULL JOIN은 왼쪽과 오른쪽의 모든 데이터를 읽어온다. 기존 상황에서 상영정보에 영화ID에 해당하지 않는 이상한 값이 추가되었다고한다.

 

이럴 경우 LEFT JOIN과 RIGHT JOIN 모두 흘리는 값이 생긴다. 모두 특정테이블을 기준으로 JOIN 되는 테이블에 없는 컬럼은 들고오지 않으니까. (LEFT JOIN의 경우 ID가 6인 상영정보를 들고오지 않았고, RIGHT JOIN의 경우에는 미니언즈와 파묘 영화를 불러오지 않았다)

 

이 때 FULL JOIN을 사용하면 흘리는 값 없이 모든 테이블의 값을 불러올 수 있다. 이 때 없는 값은 NULL로 채워지니 유의해야한다.

---

GROUP BY

같은 값을 가진 행끼리 하나로 묶을 때가 있다. CGV 클론코딩할 때 같은 타입의 영화 상영관끼리 하나로 묶는 것처럼..

CGV 클론코딩에서는 값을 다 가져온 후에 스트림으로 처리했지만 별로 성능이 좋은 편에 속하는 설계가 아니기 때문에 GROUP BY를 사용하는게 좋다고한다.

 

이렇게 단순히 데이터를 특정 컬럼을 기준으로 정렬화하는 것은 ORDER BY 를 사용하면되고, 특정 유형끼리 묶어서 특정한 계산(평균계산, 합 더하기 등...)을 수행하게할 때 쓰는 것이 GROUP BY

여러 개의 데이터가 나뉘어져있을 때, 특정 컬럼을 기준으로 그 컬럼과 동일한 값을 가지는 모든 컬럼에 대해서 일괄적인 작업을 수행할 수 있다 가령 테이블 내의 영화ID 수를 세어보려고하면,

 

SELECT MOVIE_ID, COUNT(*)
FROM MOVIEINFO
GROUP BY MOVIE_ID;

이렇게 특정 컬럼을 기준으로 원하는 작업을 묶어서 수행할 수 있다.

 

테이블 내의 영화ID의 수를 세어온다.

 

SELECT MOVIE_ID, MOVIE_NAME, SUM(audience)
FROM MOVIEINFO
GROUP BY MOVIE_ID, MOVIE_NAME;

관람객수의 총합도 구할 수 있다.

 

SELECT column, FUNCTION(column2)
FROM table
GROUP BY column;

기본적인 GROUP BY 사용례

GROUP BY로 기준이될 컬럼을 잡고 컬럼들을 그룹화한다. 그리고 FUCNTION(column2)를 통해서 그룹마다 적용할 함수를 정의한다. FUNCTION에는 COUNT(행의 개수 세기), SUM(합계), AVG(평균), MAX(최댓값), MIN(최솟값)이 일반적으로 사용된다.

 

COUNT의 경우에는 매개변수로 * (모든 컬럼 수를 셈), column이름(해당 컬럼의 수를 NULL를 제외하고 셈), DISTINCT column이름(해당 컬럼의 수를 NULL 및 중복을 제외하고 셈) 등이 있으며, 나머지 함수들은 매개변수로 컬럼명을 주면 해당컬럼에 대해 작업을 수행한다.

 

만약 COUNT를 제외하고 NULL을 제외하고싶다면

SELECT column, FUNCTION(column2)
FROM table
WHERE column2 IS NOT NULL
GROUP BY column;

WHERE 절에 조건을 추가하면된다.

---

HAVING

그룹화된 결과값을 HAVING을 통해 다시 필터링할 수 있다.

같은 상황에서 DB테이블에서 개수가 5개 이상인 영화만 관람객수를 보여주고 싶다고하자.

SELECT MOVIE_ID, MOVIE_NAME, SUM(audience)
FROM MOVIEINFO
GROUP BY MOVIE_ID, MOVIE_NAME
HAVING COUNT(*) > 5;

이렇게 특정 조건을 적용시킬 수 있다.

---

ORDER BY

DB 쿼리 검색 결과값의 순서를 맞추는 ORDER BY 역시 GROUP BY와 함께 쓰일 수 있다.

SELECT MOVIE_ID, MOVIE_NAME, SUM(audience)
FROM MOVIEINFO
GROUP BY MOVIE_ID, MOVIE_NAME
ORDER BY MOVIE_ID;

---

왜 GROUP BY를 해야하는가?

솔직히 DB에서 값을 리스트로 몽땅 가져온 다음에 스트림으로 걸러내도된다. 하지만 별로 추천되지는 않는다. 아니, 오히려 DB에서 값을 최소한으로 가져올 것이 권장된다.

 

소규모 프로젝트에서는 상관없을지 몰라도, 데이터값이 수십 수만 개가 넘어가는 대규모 프로젝트에서는 DB에 접근할 때 소모되는 자원을 최소화해야하고, DB에서 가져오는 값을 줄이는 것만으로도 이것이 가능하다. 즉, 모두 가져온 후에 필터링하지 말고, DB단에서 먼저 필터링을 수행하라는 것.

---

페이지네이션

위에서 말했듯이 DB에서 값을 가져오는 것 자체가 다 돈이기 때문에 데이터를 최대한 간략하게 들고오는 것이 중요하다. GROUP BY를 하는 이유도 그거고.

 

따라서 블로그나 카페 게시글과 같이 많은 양의 데이터를 가져와야하는 경우 일정 개수만 가져오는 방법이 있다. DB 부하도 줄이고, 가져올 데이터의 수도 줄이고. 이를 페이징(페이지네이션)이라고 칭한다.

 

SELECT * FROM MOVIE
ORDERS LIMIT 15 OFFSET 0

SELECT * FROM MOVIE
ORDERS LIMIT 0, 15

MySQL에서는 LIMIT과 OFFSET으로 이를 구현할 수 있다. 0번부터 15개를 가져오라는 뜻

 

SELECT * FROM MOVIE
ORDER BY releaseDate DESC
LIMIT 0, 15

ORDER BY 속성을 통해서 내림차순 또는 오름차순으로 배열할 수도 있다. 게시글처럼 최신순으로 봐야할 때에는 내림차순, 오래된순으로 글을 볼 때는 오름차순 등으로 보면 된다. ORDER BY (컬럼명) DESC(내림차순)/ASC(오름차순)으로 조회하면 된다. ASC와 DESC는 서로 다른 컬럼에 대해 적용시킬 수 있으며, 이 때는 앞쪽 ASC/DESC가 적용되고, 여기서 중복된 컬럼이 뒤쪽 ASC/DESC가 적용된다.

 

즉, 이렇게하면 3번째 페이지에 읽기요청이 들어오면 3번째 페이지가 몇 번 엔티티부터 시작하는지만 알아오면 나머지는 알아서 수행할 수 있다.

 

SELECT * FROM MOVIE
ORDER BY releaseDate DESC
LIMIT 15 OFFSET 0;

postgre에서는 이런 형식을 많이 쓴다고한다.