21-12-14 데이터베이스 오라클 DDL

 

DDL

 

테이블의 생성 (Create)

 

CREATE TABLE 테이블 이름 (
	필드명1 DATATYPE [DEFALUT값 or 제약조건 및 형식],
	필드명2 DATATYPE [DEFALUT값 or 제약조건 및 형식],
	필드명3 DATATYPE [DEFALUT값 or 제약조건 및 형식],
	constraint 제약조건이름 제약조건 ( 제약조건이 적용 될 필드 )
 )

 

제약 조건은 필드에만 적용하는 필드 레벨 제약조건(각 필드 옆에 작성)과 테이블 레벨의 제약조건(테이블 생성 명령 마지막에 작성)이 있다. 

 

 

 

※sql 파일 만들기 전 Database 연결이 되어있는지 확인 

-> Data Source Explorer에서 사용할 DB 우클릭 Connect 

 

 

파일 생성 후 Connected 상태인지 확인할 것

 

 

예시

 

도서 대여점의 도서 목록 테이블 생성

 

필드 : booknum, subject, makeyear, inprice, outprice

자료형 : booknum(문자 5자리), subject(문자 30자리), makeyear(숫자 4), inprice(숫자6), rentprice(숫자6)

제약조건 : booknum(not null), subject(not null), makeyer(), inprice(), outprice()

기본키 : booknum

 

CREATE TABLE booklist(
	booknum varchar2(5) NOT NULL,
	subject varchar2(30),
	makeyear number(4) DEFAULT 2021, --작성하지 않았을 시 기본값 2021
	inprice number(6) DEFAULT 0,
	rentprice number(6) DEFAULT 0, 
	--number(4)는 4bit가 아닌 4자리 숫자를 의미
	constraint booknum_pk primary key(booknum)
	--마지막 필드 작성 후 ',' 쓰지 않음
)

 

전체 선택 후 alt x로 실행하면 아래와 같이 실행 완료된다

 

 

 

방금 생성된 테이블이 제대로 생성되었는지 확인하려면

 

SELECT * FROM booklist;

 

를 입력하고 실행 

 

 

생성한 필드들이 생성된 것을 표 형태로 확인할 수 있다. 

 

 

※ CREATE TABLE의 세부 규칙

 

1. 테이블의 이름은 객체를 의미할 수 있는 적절한 이름을 사용한다. (자바의 이름 규칙과 거의 동일)

2. 테이블 이름은 다른 테이블과 중복되지 않게 지정한다

3. 한 테이블 내에서 필드 이름도 중복되지 않게 한다.

4. 각 필드들은 ", "로 구분하여 생성한다.

5. create를 비롯한 모든 sql 명령은 ";"로 끝난다.

6. 필드명 뒤에 DATATYPE은 반드시 지정하고 [] 안에 내용은 해당 내용이 있을 때만 작성하며 생략이 가능하다.

7. 테이블 명과 필드명은 반드시 문자로 시작해야 하고 예약어 명령어 등을 테이블명과 필드명으로 쓸 수 없다.

8. 테이블 생성 시 대소문자는 구분하지 않는다. (기본적으로 테이블이나 컬럼명은 대문자로 만든다.)

9. DATE 데이터 형식은 별도로 크기를 지정하지 않는다.

10. 문자 데이터의 데이터 타입 -> varchar2(), 숫자 데이터의 데이터 타입 -> number()

11. 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

12. 컬럼과 컬럼의 구분은 콤마로 하되 작성이 모두 종료되는 마지막 컬럼 또는 제약 사항의 내용 뒤에는 생략한다.

 

 

 

제약 조건 (CONSTRAINT)

 

- PRIMARY KEY 

테이블에 저장된 레코드를 고유하게 식별하기 위한 키, 하나의 테이블에 하나의 기본키만 정의할 수 있다. 여러 필드가 조합된 기본키(복합 키)의 생성도 가능하며 중복된 값, NULL값을 가진 필드를 사용할 수 있다.

=UNIQUE KEY + NOT NULL 

 

- UNIQUE KEY 

테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 말한다. 단 NULL은 고유키 제약 대상이 아니므로 NULL값을 가진 행이 여러 개 있어도 UNIQUE KEY가 될 수 있다.

 

- NOT NULL

NULL을 허용하지 않는다

 

