SQL활용 #3 - 조회 / 추가 / 삭제 / 수정

1. 조회

• 전체조회 : SELECT * FORM 테이블명;
• 조건에 해당하는것만 조회 : SELECT * FORM 테이블명 WHERE 조건;
• 정렬조회 : SELECT * FORM 테이블명 WHERE 조건 ORDER BY 컬럼명 정렬기준;
  : 필터해서 가져온 다음 정렬 O
  : 가져와서 정렬한 다음 필터 X => 불필요한 정보도 가져왔기 때문
  : DB에서 꺼낼때 필터를 최대한 거쳐서 필요한 정보만 꺼내야한다. => 성능이슈

SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;
-- 조회
SELECT * FROM MEMBER3 WHERE ID IN('AR','BR','CR');

-- 정렬
SELECT * FROM MEMBER3 WHERE ID IN('AR','BR','CR') ORDER BY ID DESC;

-- 게시판 조회수가 20이상인 것을 글번호 기준 내림차순 조회
SELECT * FROM BOARD3 WHERE HIT >= 20 ORDER BY NO DESC;

2. 추가

• 단일항목추가 : INSERT INTO 테이블명(컬럼명) VALUES(추가값);
• 항목일괄추가 : INSERT ALL INTO 테이블명(컬럼명) VALUES(추가값), INTO 테이블명(컬럼명) VALUES(추가값) SELECT * FROM DUAL;

-- 추가
-- INSERT INTO 테이블명(컬럼명) VALUES(추가값);
INSERT INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1) 
    VALUES('AR','AR',36,'010-0000-1111','M',CURRENT_DATE, CURRENT_DATE);

-- 2명의 회원 추가

COMMIT; -- 적용하기 (추가, 삭제, 수정) 변화된 데이터를 적용하는것
ROLLBACK; -- 되돌리기
SAVEPOINT; -- 저장점을 정의

-- BOARD3에 내용 추가
-- 시퀀스 생성하기 (시퀀스명 : SEQ_BOARD3_NO)
CREATE SEQUENCE SEQ_BOARD3_NO
    START WITH 1 INCREMENT BY 1 NOMAXVALUE;
-- 글쓰기 (작성자는 외래키)
-- 제목 내용 작성자 조회수
-- 시퀀스는 자동으로 커밋이 되기때문에 증가한 시퀀스값은 롤백 될 수 없다.
INSERT INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
    VALUES(SEQ_BOARD3_NO.NEXTVAL, '제목', '내용', 'AR', 1, CURRENT_DATE);
    
COMMIT; -- 적용하기 (추가, 삭제, 수정) 변화된 데이터를 적용하는것
ROLLBACK; -- 되돌리기 COMMIT 하기전에 되돌리기 
SAVEPOINT; -- 저장점을 정의

-- 조회
SELECT * FROM BOARD3;

-- 시퀀스 조회
-- DUAL 시스템 관리를 위한 테이블
SELECT SEQ_BOARD3_NO.CURRVAL FROM DUAL;


-- 일괄 추가
-- 일괄 추가를 다 하거나 아예 하지 않거나
INSERT ALL
    INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1) 
        VALUES('BR','AR',36,'010-0000-1111','M',CURRENT_DATE, CURRENT_DATE)
    INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1) 
        VALUES('CR','AR',36,'010-0000-1111','M',CURRENT_DATE, CURRENT_DATE)
    INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1) 
        VALUES('DR','AR',36,'010-0000-1111','M',CURRENT_DATE, CURRENT_DATE)
SELECT * FROM DUAL;  

SELECT * FROM MEMBER3;

COMMIT; -- 적용하기 (추가, 삭제, 수정) 변화된 데이터를 적용하는것
ROLLBACK; -- 되돌리기 COMMIT 하기전에 되돌리기

-- 한번씩 여러번 추가
-- 하나넣고 커밋, 하나넣고 커밋, 만약 두개넣고 서버가 멈추거나 하면 
-- 롤백을 해야하는 등의 일이 벌어지기 때문에 
-- 일괄추가 할깨는 INSERT ALL을 사용하는 것이 더 낫다
INSERT INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1) 
    VALUES('BR','AR',36,'010-0000-1111','M',CURRENT_DATE, CURRENT_DATE);
COMMIT; -- 적용하기 (추가, 삭제, 수정) 변화된 데이터를 적용하는것    

INSERT INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1) 
    VALUES('CR','AR',36,'010-0000-1111','M',CURRENT_DATE, CURRENT_DATE);
COMMIT; -- 적용하기 (추가, 삭제, 수정) 변화된 데이터를 적용하는것    

INSERT INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1) 
    VALUES('DR','AR',36,'010-0000-1111','M',CURRENT_DATE, CURRENT_DATE);
COMMIT; -- 적용하기 (추가, 삭제, 수정) 변화된 데이터를 적용하는것    

COMMIT; -- 적용하기 (추가, 삭제, 수정) 변화된 데이터를 적용하는것
ROLLBACK; -- 되돌리기 COMMIT 하기전에 되돌리기

3. 수정

• 단일항목수정 : UPDATE 테이블명 SET 컬럼명=변경값, 컬럼명=변경값 WHERE 조건;

-- 수정
-- UPDATE 테이블명 SET 컬럼명=변경값, 컬럼명=변경값 WHERE 조건;

-- 1. 조회
SELECT * FROM MEMBER3;

-- 2. 수정
-- 조건을 정하지 않으면 모든 항목의 데이터가 변경됨
UPDATE MEMBER3 SET AGE=22, PHONE='010-5555-6666' WHERE ID='A';

