TIL : 45번째- 230206 [2-1-월]

I. SQL

1.  서브쿼리

(1) 서브 쿼리를 사용하는 경우 : 구절의 순서를 바꿔야하는경우

1)

ROW_NUM 에다가 가입순을 기준으로 하고싶다

그러나 아래처럼 순서를 하면 문제가 발생한다

 

 

--

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 5 Column: 9

--

이와같은 문제가 발생한다 (순서가 틀림)

 

2)

우리는 ORDER BY 먼저 하고싶은데 문제가있다. 이런경우 서브쿼리를 쓰면된다

 

3) 나의 방법

그렇다고 WHERE 과 ORDER BY 의 위치를 바꾸면 

이것처럼 ROWNUM은 재멋대로 순서가 생긴다. 여기다가 ORDER BY로 해도 ROWNUM은 꼬여있다.

 

 

4) 샘의 방법

SELECT  * FROM 
    (SELECT * FROM MEMBER ORDER BY REG_DATE DESC)
        WHERE ROWNUM BETWEEN 1 AND 10;

이렇게 해서 서브쿼리를 했다

정렬을 한 다음, 뽑아내는 방식이었다

 

5) 11~ 20 까지의 ROWNUM은 될까?

 

ROWNUM 구해 -> 정렬 -> 페이징

이게 아니라

정렬 -> ROWNUM 구하기 -> 페이징

 

SELECT * FROM (SELECT ROWNUM NUM, M.* FROM 
        (SELECT * FROM MEMBER ORDER BY REG_DATE DESC)M)
        WHERE NUM BETWEEN 11 AND 20;

나는 서브쿼리 이중으로 하였다

그리고 ROWNUM만 나오는데, M이라는 별칭을 만들어준다

(왜냐하면 *가 중복된 것이기 때문에 별칭설정이 가능하다 -> 즉 테이블 별칭이 가능하다.)

 

 

 

(2) 서브 쿼리를 사용하는 경우 : 나이가 30이상인 회원 목록을 조회하시오.

1)

평균나이를 구해야하는 경우에는 서브쿼리를 이용할 수있다.

 

2)

SELECT * FROM MEMBER
    WHERE AGE >= 
    (SELECT AVG(AGE) FROM MEMBER);

이런식으로 테이블에 특정 하나의값(평균나이) 로 WHERE 의 비교기준을 만들때에도 이용이가능핟

즉 특정 값 하나를 뽑아 낼 때에도 가능하다.

 

 

3)

정리하면 기본구문의 순서를 바꾸거나, 어떤결과 값을 구하고 연산할 때 

 

 

<문제>

회원정보를 조회해주세요. 단 게시글을 가장 많이 올린자들

정렬을 하거나, 서브쿼리 두번 쓰면된다.

 

1. 정렬방식

SELECT * FROM MEMBER WHERE MEMBER.ID =
(SELECT N.WRITER_ID FROM 
    (SELECT WRITER_ID,COUNT(ID) COUNT 
    FROM NOTICE GROUP BY WRITER_ID ORDER BY COUNT DESC)N
WHERE ROWNUM = 1);

우선 회원별 게시글 수를 정렬해서 뽑아내고, 1등을 뽑아낸다

컬럼이 2개인데 하나로 줄인인다.

그리고 멤버 ID와 비교한다

 

2. 

MAX 집계함수를 이용이 가능하다. 그런데 더 복잡해서 정렬방식을 하자

 

 

(3) 서브 쿼리를 사용하는 경우 : 목록을 출력하는 서브 쿼리

1)

서브쿼리 관련 연산자 ANY, ALL 이 있다

월별/시즌별/연도별 도 가능하다

월별 -> 그룹바이 

 

위의 여성남성 정보는 좋은 예가 아니라서 다른방식으로 해보자

 

<문제 : 성별 평균나이 구하기>

SELECT GENDER,AVG(AGE) 평균나이 FROM MEMBER 
    GROUP BY GENDER;

성별로 그룹을 만든다.

만든 그룹의 컬럼과 함께 그룹(성)별나이를 평균으로한다

 

2)

<성별 평균나이/를 상회하는 나이/를 가지는 회원 정보를 출력하시오>

이런경우 필요한것이 all 과 any이다.

 

앞서 있던것은 컬럼이 2개의 값이다. 그래서 비교가 어렵다

서브쿼리가 목록인경우, 하나의 단일한 값으로 만들기위한 연산이 필요하다

이런경우 all any 이다.

 

3) 목록의 단일화를 위한 any all

all,any 의 경우 = 가 아니라 부등호(><)를 써야한다.

 

all :  모든 값을 만족 

SELECT AGE,MEMBER.* FROM MEMBER WHERE AGE > ALL     
(SELECT AVG(AGE) FROM MEMBER GROUP BY GENDER);

