SQL 명령어와 함수 (3)

하루 10분 SQL (위키북스, 한상일 지음)에서 독학하며 모르거나 중요한 부분을 기록한 것 입니다.

 

https://book.naver.com/bookdb/book_detail.nhn?bid=16395921 

하루 10분 SQL

---

1. 트랜잭션의 이해 (여러 단위 작업을 한 번에 처리하기)

• 데이터베이스 작업에서 하나로 묶을 수 있는 업무를 가리킴
• 입력, 수정, 삭제 등 여러 작업을 포함하며, 데이터의 무결성을 보장하기 위해 작업 중 오류가 발생하면 작업 전체를 취소하고 작업 전 상태로 돌아감
• 데이터베이스를 조작하는 응용 프로그램의 일괄 작업에서 연관 테이블 간에 맞지 않는 정보 입력을 방지할 목적으로 쓰임

---

<고객코드가 '2017042'인 고객에 대한 각 테이블 정보>

TB_CUSTOMER (고객 테이블)

TB_POINT (포인트 테이블)

예제 1) 포인트 테이블에 고객코드가 '2017042'인 고객의 포인트 적립 내용을 입력하고 포인터 적립 내용 확인하기

-> INSERT 사용

INSERT INTO TB_POINT VALUES ('2017042', 5, '드라이기 구매 포인트 적립', 3200, '20190801234530');

SELECT *
FROM TB_POINT
WHERE CUSTOMER_CD = '2017042';

[실행결과]

 

예제 2) 고객 테이블에서 고객코드가 '2017042'인 고객의 누적포인트의 값을 포인트 테이블의 포인트 합으로 바꾸기

-> UPDATE, SUM, WHERE, GROUP BY 사용

UPDATE TB_CUSTOMER CU
SET CU.TOTAL_POINT = (SELECT SUM(CP.POINT) 
					FROM TB_POINT CP
                    WHERE CP.CUSTOMER_CD = CU.CUSTOMER_CD
                    GROUP BY CP.CUSTOMER_CD)
WHERE CP.CUSTOMER_CD = '2017042';

[실행결과]

작업 도중 오류를 발생시켜 부정합 상태로 만들기 위한 것

 

예제 3) 데이터 부정합 상태에서는 롤백으로 작업 전 상태로 돌아감

-- 포인트 적립 내용을 입력했지만 고객 테이블의 누적포인트는 바뀌지 않아 
-- 관련 테이블의 데이터가 맞지 않는 부정합 상태
ROLLBACK; -- 부정합 상태를 작업 전으로 돌리고 작업을 마침

[실행결과]

 

예제 4부터는 고객 테이블에 누적 포인트를 업데이트하고 COMMIT 명령으로 작업을 정상 종료할 것

 

예제 4) 예제 2와 동일

-> 포인트 테이블의 포인트 합을 고객 테이블의 누적포인트로 입력하는 쿼리인데 "CP.CUSTOMER_CD = '2017042'" 구문은 잘못된 것으로 "CU.CUSTOMER_CD = '2017042'" 가 맞는 구문

UPDATE TB_CUSTOMER CU
SET CU.TOTAL_POINT = (SELECT SUM(CP.POINT) 
					FROM TB_POINT CP
                    WHERE CP.CUSTOMER_CD = CU.CUSTOMER_CD
                    GROUP BY CP.CUSTOMER_CD)
WHERE CU.CUSTOMER_CD = '2017042';

-- 누적 포인트 값을 확인
SELECT *
FROM TB_CUSTOMER
WHERE CUSTOMER_CD = '2017042';

[실행결과]

 

예제 5) 포인트 테이블과 고객 테이블에 바뀐 값을 데이터베이스에 반영하기

-- 관련 테이블의 데이터가 정확한 상태이므로 데이터베이스에 반영하고 작업을 마침
COMMIT;

[실행결과]

 

2. 서브쿼리(SUB QUERY)의 이해 (SELECT 구문 내에서 SELECT 작업하기)

<구문 #1>
SELECT S1.필드명1, S1.필드명2,  ... , S1.필드명n
FROM (
           SELECT 필드명1, 필드명2, ... , 필드명n
           FROM 테이블명
           WHERE 조건식
) S1
WHERE 조건식;

<구문 #2>
SELECT S1.필드명1, S1.필드명2,  ... , S1.필드명n
FROM 테이블명
WHERE 필드명 [IN | =] (SELECT 필드명 FROM 테이블명 WHERE 조건식);

---

TB_POINT

TB_CUSTOMER

 

