TIL : 46번째- 230207 [2-1-화]

I. SQL

어제 outter join을 했다. inner할줄 알면 가능하다

outter 껴주면 내가 한 데이터에 들어온다

 

방향에 따라서 '결합'해주고 남은 부분을 null 로 해준다.

두개테이블 

충돌하는 경우가 있는데, 이럴때 한정사를 이용해서 어디에 소속되어있는지 밝혀주어야한다.

 

길어진다

별칭을 쓰자

 

1. join : 컬럼 결합

(1) 내부조인연습

<회원목록 만든는데, 회원이 만든 게시글 수>

1) ***count부분 이해가안됨 (해결 : group 으로 한정해서 집계 함)

SELECT M.ID, M.NAME, COUNT(N.ID)
FROM MEMBER M JOIN NOTICE N
    ON M.ID = N.WRITER_ID
GROUP BY M.ID, M.NAME

*로 하면 문제가 발생한다

이런경우 *을 안하고, 하나씩 별도로 ~별 이런걸로 해서 지정해주어야한다.

 

 

2) 

이렇게 했지만 잘못된 것이다.

 

실제로 우리는 outer는 주인공 쪽을 다 채워주는 Join이다

inner는 양쪽에서 다 빠진다.

 

만약에 MEMBER 보려고하는데, 자식테이블 집계보려고하거나 공지사항 다볼때 부모데이터가져오는등이다.

결국 글을 쓰지않은 회원(0개)은 나오지않는다. 이때 outer를 쓰자

 

3)

이것을 outer를 써야한다.

<LEFT OUTER JOIN>

SELECT M.ID, M.NAME, COUNT(N.ID)
FROM MEMBER M 
    LEFT OUTER JOIN NOTICE N ON M.ID = N.WRITER_ID
GROUP BY M.ID, M.NAME;

결함이 당장없더라도 outer를 쓰는 것이 바람직하다

 

4)

이번에는 NOTICE 가 주인공인 쿼리를 써보자

< 게시글 목록을 조회하시오, 게시글 별 댓글의수는? >

(M.NAME WRTIER_NAME : 여기에서 WRTIER_NAME은 별칭)

또한 위 테이블에다가 notice의 자식인 comment도 붙이자

 

notice 자식인 코멘트를 넣을때는 부모가 안망가지게 집계할수있어야한다.

그런데 이 경우 comment 예약어 이기 때문이 " " 를 붙여주어야한다.

 

 

<-- 게시글의 작성자 이름과 댓글 수-->

SELECT N.ID, N.TITLE, N.WRITER_ID, M.NAME, COUNT(C.NOTICE_ID) Comment_Number
FROM MEMBER M
INNER JOIN NOTICE N ON N.WRITER_ID = M.ID
INNER JOIN "COMMENT" C ON C.NOTICE_ID = N.ID
GROUP BY N.ID, N.TITLE, N.WRITER_ID, M.NAME;

------

 

?????' 이거 무슨 조건이었음?

SELECT N.*, M.NAME FROM NOTICE N 
    LEFT JOIN MEMBER M ON M.ID = N.WRITER_ID;

우선 게시글부터 셀렉  -> 추가적으로 컬럼 늘리

(주인공 곁다리 꼭 구분하기)

 

 

집계하려면 그룹바이에 들어간거 다 들어가야함

SELECT N.ID, N.TITLE, C.REG_DATE, M.NAME, COUNT(C.ID) FROM NOTICE N 
    LEFT JOIN MEMBER M ON M.ID = N.WRITER_ID
    LEFT JOIN "COMMENT" C ON C.NOTICE_ID = N.ID
    GROUP BY N.ID, N.TITLE, C.REG_DATE, M.NAME;

 

결국 3중 join한것이다. (notice 를 기준으로 member 와 comment가 들어온다)

 

 

(2) SELF JOIN

1)

inner 나 outer의 다른방식이다.

조인은 관계있는 테이블간에 주인공 위주로 컬럼확장 하는것.

그러나 self는 테이블이 하나임 -> 자기와 자기를 조인하는 것이다.

 

홍길동이 관리하는 사원들, 유재석이 관리는 하는 사원들 등으로 이런 모든게 테이블이 나눠져 있는게 아니라 테이블이 다 하나이다.

사원이라는 이름으로 관계가 있다.

보스에 대한 정보를 뽑아오기 위한 것

자기를 통해서 자기데이터를 뽑아보는 것을 self join 이라고한다

 

2)

댓글도있다. 대댓글같은 형태이다.

SELECT M.ID, M.NAME, M.BOSS_ID, B.NAME BOSS
    FROM MEMBER M
    LEFT JOIN MEMBER B ON M.BOSS_ID = B.ID;

추가를 하는데, FROM도 멤버, JOIN도 멤버 두개다는 이상하다

FROM은 M으로 별칭해주고, JOIN B로 보스로 해준다

다만 이경우 inner하면안되고, outer로 해야한다 

left 를 기준으로 해준다. (왜냐하면 주인공은 M임)

 

 

(2) Cross JOIN (카티션 곱)

서로 관계가 없을 경우라도 크로스 조인하면된다.

 

SELECT * FROM MEMBER CROSS JOIN "COMMENT";

커멘트, 맴버 개수 곱해져서 나온다.

 

 

 

(2) UNION : 레코드 결합

UNION = 합집합 ( 정렬하면서 중복제거 )

UNION ALL = 합집합 ( 중복제거X 정렬X )

INTERSECT = 교집합

MINUS = 차집합

 

1)

테이블 3개 조인가능한가? 

유니언으로 하면 컬럼이 안늘어나고, 레코드가 늘어난다.

 

