[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)
);
테이블 수정(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;
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;