[SQL] DDL, Data Define Language

Woody의 기록·2023년 7월 12일
Databasesql
1

Database

목록 보기
1/2
post-custom-banner

데이터 정의어, DDL

✔️ Keywords

  • DDL(Data Definition Language)
    • CREATE
      • 테이블 생성
      • 스키마 생성
      • 도메인 생성
      • 인덱스 생성
      • 뷰 생성
    • ALTER
      • 기존 테이블에 새로운 속성 추가
      • 기존 테이블 속성 변경
      • 기존 테이블 속성 제거
    • DROP
      • 테이블 제거
      • 스키마 제거
      • 제약조건 제거
      • 도메인 제거
      • 인덱스 제거 제거
      • 뷰 제거

🔘 DDL(Data Definition Language, SQL 정의어)

  • 정의어(DDL)는 관계 데이터베이스에서 사용될 테이블, 스키마, 도메인, 인덱스, 뷰 등을 생성하거나 수정, 삭제 하기 위한 언어이다.
  • DDL의 종류에는 CREATE, ALTER, DROP문이 있다.

♞ CREATE 문

  • CREATE 명령어는 테이블, 스키마, 도메인, 인덱스, 뷰 등을 생성(정의)할 때 사용된다.

1. 테이블(Table) 생성

  • 테이블 생성은 CREATE TABLE 구문을 통해 생성되며 다음과 같은 형식으로 작성된다.
  • CREATE TABLE 구문 형식
