[SQLD 도전기] SQL 기본(2) - DDL(Data Definition Language)

본문 내용은 모두 필자가 공부하며 직접 정리한 내용입니다.

Kdata 데이터자격검정의 SQL 개발자(SQLD) 가이드를 참고하였습니다.

 

─지난 글 보기─

[SQLD 도전기] SQL 기본(1) - 관계형 데이터베이스 개요

과목 2 SQL 기본 및 활용

 

제 1장 SQL 기본

 

제 2절 DDL(Data Definition Language)

1. 데이터 유형

ㆍ데이터베이스의 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준

출처 : Kdata 데이터자격검정

출처 : Kdata 데이터자격검정

 

CHAR 유형과 VARCHAR 유형의 차이점

- VARCHAR 유형

ㆍVARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐이다.

ㆍ길이가 다양한 컬럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합하다.

ㆍ저장 측면에서도 CHAR 유형보다 작은 영역에 저장할 수 있으므로 장점이 있다.

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

예) 'AA' ≠ 'AA '

 

- CHAR 유형

ㆍ짧은 쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한다.

ㆍ문자열을 비교할 때 공백(Blank)을 채워서 비교하는 방법을 사용한다.

예) 'AA' = 'AA '

 

2. CREATE 테이블

가. CREATE TABLE

ㆍ테이블을 생성하는 구문 형식은 다음과 같다.

CREATE　TABLE　테이블이름 (
      칼럼명1 DATATYPE [DEFAULT 형식], 
      칼럼명2 DATATYPE [DEFAULT 형식], 
      칼럼명2 DATATYPE [DEFAULT 형식]
 ) ;

- 테이블 생성 시에 주의해야 할 몇 가지 규칙

ㆍ테이블명은 객체를 의미할 수 있는 적절한 이름을 사용, 가능한 단수형을 권고

ㆍ테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

ㆍ한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

ㆍ테이블 이름을 지정하고 각 칼럼들은 괄호 "()"로 묶어 지정한다.

ㆍ각 칼럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";" 으로 끝난다.

ㆍ칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다. (데이터 표준화 관점)

ㆍ칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.

ㆍ테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

ㆍ벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.

ㆍA-Z, a-z, 0-9, _, $, # 문자만 허용된다.

ㆍ테이블명이 잘못된 사례

출처 : Kdata 데이터자격검정

ㆍ테이블 생성시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

ㆍDATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.

ㆍ문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

ㆍ칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.

ㆍ칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

 

다. 제약조건(CONSTRAINT)

ㆍ사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한

데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약

ㆍ테이블 생성 시, 제약조건을 반드시 기술할 필요는 없다.

출처 : Kdata 데이터자격검정

- NULL 의미

ㆍNULL(ASCII 코드 00번)은 공백(Blank, ASCII 코드 32번)이나 숫자 0(Zero, ASCII 48) 과는 전혀 다른 값

ㆍ조건에 맞는 데이터가 없을 때의 공집합과도 다르다.

ㆍ'아직 정의되지 않은 미지의 값' 이거나 '현재 데이터를 입력하지 못하는 경우'를 의미한다.

 

- DEFAULT 의미

ㆍ데이터 입력 시에 칼럼의 값이 지정되어 있지 않을 경우 기본값(Default)을 사전에 설정할 수 있다.

ㆍ데이터 입력시 명시된 값을 지정하지 않은 경우 NULL 값이 입력

ㆍDefault 값을 정의했다면 해당 칼럼에 NULL 값이 입력되지 않고 사전에 정의된 기본 값이 자동으로 입력

 

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

ㆍ테이블을 생성한 후 테이블의 구조가 제대로 만들어졌는지 확인할 필요가 있다.

ㆍOracle의 경우 "DESCRIBE 테이블명;" 또는 간략히 "DESC 테이블명;"으로 해당 테이블에 대한 정보를 확인할 수 있다.

ㆍSQL Server의 경우 "sp_help 'dbo.테이블명'"으로 해당 테이블에 대한 정보를 확인할 수 있다.

 

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

ㆍ기존 테이블을 이용한 CTAS(Create Table ~ As Select ~) 방법을 이용할 수 있다면

칼럼별로 데이터 유형을 다시 재정의 하지 않아도 되는 장점이 있다.

ㆍCTAS 기법 사용시 주의할 점은 기존 테이블의 제약조건 중,

NOT NULL만 새로운 복제 테이블에 적용되고 그 외의 다른 제약 조건은 없어진다는 점

ㆍ제약 조건을 추가하기 위해서는 ALTER TABLE 기능을 사용해야 한다.

ㆍSQL Server에서는 Select ~ Into ~ 를 활용하여 위와 같은 결과를 얻을 수 있다.

