개발일지

최신 내림차순 게시글 리스트
"select * from ("
+ "select rownum NUM, N.* "
+ "from (select * from notice where "+field+" like ? order by regdate desc) N "
+ ") "
+ "where NUM between ? and ?";

게시글 수 가져오기
"select COUNT(ID) COUNT from ("
+ "select rownum NUM, N.* from (select * from notice where "+field+" like ? order by regdate desc) N "
+ ")";

다음 게시글 가져오기
"select * from notice where id = ( "
+ "select id from notice where regdate > "
+ "(select regdate from notice where id=?)"
+ " and rownum=1 "
+ ")";

이전 게시글 가져오기
"select * from notice where id = ("
+ " select * from (select * from notice order by regdate desc)"
+ " where regdate < (select regdate from notice where id=?) and rownum=1"
+ " )";

집계함수(count) 등에 해당되는 select 주체에는 테이블.* 같은 *은 쓸 수 없다.

DB의 데이터 타입 중 CONTENT 등에 사용되는 CLOB 데이터 타입의 경우 몇 GB 까지 들어가는 데이터형이므로 JOIN 문장에서는 사용할 수 없다.
(JOIN해서 사용할 일이 집계가 대부분이므로 굳이 그렇게 모든 field를 가져올 필요도 없다)
(사용하려면 variable 캐릭터 형식으로 형변환 후 사용할 수 있으나 4000자 내로 잘리게 된다)
(잘리지 않게 하려면 개별적으로 요청한다)


위 내용을 토대로 게시글(notice) 목록 마다 댓글(comment) 수 집계해서 가져오기

select N.id, N.title, N.writer_id, N.regdate, N.hit, N.files, count(C.id) CMT_COUNT
from notice N
left join "COMMENT" C on N.id = C.notice_id
group by N.id, N.title, N.writer_id, N.regdate, N.hit, N.files
order by N.regdate desc;


기존에 게시글 목록 가져오던 SQL

select * from (
select rownum NUM, N.* 
from (select * from NOTICE where "+field+" like ? order by regdate desc) N 
) 
where NUM between ? and ?;


둘을 합쳐야 한다 (굵게 표시된 곳에 넣어야 한다)


1) 댓글 집계수 컬럼 join한 테이블에서 order by절을 빼고 view로 만든다 
(view는 컬럼 추가정도만. 필터나 정렬은 빼는게 써먹기 좋음)

create view NOTICE_VIEW
as
select N.id, N.title, N.writer_id, N.regdate, N.hit, N.files, count(C.id) CMT_COUNT
from notice N
left join "COMMENT" C on N.id = C.notice_id
group by N.id, N.title, N.writer_id, N.regdate, N.hit, N.files
--order by N.regdate desc;


2) 리스트를 뽑을 때 가장 기준이 되는 테이블에 넣는다
select * from (
select rownum NUM, N.* 
from (select * from NOTICE_VIEW where "+field+" like ? order by regdate desc) N 
) 
where NUM between ? and ?;


3) 기존에 만들어둔 서비스클래스로 가서 쿼리를 NOTICE_VIEW로만 고쳐주면 적용된다

4) 추가로 만든 notice_view 테이블에 관련된 서비스는 새로이 notice_view 테이블용 entity class를 따로 만들어 superclass로 notice를 상속받아 사용한다
(content는 db 데이터타입 문제로 지웠으니 빈문자열로 기본값을 넣어 사용)
(어차피 게시글 리스트에는 본문이 보이지 않는다. 필요한 것만 구현하자)
(기존 list 서비스용 객체 생성 notice_view로 전부 바꾸고 새로운 entity import)

<유니온(UNION)>

-JOIN은 column을 합치는 작업이나 유니온은 레코드를 합치는 작업
-column의 갯수와 자료형만 맞춰주면 레코드를 합칠 수 있다

사용 예)
-게시판이 나뉘어져 있을 때 합쳐서 통합검색이 필요할 때
-하나의 테이블 일지라도 조건절에 따라 다르게 만들어진 결과물을 전부 합칠 때

SELECT ID, NAME, FROM MEMBER
UNION
SELECT WRITER_ID, TITLE FROM NOTICE;


UNION 옵션
UNION, MINUS, INTERSECT, UNION ALL

