[SQLD] SQL 기본 1-2. DDL (Data Definition Language)

[2] DDL (Data Definition Language)

1. 데이터 유형

데이터 유형 - 자료를 받아들일 공간을 자료의 유형별로 나누는 기준

즉, 칼럼이 받아들일 수 있는 자료의 유형을 규정

 

몸무게 -> '박지성' -> 잘못된 데이터 [숫자가 의미를 가지는 칼럼 정보에 문자 입력]

 

-> 데이터 유형과 더불어 지정한 크기도 중요한 기능 제공

 

 SQL 벤더별로 데이터 유형과 내장형 함수 부분에서 차이가 나는데 데이터베이스 내부의 구조적 차이점이 더 크다

 

 

 

문자열 유형 : CHAR, VARCHAR

 

저장 영역과 문자열 비교의 차이

 

VARCHAR : 가변길이, 필요한 영역은 실제 데이터 크기

-> 길이가 다양한 칼럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합, 작은 영역에 저장 가능

맨 처음부터 한 문자씩 비교하고, 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단

VARCHAR(40)으로 40바이트 지정시, PARKJISUNG처럼 데이터가 입력되면 11바이트 공간만 차지

-> 주민등록번호나 사번처럼 고정된 길이의 문자열이 아닌 경우 유용

 

CHAR : 문자열 비교시 공백을 채워서 비교, 길이가 짧은 쪽의 끝에 공백을 추가해 같은 길이에서 앞에서부터 비교

따라서 끝의 공백만 다른 문자열은 같다고 판단

 

즉, CHAR가 아닌 VARCHAR나 NUMERIC 유형에서는 정의한 길이나 자릿수는 최대한의 한계값을 정의한 것

-> 문자열 (CHAR와 VARCHAR)에 대한 최대 길이와 NUMBER 칼럼의 정밀도는 테이블 설계시 중요한 요소

[데이터가 입력된 경우에는 ALTER TABLE으로도 정정이 힘들기 때문에]

 

---

2. CREATE TABLE
입력될 데이터 정의, 어떤 데이터 유형으로 선언할지 결정

 

가. 테이블과 칼럼 정의

고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 후보키 중에 하나를 선정해 기본키 칼럼으로 지정

-> 선수 테이블이라면 선수ID 칼럼이 적합

 

기본키는 여러 개의 칼럼으로도 만들어질 수 있다. 기본키와 외부키를 활용 -> 테이블과 테이블간 관계를 설정

 

-> 선수 테이블에 소속팀 정보가 같이 존재한다면,

1. 특정 팀 이름의 변경시->  소속 선수 데이터를 직접 수정

2. 팀이 해체시 -> 선수 관련 정보까지 삭제되는 수정/삭제 이상(Anomaly)

 

-> 팀 정보를 관리하는 팀 테이블을 분리해 팀ID와 팀 이름을 저장하고, 선수 테이블에서 팀ID를 외부키로 참조

 

나. CREATE TABLE

테이블명 : 단수형으로 적절한 이름 사용, 중복되지 않게 문자로 시작

칼럼명 : 한 테이블 내에서 중복해 지정 가능, 문자로 시작

테이블 이름 지정 후 각 칼럼은 "()"으로 묶고, ","로 구분하며 맨 끝은 ";"으로 끝난다.

데이터 표준화 관점에서 칼럼은 다른 테이블까지 고려해 일관성 있게 사용

칼럼 뒤에 데이터 유형 지정

에약어는 사용 불가능

A-Z, a-z, 0-9, _, $, # 문자만 허용 [-, ', ~ 불가]

 

-> 선수 테이블의 TEAM_ID, 팀 테이블의 TEAM_ID는 같은 칼럼 이름

계층적 구조를 가진 전체 경로 : 실제 DBMS에서 'DB명+DB사용자명+테이블명+칼럼명' 으로 관리

 

같은 이름을 가진 칼럼은 기본키와 외래키의 관계를 가지는 경우가 많고, 조인 조건으로 주로 사용

 

CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),

