SQL DDL과 DCL - 테이블 정의와 권한 관리

DDL / DCL 학습 흐름 쌩기초

이 페이지는 SQL의 구조 관리 명령과 권한 관리 명령을 두 영역으로 나누어 봅니다.

DDL은 테이블 구조를 만드는 쪽, DCL은 사용자 권한을 다루는 쪽입니다. 시험에서는 두 영역이 같은 SQL 분류 문제로 묶여 나오지만, 실제 풀이에서는 "구조를 바꾸는가, 권한을 바꾸는가"를 먼저 나누면 문법을 훨씬 빠르게 고를 수 있습니다.

SQL 분류 핵심 정리

SQL(Structured Query Language)은 데이터베이스¹를 다루기 위한 언어입니다. 크게 3가지로 분류됩니다.

DDL - 데이터 정의어 쌩기초

DDL(Data Definition Language)은 테이블, 뷰³ 등 데이터베이스 구조(스키마)를 만들고, 바꾸고, 없애는 명령어입니다.

아래는 DDL에서 사용 가능한 명령어입니다. 테이블을 다루는 CREATE TABLE·ALTER TABLE·DROP·TRUNCATE를 먼저 보고, 마지막으로 조회 성능을 위한 CREATE INDEX·DROP INDEX를 살펴봅니다. 각 명령어의 역할과 문법을 순서대로 다룹니다.

CREATE TABLE - 테이블 생성 기초

여기서 컬럼명⁴은 테이블의 각 열 이름, 데이터타입⁵은 그 열에 저장할 값의 형식, [제약조건]⁶은 입력 값에 적용할 규칙입니다. [ ]로 감싸진 항목은 생략 가능한 선택 요소입니다.

자주 사용되는 제약조건:

예시:

제약조건에 이름 붙이기:

위 예시처럼 컬럼 옆에 바로 적는 대신, 컬럼을 모두 정의한 뒤 줄을 따로 만들어 제약조건을 선언할 수도 있습니다. 이때 맨 앞에 CONSTRAINT 제약조건명을 붙이면 그 제약조건에 이름을 줄 수 있는데, 이렇게 이름을 가진 제약조건을 named constraint라고 합니다.

• CONSTRAINT PK_회원 PRIMARY KEY (회원ID) : 회원ID를 기본키로 지정하고, 그 제약조건에 PK_회원이라는 이름을 붙입니다.
• CONSTRAINT UQ_이메일 UNIQUE (이메일) : 이메일이 중복되지 않도록 하는 UNIQUE 제약조건에 UQ_이메일이라는 이름을 붙입니다.
• CONSTRAINT CHK_등급 CHECK (등급 IN ('일반', 'VIP', 'VVIP')) : 등급이 세 값 중 하나여야 한다는 CHECK 제약조건에 CHK_등급이라는 이름을 붙입니다.

CONSTRAINT 이름을 적는 위치만 다를 뿐, 제약조건의 의미는 컬럼 옆에 적는 것과 같습니다. 이름을 붙여두면 나중에 ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명으로 특정 제약조건만 골라 삭제·관리하기 좋습니다. 외래키도 같은 방식으로 CONSTRAINT 이름 FOREIGN KEY (컬럼) REFERENCES 부모테이블 (컬럼)처럼 이름을 붙입니다.

ALTER TABLE - 테이블 수정 기초

테이블의 구조(컬럼)를 변경할 때 사용합니다. ADD는 새 컬럼을 추가, MODIFY는 기존 컬럼의 데이터타입이나 제약조건을 변경, DROP COLUMN은 컬럼을 삭제합니다.

컬럼을 삭제할 때는 DROP COLUMN을 사용합니다. 여기서 COLUMN은 SQL 표준(ISO/IEC 9075)과 PostgreSQL·MySQL에서는 생략 가능한 선택 키워드라, ALTER TABLE 학생 DROP 과목;처럼 COLUMN을 빼도 동작합니다. 다만 SQL Server 등 일부 DBMS⁸에서는 COLUMN이 필수입니다. 표준에서는 둘 다 허용하므로 기호에 맞게 쓰면 됩니다.

DROP - 테이블/뷰 삭제 기초

테이블이나 뷰를 구조째로 삭제합니다. 테이블에 저장된 데이터도 모두 함께 사라집니다.

CASCADE vs RESTRICT:

CASCADE와 RESTRICT는 삭제 대상 테이블이나 뷰를 다른 테이블(또는 뷰)이 참조하고 있을 때 어떻게 처리할지를 지정합니다. 옵션을 생략하면 RESTRICT가 적용됩니다.

