SQL利用2022年3月24日DML、TCL

DML:データオペレータ

TCL:レンダーハンドラ

会員収入

INSERT INTO MEMBER2 (USERDATE, USERID, USERPW, USERNAME, USERAGE)
VALUES (CURRENT_DATE,'D','D','가',12);
SAVEPOINT S1; -- 저장지점

INSERT INTO MEMBER2 (USERDATE, USERID, USERPW, USERNAME, USERAGE)
VALUES (CURRENT_DATE,'E','D','가',12);
SAVEPOINT S2; -- 저장지점

DELETE FROM MEMBER2 WHERE USERID = 'D';
DELETE FROM MEMBER2 WHERE USERID = 'E';

COMMIT;
ROLLBACK;

SELECT * FROM MEMBER2;
ROLLBACK TO S1; -- D가 추가된 시점

メンバー情報の変更

--D라는 회원정보의 이름, 나이를 변경

UPDATE MEMBER2 SET USERNAME='AAA', USERAGE=22 WHERE USERID='D';
COMMIT;

-- 회원정보 일괄수정

UPDATE MEMBER2 SET USERNAME='AAA', USERAGE=22 WHERE USERID='B';
UPDATE MEMBER2 SET USERNAME='AAA', USERAGE=22 WHERE USERID='C';
UPDATE MEMBER2 SET USERNAME='AAA', USERAGE=22 WHERE USERID='D';
COMMIT;

UPDATE MEMBER2 SET
    USERNAME = (CASE
        WHEN USERID= 'B' THEN 'B이름'
        WHEN USERID= 'C' THEN 'C이름'
        WHEN USERID= 'D' THEN 'D이름'
    END),
    USERAGE = (CASE
        WHEN USERID= 'B' THEN 10
        WHEN USERID= 'C' THEN 11
        WHEN USERID= 'D' THEN 12
    END)
WHERE USERID IN ('B','C','D');
COMMIT;

削除

-- 일괄 삭제(조건1개)

DELETE FROM MEMBER2 WHERE USERID IN ('A1','A2','A3');
COMMIT;

-- 일괄삭제(조건2개 : 아이디와 나이가 일치하는 것)

DELETE FROM MEMBER2 
    WHERE (USERID, USERAGE) 
    IN (('A1',11),('A2',11),('A3',11));
COMMIT;

朝礼

-- 조회하기(나이는 콤마, 날짜는 포멧을 "년월일 시")

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;

-- 페이지네이션

SELECT * FROM
    (
        SELECT 
            M.*,
            ROW_NUMBER() OVER(ORDER BY USERID DESC) ROWN
        FROM MEMBER2 M
    )M1
WHERE M1.ROWN BETWEEN 1 AND 3;

-- 페이지네이션 + 검색

SELECT * FROM
    (
        SELECT 
            M.*,
            ROW_NUMBER() OVER(ORDER BY USERID DESC) ROWN
        FROM MEMBER2 M
        WHERE M.USERNAME LIKE '%' || '가' || '%'
    )M1
WHERE M1.ROWN BETWEEN 1 AND 4;

-- 나이가 0~9 "어린이" 10~19 "10대" 20~29 "20대" ... 기존데이터 + 연령대 로 조회

SELECT M.*,
    CASE 
        WHEN (USERAGE BETWEEN 0 AND 9) THEN '어린이'
        WHEN (USERAGE BETWEEN 10 AND 19) THEN '10대'     
        WHEN (USERAGE BETWEEN 20 AND 29) THEN '20대'     
        ELSE '30대'
    END 연령대  
FROM MEMBER2 M;


-- 날짜가 2022-03-23일 항목만 조회

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

-- 아이디가 a로 시작하는 모든 항목 조회

SELECT M.*, ROW_NUMBER() OVER ( ORDER BY USERID DESC) ROWN 
FROM MEMBER2 M WHERE M.USERID LIKE  'A' || '%';

SELECT M.* FROM MEMBER2 M WHERE M.USERID LIKE 'A' || '%';

-- 나이가 10~40의 회원 수 조회

SELECT COUNT(*) 회원수 FROM MEMBER2 M WHERE USERAGE>=10 AND USERAGE<=40;
SELECT COUNT(*)회원수 FROM MEMBER2 WHERE USERAGE BETWEEN 10 AND 40;

集約関数

--나이의 표준편차, 최대값 ...

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

MEMBER2, ITEM2

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

ALTER TABLE ITEM2 ADD ITEMSELLER VARCHAR2(30);

-- 외래키 연결

ALTER TABLE ITEM2 ADD CONSTRAINT FK_ITEM2_ITEMSELLER FOREIGN KEY(ITEMSELLER) 
    REFERENCES MEMBER2(USERID);

-- 시퀀스 SEQ_ITEM2_ITEMNO 시작값 1001

CREATE SEQUENCE SEQ_ITEM2_ITEMNO START WITH 1001 INCREMENT BY 1 NOCACHE NOMAXVALUE;

-- ITEM2에 물품 3개 추가

INSERT INTO ITEM2 (ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE, ITEMSELLER)
	VALUES (SEQ_ITEM2_ITEMNO.NEXTVAL, '배', 3000, 300, CURRENT_DATE, 'A');
INSERT INTO ITEM2 (ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE, ITEMSELLER)
	VALUES (SEQ_ITEM2_ITEMNO.NEXTVAL, '귤', 2000, 400, CURRENT_DATE, 'B');
INSERT INTO ITEM2 (ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE, ITEMSELLER)
	VALUES (SEQ_ITEM2_ITEMNO.NEXTVAL, '토마토', 1000, 550, CURRENT_DATE, 'D');

