[PostgreSQL] 4.쿼리에사용되는연산자와함수

쿼리에 사용되는 연산자와 함수

1. 논리, 비교 연산자와 조건문 함수 
    -논리, 비교 연산자는 조건이 참인지 거짓인지 판별하는 연산자로 결과는 블리언타입으로 나타낸다
    -논리연산자는 조건을 추가적으로 늘릴 때 사용한다
    -비교연산자는 2개의 어떤 값 또는 조건을 비교할 때 사용한다

    1) 논리 연산자
        -AND, OR, NOT으로 구성된다
        -조심해야 하는 부분은 조건과 결과의 불리언 타입이 참과 거짓으로만 이루어져 있지않고,
         NULL을 간과하면은 안된다
         즉 조건이 NULL 일 수도 있고 결과가 NULL로 출력될 수도 있다는 것이다
        
        -AND는 두가지 조건 A,B 모두 참일 때 참을 출력한다
        -OR는 두가지 조건 중 하나만 참이라면 참을 출력한다
         예를 들어 A가 거짓이고 B가 NULL이면 NULL로 출력된다
        -NOT 연산자는 특정조건이 참일 경우 반대로 거짓을 출력하고, 거짓일 경우에는 참을 출력
         만약 특정조건이 NULL 이라면 NULL을 출력한다

    2) 비교, 범위 연산자
        (1) 비교 연산자
            -조건 A와 B를 비교할 때 사용한다
            -어떤 표현이 참인지 거짓인지 알아야 할 때 비교술어를 사용한다
            
            -불리언 조건식이 참인지 거짓인지 알아보는 연산자
             <불리언 표현식> IS TRUE = <불리언 표현식>이 참이다 > 맞으면 TRUE 아니면 FALSE
             <불리언 표현식> IS NOT TRUE = <불리언 표현식>이 참이 아니다 > 맞으면 TRUE 아니면 false
              이때 불리언 표현식이 NULL일 경우에는 참이 아니므로 결과는 TRUE값을 가진다

            <불리언 표현식> IS FALSE = <불리언 표현식>이 거짓이다 > 맞으면 TRUE 아니면 FALSE
            <불리언 표현식> IS NOT FALSE = <불리언 표현식>이 거짓이 아니다 > 맞으면 TRUE 아니면 FALSE
              이때 불리언 표현식이 NULL일 경우에는 FALSE값을 가진다

            <불리언 표현식> IS NULL = <불리언 표현식>이 NULL이다 > 맞으면 TRUE 아니면 FALSE
            <불리언 표현식> IS NOT NULL = <불리언 표현식>이 NULL이 아니다 > 맞으면 TRUE 아니면 FALSE

            -'='를 활용할 수 있지만 술어를 사용하는 이유는 
             비교연산자는 참, 거짓, NULL 세가지 항을 비교하는 삼항논리기반이다
             따라서 예상치 못한 결과값을 가져오지 않기 위해서는 술어를 사용한다

        (2) 범위 연산자
            -비교연산자를 범위 연산자로 대체할 수 있다
            -범위연산자에서 중요한 것은 경계 값 즉 경계의 값들을 포함하는지 안하는지 
            -예) 비교연산자로 1이상 9 이하의 값을 얻도록 필터 기능을 활용하려면 WHERE절 활용
                 SELECT * 
                 FROM 테이블명
                 WHERE 1<=num AND num<=9;

                 위의 내용을 범위 연산자로 좀 더 명확하고 쉽게 나타낼 수 있다
                 SELECT *
                 FROM 테이블명
                 WHERE BETWEEN 1 AND 9;
                 반대로 1미만 9를 초과하는 범위의 값을 구하고 싶다면 BETWEEN앞에 NOT만 붙이면 된다
                 WHERE NOT BETWEEN 1 AND 9;

    3) 조건문 함수 : 특정조건을 가진 컬럼을 만들 수 있다
        (1) CASE 함수
            -가장기본적인 조건문 함수로, 컬럼에 특정한 조건을 부여할 수 있다
            -IF ELSE문과 대응된다
            -CASE로 시작해서 END로 끝나는 형태이다

            CASE
              WHEN 조건문1 THEN 결과문1
              WHEN 조건문1 THEN 결과문2
              ELSE 결과문3
            END
            -가장 먼저 조건문1을 판별하여 불리언 데이터타입을 결과로 반환
             TRUE일 경우에는 결과문1이 실행된다, TRUE가 아니라면 조건문2를 평가한다
             만약 모든 조건문이 TRUE가 아니라면 결과문3을 실행하며, ELSE절이 없다면 NULL을 반환한다

        (2) COALESCE 함수
            -COALESCE() 주로 데이터를 조회할 경우, NULL 값을 다른 기본 값으로 대체할 때 사용된다

             CASCADE(매개변수1, 매개변수2, ...)
             -매개변수를 순서대로 평가하여 NULL이 아닌 첫번째 매개변수를 반환한다
             -예)COALESCE(score,0)으로 하고 쿼리문을 실행했을 때 score에 해당하는 값이 null이라면
                 null 대신 0으로 출력해준다

        (3) NULLIF 함수
            -NULLIF() 함수는 NULL을 이용하는 가장 기본적인 조건문 함수이다
            -위에 COALESCE()를 이용해서 NULL값을 0으로 바꾸었다 그렇다면 
             0을 NULL값으로 바꾸고 싶을 때 NULLIF()를 사용한다
            -NULLIF() 함수는 2개의 매개변수가 필요하다

            NULLIF(매개변수1,매겨변수2)
            -매개변수1 과 매개변수2 가 같을 경우 NULLIF()함수는 NULL을 반환하고
             서로 다를 경우 매개변수1를 반환한다
            -예) 주로 어떤 값을 0으로 나눌 때 같은 에러가 나는 수학계산을 할 때 유용하다
                 COALESCE((12/NULLIF(student,0))::chat, '나눌 수 없음')
                 -NULLIF함수를 통해 student가 0일때 null로 출력한다
                  그럼 COALESCE함수를 통해 (12/NULLIF(student,0))가 null이기 때문에 '나눌 수 없음'으로 출력된다