-CHECK 

입력할 수 있는 값의 범위를 제한한다. CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정하는 경우가 많다.

 

- FOREIGN KEY

관계형 데이터 베이스에서 테이블 간에 관계를 정의하기 위해 기본키를 다른 테이블의 외래 키로 복사하는 경우. 추가로 참조 무결성 제약 옵션이 생성된다. 외래키는 참조하는 필드의 자료형과 참조되는 필드의 자료형이 일치해야 생성, 유지된다. 

 

 

cf)

슈퍼키 : 유일성 만족 최소성 만족 못함 

후보키 : 유일성과 최소성을 모두 만족함

기본키 : 후보키 중 테이블을 대표하는 값으로 선택됨, NULL 허용하지 않음 

대체키 : 후보키 중 선택되지 못함

 

유일성 : 테이블을 식별할 수 있는 속성 혹은 속성들의 조합

최소성 : 테이블을 식별할 수 있는 속성들의 집합 중 최소한의 조합임 

 

ex) 학생 테이블의 속성 - 학번 주민등록번호 학과 학년 반 이름 번호

==> 학번과 주민등록번호는 유일성과 최소성을 만족 

==> (이름, 학번)을 조합한 복합키는 유일성은 만족하지만 이름을 제외한 학번만으로도 유일성을 만족할 수 있으므로 최소성이 만족되지 않음. = 슈퍼키 

==> (학년, 반, 번호)를 조합한 복합키는 유일성을 만족하며 하나만 제외해도 유일성을 만족시킬 수 없으므로 최소성도 만족됨 = 후보키로 기본키나 대체키가 될 수 있음

 

 

예제

 

회원 리스트 테이블 생성

 

CREATE TABLE MEMBERLIST(
	memberNum NUMBER(6) NOT NULL,
	memberName varchar2(12) NOT NULL,
	Phone varchar2(13) NOT NULL ,
	Birth DATE, 
	Bpoint NUMBER(6) DEFAULT 0,
	joinDate DATE DEFAULT sysdate,
	CONSTRAINT memberNum_pk PRIMARY KEY(memberNum)
)

SELECT * FROM MEMBERLIST;

 

 

 

※ 테이블 삭제

 

DROP TABLE 테이블이름;

 

 

예제2

 

복합키를 가지고 다른 테이블을 참조하는 외래키를 가지는 리스트

 

 

CREATE TABLE RENTLIST(
	RENT_DATE DATE DEFAULT SYSDATE,
	IDX NUMBER(5) NOT NULL, --해당 일자의 대여 순번
	BOOKNUM varchar2(5) NOT NULL,
	MEMBERNUM NUMBER(6) NOT NULL,
	DISCOUNT NUMBER(4),
	CONSTRAINT RENT_PK PRIMARY KEY (RENT_DATE, IDX), --복합 기본키
	CONSTRAINT RENT_FK1 FOREIGN KEY (BOOKNUM) REFERENCES BOOKLIST(BOOKNUM),
	-- RENTLIST의 BOOKNUM은 BOOKLIST 테이블의 BOOKNUM을 참조한다.
	CONSTRAINT RENT_FK2 FOREIGN KEY (MEMBERNUM) REFERENCES MEMBERLIST(MEMBERNUM)
)

 

RENTLIST 는 BOOKLIST의 BOOKNUM을 참조하는 외래키와 MEMBERLIST를 참조하는 외래키를 가진다

 

※복합 기본키를 생성할 때는 ( ) 괄호 안에 컬럼명을 콤마로 이어 적는다.  

 

 

데이터 타입

 

오라클의 자료형

: 지정 용량을 초과하는 데이터를 입력하면 자료 추가 오류 발생

 

1. 정수형 타입 : number(n)

　n에는 숫자의 자릿수를 적는다.

　ex) number(2)는 총 두 자리 정수형 값이 필드에 설정된다 (-99~99)

　

2. 실수형 타입 : number(n, m)

　n에는 자릿수, m에는 소수점 이하 자릿수를 적는다.

　ex) number(6, 2)는 소수점 이하 두 자리를 포함한 6자리 실수 값이 설정된다. 

 

3. 가변 문자열 : varchar2(n)  (최대 4000 byte)

