[ Oracle sql ] 06. 데이터베이스 DDL(데이터 정의어)

1. 데이터베이스

종류	관련	의미
DDL (Data Definition Language) 데이터 정의어	객체 관련	CREATE 객체 생성
		ALTER 객체 수정
		DROP 객체 삭제
DML (Data Manipulation Language) 데이터 조작어	데이터 관련	INSERT 데이터 삽입
		UPDATE 데이터 수정
		DELETE 데이터 삭제
		SELECT(DQL) 데이터 조회
TCL (Transaction Control Language) 트랜잭션 제어	트랜잭션 관련	COMMIT 데이터 확정
		ROLLBACK 데이터 취소 (최근 COMMIT시점으로 되돌림)

 

2. 데이터 타입

종류	데이터 타입	내용
문자	CHAR	고정 문자열 자료형 (최대 2000Byte)
	VARCHAR2	가변 문자열 자료형 (최대 4000Byte)
	NVARCHAR	문자 개수 자체를 길이로 계산하는 자료형 -> 유니코드에 적합
	LOB(CLOB/BLOB)
숫자	NUMBER(P,S)	P : 최대 자리수 / S : 소수점 자리수
	NUMBER	최대 40자리까지 가능
	NUMBER(크기)	크기만큼 길이 지정 가능
날짜	DATE
	TIMESTAMP	시간까지 표시

 

3. CREATE 테이블 만들기

CREATE TABLE 테이블 이름(
    컬럼명 데이터타입(크기)
);

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20)
    );

 

(1). 컬럼에 주석 달기

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원 아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원 비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원 이름';

 

(2). 제약조건(CONSTRAINTS) : 테이블 작성 시 각 컬럼마다 값 기록에 대한 제약 조건 설정 가능

- 데이터 무결성 보장 목적 : 데이터의 정확성, 일관성, 유효성이 유지되는 것을 의미

- 입력 데이터에 문제가 없는지 검사하는 목적

제약조건 종류	의미
NOT NULL	데이터에 NULL 허용 X
UNIQUE	중복된 값을 허용하지 X
PRIMARY KEY	NOT NULL + UNIQUE *고유 식별자* 한 테이블 당 한 개만 설정 가능
FOREIGN KEY	외부 테이블에서 참조해 온 컬럼 값이 존재하면 허용
CHECK	데이터 값의 범위나 조건을 지정해 설정한 값만 허용

 

(2)-1. 제약조건 설정 방법

① 컬럼 레벨 : 컬럼 옆에 제약조건 설정

② 테이블 레벨 : 컬럼 아래에다 제약조건 설정 NOT NULL 제약 조건은 테이블 레벨에서 설정 불가능

ex) UNIQUE(USER_NO, USER_ID) 한 번에 설정 시 값을 쌍으로 보게 됨.

 

(2)-2. 제약조건 이름 설정 (같은 이름은 불가능)

CONSTRAINT 이용하여 이름을 설정한다.

CREATE TABLE CONS_NAME(
    TEST_DATA1 VARCHAR2(20) CONSTRAINT CN_TD1_NN NOT NULL,
    TEST_DATA2 VARCHAR2(20) CONSTRAINT CN_TD2_UQ UNIQUE,
    TEST_DATA3 VARCHAR2(20),
    CONSTRAINT CN_TD3_UK UNIQUE(TEST_DATA3)
    );

 

(3). 외래키 (FOREIGN KEY)

- 참조하는 테이블(부모 테이블)의 참조 컬럼 값 외에 NULL 값도 가능하다.

- FOREIGN KEY(참조하여 작성 할 자식 개체) REFERENCES 참조해올 값이 있는 테이블명(참조해 올 컬럼명)

CONSTRAINT UF_GC_FK FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE(GRADE_CODE)

 

(4). 삭제 옵션

자식데이터가 부모데이터를 참조했을 경우, 부모데이터는 그냥 삭제가 불가능하다. 그렇기 때문에 삭제 옵션을 넣어준다.

① ON DELETE SET NULL : 부모 키 삭제 시 자식 키를 NULL로 변경해주는 옵션

② ON DELETE CASCADE : 부모 키 삭제 시 자식 키도 함께 삭제하는 옵션

 

(5). CHECK

데이터의 값이나 범위를 지정하여 저장하게끔 할 수 있다.

GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')) -- 남/여 둘 중 하나의 값만 가능