예제 1) 포인트 테이블에서 고객코드가 '2019000' 이후인 고객 중 포인트 합이 10,000보다 큰 여성을 대상으로 고객코드, 고객명, 포인트 합계를 검색하기

 

★ 전략: 고객코드가 '2019000' 이후인 고객 구하기 -> 고객코드별 포인트 합이 10,000 이상인 고객 구하기 -> 성별이 여성인 고객을 대상으로 고객명(CUSTOMER_NM)을 고객 테이블에서 구하기

 

① 서브쿼리 S1 : 포인트 테이블(TB_POINT)에서 고객코드(CUSTOMER_CD)가 '2019000' 이상인 고객의 고객코드 그룹을 만들고 포인트합(SUM(POINT)) 구하기 -> SUM, GROUPBY, WHERE 사용

SELECT CUSTOMER_CD, SUM(POINT) AS TOT_POINT
FROM TB_POINT 
WHERE CUSTOMER_CD >= '2019000'
GROUP BY CUSTOMER_CD;

[실행결과]

 

② 서브쿼리 S2 : S1의 포인트합(S1.TOT_POINT)이 10,000보다 큰 데이터를 구하기

-> FROM에 서브쿼리 S1 넣고 WHERE 이용

SELECT S1.CUSTOMER_CD, S1.TOT_POINT
FROM (SELECT CUSTOMER_CD, SUM(POINT) AS TOT_POINT
    FROM TB_POINT 
    WHERE CUSTOMER_CD >= '2019000'
    GROUP BY CUSTOMER_CD
) S1
WHERE S1.TOT_POINT > 10000;

[실행결과]

 

③ S2의 고객코드(S2.CUSTOMER_CD)를 대상으로 고객 테이블(TB_CUSTOMER)에서 성별이 여성인 고객의 고객코드와 같으면 검색하기 -> WHERE ~ IN 사용

SELECT *
FROM (SELECT S1.CUSTOMER_CD, S1.TOT_POINT
    FROM (SELECT CUSTOMER_CD, SUM(POINT) AS TOT_POINT
    FROM TB_POINT 
    WHERE CUSTOMER_CD >= '2019000'
    GROUP BY CUSTOMER_CD
    ) S1 -- 서브쿼리 1
WHERE S1.TOT_POINT > 10000) S2 -- 서브쿼리 2
WHERE S2.CUSTOMER_CD IN (SELECT CUSTOMER_CD
                        FROM TB_CUSTOMER
                        WHERE MW_FLG = 'W');

[실행결과]

 

3. EXISTS (서브쿼리 조건과 같으면 검색하기)

SELECT 검색필드
FROM 테이블
WHERE [NOT] EXISTS (SELECT [필드|의미없는 숫자] FROM 테이블 WHERE 조건식);

---

TB_CUSTOMER

TB_POINT

예제 1) 고객 포인트 테이블의 고객코드가 고객 테이블의 남성인 고객코드와 같으면 포인트 적립 내용을 검색하기

-> WHERE EXISTS, AND 사용

SELECT *
FROM TB_POINT CP
WHERE EXISTS (SELECT 'A' FROM TB_CUSTOMER CU
            WHERE CU.CUSTOMER_CD = CP.CUSTOMER_CD AND CU.MW_FLG = 'M');

cf) SELECT 구문의 'A' -> 의미 없는 값으로서 검색 결과가 있는지를 나타냄

 

[실행결과]

 

4. 검색 조건을 입력해서 SQL을 실행 (검색 조건을 입력)

<구문 #1>  필드 = :검색조건명
<구문 #2>  함수(:검색조건명)

 

예제 1) 성적 테이블에서 반코드를 ':v1' 검색 조건명으로 설정해 'A', 'D', 'F'를 각각 입력하고 검색하기

SELECT *
FROM TB_GRADE
WHERE CLASS_CD = :v1
ORDER BY CLASS_CD, STUDENT_NO;

[실행결과]

조건값 입력

'A' 입력

'D' 입력

'F' 입력 (데이터가 없는 상태)

예제 2) 고객 테이블에서 ':고객명', ':성별'을 검색 조건명으로 설정해 '고객명' 조건에 '이'를 입력하고, '성별' 조건에 'W'를 입력해 검색하기

-> LIKE, ||, AND 사용

SELECT *
FROM TB_CUSTOMER
WHERE CUSTOMER_NM LIKE '%' || :고객명 || '%' -- 고객명에 '이' 포함
AND MW_FLG = :성별;

[실행결과]