COMMIT;
ROLLBACK;

-- MEMBER2 + ITEM2 를 조인하여 물품번호, 물품명, 가격, 수량, 판매자 아이디, 판매자 이름 조회

SELECT * FROM MEMBER2 M, ITEM2 I WHERE M.USERID = I.ITEMSELLER;

SELECT I.ITEMNO 물품번호, I.ITEMNAME 물품명, I.ITEMPRICE 가격, I.ITEMQTY 수량, M.USERID 판매자아이디, M.USERNAME 판매자이름
FROM MEMBER2 M, ITEM2 I WHERE M.USERID = I.ITEMSELLER;

-- 판매자가 B인 물품의 물품번호, 가격, 판매자이름, 나이 조회

SELECT S.ITEMNO 물품번호, S.ITEMPRICE 가격, S.USERNAME 판매자이름, S.USERAGE 나이 
FROM (
    SELECT * FROM MEMBER2 M, ITEM2 I WHERE M.USERID = I.ITEMSELLER
    ) S 
WHERE ITEMSELLER = 'B';

SELECT I.ITEMNO 물품번호, I.ITEMPRICE 가격, M.USERNAME 판매자이름, M.USERAGE 나이 
FROM MEMBER2 M INNER JOIN ITEM2 I ON M.USERID = I.ITEMSELLER
WHERE ITEMSELLER = 'B';

-- 판매자별 물품수량, 재고수량합 조회
-- SELECT ITEMSELLER FROM 테이블 GROUP BY(ITEMSELLER)

SELECT ITEMSELLER 판매자, COUNT(*) 물품수량, SUM(ITEMQTY) 재고수량합
FROM ITEM2 GROUP BY(ITEMSELLER);

-- 판매자별 물품수량, 재고수량합계,  판매자이름, 판매자 나이 조회

SELECT I.*, M.USERNAME 이름, M.USERAGE 나이
FROM MEMBER2 M, (
    SELECT ITEMSELLER , COUNT(*) 물품수량, SUM(ITEMQTY)재고수량 FROM ITEM2 GROUP BY(ITEMSELLER)
    ) I 
WHERE M.USERID = I.ITEMSELLER;

-- 재고수량합계가 100이상인 판매자별 물품수량 조회 

SELECT ITEMSELLER, SUM(ITEMQTY) FROM ITEM2 GROUP BY(ITEMSELLER) HAVING SUM(ITEMQTY) >=200;

-- 판매자가 a인 물품의 물품수량, 물품가격평균 조회

SELECT COUNT(*), AVG(ITEMPRICE) FROM ITEM2 I WHERE I.ITEMSELLER='A';

-- 판매자별 재고수량이 가장 높은 것 1개씩 조회

SELECT ITEMSELLER, MAX(ITEMQTY) FROM ITEM2 GROUP BY(ITEMSELLER);

CREATE OR REPLACE VIEW ITEM2_VIEW AS SELECT 
    I.*, 
    ROW_NUMBER() OVER( PARTITION BY ITEMSELLER ORDER BY ITEMQTY DESC ) QTY
FROM ITEM2 I;

SELECT * FROM ITEM2_VIEW WHERE QTY=1;

SELECT * FROM (
    SELECT 
        I.*, 
        ROW_NUMBER() OVER( PARTITION BY ITEMSELLER ORDER BY ITEMQTY DESC ) QTY
    FROM ITEM2 I
) WHERE QTY=1;

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

ORDER2, MEMBER2, ITEM2

-- ORDER2 추가용 시퀀스 생성

CREATE SEQUENCE SEQ_ORDER2_ORDNO START WITH 10001 INCREMENT BY 1 NOMAXVALUE NOCACHE;

-- 주문데이터 추가(제약조건기본키, 물품코드외래키, 회원아이디외래키)

INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID) 
	VALUES (SEQ_ORDER2_ORDNO.NEXTVAL, 123, CURRENT_DATE, 1011, 'A');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID) 
	VALUES (SEQ_ORDER2_ORDNO.NEXTVAL, 123, CURRENT_DATE, 1012, 'A');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID) 
	VALUES (SEQ_ORDER2_ORDNO.NEXTVAL, 123, CURRENT_DATE, 1013, 'B');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID) 
	VALUES (SEQ_ORDER2_ORDNO.NEXTVAL, 123, CURRENT_DATE, 1013, 'C');
COMMIT;

-- ITEM2 + MEMBER2 + ORDER2 를 이용하여 주문내역 조회
-- (주문번호, 수량, 주문일자, 주문자이름, 주문자나이, 물품명, 가격, 주문금액)

SELECT * FROM ORDER2 O, ITEM2 I WHERE O.ITEMNO = I.ITEMNO;
SELECT * FROM ORDER2 O, MEMBER2 M WHERE O.USERID = M.USERID;

SELECT * FROM MEMBER2 M, ITEM2 I, ORDER2 O WHERE O.ITEMNO = I.ITEMNO AND O.USERID = M.USERID;

-- 일자별 주문수량, 주문총합, 물품명, 가격, 재고수량 

SELECT TO_CHAR(O.ORDDATE ,'YYYY-MM-DD') 주문일자, O.ITEMNO 물품번호, COUNT(*) 주문수량, SUM(ORDCNT) 주문개수 FROM ORDER2 O
GROUP BY( TO_CHAR(O.ORDDATE ,'YYYY-MM-DD'), O.ITEMNO );

-- 고객별 주문수량, 주문금액, 물품명, 가격

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