[DB] DDL(Data Definition Language)

의혁·2024년 6월 26일

DDL Database data definition language db

[DB] 데이터베이스

목록 보기

5/6

DDL(Data Definition Language)

💡 DDL이란?
: 데이터 정의어로 테이블과 같은 데이터의 스키마를 정의하거나 수정하는데 사용되는 언어
: 스키마(schema) - 테이블의 구조(컬럼명, 자료형, 자료형 크기, 테이블명 등) 및 제약 조건(unique,primary_key, notnull, check, foreign_key)
: CREATE, ALTER, DROP, TRUNCATE, RENAME

MySQL 과 MariaDB 에서는 ENGINE을 InnoDB로 사용(스토리지 엔진)
트랜잭션을 지원하며, 롤백 및 크래시 복구 기능을 제공하여 높은 신뢰성 보장
외래 키(FK) 제약조건을 지원하여 데이터 무결성 유지
행 수준의 잠금 및 MVCC(Mulit-Version Concurrency Control)를 통해 동시성을 높이고, 락 경합을 최소화

1. CREATE

💡 새로운 테이블을 생성하는 데에 사용

💡CREATE TABLE IF (NOT) EXISTS 테이블 명
💡 DESC 테이블 명

<코드>

-- 새로운 TABLE 생성
CREATE TABLE if NOT EXITSTS tbl(
  pk INT PRIMARY KEY,
  fk INT,
  col1 VARCHAR(255),
  CHECK(col1  IN ('Y','N'))
) ENGINE = INNODB;

-- 생성한 TABLE 조회
SELECT * FROM tb1;

<실행결과>

새로운 TABLE이 생성되고, INSERT하기 전에 비어있음
IF NOT EXISTS 가 없을 경우, tb1이 이미 존재하면 오류 발생( 오류 대비하여 적어주기)
VARCHAR(variable char) = 길이에 맞게 메모리를 할당하는 문자열 (' '이 들어감)
1) 숫자, 영어, 특수기호 = 1byte(아스키코드)
- 1byte = 256(0~255)bits이고, 맨 앞 비트는 MSP(부호) 비트 연산자
2) 한글(인코딩) = 2byte(MS949) or 3byte(UTP8)

<코드>

-- 존재하는 테이블을 요약
DESC tb1;
DESCRIBE tb1;

<실행결과>

DESC(describe) 테이블명을 사용하면, 테이블에 있는 컬럼의 속성을 보여줌

3-1-1. AUTO_INCREMENT

💡 제약조건으로 값을 기억해서 자동으로 추가해주는 경우에 사용

💡 INSERT 시 PRIMARY KEY 에 해당하는 컬럼에 자동으로 번호를 발생시켜 저장

<코드>

-- AUTO_INCREMENT: 값을 기억하는 번호 발생기
CREATE TABLE tb2 (
  pk INT PRIMARY KEY AUTO_INCREMENT,
  fk INT,
  col1 VARCHAR(255),
  CHECK(col1 IN ('Y', 'N'))
) ENGINE = INNODB;

-- 테이블의 속성 조회
DESC tb2;

<실행결과>

AUTO_INCREMENT 는 CREATE 시에 PRIMARY KEY 의 제약조건으로 걸어줌.
AUTO_INCREMENT 를 걸면 NULL값을 주어도 값을 기억하고 번호를 +1씩 시키며 대입함.

<코드>

-- INSERT 문을 2번 반복 실행
INSERT
  INTO tb2
VALUES
(
  NULL						-- AUTO_INCREMENT를 적용
, 2
, 'N'
);

-- 실행 결과값 확인
SELECT * FROM tb2;
COMMIT;

<실행결과>

AUTO_INCREMET 에 의해서 NULL값을 넣어도 자동으로 +1해서 넣어줌

2. ALTER

💡 컬럼, 제약조건등을 추가/변경/수정/삭제하는 경우에 사용

💡 경우의 수
1. 컬럼 추가: ALTER TABLE 테이블명 ADD 컬럼명 컬럼정의
2. 컬럼 삭제: ALTER TABLE 테이블명 DROP COLUMN 컬럼명
3. 컬럼 이름/데이터 형식 변경: ALTER TABLE 테이블명 CHANGE COLUMN 기존컬럼명 바꿀 컬럼명 컬럼정의
4. 컬럼 제약조건 추가/삭제: ALTER TABLE 테이블명 drop 제약조건