둘다 만족하는 즉 50,55 모두 높아야하기 때문에 65세 이상부터만 출력되었다.

 

 

any: 여기 목록중 하나만 만족하기

SELECT * FROM MEMBER WHERE AGE > ANY     
(SELECT GENDER, AVG(AGE) FROM MEMBER GROUP BY GENDER);

55, 41 둘중 어느하나보다 높으면 되기 때문에 50세부터 출력이 되었다.

 

 

단위값으로 만들어서 해주기

 

 

2.  조인(join)

(1) DB란?

쿼리 작성시 서브 쿼리 말고 다른것을 해야한다.

DB를 할때 테이블 하나만 가지고하지않는다 하나만 가지고 할일이 없다는걸 알아야한다

 

왜냐하면 DB는 특정한 한곳에 저장하는데 이전에는 결함이 있었다. 결함은 중복이다.

각자 부서마다 가지고있는 데이터들이 서로 결함 가진다.

이것을 한곳에 모아서 부가적으로 실시간 업데이트 및 결함이 없어진다

 

그러나 단점이 있다. -> 데이터가 쪼개어져있다

결합해서 써야하는 문제가 있다.

쪼개어서 저장한 상태

(2) join

1)

우리가 쓰는 테이블은 쪼개진 결과물을 본다. 단일한 하나가 온전하게 있는 경우는 잘 없다

이렇게 쪼개어져 있다. key를 이용한다.

이러한 게시글의 작성자 이름을 알고싶으면, 여기 Writer_id 1번을 찾아서 newlec을 이용한다

 

그러면 결합을 어떻게 하는가? join 이라고 한다

join의 종류가 4가지가 있다. 우리는 inner 1순위다 나머지는 부가적인 것이다

inner 은 교집합 인싸이다.

둘의 관계가 key를 통해서 참조(참조키 유무)할수있을 때 결합이 가능하다

 

2)

inner join은 컬럼을 늘리는 결합이다. 어디 방향으로 늘리나? 이경우 주인공이 누구인지

주인공은 자식테이블이 무조건 주인공이다.

 

여기서 자식이란 참조하는 녀석(참조 키)를 가진 것이다.

즉 먼저 있어야 하고, 나중에 레코드가 추가되는 것이다.

 

저기 삼지창은 하나가 여러개 참조하고있는것이 자식이다.

(부모여럿이 자식을 낳는 경우는 없다)

 

3)

부모 레코드가 자식만큼 name수가 늘어난다 그리고 자식이 부모한테 간다 (자식이자나!)

 

4)

이것은 메모리에서 연산하는 것이다.

이 방식은 과거 파일처리시스템이다. 결합하거나 복제하는등은 존재하지 않는다

그냥 저장했다가 읽어왔다가 한다

성능관점에서 통으로 하는게 좋을까, 결합하는 연산이 좋을까 => 후자가 좋다

왜냐하면 NAME 뉴렉이 중복 저장이 더많고, load하는데에도 더 오래걸린다

IO는 연산보다 느리다

 

CPU/Memory 로 하는게 IO 로 하는거보다 훨씬 빠르다

결국 후자로하면 수행성능을 월등히 올릴 수있다.

 

join은 성능,데이터결함도 없애고 다 좋지만, 개발자를 괴롭힌다.

그래서 중복된 데이터로 테이블 만드는 사람이 존재했다.

 

(3) inner join ( 교집합 )

1)

join key가 같은 것을 엮어주자

SELECT * FROM MEMBER 
    INNER JOIN NOTICE ON NOTICE.WRITER_ID = MEMBER.ID;

 

우선 FROM MEMBER 로 부모 컬럼을 다 가져온다

 

2)

이렇게 결합한 경우 주목할 것은 레코드의 수 이다.

왜냐하면 자식 기준으로 한다. 당연히 중복으로 가능하기때문에 경기도안양이준영이 2개이다

 

22개였는데, join하고는 21개 이다

줄어든 이유눈 notice중에 부모 member id로 존재하지 않는 것이 하나 있는것이다.

이런경우는 게시글 중에는 join 관계에서 같이 존재하지 않는 outer join을 의미한다.

'자식컬럼의 참조키'와 '다른 부모테이블의 레코드'는 없어지고, '참조키가 같은 뉴렉'만 존재한다

그리고 뉴렉은 자식테이블의 레코드 수만큼 복제된다.

 

3)

어떻게 조인되나?

 

원래는 5개이다.

join은 자식이 주인공이지만, 부모와 결합될 껀덕지가 없는거면 빠지게된다.

자식과 연관없는 레코드는 outer(부모 자식 모두) 이다.

4)

우선 자식이 누구냐 생각하자

레코드는 6개인데 outer가 누구인지 생각하자

