DDL(Data Definition Language)

데이터 정의 언어로 객체를 만들고, 수정하고, 삭제하는 구문이다.

롤백 할 수 없다.

오라클 객체 종류 :

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

1. CREATE 구문

테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문

테이블 생성하기 :: CREATE TABLE

@ 생성 방법
CREATE TABLE 테이블명(
	컬럼명 타입 [제약조건1 제약조건2 ...],
	컬럼명 타입 [제약조건]....
)

컬럼(각 저장소)는 타입이 있음

오라클 데이터형

문자: CHAR, VARCHER2, NVARCHAR2, LONG, CLOB
날짜는 문자형태로 저장하거나 날짜 형태로 입력하면 자동으로 형변환되어 날짜로 저장 된다.
- 하지만, 형변환해서 날짜 형태로 입력해주는 것이 좋다. (나중에 틀어짐)
지정 크기 벗어나면 에러나기 때문에 최대치를 보고서 크기를 설정해야함(나중에 수정 가능)

데이터형 설명 (크기 벗어나면 저장 안됨)

데이터형	설명
문자
CHAR	고정길이 문자 데이터
VARCHAR2	가변길이 문자 데이터(최대 4,000 Byte)
NVARCHAR2	가변길이 문자 데이터(좀더 확장적인 문자열 저장 가능)
엄청 큰 문자
LONG	가변길이 문자형 데이터(최대 2기가)
CLOB TO_CLOB() 같이 활용	가변길이 문자 데이터 저장 가능(최대 2기가) - character 글자가 너무 많으면 에러가 뜰수 있는데 해당 메소드로 묶어주면 정상 작동 가능하다.
숫자
NUMBER NUMBER(길이, 소수점)	숫자(최대 40미리) 길이지정숫자(최대 38자리) - PRECISION: 전체 중 실수까지도 설정(1~38) ⇒ 정수 입력시 정수, 실수 입력시 실수 - SCALE: 전체 중 소수점 자리수 설정(-84~127) ⇒ 기본적으로 반올림, 음수면 갯수만큼
날짜
DATE	년, 월
TIMESTAMP	년, 월, 일, 시, 분, 초
INTERVAL YEAR TO MONTH	년과 월을 이용하여 기간 저장
INTERVAL DAY TO SECOND	일, 시, 분, 초를 이용하여 기간 저장

타입별 예시

@ 문자
CREATE table TBL_STR(
 A CHAR(6), --6바이트만큼 저장하는 문자 칼럼
 B VARCHAR2(6),
 C NVARCHAR2(6) -- 그냥 이거 쓰면 됨
);
select * from tbl_str;
insert into tbl_str values('ABC', 'ABC', 'ABC');
insert into tbl_str values('ABCDEFG', 'ABCDEF', 'ABCDEF'); -- 에러 발생
insert into tbl_str values('가나', '오나', '자나');
insert into tbl_str values('가나다', '오나다', '자나다'); -- NVARCHAR2(2바이트)를 제외하면 한글은 글자당 3바이트로 계산되서 에러 발생
insert into tbl_str values('가나', '오나', '자나다라바마'); 

select lengthb(a), lengthb(b), lengthb(c)
from tbl_str;

6	3	6
6	6	4
6	6	6
6	6	12

@ 숫자
create table tbl_num(
 A number,
 B number(5), -- 정수만 5개 담겠다는 뜻
 C number(5, 1), -- 소수점 1자리까지 담겠다는 뜻으로 5칸중에 1자리는 소수점이 차지한다.
 D number(5, -2) -- 음수 갯수만큼 자리수를 삭제해서 0으로 대체(일, 십자리수는 00으로)
);
select * from tbl_num;
insert into tbl_num values(1234.567, 1234.567, 1234.567, 1234.567);
insert into tbl_num values(1234.123, 1234.123, 1234.123, 1234.123);
insert into tbl_num values(123400.12300, 12340.123, 1234.123, 123400.123);

