SQL - 질문답변 운영쿼리, 메시지, 좋아요

Luna·2022년 12월 23일
countgroupbysql
0

EZEN

목록 보기
10/40

질문답변 운영 쿼리

관련글 카운트 세기

SELECT refNo, COUNT(*)
FROM qna
-- ~에 의해서 그룹을 지게 해서 쓰게 하자
GROUP BY refNo;

  • refNO가 1번인 글의 관련 글은 4개, refNo가 5번인 글의 관련글은 2개

오라클 함수

형변환 함수

  • 날짜 타입 -> 문자열 타입 또는 숫자 타입 -> 문자열 타입
    to_char(원본, 형식) - 문자열로 변경하는 함수
    to_char(writeDate, 'yyyy.mm.dd') : 날짜를 문자열로 바꿔준다.
  • 날짜 입력 - 문자 형식으로 입력한다. '2021-06-22'
    -> 입력해야하는 데이터의 형태가 날짜형인 경우 날짜타입으로 자동 형변환이 된다.
  • 날짜형식에 맞는 문자열 타입 -> 날짜 타입
    to_date(문자열[, 형식]) - 날짜 데이터로 변경.
    to_timestamp(데이터[, 형식]) - 날짜 데이터로 변경
  • 문자열 타입 -> 숫자 타입
    to_number(문자열[, 형식]) - 문자열 -> 숫자

수학적 함수

  • no = 글 번호
    max(no) - 글 번호 중에서 제일 큰 수 -> SEQUENCE를 사용하지 않는 경우 max(no) + 1
    min(no) - 글 번호 중에서 제일 작은 수
    count(*) - 데이터의 갯수
    sum(price) - 데이터들의 합계
    avg(price) - 데이터들의 평균
  • group by 항목이름(부서) - 수학적 함수 + group by => 통계처리
  • nvl(no, 0) - no의 데이터가 null이면 0으로 대체해 주는 함수.

함수 실습

-- TEST TABLE
-- 상품 판매 테이블 : 그룹함수 처리 예를 위한 샘플 테이블
-- 1. 제거
DROP TABLE goods_order CASCADE CONSTRAINTS;
DROP SEQUENCE goods_order_seq;

-- 2. 생성
CREATE TABLE goods_order(
    no NUMBER PRIMARY KEY,
    code CHAR(4) NOT NULL,
    goods_name VARCHAR2(100) NOT NULL,
    -- 가격 테이블은 보통 order table에 넣지 않고 따로 만든다.
    price NUMBER NOT NULL, -- 단가
    quantity NUMBER NOT NULL -- 수량
);
-- order 테이블은 원래 상품이 세세하게 나오지 않고 '1개 외 3건' 이런식으로 간략하게 나온다.
-- 주문자 아이디, 배송 정보, 결제정보 등이 나옴.
-- 상세한 것은 테이블을 따로 만듬. ex) goods_order_detail
-- goods 테이블을 따로 만들어서 order와 detail등 Join을 해야 한다.
-- 지금은 테스트중이니까 그냥 하는걸로~

CREATE SEQUENCE goods_order_seq;

-- 3. 샘플데이터 넣기
INSERT INTO goods_order(no, code, goods_name, price, quantity)
VALUES (goods_order_seq.nextval, '0001', '케이크', 30000, 1);
INSERT INTO goods_order(no, code, goods_name, price, quantity)
VALUES (goods_order_seq.nextval, '0001', '케이크', 30000, 2);
INSERT INTO goods_order(no, code, goods_name, price, quantity)
VALUES (goods_order_seq.nextval, '0002', '책상', 200000, 2);
INSERT INTO goods_order(no, code, goods_name, price, quantity)
VALUES (goods_order_seq.nextval, '0002', '책상', 200000, 1);
INSERT INTO goods_order(no, code, goods_name, price, quantity)
VALUES (goods_order_seq.nextval, '0003', '의자', 300000, 1);
INSERT INTO goods_order(no, code, goods_name, price, quantity)
VALUES (goods_order_seq.nextval, '0003', '의자', 300000, 2);
INSERT INTO goods_order(no, code, goods_name, price, quantity)
VALUES (goods_order_seq.nextval, '0003', '의자', 300000, 3);
COMMIT;

-- 3. 확인
SELECT * FROM goods_order;

