1월 13일 평가 예상 문제

Luna·2023년 1월 11일
0

EZEN

목록 보기
16/40

스터디 내용

  1. 게시판 페이징 처리 설명
  2. 공지 사항 페이지 처리 작성
  3. 검색 정리
  4. Group by 정리 - Group 함수 정리 - sum(), avg(), min(), max(), count()
  5. 그 외 함수 정리 - trunc(), to_date(), to_char(), to_number()
  6. join 정리
  7. indext 생성 실습, 장점과 만들면 안되는 경우

OracleServiceXE, OracleXETNSListener 실행시키기

꼭 암기 해야 할 게시판 운영쿼리

-- 게시판 운영 쿼리
-- 데이터 - 글번호, 제목, 내용, 작성자, 작성일, 조회수, 비밀번호
-- 1. 게시판 리스트
--   번호, 제목, 작성자, 작성일, 조회수
select no, title, writer, writeDate, hit -- 보여줄 항목 선택
from board -- 데이터가 저장되어 있는 곳
order by no desc; -- 정렬 방법(최신글을 맨위에)

-- 2. 게시판 글보기 + 조회수 증가
--  번호, 제목, 내용, 작성자, 작성일, 조회수 - 보고자 하는 데이터:글번호가 2번
select no, title, content, writer, writeDate, hit -- 보여줄 항목 선택
from board -- 데이터가 저장되어 있는 곳
where no = 2; -- 보고자 하는 데이터:글번호가 2번
--  조회수 1 증가 - > hit = hit + 1
update board set hit = hit + 1
where no = 2;
commit;

-- 3. 게시판 글쓰기 - 비밀번호 필요
insert into board(no, title, content, writer, pw)
values(board_seq.nextval, '게시판입니다.', '좋은 글을 공유합니다.', '이영환', '1111');
commit;

-- 4. 게시판 글수정 - 비밀번호 필요
--  글번호와 비밀번호가 맞아야 수정해 준다. 수정 항목 - 제목, 내용, 작성자
update board set title = '게시판입니다.', content = '게시판 완성', writer = '개발자'
where no = 2 and pw = '1111';
commit;

-- 5. 게시판 글삭제 - 비밀번호 필요
--  글번호가 2번인 글 지우자. 비밀번호가 맞아야 한다.
delete from board
where no = 2 and pw = '1111';
rollback; -- 실행된 내용 취소

계정 생성시 롤 SYSDBA
호스트 이름 localhost
포트 1521
SID xe
-> jdbc:oracle:thin:@localhost:1521:xe

계정 만들고 권한주기

-- 계정 만들기
CREATE USER java IDENTIFIED BY java;
-- 권한 주기 (연결, 자원관리, 뷰 테이블 만들기)
GRANT CONNECT, RESOURCE, CREATE VIEW TO java;

VARCHAR2 : 4000자 입력 가능한 문자열
NOT NULL 등 제약조건
NUMBER(3) : 3자리 입력

제약조건

  1. PK - primary key : 중복 안됨, null 안됨 -> 키로 지정

  2. FK - foriegn key : 다른테이블 컬럼에 데이터가 존재, null 가능
    ON DELETE CASCADE - 부모가 삭제되면 같이 삭제
    ON DELETE SET NULL - NOT NULL이 붙어 있으면 안됨. 부모가 삭제되면 NULL로 세팅한다.
    기본 - 데이터를 사용하는 경우 부모는 삭제가 안된다. (자식 데이터를 먼저 지우고 부모데이터를 지워야만 한다.)

  3. UK - unique : 중복 안됨. ex) 주민등록번호, 핸드폰번호 등

  4. CK - check (조건) ex) score number(3) check (score >= 0 and score <= 100)
    -정규표현식 잘 활용하기

SELECT * FROM COL WHERE TNAME = 'BOARD';
SELECT * FROM COL WHERE TABLE_NAME = 'BOARD';: BOARD 테이블의 스키마를 보여줌

SQL활용

SQL query - SELECT : WHERE and / or (=in)

gender가 남자 또는 여자이어야 한다.
-- or : WHERE GENDER ='여자' OR GENDER ='남자'
-- in : WHERE GENDER IN ('여자','남자')

score가 0부터 100사이의 데이터
-- and : WHERE SCORE >=0 AND SCORE <= 100
-- between : WHERE SCORE BETWEEN 0 AND 100

현재 공지 : TRUNC()를 사용해서 BETWEEN을 이용한 쿼리 작성
-- and : WHERE startDate <= SYSDATE AND TRUNC(SYSDATE) <= endDate
-- between : WHERE TRUNC(SYSDATE) BETWEEN startDate and endDate

score의 합계, 평균, 최소점, 최고점, 갯수를 구하는 SELECT 문 작성
-- 테이블 이름 : scores
-- 합계 : SELECT SUM(score) FROM SCORES
-- 평균 : SELECT AVG(score) FROM SCORES
-- 최고점 : SELECT MAX(score) FROM SCORES
-- 최소점 : SELECT MIN(score) FROM SCORES
-- 갯수 : SELECT COUNT(score) FROM SCORES
AVG와 COUNT는점수가 null인 경우 세지 않음

-- scores (class, score) class 별 합계와 평균을 구하고 class도 출력하는 SQL 문장 작성
: SELECT class, SUM(score), AVG(score) FROM scores GROUP BY class

