[SQLD] 5-2: 데이터 정의어(DDL) & 데이터 제어어(DCL)

 

이전 포스트:

2025.02.22 - [자격증/SQLD] - [SQLD] 5-1: 데이터 조작어 (DML) 와 트랜잭션 제어(TCL)

[SQLD] 5-1: 데이터 조작어 (DML) 와 트랜잭션 제어(TCL)

 

---

SQLD 시험 대비: 데이터 정의어(DDL) & 데이터 제어어(DCL)

---

1. DDL(Data Definition Language)이란?

DDL(Data Definition Language, 데이터 정의어)은 데이터베이스의 구조를 정의하는 SQL 명령어입니다. DML이 데이터를 조작하는 역할을 한다면, DDL은 테이블, 뷰, 인덱스 등의 데이터 객체를 생성(CREATE), 수정(ALTER), 삭제(DROP)하는 역할을 합니다.

DDL은 데이터베이스의 스키마(schema)를 변경하는 작업이므로, 일반적으로 트랜잭션이 적용되지 않으며, 실행 즉시 변경 사항이 반영됩니다.

DDL의 주요 특징

• 데이터베이스의 구조(스키마)를 정의 및 변경하는 역할을 함
• COMMIT, ROLLBACK이 적용되지 않으며, 실행 즉시 변경 사항이 반영됨
• 주로 DBA(데이터베이스 관리자)가 사용
• 테이블, 뷰, 인덱스, 시퀀스 등의 객체를 생성, 수정, 삭제하는 데 활용됨
• 데이터의 저장 구조를 결정하며, 성능 최적화와 관련된 작업에도 사용됨
• 무결성 제약 조건(Integrity Constraints)을 설정하여 데이터의 정확성과 일관성을 유지 가능
• 테이블 간 관계를 정의할 수 있어 관계형 데이터베이스 설계에 필수적인 개념

 

---

2. DDL 주요 명령어 및 예제

CREATE	테이블, 뷰, 인덱스 등을 생성
ALTER	기존 객체(테이블, 컬럼, 인덱스 등)를 수정
DROP	데이터베이스 객체를 삭제
TRUNCATE	테이블의 모든 데이터를 빠르게 삭제 (DML의 DELETE와 차이점 존재)
RENAME	테이블 및 데이터베이스 객체의 이름 변경
COMMENT	테이블이나 컬럼에 설명 추가
CONSTRAINT	테이블의 제약 조건(Primary Key, Foreign Key 등) 설정
INDEX	특정 컬럼에 대한 검색 성능을 향상시키기 위한 인덱스 생성

 

2.1 CREATE - 데이터 객체 생성

CREATE 문은 새로운 데이터 객체(테이블, 뷰, 인덱스 등)를 생성하는 데 사용됩니다.

 

(1) 테이블 생성 기본 문법

CREATE TABLE 테이블명 (
    컬럼명 데이터타입 [제약조건],
    컬럼명 데이터타입 [제약조건],
    ...
);

 

(2) 예제 - 테이블 생성

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30),
    salary DECIMAL(10,2),
    hire_date DATE DEFAULT CURRENT_DATE,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

 

✅ 시험 대비 포인트:

• PRIMARY KEY를 설정하여 emp_id가 고유한 값이 되도록 함
• NOT NULL을 설정하여 name 컬럼에 반드시 값이 입력되도록 지정
• DEFAULT를 사용하여 hire_date 기본값을 현재 날짜로 설정 가능
• FOREIGN KEY를 사용하여 테이블 간 관계를 설정할 수 있음 (시험에 자주 출제 하단 내용 참조)

 

2.1.1 FOREGIN KEY 옵션 정리

 

📌 DELETE (MODIFY) Action

1. Cascade
   • Master 삭제 시 Child도 함께 삭제됨.
   • 부모 테이블의 데이터가 삭제되면, 이를 참조하는 자식 테이블의 데이터도 자동으로 삭제됨.
