자바 프로그래밍_Day_34_DDL(CREATE)

 

 

2021. 10. 06

 

 

 

     > RANK 함수

       -> RANK() OVER (정렬기준) : 동일한 순위 이후의 등수를 해당 등수의 인원 수만큼 건너 뛰고 그 다음 순위를 계산한다.

          -> ex. 공동 1위가 2명이면 그 다음 순위는 3위

       -> DENSE_RANK() OVER (정렬기준) : 동일한 순위 이후의 등수를 무조건 1씩 증가시킨다.

          -> ex. 공동 1위가 2명이면 그 다음 순위는 2위

 

 

     > RANK 함수 실습

 

       -> 공동 19위 두 명 뒤에 바로 순위가 21위인 것을 확인할 수 있다.

 

 

       -> 공동 19위 두 명 출력 후, 다음 순위는 20위 출력

 

       -> RANK 함수는 WHERE절에서 사용할 수 없다.

 

       -> FROM 절 서브쿼리 안에서 RANK함수를 사용해준 결과에서 WHERE 조건식에 RANK함수를 사용해준다.

 

 

 

     > DDL (Data Definition Language)

       -> 데이터 정의 언어로 객체(OBJECT)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문(명령어)을 말한다.

       -> 오라클 객체 종류

          -> 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 프로시저(PROCEDUAL), 함수(FUNCTION), 트리거(TRIGGER), 동의어(SYNONYM), 사용자(USER)

 

 

     > CREATE

       -> 테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문으로 컬럼들은 괄호로 묶어서 지정한다.

       -> 표현식 : CREATE TABLE 테이블명(컬럼명 자료형(크기), 컬럼명 자료형(크기), ...);

 

 

     > 오라클의 데이터형

데이터형	설명
CHAR(크기)	고정길이 문자 데이터 (최대 2,000 Byte)
VARCHAR2(크기)	가변길이 문자 데이터 (최대 4,000 Byte)
NUMBER	숫자 데이터(최대 40자리)
NUMBER(길이)	숫자데이터로, 길이 지정이 가능하다.(최대 38자리)
DATE	날짜 데이터 (BC 4712년 1월 1일 ~ AD 4712년 12월 31일)
LONG	가변 길이 문자형 데이터 (최대 2GB)
LOB	2GB까지의 가변길이 바이너리 데이터 저장이 가능(이미지, 실행파일 등 저장 가능)
ROWID	DB에 저장되지 않는 행을 식별할 수 있는 고유 값 (ROWNUM과 같은 효과)
BFILE	대용량의 바이너리 데이터 저장이 가능 (최대 4GB)
TIMESTAMP	DATE형의 확장된 형태
INTERVAL YEAR TO MONTH	년과 월을 이용하여 기간 저장
INTERVAL DAY TO SECONT	일, 시, 분, 초를 이용하여 기간 저장

 

 

 

     > CHARACTER | CHAR

       -> CHAR( SIZE [( byte / char)] )

          -> SIZE : 포함될 문자(열)의 크기

          -> 지정한 크기보다 작은 문자(열)가 입력되면 남는 공간은 공백으로 채움

          -> 데이터는 ''를 사용하여 표기하고 대소문자를 구분함

실제 값	데이터 타입	저장 되는 값	설명
KIMCHI	CHAR(6)	KIMCHI
	CHAR(9)	KIMCHI***	공백 3칸 (3byte)
	CHAR(3)	(오류)	저장되는 글자는 6글자인데 공간은 3자리이기 때문에 오류 발생
김치	CHAR(6)	김치	한글은 한 글자 당 3byte이므로 공간에 딱 맞음
	CHAR(9)	김치***	공백 3byte
	CHAR(3)	(오류)	저장되는 글자는 총 6byte인데 공간은 3byte이므로 오류 발생

 

 

     > CHARACTER | VARCHAR2

       -> VARCHAR2( SIZE [( byte / char)] )

          -> SIZE : 포함될 문자(열)의 크기

          -> 크기가 0인 값은 NULL로 인식

          -> 데이터는 ''를 사용하여 표기하고 대소문자를 구분함

          -> CHAR와 비교 시 : 공백으로 채워 넣느냐, 안 넣느냐의 차이

실제 값	데이터 타입	저장되는 값	설명
KIMCHI	VARCHAR2(6)	KIMCHI
	VARCHAR2(10)	KIMCHI	CHAR였다면 나머지 공간을 공백으로 채웠지만, VARCHAR일 경우 가변이기 때문에 공간이 해당 데이터에 맞춰지게 된다.
	VARCHAR2(3)	(오류)	저장되는 글자는 6글자인데 공간은 3자리이기 때문에 오류
