12월 21일 수업내용

정렬

• 자바에서 정렬 할 필요는 없고 정렬은 DB처리에서 ORDER BY로 정렬하면 된다.

---

공지사항

• 번호, 제목, 시작일, 종료일, 최종수정일
• 모든공지, 현재공지, 지난공지, 예약공지
• 자바에서 구현 하려면 변수를 선언(all,present 등)해서 값을 넣어주면 된다.

공지사항 스키마

CREATE TABLE notice (
    no NUMBER PRIMARY KEY,
    title VARCHAR2(300) NOT NULL,
    content VARCHAR2(2000) NOT NULL,
    startDate DATE DEFAULT SYSDATE,
    endDate DATE DEFAULT '9999-12-30', 
    writeDate DATE DEFAULT SYSDATE,
    updateDate DATE DEFAULT SYSDATE
);

• endDate DATE DEFAULT '9999-12-30'
• 공지 쿼리를 짤 때 +1을 해야하는 경우가 생기기 때문에 31일에서 +1을 하면 오류가 나서 30일로 설정을 한다.
• trunc를 사용해서 시간 정보를 지우면 31일로 설정을 해도 된다.

모든공지

SELECT no, title, startDate, endDate, updateDate
FROM notice
WHERE 1=1 -- true 조건이 없다.(무조건) 없어도 되지만 다른 쿼리들과 형식을 맞추기 위해 쓰는게 좋다.
ORDER BY no DESC; -- ORDER BY는 WHERE절 뒤에 와야 한다.

현재공지

• endDate에 시간 정보가 없기 때문에 두가지 방법으로 조건을 걸어줘야 한다.

1. 첫번째 방법 : endDate에 +1해서 하루를 추가한다.

SELECT no, title, startDate, endDate, updateDate
FROM notice
WHERE startDate <= sysdate and sysdate < endDate + 1
ORDER BY no DESC;

2. 두번째 방법 : sysdate에 시간정보를 버린다.
   trunc : 시간 정보를 제거하거나, 숫자의 소수점 제거에 사용 된다.

SELECT no, title, startDate, endDate, updateDate
FROM notice
WHERE startDate <= sysdate and trunc (sysdate) <= endDate
ORDER BY no DESC;

지난공지

• 보통 관리자에게만 보일 수 있게 구현 하지만 당첨 여부를 확인할때는 일반 회원에게도 보이게 한다.

SELECT no, title, startDate, endDate, updateDate
FROM notice
WHERE endDate + 1 < sysdate
ORDER BY no DESC;

• 현재 공지와 마찬가지로 방법 두가지
  1.endDate+1 다음날짜 00:00:00초
  2.trunc(sysdate) 시간 정보를 잘라낸다.

예약공지

SELECT no, title, startDate, endDate, updateDate
FROM notice
WHERE startDate > sysdate
ORDER BY no DESC;

---

회원관리

• gradeNo에 sequence를 안 쓴 이유
  : 등급의 범위가 몇 개 없어서 한눈에 보이기 때문에 필요가 없다
• 생년월일과 나이 입력칸이 있으면 생년월일만 적어서 나이는 계산되게 프로그래밍한다.

---

ROWNUM, 서브쿼리

• 글 번호를 보여주는 것 보다 순서번호를 보여주는게 더 나을 때 쓰는 쿼리
• 그냥 사용자에게 깔끔하게 보이게 하고 싶기 위해서 씀
• ROWNUM은 강제적으로 만든 순서이기 때문에 검색을 하게 되면 없는 글 번호가 검색 될 수 있으니 Index용으로 no를 쓰려면 ROWNUM을 쓰지 않는게 좋다.

순서번호

-- ROWNUM : 순서번호 - 가져온 순서대로 보여준다.
SELECT ROWNUM, no, title, '20' subject
FROM (
    -- 서브쿼리
    SELECT no, title
    FROM board
    ORDER BY no DESC);

순서번호 역순

-- 현재 보여지는 순서번호는 1~4인데 역순으로(4~1) 보이게 하고 싶다.
-- board의 전체 데이터의 갯수 보기
SELECT COUNT(*) FROM board;
SELECT cnt - rownum+1 rnum, no, title, '20' subject
FROM (
    -- 서브쿼리
    SELECT (SELECT COUNT(*) from board) cnt, no, title
        FROM board
        ORDER BY no DESC);

1. SELECT 뒤에는 데이터 1개만 올 수 있어서 cnt라는 변수를 만들어서 COUNT 쿼리를 담는다.
2. 첫번째 행 cnt(3) - rownum(1) +1 = 3
3. 두번째 행 cnt(3) - rownum(2) +1 = 2
4. 세번째 행 cnt(3) - rownum(3) +1 = 1