-- 2-1. 나이가 0~20까지는 200, 21~40이면 300, 아니면 400으로
UPDATE MEMBER3 SET AGE =
    -- IF문 같은 개념
    CASE
        WHEN (AGE >= 0 AND AGE <= 20) THEN 200
        WHEN (AGE >= 21 AND AGE <= 40) THEN 300
        ELSE 400
    END;    

-- 3. 조회
SELECT * FROM MEMBER3;

-- 4. 적용하기/되돌리기
COMMIT;
ROLLBACK;


---------- 해보세요

-- 1. 게시판에서 글번호가 1번인것의 제목을 'BBB' 조회수를 10
-- 2. 게시판에서 조회수가
-- 조회수가 0~9 => 10
-- 조회수가 10~20 => 20
-- 조회수가 30~40 => 30
-- 나머지 40

-- 1. 게시판에서 글번호가 1번인것의 제목을 'BBB' 조회수를 10
SELECT * FROM BOARD3;

UPDATE BOARD3 SET TITLE='BBB', HIT=10 WHERE NO=1;

-- 1-1. 글번호가 1, 13, 15번인 글의 제목 바꾸기
UPDATE BOARD3 SET TITLE='CCC', HIT=10 WHERE NO=1 OR NO=13 OR NO=15;
UPDATE BOARD3 SET TITLE='DDD', HIT=10 WHERE NO IN (1,13,15);

COMMIT;
ROLLBACK;

SELECT * FROM BOARD3;

-- 2. 게시판에서 조회수가~
SELECT * FROM BOARD3;

UPDATE BOARD3 SET HIT =
    CASE
        WHEN(HIT >= 0 AND HIT <=9) THEN 10
        WHEN(HIT >= 10 AND HIT <=20) THEN 20
        WHEN(HIT >= 30 AND HIT <=40) THEN 30
        ELSE 40
    END;    

SELECT * FROM BOARD3;

COMMIT;
ROLLBACK;

4. 삭제

• 단일항목삭제 : DELETE FROM 테이블명 WHERE 조건;

-- 삭제
-- DELETE FROM 테이블명 WHERE 조건;
SELECT * FROM BOARD3;

-- BOARD3 다 삭제
DELETE FROM BOARD3;

-- NO가 8번인 글을 삭제
DELETE FROM BOARD3 WHERE NO=8;

SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;

-- 회원A가 탈퇴
-- 탈퇴 안됨 외래키가 걸린 BOARD3의 글이 있기때문
-- 그래서 탈퇴가 불가능한 경우가 많다.
-- 이럴경우 ID만 남겨두고 중요정보를 빈칸으로 업데이트 시킨다.
DELETE FROM MEMBER3 WHERE ID='DR';

COMMIT;
ROLLBACK;

5. 실습

1. 시퀀스생성 SEQ_ITEM1_NO
   : 시작숫자 1001, 증가값 1, NOMAXVALUE
2. ITEM1 테이블에 시퀀스이용해서 5개 추가
3. ITEM1에 재고 수량이 100 미만은 100으로 변경
4. MEMBER1에 회원 3명 추가
5. 시퀀스생성 SEQ_ORDER1_ORDNO
   : 시작숫자 10001, 증가값 1, NOMAXVALUE
6. 주문 3건 수행하기(ITEM1, MEMBER1 외래키)

-- 0. 조회
SELECT * FROM ITEM1;
SELECT * FROM MEMBER1;
SELECT * FROM ORDER1;
COMMIT;
ROLLBACK;

-- 1. 시퀀스생성 SEQ_ITEM1_NO
-- 시작숫자 1001, 증가값 1, NOMAXVALUE
CREATE SEQUENCE SEQ_ITEM1_NO
    START WITH 1001 INCREMENT BY 1 NOMAXVALUE;

-- 2. ITEM1 테이블에 시퀀스이용해서 5개 추가
INSERT 
    INTO ITEM1(ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE)
        VALUES(SEQ_ITEM1_NO.NEXTVAL, '커피', 2000, 222, CURRENT_DATE);
        
-- 2**. ITEM1 테이블에 일괄추가
--INSERT ALL
--    INTO ITEM1(ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE)
--        VALUES(SEQ_ITEM1_NO.NEXTVAL, '콜라', 2000, 222, CURRENT_DATE)
--    INTO ITEM1(ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE)
--        VALUES(SEQ_ITEM1_NO.NEXTVAL + 1, '사이다', 2000, 222, CURRENT_DATE)
--SELECT * FROM DUAL;   

-- 3. ITEM1에 재고 수량이 100 미만은 100으로 변경
UPDATE ITEM1 SET ITEMQTY = 100 WHERE ITEMQTY < 100;
  
-- 4. MEMBER1에 회원 3명 추가
INSERT INTO MEMBER1(USERID, USERPW, USERNAME, USERAGE, USERDATE, USERADDR)
    VALUES('BB', 'BB', 'BB', 35, CURRENT_DATE, '제주');

-- 5. 시퀀스생성 SEQ_ORDER1_ORDNO
-- 시작숫자 10001, 증가값 1, NOMAXVALUE 
CREATE SEQUENCE SEQ_ORDER1_ORDNO
    START WITH 10001 INCREMENT BY 1 NOMAXVALUE;
 
-- 6. 주문 3건 수행하기(ITEM1, MEMBER1 외래키)
INSERT INTO ORDER1(ORDNO, ORDCNT, ORDDATE, ORDITEM, ORDID)
    VALUES(SEQ_ORDER1_ORDNO.NEXTVAL, 2, CURRENT_DATE, 1009, 'BB');

SELECT * FROM ITEM1;
SELECT * FROM MEMBER1;
SELECT * FROM ORDER1;
COMMIT;
ROLLBACK;