　입력 데이터가 실제 크기를 넘어서면 넘어선 크기만큼 자료형의 크기가 늘어나지는 않지만 반대로 지정한 크기보다 작은 문자열이 저장되려고 하면 실제 저장 크기가 저장하려는 문자만큼 줄어들어서 저장된다. = 기억 장소 절약

 

4. 고정 문자열 : char(n) (최대 2000 byte)

　n은 바이트 수를 나타낸다 

　ex) char(10)은 영문자가 5개 들어갈 수 있다. 만약 문자를 1개만 쓰더라도 10byte의 용량을 차지한다.

 

5. 고정형 유니코드 문자 : nchar(n) (최대 2000 byte)

　다국어를 입력하기 위한 고정형 자료형 

 

6. 가변형 유니코드 문자 : bvarchar(n) (최대 4000 byte)

　다국어를 입력하기 위한 가변형 자료형

 

7. 날짜 데이터 

　DATE : BC 4712년 ~ 9999년 12월 31일까지 연월일시분초 입력 가능 (일반적으로 많이 사용)

　TIMESTAMP : 연 월 일 시 분 초 밀리초 입력 가능

　　- sysdate와 systimestamp는 현재 일자와 시간을 반환하는 오라클의 내부 키워드

　

8. LOB 타입

　Large Object의 약자로 대용량 데이터를 저장할 수 있는 타입

　-CLOB : 문자형 대용량 객체. 고정 길이와 가변 길이 문자 집합 지원 (최대 크기 (4GB-1)) *-1은 DB 블록 사이즈

　-NCLOB : 유니코드를 포함한 문자형 대용량 객체 (최대 크기 (4GB-1))

　-BLOB : 이진형 대용량 객체 (최대 크기 (4GB-1))

　-BFILE : 대용량 이진 파일에 대한 로케이터 (위치, 이름 저장) (최대 크기 4GB)

 

9. 현재는 잘 사용되지 않는 자료형 

　varchar : varchar2 와 같은 형식이지만 varchar2보다 용량이 작음 

　LONG : 최대 2GB의 가변 길이 문자형 

　FLOAT : number의 하위 타입. 2진수 기준 22byte

　BINARY_FLOAT : 32비트 부동 소수점

　BINARY_DOUBLE : 64비트 부동 소수점 

 

 

테이블 조작

 

1. 필드 명의 변경

 

ALTER TABLE 테이블이름 RENAME COLUMN 변경전이름 TO 변경후이름

 

※ 세미콜론 찍으면 오류남

 

 

예시

 

ALTER TABLE BOOKLIST RENAME COLUMN SUBJECT TO TITLE

ALTER TABLE BOOKLIST RENAME COLUMN TITLE TO SUBJECT

ALTER TABLE MEMBERLIST RENAME COLUMN MEMBERNAME TO NAME

ALTER TABLE RENTLIST RENAME COLUMN RENT_DATE TO RENTDATE

ALTER TABLE RENTLIST RENAME COLUMN IDX TO NUMSEQ

 

BOOKLIST

MEMBERLIST

RENTLIST

 

※ 테이블을 만들며 생성한 기본키와 외래키의 필드명을 바꾸어도 제약조건은 사라지지 않고 유지됨

 

 

2. 필드 자료형의 변경

 

ALTER TABLE 테이블명 MODIFY 필드명 자료형

--ex)
ALTER TABLE MEMBERLIST MODIFY NAME VARCHAR2(50)

 

※ column type incompatible with referenced column type : FK 제약조건이 걸려있는 컬럼의 수정 시도 시 발생하는 에러. (참조되는 컬럼의 자료형이 현재 수정하려는 컬럼 자료형과 호환되지 않음.)

 

FK 컬럼, 참조되는 컬럼은 위와같은 에러로 수정이 불가능 하려면 외래키 제약 조건을 수정하여 삭제하고, 자료형을 수정한 후 다시 제약 조건을 설정한다. 

 

 

3. 필드의 추가

 

ALTER TABLE 테이블명 ADD 필드명 자료형 

ex) ALTER TABLE BOOKLIST ADD GRADE VARCHAR2(15)

 

추가된 GRADE 컬럼

 

 

 

4. 필드의 삭제 

 

ALTER TABLE 테이블명 DROP COLUMN 필드명

--ex)	
ALTER TABLE BOOKLIST DROP COLUMN GRADE

 

