[SQLD] 13. DDL (Data Defintion Language)

1. 데이터 유형

1. 테이블에 특정 자료를 입력할 때 그 공간을 자료의 유형별로 나누는 기준
2. 유형과 지정한 크기(SIZE)에 맞지 않는 자료 입력시 에러 발생
3. DBMS별로 데이터 유형은 차이가 많은 편이다.
   1. ANSI/ISO: NEMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, REAL, DOUBLE PRECISION
   2. SQL Server/Sybase: 작은 정수형, 정수형, 큰 정수형, 실수형, MONEY, SMALLMONEY
   3. Oracle: NUMBER
4. 테이블의 컬럼이 가지고 있는 대표적인 4가지 데이터 유형
   

   

   
   

   

   1. CHAR와 VARCHAR
      1. 저장 영역의 차이: VARCHAR는 길이가 다양한 컬럼과 정의된 길이와 실제 길이에 차이가 있는 컬럼에 적합
      2. 비교 방법의 차이: CHAR는 문자열 비교시 짧은 쪽의 공백을 추가하여 같은 길이로 만든다음 앞부터 비교
         

         

5. VARCHAR, NUMERIC 유형에서 정의한 길이의 의미는 해당 데이터 유형이 가질 수 있는 최대한의 한계값을 정의

한 것이다.

2. CREATE TABLE

가. 테이블과 컬럼 정의

1. 기본키 지정: 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 컬럼(들)
   1. 예: 선수 테이블의 선수ID 컬럼
2. 기본키와 외부키를 활용해서 테이블과 테이블간 관계 정의
3. 예제
   

   

나. CREATE TABLE

1. 규칙
   1. 테이블명은 객체를 의미하는 것으로 짓되 단수형을 권고한다.
   2. 테이블명은 유일해야 한다.
   3. 테이블 내의 컬럼명은 유일해야 한다.
   4. 각 컬럼은 콤마(,)로 구분되고 테이블 생성문의 마지막은 세미콜론(;)으로 끝난다.
      컬럼명은 데이터 표준화 관점에서 데이터베이스 단위로 일관성 있게 명하는 것이 좋다.
   5. 컬럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
   6. 테이블명과 컬럼명은 반드시 문자로 시작해야 하고 벤더별로 길이의 한계가 있다.
   7. 벤더에서 사전에 예약한 예약어는 사용할 수 없다.
   8. A-Z, a-z, 0-9, _, $, #만 허용된다.
   9. 대/소문자 구분은 하지 않는다.(기본적으로 대문자로 만들어진다.)
   10. 제약조건은 CONTRAINT 구문을 이용하여 추가한다.

예제(Oracle)

CREATE TABLE PLAYER(
    PLAYER_ID		CHAR(7) NOT NULL,
    PLAYER_NAME		VARCHAR2(20) NOT NULL,
    TEAM_ID	        CHAR(3) NOT NULL,
    E_PLAYER_NAME	VARCHAR2(40),
    NICKNAME		VARCHAR2(30),
    JOIN_YYYY		CHAR(4),
    POSITION		VARCHAR2(10),
    BACK_NO	        NUMBER(2),
    NATION	        VARCHAR(2),
    NATION	        VARCHAR2(20),
    BIRTH_DATE		DATE,
    SOLAR	        CHAR(1),
    HEIGHT	        NUMBER(3),
    WEIGHT              NUMBER(3),
    CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),
    CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
    CONSTRAINT PLAYER_UQ UNIQUE (BACK_NO)
);

다. 제약조건(CONSTRAINT)

1. 데이터의 무결성을 유지하기 위한 보편적인 방법
2. 테이블 생성시, 혹은 이후 ALTER TABLE을 통해 추가 가능
3. 제약조건의 종류
   

   

4. NULL: 공백이나 숫자와는 전혀 다른 값이며 '아직 정의되지 않은 미지의 값'이거나 '현재 데이터를 입력하지 못하는 경우'를 의미한다.
5. DEFAULT: 데이터 입력시 컬럼의 값이 지정되어 있지 않을때 입력될 기본값(지정하지 않으면 NULL이 입력된다.)

라. 생성된 테이블 구조 확인

• ORACLE의 경우

• SQL Server의 경우
  

  

마. SELECT 문장을 통한 테이블 생성 사례

