Oracle DB #5

<유니온(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에 따른 이벤트 조건)에 따라 알아서 증가한다.