SQL활용 #11 - DDL / DML / TCL

김형우·2022년 3월 24일
0

SQL 활용

목록 보기
12/14

정리 안하고 넘어갈꺼

전체코드

-- TCL
-- COMMIT;
-- ROLLBACK;
-- SAVEPOINT;

-- 컬럼 명시 필요함
-- 새 컬럼을 추가해도 작동하기 때문
INSERT INTO MEMBER2 (USERID, USERPW, USERNAME, USERAGE, USERDATE)
    VALUES('Q','Q','QMAN',24,CURRENT_DATE);    
SAVEPOINT S1; -- 저장지점

INSERT INTO MEMBER2 (USERID, USERPW, USERNAME, USERAGE, USERDATE)
    VALUES('R','R','RMAN',34,CURRENT_DATE);
SAVEPOINT S2; -- 저장지점    

DELETE FROM MEMBER2 WHERE USERID = 'Q';
DELETE FROM MEMBER2 WHERE USERID = 'R';

SELECT * FROM MEMBER2;

ROLLBACK TO S1;
ROLLBACK TO S2;

COMMIT;
ROLLBACK;

---------------------------------------------------------------------------
-- 수정
    -- Q라는 회원정보의 이름 나이를 변경
UPDATE MEMBER2 SET USERNAME = 'QQQ', USERAGE = 111 WHERE USERID = 'Q';
SELECT * FROM MEMBER2;    

-- 일괄수정
    -- SPRING에서는 UPDATE 할때마다 AUTO COMMIT 한다
    -- 하나씩 하다가 서버가 꺼지면 롤백이 안됨
    -- 때문에 일괄수정 필요
    -- 반복문 X 쓰지마
SELECT * FROM MEMBER2;      
    
UPDATE MEMBER2 SET USERNAME = 'QQQ', USERAGE = 111 WHERE USERID = 'A1';
UPDATE MEMBER2 SET USERNAME = 'QQQ', USERAGE = 111 WHERE USERID = 'A2';
UPDATE MEMBER2 SET USERNAME = 'QQQ', USERAGE = 111 WHERE USERID = 'A3';

-- 일괄수정
UPDATE MEMBER2 SET
    USERNAME = 
    (CASE
        WHEN USERID = 'A1' THEN 'A1수정'
        WHEN USERID = 'A2' THEN 'A2수정'
        WHEN USERID = 'A3' THEN 'A3수정'
    END),
    USERAGE = 
    (CASE
        WHEN USERID = 'A1' THEN 11
        WHEN USERID = 'A2' THEN 22
        WHEN USERID = 'A3' THEN 33
    END)
WHERE USERID IN ('A1', 'A2', 'A3');   

SELECT * FROM MEMBER2;  
COMMIT;
ROLLBACK;

---------------------------------------------------------------------------

-- 일괄삭제(조건1개 : 아이디가 일치)
DELETE FROM MEMBER2 WHERE USERID IN ('A4', 'A7', 'A6');
SELECT * FROM MEMBER2;  
COMMIT;

-- 일괄삭제(조건2개 : 아이디와 나이가 일치 AND 조건)
DELETE FROM MEMBER2 
    WHERE (USERID, USERAGE) 
    IN (('A8',28),('A9',29),('A10',30)); 
SELECT * FROM MEMBER2;  
COMMIT;
ROLLBACK;

---------------------------------------------------------------------------
-- 조회하기
    -- 제일 어려움
    -- 테이블명을 축약해서 .을 찍는 방향으로 가야 좋음
    -- 조인을 많이 하기 때문
-- 조회하기 (나이는 콤마, 날짜는 포맷을 "년월일 시")    
SELECT 
    M.USERID 아이디,
    M.USERNAME 이름,
    M.USERAGE*2 나이_2배,
    TO_CHAR(M.USERAGE, '9,9,9') 나이_포맷변경,
    TO_CHAR(M.USERDATE, 'YYYY"년 "MM"월 "DD"일 "HH24"시"') 날짜    
FROM MEMBER2 M;
    
-- 페이지네이션
    -- ROW_NUMBER() OVER(ORDER BY USERID ASC)
SELECT * FROM    
    (SELECT 
        M.*,
        ROW_NUMBER() OVER(ORDER BY USERID ASC) ROWNO
    FROM 
        MEMBER2 M) M1
WHERE M1.ROWNO BETWEEN 1 AND 4; 

/*
SELECT 
    M.*,
    ROW_NUMBER() OVER(ORDER BY USERID ASC) ROWNO
FROM 
    MEMBER2 M
WHERE M.ROWNO = 1; -- 안됨, 새로 만든것이기 때문
*/