UNION 연산자
-동일한 레코드는 합쳐서 하나로 만든 뒤 출력

MINUS 연산자
-첫번째 레코드에서 두번째가 가진 레코드를 빼고 남은 결과물을 만듬

INTERSECT 연산자
-두 레코드의 공통 분모만을 남김

UNION ALL 연산자
-공통 분모와 상관 없이 모든 레코드를 한꺼번에 출력


ex)
select id, name from member where id like '%n%'
union
select id, name from member where id like '%g%';

select id, name from member where id like '%n%'
minus
select id, name from member where id like '%g%';

-하나의 테이블 내에서도 사용해 원하는 조건식을 채운 결과 값 들을 합칠 수 있다.
(광고 가려낼 때, 차단 기능 만들 때?)



<뷰(VIEW)>

게시글 조인하기
select n.id, n.title, n.write_id, m.name, count(c.id) cnt from
member m
right outer join notice n on m.id = n.writer_id
left outer join "COMMENT" c on n.id = c.notice_id
group by n.id, n.title, n.writer_id, m.name;

-게시글 테이블의 식별자,제목,작성자ID,이름,댓글수 출력
-게시글 테이블의 회원ID를 기준으로 회원 테이블 조인_회원id와 작성자ID 대조
-게시글 테이블의 게시글ID를 기준으로 댓글 테이블 조인_게시글ID와 댓글게시글ID 대조
-식별자,제목,작성자ID,이름으로 묶어서 집계한다.


위와 같은 쿼리를 매번 해주긴 귀찮으므로 VIEW로 정의해서 사용한다.


=VIEW로 만들기=

CREATE VIEW NOTICEVIEW
AS
select n.id, n.title, n.write_id, m.name, count(c.id) cnt from
member m
right outer join notice n on m.id = n.writer_id
left outer join "COMMENT" c on n.id = c.notice_id
group by n.id, n.title, n.writer_id, m.name;


=VIEW를 이용해 쿼리하기=

select * from NOTICEVIEW where title like '%오%'
order by seq desc



<데이터 딕셔너리(Data Dictinary)>

DBMS 내 사용자 정보, 권한, 테이블, 뷰, 제약조건, 함수, 프로시저 등 사용자 서비스를 위해 존재하는 데이터 베이스.
SELECT * FROM DICT; 를 통해 확인해 볼 수 있으며, GUI 형식의 관리 프로그램이 있기 전에는
딕셔너리를 확인하고 명령어를 입력해 테이블, 컬럼, 제약조건 등의 현재 상태를 확인하며 조작해야 했다.
도구 자체를 손댈 일이 없다면 딱히 DBMS의 딕셔너리를 사용할 일은 없다.



<제약 조건>

도메인 - 엔티티 - 릴레이션



=도메인 제약 조건=

-컬럼 내 유효한 값의 범위

종류)
NOT NULL, DEFAULT, CHECK


NOT NULL
-반드시 값이 들어가야 하는 컬럼
-ID, PW, TITLE, WRITEID 등

사용법)
CREATE TABLE TEST
(
ID VARCHAR2(50) NOT NULL,
EMAIL VARCHAR2(200) NULL,
PHONE CHAR(13) NOT NULL
)

테이블이 이미 생성된 경우
ALTER TABLE TEST MODIFY EMAIL VARCHAR2(200) NOT NULL;


DEFAULT
-반드시 값이 들어가야 하나, 사용자가 값을 전달하지는 않는 컬럼
-등록일자, 조회수 등

사용법)
CREATE TABLE TEST
(
ID VARCHAR2(50) NOT NULL,
EMAIL VARCHAR2(200) NULL,
PHONE CHAR(13) NOT NULL,
PWD VARCHAR2(200) DEFAULT '111'
)

테이블이 이미 생성된 경우
ALTER TABLE TEST MODIFY EMAIL VARCHAR2(200) DEFAULT '111';


CHECK
-값의 유효범위를 검사할 수 있게하는 제약 조건
ex) 학번 : 0보다 큰 정수, 이름 : 20자 내의 문자, 입력되는 핸드폰 번호의 포맷 형식 등

사용법)
CREATE TABLE TEST
(
ID VARCHAR2(50) NOT NULL,
PHONE VARCHAR2(200) CHECK(PHONE LIKE '010-%-____') NOT NULL,
EMAIL VARCHAR2(500) NULL
)