@ 날짜
create table tbl_date(
 BIRTHDAY date,
 RENT_START timestamp
);
select * from tbl_date;
insert into tbl_date values(sysdate, systimestamp);
-- 1. 문자저장
insert into tbl_date values('24/02/13', '24/02/13 09:00');
--- 2. 형변환해서 저장하기 : 위와 결과는 같아보이지만 좀더 정확한 데이터로 작동할 수 있음.
insert into tbl_date values(to_date('24/02/13', 'YY/MM/DD'), 
                    to_timestamp('24/02/13 09:00', 'RR/MM/DD HH24:MI'))

2. 제약 조건(CONSTRAINTS)

데이터 무결성 보장을 위해 테이블의 컬럼에 기록될 데이터에 제약 조건을 설정할 수 있다.

입력 데이터에 문제가 없는지 검사, 데이터 수정/삭제 가능 여부 검사 등에 사용된다.

1) 제약 조건의 종류

들어올 데이터 종류에 따라서 제약 조건을 설정한다.

나중에 설명될 이야기지만 제약 조건을 설정하면 제약조건의 이름이 붙게 된다

제약 조건(표기명)	설명	비고
NOT NULL(C)	NULL 값 금지 (컬럼 레벨에서만 설정 가능)	- 기본적인 칼럼값으로는 NULL able(null값 입력 가능) 상태이다
UNIQUE(U)	중복값 금지(NULL은 가능) (컬럼 레벨 O, 테이블 레벨 O)	- 컬럼: 주민번호, 계좌번호 등 - 테이블: (아이디, 상품번호)
PRIMARY KEY (P)	NULL 금지 + 중복값 금지 (둘다 가능하지만, 일반적으로 컬럼 레벨 설정 사용) 1. 실사용 컬럼에 설정 2. 무의미 컬럼을 생성해서 넘버링 설정(대부분)	컬럼의 고유 식별자로 사용하기 위함 → row 구분 - 여러 컬럼을 묶어서 테이블 레벨에서 선언하는 복합키는 되도록 안쓰는게 좋다(복잡해짐)
FOREIGN KEY(R)	참조되는 테이블(외부에서 가져오는) 컬럼에 이미 있는 값만 허용 (컬럼 레벨 O, 테이블 레벨 O-한개만 가능-) 칼럼 타입 references 참조할테이블(칼럼)	- 참조관계를 설정할 때 사용 (참조하는 테이블(자식)에 설정) ⇒ 참조되는 테이블(부모)에서 선택된 컬럼은 pk or unique제약 조건이 설정되어있어야한다. 🚨 fk 제약조건이 설정된 부모테이블과 그 데이터는 함부로 삭제할 수 없다. ⇒ 삭제에 대한 옵션 설정 가능
CHECK(C)	저장 가능한 데이터 값의 범위나 조건을 지정하고 설정값만 허용 (컬럼 레벨 O)

💡FK 삭제 옵션 설정하기

(1) ON DELETE SET NULL

참조값이 삭제되면 null로 설정

단, 컬럼에 not null 제약 있으면 불가능

참조값이 사라져도 활용가치가 남아있는 테이블

예) 탈퇴 회원의 구매내역(상품 등)

(2) ON DELETE CASCADE

참조값이 삭제되면 같이 삭제

참조값이 사라지면 활용가치가 없어지는 테이블의 경우 사용

예) 퇴자사의 가족정보 테이블, 삭제된 게시글의 댓글 테이블

@ 선언은 접근제한자처럼 컬럼 행에 넣어주면 된다.
create table fk_board_comment(
    comment_no number primary key,
    comment_content varchar2(1000) not null,
    comment_writer varchar2(20) references uq_member(member_id),
--    board_no number references fk_board(board_no) on delete set null, 혹은
    board_no number references fk_board(board_no) on delete cascade,
    comment_date date
);

