DDL
오브젝트(객체) 종류
- 테이블 : 데이터를 저장
- 인덱스 : 질의의 효율성을 높인다.
- 뷰 : 하나 이상의 테이블에 있는 데이터의 부분집합이다.
- 시퀀스 : 기본 키 값을 생성한다.
- 시노님 : 객체에 다른 이름을 제공한다.
오브젝트 관련 DDL
CREATE : 데이터베이스 내의 모든 객체를 생성
ALTER : 이미 생성된 객체의 구조를 변경
DROP : 생성되어 있는 객체를 삭제
RENAME : 이미 생성한 객체의 이름을 변경(개명의 개념)
COMMENT : 객체이름, 컬럼에 대한 설명을 데이터베이스 내에 저장
TRUNCATE : 테이블에 저장되어 있는 모든 행을 삭제(테이블 초기화)
데이터유형
- VARCHAR2(SIZE) 가변길이 문자데이터
- CHAR(SIZE) 고정길이 문자데이터
- NUMBER(p,s) 가변길이 숫자데이터
- DATE 날짜 및 시간값
- CLOB 최대 4GB의 문자데이터
- LONG 최대 2GB의 가변 길이 문자데이터
- RAW / LONG ROW 원시 이진데이터
- BLOB 최대 4GB의 이진데이터
- BFILE 외부 파일에 저장된 이진데이터 (최대 4GB)
- ROWID 테이블에서 행의 고유주소를 나타내는 64진수
예 ) HAPPY란 문자열을 varchar2(10)인 컬럼과 char(10)인 컬럼에 저장한 경우
CHAR(10) 'HAPPY ' >>공백 有
VHARCHAR(10) 'HAPPY'데이터 딕셔너리
- ORACLE SERVER가 생성 및 유지관리하는 테이블의 COLLECTION.
- 데이터베이스 정보를 포함.
USER_XXX : 내가 소유한 객체들의 정보
ALL_XXX : 내가 접근할 수 있는 객체들의 정보 (대표적으로 DUAL테이블)
DBA_XXX : DBA ROLE로 접근
CREATE TABLE구문
CREATE TABLE [schema.]table_name
( column datatype [DEFAULT …],
[, column datatype . . .]
) ;
CREATE TABLE emp
(empid number(4)
,ename varchar2(10)
,sal number(7) default 100
,hiredate date default sysdate) ;제약조건
제약조건의 유형
- NOT NULL : 해당 열에 NULL값 허용X
- UNIQUE : 해당 열에 중복값 허용 X
- PRIMARY KEY : 테이블의 각 행을 고유하게 식별하는 컬럼 또는 컬럼 집합
- FOREIGN KEY : 동일한 테이블이나 다른 테이블에 있는 기본 키 또는 고유 키 열과의 참조 관계를 설정
- CHECK : 각 행이 만족시켜야 하는 조건을 정의.
CURRVAL, NEXTVAL, LEVEL 및 ROWNUM의사 열 참조 불가능.
SYSDATE, UID, USER 및 USERENV 함수 호출 사용 불가능.
다른 행의 값을 참조하는 질의 사용 불가능.
컬럼레벨
제약조건을 컬럼과 함께 정의
(id NUMBER(5)
CONSTRAINT sawon_id_pk PRIMARY KEY
, 컬럼2 데이터타입
CONSTRAINT 제약조건 )
CONSTRAINT 제약조건명은 유일해야 하므로,
테이블명_컬럼명_PK ( | UK | FK | CK | NN)
으로 부여하는것이 일반적.
제약조건명은 생략 가능. (생략할 경우 SYS_C number 로 자동생성)
NOTNULL 제약조건은 반드시 컬럼레벨로만 정의 가능.
테이블레벨
제약조건을 컬럼과 분리하여 정의
복합키 선언시 사용
(id NUMBER(5)
,컬럼2 데이터타입
,컬럼3 데이터타입
, ...
,
CONSTRAINT sawon_id_pk PRIMARY KEY(id)
,CONSTRAIN sawon_id_pk PRIMARY KEY (DEPT_ID, ID) --복합키
,CONSTRAINT 제약조건명2 제약조건(컬럼2)
)
--SAWON 테이블 생성
CREATE TABLE sawon
(id NUMBER(5)
CONSTRAINT sawon_id_pk PRIMARY KEY, --PK
name VARCHAR2(10)
CONSTRAINT sawon_name_nn NOT NULL, --NOT NULL
salary NUMBER(7,2) default 200,
phone VARCHAR2(12)
CONSTRAINT sawon_phone_uk UNIQUE --UNIQUE
CONSTRAINT sawon_phone_ck CHECK --CHECK
(phone LIKE '3429%'),
dept_id NUMBER(2)
CONSTRAINT sawon_dept_id_fk --FK
REFERENCES dept(deptno));
--TEST
insert into sawon(id) values(1);
insert into sawon(id, name) values(1, '홍길동');
-->>SAL은 DEFAULT값인 200으로 저장,
--PHONE, DEPT_ID = NULL
insert into sawon(id, name) values(1, '김철수');
-->> 앞에 입력한 홍길동 사원의 id와 동일.
-->> 기본키 무결성 조건(HR.SAWON_ID_PK)에 위배되어 오류 발생
update sawon
set phone = '3426-1234' where id = 1;
-->>PHONE은 3429%로 시작되어야하는 체크 제약조건(HR.SAWON_PHONE_CK)에 위배되어 오류 발생
update sawon
set phone = '3429-1234' where id = 1;
-->>정상적으로 저장
update sawon
set dept_id = 99 where id = 1;
-->> DEPTNO = 99에 해당하는 부모키가 없어 무결성 제약조건(DEPT_ID_FK)에 위배되어 오류 발생
insert into dept(deptno, dname) values(99,'연습');
-->> 부모키에 DEPTNO=99 INSERT
update sawon
set dept_id = 99 where id = 1;
-->> 정상적으로 저장
delete from dept where deptno = 99;
-->> DEPT=99 부모키 삭제하려고 하면 무결성 제약조건 위배로 오류 발생
update sawon
set dept_id = 10 where id = 1;
-->> 자식키 DEPT_ID = 99를 DEPT_ID = 10으로 변경
delete from dept where deptno = 99;
-->> 부모키 DEPTNO=99 정상적으로 삭제ON DELETE CASCADE
--SAWON 테이블 생성
CREATE TABLE sawon
(id NUMBER(5)
CONSTRAINT sawon_id_pk PRIMARY KEY,
name VARCHAR2(10)
CONSTRAINT sawon_name_nn NOT NULL,
salary NUMBER(7,2) default 200,
phone VARCHAR2(12)
CONSTRAINT sawon_phone_uk UNIQUE
CONSTRAINT sawon_phone_ck CHECK
(phone LIKE '3429%'),
dept_id NUMBER(2)
CONSTRAINT sawon_dept_id_fk
REFERENCES dept(deptno) on delete cascade);
insert into dept(deptno, dname) values(99,'연습');
insert into sawon(id, name, dept_id) values(1, '홍길동',99);
insert into sawon(id, name, dept_id) values(2, '홍길서',99);
insert into sawon(id, name, dept_id) values(3, '홍길남',99);
insert into sawon(id, name, dept_id) values(4, '홍길북',99);
-->> SALARY는 DEFAULT값인 200, PHONE = NULL인 4개의 행 INSERT
delete from dept where deptno = 99;
-->>부모키인 DEPTNO = 99를 삭제
-->> 자식키도 함께 삭제됨 (참조 무결성)ON DELETE SET NULL
CREATE TABLE sawon
(id NUMBER(5)
CONSTRAINT sawon_id_pk PRIMARY KEY,
name VARCHAR2(10)
CONSTRAINT sawon_name_nn NOT NULL,
salary NUMBER(7,2) default 200,
phone VARCHAR2(12)
CONSTRAINT sawon_phone_uk UNIQUE
CONSTRAINT sawon_phone_ck CHECK
(phone LIKE '3429%'),
dept_id NUMBER(2)
CONSTRAINT sawon_dept_id_fk
REFERENCES dept(deptno) on delete set null);
insert into dept(deptno, dname) values(99,'연습');
insert into sawon(id, name, dept_id) values(1, '홍길동',99);
insert into sawon(id, name, dept_id) values(2, '홍길서',99);
insert into sawon(id, name, dept_id) values(3, '홍길남',99);
insert into sawon(id, name, dept_id) values(4, '홍길북',99);
-->> PHONE, DEPT_ID가 NULL 인 네개의 행 INSERT
delete from dept where deptno = 99;
-->> 부모키인 DEPTNO = 99 삭제
-->> DEPTNO = 99를 참조하는 자식키는 NULL값으로 초기화
insert into sawon(id, name, salary)
values(5,'백장미', null);
-->> SALARY를 NULL로 명시하면 DEFAULT값인 200이 아닌 NULL로 저장제약조건 추가
테이블 레벨에서의 추가. (NOT NULL 제약조건은 반드시 컬럼과 함께 정의해야하므로, 이 방법으로는 추가할 수 없다)
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column) ;
제약조건 삭제
NOT NULL 제약조건은 삭제 불가능
ALTER TABLE table
DROP CONSTRAINT constraint [CASCADE] ;
ALTER
- 새로운 컬럼 추가
- 기존 컬럼의 수정
: 데이터 사이즈 수정 (해당 컬럼이 모두 NULL일 경우 사이즈 축소 가능, 하나라도 데이터가 있을 경우는 사이즈 증가만 가능)
: 해당 컬럼이 모두 NULL일 경우, 데이터 타입 변경 가능
: NOT NULL 제약조건 추가 - 열 삭제
- UNUSED 컬럼 지정
: 아직 운영중일 때 - UNUSED 컬럼 삭제
: 운영이 끝난 후 최종적으로 삭제
컬럼 추가(ADD)
ALTER TABLE EMP_CP ADD(CONSTRAINT EMP_CP_ID_PK PRIMARY KEY(ID));
컬럼 변경(MODIFY)
ALTER TABLE EMP_CP MODIFY NAME NOT NULL;
TRAUNCATE TABLE
- 테이블에서 모든 행 제거
- 해당 테이블이 사용하는 저장공간 해제(초기화)
- TRUNCATE 명령을 사용한 행 제거 작업은 롤백 할 수 없다.
DROP TABLE
- 테이블의 모든 데이터 및 구조를 삭제
- 보류중인 트랜잭션을 모두 커밋
- 인덱스를 모두 삭제
- DROP TABLE 문은 롤백 할 수 없다.
테이블 이름 변경 / 주석 추가
테이블, 뷰, 시퀀스, 시노님의 이름을 변경하려면 RENAME 명령을 사용
RENAME old_name TO new_name
COMMENT 문을 사용하여 테이블 또는 열에 주석을 추가
COMMENT ON TABLE
table | COLUMN table.column
IS ‘text’ ;
