문제은행 DB설계 분투기 15

쿼리문을 공부해보도록 하겠습니다.

쿼리문 공부

SELECT 
            A.VOD_CD,
            A.VOD_TY,
            A.VOD_NM,
            A.VOD_PATH,
            CONCAT(REPLACE(A.VOD_PATH, SUBSTRING_INDEX(A.VOD_PATH, '/', -1), A.VOD_CD), '.jpg') AS THUMB_PATH,
            A.VOD_URL,
            A.FILE_NM,
            A.FILE_SIZE,
            A.RUN_TM,
            A.APPROVE_TY,
            A.USE_YN,
            A.CREAT_DT,
            B.BIZ_CD,
            B.BIZ_ID,
            B.BIZ_NM,
            B.RECMDR_NM,
            <![CDATA[
            CASE
                WHEN TIMESTAMPDIFF(MINUTE, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) <= 0 THEN '방금 전'
                WHEN TIMESTAMPDIFF(MINUTE, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) < 60 THEN CONCAT(TIMESTAMPDIFF(MINUTE, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '분 전')
                WHEN TIMESTAMPDIFF(HOUR, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) < 24 THEN CONCAT(TIMESTAMPDIFF(HOUR, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '시간 전')
                WHEN TIMESTAMPDIFF(DAY, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) < 30 THEN CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '일 전')
                ELSE CONCAT(TIMESTAMPDIFF(MONTH, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '달 전')
            END AS AGO_TIME]]>       
        FROM DS_VOD A, DS_BIZ_USER B
        WHERE
            A.USE_YN = 'Y'
            AND A.AD_YN = 'Y'
            AND B.BIZ_CD = A.CREAT_USER_CD
        ORDER BY
            A.CREAT_DT DESC
        LIMIT
            ${startRowNum}, ${countPerPage}
            

만약 이런 쿼리문이 있다고 했을 때
하나하나 분석해볼까요?

SELECT, FROM, WHERE, ORDER BY, LIMIT
각각의 절들을 살펴보죠
SELECT는 말 그대로 저러한 데이터들을 행으로 가져오고

FROM : 이러한 테이블에서 가져온다는 의미이고

WHERE : A.USER_YN이 Y이면서 A.AD_YN이 Y이고
B.BIZ_CD 와 A.CREAT_USER_CD 가 같은 것들을 가져오려고 한다.

ORDER BY : 결과 데이터를 A.CREAT_DT를 기준으로 DESC 내림차순으로 정렬한다.

LIMIT : 시작행의 숫자와 페이지당 카운트할 숫자를 지정해줄 수 있다.

if와 choose의 차이

if와 choose의 차이를 알아보고 적재적소에 쓰도록 하기 - 참고한 블로그 글

if는 단일조건에서 사용하고

choose, when/otherwise는 다중조건문에서 사용한다.
when은 if, if else에 해당하고 otherwise는 else에 해당한다.

이렇게 실행시 조건에따라 쿼리가 바뀌는 것을 동적쿼리(Dynamic query)라고 합니다.

xml에서의 CDATA[ ... ]

<![CDATA[ ... ]]> 구문은 무엇일까요?

문자 데이터 포함: 특수 문자, HTML 또는 XML 코드와 같이 일반적인 XML 태그로 해석되기 원치 않는 문자 데이터를 포함하는 경우에 CDATA 섹션을 사용합니다.
그래서

<![CDATA[
            CASE
                WHEN TIMESTAMPDIFF(MINUTE, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) <= 0 THEN '방금 전'
                WHEN TIMESTAMPDIFF(MINUTE, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) < 60 THEN CONCAT(TIMESTAMPDIFF(MINUTE, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '분 전')
                WHEN TIMESTAMPDIFF(HOUR, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) < 24 THEN CONCAT(TIMESTAMPDIFF(HOUR, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '시간 전')
                WHEN TIMESTAMPDIFF(DAY, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()) < 30 THEN CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '일 전')
                ELSE CONCAT(TIMESTAMPDIFF(MONTH, DATE_FORMAT(A.CREAT_DT, "%Y%m%d%H%i%s"), NOW()), '달 전')
            END AS AGO_TIME]]>     

이렇게 하면 XML 파서가 해당 부분을 태그로 처리하지 않고 그대로 텍스트로 유지합니다.

---

CONCAT과 REPLACE 그리고 SUBSTRING_INDEX

```CONCAT(REPLACE(A.VOD_PATH, SUBSTRING_INDEX(A.VOD_PATH, '/', -1), A.VOD_CD), '.jpg') AS THUMB_PATH```

이 쿼리문을 해석해보려합니다.

먼저 SUBSTRING_INDEX(A.VOD_PATH, '/', -1)은 A.VOD_PATH에서 오른쪽에서 첫 번째 '/'를 기준으로 분리된 마지막 부분을 반환합니다.

REPLACE(A.VOD_PATH, SUBSTRING_INDEX(A.VOD_PATH, '/', -1), A.VOD_CD)은 A.VOD_PATH에서 마지막 부분을 A.VOD_CD로 대체합니다. 이로써 파일 경로에서 파일명 부분을 A.VOD_CD 값으로 바꾸는 것이 목표입니다.

CONCAT(..., '.jpg'): 이 부분은 문자열 연결(concatenation)을 수행합니다.
  ...는 앞서 나온 REPLACE 함수로 생성된 문자열을 나타냅니다.
 '.jpg'는 문자열 ".jpg"를 나타냅니다.

xml에서의 bind 엘리먼트

<bind name="likeSearchKeyword" value="'%' + searchKeyword + '%'" />

bind 요소: 이는 XML에서 데이터 바인딩 또는 변수 할당을 수행하는데 사용되는 요소입니다.

name="likeSearchKeyword": 이 부분은 변수 또는 바인딩된 데이터의 이름을 정의합니다. 이 경우 "likeSearchKeyword"라는 이름을 사용합니다.

value="'%' + searchKeyword + '%'": 이 부분은 변수 또는 바인딩된 데이터의 값을 설정하는 부분입니다. 여기서 수행되는 작업은 검색 키워드를 부분 일치 검색용 문자열 패턴으로 가공하는 것입니다.

즉 사용자가 입력한 검색 키워드를 부분 일치 검색 키워드로 만들어 쿼리문에 사용하기 위함입니다.

---

join 공부

이번에는 다른 쿼리를 살펴보겠습니다.

FROM
            DS_VOD A
        JOIN 
            DS_BIZ_USER B ON B.BIZ_CD = A.CREAT_USER_CD
        JOIN 
            DS_CONTENT DC ON DC.EXT_REF_CD = A.VOD_CD
        JOIN
            DS_USER_LOG UL ON UL.CN_CD = DC.CN_CD

DS_VOD A JOIN DS_BIZ_USER B ON B.BIZ_CD = A.CREAT_USER_CD :

DS_VOD 테이블(A)과 DS_BIZ_USER 테이블(B)은 BIZ_CD 열과 CREAT_USER_CD 열 간의 값이 일치하는 행을 조인합니다. 이것은 두 테이블 간의 관계를 설정하며, A.CREAT_USER_CD가 B.BIZ_CD와 일치하는 행을 가져옵니다.

DS_VOD A JOIN DS_CONTENT DC ON DC.EXT_REF_CD = A.VOD_CD :

DS_VOD 테이블(A)과 DS_CONTENT 테이블(DC)은 EXT_REF_CD 열과 VOD_CD 열 간의 값이 일치하는 행을 조인합니다. 이것은 두 테이블 간의 관계를 설정하며, A.VOD_CD가 DC.EXT_REF_CD와 일치하는 행을 가져옵니다.

DS_CONTENT DC JOIN DS_USER_LOG UL ON UL.CN_CD = DC.CN_CD :

DS_CONTENT 테이블(DC)과 DS_USER_LOG 테이블(UL)은 CN_CD 열과 CN_CD 열 간의 값이 일치하는 행을 조인합니다. 이것은 두 테이블 간의 관계를 설정하며, DC.CN_CD가 UL.CN_CD와 일치하는 행을 가져옵니다.

---

join을 다시 한 번 배워본다. - https://cceeun.tistory.com/189

inner 조인과 outer 조인이 있습니다. 이를 이해할 때는 원 두개를 상상하며 생각해보면 쉬운데요.
inner 조인은 교집합, outer 조인은 차집합으로 생각하면 쉽습니다.

inner 조인은 사용시 inner를 생략하고 사용할 수 있습니다.
inner 조인은 교집합에 해당합니다.

outer조인은 left outer join과 right outer join으로 나뉘어집니다.

left outer join은 문장에 왼쪽에 위치한 테이블을 그대로 놔두고 오른쪽 테이블과 조건이 맞는 부분을 붙입니다.

right outer join은 문장에 오른쪽에 위치한 테이블을 그대로 놔두고 왼쪽 테이블과 조건이 맞는 부분을 붙입니다.

서브쿼리와 조인의 차이

SUBQUERY 와 JOIN 의 차이 (上)
SUBQUERY 와 JOIN 의 차이 (下)
의 글을 참고하여 찾아보았습니다.