시나리오 예시:

직원 테이블의 회사코드 컬럼이 회사 테이블의 기본키를 외래키로 참조하고 있는 상황을 가정합니다.

TRUNCATE - 테이블 데이터 전체 삭제 기초

테이블 구조는 유지하면서 모든 데이터(행)만 삭제합니다.

TRUNCATE 전/후 비교:

TRUNCATE 전 학생 테이블:

TRUNCATE 후 학생 테이블:

컬럼 구조(학번, 이름, 학년)는 그대로 남아 있고, 저장된 데이터만 모두 사라집니다.

CREATE INDEX - 인덱스 생성 기초

인덱스(index, 색인)는 책 뒤의 '찾아보기'와 같습니다. 찾아보기가 본문과 따로 붙어 있듯, 인덱스도 테이블과는 별도로 저장되어 특정 컬럼 값이 어느 행에 있는지를 빠르게 알려줍니다.

데이터를 찾아 읽는 조회(SELECT) 속도를 높이려고 만들며, 인덱스를 추가하거나 지워도 테이블의 실제 데이터는 바뀌지 않습니다. 인덱스가 내부적으로 어떤 자료구조(B-트리·해싱 등)로 동작하는지는 인덱스 접근 구조에서 다룹니다. 여기서는 인덱스를 만들고 없애는 DDL 문법만 봅니다.

[UNIQUE]는 생략 가능한 선택 요소입니다. 붙이면 해당 컬럼에 같은 값을 두 번 넣을 수 없게 막는데(이미 있는 값을 또 넣으면 오류로 거부됩니다), 앞서 본 UNIQUE 제약조건과 효과가 비슷하면서 검색 속도까지 빨라집니다. 컬럼을 하나만 적으면 그 컬럼에 대한 인덱스가, 여러 개 적으면 그 컬럼들을 한 묶음으로 본 복합 인덱스가 만들어집니다. 복합 인덱스는 여러 컬럼을 함께 조건으로 자주 검색할 때 유용합니다.

예시:

DROP INDEX - 인덱스 삭제 기초

만들어 둔 인덱스를 삭제할 때는 DROP INDEX를 사용합니다. 인덱스 이름만 적으면 되고(테이블명은 쓰지 않습니다), 인덱스만 사라질 뿐 테이블과 그 안의 데이터는 그대로 유지됩니다.

DCL - 데이터 제어어 쌩기초

데이터베이스에는 여러 사용자(DBA⁹, 일반 사용자, 애플리케이션 계정 등)가 접근합니다. 사용자마다 허용할 작업 범위가 다르므로, 권한을 세밀하게 제어할 필요가 있습니다. DCL(Data Control Language)은 이러한 사용자 권한을 부여하거나 회수하는 명령어입니다.

GRANT - 권한 부여 기초

권한 종류:

예시:

WITH GRANT OPTION - 권한 재부여 허용 심화

GRANT 구문 끝에 WITH GRANT OPTION을 붙이면, 권한을 받은 사용자가 다른 사용자에게도 같은 권한을 부여할 수 있게 됩니다.

예시:

WITH GRANT OPTION 없이 부여한 경우, USER1은 자신만 SELECT를 수행할 수 있고 다른 사용자에게 권한을 넘길 수 없습니다.

REVOKE - 권한 회수 기초

예시:

REVOKE ... CASCADE - 권한 연쇄 회수 심화

WITH GRANT OPTION으로 권한이 여러 사용자에게 퍼진 상황에서, 중간 사용자의 권한을 회수할 때 사용하는 옵션입니다. DROP의 CASCADE와 의미가 비슷합니다 — 이 사용자가 재부여한 권한도 모두 함께 회수됩니다.

시나리오 예시:

DBA가 USER1에게 WITH GRANT OPTION으로 SELECT 권한을 주고, USER1이 USER2에게 SELECT 권한을 재부여한 상태입니다.

REVOKE GRANT OPTION FOR - 재부여 권한만 회수 심화

사용자 본인의 권한은 유지하고, 다른 사용자에게 재부여할 수 있는 권한만 회수하려면 GRANT OPTION FOR를 사용합니다.

이 구문 실행 후 USER1은 여전히 학생 테이블을 SELECT할 수 있지만, 다른 사용자에게 SELECT 권한을 넘길 수는 없게 됩니다.

정보처리기사 실기 대비 문제