1)컬럼 추가

1-1) 컬럼 하나 추가
<코드>

-- 컬럼 추가
ALTER TABLE tb2 ADD col2 INT NOT NULL;

-- 조회
DESC tb2;

<실행결과>

1-2) 컬럼 여러개 추가
<코드>

-- 컬럼 여러개 추가
ALTER TABLE tb2
ADD col3 DATE NOT NULL,			
ADD col4 TINYINT NOT NULL; 

-- 조회
DESC tb2;

<실행결과>

ALTER을 통해서 컬럼을 추가할 수 있음 (테이블에 새로운 컬럼을 추가한거지 값이 들어간 것은 X)

2)컬럼 삭제

<코드>

-- 컬럼 삭제
ALTER TABLE tb2 DROP COLUMN col2;

-- 조회
DESC tb2;

<실행결과>

ALTER을 통해서 컬럼을 삭제할 수 있음(= DELETE)

3) 컬럼 이름/ 컬럼 정의 변경

<코드>

-- 컬럼 이름 및 컬럼 정의 변경
ALTER TABLE tb2 CHANGE COLUMN fk change_fk INT NOT NULL;

-- 조회
DESC tb2;

<실행결과>

ALTER을 통해서 컬럼의 일정 부분을 수정가능 (= UPDATE)

4) 컬럼 제약조건 추가/제거

4-1) 컬럼 제약조건 제거
<코드>

--  제약조건 제거 (drop X -> modify)
-- AUTO_INCREMENT부터 수정 (INT AUTO_INCREMENT -> INT)
ALTER TABLE tb2 MODIFY pk INT;
-- PRIMARY KEY 제거
ALTER TABLE tb2 DROP PRIMARY KEY;

-- 조회
DESC tb2;

<실행결과>

4-2) 컬럼 제약조건 추가
<코드>

--제약조건 추가
-- PRIMARY KEY 추가
ALTER TABLE tb2
ADD PRIMARY KEY(pk);
-- AUTO_INCREMENT 추가
ALTER TABLE tb2 MODIFY pk AUTO_INCREMENT;

-- 조회
DESC tb2;

<실행결과>

3. DROP

💡 테이블을 삭제할 경우에 사용

💡 DROP TABLE IF EXISTS 테이블 명 (ex. tb3,tb4)

복수개의 테이블을 한번에 삭제도 가능

<코드>

-- 새로운 테이블 tb3 생성
CREATE TABEL IF NOT EXITS tb3 (
  pk INT PRIMARY KEY ATUO_INCREMENT,
  fk INT,
  col1 VARCHAR(255),
  CHECK(col1 IN ('Y','N'))
 ) ENGINE = INNODB;
 
 -- DROP 문을 이용하여 tb3 제가
 DROP TABEL IF EXISTS tb3;

<실행결과>

DROP문을 이용하여 테이블 전체를 삭제할 수 있음.
DROP과 동일한 역할을 하는 TRUNCATE도 존재

4 TRUNCATE

💡테이블을 초기화 하는 경우에 사용

💡 TRUNCATE (TABLE) 테이블 명

<코드>

-- tb6 테이블 생성
CREATE TABLE IF NOT EXISTS tb6 (
 pk INT AUTO_INCREMENT PRIMARY KEY,
 fk INT,
 col1 VARCHAR(255),
 CHECK(col1 IN ('Y', 'N'))
) ENGINE=INNODB;

-- 4개 행 데이터 INSERT
INSERT INTO tb6 VALUES (null, 10, 'Y');
INSERT INTO tb6 VALUES (null, 20, 'Y');
INSERT INTO tb6 VALUES (null, 30, 'Y');
INSERT INTO tb6 VALUES (null, 40, 'Y');

-- 제대로 INSERT 되었는지 확인
SELECT * FROM tb6;

-- 테이블 초기화 하기
-- TRUNCATE (TABLE) tb6;
TRUNCATE tb6;