SQL - 질문답변 테이블, 스키마, 운영쿼리 / 메시지 스키마

질문 답변 테이블

• 질문글 밑에 답글 단 답변글이 관련글이라는 걸 알 수 있는 방법은 컬럼을 하나 새로 만들고 데이터를 넣으면 된다.
• 순서번호 : 질문글이 1번이면 그 밑에 최초로 답변을 단 글은 2번이다. (그 이후 1씩 증가)
• id가 REFERENCES 되어 있으면 member에 있는 아이디어야 한다. (로그인된 회원이어야 함)
• 들여쓰기 : 컬럼을 새로 만든다
  -> 맨 처음 질문글 (들여쓰기 없음 0) -> 그 글의 답변글들 (1) -> 답변글의 답변글 (2)
• 순서번호(ordNo)와 들여쓰기번호(levNo)는 연산이 필요하기 때문에 JAVA에서 처리한다.

질문 답변 스키마

-- 질문답변 스키마
-- 1. 제거
DROP TABLE qna CASCADE CONSTRAINTS;
DROP SEQUENCE qna_seq;

-- 2. 생성
CREATE TABLE qna (
    no NUMBER PRIMARY KEY,
    title VARCHAR2(300) NOT NULL,
    content VARCHAR2(2000) NOT NULL,
    id VARCHAR2(20) NOT NULL REFERENCES member(id), -- id가 있는 사람만(회원인 사람만) 글을 쓸 수 있다.
    -- 로그인이 되어 있기 때문에 아이디는 실제적으로 입력하지 않는다.
    -- 실제 입력 받는 것은 제목과 내용이다.
    writeDate DATE DEFAULT sysdate,
    hit NUMBER DEFAULT 0, -- 0으로 넣지 않으면 NULL로 나오기 때문에 연산이 안된다.
    -- 질문글 밑으로 모으기 위해 만든 컬럼. 질문번호가 있어야 refNo를 만들 수 있다.
    -- 질문글이 1번이고, refNo는 자기 자신인 1번, 그에대한 답변글의 refNo도 1번이다.
    refNo NUMBER REFERENCES qna(no),
    -- 순서번호 ordNo = 보는 글 +1 : 자바 프로그램에서 처리
    -- 질문글이 1번이고 그에대한 답변글의 ordNo는 2번, 그 밑의 글은 3번이다.
    ordNo NUMBER,
    -- 들여쓰기 lev No = 답변글 +1 : 자바 프로그램에서 처리
    -- 질문은 0번 들여쓰기, 질문의 답변들은 1번 들여쓰기, 답변의 답변글들은 2번 들여쓰기.
    levNo NUMBER,
    -- on DELETE CASCADE : 자동삭제. 질문글이 지워지면 그 질문글의 답글까지 지운다.
    -- on SET NULL : 부모글이 삭제되면 답변글의 번호가 오류가 나므로 답변글의 번호를 NULL 값으로 둔다.
    parentNo NUMBER REFERENCES qna(no) on DELETE CASCADE
);

CREATE SEQUENCE qna_seq;

-- 3. 샘플데이터
-- 3-1. 질문글
-- title, content는 사용자가 입력, id는 로그인 정보를 가져온다. no는 SEQUENCE 자동생성
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '자바란?', '자바가 궁금합니다', 'test', qna_seq.nextval, 1, 0, NULL);
COMMIT;

-- 3-2. 답변글1 - 질문글 1번에 대한 답변 (1번글이 있어야만 한다. 없으면 답글을 달 수 없다.)
-- refNo = p.refNo, ordNo = p.ordNo +1, lev No = p.levNo+1, parentNo = p.no
-- 1번글을 보고 답변을 쓰면 ordNo가 2가 된다. 그러면 refNo가 같고 ordNo 이상인 ordNo를 +1 증가 시켜줘야 한다.
SELECT * FROM qna WHERE no = 1; -- 1번글이 있어야 하고 내가 그 글을 보고 있다.
-- 새로운 답변의 구해진 순서번호(현재는 2)보다 큰 순서번호는 +1 한다.
UPDATE qna set ordNo = ordNo + 1 WHERE refNo = 1 AND ordNo >= 2;
-- refNo : 1, orNo : 1 + 1, levNo : 0 + 1, parenNo : 1
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '[답변]자바란?', '컴퓨터언어입니다', 'admin', 1, 2, 1, 1);
COMMIT;

