[Oracle] DDL

DDL(database definition language): 데이터 정의어

​

제약조건(CONSTRAINT)

1. PRIMARY KEY(기본키)

-테이블에 저장된 레코드를 고유하게 식별하기 위한 기본키, 하나의 테이블에 하나의 기본키만 정의할 수 있음

-테이블을 구성하는 필드들 중 자격이 되는 필드 중 하나에 부여하는 테이블의 대표값이라고 할 수 있음

-테이블을 구성하는 필드들 중에서 갖는 값들이 빈칸(null)이 없고 서로 다른 값(유일한 값. 즉 중복된 값을 갖을 수 없음)을 갖고 있어 레코드들을 유일하게 구분해낼 수 있는 필드를 기본키로 지정함

-기본키를 지정함으로써 기본키가 지정하지 않았을 때 발생할 수 있는 오류들을 미연에 방지할 수 있음

-기본키로 결함을 없애는 것. 그래서 결함없이 유지되는 것을 개체 무결성이라고 부름

-여러 필드가 조합된 기본키 생성 가능함

-PRIMARY KEY=UNIQUE KEY+NOT NULL

​

2. UNIQUE KEY

-테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의함

-단 NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러개가 UNIQUE KEY 제약에 위반하지는 않음

​

3. NOT NULL

-비어있는 상태, 아무것도 없는 상태를 허용하지 않음 -입력 필수

​

4. CHECK

-입력할 수 있는 값의 범위를 제한함.

-CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정함

​

5. FOREIGN KEY

-테이블 간의 필수 포함 관계에 있어 상대 테이블의 특정 필드값을 참조하면서 없는 값을 사용하지 않는 필드

-관계형 데이터 베이스에서 테이블 간에 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성됨.

-외래키로 유지되는 무결성을 참조무결성이라고 부름

-외래키가 생성되려면 언급되는 필드들에 연결되는 필드들의 자료형도 일치해야함

---

데이터 생성(CREATE)

Create Table 테이블이름(
	필드명1 DATATYPE[DEFAULT 값 or 제약조건 및 형식],
	필드명2 DATATYPE[DEFAULT 값 or 제약조건 및 형식],
	필드명3 DATATYPE[DEFAULT 값 or 제약조건 및 형식],
    ...
);

 

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

-다른 테이블과 중복되지 않게 테이블 이름을 지정함

-한 테이블 내에서 필드이름도 중복되지 않게 함

-각 필드들은 ','로 구분하여 생성함

-create를 비롯한 모든 sql 명령은 ';'로 끝남

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

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

-테이블 생성 시 대/소문자 구분은 하지 않음(기본적으로 테이블이나 컬럼명은 대문자로 만들어짐)

-DATE 데이터 형식은 별도로 크기를 지정하지 않음

-문자데이터의 DataType: varchar2(10), 숫자데이터의 데이터타입: number(4)

-문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 함

-컬럼과 컬럼의 구분은 콤마로 하되, 작성이 모두 종료되는 마지막 컬럼 또는 제약사항의 내용 뒤에는 콤마를 찍지 않음

-- 도서대여점의 도서목록 테이블의 생성
-- 테이블명: booklist
-- 필드: booknum, subject, makeyear, inprice, outprice, rentprice
-- 자료형: booknum(문자 5자리), subject(문자 30), makeyear(숫자 4),
          inprice(숫자 6), rentprice(숫자 6)

drop table booklist purge;
-- 기존에 있을 수 있는 booklist 테이블을 삭제함
 