2. Set Null
   • Master 삭제 시 Child의 해당 필드 값을 NULL로 변경.
   • 부모 데이터가 삭제되면, 자식 테이블의 외래 키 값이 NULL로 설정됨.
3. Set Default
   • Master 삭제 시 Child의 해당 필드 값을 기본값(Default)으로 설정.
   • 부모 데이터가 삭제되면, 자식 테이블의 외래 키 값이 미리 지정된 기본값으로 변경됨.
4. Restrict (부서-사원 관계와 유사)
   • Child 테이블에 PK 값이 남아있는 경우 Master 삭제 불가.
   • 부모 데이터가 삭제되기 전에 자식 테이블에서 해당 데이터를 먼저 삭제해야 함.

📌 INSERT Aciton

1. Automatic
   • Master 테이블에 PK가 입력되면 자동으로 Child 테이블에 연관된 행이 삽입됨.
   • 예를 들어, 부모(마스터) 테이블의 기본 키(PK)가 생성될 때, 자식(차일드) 테이블에도 자동으로 관련된 데이터가 추가됨.
2. Set Null
   • Master 테이블에 PK가 없는 경우, Child 테이블의 외래 키를 NULL 값으로 처리함.
   • 부모 키 값이 설정되지 않은 경우, 자식 테이블에서 해당 외래 키 컬럼을 NULL로 유지.
3. Set Default
   • Master 테이블에 PK가 없는 경우, Child 테이블의 외래 키를 기본값으로 설정함.
   • 부모 키 값이 없을 경우, 미리 정의된 기본값(DEFAULT)을 사용하여 자식 테이블의 외래 키를 설정.
4. Dependent (부서-사원 관계와 유사)
   • Master 테이블에 PK가 없으면 Child 테이블 입력이 제한됨.
   • 즉, 부모 테이블에 먼저 기본 키 값이 존재해야 하며, 그렇지 않으면 자식 테이블에 데이터가 추가될 수 없음.
5. No Action
   • 참조 무결성을 위반하는 삽입 시 아무런 조치를 취하지 않음.
   • 부모 테이블에 해당 키가 없는데 자식 테이블에 삽입하려고 하면 제약 조건 위반 오류가 발생할 수도 있음.

---

2.2 ALTER - 데이터 객체 수정

ALTER 문은 기존 테이블 또는 데이터베이스 객체를 수정하는 명령어입니다.

 

(1) 기본 문법

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입;
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 새컬럼명;

 

(2) 예제 - 테이블 수정

ALTER TABLE employees ADD hire_date DATE;
ALTER TABLE employees MODIFY salary DECIMAL(12,2);
ALTER TABLE employees DROP COLUMN department;
ALTER TABLE employees RENAME COLUMN name TO full_name;

 

✅ 시험 대비 포인트:

• ADD를 사용하여 새로운 컬럼을 추가할 수 있음
• MODIFY를 사용하여 기존 컬럼의 데이터 타입을 변경할 수 있음
• DROP COLUMN을 사용하면 특정 컬럼을 삭제할 수 있음
• RENAME COLUMN을 사용하여 컬럼명을 변경할 수 있음

 

---

2.3 DROP - 데이터 객체 삭제

DROP 문은 테이블, 뷰, 인덱스 등 데이터베이스 객체를 완전히 삭제하는 데 사용됩니다.

(1) 기본 문법

DROP TABLE 테이블명;
DROP VIEW 뷰명;
DROP INDEX 인덱스명;

 

(2) 예제 - 테이블 삭제

DROP TABLE employees;

 

✅ 시험 대비 포인트:

• DROP을 실행하면 해당 객체가 완전히 삭제되며, 복구할 수 없음
• 테이블을 삭제하면 해당 테이블의 모든 데이터도 함께 삭제됨

 

---

2.4 TRUNCATE - 테이블 초기화