dragon이 부모가 없다 / outer로 빠지고

 

 

(4) outter join

1)

부모에도 자식에도 outter가 있을 수있다.

이를 같이 껴주는 훈훈한join 이다

이 경우 어디에 outter를 껴주어야할까?

왼쪽을 원하면 LEFT 로 명시해준다 반대는 RIGHT 이다.

 

 

2)

6개인데

우선 3개 inner에다가 / left 는 3명 더있다 이를 합쳐준다

 

SELECT ID, NAME, ID, TITLE
FROM MEMBER
    INNER JOIN NOTICE
    ON NOTICE.WRITER_ID = MEMBER.ID;

이런경우 모호하다고 하니까 별칭을 써주자

 

SELECT M.ID, M.NAME, N.ID, N.TITLE
FROM MEMBER M
    INNER JOIN NOTICE N
    ON N.WRITER_ID = M.ID;

주인공이 누구인가? 생각하자

 

3) RIGHT OUTER JOIN

outer 할 때 빠진거를 껴주어야한다.

SELECT M.ID, M.NAME, N.ID, N.TITLE
FROM MEMBER M
    RIGHT OUTER JOIN NOTICE N
    ON N.WRITER_ID = M.ID;

이렇게 하면 22개로 1개가 빠진 21개가 아니라 모두 나온다.

게시글은 썻지만, 실제로 멤버에 없는 사람들도 나온다.

 

 

 

4) LEFT OUTER JOIN

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

부모컬럼 MEMBER 에 비록 게시글을 쓰지않은 자가 있다고 하더라도, 다 표현해준다.

 

 

 

5) FULL OUTER JOIN

    
SELECT M.ID, M.NAME, N.ID, N.TITLE
FROM MEMBER M
    FULL OUTER JOIN NOTICE N
    ON N.WRITER_ID = M.ID;

full 하면 TITLE이 없는 즉 아직 글을 안쓴경우 + 가입을 안한경우 모두 나온다

 

 

 

<결론>

자식은 모든게 나오게 하는 것이 outer join 으로 중요하다.

자식은 무조건 나오게 하고, 부모는 자식에다가 곁다리로 들어간다

 

 

---

1. 보충

(1) 금일 HTML 복습

 

(2) date 를 update 

UPDATE MEMBER SET REG_DATE = 1994-07-06 15:30:00
WHERE NAME = '모재영';

 

입력형식 : UPDATE 테이블명 SET 컬럼명=TO_DATE( '날짜데이터', '입력포맷'  );

UPDATE SM_USER SET CHGDATE=TO_DATE( '2009/06/01 11:00:00', 'YYYY/MM/DD HH:M
I:SS'  );

 

이렇게 포맷도 같이 제시해주어야한다

 

 

 

(3) 주인공이 대체 무엇?

 

(4) 보충:GROUP BY

 

1)단독속성

What (정의) : 특정컬럼을 기준으로 집계하는 것이다.

Why (존재이유) : GROUP BY없는 집계함수의 경우 모든 모든 레코드를 기준으로 집계를했다. , 그러나 GROUP BY 를 이용하면 그룹별로 묶어서 집계가 가능하다.

 

How (방법) :

SELECT [GROUP BY 절에 지정된 컬럼1] [GROUP BY별로 집계할 값]  FROM [테이블 명]  GROUP BY [ 그룹으로 묶을 컬럼 값 ]

한편 SELECT 구절에 *을 넣을 수 없다.

왜냐하면 컬럼은 이미 묶여있는데, 묶여있는 컬럼과 기존 테이블의 컬럼들은 함께 있을 수 없기 때문이다.

 

-> 이후 HAVING 으로 그 결과값을 추가하는 것이다.

 

2)다중 속성

GROUP BY 의 속성을 다중으로 해줄 수있다.

만약 GROUP BY 지역, 성별, 평균나이 라고 할 때

 

결과는

서울 남 20 / 서울 녀 20 / 서울 남 30 / 서울 여 30 /

경기 남 20 / 경기 녀 20 / 경기 남 30 / 경기 여 30

이런식으로 각 경우의 수 별로 그룹을 만들 수있게된다.

 

 

 

(5) 보충:HAVING

 

What (정의) : GROUP BY 한 결과에 대해서 다시 필터를 넣어주는 역할을 한다.

How (방법) : WHERE -> GROUP BY -> HAVING 

e.g. (예) :

 

 

 

 

e.g. (예) :

 

 

2. 회고 

1) join 에서 주인공이 머냐고 하는것이 와닫지 않는다 좀 더 공부를 해야겠다

 

 

2) 수업때는 용케도 풀었는데, 서부쿼리 2중 3중으로 된게 다시 해보니 녹록치않다 연습을 자주하자!!

생각을 하면서!!!