김치	VARCHAR2(6)	김치	한글은 한 글자 당 3byte이므로 공간에 딱 맞음
	VARCHAR2(10)	김치	CHAR였다면 나머지 공간을 공백으로 채웠지만, VARCHAR일 경우 가변이기 때문에 공간이 해당 데이터에 맞춰지게 된다.
	VARCHAR2(3)	(오류)	저장되는 글자는 6글자인데 공간은 3자리이기 때문에 오류

 

 

     > VARCHAR2 / NVARCHAR2

       -> 'N'이 있을 경우

          -> 유니코드 문자형, 모든 문자 2byte, 크기를 글자 수 단위로 받음

       -> 'N'이 없을 경우

          -> 한글/한자 : 3byte, 영어 / 숫자 / 기호 : 1byte, 크기를 바이트 수 단위로 받음

 

       -> 'VAR'가 있을 경우

          -> 가변 문자

       -> 'VAR'가 있을 경우

          -> 고정 문자

 

       -> '2'가 없을 경우

          -> 2000byte

       -> '2'가 있을 경우

          -> 4000byte

 

       -> 오라클에서는 VARCHAR2를 주로 쓴다.

 

     > NUMBER

       -> NUMBER[ ( P [, S ] ) ]

          -> P : 표현할 수 있는 전체 숫자 자리 수 (1 ~ 38)

          -> S : 소수점 이하 자리 수 (-84 ~ 127)

실제 값	데이터 타입	저장 되는 값	설명
12345.678	NUMBER	12345.678
	NUMBER(7)	12346	7자리이지만 정수는 5자리이므로 5개만 표현, 첫 번째 소수로 인해 반올림 되어 저장
	NUMBER(7, 1)	12345.7	7자리이지만 정수 5자리와 소수 1자리만 표현, 두 번째 소수로 인해 반올림 되어 저장
	NUMBER(7, 3)	(오류)	7자리, 소수점 이하 3자리로 정수는 총 4자리인데 실제 값의 정수는 5자리이므로 오류
	NUMBER(5, -2)	12300	S가 -2여서 소수점 왼쪽 두 번째 자리 4가 반올림되어 저장
0.1234	NUMBER(4, 5)	(오류)	유효숫자는 4개가 맞지만 소수점 아래가 5자리인데 4자리이므로 오류
0.01234	NUMBER(4, 5)	0.01234
0.0001234	NUMBER(3, 7)	(오류)	소수점 이하 일곱 째 자리까지 유효숫자는 4개인데 P가 3이므로 오류
0.00001234	NUMBER(3, 7)	0.0000123	소수점 이하 일곱 째 자리까지 유효숫자는 3개이기 때문에 마지막 4 제외

 

 

     > DATE

       -> DATE

          -> 일자(세기 / 년 / 월 / 일) 및 시간(시 / 분 / 초) 정보 관리

          -> 기본적으로 화면에 년 / 월 / 일 정보만 표기

          -> 날짜 연산 및 비교 가능

연산	결과 타입	설명
날짜 + 숫자	DATE	날짜에서 숫자만큼 며칠 후
날짜 - 숫자	DATE	날짜에서 숫자만큼 며칠 전
날짜 - 날짜	NUMBER	두 날짜의 일수 차
날짜 + 숫자/24	DATE	날짜 + 시간

 

 

     > 컬럼 주석

       -> 테이블의 컬럼에 주석을 다는 구문

       -> 표현식 : COMMENT ON COLUMN 테이블명.컬럼명 IS '주석 내용';

 

 

     > CREATE 실습 코드

 

 

       -> TABLE - MEMBER 생성

       -> 테이블 삭제 시 DROP TABLE 테이블명;

 

       -> 생성한 테이블 확인

          -> DESC MEMBER; -> 테이블의 구조를 표시해주는 구문

 

 

 

       -> 컬럼에 주석달기

 

 

 

 

       -> 데이터 딕셔너리 (Data Dictionary)

 

 

 

 

       -> 메타데이터들에 대한 구조를 확인할 수 있다.

 

 

       -> INSERT INTO 샘플 데이터 추가

 

 

 

       -> 커밋작업 : 커밋 전에는 추가한 데이터는 메모리 버퍼에 임시 저장 되므로 실제 테이블에 반영시키는 작업을 진행해줘야 한다.

       -> 커밋을 해야 다른 계정에서 부를 수 있다.

 

 

       -> 오라클은 오토커밋(autocommit)이 비활성화 되어있다.

       -> 오토 커밋(autocommit)이 on으로 되어 있으면 데이터를 commit해주지 않아도 자동으로 커밋해줘서 실제 데이터에 반영될 수 있게 해준다.

       -> autocommit을 on / off 하는 명령어는 SET AUTOCOMMIT ON/OFF 로 실행시켜줄 수 있다.

 

 

 

     > 제약 조건(CONSTRAINTS)

       -> 테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약조건을 설정할 수 있는데 이는 데이터 무결성 보장을 주 목적으로 한다.

       -> 입력 데이터에 문제가 없는지에 대한 검사와 데이터의 수정/삭제 가능 여부 검사등을 위해 사용한다.

          -> * 데이터 무결성(data integrity) : 컴퓨팅 분야에서 완전한 수명 주기를 거치며 데이터의 정확성과 일관성을 유지하고 보증하는 것을 가리키며 데이터베이스나 RDBMS 시스템의 중요한 기능

 

