TIL : 44번째- 230203 [1-5-금]

I. 지난시간 숙제 확인 및 피드백

자바와 DB부분을 서로 분리하는 것이 좋다. (입출력 따로보자)

배열, 컬렉션에 담아 둔 뒤에  끝나고 난 뒤에 출력하는거도 가능하다.

 

II. SQL

1. 정규식 표현하기

(1) SQL에 정규표현식 사용

REGEXP_LIKE(NAME, '^.*모.*$');

위의 것을 해석하면 NAME컬럼에

모 앞에 null을 제외한 하나의 문자가 0개이상 있고

모 뒤에 null을 제외한 하나의 문자가 0개이상 있다 ***

 

정규식 like절로 컬럼의 패턴이 일치하는지

즉 where에 전화번호에 특정형태만 오게끔

 

2. ROWNUM 그리고 행 제안하기

 

row가 32개인데 이중에서 10개씩 나누어서 표현해보도록 하자

 

(1) 행을 제한하기 : 회원 목록에서 상휘 5명만 조회하시오

1)

오라클은 ROWNUM이라는 특수한 컬럼이 있다

RWONUM이란 SELECT 한 결과집합을 임시로 일련번호를 붙이는 것이다.

이런경우 일련번호는 1번부터 붙여진다.

 

이런방식으로 5개씩 끊어서 한페이지로 나오게 할수도 있다

ROWNUM으로 해서 5개씩 한페이지씩 나오게끔 해야한다

앞 ROWNUM에다가 옆에 나란히 멤버들을 컬럼으로 붙인다.

 

뒤에

WHERE ROWNUM BETWEEN  1 AND 5;

가 ROWNUM 의 1~5만 표현할수있다

 

2)

SELECT ROWNUM , MEMBER.* FROM MEMBER WHERE ROWNUM BETWEEN  6 AND 10;

그러나 

6~10은 표현이 잘 되지않는다.

왜 이런 2페이지는 안나올까?

이런 경우 1페이지는 잘 나온다.

 

ROWNUM은 결과집합에만 나온다 언제만들어 지는가?

 

ROWNUM만들고 나머지 컬럼을 붙인다

ROWNUM은 1부터 만들어지는데 처음부터 WHERE로 5이상을 넣으면 SELECT 조건(ROWNUM이 5이상 그러나 1임)에 맞지않아서, 결과집합으로 나올수가 없다.

다시 ROWNUM옆 데이터들은 없어진다 

 

3)

오라클은 이런경우 해결이 어렵다. 그래서 다른방법이 없다.

우선 ROWNUM을 따로 만들어 놓자

FROM 옆은 테이블 명이 아니라 격자형 데이터가 오는 곳이다. 이중으로 SELECT 결과 집합으로 가능하다

이렇게도 가능하다.

괄호안 것 먼저 실행하고 이후것을 쿼리하겠다 먼저 쿼리한 경우 소괄호를 쓴다

선 실행 쿼리를 서브쿼리(SUB-QUERY)라고 한다.

 

SELECT * FROM (SELECT ROWNUM NUM, MEMBER.* FROM MEMBER)
WHERE NUM BETWEEN 6 AND 10;

괄호 밖 SELECT 에서 *은 MEMBER.*을 하면안된다. 왜냐하면 가져오는 격자형테이블은 더이상 MEMBER이라고 볼수없기 때문이다.

괄호 안 ROWNUM 이 붙어있는 테이블을 새로만들기 위한 것이기 때문에 ROWNUM의 컬럼부분을 표현해주는 격자형 테이블이 필요하다.

괄호 안에서 SELECT를 해서 NUM별칭의 ROWNUM을 만든다. 만들어진 격자형데이터로 6~10번 을 WHERE을 한다.

 

별칭을 하지않으면 ROWNUM이 두개가 생긴다

여기서 두개 식별을 하는방법이 있어야하는데, 오류가 안뜨는거 보면 우선순위가 있다고 볼수있다

괄호 안은 안 / 밖은 밖으로 볼 수있다. (SQL 우선순위)

 

3. 중복 값 제거  DISTINT

우리 테이블 보면 PWD가 중복된 것이 있다.

중복된 것 없이 나열해달라고 하면 해준다.

 

SELECT DISTINCT PWD FROM MEMBER;

이렇게 하면 중복된것을 없애줌

 

 

4. Function

(1) 함수

비교 할때 연산자만 존재하는것이 아니라 함수도 있다

이렇게 하면 갯수가 나온다

세번째부터 뽑아서 준다.

 

(2) 함수의 구분

1)

다양한 방식의 함수가 있다. 이런 것들은 오라클 필수함수를 따로 공부해보면좋다

https://earth-95.tistory.com/150?category=1023354 

 

수업시간에 보기에 개념을 보기보다는 어떤함수가 있는지, 어떤기능이 있는지 기억하는 정도로 가지자~

 

 

2)

그러나 집계함수는 중요하기 때문에 배워보자

 

4. SELECT 구절 : 연산절

(1) SELECT 문의 구절 

여기 있는 하나하나는 다 연산자이다 그래서 순서가 함부로 바뀌면안된다

순서를 바꾸려면 서브쿼리를 사용하는거고 순서는 중요하다

 

SF WG HO (SF 원더걸스 호! : 이거를 암기하자) 

 

(2) 우선순위

1) FROM, WHERE, GROUP BY

여기서 FROM은 재료를 만드는 곳이다

WHERE은 필터링한다

GROUPBY는 집계를한다.

 