-- 페이지네이션 + 검색
SELECT M.* FROM MEMBER2 M;
-- 검색    
    -- 이름에 'A'가 들어있는사람을 뽑아서 번호(ROWNO)를 매김
SELECT 
    M.*,
    ROW_NUMBER() OVER(ORDER BY USERID ASC) ROWNO
FROM 
    MEMBER2 M
WHERE 
    M.USERNAME LIKE '%'||'A'||'%';
-- 검색 + 페이지네이션
    -- 위의 테이블을 사용
SELECT * FROM
    (SELECT 
        M.*,
        ROW_NUMBER() OVER(ORDER BY USERID ASC) ROWNO
    FROM 
        MEMBER2 M
    WHERE 
        M.USERNAME LIKE '%'||'A'||'%') M1
WHERE M1.ROWNO BETWEEN 1 AND 4;

---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- 실습
-- 나이가 0~9 "어린이" 10~19 "10대" 20~29 "20대" ... 기존데이터 + 연령대 로 조회
-- 날짜가 2022-03-23일 항목만 조회
-- 아이디가 a로 시작하는 모든 항목 조회
-- 나이가 10~40의 회원 수 조회
---------------------------------------------------------------------------
-- 나이가 0~9 "어린이" 10~19 "10대" 20~29 "20대" ... 기존데이터 + 연령대 로 조회
SELECT M.* FROM MEMBER2 M;
SELECT 
    M.*,
    (CASE 
        WHEN M.USERAGE >=  0 AND M.USERAGE < 10 THEN '어린이'
        WHEN M.USERAGE >= 10 AND M.USERAGE < 20 THEN '10대'
        WHEN M.USERAGE >= 20 AND M.USERAGE < 30 THEN '20대'
        WHEN M.USERAGE >= 30 AND M.USERAGE < 40 THEN '30대'
        WHEN M.USERAGE >= 40 AND M.USERAGE < 50 THEN '40대'
        ELSE '어르신'
    END) 연령대
FROM MEMBER2 M; 

-- 날짜가 2022-03-23일 항목만 조회
SELECT M.* FROM MEMBER2 M;
SELECT M.* FROM MEMBER2 M WHERE M.USERDATE = '22/03/23'; -- 안됨
SELECT M.*, TO_CHAR(USERDATE, 'YYYY-MM-DD') 날짜 FROM MEMBER2 M;
SELECT * FROM 
    (SELECT M.*, TO_CHAR(USERDATE, 'YYYY-MM-DD') 날짜 FROM MEMBER2 M)
WHERE 날짜 = '2022-03-23'; 

-- 쌤꺼
SELECT M.* FROM MEMBER2 M
    WHERE TO_CHAR(USERDATE, 'YYYY-MM-DD') = '2022-03-23';

-- 아이디가 a로 시작하는 모든 항목 조회
SELECT M.* FROM MEMBER2 M;
SELECT M.* FROM MEMBER2 M WHERE SUBSTR(M.USERID, 0, 1) = 'A'; 

-- 나이가 10~40의 회원 수 조회
SELECT M.* FROM MEMBER2 M;
SELECT COUNT(*) FROM MEMBER2 M WHERE M.USERAGE >= 10 AND M.USERAGE <= 40;

-- 나이의 표준편차, 최대값 ...
SELECT 
    STDDEV(USERAGE) 표준편차, 
    MAX(USERAGE) 최대값, 
    MIN(USERAGE) 최소값, 
    SUM(USERAGE) 합, 
    AVG(USERAGE) 평균 
FROM 
    MEMBER2 M;

---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- MEMBER2의 USERID를 ITEM2테이블에 ITEMSELLER외래키 설정
-- ITEM2 ITEMSELLER VARCHAR2() 컬럼 추가, 외래키 연결

-- 시퀀스 SEQ_ITEM2_ITEMNO 시작값 1001
    -- ITEM2에 물품 3개 추가
---------------------------------------------------------------------------

-- MEMBER2의 USERID를 ITEM2테이블에 ITEMSELLER외래키 설정
    -- ITEM2 ITEMSELLER VARCHAR2() 컬럼 추가, 외래키 연결
SELECT M.* FROM MEMBER2 M;
SELECT I.* FROM ITEM2 I;
-- ITEM2 ITEMSELLER VARCHAR2() 컬럼 추가
ALTER TABLE ITEM2 ADD ITEMSELLER VARCHAR2(15);
-- ITEM2 ITEMSELLER VARCHAR2() 컬럼 변경 15->30
ALTER TABLE ITEM2 MODIFY ITEMSELLER VARCHAR2(30);
-- 외래키 연결
ALTER TABLE ITEM2 ADD CONSTRAINT FK_ITEM2_MEMBER2 FOREIGN KEY(ITEMSELLER) REFERENCES MEMBER2(USERID);