제약 조건	설명
NOT NULL	데이터에 NULL을 허용하지 않음
UNIQUE	중복된 값을 허용하지 않음, 데이터의 유일성 보장
PRIMARY KEY	NULL과 중복 값을 허용하지 않음(컬럼의 고유 식별자로 사용하기 위해), 테이블 당 하나만 설정 가능
FOREIGN KEY	참조되는 테이블의 컬럼의 값이 존재하면 허용, 외래키 | 참조키
CHECK	저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용

 

     > 제약 조건(CONSTRAINTS) 확인

       -> DESC USER_CONSTRAINTS;

       -> DESC USER_CONS_COLUMNS;

 

 

     > 제약 조건(CONSTRAINTS) | NOT NULL

       -> 해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용

       -> 특정 컬럼에 값을 저장하거나 NULL값을 허용하지 않도록 컬럼 레벨에서만 지정해줄 수 있다.

       -> NOT NULL 제약조건이 설정된 컬럼에 NULL값이 입력되면, 행 자체를 삽입하지 않음

 

       -> 사용자가 작성한 제약조건을 확인하는 뷰

 

       -> 제약조건이 걸려있는 컬럼을 확인하는 뷰

 

       -> 기존 MEMBER 테이블은 값에 NULL이 있어도 삽입 가능 (NOT NULL 제약조건 전)

 

 

       -> NOT NULL 제약조건 설정된 테이블 생성

       -> NOT NULL 제약조건은 컬럼 레벨에서만 설정이 가능하다.

 

 

       -> NOT NULL 제약조건이 설정된 테이블 생성 후 NULL이 들어있는 값을 입력하면 오류가 발생한다. (NOT NULL 제약조건 후)

 

 

       -> NOT NULL 제약조건이 걸려있는 컬럼에는 반드시 값이 있어야 한다.

 

 

 

 

 

     > 제약 조건(CONSTRAINTS) | UNIQUE

       -> 컬럼 입력 값에 대해 중복을 제한하는 제약조건으로 컬럼 레벨과 테이블 레벨에 설정 가능

       -> 중복 값이 있는 경우 UNIQUE 제약 조건에 의해 행이 삽입되지 않음

       -> UNIQUE 제약조건은 NULL 값 중복은 가능 -> 테이블 생성 시 컬럼 레벨에 NOT NULL 지정하면 해결

       -> 두 개의 컬럼을 묶어 하나의 UNIQUE 제약조건 설정이 가능

 

 

     > UNIQUE 제약조건 실습 코드

       -> UNIQUE 제약조건 때문에 중복값 입력 시 오류 발생

 

 

 

 

 

     > 제약 조건(CONSTRAINTS) | PRIMARY KEY

       -> 테이블에서 한 행의 정보를 구분하기 위한 고유 식별자 (Identifier) 역할

       -> NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며 한 테이블 당 하나만 설정 가능

       -> 컬럼 레벨과 테이블 레벨 둘 다 지정 가능

       -> 두 개의 컬럼을 묶어 하나의 PRIMARY KEY 제약조건 설정이 가능

 

 

 

       -> 기본키 (PRIMARY KEY) 중복으로 오류 발생

 

       -> 기본 키가 NULL 이므로 오류 발생

 

 

 

       -> 두 개의 컬럼을 묶어서 하나의 기본 키를 설정 -> 복합키

 

 

 

 

 

 

     > 제약 조건(CONSTRAINTS) | FOREIGN KEY

       -> 참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는 것

       -> 참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성 되는데, 참조되는 값은 제공되는 값 외에 NULL 사용이 가능하며, 참조할 테이블의 참조할 컬럼명을 생략할 경우 PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 된다.

       -> 부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤 방식으로 처리할지에 대한 내용을 제약조건 설정 시 옵션으로 지정 가능하다. -> 기본 삭제 옵션은 ON DELETE RESTRICTED로 지정되어 있음

 

       -> FOREIGN KEY 옵션 (ON DELETE SET NULL)

          -> 부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 NULL로 변경됨

 

       -> FOREIGN KEY 옵션 (ON DELETE CASCADE)

          -> 부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 존재하던 행 전체 삭제

 

       -> 부모테이블에 50이라는 값이 없기 때문에 외래키 제약 조건에 위배되어 오류가 발생한다.

 

 

     > 제약 조건(CONSTRAINTS) | CHECK

       -> 해당 컬럼에 입력 되거나 수정되는 값을 체크하여 설정된 값 이외의 값이면 에러 발생

       -> 비교 연산자를 이용하여 조건을 설정하고 비교 값을 리터럴만 사용 가능하고 변하는 값이나 함수 사용은 불가능

 

 

 

 

     > SUBQUERY를 이용한 CREATE TABLE

       -> 서브 쿼리를 이용해서 SELECT의 조회 결과로 테이블을 생성하는 방법으로 컬럼명과 데이터 타입, 값이 복사 되고 제약 조건은 NOT NULL만 복사됨