2. 배열 연산자와 함수
    -배열을 이용하여 비교, 포함관계, 병합 등 다양하게 응용할 줄 알아야 한다
    1) 배열 연산자
        -비교연산자를 사용한다. 순서대로 원소끼리 비교하게 된다

        -배열 연산자에서 포함관계를 확인하는 방법은 <@ , @> 연산자를 사용한다
         예) ARRAY[1,2,3]@>ARRAY[1,3] = TRUE

        -원소 단위로 겹침 유무를 확인하는 && 연산자
        -두 배열을 비교하여 하나라도 겹치는 원소가 있다면 결과는 참을 반환한다
         예) ARRAY[1,2,3,4]&&ARRAY[1,5,6] = TRUE

        -배열끼리 병합 또는 원소를 추가 할때는 || 연산자 사용
        -배열끼리 병합
         예) ARRAY[1,2,3]||ARRAY[1,3] => [1,2,3,1,3]

        -2차원 배열로 병합
         예) ARRAY[[1,2,3],[4,5,6]]||ARRAY[7,8,9] => {{1,2,3},{4,5,6},{7,8,9}}

        -원소 배열 병합
         예) 1||ARRAY[2,3,4] => {1,2,3,4}

        -배열 안에 배열을 추가 : 2차원 배열 사용
         예) 1. ''사이에 중괄호를 사용하는 방법
                VALUES('{{,2,3},{4,5,6},{7,8,9}}')
             2. ARRAY[]를 활용한다
                VALUES(ARRAY[[1,2,3],[4,5,6],[7,8,9]])

    2) 배열 함수
        array_append(배열, 원소)
        -배열에 맨 뒤에 원소를 추가
        -첫번째 매개변수에 추가할 배열을 놓고 그 뒤에 추가할 원소를 넣는다
        -예) SELECT array_append(ARRAY[1,2],3)

        array_prepend(원소, 배열)
        -배열에 맨 앞에 원소를 추가
        -예) SELECT array_prepend(1, ARRAY[2,3])

        array_remove(배열, 원소)
        -배열의 특정 원소를 삭제
        -예) SELECT array_remove(ARRAY[1,2,3,4],4)

        array_replace(배열, 원소1, 원소2)
        -배열의 특정 원소를 다른 원소와 대체(변경)
        -원소1=기존의 원소, 원소2=새로운원소
        -예) SELECT array_replace(ARRAY[1,4,3],4,2)
            배열안의 4를 2로 변경해주는 원소이다

        array_cat(배열1, 배열2)
        -배열과 배열을 병합
        -||연산자를 이용해서 병합할 수 도 있다
        -예) array_cat(ARRAY[1,2], ARRAY[3,4]) => {1,2,3,4}