-- 시퀀스 SEQ_ITEM2_ITEMNO 시작값 1001
CREATE SEQUENCE SEQ_ITEM2_ITEMNO
    START WITH 1001 INCREMENT BY 1 NOMAXVALUE NOCACHE;
    
-- ITEM2에 물품 3개 추가
-- 기본키 제약조건 시퀀스, 외래키 제약조건 ITEMSELLER는 MEMBER2에 있는 아이디만 가능
INSERT INTO ITEM2(ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE, ITEMSELLER)
    VALUES(SEQ_ITEM2_ITEMNO.NEXTVAL, '코젤', 7000, 55, CURRENT_DATE, 'A');
    
-- JOIN 후 VIEW 만들어야지~
SELECT I.*, M.USERNAME FROM ITEM2 I, MEMBER2 M WHERE I.ITEMSELLER = M.USERID;

---------------------------------------------------------------------------
---------------------------------------------------------------------------

-- MEMBER2 + ITEM2를 조인하여 물품번호, 물품명, 가격, 수량, 판매자아이디, 판매자이름 조회
    -- SELECT * FROM 테이블1, 테이블  WHERE 조건
-- 판매자가 'B'인 물품의 물품번호, 가격, 판매자이름, 나이 조회
-- 판매자별 물품수량, 재고수량 합계 조회
    -- SELECT ITEMSELLER FROM 테이블 GROUP BY(ITEMSELLER)
-- 판매자별 물품수량, 재고수량, 판매자이름, 판매자나이 조회 
-- 재고수량합계가 100이상인 판매자별 물품수량 조회 
-- 판매자가 a인 물품의 물품수량, 물품가격평균 조회
-- 판매자별 재고수량이 가장 높은 것 1개씩 조회

---------------------------------------------------------------------------
-- MEMBER2 + ITEM2를 조인하여 물품번호, 물품명, 가격, 수량, 판매자아이디, 판매자이름 조회
SELECT M.* FROM MEMBER2 M;
SELECT I.* FROM ITEM2 I;
SELECT I.ITEMNO, I.ITEMNAME, I.ITEMPRICE, I.ITEMQTY, M.USERID, M.USERNAME FROM ITEM2 I, MEMBER2 M WHERE I.ITEMSELLER = M.USERID;

-- 판매자가 'B'인 물품의 물품번호, 가격, 판매자이름, 나이 조회
-- 1. SELECT JOIN
SELECT I.ITEMNO, I.ITEMPRICE, M.USERNAME, M.USERAGE FROM ITEM2 I, MEMBER2 M WHERE I.ITEMSELLER = M.USERID;
-- 2. SELECT WHERE
SELECT IM.ITEMNO, IM.ITEMPRICE, IM.USERNAME, IM.USERAGE FROM
    (SELECT I.ITEMNO, I.ITEMPRICE, M.USERNAME, M.USERAGE, M.USERID FROM ITEM2 I, MEMBER2 M WHERE I.ITEMSELLER = M.USERID) IM
WHERE IM.USERID = 'B';    

-- 3. 쌤꺼
SELECT I.ITEMNO, I.ITEMPRICE, M.USERNAME, M.USERAGE, M.USERID FROM ITEM2 I, MEMBER2 M WHERE I.ITEMSELLER = M.USERID AND M.USERID = 'B';

-- 판매자별 물품수량, 재고수량 합계 조회
SELECT I.ITEMSELLER FROM ITEM2 I GROUP BY(I.ITEMSELLER);
SELECT I.ITEMSELLER 판매자, COUNT(*) 물품수량, SUM(I.ITEMQTY) 재고수량 FROM ITEM2 I GROUP BY(I.ITEMSELLER);

-- 판매자별 COUNT(물품수량), SUM(재고수량합계),  판매자이름, 판매자 나이 조회
SELECT M.*, I1.* 
    FROM 
        MEMBER2 M, 
        (SELECT I.ITEMSELLER 판매자, COUNT(*) 물품수량, SUM(I.ITEMQTY) 재고수량 FROM ITEM2 I GROUP BY(I.ITEMSELLER)) I1
    WHERE I1.판매자 = M.USERID;    

-- 재고수량합계가 100이상인 판매자별 물품수량 조회 
SELECT IM.판매자, IM.물품수량 FROM
    (SELECT I.ITEMSELLER 판매자, COUNT(*) 물품수량, SUM(I.ITEMQTY) 재고수량 FROM ITEM2 I GROUP BY(I.ITEMSELLER)) IM