TRUNCATE 문은 테이블의 모든 데이터를 빠르게 삭제하는 명령어입니다.

 

(1) 기본 문법

TRUNCATE TABLE 테이블명;

(2) 예제

TRUNCATE TABLE employees;

 

✅ 시험 대비 포인트:

• DELETE와 달리 트랜잭션 로그를 최소화하여 빠르게 동작함
• WHERE 절을 사용할 수 없으며, 테이블의 모든 데이터를 삭제함
• ROLLBACK이 불가능하며, 실행 즉시 변경 사항이 반영됨

 

---

2.5 RENAME - 테이블 이름 변경

RENAME 명령어는 테이블 또는 기타 데이터베이스 객체의 이름을 변경하는 데 사용됩니다.

 

기본 문법

ALTER TABLE 기존_테이블명 RENAME TO 새_테이블명;

예제

ALTER TABLE employees RENAME TO staff;

✅ 주의할 점:

• 테이블의 이름을 변경하면 기존 데이터를 유지한 채 이름만 변경됨
• 기존 테이블 이름이 외부 키(Foreign Key)로 참조될 경우, 테이블 이름을 변경할 수 없음

 

---

2.6. COMMENT - 설명 추가

COMMENT 명령어는 테이블이나 컬럼에 설명을 추가하는 데 사용됩니다. 주로 데이터베이스 문서화 목적으로 사용됩니다.

 

기본 문법

COMMENT ON TABLE 테이블명 IS '테이블 설명';
COMMENT ON COLUMN 테이블명.컬럼명 IS '컬럼 설명';

예제

COMMENT ON TABLE employees IS '직원 정보를 저장하는 테이블';
COMMENT ON COLUMN employees.salary IS '직원의 급여 정보';

 

✅ 주의할 점:

• COMMENT는 테이블과 컬럼에만 적용 가능하며, 실제 데이터에는 영향을 주지 않음
• 일부 DBMS(MySQL 등)에서는 COMMENT를 테이블 생성 시 COMMENT='...' 형식으로 사용해야 할 수도 있음

| CONSTRAINT | 테이블의 제약 조건(Primary Key, Foreign Key 등) 설정 |

---

2.7 CONSTRAINT - 제약 조건 설정

CONSTRAINT 명령어는 테이블의 무결성을 유지하기 위해 제약 조건을 설정하는 데 사용됩니다.

주요 제약 조건에는 PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, NOT NULL이 있습니다.

 

기본 문법

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건_이름 제약조건 (컬럼명);

예제

ALTER TABLE employees ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id);
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

 

✅ 주의할 점:

• FOREIGN KEY는 참조 무결성을 유지하며, 삭제/수정 시 제한이 적용될 수 있음
• CHECK 제약 조건은 데이터 입력 시 유효성 검사를 수행함
• UNIQUE는 중복값 입력을 방지함

 

---

2.8 INDEX - 검색 성능 향상

INDEX 명령어는 데이터 검색 속도를 향상시키기 위해 특정 컬럼에 대해 인덱스를 생성하는 데 사용됩니다.

 

기본 문법

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);

 

예제

CREATE INDEX idx_salary ON employees (salary);

 

✅ 주의할 점:

• 인덱스는 SELECT 성능을 향상시키지만, INSERT, UPDATE, DELETE 성능을 저하시킬 수 있음
• 적절한 컬럼에 인덱스를 적용해야 하며, 너무 많은 인덱스는 오히려 성능 저하를 유발할 수 있음

 

---

3. DCL(Data Control Language)이란?

DCL(Data Control Language, 데이터 제어어)은 데이터베이스의 보안과 접근 권한을 관리하는 SQL 명령어입니다. DCL은 데이터베이스의 사용자가 특정 데이터에 대해 읽기(SELECT), 수정(INSERT, UPDATE, DELETE), 실행(EXECUTE) 등의 작업을 수행할 수 있도록 권한을 부여하거나 제한하는 역할을 합니다.

 