ㆍ칼럼 속성에 Identity를 사용했다면 Identity 속성까지 같이 적용이 된다.

 

3. ALTER TABLE

ㆍ주로 칼럼을 추가/삭제하거나 제약조건을 추가/삭제하는 작업을 진행할 때 쓰는 명렁어

가. ADD COLUMN

ㆍ기존 테이블에 필요한 칼럼을 추가하는 명령

ALTER TABLE 테이블명 
ADD 추가할 칼럼명 데이터 유형;

[예제] Oracle 
ALTER TABLE PLAYER 
ADD (ADDRESS VARCHAR2(80)); 
테이블이 변경되었다.

ㆍ주의할 것은 새롭게 추가된 칼럼은 테이블의 마지막 칼럼의 되며 칼럼의 위치를 지정할 수는 없다.

 

나. DROP COLUMN

ㆍ테이블에서 필요 없는 칼럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능

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

ㆍ한 번 삭제된 칼럼은 복구가 불가능하다.

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

[예제] Oracle 
ALTER TABLE PLAYER 
DROP COLUMN ADDRESS; 
테이블이 변경되었다.

 

다. MODIFY COLUMN

ㆍALTER TABLE 명령을 이용해 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대한 변경을 포함할 수 있다.

ㆍ테이블의 칼럼에 대한 정의를 변경하는 명령은 다음과 같다.

[Oracle] 
ALTER TABLE 테이블명 
MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 
	칼럼명2 데이터 유형 …); 
[SQL Server] 
ALTER TABLE 테이블명 
ALTER (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 
	칼럼명2 데이터 유형 …);

- 칼럼 변경 시, 몇 가지 사항을 고려해서 변경해야 한다.

ㆍ해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다.

이는 기존의 데이터가 훼손될 수 있기 때문이다.

ㆍ해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

ㆍ해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

ㆍ해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

 

- RENAME COLUMN

ㆍ테이블을 생성하면서 만들어졌던 칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우에

유용하게 쓰일 수 있는 RENAME COLUMN 문구이다.

ALTER TABLE 테이블명 
RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명;

ㆍRENAME COLUMN으로 칼럼명이 변경되면, 해당 칼럼과 관계된 제약조건에 대해서도

자동으로 변경되는 장점이 있지만, ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시되어 있는 기능이 아니고

Oracle 등 일부 DBMS에서만 지원하는 기능이다.

 

ㆍSQL Server에서는 sp_rename 저장 프로시저를 이용하여 칼럼 이름을 변경할 수 있다.

sp_rename 변경해야 할 칼럼명, 새로운 칼럼명, 'COLUMN';

sp_rename 'dbo.TEAM_TEMP.TEAM_ID', 'TEAM_TEMP_ID', 'COLUMN'; 
주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다.

 

라. DROP CONSTRAINT

ㆍ테이블 생성 시 부여했던 제약조건을 삭제하는 명령어 형태는 다음과 같다.

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

 

마. ADD CONSTRAINT

ㆍ테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.

ALTER TABLE 테이블명 
ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

 

4. RENAME TABLE

ㆍRENAME 명령어를 사용하여 테이블의 이름을 변경할 수 있다.

RENAME 변경전 테이블명 TO 변경후 테이블명;

ㆍSQL Server에서는 sp_rename을 이용하여 테이블 이름을 변경할 수 있다.

sp_rename 변경전 테이블명, 변경후 테이블명;

 

5. DROP TABLE

ㆍ테이블을 잘못 만들었거나 테이블이 더 이상 필요 없을 경우 해당 테이블을 삭제해야 한다.

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

ㆍDROP 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제한다.

ㆍCASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다.

ㆍSQL Server에서는 CASCADE 옵션이 존재하지 않으며 테이블을 삭제하기 전에

참조하는 FOREIGN KEY 제약 조건 또는 참조하는 테이블을 먼저 삭제해야 한다.

 

6. TRUNCATE TABLE

ㆍ테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 한다.

TRUNCATE TABLE PLAYER;

 

 

DROP, TRUNCATE, DELETE 차이점

- 테이블 자체를 삭제

ㆍDROP TABLE : 테이블 자체가 없어지기 때문에 테이블 구조를 확인할 수 없다.

 

- 테이블 내의 데이터만 삭제(둘은 처리하는 방식 자체가 다름)

- TRUNCATE TABLE : 테이블 구조는 그대로 유지한 채, 데이터만 전부 삭제하는 명령어

: 정상적인 복구가 불가능하므로 주의해야 한다.

- DELETE TABLE :  삭제된 데이터를 로그로 저장하기 때문에

시스템 활용 측면에서는 TRUNCATE 명령어보다 시스템 부하가 크다.

 

다음에는 DML에 관한 내용을 정리하여 포스팅하겠습니다.