테이블이 이미 생성된 경우
ALTER TABLE TEST ADD CONSTRAINT CK_TEST_PHONE CHECK(PHONE LIKE '010-%-____');

제약조건을 검색하는 경우
SELECT * FROM user_constraints WHERE TABLE_NAME = 'TEST';

제약조건을 삭제하는 경우
ALTER TABLE TEST DROP CONSTRAINT CK_TEST_PHONE;

정규식을 이용한 체크 제약 조건
-'010-%-____' 오라클의 제약 조건의 경우 한계가 있다. '010-AAAA-1234', '010-12443243-1234' ...
-더 세밀한 제약 조건을 위해 정규식을 사용한다.

ex) 도구프로그램을 사용해 체크 제약 조건 등록 시
REGEXP_LIKE(PHONE, '^01[01]-\d{3,4}-\d{4}$');

명령어 사용시)
ALTER TABLE TEST ADD CONSTRAINT CK_TEST_PHONE CHECK(REGEXP_LIKE(PHONE, '^01[01]-\d{3,4}-\d{4}$'));

정규식 참고사이트
https://regexlib.com/CheatSheet.aspx



=ENTITY 제약 조건=

PRIMARY KEY / UNIQUE

-중복된 레코드가 없도록 제한한다.


PRIMARY KEY
-NULL이 들어갈 수 없다

UNIQUE
-NULL이 들어갈 수 있다


사용법)
CREATE TABLE NOTICE
(
ID NUMBER PRIMARY KEY, --기본키 제약 조건
TITLE VARCHAR2(300) NOT NULL, --NOT NULL
WRITER_ID VARCHAR2(50) NOT NULL UNIQUE, --NOT NULL/UNIQUE
CONTENT VARCHAR2(4000),
REGDATE DATE DEFAULT SYSDATE, --기본값 현재 날짜
HIT NUMBER DEFAULT 0 --기본값 0
)

제약 조건의 이름을 명시적으로 지정해줄 때)

ID NUMBER CONSTRAINT NOTICE_ID_PK PRIMARY KEY

또는 따로 하단부로 빼서 지정해준다

CREATE TABLE NOTICE
(
ID NUMBER,
WRITER_ID VARCHAR2(50) NOT NULL,
CONSTRAINT NOTICE_ID_PK PRIAMRY KEY(ID),
CONSTRAINT NOTICE_ID_UK UNIQUE(WRITER_ID)
)

테이블이 이미 생성된 경우
ALTER TABLE NOTICE 
ADD CONSTRAINT NOTICE_ID_PK PRIMARY KEY(ID);
ALTER TABLE NOTICE 
ADD CONSTRAINT NOTICE_WRITER_UK UNIQUE(WRITER_ID);

-도구프로그램(SQL DEVELOPER)을 사용할 경우 그냥 편집창 들어가서 PK에 체크해 주거나
-UNIQUE의 경우 제약조건 탭에서 새 고유 제약 조건을 처리해주면 된다



=시퀀스(SEQUENCE)=

-PK id 등 지속적으로 증가하는 값을 일일히 확인하기가 번거롭기 때문에 시퀀스로 등록
-데이터가 삽입 될 시 자동으로 증가하도록 해줄 수 있다.

다음으로 시작 : 시작 값 
증분 : 증분 값
최소값 : 최소 값
최대값 : 최대 값
캐시 : 시퀀스가 한번 작업당 미리 확보해놓을 결과값을 정해준다. 
- 레코드 삽입 마다 매번 연산을 하지 않기 때문에 레코드 삽입이 잦을 시 성능이 개선된다
주기 : 최대값에 도달했을 경우 다시 최소값부터 시작하는 순환을 할지 여부
정렬 : 캐시에 들어있는 데이터의 정렬의 보장성 여부

-값을 넣어주지 않아도 default 값들로 생성된다

시퀀스 등록 DDL 예제)
CREATE SEQUENCE NOTICE_ID_SEQ INCREMENT BY 1 START WITH 1;

사용법)
INSERT INTO NOTICE(ID, TITLE, WRITER_ID)
VALUES(NOTICE_ID_SEQ.NEXTVAL, '시퀀스1', '김댕댕');