SQL에서 데이터의 무결성과 보안을 유지하는 것은 매우 중요한 요소이며, DCL을 사용하면 특정 사용자가 허가된 작업만 수행하도록 제어할 수 있습니다. 일반적으로 DCL은 데이터베이스 관리자(DBA)가 사용하며, 보안 및 권한 관리를 위한 핵심적인 역할을 합니다.

 

DCL의 주요 특징

• 특정 사용자 또는 역할(Role)에 데이터베이스 객체에 대한 권한을 부여 또는 제거할 수 있음
• 데이터베이스의 보안과 무결성을 유지하는 데 필수적
• 트랜잭션과 함께 사용되며 COMMIT, ROLLBACK을 통해 확정 또는 취소 가능
• 주로 DBA(데이터베이스 관리자)가 관리하며, 일반 사용자는 직접 사용할 일이 많지 않음

---

4. DCL 주요 명령어 및 예제

 

GRANT	특정 사용자 또는 역할(Role)에게 권한을 부여
REVOKE	특정 사용자 또는 역할(Role)에게 부여된 권한을 회수
DENY	특정 사용자에게 특정 작업을 거부 (일부 DBMS에서 지원)
AUDIT	데이터베이스 작업을 감사하고 로그를 생성하는 기능 (일부 DBMS에서 지원)

 

4.1 GRANT 문 - 권한 부여

 

GRANT 문은 특정 사용자 또는 역할(Role)에게 데이터베이스 객체에 대한 접근 권한을 부여하는 데 사용됩니다.

(1) 기본 문법

GRANT 권한목록 ON 객체 TO 사용자 [WITH GRANT OPTION];

(2) 주요 권한 유형

권한 유형	설명
SELECT	테이블에서 데이터를 조회할 수 있는 권한
INSERT	테이블에 새로운 데이터를 삽입할 수 있는 권한
UPDATE	기존 데이터를 수정할 수 있는 권한
DELETE	데이터를 삭제할 수 있는 권한
EXECUTE	저장 프로시저 및 함수를 실행할 수 있는 권한
ALL PRIVILEGES	해당 객체에 대한 모든 권한 부여

 

(3) 예제 - 특정 사용자에게 권한 부여

GRANT SELECT, INSERT ON employees TO user1;

 

✅ 설명: user1에게 employees 테이블에 대한 SELECT(조회) 및 INSERT(삽입) 권한을 부여합니다.

 

 

GRANT ALL PRIVILEGES ON employees TO admin_user WITH GRANT OPTION;

 

✅ 설명: admin_user에게 employees 테이블의 모든 권한을 부여하며, 다른 사용자에게도 권한을 부여할 수 있도록 WITH GRANT OPTION을 추가하였습니다.

 

---

4.2 GRANT OPTION이란?

GRANT OPTION은 GRANT 명령어를 사용할 때 특정 사용자가 부여받은 권한을 다른 사용자에게도 부여할 수 있도록 허용하는 옵션입니다. 즉, WITH GRANT OPTION을 추가하면 해당 사용자가 자신이 받은 권한을 제3자에게 다시 부여할 수 있는 권한을 가지게 됩니다.

---

(1) GRANT OPTION 기본 문법

GRANT 권한목록 ON 객체 TO 사용자 WITH GRANT OPTION;

• 권한목록 : SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 등 부여할 권한을 지정
• 객체 : 테이블, 뷰, 프로시저 등 권한을 부여할 대상
• 사용자 : 권한을 받을 사용자
• WITH GRANT OPTION : 권한을 받은 사용자가 다른 사용자에게도 동일한 권한을 부여할 수 있도록 허용

---

(2) GRANT OPTION 예제

 

* 기본 예제 - 특정 사용자에게 권한 부여

GRANT SELECT, INSERT ON employees TO user1 WITH GRANT OPTION;

 