1. 구문
   1. Oracle: Create Table ~ As Select~(CTAS)
      

      

   2. SQL Server: Select~ Into~
      

      

2. 특징
   1. 기존 테이블을 이용한 CTAS를 사용하면 컬럼별로 데이터 유형을 재정의 하지 않아도 된다.
   2. NOT NULL 제약조건만 새로운 복제 테이블에 적용되며 다른 제약조건은 없어진다.
   3. SQL Server는 컬럼 속성에 Identity를 사용했다면 그 속성까지 적용된다.

3. ALTER TABLE

가. ADD COLUMN

1. 기존 테이블에 필요한 컬럼을 추가하는 명령
2. 새롭게 추가된 컬럼은 테이블의 마지막 컬럼이 되며 위치를 지정할 수는 없다.
3. 예제
   

   

   
   

   

나. DROP COLUMN

1. 테이블에서 필요없는 컬럼을 삭제하는 명령
2. 데이터가 있거나 없거나 모두 삭제 가능
3. 한번에 하나의 컬럼만 삭제 가능하며 컬럼 삭제후 테이블에 최하 하나 이상의 컬럼이 존재해야 한다.
4. 한번 삭제된 컬럼은 복구가 불가능하니 주의한다.
5. 예제
   

   

다. MODIFY COLUMN

1. 테이블에 존재하는 컬럼에 대해서 데이터 유형, 디폴트값, NOT NULL 제약조건에 대한 변경을 한다.
2. 고려사항
   1. 컬럼의 크기를 늘릴 수는 있지만 줄일 수는 없다.
   2. 해당 컬럼이 NULL 값만 가지고 있거나 아무 행도 없으면 컬럼을 줄일 수 있다.
   3. 해당 컬럼이 NULL 값만 가지고 있으면 데이터 유형을 변경할 수 있다.
   4. 해당 컬럼의 DEFUALT 값을 바꾸면 변경 작업 이후 발생하는 행에만 적용된다.
   5. 해당 컬럼에 NULL 값이 없을 때에만 NOT NULL 제약조건을 추가할 수 있다.
3. 예제
   1. TEAM 테이블의 ORIG_YYYY 칼럼의 데이터 유형을 CHAR(4)→VARCHAR2(8)으로 변경하고， 향후 입력
      되는 데이터의 DEFAULT 값으로 '20020129'을 적용하고， 모든 행의 ORIG 칼럼에 NULL이 없으
      므로 제약조건을 NULL -> NOT NULL로 변경한다.
   2. 구문
      

      

라. RENAME COLUMN

1. 컬럼명을 변경하는 명령
2. 컬럼명이 변경되면 해당 컬럼과 관련된 제약조건에 대해서도 자동으로 변경된다.(Oracle만)
3. 예제
   1. Oracle
      

      

   2. SQL Server
      

      

마. DROP CONSTRAINT

1. 테이블 생성시 부여했던 제약조건을 삭제하는 명령
2. 예제
   

   

   
   

   

바. ADD CONSTRAINT

1. 필요에 의해서 제약조건 추가하는 명령
2. 예제: PLAYER 테이블에 TEAM 테이블과의 외래커 제약조건을 추가한다. 제약조건명은 PLAYER FK로 하고，
   PLAYER 태이블의 TEAM ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건이다.
   

   

   1. PLAYER 테이블이 참조하는 TEAM 테이블을 제거할시
      

      

      
      

      

   2. PLAYTER 테이블이 참조하는 TEAM 테이블의 데이터를 삭제할시
      

      

4. RENAME TABLE

1. 테이블의 이름을 변경하는 명령
2. 예제
   

   

5. DROP TABLE

1. 테이블을 삭제하는 명령
2. CASCADE CONSTRAINT: 해당 테이블에 정의된 제약조건도 삭제
   1. SQL Server에서는 CASCADE옵션이 없으므로 삭제하기 전에 먼저 제약조건을 삭제해야 한다.
3. 예제
   

   

   
   

   

6. TRUNCATE TABLE

1. 해당 테이블에 들어있는 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제(테이블 자체는 삭제 안됨)
2. 데이터의 조작에 해당하므로 DML로 분류할 수도 있지만 내부 처리 방식이나 Auto Commit 특성으로 인해 DDL로 분류
3. DELETE보다 시스템 부하가 적다.
4. 이 명령은 정상적인 복구가 불가능하므로 주의 필요
5. 예제
   

   

    