create table booklist(
	booknum varchar2(5) not null, 
-- not null: 필드값에 null값이 올 수 없다라는 제약 조건. 
-- 빈칸으로 놔둘 수 없으니 무엇이든 값을 채워야 함.
	subject varchar2(30) not null,
	makeyear number(4) default 2022,
	inprice number(6) default 0,
	rentprice number(6) default 0,
	constraint blist_pk primary key(booknum)
-- constaraint: 테이블레벨의 제약조건을 지정하는 키워드
-- blist_pk: 테이블 외부에서 현재 제약조건을 컨트롤 하기 위한 
             제약조건의 고유이름
-- primary key(booknum): 기본키로 booknum을 지정하겠다는 뜻
	
-- 제약조건은 필드 옆에 기술하는 필드레벨 제약조건이 있고, 
-- (subject varchar2(30) not null에서 not null이 필드레벨 제약조건)
-- 테이블 생성 명령 마지막에 모아서 쓰는 테이블 레벨의 제약조건이 있음. 
-- 제약조건은 어디에다 써도 무관함. 
-- 다만 필드레벨의 제약조건은 해당 필드 옆(오른쪽 제약조건란)에 써야 
-- 그 필드에 적용됨

-- 마지막 필드를 또는 기타사항 등을 쓰고 더이상 쓸 내용이 없으면 
-- 컴마(,)를 쓰지 않음
);


-- 테이블 생성2
-- 테이블 이름: MemberList(회원리스트)
-- 필드: memberNum, memberName, Phone, Birth, Bpoint, joinDate
-- 데이터 형식: memberNum: VARCHAR2(5), memberName: VARCHAR2(12),
--			   phone: VARCHAR2(13), Birth: DATE, Bpoint: NUMBER(6),
--			   joinDate: DATE
-- 제약조건
-- 1. memberNum, memberName, Phone: Not Null 필드레벨로 설정
-- 2. memberNum: Primary Key 테이블 레벨로 설정
-- 3. Bpoint: 기본값 0
-- 4. joinDate: 기본값 sysdate(joindate date default sysdate)

drop table memberlist purge;

create table memberlist(
	memberNum varchar2(5) not null,
	memberName varchar2(12) not null,
	Phone varchar2(13) not null,
	Birth date,
	Bpoint number(6) default 0,
	joinDate date default sysdate,
	constraint mlist_pk primary key(memberNum)
);


-- 테이블 생성 3
-- 테이블 이름: rentlist
-- 필드: rent_date(date), idx(number(3)), bNum(number(5)), 
--		 mNum(number(5)), discount(number(4))
-- 제약조건
-- 1. bNum, mNum: not null
-- 2. rent_date: default(오늘 날짜)
-- 3. rent_date, idx: 조합에 의한 기본키 설정

drop table rentlist purge;

create table rentlist(
	rent_date date default sysdate,
	idx number(3) not null, -- 해당일자의 대여순번
	bNum varchar2(5) not null,
	mNum varchar2(5) not null,
	discount number(4),
	constraint r_pk primary key(rent_date, idx),
-- 두 개의 필드가 조합되어 기본키로 지정될 수 있음. 이를 슈퍼키라고 함
	constraint r_fk1 foreign key(bNum) 
    references booklist(booknum),
-- rentlist 테이블의 bNum은  
-- booklist 테이블의 booknum을 참조하는 외래키로 지정
-- (제약조건이름 r_fk1)
	constraint r_fk2 foreign key(mNum)
    references memberlist(memberNum)
-- renlist 테이브르이 mNum은 
-- memberlist 테이블의 memberNum을 참조하는 외래키로 지정
-- (제약조건이름 r_fk2)	
);

 

해당 블록 선택 후 Alt+x하면 table 생성됨

---

테이블 수정(ALTER)

 

1. 필드명 변경

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

-- booklist 테이블의 subject 필드명을 title로 수정
alter table booklist rename column subject to title;
-- 반대로 title 필드명을 subject 필드명으로 수정함
alter table booklist rename column title to subject;
-- memberlist 테이블의 membername 필드를 name으로 변경
alter table memberlist rename column membername to name
-- rentlist 테이블의 rent_date 필드를 rentdate로 변경
alter table rentlist rename column rent_date to rentdate
-- rentlist의 idx를 num으로 변경
alter table rentlist rename column idx to num
-- booklist의 booknum을 num으로 변경
alter table booklist rename column booknum to num
-- memberlist의 membernum을 num으로 변경
alter table memberlist rename column membernum to num

*바뀐 내용을 확인하고 싶으면 select를 이용함

select*from booklist;
select*from memberlist;
select*from rentlist;

 

booknum 필드가 num으로 변경된 것을 확인할 수 있

membername → name     membernum → num

 

2. 필드 자료형 변경

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

-외래키와 상관없는 자료형 변경