3. JSON 연산자와 함수
    -JSON에는 JSON 데이터타입과 JSONB 데이터타입이 있다
    -JSONB, JSON 모두 쓸 수 있는 연산자가 있고 JSONB에만 쓸 수 있는 함수가 있다

    1) JSON 연산자
        (1) JSON, JSONB 공통 연산자
            -JSON 오브젝트는 '키값' 과 '벨류값'으로 이루어져 있다
            '->' 연산자 
                -JSON오브젝트에 저장된 키 값으로 벨류 값을 가져올 때 
                -'{"키 값1:"벨류 값1", "키 값2:"벨류 값2"}'  -> "키 값1"
                -예) SELECT '{"p":{"1":"postgres"}, "s":{"1":"sql"}}'::json -> 'p' => {"1":"postgres"}
                     이때 형변환을 시켜주는 이유는 데이터타입이 정의되지 않아서 확실하게 json타입임을 밝혀주는 것
                
                -JSON 배열에서 인덱스로 JSON오브젝트를 불러오기
                 인덱스는 0부터 시작이다, 만약 인덱스가 음수라면 뒤에서부터 시작한 것이다
                 -> 인덱스 번호

            '->>' 연산자    
                -JSON속의 데이터를 TEXT 데이터타입으로 가져오는 방법
                -예) SELECT '{"p":{"1":"postgres"}, "s":{"1":"sql"}}'::json ->> 'p'

            '#>' 연산자
                -JSON이 복잡한 다층의 구조로 이루어져 있을때 특정한 경로의 값을 가져옴
                -예) SELECT '{"i":{"love":{"book":"postgresql"}}}'::json #>> '{"i","love","book"}'
                     result = postgresql
                
                -text데이터타입 사이에 json배열이 중간에 껴 있다면 키값 대신 인덱스번호를 넣어준다
                -예) SELECT '{"post":[{"gre":{"sql":"do it"}},{"t":"sql"}]}'::json #> '{"post",0,"gre","sql"}'
                     result = "do it"

            '#>>' 연산자
                -특정한 경로의 값을 TEXT 데이터타입으로 가져옴

        (2) 추가적인 JSONB 연산자
            -JSON 데이터타입에서는 기본연산자(<,>,=,<>,<=,>=)는 사용할 수 없다
            
            <@ 또는 @> 연산자
                -JSONB끼리 포함관계를 판별한다
                -참, 거짓, NULL을 리턴한다

            ? 연산자
                -JSONB에 해당 문자열을 키 값으로 존재하는지 판별
                -참, 거짓, NULL을 리턴한다
            
            ?| 연산자
                -JSONB에 배열속 원소가 키 값으로 하나 이상 존재하는지 판별
                -하나라도 있으면 참, 하나도 없으면 거짓 또는 NULL

            ?& 연산자
                -JSONB에 배열속 원소가 키 값으로 모두 존배하는지 판별
                -모두 존재해야 참, 하나라도 없으면 거짓 또는 NULL

            || 연산자
                -2개이 JSONB를 병합
                -JSONB 데이터 타입으로 리턴

            - 연산자 (빼기)
                -JSONB 오브젝트의 하나 이상의 원소를 삭제
                -JSONB 배열의 해당 인덱스 번호의 원소를 삭제
                -JSONB 데이터 타입으로 리턴

    2) JSON 생성과 처리함수
        (1) JSON 생성함수
            -JSON오브젝트와 JSON배열이 있다
            
            json_build_object()
            -JSON 오브젝트를 만드는 생성함수
            -json_build_object("키1","벨류1","키2","벨류2",...)
            -JSONB 데이터타입의 JSON오브젝트를 생성하고 싶다면
             jsonb_build_object("키1","벨류1","키2","벨류2",...) 
            -JSON오브젝트는 키-벨류 쌍으로 생성되어야 하므로 짝수만 올 수 있다

            json_build_array()
            -JSON 배열을 만드는 생성함수
            -json_build_array("원소1","원소2","원소3",...)
            -JSONB 데이터타입의 JSON배열을 생성하고 싶다면
             jsonb_build_array("원소1","원소2","원소3",...)
            -JSON배열은 오브젝트와 다르게 원소의 개수가 홀수여도 생관없다

        (2) JSON 처리함수
            -특정한 작업을 수행하는 처리함수

            json_array_length()
            -JSON배열의 원소의 개수를 세는 함수
            -매개변수로는 JSON이 들어가고 결과는 정수형으로 출력된다
            -예) SELECT json_array_length('["a",1,"b",2"c"]'::json)
                 result = 5
            -만약 JSONB로 저장되어 있다면, jsonb_array_length() 함수를 이용한다

            json_each()
            -키 값을 텍스트데이터 타입의 컬럼으로, 벨류 값을 JSON 데이터타입의 컬럼으로 정리해준다
            -JSONB데이터타입으로 벨류값을 저장했다면 jsonb_each() 함수를 사용한다
            -벨류 값도 텍스트로 출력받길 원한다면 json_each_text() 함수를 사용한다
             하지만 일반적으로 키 값에 데이터값을 넣지 않는다 데이터의 값은 주로 벨류값에 넣는다

            json_array_elements()
            -JSON배열 속 원소를 컬럼으로 불러올 수 있다
            -함수의 구조로 살펴보면 매개변수로는 JSON배열로 들어온다
            -예) SELECT *
                 FROM json_array_elements('[1,"a", {"b":"c"}, ["d",2,3]]')
                 result = 1 "a" {"b":"c"} ["d",2,3]
                -JSON 배열로 이루어진 원소를 컬럼으로 불러온다
                -이때 컬럼의 데이터타입은 JSON 데이터타입이다
                -TXET 데이터타입으로 조회하고 싶다면, json_array_elements_text() 함수를 사용한다