💯 제약조건별 예시

FOREIGN 예시

-- 댓글테이블 구성해보기: 댓글번호, 내용, 작성자(FK), 게시글번호(FK), 작성일
create table fk_board_comment(
    comment_no number primary key,
    comment_content varchar2(1000) not null,
    comment_writer varchar2(20) references uq_member(member_id),
    board_no number references fk_board(board_no),
    commnet_date date
);
insert into fk_board_comment values(1, 'MJ 두끼 힘내요! 아랫배?', 'GDJ79', 1, sysdate);
insert into fk_board_comment values(2, '헌수 MJ 그만 눌러! 아랫배?', 'user01', 1, sysdate);
select * from fk_board_comment;

-- 게시글 1의 작성자와 댓글 가져오기
select *
from fk_board join uq_member on board_writer = member_id
             left join fk_board_comment using(board_no) -- 선택관계니까 outer join
where board_no = '2';

-- fk 제약조건이 설정된 부모테이블의 데이터는 함부로 삭제할 수 없다.
delete from fk_board where board_no = 1; -- 부모테이블 데이터 삭제//자식 레코드가 발견되었다는 에러 발생.
drop table fk_board; -- 부모테이블 삭제//외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다

-- 댓글테이블 구성해보기: 댓글번호, 내용, 작성자(FK), 게시글번호(FK), 작성일
create table fk_board_comment(
    comment_no number primary key,
    comment_content varchar2(1000) not null,
    comment_writer varchar2(20) references uq_member(member_id),
    board_no number references fk_board(board_no),
    commnet_date date
);
insert into fk_board_comment values(1, 'MJ 두끼 힘내요! 아랫배?', 'GDJ79', 1, sysdate);
insert into fk_board_comment values(2, '헌수 MJ 그만 눌러! 아랫배?', 'user01', 1, sysdate);
select * from fk_board_comment;

-- 게시글 1의 작성자와 댓글 가져오기
select *
from fk_board join uq_member on board_writer = member_id
             left join fk_board_comment using(board_no) -- 선택관계니까 outer join
where board_no = '2';

-- fk 제약조건이 설정된 부모테이블의 데이터는 함부로 삭제할 수 없다.
delete from fk_board where board_no = 1; -- 부모테이블 데이터 삭제//자식 레코드가 발견되었다는 에러 발생.
drop table fk_board; -- 부모테이블 삭제//외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다

NOT NULL 예시

@ null이면 안되는 곳에 지정 -> 회원가입 아이디와 비번 등
-- not null
create table nn_member( -- member 테이블과 겹치면 안되니까 nn_
    member_no number,
    member_id varchar2(50) not null,
    member_pwd varchar2(50) not null,
    member_name varchar2(20),
    memeber_age number
-- not null(memeber_id) 테이블 레벨 설정은 불가능하다
); -- 제약조건 안쓰면 nullable상태로 null이 들어가진다.

UNIQUE 예시

@ 칼럼 레벨에서 설정
create table uq_member( 
    member_no number,
    member_id varchar2(50) unique,
    member_pwd varchar2(50) not null,
    member_name varchar2(20),
    memeber_age number
);
@ 테이블 레벨에서 설정
create table uq_member( 
    member_no number,
    member_id varchar2(50),
    member_pwd varchar2(50) not null,
    member_name varchar2(20),
    memeber_age number,
    unique(member_id, member_name) -- 하나의 그룹으로 묶어서 중복체크
    -- 장바구니, 구매내역에 활용되는 부분(아이디, 상품번호) -> 한 아이디에 동일 상품하나만 담기도록
);

PRIMARY KEY 예시

-- PRIMARY key 설정 방법 : 컬럼레벨, 테이블레벨 모두 가능
-- 컬럼레벨
create table pk_member( 
    member_no number **primary key**,
    member_id varchar2(50) **unique not null**,
    member_pwd varchar2(50) not null,
    member_name varchar2(20),
    memeber_age number
);
select* from pk_member;
insert into pk_member values(1, 'admin', '1234', '전승우', 29);
insert into pk_member values(2, 'user01', '1111', '최헌수', 23); -- not null 조건이 포함되어 있음