CREATE TABLE 테이블명 (
{[속성명 데이터타입 [NOT NULL],}
[PRIMARY KEY(속성명),]
[UNIQUE(속성명),]
[FOREIGN KEY(속성명) REFERENCES 참조테이블명(참조속성명)
	[ON DELETE CASCADE | SET NULL | SET DEFAULT | NO ACTION]
	[ON UPDATE CASCADE | SET NULL | SET DEFAULT | NO ACTION]
[CONSTRAINT 제약조건명 제약조건]
);
  • 속성의 데이터 타입
    • 데이터 타입은 특정 속성에 저장되는 데이터의 유형을 의미한다.
    • 데이터 타입의 종류
      데이터 타입표현 형식
      정수(integer)INT
      실수(float)FLOAT 또는 REL
      고정 길이 문자CHAR(문자수)
      가변 길이 문자VARCHAR(문자수)
      시간TIME
      날짜DATE

CHAR vs VARCHAR

  • 고정 길이 문자(CHAR)는 지정된 문자수만큼 기억 공간을 항상 차지한다.
  • 가변 길이 문자(VARCHAR)는 지정된 범위 내에서 실제 입력된 자료에 따라 유동적으로 기억공간을 차지한다.
  • 예를 들어, CHAR(10), VHARCHAR(10)에 각각 “hello” 라는 5개의 문자가 입력된 경우, CHAR(10)은 입력 길이에 관계없이 10개의 기억공간을 고정적으로 사용하지만 VHARCHAR(10)은 최대 10개의 문자열을 저장할 수 있는 기억공간중 문자열 5개를 저장할 공간만 차지하도록 유동적으로 조절한다.
  • 제약 조건
    1. NOT NULL 제약 조건:
      이 제약 조건은 특정 속성이 NULL 값을 허용하지 않도록 한다. 따라서 해당 속성은 항상 값을 가져야 함을 의미한다. 다음은 NOT NULL 제약 조건을 설정하는 예시이다.
       CREATE TABLE 테이블명 (
     속성명 데이터_타입 NOT NULL
 );
 
 또는
 
 CREATE TABLE 테이블명 (
 	  속성명 데이터타입 CONSTRAINT 제약조건명 NOT NULL
 );
    2. UNIQUE 제약 조건:
      UNIQUE 제약 조건은 특정 속성에 중복된 값을 허용하지 않도록 한다. 이 제약 조건을 설정하면 해당 속성에는 고유한 값만 입력될 수 있다. 다음은 UNIQUE 제약 조건을 설정하는 예시이다.
       CREATE TABLE 테이블명 (
     속성명 데이터_타입 UNIQUE
 );
 
 또는 
 
 CREATE TABLE 테이블명 (
     속성명 데이터_타입,
 		UNIQUE(속성명)
 );
    3. PRIMARY KEY 제약 조건:
      PRIMARY KEY 제약 조건은 테이블에서 특정 속성을 주 식별자(**PRIMARY KEY**)로 지정합니다. 이 제약 조건을 설정하면 해당 속성에는 중복된 값이 없어야 하며, 자동으로 인덱스가 생성된다. 다음은 PRIMARY KEY 제약 조건을 설정하는 예시이다.
       CREATE TABLE 테이블명 (
     속성명 데이터_타입 PRIMARY KEY
 );
 
 또는
 
 CREATE TABLE 테이블명 (
     속성명 데이터_타입,
 	  PRIMARY KEY(속성명)
 );
    4. FOREIGN KEY 제약 조건:
      FOREIGN KEY 제약 조건은 테이블 간의 관계를 설정한다. 특정 속성은 다른 테이블의 주 식별자(**PRIMARY KEY**)를 참조하게 된다. 다음은 FOREIGN KEY 제약 조건을 설정하는 예시이다.
        CREATE TABLE 테이블명 (
      속성명 데이터타입,
      FOREIGN KEY(속성명) REFERENCES 참조_테이블명(참조_속성명)
  );
    5. CHECK 제약 조건:
      CHECK 제약 조건은 특정 속성에 입력될 수 있는 값의 범위나 조건을 제한한다. 다음은 CHECK 제약 조건을 설정하는 예시이다.
       CREATE TABLE 테이블명 (
     속성명 데이터타입 CHECK(조건식)
 );
 
 또는
 
 CREATE TABLE 테이블명 (
     속성명 데이터타입,
 		CONSTRAINT 제약조건명 CHECK(속성명=범위)
 );

2. 스키마(Schema) 생성

  • 시스템 관리자가 일반 사용자에게 스키마에 대한 권한을 주기 위한 스키마를 만들기 위해 사용된다.
  • CREATE SCHEME 구문에 의해 생성한다.
  • 스키마 생성 구문
    CREATE SCHEMA 스키마명 AUTHORIZATION 사용자;

3. 도메인(Domain) 정의

  • 한 속성값의 범위를 지정하기 위한 도메인은 CREATE DOMAIN 구문을 통해 생성할 수 있다.
  • 도메인 생성 구문
    CREATE DOMAIN 도메인명 데이터타입
[DEFUALT 기본값]
[CONSTRAINT 제약조건명 CHECK(VALUE IN(범위))];

4. 인덱스(Index) 정의

  • 인덱스는 데이터베이스에서 자료를 보다 효율적으로 검색하기 위해 사용되며 인덱스는 시스템에 의해 자동으로 관리된다.
  • 인덱스는 CREATE INDEX 구문을 통해 생성된다.
  • 인덱스 생성 구문
    CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명[ASC|DESC])
[CLUSTER];
  • UNIQUE: 중복을 허용하지 않도록 인덱스를 생성할 때 사용하며, 생략시 중복이 허용된다.
  • ON 테이블명(속성명): 지정된 테이블의 속성으로 인덱스를 만든다.
    • [ASC|DESC]: 인덱스로 사용될 속성값의 정렬방법을 나타낸다. ASC는 오름차순, DESC는 내림차순을 의미한다.
  • CLUSTER: 인접된 튜플들을 물리적인 그룹으로 묶어서 저장하도록 할 때 사용된다.

5. 뷰(View) 생성

  • 뷰는 기본 테이블로부터 유도된 가상 테이블로 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 비슷하다.
  • View는 데이터 모델링, 데이터 접근 제어, 복잡한 쿼리 단순화 등 다양한 용도로 사용된다.
  • 뷰는 CREATE VIEW 구문을 통해 생성되고, CREATE OR REPLACE VIEW 구문으로 기존 VIEW가 존재하는 경우 갱신할 수 있다.
  • 뷰 생성 구문
    CREATE VIEW 뷰이름 as
SELECT 속성명1, 속성명2, ... ,
FROM 테이블명
WHERE 조건

♞ ALTER 문

  • SQL의 ALTER 문은 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 수정하는 데 사용된다.
  • ALTER 문을 통해서 기존에 만들어진 테이블에 속성을 추가하거나 기존 속성을 변경 또는 삭제할 수 있다.

1. 테이블 변경:

  • 테이블 이름 변경:
    ALTER TABLE 테이블명 RENAME TO 바꿀테이블명;
  • 새로운 속성 추가:
    ALTER TABLE 테이블명 ADD 추가할속성명 데이터타입;
  • 기존 열의 데이터 타입 변경:
    ALTER TABLE 테이블명 ALTER COLUMN 속성명 SET DATA TYPE 변경할데이터타입;
  • 열 삭제:
    ALTER TABLE 테이블명 DROP COLUMN 삭제할속성명;

2. 제약 조건 추가/변경/제거

  • 제약 조건 추가:
    ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건;
  • 제약 조건 변경:
    ALTER TABLE 테이블명 ALTER CONSTRAINT 제약조건명 제약조건;
  • 제약 조건 제거:
    ALTER TABLE 테이블명 DROP CONSTRAINT 제거할제약조건;

3. 인덱스 추가/변경/제거

  • 인덱스 추가:
    ALTER TABLE 테이블명 ADD INDEX 인덱스명(속성명);
  • 인덱스명 변경:
    ALTER TABLE 테이블 ALTER INDEX 인덱스명 RENAME TO 바꿀인덱스명;
  • 인덱스 제거:
    ALTER TABLE 테이블명 DROP INDEX 삭제할인덱스명;

4. 뷰 변경

  • 뷰 정의 변경:
    ALTER VIEW 뷰이름 AS
SELECT 속성명1, 속성명2, ... , 
FROM 테이블명
WHERE 조건;
    • 위의 구문에서 뷰이름은 변경하려는 뷰의 이름을 지정하는 부분이다. AS 키워드를 사용하여 새로운 뷰의 정의를 수행한다. 그리고 SELECT 문을 사용하여 새로운 뷰의 정의를 작성한다. 이때 원하는 테이블과 조건을 사용하여 데이터를 선택하고, 필요한 속성을 지정할 수 있다.

5. 기타 변경

  • 기본 키(primary key) 설정:
    ALTER TABLE 테이블명 ADD PRIMARY KEY (속성명);
  • 외래 키(foreign key) 설정:
    ALTER TABLE 테이블명 ADD FOREIGN KEY (속성명) REFERENCES 참조테이블명

♞ DROP 문

  • DROP 명령어는 기존에 사용되던 테이블, 스키마, 도메인, 인덱스, 뷰, 제약조건 등을 제거할 때 사용되는 명령어로, DROP 을 통해 삭제를 수행하면 테이블 전체가 제거된다.

1. 테이블 삭제

DROP TABLE 테이블명 [CASCADE|RESTRICT];

2. 스키마 삭제

DROP SCHEMA 스키마명 CASECADE|RESTRICT];