✅ 설명:

• user1에게 employees 테이블에 대한 SELECT 및 INSERT 권한을 부여
• WITH GRANT OPTION이 포함되어 있어, user1이 다른 사용자(user2 등)에게도 동일한 권한을 부여할 수 있음

 

* user1이 다른 사용자에게 권한을 부여하는 경우

GRANT SELECT, INSERT ON employees TO user2;

 

✅ 설명:

• user1이 GRANT OPTION을 가지고 있기 때문에, 자신이 부여받은 SELECT와 INSERT 권한을 user2에게 부여 가능

 

* user1의 권한을 회수하면 어떻게 될까?

REVOKE SELECT, INSERT ON employees FROM user1;

 

✅ 설명:

• user1이 SELECT, INSERT 권한을 회수당하면, user1이 부여했던 user2의 권한도 자동으로 회수됨
• 즉, GRANT OPTION을 사용하여 부여된 권한은 원래 권한을 부여받은 사용자(user1)가 권한을 잃으면, 해당 사용자가 부여했던 모든 사용자(user2 등)도 해당 권한을 잃게 됨

---

(3) GRANT OPTION 주의사항

 

✅ GRANT OPTION을 받은 사용자는 자신이 가진 권한만 부여 가능

• 예를 들어, SELECT와 INSERT만 부여받은 경우, 다른 사용자에게도 동일한 SELECT, INSERT만 줄 수 있으며 DELETE 같은 추가 권한을 줄 수 없음

✅ GRANT OPTION은 계단식으로 작동

• 권한을 받은 사용자가 또 다른 사용자에게 권한을 부여할 경우, 최초 권한이 취소되면 연쇄적으로 모든 부여된 권한이 사라짐

✅ GRANT OPTION을 제거하려면 REVOKE WITH GRANT OPTION을 사용해야 함

• REVOKE 문을 사용할 때 WITH GRANT OPTION만 제거할 수도 있음

REVOKE GRANT OPTION FOR SELECT, INSERT ON employees FROM user1;

✅ 설명:

• user1은 여전히 SELECT, INSERT 권한을 가지고 있지만, 더 이상 다른 사용자에게 해당 권한을 부여할 수 없음

 

---

4.2 REVOKE 문 - 권한 회수

REVOKE 문은 이미 부여된 권한을 특정 사용자 또는 역할로부터 회수하는 데 사용됩니다.

 

(1) 기본 문법

REVOKE 권한목록 ON 객체 FROM 사용자;

 

(2) 예제 - 특정 사용자의 권한 회수

REVOKE SELECT, INSERT ON employees FROM user1;

 

✅ 설명: user1에게 부여된 employees 테이블의 SELECT 및 INSERT 권한을 제거합니다.

 

 

REVOKE ALL PRIVILEGES ON employees FROM admin_user;

 

✅ 설명: admin_user가 가지고 있던 employees 테이블의 모든 권한을 회수합니다.

 

 

---

4.3 DENY 문 - 권한 거부 (일부 DBMS에서 지원)

DENY 문은 특정 사용자에게 특정 작업을 명시적으로 **거부(차단)**하는 데 사용됩니다. 일부 DBMS(Microsoft SQL Server 등)에서 지원됩니다.

(1) 기본 문법

DENY 권한목록 ON 객체 TO 사용자;

 

(2) 예제 - 특정 사용자에게 권한 거부

DENY SELECT ON employees TO user1;

 

✅ 설명: user1이 employees 테이블의 데이터를 조회할 수 없도록 설정합니다.

 

❗ DENY와 REVOKE의 차이점

• REVOKE는 사용자가 기존에 가지고 있던 권한을 제거하는 것이며, 이후 다시 부여될 수 있음
• DENY는 특정 작업을 명시적으로 거부하며, GRANT로도 해당 권한을 부여할 수 없음 (우선순위가 더 높음)

 