-- varchar2(12)였던 memberlist 테이블의 name 필드를 
-- varchar2(30)으로 변경
alter table memberlist modify name varchar2(30); -- 수정 성공

-외래키로 연결되어 있는 필드의 자료형 변경

-- booklist의 num(varchar2(5))필드를 number(5)로 자료형 변경
alter table boolist modify num number(5); -- 수정 실패
-- rentlist의 bNum(varchar2(5))필드를 number(5)로 자료형 변경 
alter table rentlist modify bnum number(5); -- 수정 실패
-- 참조되거나 참조하는 컬럼(필드)의 자료형이 
-- 현재 수정하려는 컬럼(필드) 자료형과 호환되지 않음

 

-외래키는 참조하는 필드의 자료형과, 참조되는 필드의 자료형이 일치해야 생성도 되고 유지도 됨

-외래키로 연결되어서 참조되고 참조하고 있는 필드들은 위의 명령으로 수정이 바로 불가능함

-가능하게 하려면 외래키 제약 조건을 수정하여 없애버리고 참조되는 필드와 참조하는 필드를 모두 수정한 후 외래키 제약 조건을 다시 설정함

 

 

3. 필드 추가

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

-- booklist에 구매등급을 저장할 수 있는 
-- grade 필드를 varchar2(15)로 추가
alter table booklist add grade varchar2(15);

-- memeberlist에 성별(gender)필드를 varchar2(3)으로 추가
alter table memberlist add gender varchar2(3);

-- memberlist에 나이(age)필드를 number(3)로 추가
alter table memberlist add age number(3);

 

4. 필드 삭제

ALTER TABLE 테이블명 DROP COLUMN 필드명

 

5. 제약조건 추가/제거

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

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

-- 필드레벨로 지정되어 있는 제약조건은 modify로 수정(추가, 삭제)함
-- booklist의 num varchar2(5) 수정
alter table booklist modify num number(5);
-- memberlist의 num varchar2(5) 수정
alter table memberlist modify num number(5);
-- rentlist의 bNum varchar2(5) 수정
alter table rentlist modify bNum number(5);
-- rentlist의 mNum varchar2(5) 수정
alter table rentlist modify mNum number(5);

-- 테이블레벨로 지정되어 있는 제약조건은 
-- alter table 테이블명 drop(or add) constraint 제약조건명으로 
-- 수정/추가함. 
-- 이때 제약조건의 이름이 사용됨

-- rentlist 테이블의 bnum에 걸려 있는 외래키 제약조건 제거
alter table rentlist drop constraint r_fk1;
-- rentlist 테이블의 mnum에 걸려 있는 외래키 제약조건 제거
alter table rentlist drop constraint r_fk2;
-- rentlist 테이블의 기본키 제거
alter table rentlist drop constraint r_pk;

-- 위에 삭제되었던 r_fk1, r_fk2, r_pk 제약조건은 다시 설정. 
-- r_pk는 num 필드 하나로만 설정함
alter table rentlist add constraint r_fk1 foreign key(bNum) 
references booklist(num);
alter table rentlist add constraint r_fk2 foreign key(mNum) 
references memberlist(num);
alter table rentlist add constraint r_pk primary key(num);

-- memberlist 테이블의 성별(gender) 필드에
-- 'F', 'M' 두글자만 입력되도록 제약조건을 추가
alter table memberlist add constraint 
check_gender check(gender in('F','M'));
-- in()함수: 괄호 안의 항목 중 하나에 해당하면 true가 리턴되는 함수. 
-- 위의 내용은 check 함수에 의해 gender 필드의 돌아갈 값이 
-- in() 함수 안의 항목 중 하나와 같다면 입력 허용, 
-- 아니면 불허하는 제약조건임

-- memberlist 테이블의 나이(age) 필드에 
-- 120살이 초과되는 나이는 입력되지 못하게 제약 조건 추가
alter table memberlist add constraint chk_age check(age<120);

---

EX

-- 테이블 생성 연습 문제
-- 테이블명: ORDERS1
-- 필드: 
--   order_id number(12,0), order_date date, 
--   order_mode varchar2(8), custobe_id number(6,0),
--	 order_status number(2,0),order_total number(8,2),
--	 sales_rep_id number(6,0), promotion_id number(6,0)
-- 제약사항
-- 1. order_id: 기본키
-- 2. order_mode에는 'direct', 'online'만 입력가능한 테이블레벨 생성
-- 3. order_total: 디폴트 값 0