사용법2)
편집 -> ID열 탭 -> 유형 : 열 시퀀스 -> 시퀀스 : NOTICE_ID_SEQ -> TRG1로 등록
INSERT INTO NOTICE(ITITLE, WRITER_ID)
VALUES('시퀀스1', '김댕댕');
-굳이 적지 않아도 트리거(CRUD에 따른 이벤트 조건)에 따라 알아서 증가한다.

<트랜잭션>

-하나의 단위(목적)으로 수행되길 바라는 쿼리의 묶음(업무 수행단위, 논리적 수행단위)
-두개 이상의 쿼리 문장으로 이루어진 명령어

<트랜잭션 처리>

-ACID를 유지하는 것 (automaticity_원자성, consistency_일관성, isolation_독립성, durability_지속성)

DB작업 중 트랜잭션이 걸릴 경우 DB를 사용하는 다른 사용자에 의해 LOCK이 걸린 상태이다
함께 작업하는 작업자에게 COMMIT이나 ROLLBACK을 요청해야한다



<DB 연산자>

산술 연산자
+, -, *, /
|| 문자열 더하기

비교 연산자
=, !=, ^=, <>, >, <, >=, <=, IS NULL, IS NOT NULL
!=, ^=, <>  용도가 같음
IS NULL, IS NOT NULL    사용예) select * from 테이블명 where is null

관계 연산자
NOT, AND, OR, BETWEEN, IN

BETWEEN 
select * form 테이블명 where 속성명 between 0 and 2;  
0~2까지를 조회
IN 
select * form 테이블명 where 속성명 in (0,2,7);  
0,2,7인 hit을 조회
NOT
select * form 테이블명 where 속성명 not in (0,2,7); 
0,2,7을 제외한 hit을 조회

패턴 연산자
LIKE, %, _
select * from 테이블명 where 속성명 like '박%';  
-박으로 시작하는 문자열을 찾음
select * from 테이블명 where 속성명 not like '박%';
-박으로 시작하지 않는 모든 문자열
'%박%'
-박이란 문자가 포함된 문자열을 찾음
'박_';
-박이란 문자로 시작하는 두글자 문자열을 찾음
'_박__';
- 박을 포함해 앞으로 한글자 뒤로 두글자인 문자열 찾음



정규식을 이용한 패턴 검색

참고사이트
https://regexlib.com/CheatSheet.aspx


^\D\w*@\D\w*.(org|net|com)$ -이메일 검색 정규식
^01[016-9]-\d{3,4}-\d{4}$ -핸드폰 번호 검색 정규식

사용 법)
select * from 테이블명 where regexp_like(속성명, '01[016-9]-\d{3,4}-\d{4}');

정규식 시작을 뜻하는 ^과 끝 $을 제외하면 해당 패턴이 문자열 일부에 속해 있더라도 찾을 수 있다 



행을 제한하기 (페이징)_(ROWNUM)
select * from 테이블명 where rownum between 1 and 5;
rownum은 기존 테이블에 속해있는 속성이 아니고 결과집합을 만들어낼 때 임시로 생성되므로 between 2 ~ 이상으론 출력할 수 없다.
페이징을 위해서는 먼저 테이블에 rownum을 생성해준 것처럼 쿼리로 결과집합을 만들어준 상태에서
조건비교를 시켜야 한다.
사용 법)
select * from (select rownum 별칭, 테이블명.* from 테이블명) where 별칭 between 6 and 10; 


중복 값 제거(DISTINCT)
select distinct 속성명 from member;

SQLPlus   cli 기반 OracleDB 관리 프로그램

SQLDeveloper   gui 기반 OracleDB 관리 프로그램   (CTRL+ENTER   블록된 명령어 실행)

오브젝트로 이루어진 데이터베이스 구조 -> SCHEMA
테이블 내의 다루고자 하는 개념상의 데이터 -> ENTITY

SQLPlus SQL 몇개

sqlplus sys as sysdba 
password 접속

select name from v$pdbs;
가상db 확인

EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
리스너 로컬 접속만 가능 해제 -> 원격 접속 활성화

다른 컴퓨터에서 접속시 host : 부분에 공인ip