-- 4. SUM
-- 총 수익을 알아내기 (가격 * 수량)의 합계
SELECT SUM (price * quantity) total_price FROM goods_order;
-- 총 판매된 물건의 갯수
SELECT SUM (quantity) total_quantity FROM goods_order;
-- 수익과 판매 같이 나오게 하기
SELECT SUM (price * quantity) total_price, SUM (quantity) total_quantity FROM goods_order;
-- 상품별 합계 -> SUM() GROUP BY 
SELECT code, goods_name, SUM(price*quantity) total_price, sum(quantity) total_quantity FROM goods_order
GROUP BY code, goods_name
ORDER BY code;

-- 5. AVERAGE

메시지 테이블

메시지 운영 쿼리

-- 메시지 운영쿼리
-- 1. 리스트
-- 모든메시지 / 읽지 않은 메시지 (where acceptDate is null) / 읽은 메시지 (where acceptDate is not null)
-- 받은 메시지 보기 - 받은 사람이 '나'인 것 (WHERE accepter = '내아이디')
-- 보낸 메시지 보기 - 보낸 사람이 '나'인 것 (WHERE sender = '내아이디')
-- 나의 받은, 보낸 메시지 보기 - WHERE accepter = '내아이디' OR sender = '내아이디'

-- 전체 메시지 보기
SELECT * FROM message;
-- 읽지 않은 전체 메시지 보기
SELECT * FROM message WHERE acceptDate IS NULL;
-- 읽은 전체 메시지 보기
SELECT * FROM message WHERE acceptDate IS NOT NULL;
-- test의 받은 메시지, 보낸 메시지 보기.
SELECT no, sender, sendDate, accepter, acceptDate
FROM message
WHERE accepter = 'test' OR sender = 'test';

-- 2. 보기 : acceptDate가 sysdate로 세팅(메시지 번호가 같다. + accepter가 본인 + acceptDate가 null인 경우) -> 메시지 보기
-- 메시지를 한 번 읽으면 그 날짜에만 읽었다고 표시 되어야 하고, 만약에 내일 한 번 더 읽어도 읽은 날짜만 세팅 되게 해야 한다.
-- 그러려면 acceptDate가 null값일 때만(읽지 않은 메시지 일때만) sysdate로 세팅 해 줘야 한다.
-- test로 로그인을 했다. accepter가 test인 데이터(no=3)을 읽으려고 한다.
UPDATE message SET acceptDate = sysdate
WHERE no = 3 AND accepter = 'test' AND acceptDate IS NULL;
-- 조건에 no = 3을 먼저 쓴 이유는 빨리 검색 하기 위해 쓴 것이다. (데이터튜닝)
-- accepter = 'test'를 먼저 쓰면 수많은 test중에 3번을 찾아야 하기 때문에 속도가 느려진다.
-- no = 3을 먼저 찾으면 비교하는 횟수가 줄어들어서 속도가 빨라진다
COMMIT;

SELECT no, content, sender, sendDate, accepter, acceptDate
FROM message
WHERE no = 3;

-- 3. 보내기
INSERT INTO message(no, content, sender, accepter)
VALUES (message_seq.nextval, '그래잘부탁해', 'test', 'admin');
COMMIT;

-- 4. 수정
-- 상대편이 읽기 전까지 보낸 메시지에 한해서 수정할 수 있다.
-- 수정 대신에 삭제하는 것도 괜찮다. (수신확인 안한 메시지 발송 취소 하기)
UPDATE message SET content='반가워요'
WHERE no = 2 AND sender = 'test' AND acceptDate IS NULL;
COMMIT;

-- 5. 삭제
-- 받은 메시지 (읽든 안읽든 삭제 할 수 있다.)
DELETE FROM message
WHERE no = 2 AND sender = 'test';
ROLLBACK;
COMMIT;

-- 보낸 메시지 (상대방이 읽지 않은 메시지어야 한다.)
DELETE FROM message
WHERE no = 2 AND acceptDate IS NULL;
ROLLBACK;
COMMIT;

-- 두가지 조건 한번에 쓰기
-- (sender='test' and acceptDate IS NULL) : test가 보낸 메시지 중에 상대방이 안 읽은 메시지
-- (accepter = 'test') : test가 받은 메시지
DELETE FROM message
WHERE no = 2 AND ((sender='test' and acceptDate IS NULL) or (accepter = 'test'));
ROLLBACK;
COMMIT;

좋아요

요구사항

  • 회원이 게시글에 대해서 좋아요를 할 수 있다.
  • LIST 좋아요 누른 목록, VIEW 좋아요 누른 글, WRITE 좋아요, DELETE 좋아요 취소
    싫어요 기능 추가 할 경우 : UPDATE 좋아요 누른걸 취소하고 싫어요 누르기
  • 한 명의 회원이 게시글 한 개당 한 번만 좋아요를 할 수 있다.
  • 좋아요를 구현하려면 별도의 테이블을 구현한다.

    여러 사람이 여러개의 게시글에 대해서 좋아요를 누르는 것은 불특정 다수(정해지지 않은 데이터를 저장 할 수 있어야 한다)로 이루어지므로 컬럼을 계속 무한대로 만들어야 하므로 테이블을 만들어서 JOIN 해야 한다.