-- 3-2. 답변글2 - 질문글 1번에 대한 답변 (1번글이 있어야만 한다. 없으면 답글을 달 수 없다.)
-- refNo = p.refNo, ordNo = p.ordNo +1, lev No = p.levNo+1, parenNo = p.no
-- 1번글을 보고 답변을 쓰면 ordNo가 2가 된다. 그러면 refNo가 같고 ordNo 이상인 ordNo를 +1 증가 시켜줘야 한다.
SELECT * FROM qna WHERE no = 1; -- 1번글이 있어야 하고 내가 그 글을 보고 있다.
-- 새로운 답변의 구해진 순서번호(현재는 2)보다 큰 순서번호는 +1 한다.
UPDATE qna set ordNo = ordNo + 1 WHERE refNo = 1 AND ordNo >= 2;
-- refNo : 1, orNo : 1 + 1, levNo : 0 + 1, parenNo : 1
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '[답변]자바란?2', '컴퓨터언어입니다2', 'admin', 1, 2, 1, 1);
COMMIT;

-- 3-3. 답변의 답변 - 3번 답변에 답변
-- refNo = p.refNo, ordNo = p.ordNo +1, lev No = p.levNo+1, parenNo = p.no
-- 3번글을 보고 답변을 쓰면 ordNo가 3이 된다. 그러면 refNo가 같고 ordNo 이상인 ordNo를 +1 증가 시켜줘야 한다.
SELECT * FROM qna WHERE no = 3; -- 3번글이 있어야 하고 내가 그 글을 보고 있다.
-- 새로운 답변의 구해진 순서번호(현재는 3)보다 이상 순서번호는 +1 한다.
UPDATE qna set ordNo = ordNo + 1 WHERE refNo = 1 AND ordNo >= 3;
-- refNo : 1, orNo : 2 + 1, levNo : 1 + 1, parenNo : 1
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '[답변][답변]자바란?2?', '컴퓨터언어입니다2?', 'admin', 1, 3, 2, 3);
COMMIT;

-- 4. 확인
SELECT * FROM qna;

질문 답변 운영 쿼리

-- 질문 답변 운영쿼리

-- 1. 리스트 - 질문글과 그 글 밑에 달린 답변글 모두 가져오기.
SELECT no, title, id, writeDate, hit, refNo, ordNo, levNo, parentNo
FROM qna
ORDER BY refNo DESC, ordNo ASC; -- ASC는 기본이라 생략 가능.
-- refNo를 먼저 정렬해서 부모글들을 역순으로 정렬 한 후에, 부모글 밑에 달린 답변글을 ordNo ASC를 통해 최신글이 위로 올라오게 정렬한다.

-- 1.1 리스트 - 질문글과 그 밑의 달린 답변글의 갯수만 가져오기.
-- 질문글 구별 방법 : no번호와 refNo가 같거나, ordNo가 1번이거나, levNo가 0번이거나, parentNo가 null인 것 중에
--                  선택해서 코드를 구현하면 된다.
SELECT no, title, id, writeDate, hit, refNo, ordNo, levNo, parentNo, (
-- 질문글에 달린 답변글의 갯수를 세는 쿼리
    SELECT COUNT(*) FROM qna cq
    -- no의 번호와 refNO의 번호가 같은 글의 갯수를 가져온다.
    WHERE q.no = cq.refNo) - 1 replyCnt
FROM qna q
-- WHERE no = refNo -- no번호와 refNo가 같거나
-- WHERE ordNo = 1 -- ordNo가 1번이거나
-- WHERE levNo = 0 -- levNo가 0번이거나
WHERE parentNo is NULL -- parentNo가 null
-- NULL 값인걸 물어보는것은 대입연산자(=)로 비교할 수 없다.
ORDER BY refNo DESC, ordNo ASC;

-- 2. 보기
-- 조회수 1 증가
UPDATE qna SET hit = hit + 1
WHERE no = 4;

SELECT no, title,content, id, writeDate, hit, refNo, ordNo, levNo, parentNo
FROM qna
WHERE no = 4;

-- 3. 등록
-- 3-1. 질문글
-- title, content는 사용자가 입력, id는 로그인 정보를 가져온다. no는 SEQUENCE 자동생성
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '오라클이란?', '오라클이 궁금합니다', 'test', qna_seq.nextval, 1, 0, NULL);
COMMIT;

-- 3-2. 답변글1 - 질문글 5번에 대한 답변 (5번글이 있어야만 한다. 없으면 답글을 달 수 없다.)
-- refNo = p.refNo, ordNo = p.ordNo +1, lev No = p.levNo+1, parentNo = p.no
-- 5번글을 보고 답변을 쓰면 ordNo가 2가 된다. 그러면 refNo가 같고 ordNo 이상인 ordNo를 +1 증가 시켜줘야 한다.
SELECT * FROM qna WHERE no = 5; -- 5번글이 있어야 하고 내가 그 글을 보고 있다.
-- 새로운 답변의 구해진 순서번호(현재는 2)보다 큰 순서번호는 +1 한다.
UPDATE qna set ordNo = ordNo + 1 WHERE refNo = 5 AND ordNo >= 2;
-- refNo : 5, orNo : 1 + 1, levNo : 0 + 1, parenNo : 5
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '[답변]오라클이란?', '데이터베이스', 'admin', 5, 2, 1, 5);
COMMIT;