서브쿼리

sub query를 이용한 페이지 처리
-- 1페이지 10개씩 : 1~10번, 2페이지 11~20번
1. 제일 큰 번호를 맨 위로 정렬시켜서 데이터를 가져온다.
2. 1번 데이터에 순서 번호를 붙인다. ROWNUM RNUM
3. 1페이지에 해당하는 데이터 10개 가져오기. RNUM이 1~10번까지 인 데이터.

-- 페이징 처리하기 (한페이지에 10개씩만 보이기)
-- board 데이터 늘리기 - 서브쿼리
INSERT INTO board (no, title, content, writer, pw)
(select board_seq.nextval, title, content, writer, pw from board);
COMMIT;

SELECT * FROM board;
SELECT COUNT(*) FROM board;

-- 1페이지에 해당되는 데이터 가져오기 : Oracle, mySQL, MS SQL이 페이징 처리하는 방법이 각각 다르다.
-- PK INDEX는 Oracle에서 자동으로 만든다. -> 속도가 빨라짐.
-- 추가, 수정, 삭제가 빈번하게 일어나는 데이터와 거의 대부분의 데이터로 검색하는 경우는 INDEX를 만들지 않는게 좋다.
-- : 공간만 많이 차지하고 효율성이 떨어진다.

-- 1. 데이터를 가져와서 최신글부터 보이게 정렬시킨다.
SELECT no, title, writer, writeDate, hit FROM BOARD ORDER BY no DESC;

-- 2. 1번의 데이터를 가지고 순서번호(rnum)를 붙인다. -서브쿼리
-- 예를 들면 가장 최근에 쓰인 160번의 글의 rnum은 1번이 된다.
SELECT ROWNUM rnum, no, title, writer, writeDate, hit
FROM (
    -- 1번 데이터
    SELECT no, title, writer, writeDate, hit FROM BOARD ORDER BY no DESC
);

-- 3. 2번데이터를 가지고 순서번호가 1~10까지 
-- BETWEEN을 사용해서 가져오기
-- 최종적으로 보이는 데이터기 때문에 rnum은 가져오지 않아도 된다.
SELECT rnum, no, title, writer, writeDate, hit
FROM (
    -- 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; -- 최근에 쓴 글 10개만 가져오기

검색 조건 title과 writer 중에서 java가 포함되어 있는 데이터 가져오기 -like

SELECT no, title, writer, writeDate, hit
FROM board
WHERE title like '%java%' OR writer like '%java%';

VIEW no = 2

SELECT no, title, content, writer, writeDate, hit FROM board
WHERER no = 2;

INSERT

INSERT INTO board (no, title, content, writer, pw) 
VALUES (board_seq.nextval, '제목', '내용', '작성자', 'password');

UPDATE

UPDATE board SET title = '제목수정', content = '내용수정', writer = '작성자수정'
WHERE no = 2 AND pw = 'password';

DELETE

DELETE FROM board
WHERE no = 2 AND pw = 'password';

INCREAS

UPDATE board SET hit = hit + 1
WHERE no = 2;

데이터가 입력 오류가 나는 경우 -> 이유 작성하시오.

JOIN

member table에서 아이디, 이름, 등급번호를 가져오고 grade table에서 등급이름을 가져오는 SQL 쿼리 작성

SELECT m.id, m.name, m.gradeNo, g.gradeName
FROM member m, grade g
WHERE g.gradeNo = m.gradeNo;

Outter Join : g.gradeNo(+)
데이터가 NULL인 경우에는 외부조인(Outter Join)을 해야한다. NULL로 조인해서 가져온다.
예를들면 좋아요 기능을 구현할 때 좋아요를 안한 상태에서도 (null) 리스트를 가져와야 한다.

4개의 테이블을 JOIN하는 경우

FROM t1, t2, t3, t4
WHERE t1.PK = t2.FK AND t2.PK = t3.FK AND t3.PK = t4.FK;

index 만들기와 검색

-- 인덱스 생성
-- 게시판으로 만들기
-- 글번호 검색을 많이 한다. -PK이므로 이미 이컬럼으로 만들어 져 있므로 오류가 난다.
CREATE INDEX idx_board_no
ON board(no);
-- 제목 검색
CREATE INDEX idx_board_title
ON board(title);
-- 작성자 검색
CREATE INDEX idx_board_writer
ON board(writer);
-- 제목, 작성자 동시에 검색
CREATE INDEX idx_board_title_writer
ON board(title, writer);
-- ** 검색을 많이하는 테이블인 경우 검색을 많이하는 컬럼을 속도 향상을 위해 인덱스를 만든다.

-- 인덱스 조회
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'BOARD';
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'BOARD';

-- 인덱스 삭제
DROP INDEX idx_board_title;
DROP INDEX idx_board_writer;
DROP INDEX idx_board_title_writer;

PK INDEX는 Oracle에서 자동으로 만든다. -> 속도가 빨라짐.
추가, 수정, 삭제가 빈번하게 일어나는 데이터와 거의 대부분의 데이터로 검색하는 경우는 INDEX를 만들지 않는게 좋다.
: 공간만 많이 차지하고 효율성이 떨어진다.

통계 내기

-- 점수 처리를 위한 테이블 - 반, 학생번호, 점수
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;
profile
Luna
이전 포스트

SQL - 작성, 활용, 응용

다음 포스트

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

0개의 댓글