MySQL 데이터 정의 언어 DDL (Data Definition Language)

DDL 이란? 

데이터베이스의 구조 및 정의를 생성하거나 변경하는 쿼리를 DDL (데이터 정의 언어) 라고 한다.

스토어드 프로시저나 함수, 그리고 DB나 테이블 등을 생성하거나 변경하는 대부분의 명령이 DDL에 해당한다.

 

CREATE, ALTER, DROP, TRUNCATE  등이 있다.

---

# 데이터 베이스 정의

 

1. 데이터 베이스 생성

-- #1
CREATE DATABASE [IF NOT EXISTS] <테이블이름>; 
-- #2
CREATE DATABASE IF NOT EXISTS employees; 
-- #2
CREATE DATABASE IF NOT EXISTS employees CHARACTER SET utf8; 
-- #3
CREATE DATABASE IF NOT EXISTS employees CHARACTER SET utf8 COLLATE utf8_general_ci;

- IF NOT EXISTS 라는 키워드를 사용하면 데이터베이스가 없는 경우에만 생성하고, 있다면 에러가 나지않고  무시한다.

 

2. 데이터 베이스 목록

SHOW DATABASES;
SHOW DATABASES LIKE '%emp%'; -- 'emp' 라는 문자열을 포함한 데이터베이스 목록만 조회

- 데이터베이스의 목록을 나열한다. 단 권한을 가지고 있는 데이터베이스의 목록만 표시된다.

 

3. 데이터베이스 선택

USE <데이터베이스이름>
USE employees;

- 기본 데이터베이스를 선택하는 명령이다.

- SQL 문장에서 별도로 데이터베이스를 명시하지 않고 테이블 이름이나 프로시저의 이름만 명시하면 MySQL 서버는 현재 커넥션의

  기본 데이터베이스에서 주어진 테이블이나 프로시저를 검색한다.

 

4. 데이터베이스 속성 변경

ALTER DATABASE <데이트베이스이름> CHARACTER SET=utf8;
ALTER DATABASE employees CHARACTER SET=utf8;
ALTER DATABASE employees CHARACTER SET=utf8 COLLATE=utf8_general_ci;

- 데이터베이스를 생성할 때 지정한 문자집합이나 콜레이션을 변경한다.

 

5. 데이터베이스 삭제

DROP DATABASE [IF EXISTS] <데이터베이스이름>;
DROP DATABASE IF EXISTS employees;

- IF EXISTS 키워드를 사용하면 해당 데이터베이스가 존재할 때만 삭제하고, 그렇지 않으면  실행하지 않는다.

---

# 테이블 정의

 

1. 테이블 생성

-- 테이블 생성 예시
CREATE  TABLE IF NOT EXISTS db_test (
  member_id INT UNSIGNED AUTO_INCREMENT,
  nickname CHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,
  home_url VARCHAR(200) NOT NULL DEFAULT '',
  birth_year SMALLINT(4) UNSIGNED ZEROFILL,
  member_point INT  DEFAULT 0,
  registered DATETIME,
  modified_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gender ENUM('Female', 'Male') NOT NULL,
  hobby SET('Reading', 'Game', 'Sports'),
  profile TEXT,
  session_data BLOB,
  PRIMARY KEY (member_id),
  UNIQUE INDEX ux_nickname (nickname),
  INDEX ix_registered (registered)  
) ENGINE=INNODB;

 