GRADE 컬럼 삭제

 

 

 

5. 제약 조건의 제거

 

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름

--ex)
ALTER TABLE RENTLIST DROP CONSTRAINT RENT_PK

 

 

6.  제약 조건의 추가

 

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 제약조건 (제약조건 대상 컬럼)

--ex)
ALTER TABLE RENTLIST ADD CONSTRAINT RENT_PK PRIMARY KEY (NUM)
ALTER TABLE RENTLIST ADD CONSTRAINT FK1 FOREIGN KEY (NUM) REFERENCES BOOKLIST(NUM)

 

 

※ IN() 함수 : 괄호 안의 항목 중 하나에 해당하면 TRUE가 리턴되는 함수.

 

ALTER TABLE MEMBERLIST ADD CONSTRAINT CHECK_GENDER CHECK(GENDER IN('F','M'))

ALTER TABLE MEMBERLIST ADD CONSTRAINT CHECK_AGE CHECK(AGE<=120);

 

첫번째 코드를 실행하면 CHECK 함수에 의해 GENDER 필드에 들어갈 값이 IN() 함수 안의 항목 중 하나와 같다면 입력 허용, 아니면 허용하지 않음 두번째 코드는 AGE가 120을 초과하면 입력을 허용하지 않는다.

 

* CHECK(컬럼이름 BETWEEN A AND B) : 컬럼이름에 들어갈 수 있는 값이 A보다 크고 B보다 작게 설정

 

 

7. 테이블 복사 

 

CREATE TABLE 새로운테이블이름 AS SELECT * FROM 복사할테이블이름

 

 

8. 테이블 제거 

 

DROP TABLE 삭제할테이블이름 PURGE

 

마지막에 PURGE를 쓰지 않으면 나중에 복구가 가능, PURGE 를 쓰면 완전히 삭제 된다

 

 

 

 

뷰 VIEW

 

물리적인 테이블에 근거한 논리적인 가상 테이블.

실질적으로 데이터를 저장하고 있지 않지만 마치 테이블을 사용하는 것과 동일하게 사용할 수 있다. 

존재하는 테이블에 대한 일종의 쿼리문이며 두 개 이상의 테이블 혹은 또 다른 뷰를 참조할 수도 있다. 이때 참조하는 테이블을 기본 테이블이라고 하며 이 기본테이블 의 정보를 검색하여 테이블로 구성한 것이 뷰이다. -> 원본을 참조하기 때문에 원본이 변경되면 뷰도 함께 변경됨 

 

 

뷰 생성 방법

 

CREATE OR REPLACE VIEW 뷰이름 AS SELECT ~

 

SELECT 구문을 통해 선택된 컬럼 / 데이터만 뷰에 표시된다. 

 

create or replace view rentjointable as
select a.rentdate as "대여 일자", b.subject as "도서 제목", c.name as "회원 성명", 
		  c.bpoint as "사은포인트" , b.rentprice - a.discount  as "매출금액" 
from rentlist a, booklist b, memberlist c
where a.booknum = b.num and a.membernum = c.num;

select * from rentjointable;
select * from tab;

 

 

시퀀스 

 

테이블내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기

테이블 생성 후 시퀀스(일련번호)를 따로 만들어야 한다

 

시퀀스 생성방법

 

CREATE SEQUENCE 시퀀스이름 START WITH 시작숫자 INCREMENT BY 증가량

 

주로 NUBER 형식에 기본값으로 사용한다. (NUMBER(n)의 최댓값까지 증가 가능)

 

 

사용 예시

 

CREATE SEQUENCE MEMBER_SEQ START WITH 1 INCREMENT BY 1 
INSERT INTO MEMBERLIST(NUM, NAME, PHONE) VALUES(MEMBER_SEQ.NEXTVAL, '홍길동', '010-1111-2222');
INSERT INTO MEMBERLIST(NUM, NAME, PHONE) VALUES(MEMBER_SEQ.NEXTVAL, '홍길남', '010-1221-2222');
INSERT INTO MEMBERLIST(NUM, NAME, PHONE) VALUES(MEMBER_SEQ.NEXTVAL, '홍길서', '010-1331-2222');

SELECT * FROM MEMBERLIST

 

시퀀스이름.NEXTVAL => 사용된 다음 숫자를 넣음