-- 테이블레벨(복합키 예시)
create table pk_member( 
    member_no number,
    member_id varchar2(50),
    member_pwd varchar2(50) not null,
    member_name varchar2(20),
    memeber_age number,
    **primary key(member_no, member_id)**
);
insert into pk_member values(2, 'admin', '1234', '전승우', 29); -- 복합키라서 저장 가능함.
insert into pk_member values(null, 'admin', '1234', '전승우', 29); -- 불가능함

CHECK 예시

@ 작성방법
CREATE TABLE PERSON(
    NAME VARCHAR2(20),
    AGE NUMBER CHECK (AGE > 0),
    GENDER VARCHAR2(5) **check(gender in ('M', 'F'))**
);

-- 게시판 항목을 check로 넣는 것도 가능하지만, 실제 확장성을 생각하면 2번 방법이 나음
create table test_board(
    type varchar2(10) check(type in('자유','공용', '비밀')), -- 1
    title varchar2(20)
    foreaign key(type) references board_type(type_no) -- 2
)
create table board_type( --2
    type_no number primary key,
    type_name varchar2(10)
);

2) 제약조건 설정 방법

(1) 컬럼 레벨에서 설정

CREATE로 생성시 한줄 마다 지정 → 컬럼 한개에 제약조건 설정할 때 사용한다.

(2) 테이블 레벨에서 설정

CREATE 마지막 문단에 설정 → 여러 컬럼을 묶어서 설정할 때 사용한다.

테이블 레벨 설정이 불가능한 조건도 있다.

3) default값 설정하기

모든 타입에 대해 default값을 설정할 수 있다.

CREATE TABLE DEFAULT_TEST(
    TEST_NO NUMBER PRIMARY KEY,
    TEST_DATE DATE DEFAULT SYSDATE NOT NULL, --설정 안하면 오늘이 들어가도록 설정
    TEST_DATA VARCHAR2(20) DEFAULT '기본값'
);
@ 디폴드값으로 바로 삽입도 가능하다.
INSERT INTO DEFAULT_TEST VALUES(1, DEFAULT, DEFAULT);

4) 제약조건 이름 설정하기

제약조건을 설정하지 않았을 때 스크립트에 표시되는 내용

명령의 213 행에서 시작하는 중 오류 발생 -
INSERT INTO PERSON VALUES('이름', -2, '남')
오류 보고 -
ORA-02290: 체크 제약조건(SQL.**SYS_C008389**)이 위배되었습니다

설정방법 :: CONSTRAINT 제약조건명

타입과 제약조건 사이에 설정
테이블 레벨에서 설정

네이밍 규칙을 만들어 통일하는 편이 좋다.

조건명은 보통 변수명과 제약조건 내용을 쉽게 유추할 수 있게 짓는다.

CREATE TABLE MEMBER_TEST(
    MEMBER_NO NUMBER CONSTRAINT MEMBER_NO_PK PRIMARY KEY,
    MEMBER_ID VARCHAR2(20) CONSTRAINT MEMBER_ID_UQ UNIQUE CONSTRAINT NN_MEMBER_ID NOT NULL,
    MEMBER_AGE NUMBER CONSTRAINT CK_AGE CHECK(MEMBER_AGE >0),
    BOARD_NO NUMBER,
    CONSTRAINT FK_BOARD_NO FOREIGN KEY(BOARD_NO) REFERENCES FK_BOARD(BOARD_NO)
);

5) 제약조건들 확인하기(제약조건 조회)

오라클이 제공하는 USER_CONSTRAINTS, USER_CONS_COLUMNS테이블 이용

