오라클 SQL DML 데이터조작어

● 데이터 조작어(DML. Data Manipulation Language)
INSERT: 데이터 입력
UPDATE: 데이터 수정
DELETE: 데이터 삭제
SELECT: 데이터 조회

---

● 추가

INSERT INTO 테이블명(컬럼명) VALUES(추가값);

-- 제약조건: 기본키, 성별
INSERT INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1)
VALUES('D', 'D', 16, '010-0000-0004', 'M', CURRENT_DATE, CURRENT_DATE); 

<< 이 부분만 계속 수정하고 컨트럴 엔터치면 INSERT 작동

COMMIT; -- 적용
ROLLBACK; -- 되돌리기

● 일괄추가

INSERT ALL 
INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1)
VALUES('E', 'D', 16, '010-0000-0004', 'M', CURRENT_DATE, CURRENT_DATE)
INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1)
VALUES('F', 'D', 16, '010-0000-0004', 'F', CURRENT_DATE, CURRENT_DATE)
INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1)
VALUES('G', 'D', 16, '010-0000-0004', 'M', CURRENT_DATE, CURRENT_DATE)

SELECT * FROM DUAL;

※ 외래키가 있는 테이블에 자료를 넣으려면, 부모 테이블에 외래키의 항목이 있어야 그 항목과 일치하는 값을 자녀 키에 넣을 수 있음
예를 들면 A테이블의 NO값을 B테이블에서 외래키로 사용한다면
A테이블의 NO값이 존재해야 B테이블 외래키로 A테이블의 NO값을 입력할 수 있음

---

● 조회

SELECT * FROM MEMBER3;

SELECT * FROM MEMBER3 WHERE ID IN('A', 'B', 'C') ORDER BY ID DESC;

● 검색. 제목에 B가 포함된 것

SELECT NO, TITLE, WRITER, HIT, REGDATE FROM BOARD3
    WHERE TITLE LIKE '%' || 'B' || '%'
    ORDER BY NO DESC;

● 페이지네이션

SELECT * FROM (
    SELECT 
        NO, TITLE, WRITER, HIT, REGDATE DT, ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN
    FROM 
        BOARD3
) WHERE ROWN BETWEEN 1 AND 3;

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

● 페이지네이션 + 검색(이름에 '가'가 포함되어 있는 것 가져와서 번호매김)

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

※ 컬럼명 변경 - 항목 뒤 한칸 띄우고 적기

VIEW. 원본 데이터를 뷰로 만들어서 결국 뷰로 보여주는 것임.

CREATE OR REPLACE VIEW BOARD33 AS
SELECT
    NO, TITLE, WRITER, HIT, REGDATE DT,
    ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN
FROM
    BOARD3;

SELECT * FROM BOARD33;

● 조회(나이: 콤마, 날짜: 년, 월, 일, 시)

SELECT 
    M.*, 
    TO_CHAR(USERAGE, '9,9,9') 나이,
    TO_CHAR(USERDATE, 'YYYY-MM-DD HH24') 날짜
FROM MEMBER2 M;

SELECT 
	M.USERID,
    M.USERAGE * 2, 
    TO_CHAR(USERAGE, '9,9,9') 나이,
    TO_CHAR(USERDATE, 'YYYY-MM-DD HH24') 날짜
FROM MEMBER2 M;

---

● 수정

UPDATE MEMBER3 SET AGE=22, PHONE='010-2242-5678' WHERE ID='C';

● CASE END문

UPDATE MEMBER3 SET AGE =
    CASE
        WHEN (AGE >= 0 AND AGE <=300) THEN 200
        ELSE 300
    END; 

● 일괄수정

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

---

● 삭제
DELETE FROM 테이블명 WHERE 조건;

DELETE FROM BOARD3 WHERE NO=2;

외래키로 빠져나가있는 값은 삭제가 안됨. 그래서 외래키로 나간 값을 제외하고 UPDATE 해야함.

● 일괄삭제(조건 1개)

DELETE FROM MEMBER2 WHERE USERID IN ('A', 'B', 'C');

● 일괄삭제(조건 2개. 아이디와 나이 모두 일치. AND). 일괄 특)배열로 데이터 전송

DELETE FROM MEMBER2 WHERE (USERID, USERAGE) IN (('A', 23), ('B', 24), ('C', 25));

---

● 시퀀스 생성