WHERE IM.재고수량 >= 100; 
-- 재고수량합계가 100이상인 판매자별 물품수량 조회 
    -- 쌤꺼
    -- 그룹의 조건은 HAVING
SELECT ITEMSELLER, SUM(ITEMQTY) 
    FROM ITEM2 I 
    GROUP BY(ITEMSELLER)
    HAVING SUM(ITEMQTY)>=100;    

-- 판매자가 a인 물품의 물품수량, 물품가격평균 조회
SELECT * FROM ITEM2;
SELECT * FROM MEMBER2;
-- 1. SELECT GROUP
SELECT I.ITEMSELLER, COUNT(*), AVG(I.ITEMPRICE) FROM ITEM2 I
    GROUP BY(I.ITEMSELLER);
-- 2. SELECT WHERE
SELECT IG.* FROM
    (SELECT I.ITEMSELLER 판매자, COUNT(*) 물품수량, AVG(I.ITEMPRICE) 물품가격평균 FROM ITEM2 I
    GROUP BY(I.ITEMSELLER)) IG
WHERE IG.판매자 = 'A';   
    -- 쌤꺼
SELECT ITEMSELLER, SUM(ITEMQTY), COUNT(*) 
FROM ITEM2 I
WHERE ITEMSELLER ='B'
GROUP BY(ITEMSELLER);    

-- 판매자별 재고수량이 가장 높은 것 1개씩 조회
-- 판매자별 재고수량 랭크
SELECT 
    I.*, 
    M.*, 
    RANK() OVER(PARTITION BY I.ITEMSELLER ORDER BY I.ITEMQTY DESC) RANK
FROM 
    ITEM2 I, MEMBER2 M
WHERE I.ITEMSELLER = M.USERID;
-- 제일 높은것 1개씩 조회
SELECT IM.ITEMNO, IM.ITEMNAME, IM.ITEMQTY, IM.ITEMSELLER, IM.USERNAME FROM
    (SELECT 
        I.*, 
        M.*, 
        RANK() OVER(PARTITION BY I.ITEMSELLER ORDER BY I.ITEMQTY DESC) RANK
    FROM 
        ITEM2 I, MEMBER2 M
    WHERE I.ITEMSELLER = M.USERID) IM
WHERE IM.RANK = 1;

    -- 쌤꺼
SELECT I.*, ROW_NUMBER() OVER(PARTITION BY ITEMSELLER ORDER BY I.ITEMQTY DESC) FROM ITEM2 I;   
-- VIEW로 만듦
CREATE OR REPLACE VIEW ITEM2_VIEW AS
    SELECT I.*, ROW_NUMBER() OVER(PARTITION BY ITEMSELLER ORDER BY I.ITEMQTY DESC) 랭크 FROM ITEM2 I;
    
SELECT * FROM ITEM2_VIEW;

SELECT * FROM ITEM2_VIEW WHERE 랭크 = 1;

---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- ITEM2 + MEMBER2 + ORDER2를 이용하여 주문내역 조회
    -- (주문번호, 수량, 주문일자, 주문자이름, 주문자나이, 물품명, 가격, 주문금액)
-- 일자별 주문수량, 주문총합, 물품명, 가격, 재고수량
-- 고객별 주문수량, 주문금액, 물품명, 가격
---------------------------------------------------------------------------
SELECT * FROM ITEM2;
SELECT * FROM MEMBER2;    
SELECT * FROM ORDER2;    

-- 주문추가
    -- 시퀀스 생성
CREATE SEQUENCE SEQ_ORDER2_ORDNO 
    START WITH 501 INCREMENT BY 1 NOMAXVALUE NOCACHE;

-- 주문추가
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
    VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 5, CURRENT_DATE, 1006, 'A2');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
    VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 2, CURRENT_DATE, 1007, 'A2');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
    VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 44, CURRENT_DATE, 1008, 'A2');    
COMMIT;

---------------------------------------------------------------------------
-- ITEM2 + MEMBER2 + ORDER2를 이용하여 주문내역 조회
    -- (주문번호, 수량, 주문일자, 주문자이름, 주문자나이, 물품명, 가격, 주문금액)
SELECT O.*, I.* FROM ORDER2 O, ITEM2 I;    
SELECT O.*, I.* FROM ORDER2 O, ITEM2 I WHERE O.ITEMNO = I.ITEMNO;    
SELECT O.ORDNO, O.ORDCNT, O.ORDDATE, I.ITEMNAME, I.ITEMPRICE, I.ITEMSELLER FROM ORDER2 O, ITEM2 I WHERE O.ITEMNO = I.ITEMNO;

