데이터베이스 (DDL)

오늘은 데이터베이스에 대한 복습을 진행을 해볼 것이다.

DDL(Data Definition Language)은 데이터베이스의 구조를 정의 및 수정하는데 사용되는 명령어들이다. 주로 데이터베이스 객체의 생성, 수정, 삭제 등에 대한 명령어들이 포함되어있다.

구성으로는 CREATE, ALTER, DROP, RENAME, TRUNCATE 등이 존재한다. 그럼 이제 하나하나씩 실습을 통해서 확인을 해보자.

작업 환경은 MySQL로 진행을 한다. 보통 명령어는 대문자를 사용하던데, 개인적으로 소문자가 더 편하기 때문에 소문자를 차용한다. 그리고 MySQL에선 명령어에 대한 대소문자 구별을 따로 안하기 때문에 혹여나 다른 SQL QUERY를 사용할 땐 대소문자를 유의해야 한다.

 

CREATE

-- Students 테이블 생성
CREATE TABLE Students (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    StudentName VARCHAR(100) NOT NULL,
    BirthDate DATE,
    Gender VARCHAR(10),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Subjects 테이블 생성
CREATE TABLE Subjects (
    SubjectID INT PRIMARY KEY AUTO_INCREMENT,
    SubjectName VARCHAR(100) NOT NULL,
    SubjectDescription TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Scores 테이블 생성
CREATE TABLE Scores (
    ScoreID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    SubjectID INT,
    Score DECIMAL(5, 2) NOT NULL,
    ExamDate DATE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

그림 1. MySQL create 실습

create 명령어는 간단하게 테이블 객체를 생성하는 명령어이며, create 의 괄호 안에는 각 속성에 대한 설정과 제약조건을 설정하는 공간으로 구성이 되어있다. 얼추 보이는 것만 간단하게 설명을 해보자면,

INT, VARCHAR, TEXT, DATE, DATETIME,DECIMAL(5,2) 등 등 속성에 대한 데이터 타입을 설정해주는 것이다.

PRIMARY KEY : 기본키

AUTO_INCREMENT : 다음 튜플로 갈 때마다 +1

NOT NULL : NULL 값 허용 안됨

기타 등 등. 

외래키(FOREIGN KEY)를 해석해보면 Scores 테이블에 있는 StudentID 와 Subjects 테이블에 있는 StudentID를 참조하고,  둘 중 한 곳에 delete하거나 update를 진행하면 나머지도 연동이 되게(cascade)를 설정해 놓았다. SubjectID는 Subjects 테이블에서 사라지면 Scores 테이블에서는 NULL로 지정이 되고(SET NULL), update 시에는 똑같이 연동이 되게 설정이 되어있다.(cascade)

그림 2. Foreign Key 설정

ALTER

alter문은 보통 테이블 구조를 변경(컬럼 추가, 변경, 삭제, 제약 조건 추가 등)을 할 때 사용이 된다.

alter table Scores add Grade varchar(2) after Score;

여기 테이블의 구조를 바꾸는 간단한 ALTER 문이 있다.

Scores 테이블에 (table Scores)

varchar(2) 데이터 형태의 Grade란 이름을 ( add Grade varchar(2) )

Score이란 컬럼 뒤에다가 위치를 시키는 것이다. ( after Score)

 

그림 3. ALTER 문 적용 전

그림 4. alter문 적용 후

그 외에도 컬럼 타입을 변경하는 MODIFY, 제약 조건을 추가 등을 할 수 있다.

alter table Students modify Gender enum('Male', 'Female', 'Other') not null;

alter table Subjects add constraint unique_subject_name unique (SubjectName);

 

DROP

drop 문은 데이터베이스 객체를 완전히 삭제할 때 사용되는 명령어이다.

데이터 베이스 객체에는 데이터베이스, 테이블, 뷰, 인덱스, 외래키 제약조건 등이 포함이 된다.

주의해야 될 점은 한 번 실행되면 모든 데이터와 구조가 삭제되고 복구가 불가능하니 조심해서 사용을 해야된다.

 

drop table Scores;

 

해당 쿼리는 Scores라는 테이블을 삭제 시킨다. 

drop database temp;

그 외에도 DATABASE를 드랍할 때도 drop 명령어가 사용된다.

drop table if exists Scores;

테이블이 있는 경우에만 실행이 되게끔 (if exists) 설정이 가능하다.

drop table Subjects;

Subjects 테이블을 삭제를 하려 하지만 안된다는 것을 알 수 있다. 그 이유는 해당 테이블에 외래키 제약이 걸려 있으면 먼저 제약을 삭제하거나 참조 관계를 해제를 해야되기 때문이다.

그림 5. MySQL에서 DROP문 실행 불가

MySQL에서 이 방법을 해결하기 위해서는 두 가지의 방법이 존재한다. 

첫 번째는 외래키 제약 검증을 일시적으로 해제하는 것이다.

set foreign_key_checks = 0;
-- 이후 삭제 작업 실행
set foreign_key_checks = 1;

삭제 후에는 다시 복구를 진행해야지 무결성이 유지가 된다. 해당 설정은 세션 단위로 진행이 되기 때문에 다시 접속하면 초기화 된다.

 

두 번째는 특정 외래키 제약을 삭제하는 법이다.

show create table Scores;

외래키의 제약 이름은 MySQL에서 자동 생성을 해주기 때문에 위의 쿼리를 통하여 먼저 확인을 해줘야 한다.

그림 6. 제약조건 이름 확인

그럼 score_ibfk_1 이란 제약 이름을 확인 할 수 잇다.

alter table Scores drop foreign key scores_ibfk_1;

그림 7. 깔끔한 삭제 완료

성공적으로 테이블을 삭제 할 수 있다고 뜬다.

 

Rename

rename 문은 데이터베이스 객체의 이름을 변경할 때 사용한다.

rename table Students to StudentInfo;

기존 이름 Students 에서 StudentInfo 로 바꾸는 쿼리문이다.

그림 8. Rename 확인

그림 9. 변경된 테이블 명

rename table StudentInfo to Students, Subjects To CourseInfo;

,(comma)를 통해서 여러 개의 테이블 이름을 한 번에 바꿀 수 있고, 

rename table temp.Scores to temp2.Scores_2;

다른 테이블로 옮기면서 이름을 변경 할 수도 있다.

 

Truncate

truncate 문은 테이블의 모든 데이터를 한 번에 삭제하는 명령어이다.

delete 문은 행 단위로 지우는 형태이고, truncate는 테이블을 초기화 하는 방식이라고 보면 된다. 그래서 auto_increment같은 값도 초기화 되어버린다. 아예 싹 제로 베이스로 초기화 되는 건 아니고, 구조(컬럼, 제약 조건 등)은 유지가 된다. 다만 롤백은 안되므로 사용 시 주의를 해야한다.

 

그림 10. 임의의 데이터

 

 

그림 11. 초기화된 테이블

이렇게 데이터가 초기화 됐다는 것을 알 수 있다.

 

외에도 여러가지 명령어가 있겠지만 일단 DDL의 정리는 요기까지 하는 걸로 한다.