이렇게 순서대로 조작이 가능하다.

 

사실은 SELECT는 가장 마지막에 실행되는 것이다.

뒤의 우선순위는 앞의 우선순위가 만든 흔적을 이용하는 것이다.

 

(2) ORDER BY

ORDERBY로 정렬이 가능하다. ID로 하면 정렬이된다. (기본 ASC : 오름차순 기준) 

만약 이름이 중복되면 2차정렬이된다.

SELECT * FROM MEMBER ORDER BY NAME ASC, NICNAME DESC;

이렇게 NAME 은 1차정렬 기본인 ASC 오름차순이고, 동일이름이 있을 경우 NICNAME으로 내림차순으로 정렬하는 방식이다. 

이러한 정렬은 필수적인 요소로 볼 수있다.

 

3) 

ID로 해보자

SELECT * FROM MEMBER WHERE NAME LIKE '박%' ORDER BY ID;

 

 

(3) 집계하기 

1)

??? 에 *을 넣으면 row가 몇개인지 수가 나온다 /만약 name을 하면 null을 빼고 개수를 알려준다

이런 경우 null을 할수없는 것 프라이머리 key인 ID를 해준다

 

그런데 이 이름그대로 컬럼명이정해져서 별칭을 정해준다

 

AGE를 추가할 때 기준은 가급적 ID를 기준으로하자

왜냐하면 중복된 이름이 있을 수있다.

 

2)

나이별 지역별 투표율 에서 별로 묶어서 한다. 개수가 몇개인지 알고 싶다.

 

나이별 그룹을 묶어서 개수를 구하기를 원한다 나이를 오람차순으로 

SELECT AGE,COUNT(ID) FROM MEMBER GROUP BY AGE;

나이별 그룹을 만든다. 그 그룹에서 동일한 나이끼리 ID가 몇개인지 찾는다

 

3) <숙제>

나이대별로 하는거를 생각해보자

AGE/10 * 10

 

<힌트>

SELECT AGE/10*10,COUNT(ID) FROM MEMBER GROUP BY AGE/10*10;

<답>

SELECT AGE10*10 AS AGE ,COUNT(ID)
    FROM (SELECT MEMBER.*, TRUNC(AGE/10) AGE10
            FROM MEMBER)
    GROUP BY AGE10 
    ORDER BY AGE10 ASC;

서브쿼리를 이용해서 기준이되는 테이블을 다시 마련해준다

왜냐하면 서브쿼리로 나누기10을 하고 버림 함수를 써야하기 때문이다

그 결과치의 AGE10 으로 GROUP BY를 해준다.

 

4) <전체 멤버 중 평균나이?>

SELECT AVG(AGE) FROM MEMBER;

AVERAGE가 아님 유의!!

 

<세명간에 나이를 연산한다면?>

SELECT AVG(AGE) FROM MEMBER WHERE ID IN (1,183,177);

 

 

<ID가 홀수인 회원의 평균나이를 구하시오>

SELECT AVG(AGE) FROM MEMBER WHERE MOD(ID,2) = 1;

나머지연산은 MOD함수를 쓴다. 이경우 = 1 로 표현을 해 준다.

 

 

 

<작성자 별 게시글 수 출력하기>

SELECT WRITER_ID,COUNT(ID) FROM NOTICE GROUP BY WRITER_ID;

이렇게 할 수있는데, 숫자가 아닌 문자로 알고싶다.

SELECT (SELECT NAME FROM MEMBER WHERE ID = WRITER_ID) NAME,
        COUNT(ID)
        FROM NOTICE 
        GROUP BY WRITER_ID;

서브쿼리 방식으로 NAME을 뽑아낸다 다만 NOTICE테이블의 WRITER_ID 들고와서 가능하다

사실 이거보다는 join이 더 좋을 수있다

 

두개 테이블은 부모자식관계가 있는경우 서브쿼리는 성능면에서도 바람직하지않음

 

<게시글을 2개이상 쓴사람들 구하기>

바로 WHERE은 안된다

 

SELECT WRITER_ID,
        (SELECT NAME FROM MEMBER WHERE ID = WRITER_ID) NAME,
        COUNT(ID) COUNT
        FROM NOTICE 
        GROUP BY WRITER_ID
        HAVING COUNT(ID) >= 2;

이런경우 HAVING이다

GROUP BY 다음이 HAVING이다 이 순서이다

 

이 경우 HAVING COUNT(ID) 의 별칭이 문제되는데, 이 별칭은 순서상 마지막(SELECT)에 사용된다.

그런데 뒤에 

SELECT WRITER_ID,
        (SELECT NAME FROM MEMBER WHERE ID = WRITER_ID) NAME,
        COUNT(ID) COUNT
        FROM NOTICE 
        GROUP BY WRITER_ID
        HAVING COUNT(ID) >= 2
        ORDER BY COUNT DESC;

ORDER을 쓰면은 COUNT별칭을 쓸수있다

이런 점에서 마지막에 실행되는 것을 알 수있다.

 

---

1. 보충

 

2. 회고 

1) 여러가지 조건을 나 스스로 시도해보았는데, 뭔가 곧바로 머릿속에서 그림이 그려지는건 아니라서 조금 더 연습이 필요할거 같다 이런점은 아마도 정처기와 프로젝트를 준비하면서 자연스럽게 되지않을까 생각한다.

 

2) join 을 예전에 학교에서 DB시간에 했던거 같은데 서브쿼리방식보다 더 좋다고 하는데, 어떤건지 어서 배우고싶다.