CONSTRAINT UC2_TN_CK CHECK(TEST_NUMBER > 0) -- 양수의 값만 가능

 

(6). 서브쿼리(SUBQUARY)를 이용한 테이블 작성 방법

CREATE TABLE EMPLOYEE_COPY2
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
    FROM EMPLOYEE
        LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
        JOIN JOB USING(JOB_CODE);

 

 

 

4. ALTER 객체 수정

-- 컬럼 추가/삭제, 제약조건 추가/삭제, 컬럼 자료형 변경, DEFAULT값 변경, 테이블 명/컬럼 명/제약조건 명 변경

(1) 컬럼 추가

ALTER TABLE 테이블 명 ADD (추가할 컬럼 명 자료형(크기));

 

DEFAULT를 이용하여 데이터를 입력하지 않았을 때 NULL 값 대신 들어가 있을 값 설정 가능

ALTER TABLE DEPT_COPY
ADD (LNAME VARCHAR2(40) DEFAULT '한국');

 

= LNAME에 NULL 대신 한국이라는 값 삽입

(2) 컬럼 수정

ALTER TABLE 테이블 명 MODIFY 수정할 컬럼 명 자료형(크기);

* 자료 크기도 줄일 수 있지만 안에 있는 데이터의 크기를 고려하여 크기를 줄여야한다.

 

(3) 컬럼 삭제 (데이터가 들어있어도 삭제가 가능하다)

ALTER TABLE 삭제할 테이블 명 DROP COLUMN 삭제할 칼럼 명;

 

*ROLLBACK을 해도 삭제된 데이터는 돌아오지 않는다. (CREATE, ALTER, DROP은 롤백 불가능)

** 모든 컬럼을 다 삭제할 수는 없다. 테이블에는 최소 1개의 컬럼이 남아있어야한다.

 

(4) 제약 조건 추가

ALTER TABLE USER_FOREIGNKEY4 ADD UNIQUE(USER_ID);
ALTER TABLE USER_FOREIGNKEY4 MODIFY USER_PWD NOT NULL; -- NOT NULL 은 수정하는 방법이 다르다
ALTER TABLE USER_FOREIGNKEY4 ADD CHECK(GENDER IN ('남', '여'));
ALTER TABLE USER_FOREIGNKEY4 ADD FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE4(GRADE_CODE);

--방법1. 한줄로 따로따로
ALTER TABLE DEPT_COPY ADD CONSTRAINT DCOPY_DID_PK PRIMARY KEY(DEPT_ID);
ALTER TABLE DEPT_COPY ADD CONSTRAINT DCOPY_DTITLE_UQ UNIQUE(DEPT_TITLE);
ALTER TABLE DEPT_COPY MODIFY LNAME CONSTRAINT DCOPY_LNAME_NN NOT NULL;
--방법2. 한 번에 추가
ALTER TABLE DEPT_COPY ADD CONSTRAINT DCOPY_DID_PK PRIMARY KEY(DEPT_ID)
                    ADD CONSTRAINT DCOPY_DTITLE_UQ UNIQUE(DEPT_TITLE)
                    MODIFY LNAME CONSTRAINT DCOPY_LNAME_NN NOT NULL;

 

* 제약조건 NOT NULL 의 경우 ADD가 아닌 MODIFY를 이용하여 수정한다.

ALTER TABLE USER_FOREIGNKEY4 ADD FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE4;

 

** 내가 참조하는 테이블의 PRIMARY KEY를 가지고와서 참조를 한다면 () 생략 가능

 

(5) 제약 조건 삭제

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

 

ALTER TABLE DEPT_COPY
DROP CONSTRAINT DCOPY_DTITLE_UQ
DROP CONSTRAINT SYS_C007157
MODIFY LNAME NULL; --NOT NULL 제약조건 삭제하는 방법

 

 

(6) 이름 변경

-- 컬럼 이름 변경
ALTER TABLE DEPT_COPY
RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

SELECT * FROM DEPT_COPY;

--제약조건 이름 변경
ALTER TABLE DEPT_COPY
RENAME CONSTRAINT SYS_C007158 TO DCOPY_LID_NN;

--테이블 이름 변경
ALTER TABLE DEPT_COPY
RENAME TO DEPT_TEST;

 

5. DROP 객체 삭제

DROP TABLE USER_FOREIGNKEY2 CASCADE CONSTRAINTS; -- 제약조건까지 함께 삭제