[학습일지]JAVA교육일지 47일차

1. DateTime Function

2. Casting Function

3. DDL

4. Sequence 

5. Insert

---

1. DateTime Function

[ SYSDATE ]

- 날짜 함수 중 가장 대표 함수이다.

- 현재 시스템의 시각을 반환한다.

SELECT SYSDATE AS NOW,
       SYSDATE-1 AS YESTERDAY,
       SYSDATE+1 AS TOMORROW
FROM DUAL;
--> NOW : 현재 날짜
--> YESTERDAY : 어제 날짜
--> TOMORROW : 내일 날짜

 

[ ADD_MONTHS ]

- 몇 개월 이후 날짜를 구하는 함수이다.

SELECT ADD_MONTHS(SYSDATE, 3)
FROM DUAL;
--> 현재날짜에서 3개월 뒤 출력

 

[ MONTHS_BETWEEN ]

- 두 개의 날짜 데이터를 입력하고, 두 날짜 간의 개월 수 차이를 구하는 함수이다.

SELECT 
    NAME, 
    MONTHS_BETWEEN(SYSDATE, IBSADATE) AS "근무시간(월)",
    MONTHS_BETWEEN(SYSDATE, IBSADATE) / 12 AS "근무시간(년)"
FROM TBLINSA;

--> 근무시간(월): 입사날짜 ~ 현재날짜 월 출력
--> 근무시간(년): 입사날짜 ~ 현재날짜 년 출력

 

[ NEXT_DAY, LAST_DAY ]

- NEXT_DAY: 입력한 날짜 데이터에서 돌아오는 요일의 날짜를 반환한다.

- LAST_DAY: 해당 날짜가 속한 달의 마지막 날짜를 반환한다.

SELECT
    NEXT_DAY(SYSDATE, '수요일'),
    LAST_DAY(SYSDATE)
FROM DUAL;
--> 현재날짜의 돌아오는 '수요일' 날짜 출력
--> 현재날짜의 달에 마지막일 수 출력

 

2. Casting Function

[ TO_CHAR (1) ]

- 날짜 데이터를 문자 데이터로 변환

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간
FROM DUAL;
--> 2021/05/17 20:20:12 (현재날짜시간 출력)

SELECT 
    TO_CHAR(SYSDATE, 'MM') AS MM,
    TO_CHAR(SYSDATE, 'MONTH') AS MONTH,
    TO_CHAR(SYSDATE, 'MON') AS MON,
    TO_CHAR(SYSDATE, 'DD') AS DD,
    TO_CHAR(SYSDATE, 'DAY') AS DAY,
    TO_CHAR(SYSDATE, 'DY') AS DY
FROM DUAL;
--> MM(월 2자리): 05
--> MONTH(월 풀네임): 5월
--> MON(월 약어): 5월
--> DD(일 2자리): 17
--> DAY(일 풀네임): 월요일
--> DY(일 약어): 월

 

[ TO_CHAR (2) ]

- 숫자 데이터를 문자 데이터로 변환

SELECT
    TO_CHAR(123.456),
    TO_CHAR(123.456, '$999.999'),
    TO_CHAR(123.456, 'L999.999'),
    TO_CHAR(123.456, '999'),
    TO_CHAR(123.456, '000'),
    TO_CHAR(1000000, '9,999,999')
FROM DUAL;
--> 123.456
--> $123.456
--> \123.456
--> 123
--> 123
--> 1,000,000

 

[ TO_NUMBER ]

- 문자 데이터를 숫자 데이터로 변환

SELECT TO_NUMBER('1300') - TO_NUMBER('1500') FROM DUAL;
--> -200

 

[ TO_DATE ]

- 문자 데이터를 날짜 데이터로 변환