• 각 칼럼은 " 칼럼명 + 칼럼타입 + [타입별옵션] + [NULL 여부] + [기본값]" 의 순서대로 명시한다.
• 모든 칼럼은 칼럼의 초기값을 설정하는 DEFAULT,  칼럼이 NULL이 될 수 있는지 여부를 나타내는 NULL | NOT NULL 설정할 수 있다.
• 문자열 타입은 타입 뒤에 반드시 칼럼에 최대한 저장할 수 있는 문자 수를 명시해야 한다.
• 문자열 타입은 CHARACTER SET 절은 칼럼에 저장되는 문자열 값이 어떤 문자집할을 사용할지를 결정하고 COLLATE 로 비교나 정렬 규칙을 나타내고자 콜레이션을 설정할 수 있다. CHARACTER SET 만 설정되면 해당 문자 집합의 기본 콜레이션이 자동으로 설정된다.
• 숫자 타입은 UNSIGNED 키워드를 명시할 수 있고 디폴트 값은 SIGNED가 된다.
• 숫자 타입은 ZEROFILL 이라는 키워드도 선택적으로 가질 수 있는데, 이는 숫자 값의 왼쪽에 '0'을 패딩할지를 결정하는 옵션이다.
• 날짜 타입에서 DATE 나 DATETIME 타입은 특별히 명시할 수 있는 옵션이 없고 TIMESTAMP 타입은 어떤 조작을 할 때 값이 자동으로 현재시간으로 업데이트할지를 결정하는 옵션을 추가로 명시할 수 있다.
• ENUM 또는 SET  타입은 타입의 이름 뒤에 해당 칼럼이 가질수 있는 값을 괄호로 정의해야만 한다.
• ENGINE=InnoDB 라고 정의 했기 때문에 이테이블은 InnoDB 스토리지 엔진을 사용하는 테이블로 생성된다.

2. 테이블 구조 조회

SHOW CREATE TABLE <테이블이름>;
SHOW CREATE TABLE db_test;

- SHOW CREATE TABLE 명령으로 나온 출력값이 최초 테이블을 생성할 때 사용자가 실행한 내용을 그대로 보여주는 것이 아니고

MySQL 서버가 테이블의 메타정보를 읽어서 이를 CREATE TABLE 명령으로 재작성해서 보여주는 것이다.

- 칼럼의 목록과, 인덱스, 그리고 외래키 정보를 동시에 보여주기 때문에 자주 사용한다.

 

SHOW CREATE TABLE db_test;

DESC db_test;
DESCRIBE db_test;

- DESC 명령은 DESCRIBE의 약어 형태의 명령으로 둘 모두 같은 결과를 보여준다.

- DESC 명령은 테이블의 칼럼 정보를 보기 좋게 표 형태로 표시해준다. 하지만 인덱스나 외래키 그리고 테이블 자체의 속성을 보여주지는 않는다. 

 

DESC db_test;

3. 테이블 구조 변경

 

-- #1 테이블의 문자 집합 변경
ALTER TABLE db_test CHARACTER SET 'utf8';
-- #2 테이블의 스토리지 엔진 변경
ALTER TABLE db_test ENGINE=myisam;
ALTER TABLE db_test ENGINE=innodb;

 