---

날짜 가져오기

SELECT sysdate from dual;

• SELECT 형식을 맞추기 위해 없는 테이블(dual)을 가져와서 형식을 맞춰준다.

---

Index

-- index는 검색을 빠르게 하기 위해서 만든다. 
-- 저장공간이 따로 필요하고 보통 WHERE절에 걸어놓은 조건으로 인덱스를 만든다.
SELECT COUNT(no) from board;

• 보통 회원 정보를 물을 때 이름이나 핸드폰 번호 뒷자리를 물어본다. 이런걸로 Index를 만들면 된다.
• COUNT는 Null값은 세지 않는다.

---

MAX

• 가장 큰 번호를 찾아서 +1 하면 그 다음 번호를 붙일 수 있다.

SELECT MAX(no) + 1 nextNo FROM board;

---

JOIN

• 계속해서 중복되는 데이터는 테이블을 따로 만들어서 JOIN 시킨다. -> 속도는 조금 느려지지만 관리가 쉽다.
  : 예를 들면 회원 리스트에서 등급:1, 일반회원은 중복된 데이터라 등급관리 테이블을 따로 만든다.
• CASE WHEN을 사용하면 속도는 빠르지만 쿼리가 복잡해진다.

  테이블 두개 만들어서 FK 설정하기, CASE WHEN을 사용한 쿼리
  > 12월 20일 수업내용

회원관리 스키마

회원관리 운영쿼리

Inner Join

Outter Join

• Null 값을 JOIN하면 데이터가 나오지 않는데 Null값을 JOIN해도 데이터가 나오게 할 수 있다.

---

이미지 게시판

이미지 게시판 스키마

-- 이미지 스키마
-- 1.제거
DROP TABLE image CASCADE CONSTRAINTS;
DROP SEQUENCE image_seq;

-- 2.생성
CREATE TABLE image (
    no NUMBER PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    content VARCHAR(2000) NOT NULL,
    id VARCHAR2(20) NOT NULL REFERENCES member(id),
    writeDate DATE DEFAULT sysdate,
    fileName VARCHAR2(50) NOT NULL
);

CREATE SEQUENCE image_seq;

-- 3. 샘플데이터
INSERT INTO image (no, title, content, id, fileName)
VALUES (image_seq.nextval, '제목1', '내용1', 'test', '이미지1.jpg');
INSERT INTO image (no, title, content, id, fileName)
VALUES (image_seq.nextval, '제목2', '내용2', 'admin', '이미지2.jpg');
INSERT INTO image (no, title, content, id, fileName)
VALUES (image_seq.nextval, '제목3', '내용3', 'admin', '이미지3.jpg');
COMMIT;

-- 4. 확인
SELECT * FROM image;

이미지 게시판 운영쿼리

--이미지게시판 운영쿼리
-- 1. 리스트
SELECT i.no, i.title, m.id, m.name, i.writeDate, i.fileName
FROM image i, member m
WHERE i.id = m.id
ORDER BY no DESC;

-- 2. 보기
SELECT i.no, i.title, i.content, i.id, m.name, i.writeDate, i.fileName
FROM image i, member m
-- WHERE 일반조건 and 조인조건
WHERE (no = 3) and (i.id = m.id);

-- 3. 등록 : id는 member 테이블에 있는 아이디만 사용가능
-- 현재 이미지는 1개만 올릴 수 있다.
-- 한 게시글에 여러개를 올리고 싶으면 fileName1, fileName2 이런식으로 컬럼을 여러개 하던가 fileName 테이블을 한개 더 만들어서 JOIN 시키면 된다.
INSERT INTO image (no, title, content, id, fileName)
VALUES (image_seq.nextval, '제목4', '내용4', 'test', '이미지4.jpg');
COMMIT;

-- 4. 수정 - 제목, 내용, 이미지 (본인의 이미지는 본인만 수정할 수 있다)
UPDATE image SET title='제목수정', content='내용수정', fileName='이미지수정.jpg'
WHERE no = 3 and id = 'admin'; -- 3번글의 작성자가 누군지 확인 후에 조건을 걸어주기.
COMMIT;

-- 4-1. 파일명 변경
UPDATE image SET fileName='이미지수정'
WHERE no = 1 and id = 'test';
COMMIT;

-- 5. 삭제
DELETE FROM image
WHERE no = 2 and id = 'admin';
ROLLBACK;
COMMIT;