(안되면 DB서버 컴퓨터 방화벽에 oracleDB에서 사용하려고 설정한 port 번호 열어주고

공유기 쓰면 공유기 설정에서도 열어줘야함)

DBMS 명령어

DDL
CREATE ALTER DROP
DML
INSERT SELECT UPDATE DELETE
DCL
GRANT REVOKE

Data Types
Character   'abcd', 'A', '148'
Numeric    38, 3.85, 3.85F, 137
Date         '2013-02-09'
LOB          '2019-06-03 10.38.29.00000'

Character Type
CHAR [(size [BYTE | CHAR])]   CHAR(3) 고정길이 3바이트 할당. size 1당 1바이트.
VCHAR2 (size [BYTE | CHAR])   VARCHAR2(3) 가변길이 3바이트.
NCHAR [(size)]   NCHAR(2)   16비트 UTF 인코딩 테이블 사용. size 1당 2바이트. 외국어 2글자.
NVARCHAR2 (size)
CLOB   대용량 텍스트 데이터 타입(최대 4G) 
NCLOB   대용량 텍스트 유니코드 데이터 타입(최대 4G)
MAX SIZE : STANDARD 4000bytes, EXTENDED 32767 bytes

Numeric Type
NUMBER[(p[,s])]
NUMBER(4)
NUMBER(6,2)   소수점 2자리를 포함한 최대6자리
NUMBER(6,-2)   소수점 -2자리에서 반올림하는 최대6자리
NUMBER   NUMBER(38,*)
NUMBER(*,5)   NUMBER(38,5)   

Date Type
DATE   4712 BC~9999 AD (EX:01-JAN-99)     BC4712년을 시작으로 시간을 카운트
TIMESTAMP     NLS_TIMESTAMP_FORMAT 파라미터에 명시된 값을 따름
TIMESTAMP WITH TIME ZONE     NLS_TIMESTAMP_TZ_FORMAT 파라미터에 명시된 값을 따름
TIMESTAMP WITH LOCAL TIME ZONE     NLS_TIMESTAMP_FORMAT 파라미터에 명시된 값을 따름

테이블 구성 예)

CREATE TABLE NOTICE
(
  ID NUMBER,
  TITLE NVARCHAR2(100),
  WRITER_ID NVARCHAR2(50),
  CONTENT CLOB,
  REGDATE TIMESTAMP,
  HIT NUMBER,
  FILES NVARCHAR2(1000)
);



명령어 사용 예)
(명령어는 대소문자를 가리지 않음)
(예약어 또는 공백을 사용하고 싶다면 큰따옴표로 감싸준다. 큰 따옴표 감쌀 시 대소문자 가림)


DDL
CREATE TABLE 생성할테이블이름;   
DROP TABLE 삭제할테이블이름;
ALTER TABLE 테이블이름 MODIFY 속성명 수정할데이터타입;
ALTER TABLE 테이블이름 DROP COLUMN 삭제할속성명;
ALTER TABLE 테이블이름 ADD 추가할속성명 데이터타입;


DML

INSERT INTO 테이블이름 (속성명1, 속성명2) VALUES('넣을값1', '넣을값2');   
-지정하지 않을 시 NULL값 입력. 값은 대소문자 가림.

SELECT * FROM 확인할테이블이름;
SELECT 속성명 바꿀속성명, 속성명2  FROM 확인할테이블이름;   
-애플리케이션과 DB의 속성명이 다를 시 쿼리문 작성할 때 속성명을 변경해서 애플리케이션에 제공
SELECT * FROM NLS_DATABASE_PARAMETERS;   
-환경설정 확인
SELECT LENGTH('한글') FROM DUAL;   
-문자 갯수 확인
SELECT LENGTHB('한글') FROM DUAL;   
-문자 바이트 확인

UPDATE 테이블이름 SET 속성명1='바꿀값1', 속성명2,='바꿀값2' WHERE 속성명='찾을값';
-테이블 내 모든 속성값을 바꿀게 아니라면 UPDATE 사용 시 반드시 조건절 WHERE을 사용해야한다.

DELETE 테이블이름 WHERE 속성명='삭제할값';
-테이블 내 모든 속성값을 삭제할게 아니라면 반드시 조건절 WHERE을 사용해야한다.
-삭제할 값이 속한 COLUMN을 삭제한다.