SELECT 
    OI.*, M.USERNAME, M.USERAGE 
    FROM 
        (SELECT O.ORDNO, O.ORDCNT, O.ORDDATE, I.ITEMNAME, I.ITEMPRICE, I.ITEMSELLER FROM ORDER2 O, ITEM2 I WHERE O.ITEMNO = I.ITEMNO) OI,
        MEMBER2 M
WHERE M.USERID = OI.ITEMSELLER;    
    
SELECT 
    O.ORDNO, O.ORDCNT, O.ORDDATE, M.USERNAME, M.USERAGE, I.ITEMNAME, I.ITEMPRICE 
    FROM ORDER2 O, ITEM2 I, MEMBER2 M
    WHERE O.ITEMNO = I.ITEMNO AND O.USERID = M.USERID;

SELECT MIO.* FROM 
    (SELECT 
        O.ORDNO, O.ORDCNT, O.ORDDATE, M.USERNAME, M.USERAGE, I.ITEMNAME, I.ITEMPRICE 
        FROM ORDER2 O, ITEM2 I, MEMBER2 M
        WHERE O.ITEMNO = I.ITEMNO AND O.USERID = M.USERID) MIO;

    
SELECT * FROM ITEM2;
SELECT * FROM MEMBER2;    
SELECT * FROM ORDER2;     

---------------------------------------------------------------------------
-- 일자별 주문수량, 주문총합, 물품명, 가격, 재고수량
SELECT 
    TO_CHAR(O.ORDDATE, 'YYYY-MM-DD'), 
    O.ITEMNO,
    O.ORDNO
FROM 
    ORDER2 O
GROUP BY (TO_CHAR(O.ORDDATE, 'YYYY-MM-DD'), O.ITEMNO, O.ORDNO);

    -- 그룹 + ORDER2
SELECT 
    OG.ODATE, O.ORDCNT, I.ITEMNAME, I.ITEMPRICE, I.ITEMQTY
    FROM 
        ORDER2 O,
        (SELECT 
            TO_CHAR(O.ORDDATE, 'YYYY-MM-DD') ODATE, 
            O.ITEMNO OITEMNO,
            O.ORDNO OORDNO
        FROM 
            ORDER2 O
        GROUP BY (TO_CHAR(O.ORDDATE, 'YYYY-MM-DD'), O.ITEMNO, O.ORDNO))OG,
        ITEM2 I
    WHERE O.ORDNO = OG.OORDNO AND O.ITEMNO = OG.OITEMNO AND OG.OITEMNO = I.ITEMNO;
    
    
    
SELECT 
    OI.ODATE, OI.ITEMNAME, OI.ITEMPRICE, OI.ITEMQTY, COUNT(*), SUM(OI.ORDCNT)
    FROM 
        (SELECT 
            OG.ODATE, O.ORDCNT, I.ITEMNAME, I.ITEMPRICE, I.ITEMQTY
            FROM 
                ORDER2 O,
                (SELECT 
                    TO_CHAR(O.ORDDATE, 'YYYY-MM-DD') ODATE, 
                    O.ITEMNO OITEMNO,
                    O.ORDNO OORDNO
                FROM 
                    ORDER2 O
                GROUP BY (TO_CHAR(O.ORDDATE, 'YYYY-MM-DD'), O.ITEMNO, O.ORDNO)) OG,
                ITEM2 I
            WHERE O.ORDNO = OG.OORDNO AND O.ITEMNO = OG.OITEMNO AND OG.OITEMNO = I.ITEMNO) OI
    GROUP BY (OI.ODATE, OI.ITEMNAME, OI.ITEMPRICE, OI.ITEMQTY, COUNT(*), SUM(OI.ORDCNT));        
            

---------------------------------------------------------------------------
-- 고객별 주문수량, 주문금액, 물품명, 가격
SELECT 
    O.USERID 고객아이디, 
    O.ITEMNO 물품코드, 
    SUM(O.ORDCNT) 주문수량  
FROM 
    ORDER2 O 
GROUP BY(O.USERID, O.ITEMNO);

SELECT 
    O.ORDNO, O.ORDCNT, O.ORDDATE, M.USERNAME, M.USERAGE, I.ITEMNAME, I.ITEMPRICE 
    FROM ORDER2 O, ITEM2 I, MEMBER2 M
    WHERE O.ITEMNO = I.ITEMNO AND O.USERID = M.USERID;
       
        
  
    
    
     
profile
The best

0개의 댓글