왜 쓰는가? 통합검색 일때! : 게시글 + 과정을 둘다 통합해서 검색할 때

 

2)

 

<'재'가 들어가는 게시글 찾기>

SELECT * FROM NOTICE WHERE TITLE LIKE '%재%'

<'재' 가 들어가는 댓글>

SELECT * FROM "COMMENT" WHERE CONTENT LIKE '%재%'

여기서 나오는 두개를 통합(결합)하고 싶을 때 컬럼을 맞추어주자

 

 

그리고 상수를 추가해서 어디서부터 온건지 파악이가능하다

SELECT 'NOTICE' TYPE, ID, TITLE FROM NOTICE WHERE TITLE LIKE '%재%'
UNION
SELECT 'COMMENT' TYPE, ID, CONTENT FROM "COMMENT" WHERE CONTENT LIKE '%재%';

 

3)

만약에 커멘트 아이디를 4를 32로 바꿔보자

같은 데이터 즉 TYPE, ID, TITLE가 같으면 없어진다.

 

 

 

만약에 빼지말고 다 넣어라고 UNION ALL 도 해줄 수있다.

SELECT  ID, TITLE FROM NOTICE WHERE TITLE LIKE '%재%'
UNION ALL
SELECT  ID, CONTENT FROM "COMMENT" WHERE CONTENT LIKE '%재%';

 

4) MINUS 연산자 : 차집합

SELECT  ID, TITLE FROM NOTICE WHERE TITLE LIKE '%재%'
MINUS
SELECT  ID, CONTENT FROM "COMMENT" WHERE CONTENT LIKE '%재%';

아무것도 안나온다.

 

5) INTERSECT : 교집합

SELECT  ID, TITLE FROM NOTICE WHERE TITLE LIKE '%재%'
INTERSECT
SELECT  ID, CONTENT FROM "COMMENT" WHERE CONTENT LIKE '%재%';

교집합의 결과가 나온다.

 

 

 

결국 join은 컬럼 결합 / union 은 레코드 결합

 

(3) VIEW

1)

데이터를 베이스화 한것은 중복된 것을 막기 위한 것이다.

이것을 해결하기 위한것은 각자가 아냐, 같이 써! 가된다

 

즉 중복을 제거 함으로서 결함을 제거하는것이다. 

 

위 이미지 처럼 다 쪼개어져있다.

사용자 정보 / 게시글 정보가 다 잘라져서 따로있다

하지만 이것의 문제는 저장할 때와 달리 사용할 때는 분리해서 사용하지 않는다

 

그때그때 컬럼을 다보기도, 일부만 보기도 하다

 

아래는 DB가 나오고 나서 중복제거 하기위해서 나왔다

생각해보면 위처럼 쓰고싶지안은가? 그래서 뷰라는 개념이 나왔다

 

2)

이렇게 조인해서 했었지만, 이거를 조인한 결과물을 

이와같이 VIEW로 할 수있다.

즉 가상의 읽기전용 테이블을 만든것이다.

 

3)

다만 WHERE과 ORDERBY가 들어가면안된다

 

4)

join없는 단순 SELECT 하나의 테이블을 가지고도 VIEW를 만드는 일이 있을까? 

: 보안에 해당하는 문제를 위해서 가리고 싶은게 있을 때!

: 업무적으로 자주보는 모양에대해서 만들 수도 있다.

 

(4) 데이터 딕셔너리

인증하기 위한 사용자정보 등을 저장하는것이 데이터딕셔너리라고한다

테이블이 존재하지만 사용할 수는 없다.

 

뷰라고 하는것이 

Table 을 조회하고싶을 때 DBA가 사용할수있는 것은 현존하는 모든 USER테이블을 잡을 수있다

 

이런걸 우리가 왜 써야하지? 지금은 디벨로퍼가 있으니까 사용할 일이 적지만, 예전에는 CLI 였다

그러다보니 지금은 잘 안씀

 

 

 

II. 자바를 이용한 SQL

예전과달리 요새는 중첩이 줄었다. A-JAX 등 때문에

 

 

1. 메뉴만들기

(1) 웹개발 기초 마련 :

1)

package com.newlecture.web.controller.menu;

import java.io.IOException;
import java.io.PrintWriter;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

@WebServlet("/menu/list")
public class ListController extends HttpServlet{

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		PrintWriter out = resp.getWriter();
		
		out.print("hello");
	}
}

1 웹서블릿을 어노테이션해준다.

2 extends 해준다

3 doGet을 오버라이드 하고, hello 를 출력한다.

 

 

2)

DB 쿼리 / 업무로직 / 입출력 이 한곳에 모으면 안좋은이유 :

기업에서는 안좋다 -> 전문가들이 일을 나눌 수있는건데, DB전문가, 업무전문가, 입출력전문가 등이 있다.

즉 협업이 안된다

나누는 분기점이 있다면 분기가 필요하다. 결제방법잘아는 사람 행정에서 발주시스템 아는사람이 있다. 즉 바톤넘기기 (동기)가 된다

 

그래서 데이터 가져오는 코드를 여기서 안한다

 

--

또한 나누어져 있다는 것은 수정이 어렵다

 

3)

그래서 메뉴에 데이터를 제공하는 파일을 따로 만들자

이거와 관련해서 다형성 등을 이제 배우자!

 

 

---

1. 보충

 

(1) 그룹바이 여러개일때

그리고 2중조인할때 *가 안될때

 

다중속성 GROUP BY 는 모든 경우의 수를 표현하는 것임

이런경우 * 한다는것은 경우의 수를 표현할 수 없는 상태가 됨

 

 

(2) 패키지 이름구성 (com)

 

 

 

 

2. 회고