USER_CONSTRAINTS → 컬럼 정보는 포함되어있지 않음
USER_CONS_COLUMNS → 테이블명이 포함되어있지 않음

⇒ 둘을 join해서 조회가 가능하다. (중복되는 컬럼들이 많으므로 별칭 사용)

SELECT UC.CONSTRAINT_NAME, UC.TABLE_NAME, CONSTRAINT_TYPE, COLUMN_NAME
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.TABLE_NAME = 'member_test'; -- 조회 원하는 테이블을 조건으로 추가해서 조회 가능

💡테이블 간 관계 확인하기

서버의 테이블 카테고리에서 원하는 테이블을 선택한 뒤, model 코너를 확인하면 테이블 간 관계를 아래와 같은 화면처럼 확인할 수 있다.

3. COMMNET 작성

협업시 나 이외의 다른 사람이 이해하고 쓸 수 있게 코멘트 등을 이용한다. (협업을 더 쉽게 해준다)

1) 칼럼 코멘트

작성 : comemt on cloumn 테이블명.컬럼명 is '내용';

comment on column member_test.member_no is '회원번호 PRIMARY KEY';
comment on column member_test.member_id is '회원아이디 8글자이상 영문';

select *
from USER_COL_COMMENTS
where table_name = 'MEMBER_TEST'; --대문자로 적어야함

조회 : 오라클이 제공하는 USER_COL_COMMENTS 테이블 이용

select *
from USER_COL_COMMENTS
where table_name = 'MEMBER_TEST'; --대문자로 적어야함

2) 테이블 코멘트

작성 : comment on table 테이블명 is '내용';
comment on table member_test is '제약조건 연습테이블'; select * from USER_TAB_COMMENTS;
조회 : 오라클이 제공하는 USER_TAB_COMMENTS 테이블 이용
select * from USER_TAB_COMMENTS where table_name ='MEMBER_TEST';

4. 테이블 복사하기

기존 테이블을 복사해서 생성할 수 있다.

단, 사본은 제약조건이 not null 제약조건만 복사되므로 다시 설정을 해줘야한다.

create table copy_emp --사본 생성 테이블명
as select * from employee; --복사 대상 테이블명

원하는 칼럼만 선택복사하기

선택한 칼럼은 가상 칼럼이 아닌 실제 데이터가 된다.

create table copy_emp2
as select emp_id, emp_name, salary * 12 as year_sal from employee;
select * from copy_emp2;

테이블 구조만 복사해오기

무조건 false값을 리턴하는 조건절을 입력해주면 빈 껍데기만 복사된다.

create table copy_emp3
as select * from employee
where 1 = 2;
select * from copy_emp3;

5. 테이블 수정 :: ALTER

컬럼, 제약조건, default값등 객체를 수정할 수 있는 구문

ALTER 객체(table,,,) 객체명 명령ADD,MODIFY… (컬럼명 타입 [제약조건])

1) 컬럼 추가

다음 예시와 같이 작성하여 기존 데이터에 컬럼을 추가할 수 있다.

@ 작성 방법
alter table tbl_useralter add(user_name varchar2(20));

desc tbl_useralter; -- 데이터 구조를 확인해보면 추가가 완료되었다.

단, 이미 있는 row의 해당 칼럼에는 Null값을 넣게되므로 not null 제약조건의 칼럼은 그냥 추가할 수 없고, 디폴트 값을 주어 null이 아닌 값을 넣어주면 된다

alter table tbl_useralter add(email varchar2(100) not null); 
    -- 테이블은 필수 열을 추가하기 위해 (NOT NULL) 비어 있어야 합니다.
alter table tbl_useralter add(email varchar2(100) default '미설정' not null); 
		-- Table TBL_USERALTER이(가) 변경되었습니다.

2) 컬럼(자료형, 길이) 수정

ALTER TABLE 테이블명 MODIFY 컬럼명 자료형(길이)