SELECT * FROM TBLINSA
    WHERE IBSADATE BETWEEN
    TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
    TO_DATE('2010-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
--> 2010년도에 입사한 명단 출력

 

3. DDL

[정의]

- 데이터 정의어(DDL: Data Definition Laguage)는 데이터베이스 데이터를 보관하고 관리하기 위해 제공되는 여러 객체(object)의 생성, 변경, 삭제 관련 기능을 수행한다.

 

[ CREATE ]

- 오라클 데이터베이스 객체를 생성하는 데 사용하는 명령어이다.

CREATE TABLE 테이블명 (
    열1 이름  열1 자료형
    열2 이름  열2 자료형
    ...
    열N 이름  열N 자료형 NULL표기 제약사항
);

 

[ 제약 사항, Constraint ]

- 해당 컬럼에 들어갈 데이터(값)에 대한 조건(규제 사항)

- 조건을 만족하면 데이터 통과

- 조건을 만족못하면 데이터 거부 > 에러 발생 > 유효성 검사 도구

- 종류: NOT NULL, PRIMARY KEY(PK), FOREIGN KEY, UNIQUE, CHECK, DEFAULT

 

[ NOT NULL ]

- NULL을 가지지 못한다.(필수값)
- 해당 컬럼이 반드시 값이 가져야 한다.
- 해당 컬럼이 값이 없으면 에러

CREATE TABLE tblMemo (      
    name VARCHAR2(30) NOT NULL,      
    memo VARCHAR2(1000) NULL   
    regdate DATE
    --컬럼 끝에는 NULL이 항상 붙어있다.(따로 지정안하면 기본값 = NULL)
);
INSERT INTO TBLMEMO (name, memo, regdate) VALUES ('홍길동', '메모입니다1', SYSDATE);
INSERT INTO TBLMEMO (name, memo, regdate) VALUES ('홍길동', NULL, SYSDATE);
INSERT INTO TBLMEMO (name, memo, regdate) VALUES (NULL, '메모입니다2', SYSDATE);

SELECT * FROM TBLMEMO;
첫번째 INSERT --> name='홍길동', memo='메모입니다1', regdate=2021-05-17 데이터 추가
두번째 INSERT --> name='홍길동', meme=NULL, regdate=2021-05-17 데이터 추가 
세번째 INSERT --> name컬럼의 제약사항이 NOT NULL이므로 에러

 

[ PRIMARY KEY(PK) ]

- 해당 컬럼이 모든 레코드 중에 유일한 값을 가져야 한다.(필수값)

- 주로 식별자로 사용한다.
- 다른 레코드와 같은 값을 가질 수 없다.
- 반드시 테이블엔 PK가 존재해야 한다.
- 기본키의 형태
    a) 단일 기본키 > 기본키: 1개의 컬럼이 PK 역할
    b) 복합 기본키 > 복합키: 2개 이상의 컬럼이 모여서 PK 역할, Composite Key

CREATE TABLE TBLMEMO (
    SEQ NUMBER(3) PRIMARY KEY,    
    NAME VARCHAR2(30),      
    MEMO VARCHAR2(1000),    
    REGDATE DATE       
);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (1, '홍길동', '메모입니다', SYSDATE);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (2, '아무개', '테스트용1', SYSDATE);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (NULL, '아무개', '테스트용2', SYSDATE);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (1, '하하하', '테스트용3', SYSDATE);

SELECT * FROM TBLMEMO;
첫번째 INSERT --> SEQ=1, NAME='홍길동', MEMO='메모입니다', regdate=2021-05-17 데이터 추가
두번째 INSERT --> SEQ=2, NAME='아무개', MEMO='테스트용1', regdate=2021-05-17 데이터 추가 
세번째 INSERT --> SEQ컬럼의 제약사항이 PK이므로 에러(PK값은 반드시 존재해야 한다.)
네번째 INSERT --> SEQ컬럼의 제약사항이 PK이므로 에러(PK값은 중복값이 없어야 한다.)

 

[ UNIQUE ]

- ( PRIMARY KEY - NOT NULL )
- 중복값은 가질 수 없는데 NULL은 가질 수 있다.

CREATE TABLE TBLMEMO (
    SEQ NUMBER(3) PRIMARY KEY,     
    NAME VARCHAR2(30) UNIQUE, -- 중복값 금지, 식별자로는 사용할 수 없다.(NULL을 허용하기 때문에)   
    MEMO VARCHAR2(1000),    
    REGDATE DATE      
);

INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (1, '홍길동', '메모입니다', SYSDATE);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (2, '아무개', '메모입니다', SYSDATE);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (3, '홍길동', '다른메모다', SYSDATE);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (4, NULL, '메모입니다', SYSDATE);

SELECT * FROM TBLMEMO;
첫번째 INSERT --> SEQ=1, NAME='홍길동', MEMO='메모입니다', regdate=2021-05-17 데이터 추가
두번째 INSERT --> SEQ=2, NAME='아무개', MEMO='메모입니다', regdate=2021-05-17 데이터 추가 
세번째 INSERT --> NAME컬럼의 제약사항이 UNIQUE이므로 에러(중복값이 없어야한다.)
네번째 INSERT --> SEQ=4, NAME=NULL, MEMO='메모입니다', regdate=2021-05-17 데이터 추가

 

[ 제약 사항 + CHEECK ]

- 사용자 정의 제약 조건
- WHERE절에서 조건을 거는것과 동일