CREATE SEQUENCE SEQ_BOARD3_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;

INSERT INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
VALUES(SEQ_BOARD3_NO.NEXTVAL, 'A', 'A', 'A', '1', CURRENT_DATE);

create sequence seq_테이블 이름-필드이름
 increment by n   <== 증가값 (default 1)
 start with n   <== 시작값 (default 1)
 maxvalue n | nomaxvalue  <== 최고값 (nomaxvalue default 10*E27)
 minvalue n | nominvalue  <== 최소값 (nominvalue default 1)
 cycle | nocycle   <== 최소,최고값이후 값의 생성유무 (default nocycle)
 cache | nocache   <== 메모리에 유지할 값의 수 (default 20)

● 시퀀스 조회

SELECT SEQ_BOARD3_NO.CURRVAL FROM DUAL;
select * from user_sequences;

● 시퀀스 값조회

select seq_bbs_no.currval from dual;

● 시퀀스 삭제

drop sequence seq_bbs_no;

---

● COMMIT, ROLLBACK

COMMIT; -- 적용
ROLLBACK; -- 되돌리기

COMMIT하면 DB에 영구저장.
COMMIT하고 ROLLBACK하면 ROLLBACK 안됨

COMMIT안하고 ROLLBACK하면 ROLLBACK 작동(이전 COMMIT 직전 상태로 ROLLBACK)

---

● 실습

● 시퀀스 생성(SEQ_ITEM1_ITEMNO). 시작숫자 1001, 증가값 1, NOMAXVALUE

CREATE SEQUENCE SEQ_ITEM1_ITEMNO START WITH 1001 INCREMENT BY 1 NOMAXVALUE;

● ITEM1 테이블에 시퀀스를 이용해서 5개 추가

INSERT INTO ITEM1(ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE)
VALUES(SEQ_ITEM1_ITEMNO.NEXTVAL, 'E', 100, 50, CURRENT_DATE);

● 재고수량 100 미만은 100으로 변경

UPDATE ITEM1 SET ITEMQTY =100 WHERE ITEMQTY < 100;  

● ITEM1에 "검색어" 가 포함된것 조회 ex)검색어 문자 '1'

SELECT * FROM ITEM1 WHERE ITEMNAME LIKE '%' ||'1'||'%';

● MEMBER1에 회원 5명 추가

INSERT INTO MEMBER1(USERID, USERPW, USERNAME, USERAGE, USERDATE, USERADDR)
VALUES('B', 'B', 'B', 65, CURRENT_DATE, 'B시');

● 회원이름 순으로 오름차순 정렬 조회

SELECT * FROM MEMBER1 ORDER BY USERNAME ASC;

● 나이가 30 이상인 사람만 조회

SELECT * FROM MEMBER1 WHERE USERAGE >= 30;

● 시퀀스 생성(SEQ_ORDER_ORDNO). 시작숫자 10001, 1, NOMAXVALUE

CREATE SEQUENCE SEQ_ORDER1_ORDNO START WITH 10001 INCREMENT BY 1 NOMAXVALUE;

● 주문(ORDER1) 3건 추가하기(외래키 ITEM1, MEMBER1)

INSERT INTO ORDER1(ORDNO, ORDCNT, ORDDATE, ORDITEM, ORDID)
VALUES(SEQ_ORDER1_ORDNO.NEXTVAL, 5000, CURRENT_DATE, 1005, 'E');

● 주문내역을 주문일자 기준으로 내림차순 정렬 조회

SELECT * FROM ORDER1 ORDER BY ORDDATE DESC;

● 주문수량이 1000 이상인 것만 조회

SELECT * FROM ORDER1 WHERE ORDCNT >= 1000;

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

SELECT M.*, 
    CASE 
        WHEN (USERAGE >=0 AND USERAGE<=9) THEN '어린이' 
        WHEN (USERAGE >=10 AND USERAGE<=19) THEN '10대'
        WHEN (USERAGE >=20 AND USERAGE<=29) THEN '20대'
        ELSE '나머지'
    END 연령대 
FROM MEMBER2 M;

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

SELECT M.* FROM MEMBER2 M
WHERE TO_CHAR(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%';

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

SELECT M.USERNAME, COUNT(*) 
FROM MEMBER2 M WHERE USERAGE BETWEEN 10 AND 40 GROUP BY M.USERNAME;

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

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