3. 도메인 삭제

DROP DOMAIN 도메인명 [CASECADE|RESTRICT];

4. 인덱스 삭제

DROP INDEX 인덱스명;

5. 뷰 삭제

DROP VIEW 뷰이름 [CASECADE|RESTRICT];

6. 제약조건 삭제

DROP CONSTRAINT 제약조건명;

DROP vs TRUNCATE

  • DROP과 TRUNCATE는 둘 다 데이터베이스에서 테이블을 삭제하는 구문이지만 다음과 같은 차이점이 존재한다.
  1. 작동 방식:
    • DROP: DROP 문은 테이블을 완전히 삭제한다. 즉 테이블과 관련된 데이터, 인덱스, 제약 조건 등 모든 객체가 삭제된다.
    • TRUNCATE: TRUNCATE 문은 테이블의 모든 데이터를 삭제한다. 테이블 스키마는 그대로 유지되며, 테이블 자체만 비워진다. 데이터의 물리적인 공간은 반환되지만, 테이블 구조는 그대로 유지된다.
  2. 롤백 가능 여부:
    • DROP: DROP 문은 테이블을 삭제하므로 롤백할 수 없다. 한 번 실행된 후에는 복구할 수 없으며, 데이터와 구조가 영구적으로 손실된다.
    • TRUNCATE: TRUNCATE 문은 테이블의 데이터를 삭제하지만 테이블 구조는 그대로 유지된다. 트랜잭션을 사용하는 경우 TRUNCATE 문은 롤백이 가능하다. 롤백되면 삭제된 데이터가 복구된다.
  3. 성능:
    • DROP: DROP 문은 테이블과 관련된 모든 객체를 삭제하기 때문에 작업이 더 많이 소요될 수 있다.
    • TRUNCATE: TRUNCATE 문은 테이블의 데이터를 삭제하고 테이블을 재사용할 수 있도록 공간을 반환하기 때문에 DROP에 비해 더 빠를 수 있다.
  4. 권한:
    • DROP: DROP 문은 테이블을 삭제하기 위해 필요한 권한을 요구한다. 테이블의 구조를 변경하는 작업이므로 더 높은 수준의 권한이 필요하다.
    • TRUNCATE: TRUNCATE 문은 테이블의 데이터를 삭제하는 작업이기 때문에 DROP에 비해 상대적으로 낮은 수준의 권한이 필요하다.

→ DROP 문은 테이블 자체와 모든 관련 객체를 삭제하는 반면 TRUNCATE 문은 테이블의 데이터를 삭제하고 테이블을 재사용할 수 있도록 공간을 반환한다. 롤백 가능 여부와 성능 측면에서도 차이가 있기 때문에 적절한 상황에 맞게 선택해야 한다.

profile
Woody의 기록
Github - https://www.github.com/woody35545
post-custom-banner

0개의 댓글