-- 3-2. 답변글2 - 질문글 5번에 대한 답변 (1번글이 있어야만 한다. 없으면 답글을 달 수 없다.)
-- refNo = p.refNo, ordNo = p.ordNo +1, lev No = p.levNo+1, parenNo = p.no
-- 5번글을 보고 답변을 쓰면 ordNo가 2가 된다. 그러면 refNo가 같고 ordNo 이상인 ordNo를 +1 증가 시켜줘야 한다.
SELECT * FROM qna WHERE no = 5; -- 1번글이 있어야 하고 내가 그 글을 보고 있다.
-- 새로운 답변의 구해진 순서번호(현재는 2)보다 큰 순서번호는 +1 한다.
--      -> ordNo 2였던걸 3으로 변경하고 밑에 INSERT할 글을 2번으로 올린다. -> 최신글을 위로 보이게 하는 쿼리
UPDATE qna set ordNo = ordNo + 1 WHERE refNo = 1 AND ordNo >= 2;
-- refNo : 5, orNo : 1 + 1, levNo : 0 + 1, parenNo : 1
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '[답변]오라클이란?2', '데이터베이스2', 'admin', 5, 2, 1, 5);
COMMIT;

-- 3-3. 답변의 답변 - 7번 답변에 답변
-- refNo = p.refNo, ordNo = p.ordNo +1, lev No = p.levNo+1, parenNo = p.no
-- 7번글을 보고 답변을 쓰면 ordNo가 3이 된다. 그러면 refNo가 같고 ordNo 이상인 ordNo를 +1 증가 시켜줘야 한다.
SELECT * FROM qna WHERE no = 7; -- 3번글이 있어야 하고 내가 그 글을 보고 있다.
-- 새로운 답변의 구해진 순서번호(현재는 3)보다 이상 순서번호는 +1 한다.
UPDATE qna set ordNo = ordNo + 1 WHERE refNo = 1 AND ordNo >= 3;
-- refNo : 5, orNo : 2 + 1, levNo : 1 + 1, parenNo : 1
INSERT INTO qna (no, title, content, id, refNo, ordNo, levNo, parentNo)
VALUES (qna_seq.nextval, '[답변][답변]오라클이란?2?', '오라클이궁금합니다2', 'test', 5, 3, 2, 7);
COMMIT;

-- 4. 수정
UPDATE qna SET title='[답변][답변]자바3333', content='객체지향언어'
WHERE no = 4 and id = 'test'; 
-- id가 test인 사람만 4번글을 고칠 수 있다. (4번 글을 쓴 사람은 test이다.)
--          ->본인 글만 수정 할 수 있다.
COMMIT;

-- 5. 삭제
-- 글을 삭제 하면 밑에 달린 답변글도 삭제 된다.
-- REFERENCES qna(no) on DELETE CASCADE라고 선언이 되어 있어야 하위글도 삭제 된다.
DELETE FROM qna
WHERE no = 3 AND id = 'admin';
ROLLBACK;
COMMIT;

---

메시지 테이블

• 보낸 사람의 아이디와 받는 사람의 아이디는 member테이블에서 가져온다.
• 친구 등록 기능을 넣어서 받는 사람을 선택 할 시에 친구 목록을 가져오는 방법도 있다.
• 보낸 사람과 받는 사람이 같은 경우도 있다. -> 나에게 메세지 쓰기
• 받은 날짜는 DEFAULT 값은 입력하지 않는다.
  -> 메세지를 열어 봤을 때 UPDATE SET을 이용해서 데이터를 열어 본 날짜로 변경한다.

메시지 스키마

-- 메시지 스키마
-- 1. 제거
DROP TABLE message CASCADE CONSTRAINTS;
DROP SEQUENCE message_seq;

-- 2. 생성
CREATE TABLE message(
    no NUMBER PRIMARY KEY,
    content VARCHAR2(2000) NOT NULL,
    sender VARCHAR2(20) NOT NULL REFERENCES member(id),
    sendDate DATE DEFAULT sysdate NOT NULL,
    accepter VARCHAR2(20) NOT NULL REFERENCES member(id),
    accepDate DATE DEFAULT NULL
);

CREATE SEQUENCE message_seq;

-- 3. 샘플 데이터 넣기
-- test가 admin에게 : member에 있는 계정
INSERT INTO message(no, content, sender, accepter)
VALUES (message_seq.nextval, '안녕', 'test', 'admin');
INSERT INTO message(no, content, sender, accepter)
VALUES (message_seq.nextval, '반가워', 'test', 'admin');
-- admin이 test에게 : member에 있는 계정
INSERT INTO message(no, content, sender, accepter)
VALUES (message_seq.nextval, '잘부탁해', 'admin', 'test');
COMMIT;

-- 4. 확인
SELECT * FROM message;

메시지 운영쿼리