--방법1
CREATE TABLE MEMO (
    SEQ NUMBER(3) CONSTRAINT MEMO_SEQ_pk PRIMARY KEY,     
    NAME VARCHAR2(30) CONSTRAINT MEMO_NAME_CK CHECK(LENGTH(NAME) BETWEEN 3 AND 5),    
    MEMO VARCHAR2(1000) CONSTRAINT MEMO_MEMO_CK CHECK(LENGTH(MEMO) > 10),    
    REGDATE DATE      
);
--방법2: 가독성이 좋음(선호 방식)
CREATE TABLE MEMO (
    SEQ NUMBER(3),     
    NAME VARCHAR2(30),    
    MEMO VARCHAR2(1000),    
    REGDATE DATE,    
   
    CONSTRAINT MEMO_SEQ_pk PRIMARY KEY(SEQ),
    CONSTRAINT MEMO_NAME_CK CHECK(LENGTH(NAME) BETWEEN 3 AND 5),
    CONSTRAINT MEMO_MEMO_CK CHECK(LENGTH(MEMO) > 10)
);

SEQ제약사항 : 유일하면서 중복값 없는 PK값을 가진다.
NAME제약사항: 이름의 길이는 3글자 이상 5글자 이하 값을 가진다.
MEMO제약사항: 메모의 길이는 10글자 초과 값을 가진다.

--> 한개의 컬럼이라도 제약사항에 만족을 못한다면 에러발생

 

[ DEFAULT ]

- 기본값 설정
- 해당 컬럼값이 NULL이면 미리 준비한 기본값을 대입한다.

CREATE TABLE TBLMEMO (
    SEQ NUMBER(3) PRIMARY KEY,     
    NAME VARCHAR2(30) DEFAULT '익명',    
    MEMO VARCHAR2(1000) DEFAULT '메모없음',    
    REGDATE DATE      
);

INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (1, '홍길동', '메모입니다', SYSDATE);
INSERT INTO TBLMEMO (SEQ, NAME, MEMO, REGDATE) VALUES (2, NULL, NULL, SYSDATE);
INSERT INTO TBLMEMO (SEQ, REGDATE) VALUES (3, SYSDATE);

SELECT * FROM TBLMEMO;
첫번째 INSERT --> SEQ=1, NAME='홍길동', MEMO='메모입니다', regdate=2021-05-17 데이터 추가
두번째 INSERT --> SEQ=2, NAME=NULL, MEMO=NULL, regdate=2021-05-17 데이터 추가 
세번째 INSERT --> SEQ=3, NAME='익명', MEMO='메모없음', regdate=2021-05-17 데이터 추가

 

4. Sequence 

- 데이터베이스 객체 중 하나

- 식별자를 만드는데 주로 사용된다.(PK 컬럼에서 잘 사용된다.)

- 일련변호를 만들어서 제공하는 객체(중복되지 않은 번호)

- 오라클 전용

 

[ 시퀀스 만들기 ]

1. SEQUENCE 객체 만들기
CREATE SEQUENCE 시퀀스명;

2. SEQUENCE 객체 삭제하기
DROP SEQUENCE 시퀀스명;

3. SEQUENCE 객체 사용하기
시퀀스명.NEXTVAL 함수 --> 1씩 카운트
시퀀스명.CURRVAL 함수 --> 현재 데이터 값을 '출력만' 해준다.


CREATE SEQUENCE seqNum; --시퀀스 객체 생성
SELECT seqNum.NEXTVAL FROM DUAL; --시퀀스 1씩 카운트
DROP SEQUENCE seqNum; --시퀀스 객체 삭제

 

[ 시퀀스 상세 설정 ]

CREATE SEQUENCE 시퀀스명
    INCREMENT BY N  --> 증감치(양수, 음수) 
    START WITH N    --> 시작값(SEED)
    MAXVALUE N      --> 최댓값(넘어갈 시 에러)
    MINVALUE N      --> 최솟값(넘어갈 시 에러)
    CYCLE           --> 순환구조(PK 써먹기에 곤란함..)
    CACHE N;
    
--EX1)
CREATE SEQUENCE seqTset
    INCREMENT BY 1  
    START WITH 1    
    MAXVALUE 10           
    CYCLE          
    CACHE 5;
    
SELECT seqTest.NEXTVAL FROM DUAL;
--> 1부터 시작, 1씩 증가, 최대값은 10이며, 최대값 도달 시 1부터 다시 카운트 한다.

--EX2)
CREATE SEQUENCE seqTset
    INCREMENT BY 3  
    START WITH 2    
    MAXVALUE 10           

