[MariaDB] 3. DDL - database schema

DDL

Data Definition Language

데이터를 정의하는 데에 사용되는 SQL입니다.
데이터 구조를 생성/변경/제거할 때 이용합니다.

데이터 구조는 다른 말로 Schema(스키마)라고도 부릅니다. (구조 = 스키마)
이 스키마를 이루는 Object(객체) 요소에는 table, view, index, stored routine 등이 있습니다.

DDL에는 create(생성), 변경(alter), 제거(drop), truncate가 있고, 이 SQL은 데이베이스나 데이터베이스 내에 정의된 Object을 대상으로 사용할 수 있습니다.

이번 포스팅에서는 데이터베이스 스키마를 DDL을 이용하여 생성, 수정, 제거하는 방법을 알아볼 것입니다.

1. database schema 생성
2. database schema 조회
3. database schema 변경
4. database schema 제거

---

1. database schema 생성

데이터베이스는 Object들로 이뤄진 논리적인 집합입니다.
사용자의 접근 권한이나 애플리케이션에서 이용될 Object 단위로 데이터베이스를 구성하는 것이 좋습니다.

CREATE [OR REPLACE] {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'comment'

데이터베이스 스키마를 생성을 하는 문법은 위와 같으며, character set이나 collate는 옵션값으로 설정하지 않을 경우 기본 설정값으로 자동 설정됩니다.

• character set
  • 문자 인코딩을 설정합니다.
• collate
  • 문자데이터(varchar, char)의 정렬방식을 설정합니다.
• comment
  • 데이터베이스에 대한 코멘트(설명)을 설정합니다.

IF EXISTS 나 OR REPLACE 를 이용하여 이미 database가 존재할 경우에 대한 처리도 할 수 있습니다.

OR REPLACE 조건을 이용하면 이미 존재하는 데이터베이스의 경우 교체를 진행합니다. 만일 IF EXISTS 조건을 이용할 경우 이미 존재하는 데이터베이스라면 추가를 진행하지 않으며 에러 메시지도 출력하지 않습니다.

create database test_db;

MariaDB에 설정한 기본 character set과 collation을 이용할거라면 위와 같이 create database 데이터베이스명;만 작성해도 됩니다.

create database if not exists test_db
character set utf8 collate utf8_unicode_ci
comment '테스트 데이터베이스';

character set과 collation, comment를 모두 설정하는 방법은 위와 같습니다.

---

2. database schema 조회

SHOW CREATE {DATABASE | SCHEMA} db_name;

show create database 명령어로 데이터베이스 스키마를 조회할 수 있습니다.

show create database test_db\G
*************************** 1. row ***************************
       Database: test_db
Create Database: CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

---

3. database schema 변경

데이터베이스의 기본 character set이나 collation을 변경하거나 코멘트를 설정합니다.

ALTER {DATABASE | SCHEMA} db_name
    alter_specification ...

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'comment'

이 때, 주의해야할 점이 하나 있는데. 이 명령문은 데이터베이스의 기본 character set이나 collation을 변경해주는 것이기 때문에, 테이블이나 테이블 내의 컬럼에 직접 설정된 character set이나 collation에는 적용되지 않는다는 점입니다.

만일, 직접 설정된 값을 변경하고 싶다면 테이블과 테이블 내의 컬럼을 직접 수정해야 합니다.

아래는 데이터베이스 스키마를 변경하는 간단한 sql 예시입니다.

alter database test_db
character set utf8mb4
collate utf8mb4_unicode_ci
comment '테스트db';

---

4. database schema 제거

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

IF EXISTS 조건은 데이터베이스가 존재할 경우 DROP명령어를 실행합니다.
IF EXISTS 조건을 추가할 경우, 존재하지 않는 데이터베이스에 drop 명령어를 시도해도 에러를 출력하지 않습니다.

drop database if exists test_db;