정리

------DDL
----CREATE 문
CREATE TABLE 테이블명 (
    속성1           타입    NOT NULL,
    속성2           타입
    CONSTRAINT 제약조건명 PRIMARY KEY (속성1),
    CONSTRAINT 제약조건명 FOREIGN KEY (속성1) REFERENCES 외래테이블명(외래테이블기본키)
    CONSTRAINT 제약조건명 UNIQUE (속성2)
);

--SELECT문으로 테이블 생성
CREATE TABLE 테이블명
AS SELECT * FROM 가져올테이블명;

----ALTER TABLE 
--ADD COLUMN : 테이블에 컬럼 추가
ALTER TABLE 테이블명
ADD (추가할_속성명 타입);
--예시
ALTER TABLE PLAYER
ADD (ADDRESS VARCHAR2(80));

--DROP COLUMN : 컬럼 삭제
ALTER TABLE 테이블명
DROP COLUMN 삭제할컬럼명;
--예시
ALTER TABLE PLAYER
DROP COLUMN ADDRESS;

--MODIFY COLUMN : 존재하는 속성들에 대해 데이터 유형, 디폴트값, NOT NULL 제약조건 변경
ALTER TABLE 테이블명
MODIFY (속성명 타입 DEFAULT '디폴트값' NOT NULL);
--예시
ALTER TABLE TEAM_TEMP
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

--RENAME COLUMN : 컬럼명 변경
ALTER TABLE 테이블명
RENAME COLUMN 기존속성명 TO 바꿀속성명;
--예시
ALTER TABLE PLAYER
RENAME COLUMN PLAYER_ID TO TEMP_ID;

--DROP CONSTRAINT : 테이블 생성시 부여했던 제약조건 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;
--예시
ALTER TABLE PLAYER
DROP CONSTRAINT PLAYER_FK;

--ADD CONSTRAINT : 해당 테이블에 제약조건 추가
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 [기본키|외래키|유니크 등등] ...;
--예시
ALTER TABLE PLAYER
ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCE TEAM(TEAM_ID);

----RENAME TABLE
RENAME 기존테이블명 TO 바꿀테이블명;
--예시
RENAME TEAM TO TEAM_TEMP;

----DROP TABLE : CASCADE CONSTRAINT 해당 테이블에 정의된 제약조건도 삭제
DROP TABLE 테이블명 [CASCADE CONSTRAINT];
--예시
DROP TABLE PLAYER [CASCADE CONSTRAINT];

----TRUNCATE TABLE : 해당 테이블 모든 행 제거 및 저장 공간까지 삭제
--DELETE보다 시스템 부하 적음 / 이 명령은 복구가 불가능하므로 사용에 주의
TRUNCATE TABLE 테이블명;
--예시
TRUNCATE TABLE TEAM;


----참고
--DELETE(/MODIFY) ACTION : CASCADE, SET NULL, SET DEFAULT, RESTRICT
1. CASECADE: MASTER 삭제시 CHILD 같이 삭제
2. SET NULL: MASTER 삭제시 CHILD 해당 필드 NULL
3. SET DEFAULT: MAETER 삭제시 CHILD 해당 필드 DEFAULT값으로 설정
4. RESTRICT: CHILD테이블에 PK값이 없는 경우만 MASTER삭제 허용
5. NO ACTION: 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

--INSERT ACTION: AUTOMATIC, SET NULL, SET DEFAULT, DEPEDNDENT
1. AUTOMATIC: MASTER 테이블에PK가 없는 경우 MASTER PK를 생성후 CHIILD입력
2. SET NULL: MASTER 테이블에 PK가 없는 경우 CHILD외부키를 NULL 값으로 처리
3. SET DEFAULT: MASTER 테이블에 PK가 없는 경우 CHILD 외부키를 지정된 기본값으로 입력
4. DEPENDENT: MASTER 테이블에 PK가 존재할 때만 CHILD 입력 허용
5. NO ACTION: 참조무결성을 위반하는 입력 액션을 취하지 않음

 

 

 

정리

 

 

[출처]

wiki.gurubee.net/display/STUDY/2.DDL%28DATA+DEFINTION+LANGUAGE%29