---

4.4 AUDIT 문 - 감사 및 로깅 (일부 DBMS에서 지원)

AUDIT 문은 특정 테이블이나 사용자의 데이터베이스 작업을 감사하고 로그를 생성하는 기능을 제공합니다. 일부 엔터프라이즈 데이터베이스 시스템에서 지원됩니다.

 

(1) 기본 문법

AUDIT 권한목록 ON 객체 BY 사용자;

 

(2) 예제 - 특정 사용자의 데이터 액세스 로깅

AUDIT SELECT ON employees BY user1;

 

✅ 설명: user1이 employees 테이블에서 데이터를 조회할 때 감사 로그를 기록합니다.

 

 

---

5. 마무리 및 SQLD 시험 대비 TIP

 

DDL과 DCL은 데이터베이스의 구조와 보안을 유지하는 데 필수적인 요소입니다. DDL은 데이터베이스 객체(테이블, 뷰, 인덱스 등)의 생성 및 수정을 담당하며, DCL은 사용자 권한을 관리하고 데이터 접근을 제어하는 역할을 합니다.

SQLD 시험에서는 DDL과 DCL의 기능 차이점과 실제 SQL 문법 적용 사례가 출제될 가능성이 높으므로, 아래 핵심 사항을 정리하여 대비하세요.

 

✅ SQLD 시험 대비 핵심 정리

1. DDL(Data Definition Language) 핵심 정리
   • CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT, CONSTRAINT, INDEX의 역할을 숙지
   • DROP과 TRUNCATE의 차이점 이해 (DROP은 구조까지 삭제, TRUNCATE는 데이터만 삭제)
   • ALTER TABLE을 활용한 컬럼 추가, 수정, 삭제 방법 익히기
   • FOREIGN KEY, PRIMARY KEY, CHECK 등의 제약 조건을 사용하여 데이터 무결성을 유지하는 방법 숙지
2. DCL(Data Control Language) 핵심 정리
   • GRANT와 REVOKE의 차이점 파악 (GRANT는 권한 부여, REVOKE는 권한 회수)
   • WITH GRANT OPTION을 사용하면 부여된 사용자가 다른 사용자에게도 권한을 줄 수 있음
   • DENY는 GRANT보다 우선순위가 높아 명시적으로 특정 권한을 차단할 수 있음
   • AUDIT을 활용한 데이터 접근 로깅 개념 숙지 (일부 DBMS에서만 지원됨)
3. SQL 문법을 직접 연습하여 이해도 향상
   • CREATE TABLE, ALTER TABLE, GRANT, REVOKE 등의 명령어를 직접 작성하고 실행해 보기
   • FOREIGN KEY와 같은 관계 설정이 어떻게 작동하는지 실습하여 개념 확립
   • 권한을 부여하고 철회하는 GRANT, REVOKE 문을 사용해 특정 사용자의 접근 제한을 테스트
4. SQLD 시험에서 자주 나오는 개념 정리
   • DROP TABLE employees;를 실행하면 테이블 자체가 삭제되므로 복구가 불가능함
   • TRUNCATE TABLE employees;를 실행하면 모든 데이터가 삭제되지만 테이블 구조는 유지됨
   • GRANT SELECT ON employees TO user1;은 user1이 employees 테이블을 조회할 수 있도록 허용함
   • REVOKE SELECT ON employees FROM user1;은 user1의 employees 테이블 조회 권한을 제거함
   • DENY SELECT ON employees TO user1;은 user1이 GRANT를 받더라도 employees 테이블을 조회할 수 없도록 차단

 

 

 

---

 

💡 도움이 되셨다면 댓글과 공감 부탁드립니다! 😊
📌 더 많은 알고리즘 풀이와 프로그래밍 자료는 블로그에서 확인하세요!
✉️ 문의나 피드백은 댓글이나 이메일로 남겨주세요.