제약조건

  • 한 명의 회원은 게시글 한 개에 대해 한번 좋아 할 수 있다.
  • 필요한 데이터 : 아이디, 좋아요 한 게시글
    =중복이 되면 안된다. UNIQUE + NOT NULL = PRIMARY KEY

좋아요 스키마

-- 게시판 좋아요 스키마

-- 1. 제거
DROP TABLE board_like CASCADE CONSTRAINTS;

-- 2. 생성
-- 두개 이상의 컬럼에 PK 제약조건 추가하는 방법
-- 방법[1]
-- CREATE TABLE board_like (
    -- 회원 한 사람이 게시판 글 하나에 한 번만 좋아요를 누를 수 있다.
--     id VARCHAR(20) NOT NULL REFERENCES member(id),
--     no NUMBER NOT NULL REFERENCES board(no),
    -- PK가 2개 이상으로 이루어져 있으면 컬럼 오른쪽에 선언할 수 없다.
    -- PRIMARY KEY (컬럼명1, 컬럼명2..)
--     PRIMARY KEY (id, no)
-- );

-- 방법[2]
-- 제약조건에 이름을 붙이고 싶으면 CONSTRAINT 키워드를 사용하기.
CREATE TABLE board_like (
    -- 회원 한 사람이 게시판 글 하나에 한 번만 좋아요를 누를 수 있다.
    -- 제약조건에 이름을 붙이고 싶으면 CONSTRAINT 키워드를 사용하기.
    -- 제약조건에 SYS_000000 으로 나왔던걸 내가 설정한 이름으로 바꾸는 것.
    id VARCHAR(20) CONSTRAINT board_like_id_nn NOT NULL,
    no NUMBER CONSTRAINT board_like_no_nn NOT NULL,
    -- PK가 2개 이상으로 이루어져 있으면 컬럼 오른쪽에 선언할 수 없다.
    CONSTRAINT board_like_id_no_pk PRIMARY KEY (id, no),
    -- ON DELETE CASCADE : 원래 있던 글이 삭제되면 좋아요도 삭제되어야 한다.
    CONSTRAINT board_like_id_fk FOREIGN KEY (id) REFERENCES member(id) ON DELETE CASCADE,
    CONSTRAINT board_like_no_fk FOREIGN KEY (no) REFERENCES board(no)ON DELETE CASCADE
);

SELECT * FROM board;
SELECT * FROM board_like;

-- 샘플 데이터 넣기
-- test라는 사람이 2번글에 좋아요를 눌렀다.
INSERT INTO board_like (id, no)
VALUES ('test', 2);
COMMIT;

좋아요 운영쿼리

-- 게시판 좋아요 운영 쿼리
-- 1. 리스트 - 내가 좋아요를 누른 게시글의 리스트 - board, board_like -> join 해서 가져온다.
SELECT bl.no, b.title, b.writer, b.writeDate, b.hit
FROM board b, board_like bl
-- test가 좋아요를 누른 게시글만 가져온다.
WHERE (bl.id = 'test') AND (b.no = bl. no); -- INNER JOIN

SELECT * FROM board;
SELECT * FROM board_like;

-- 2. 보기 - 게시판의 글을 볼 때 내가 좋아요 한 게시판인지 확인
-- 내가 좋아요 했는지 안했는지에 대한 데이터도 같이 가져와야 함
SELECT no, title, content, writer, writeDate, hit,
 (
    SELECT no FROM board_like
    WHERE id = 'test' AND no = 2
 ) likeIt
FROM board
WHERE no = 2;

-- 3. 등록 - 좋아요 누르기. id, no를 넣어야 한다. 중복 불가(한번만)
INSERT INTO board_like (id, no)
VALUES('test', '3');
COMMIT;

-- 4. 수정 - 구현하지 않는다.
-- 만약에 싫어요를 같이 구현 할 때는 좋아요를 싫어요로 수정 할 때 필요하다.
-- 지금은 좋아요만 할거라서 구현 안 할 거 야 ~

-- 5. 삭제 - 좋아요 취소
DELETE FROM board_like
WHERE no = 2 AND id = 'test';
COMMIT;
profile
Luna
이전 포스트

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

다음 포스트

JDBC - test 코드 작성하기 (list, view, write, update, increase, delete)

0개의 댓글