SQL - 페이징처리, 통계내기

페이징 처리

SELECT * FROM board;
-- 함수로 처리해서는 가져 갈 수 없다. 그래서 cnt를 붙여줘야 가져갈 수 있다.
SELECT COUNT(*) cnt FROM board; -- rs.getLong("cnt")
SELECT COUNT(*) FROM board; -- rs.getLong(1) : 컬럼의 순서번호를 쓰면 함수 그대로 가져온다.
SELECT no, title, writer FROM board; -- no : rs.getLong(1), title : rs.getString(2), writer : rs.getString(3)

-- 데이터를 불러와서 바로 넣기
-- 이미 갖고 있는 데이터의 값을 또 INSERT함 - (전체데이터 * 2) 씩 증가
INSERT INTO board(no, title, content, writer)
(SELECT board_seq.nextval, title, cotent, writer from board);
COMMIT;

-- 1. list
-- 1페이지. 1페이지당 10개의 데이터 표시. 1페이지 데이터 1~10, 2페이지 데이터 21~30 ... -> 계산은 java에서
-- 1-1. 데이터를 가져와서 최신글부터 보이게 정렬
SELECT no, title, writer, writeDate, hit FROM board ORDER BY no DESC;

-- 1-2. 1번에서 정렬한 데이터에 순서번호를 붙인다.
SELECT ROWNUM rnum, no, title, writer, writeDate, hit FROM (
    -- 1번에서 정렬한 데이터 가져오기
    SELECT no, title, writer, writeDate, hit FROM board ORDER BY no DESC
);

-- 1-3. 2번 데이터를 가지고 1페이지에 해당되는 데이터를 가져온다. rnum 1~10
SELECT rnum, no, title, writer, writeDate, hit FROM (
    -- 1번에 순서번호를 붙인 2번 데이터 가져오기
    SELECT ROWNUM rnum, no, title, writer, writeDate, hit FROM (
        -- 1번에서 정렬한 데이터 가져오기
        SELECT no, title, writer, writeDate, hit FROM board ORDER BY no DESC
    )
) WHERE rnum BETWEEN 1 AND 10;

-- 2. view
-- 3. write
-- 4. update
-- 5. delete

-- 비밀번호를 모두 1111로 맞추고 싶다.
UPDATE board SET pw = '1111';
COMMIT;

통계내기

-- 점수 처리를 위한 테이블 - 반, 학생번호, 점수
CREATE TABLE scores (
    no NUMBER PRIMARY KEY, -- 일련번호, PK를 다른 컬럼에 잡을 수 없을 때 하나 만들어 놓으면 좋다.
    class NUMBER(2) NOT NULL, -- 반
    sno NUMBER(2) NOT NULL, -- 학생번호, 학생 테이블의 학번 (Join해서 가져온다), REFERENCES
    score NUMBER(3) CHECK (score BETWEEN 0 AND 100) NOT NULL -- 점수
); -- DEFAULT는 제약조건(NOT NULL) 앞에 붙인다

CREATE SEQUENCE scores_seq;

-- test 데이터 넣기
-- 차례대로 모든 데이터를 다 넣을 때 컬럼명 생략 가능
INSERT INTO scores VALUES(scores_seq.nextval, 1, 1, 100);
INSERT INTO scores VALUES(scores_seq.nextval, 1, 2, 80);
INSERT INTO scores VALUES(scores_seq.nextval, 2, 3, 90);
INSERT INTO scores VALUES(scores_seq.nextval, 2, 4, 75);
INSERT INTO scores VALUES(scores_seq.nextval, 2, 5, 95);
INSERT INTO scores VALUES(scores_seq.nextval, 3, 6, 100);
INSERT INTO scores VALUES(scores_seq.nextval, 3, 7, 70);
COMMIT;

SELECT * FROM scores;

-- 전체 학년의 통계치
-- score의 합계, 평균(소수점 아래 두자리 까지만), 최소값, 최대값, 컬럼갯수
-- '999.00' : 9를 쓰면 비어서 나오고, 0이라고 쓰면 0을 넣는다.
-- 예를 들면 평균이 87.14일 때 '099.00' 이라고 쓰면 087.14가 나온다.
SELECT SUM(score) 합계, TO_CHAR(AVG(score), '999.00') 평균, MIN(score) 최소점, MAX(score) 최고점, COUNT(score) 응시인원
FROM scores;

-- scores 테이블의 반 별 통계치
SELECT class 반, SUM(score) 합계, TO_CHAR(AVG(score), '999.00') 평균, MIN(score) 최소점, MAX(score) 최고점, COUNT(score) 응시인원
FROM scores
GROUP BY class;

-- member 테이블의 성비율 통계
SELECT gender 성별, COUNT(*) 인원
FROM member
GROUP BY gender;

-- 나이대별 통계
-- 10대, 20대, 30대, 40대, 50대 60대, 70대, 80대, 90대
-- 나이 컬럼이 존재해야 한다.
-- 10대 찾는 계산 -> (나이 / 10) -> 소수점 절사 trunc() -> 곱하기 10
-- ex) 24/10= 2.4 -> 소수점 절사하면 2 -> 2 * 10 -> 20대

-- score 테이블의 점수대 별 통계
-- 100점대의 갯수, 90점대의 갯수, 80점대의 갯수, 70점대의 갯수
-- " " 안에 넣으면 대소문자 구분하고 빈칸도 넣을 수 있다.
SELECT (TRUNC (score / 10) *10) "Score Area", COUNT(*) count
FROM scores
GROUP BY (TRUNC (score / 10) *10)
ORDER BY "Score Area" DESC;