단, 기존 컬럼row 데이터의 자료형이 들어갈 수 있는 형태여야한다.

alter table tbl_useralter modify gender char(5); --타입변경 
alter table tbl_useralter modify user_name varchar2(100); --길이변경

3) 컬럼 삭제하기

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

alter table tbl_useralter drop column email;
alter table tbl_useralter drop column user_name;

4) 특정 컬럼의 제약조건 추가/수정/삭제

제약조건 추가하기
- 테이블 레벨에서 작성한 방식과 동일
- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(컬럼명)
- not null은 수정을 통해 진행해야한다.
제약조건 수정하기
- 이미 모든 컬럼은 nullable 조건을 갖고 있기 때문에 not null 제약조건은 수정해야한다.
- ALTER TABLE 테이블명 MODIFY CONSTRAINT 제약조건명 제약조건(컬럼명)
제약조건 삭제하기
- ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명 제약조건(컬럼명)

5) 컬럼명, 제약조건명, 테이블명 변경하기

컬럼명 변경
- ALTER TALBE 테이블명 RENAME COLUMN 이전컬럼명 TO 바꿀컬럼명;
제약조건명 변경
- ALTER TALBE 테이블명 RENAME CONSTRAINT 이전제약조건명 TO 바꿀제약조건명;
테이블명 변경
- ALTER TALBE 테이블명 RENAME TO 바꿀테이블명;

6. 삭제 :: DROP

데이터베이스 객체를 삭제하는 구문

테이블 삭제

DROP TABLE 테이블명;

참조관계에서 부모테이블은 함부로 삭제할 수 없다
→ 관계를 끊는 구문을 같이 적어주면 삭제할 수 있다. (DROP TABLE 테이블명 CASCADE CONSTRAINTS; )

저작자표시 비영리 변경금지

'Oracle DataBase' 카테고리의 다른 글

9. ORACLE :: DCL 구문(사용 권한 구문) 정리, 요약 (2)	2024.06.12
8. ORACLE :: DML구문(insert, upadate, delete) 정리 (0)	2024.06.12
6. ORACLE 정리 :: 서브쿼리(SUBQUERY) - 단일행, 다중행, 다중열, 상관, 스칼라 (2)	2024.06.04
5. ORACLE 정리 :: 집합 연산자(UNION)와 JOIN문 (0)	2024.06.03
4. ORACLE 함수(Function) :: 그룹 함수 정리 (0)	2024.05.31

7. ORACLE 정리 :: DDL구문(create, alter, drop)

DDL(Data Definition Language)

1. CREATE 구문

테이블 생성하기 :: CREATE TABLE

2. 제약 조건(CONSTRAINTS)

1) 제약 조건의 종류

💡FK 삭제 옵션 설정하기

(1) ON DELETE SET NULL

(2) ON DELETE CASCADE

💯 제약조건별 예시

FOREIGN 예시

NOT NULL 예시

UNIQUE 예시

PRIMARY KEY 예시

CHECK 예시

2) 제약조건 설정 방법

(1) 컬럼 레벨에서 설정

(2) 테이블 레벨에서 설정

3) default값 설정하기

4) 제약조건 이름 설정하기

설정방법 :: CONSTRAINT 제약조건명

5) 제약조건들 확인하기(제약조건 조회)

💡테이블 간 관계 확인하기

3. COMMNET 작성

1) 칼럼 코멘트

2) 테이블 코멘트

4. 테이블 복사하기

원하는 칼럼만 선택복사하기

테이블 구조만 복사해오기

5. 테이블 수정 :: ALTER

1) 컬럼 추가

2) 컬럼(자료형, 길이) 수정

3) 컬럼 삭제하기

4) 특정 컬럼의 제약조건 추가/수정/삭제

5) 컬럼명, 제약조건명, 테이블명 변경하기

6. 삭제 :: DROP

테이블 삭제

'Oracle DataBase' 카테고리의 다른 글

티스토리툴바