SELECT seqTest.NEXTVAL FROM DUAL;
--> 2부터 시작, 3씩 증가, 최대값 10이며, 최대값 도달 시 에러 발생.

5. Insert

- DML명령어에 속한다.

- 데이터를 테이블에 추가하는 명령어이다. (행 추가, 레코드 추가)

- INSERT INTO 테이블명 (컬럼 리스트) VALUES (값 리스트);

[ INSERT 예제1 ]

CREATE TABLE tblMemo (
    SEQ NUMBER PRIMARY KEY,
    NAME VARCHAR2(30) DEFAULT '익명' NOT NULL,
    MEMO VARCHAR2(1000) NOT NULL,
    REGDATE DATE DEFAULT SYSDATE
);

CREATE SEQUENCE seqMemo; --tblMemo.seq 대입

INSERT INTO tblMemo (SEQ, NAME, MEMO, REGDATE) 
    VALUES (seqMemo.NEXTVAL, '홍길동', '메모입니다.', SYSDATE); --1
    
INSERT INTO tblMemo (NAME, MEMO, REGDATE, SEQ) 
    VALUES (seqMemo.NEXTVAL, '홍길동', '메모입니다.', SYSDATE); --2
    
INSERT INTO tblMemo (NAME, MEMO, REGDATE, SEQ) 
    VALUES ('홍길동', '메모입니다.', SYSDATE, seqMemo.NEXTVAL); --3
    
INSERT INTO tblMemo (SEQ, NAME, MEMO, REGDATE) 
    VALUES (seqMemo.NEXTVAL, '홍길동', SYSDATE);               --4
    
INSERT INTO tblMemo (SEQ, NAME, REGDATE) 
    VALUES (seqMemo.NEXTVAL, '홍길동', '메모입니다.', SYSDATE); --5
    
1번째 INSERT: 컬럼 리스트, 값 리스트 구성대로 데이터 값 추가
2번째 INSERT: 컬럼 리스트의 순서와 값 리스트의 순서는 일치해야한다. ERROR
3번째 INSERT: 원본 테이블의 컬럼 순서와 INSERT 컬럼 순서는 무관하다. 구성대로 데이터 값 추가
4번째 INSERT: 값 리스트에 MEMO의 값이 없기 때문에 ERROR (NOT NULL)
5번째 INSERT: 컬럼 리스트에 MEMO가 없기 때문에 ERROR (NOT NULL)

 

[ INSERT 예제2 ]

CREATE TABLE tblMemo (
    SEQ NUMBER PRIMARY KEY,
    NAME VARCHAR2(30) DEFAULT '익명' NOT NULL,
    MEMO VARCHAR2(1000) NOT NULL,
    REGDATE DATE DEFAULT SYSDATE
);

CREATE SEQUENCE seqMemo; --tblMemo.seq 대입

INSERT INTO tblMemo (SEQ, NAME, MEMO) 
    VALUES (seqMemo.NEXTVAL, '홍길동', '메모입니다.');         --1
    
INSERT INTO tblMemo (SEQ, NAME, MEMO, REGDATE) 
    VALUES (seqMemo.NEXTVAL, '홍길동', '메모입니다.', NULL);   --2
    
INSERT INTO tblMemo (SEQ, MEMO) 
    VALUES (seqMemo.NEXTVAL, '메모입니다.');                   --3
    
INSERT INTO tblMemo (SEQ, NAME, MEMO, REGDATE) 
    VALUES (seqMemo.NEXTVAL, NULL, '메모입니다', NULL);        --4


INSERT INTO tblMemo (SEQ, NAME, MEMO, REGDATE) 
    VALUES (seqMemo.NEXTVAL, DEFAULT, '메모입니다', DEFAULT);
    
1번째 INSERT: REGDATE값이 없으므로 DEFAULT값으로 SYSDATE가 들어간다. (암시적 방법)
2번째 INSERT: REGDATE의 값이 NULL 이기때문에 NULL값으로 추가된다.    (명시적 방법)
3번째 INSERT: NAME값이 없으므로 DEFAULT값으로 '익명'이 들어간다.     (암시적 방법)
4번째 INSERT: NAME값이 명시 되었지만 NULL값이라 ERROR               (PRIMARY KEY)

---

MEMO >

# 데이터베이스에서 제약사항(Constraint)은 매우 중요하다.

 

# 여지껏 데이터베이스를 잘 이해하다가 오늘 여러가지 배우다보니까 많이 헷갈리고 못따라갔다. 복습하자!

 

# SQL은 앞에 배운 내용이 계속 누적되서 쓰이기 때문에 뒤쳐지면 안된다. 복습 철저히 할 것.