[MySQL] DDL, DML, DCL 명령어

웹 개발을 하면서 실제로 사용은 하지만 익숙하지 않아 헷갈리는 경우가 있는 Mysql 명령어에 대해 알아보겠습니다.

이번 포스트에서는 DDL, DML, DCL 명령에 대해 정리하며 알아봅시다. 관계형 데이터베이스 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어입니다.

SQL은 DBMS에 실행할 수 있는 명령의 종류에 따라 DDL, DML, DCL 3가지로 구분됩니다.

 

목차

1. DDL(Data Definition Language)

2. DML(Data Manipulation Language)

3. DCL(Data Control Language)

 

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

 - 테이블의 구조나 관계를 생성하는 데 사용되며, CREATE, DROP, TRUNCATE, ALTER가 있습니다.

 

CREATE : CREATE 명령어는 테이블을 생성하거나 데이터베이스를 생성하는 명령어입니다.

-- 데이터베이스 생성
CREATE DATABASE [테이블명];

-- 테이블 생성
CREATE TABLE user
(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '유저 고유번호',
    name VARCHAR(30) NOT NULL COMMENT '이름',
    email VARCHAR(50) NOT NULL COMMENT '이메일',
    phone VARCHAR(11) NOT NULL COMMENT '핸드폰',
    sex VARCHAR(1) NULL COMMENT '성별'
) ENGINE=INNODB;

 - PRIMARY KEY(PK) 테이블의 기본키

 - AUTO_INCREMENT 인덱스 값 자동 증가

 - COMMENT 해당 칼럼의 설명

 - ENGINE=INNODB 데이터 저장구조 선택

 

DROP : CREATE로 생성한 테이블이나 데이터베이스를 삭제하는 명령어입니다. 

DROP TABLE [테이블명];

TRUNCATE : CREATE로 생성한 테이블에서 데이터를 삭제하는 명령어입니다.

TRUNCATE TABLE [테이블명];

ALTER : CREATEF로 생성한 테이블에서 데이터베이스의 객체를 변경하는 명령어입니다.

-- 컬럼 추가
ALTER TABLE [테이블명] ADD [추가할컬럼];

-- 컬럼 타입 수정
ALTER TABLE [테이블명] MODIFY [변경할컬럼] [변경할타입];

-- 컬럼명 수정
ALTER TABLE [테이블명] CHANGE [기존컬럼명] [변경할컬럼명];

-- 컬럼 삭제
ALTER TABLE [테이블명] DROP [삭제할컬럼명];

2. DML(Data Manipulation Language) 데이터 조작 언어

 - 테이블에 데이터 검색, 등록, 수정, 삭제하는 데 사용하며 SELECT, UPDATE, DELETE, INSERT문 등이 있습니다.

 

위에서 생성한 user 테이블을 예로 사용해 보겠습니다.

 

SELECT :  행 검색

SELECT	
	*
FROM user

UPDATE : 행 수정

UPDATE user
SET email = 'hong@naver.com'
WHERE id = 1;

INSERT : 행 등록

INSERT INTO user(id,name,email,phone,sex,address)
VALUES('1','홍길동','hong@naver.com','01012345678','남자','서울시 강남구');

DELETE : 행 삭제

DELETE FROM user WHERE id = 1

3. DCL(Data Control Language) 데이터 제어 언어

 - 데이터의 보안, 무결성, 회복, 병행 수행제 등을 정의하는 데 사용합니다.

 - 데이터베이스의 사용 권한을 관리하는 데 사용되며 GRANT, REVOKE, COMMIT, ROLLBACK 등이 있습니다.

 - COMMIT과 ROLLBACK 명령어는 TCL(Transaction Control Language)로 트랜잭션을 제어하는 명령어로 구분됩니다.

 

GRANT : 사용자 권한을 설정하는 명령어입니다.

-- 사용자 권한 부여 명령어
GRANT 권한종류 ON 대상 TO 계정명 IDENTIFIED BY 암호 [WITH GRANT OPTION];

예제)
-- 모든 권한을 가진 계정 생성
GRANT ALL ON *.* TO test@localhost IDENTIFIED BY "test1";

-- 특정 데이터베이스에 조회권한을 가진 계정 생성
GRANT SELECT ON test.* TO test@localhost IDENTIFIED BY "test1";

-- GRANT로 계정생성 후 아래의 명령어를 실행해야 적용됩니다. 
FLUSH privileges;

REVOKE : GRANT 명령어로 적용한 권한을 해제하는 명령어입니다.

-- 권한 해제 명령어
REVOKE insert, update, create ON [DB명.테이블명] TO [user@host];

-- 전체 권한 해제 명령어
REVOKE ALL ON [DB명.테이블명] TO [user@host];

COMMIT : 작업한 결과를 물리적 디스크로 저장하고, 조작 작업이 완료됨을 사용자에게 알려주는 명령어입니다.

COMMIT;

이전까지의 작업을 저장하는 명령어

 

ROLLBACK : 작업했던 내용을 원래의 상태로 복구하기 위한 명령어입니다.

ROLLBACK;

 주의점 : COMMIT 하기 이전의 상태만 ROLLBACK이 가능합니다. 만약 COMMIT 명령어를 입력하였다면 이전 상태로 되돌릴 수 없습니다.

 

 

DROP, DELETE, TRUNCATE 명령어의 차이점

• DELETE 명령어는 데이터는 지워지지만 테이블 용량은 줄어들지 않습니다. 원하는 데이터만 지울 수 있고, 삭제 후 잘못 삭제한 것을 되돌릴 수 있습니다.
• TRUNCATE 명령어는 용량이 줄어들고, 인덱스 등도 모두 삭제됩니다. 테이블은 삭제하지 않고 데이터만 삭제하는 특징이 있습니다. 중요한 점은 삭제 후 절대 되돌릴 수 없으니 주의하여야 합니다.
• DROP 명령어는 테이블 전체를 삭제합니다. TRUNCATE와 마찬가지로 삭제 후 절대 되돌릴 수 없습니다.