CONSTRAINT PLYAER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)

 

대소문자 구분 X

DATETIME은 별도 크기 지정 X

문자 데이터 유형은 최대 길이 표시 필요

칼럼과 칼럼은 콤마로 구분, 마지막 컬럼은 콤나 안찍기,

제약조건이 있으면 CONSTRAINT 이용해 추가

 

제약조건 : 칼럼 LEVEL 정의 방식, 테이블 LEVEL 정의 방식

 

다. 제약조건

제약조건 : 데이터 무결성을 유지하기 위함

NULL : 아직 정의되지 않은 미지의 값, 현재 데이터를 입력하지 못하는 경우

DEFAULT : 칼럼의 값이 지정되어 있지 않을 경우 기본값 설정, DEFAULT 미설정시 NULL 값 입력

 

제약 조건의 종류

PK : 기본키 [PRIMARY KEY] - 기본키 제약 정의시 자동으로 UNIQUE 인덱스 생성, 기본키 구성 컬럼에 NULL 입력 불가

즉 기본키 제약 = 고유키 제약 & NOT NULL 제약

UK : 고유키 [UNIQUE KEY] - 행 데이터를 공유하게 식별하기 위한 고유키 정의 [NULL은 제약 대상이 아님]

NOT NULL : 입력 필수 [CHECK의 일부분]

CHECK : 입력할 수 있는 값의 범위 제약 [TRUE or FALSE로 평가]

FK : 외래키 [FOREIGN KEY] - 참조 무결성 제약 옵션 [CASCADE]

 

 

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

DESCRIBE 테이블명; DESC 테이블명;

 

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

CTAS : Create Table~ As Select~ : 기존 테이블을 이용해 칼럼별로 데이터 유형을 재정의 하지 않고 생성

-> 제약 조건 중 NOT NULL만 적용

-> 나머지는 ALTER TABLE로 적용

 

-> CREATE TABLE TEAM_TEMP AS SELECT* FROM TEAM;

 

---

 

3. ALTER TABLE

칼럼을 추가/삭제 제약조건을 추가/삭제

 

가. ADD COLUMN

추가된 칼럼은 마지막 컬럼이 되며, 칼럼의 위치는 임의지정 불가

 

나. DROP COLUMN

한 번에 하나의 칼럼만 삭제 가능하고, 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 한다.

 

다. MODIFY COLUMN

제약 조건의 변경 가능

 

ALTER TABLE TEAM_TEMP

MOFIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

 

RENAME COLUMN

칼럼명의 변경

 

ALTER TABLE PLAYER

RENAME COLUMN PLAYER_ID TO TEMP_ID;

 

라. DROP CONSTRAINT

제약 조건의 삭제

 

ALTER TABLE PLAYER

DROP CONSTRAINT PLAYER_FK;

 

마. ADD CONSTRAINT

제약 조건의 추가

 

ALTER TABLE PLAYER

ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

 

무결성 제약조건 위배시 [자식 레코드 발견시] 삭제 불가능

참조 무결성 제약 조건 : FK 설정으로 테이블 삭제나 필요한 데이터의 삭제를 방지

 

4. RENAME TABLE

테이블명의 변경

RENAME TEAM TO TEAM_BACKUP;

 

5. DROP TABLE

테이블의 삭제

 

DROP TABLE 테이블명 [CASCADE CONSTRAINT]; 

CASCADE CONSTRAINT : 관계가 있었던 참조되는 제약조건에 대해서도 삭제

 

6. TRUNCATE TABLE

테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제

 

 

DROP TABLE : 테이블 자체가 사라짐

TRUNCATE TABLE : 테이블 구조는 그대로 유지한 채 데이터만 전부 삭제

 

테이블의 데이터를 삭제하는 명령어

TRUNCATE TABLE : 테이블 전체 데이터를 삭제하는 경우, DELETE에 비해 시스템 부하가 적다.

단, 정상적인 복구가 불가능하다.

DELETE : DML로 WHERE절로 해당되는 데이터만 선택해 삭제할 수 있다.