create table orders1(
	order_id number(12,0) ,
	order_date date,
	order_mode varchar2(8),
	customer_id number(6,0),
	order_status number(2,0),
	order_total number(8,2) default 0,
	sales_rep_id number(6,0),
	promotion_id number(6,0),
	constraint pk_order primary key(order_id),
	constraint ck_order_mode check(order_mode in('direct','online'))
);

-- 테이블 수정 연습문제
-- customer_id 필드명을 customer_number로 수정
alter table  orders1 rename column customer_id to customer_number;

-- promotion_id 값은 10000~99999 사이의 값만 저장 가능
-- 작성 tip: promotion_id between 10000 and 99999
alter table orders1 add constraint pro_check 
check(promotion_id between 10000 and 99999);

-- 테이블의 복사
create table orders2 as select*from orders1;

-- 테이블의 제거
drop table orders2 purge; --purge는 생략 가능
-- purge가 없이 삭제된 테이블은 나중에 휴지통과 같이 복구가 가능
-- purge를 사용하면 완전 삭제

-- purge를 쓰지 않고 삭제한 테이블의 복구
-- 양식: flashback table 테이블명 to before drop;
-- 삭제된 정보들의 조회
select*from recyclebin;

---

VIEW

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

-가상이란 단어는 실질적으로 데이터를 저장하고 있지 않기 때문에 붙인 것이고, 테이블이란 단어는 실질적으로 데이터를 저장하고 있지 않더라도 사용 계정자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문에 붙인 것

-뷰는 기본테이블에서 파생된 객체로서 기본테이블에 대한 하나의 쿼리문

-실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 함

-사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용할 수 있음

-뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록함

-뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 되는데 이 테이블은 기본테이블이라고 함. 두개 이상의 테이블 또는 한 개의 테이블이나 또 다른 뷰를 참조하는 객체

-저장된 테이블이라기 보다 공식 또는 select 문을 갖고 있다가 명령대로 불러와 그때 그때 구성하는 형식

-원본의 데이터 변화가 실시간으로 반영됨

creat or replace view 뷰이름 as(select구문)
--select 명령: 실제 테이블의 부분집합
--             (행 일부 또는 전부, 열 일부 또는 전부)

 

---

 

SEQUENCE

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

-테이블 생성 후 시쿼스(일련번호)를 따로 만들어야 함

-주로 number 형식에 기본키값으로 사용함

-일련번호정도로 이해해도 무방함

-number(자리수): 자료형의 자리수가 몇자리냐에 따라 그만큼 숫자가 증가할 수 있음

 

1. 시퀀스 생성

 create sequence 시퀀스이름 start with 시작숫자 increment by 증가량;

create sequence member_seq start with 1 increment by 1;

insert into memberlist (num, name, phone) 
values(member_seq.nextVal, '홍길동', '010-2222-3333');
insert into memberlist (num, name, phone) 
values(member_seq.nextVal, '홍길남', '010-5555-6666');
insert into memberlist (num, name, phone) 
values(member_seq.nextVal, '홍길서', '010-7777-8888');

 

2. 현재 시퀀스가 어디까지 증가되어져 있는지 확인

select member_seq.currval, member_seq.nextVal from dual;

 

3. 시퀀스 수정: 최대 증가값을 14까지로 제한

alter sequence member_seq maxvalue 14;

 

4. 시퀀스 삭제

-지나간 숫자를 꼭 쓰고 싶다면 시퀀스를 삭제하고 새로 만들 때 그 번호부터 시작해야함

drop sequence member_seq;

 

 

연습해보기

-- 1부터 1씩 늘어나는 book_seq, rent_seq를 생성해줌
create sequence book_seq start with 1 increment by 1;
create sequence rent_seq start with 1 increment by 1;

-- member_seq 를 삭제했다가 4부터 늘어나도록 다시 생성해줌
drop sequence member_seq;
create sequence member_seq start with 4 increment by 1;