• ALTER TABLE 문장(#2) 에 명시된 ENGINE 이 기존과 같더라도 테이블의 데이터를 복사하는 작업은 실행되기 때문에 주의해야한다.
• 스토리지 엔진을 변경하는 목적으로도 사용하지만, 테이블 데이터를 리빌드하는 목적으로도 사용한다.
• 테이블 리빌드 작업은 주로 레코드의 삭제가 자주 발생하는 테이블에 프래그엔테이션을 제거해 디스크의 공간을 줄이는 역활을한다.

※ 프래그멘테이션 (Fragmentation)

- 데이터가 저장되지 않은 빈 공간

 

※ 추가

- OPTIMIZE TABLE <테이블명> 명령어를 사용하면 InnoDB 스토리지 엔진은 내부적으로 "ALTER TABLE <테이블명> ENGINE=innodb" 명령과 동일한 작업을 수행한다.

- InnoDB 테이블 최적화란 레코드를 한 건씩 새로운 테이블에 복사함으로써 테이블의 레코드를 컴팩트하게 만들어 주는것이다.

- InnoDB 테이블에서 칼럼을 추가하거나 삭제하는 작업은 모두 이러한 테이블 리빌드 작업이 수행한다 즉 DDL을 실행하고 다시 바로 OPTIMIZE TABLE 명령을 실행할 필요가 없다는 것 이다.

 

4. 테이블 칼럼 변경

-- 칼럼 추가
ALTER TABLE <테이블명> ADD COLUMN <추가할컬럼명> <데이터타입>;
ALTER TABLE db_test2 ADD COLUMN telno VARCHAR(20) AFTER hobby;
ALTER TABLE db_test2 ADD telno VARCHAR(20) AFTER hobby;

- 칼럼을 추가하는 작업은 항상 테이블의 데이터를 새로운 테이블로 복사하는 형태로 처리한다.

- 테이블의 레코드 건수가 많아질수록 칼럼 추가 작업이 느려진다.

- ALTER TABLE 명령에서 COLUMN 키워드는 생략해도 무방하다.

- AFTER <기존칼럼명> 을 붙이면 <기존컬럼명> 뒤에 칼럼을 추가한다.

 

-- 칼럼 삭제
ALTER TABLE <테이블이름> DROP COLUMN <삭제할칼럼명>;
ALTER TABLE db_test2 DROP COLUMN telno;
ALTER TABLE db_test2 DROP telno;

- 칼럼을 삭제하는 작업도 테이블의 데이터를 새로운 테이블로 복사하면서 칼럼을 제거하는 형태로 처리하기 때문에 레코드 건수에 따라 처리 시간이 달라진다.

 

-- 칼럼명 변경
ALTER TABLE <테이블이름> CHANGE COLUMN  <이전칼럼명> <변경할 칼럼명> <변경할 칼럼 타입> 
ALTER TABLE db_test2 CHANGE COLUMN nickname nickname2 CHAR(20) NOT NULL;
ALTER TABLE db_test2 CHANGE COLUMN nickname2 nickname CHAR(20);

- 칼럼 타입이 변경되지 않는다면 기존 테이블의 칼럼과 똑같이 타입을 명시하면 된다.

- InnoDB 에서는 임시 테이블로 데이터를 복사하는 작업이 실행되기 때문에 레코드 건수에 따라 상당히 느리게 처리될 수도 있다.

 

-- 칼럼명 의외의 타입이나 NULL 여부를 변경하는 경우
ALTER TABLE <테이블이름> MODIFY COLUMN <변경할칼럼명> <데이터타입>;
ALTER TABLE db_test2 MODIFY COLUMN nickname CHAR(19) NULL;
ALTER TABLE db_test2 MODIFY COLUMN nickname CHAR(20) NOT NULL;

-- ENUM 새로운 아이템 추가
ALTER TABLE db_test2 MODIFY COLUMN hobby ENUM ('Reading', 'Game', 'Sports', 'Dev');

- 칼럼의 타입이나 NULL 여부등을 변경할 때는 CHANGE COLUMN 키워드 대신 MODIFY COLUMN 키워드를 사용해야 한다.

- ENUM이나 SET 같은 타입에 새로운 아이템이 추가될 떄는 데이터를 복사하지않고 테이블의 메타 정보만 변경하기 때문에 빠르게 처리된다.

 

5. 인덱스 추가 및 삭제

-- 인덱스 추가
ALTER TABLE <테이블명> ADD INDEX <인덱스이름> (인덱스를 구성하는 칼럼명)
ALTER TABLE db_test ADD INDEX ix_registered  (registered);

-- PK 추가
ALTER TABLE db_test ADD PRIMARY KEY (칼럼명)

-- 유니크 인덱스 추가
ALTER TABLE db_test ADD UNIQUE INDEX <인덱스이름> (인덱스를 구성하는 칼럼명)

-- 인덱스 삭제
ALTER TABLE <테이블명> DROP INDEX <삭제할인덱스명>;
ALTER TABLE db_test DROP INDEX ix_registered;

-- 인덱스 조회
SHOW INDEX FROM <테이블 이름>

- ALTER TABLE 명령의 마지막 부분에는 인덱스 이름과 인덱스를 구성하는 칼럼을 명시한다.

- PRIMARY KEY는 별도의 이름을 가질 수 없기 때문에 구성할 칼럼만 입력한다.

 

 

6. 칼럼 및 인덱스 변경을 모아서 실행

-- 단일 실행
ALTER TABLE db_test2 DROP INDEX ix_registered;
ALTER TABLE db_test2 ADD INDEX ix_hobby (hobby);
ALTER TABLE db_test2 ADD COLUMN telno VARCHAR(15);


-- 모아서 실행
ALTER TABLE db_test2 
DROP INDEX ix_registered, 
ADD INDEX ix_hobby (hobby), 
ADD COLUMN telno VARCHAR(15);

- 하나로 모아서 실행하는 방법은 각 명령으로 나눠서 실행할 때보다 빠르게 처리된다.

- 가능하다면 스키마 변경은 테이블 단위로 모아서 실행하는게 좋다.