관리구문(DDL, DML, TCL, DCL)

데이터베이스 관리구문은 데이터 정의(DDL), 데이터 조작(DML), 트랜잭션 제어(TCL), 데이터 제어(DCL)로 구분된다.

1. DDL (Data Definition Language)

DDL은 데이터베이스 구조를 정의하는 언어로, 객체의 생성, 변경, 삭제 작업을 수행한다. AUTO COMMIT 특성을 가지므로 실행 즉시 저장되며 원복이 불가능하다.

CREATE

데이터베이스 객체를 생성하는 명령어다.

테이블 생성

-- 기본 문법
CREATE TABLE 테이블명 (
    컬럼명1 데이터타입 [제약조건],
    컬럼명2 데이터타입 [제약조건],
    ...
);

-- 예제: 직원 테이블 생성
CREATE TABLE EMPLOYEE (
    EMP_ID NUMBER(5) PRIMARY KEY,
    EMP_NAME VARCHAR2(50) NOT NULL,
    SALARY NUMBER(10,2) DEFAULT 0,
    DEPT_ID NUMBER(3)
);

테이블 복제

-- 구조와 데이터 모두 복제
CREATE TABLE EMP_COPY AS
SELECT * FROM EMP;

-- 구조만 복제 (데이터 제외)
CREATE TABLE EMP_STRUCTURE AS
SELECT * FROM EMP WHERE 1=2;

-- 특정 컬럼만 복제하면서 컬럼명 변경
CREATE TABLE EMP_SUMMARY AS
SELECT EMPNO AS 사번, ENAME AS 이름, SAL AS 급여
FROM EMP;

📊 주요 데이터 타입

Oracle 데이터 타입:

데이터 타입	설명	예시
VARCHAR2(n)	가변길이 문자열 (최대 4000byte)	VARCHAR2(100)
CHAR(n)	고정길이 문자열 (최대 2000byte)	CHAR(10)
NUMBER(p,s)	숫자형 (p:전체자리수, s:소수점자리수)	NUMBER(10,2)
DATE	날짜형	DATE
CLOB	대용량 문자 데이터 (최대 4GB)	CLOB
BLOB	대용량 이진 데이터 (최대 4GB)	BLOB

SQL Server 데이터 타입:

데이터 타입	설명	예시
VARCHAR(n)	가변길이 문자열	VARCHAR(100)
CHAR(n)	고정길이 문자열	CHAR(10)
NUMERIC(p,s)	숫자형	NUMERIC(10,2)
DATETIME	날짜시간형	DATETIME

ALTER

기존 테이블의 구조를 변경하는 명령어다.

컬럼 추가

-- 단일 컬럼 추가
ALTER TABLE EMP ADD (EMAIL VARCHAR2(100));

-- 다중 컬럼 추가 (괄호 필수)
ALTER TABLE EMP ADD (
    PHONE VARCHAR2(20),
    ADDRESS VARCHAR2(200) DEFAULT '미입력'
);

-- DEFAULT와 NOT NULL 함께 사용 (순서 중요)
ALTER TABLE EMP ADD (
    STATUS VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL
);

컬럼 수정

-- 데이터 타입 및 크기 변경
ALTER TABLE EMP MODIFY (ENAME VARCHAR2(100));

-- 다중 컬럼 수정
ALTER TABLE EMP MODIFY (
    SAL NUMBER(12,2),
    COMM NUMBER(10,2)
);

-- DEFAULT 값 변경
ALTER TABLE EMP MODIFY (SAL DEFAULT 1000);

-- DEFAULT 값 제거
ALTER TABLE EMP MODIFY (SAL DEFAULT NULL);

컬럼명 변경

-- 컬럼명 변경 (동시 변경 불가)
ALTER TABLE EMP RENAME COLUMN ENAME TO EMP_NAME;

컬럼 삭제

-- 컬럼 삭제
ALTER TABLE EMP DROP COLUMN COMM;

-- 주의: 동시 삭제 불가
-- ALTER TABLE EMP DROP (COMM, MGR); -- 에러 발생

DROP

객체를 완전히 삭제하는 명령어다.

-- 테이블 삭제
DROP TABLE EMP;

-- PURGE 옵션: RECYCLEBIN 사용하지 않고 완전 삭제
DROP TABLE EMP PURGE;

TRUNCATE

테이블 구조는 유지하고 데이터만 삭제하는 명령어다.

-- 데이터 전체 삭제 (AUTO COMMIT)
TRUNCATE TABLE EMP;

💡 DELETE vs TRUNCATE vs DROP 비교

구분	DELETE	TRUNCATE	DROP
대상	데이터	데이터	구조+데이터
롤백	가능	불가능	불가능
조건	가능 (WHERE절)	불가능	불가능
속도	느림	빠름	빠름
자동커밋	수동	자동	자동
저장공간	유지	해제	완전해제

2. 제약조건 (Constraints)

데이터 무결성을 보장하기 위해 컬럼에 설정하는 규칙이다.

PRIMARY KEY (기본키)

테이블의 각 행을 고유하게 식별하는 제약조건이다.

-- 테이블 생성시 설정 (컬럼 레벨)
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) PRIMARY KEY,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);

-- 테이블 생성시 설정 (테이블 레벨, 제약조건명 지정)
CREATE TABLE DEPT (
    DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13),
    CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
);

-- 기존 테이블에 추가
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);

-- 복합 기본키
ALTER TABLE ORDERDETAIL ADD CONSTRAINT PK_ORDERDETAIL 
PRIMARY KEY (ORDER_ID, PRODUCT_ID);

UNIQUE

중복값을 허용하지 않는 제약조건이다. NULL은 허용한다.

-- 유일키 설정
ALTER TABLE EMP ADD CONSTRAINT UK_EMP_EMAIL UNIQUE (EMAIL);

-- 복합 유일키
ALTER TABLE EMP ADD CONSTRAINT UK_EMP_SSN UNIQUE (SSN1, SSN2);

NOT NULL

NULL 값을 허용하지 않는 제약조건이다. 다른 제약조건과 달리 컬럼의 특성으로 처리된다.

-- 컬럼 생성시 NOT NULL 설정
ALTER TABLE EMP ADD (PHONE VARCHAR2(20) NOT NULL);

-- 기존 컬럼에 NOT NULL 설정 (MODIFY 사용)
ALTER TABLE EMP MODIFY (ENAME NOT NULL);

-- NOT NULL 해제
ALTER TABLE EMP MODIFY (ENAME NULL);

FOREIGN KEY (외래키)

참조 테이블의 기본키나 유일키를 참조하는 제약조건이다.

-- 외래키 설정
ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT 
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);

-- ON DELETE CASCADE: 부모 삭제시 자식도 함께 삭제
ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT 
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE CASCADE;

-- ON DELETE SET NULL: 부모 삭제시 자식의 참조값을 NULL로 변경
ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT 
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE SET NULL;

CHECK

컬럼 값의 범위나 조건을 제한하는 제약조건이다.

-- 급여 범위 제한
ALTER TABLE EMP ADD CONSTRAINT CK_EMP_SAL CHECK (SAL >= 0);

-- 성별 값 제한
ALTER TABLE EMP ADD CONSTRAINT CK_EMP_GENDER 
CHECK (GENDER IN ('M', 'F'));

-- 복합 조건
ALTER TABLE EMP ADD CONSTRAINT CK_EMP_SALARY 
CHECK (SAL >= 1000 AND SAL <= 10000);

제약조건 관리

-- 제약조건 삭제
ALTER TABLE EMP DROP CONSTRAINT FK_EMP_DEPT;

-- 제약조건 비활성화/활성화
ALTER TABLE EMP DISABLE CONSTRAINT FK_EMP_DEPT;
ALTER TABLE EMP ENABLE CONSTRAINT FK_EMP_DEPT;

-- 제약조건 정보 조회
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP';

3. DML (Data Manipulation Language)

데이터의 삽입, 수정, 삭제를 담당하는 언어다. 수동 COMMIT이 필요하다.

INSERT

테이블에 새로운 행을 삽입하는 명령어다.

-- 전체 컬럼에 데이터 삽입
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'ATLANTA');

-- 특정 컬럼에만 데이터 삽입
INSERT INTO DEPT (DEPTNO, DNAME) VALUES (50, 'MARKETING');

-- 다중 행 삽입 (Oracle 23c부터 지원)
INSERT INTO DEPT VALUES 
(60, 'IT', 'SEOUL'),
(70, 'HR', 'BUSAN');

-- 서브쿼리를 이용한 다중 행 삽입
INSERT INTO EMP_BACKUP
SELECT * FROM EMP WHERE DEPTNO = 10;

UPDATE

기존 데이터를 수정하는 명령어다.

-- 단일 컬럼 수정
UPDATE EMP SET SAL = 3000 WHERE ENAME = 'SMITH';

-- 다중 컬럼 수정 (방법1)
UPDATE EMP SET SAL = 3000, COMM = 500 WHERE ENAME = 'SMITH';

-- 다중 컬럼 수정 (방법2)
UPDATE EMP SET (SAL, COMM) = (3000, 500) WHERE ENAME = 'SMITH';

-- 서브쿼리를 이용한 수정
UPDATE EMP SET SAL = (
    SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20
) WHERE DEPTNO = 10;

-- 다중 컬럼 서브쿼리 수정
UPDATE EMP SET (SAL, COMM) = (
    SELECT SAL, COMM FROM EMP WHERE ENAME = 'KING'
) WHERE ENAME = 'SMITH';

DELETE

데이터를 삭제하는 명령어다.

-- 조건에 맞는 행 삭제
DELETE FROM EMP WHERE DEPTNO = 30;

-- 모든 행 삭제
DELETE FROM EMP;

-- 서브쿼리를 이용한 삭제
DELETE FROM EMP WHERE DEPTNO IN (
    SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS'
);

MERGE

참조 테이블과 비교하여 INSERT, UPDATE, DELETE를 동시에 수행하는 명령어다.

-- 기본 MERGE 문법
MERGE INTO 대상테이블 USING 참조테이블
ON (조인조건)
WHEN MATCHED THEN
    UPDATE SET 컬럼 = 값
WHEN NOT MATCHED THEN
    INSERT (컬럼목록) VALUES (값목록);

-- 실제 예제
MERGE INTO EMP_TARGET T
USING EMP_SOURCE S ON (T.EMPNO = S.EMPNO)
WHEN MATCHED THEN
    UPDATE SET T.SAL = S.SAL, T.COMM = S.COMM
WHEN NOT MATCHED THEN
    INSERT (EMPNO, ENAME, SAL, DEPTNO)
    VALUES (S.EMPNO, S.ENAME, S.SAL, S.DEPTNO);

4. TCL (Transaction Control Language)

트랜잭션의 논리적 단위를 제어하는 언어다.

트랜잭션의 특성 (ACID)

• 원자성(Atomicity): 트랜잭션의 모든 연산이 성공하거나 모두 실패
• 일관성(Consistency): 트랜잭션 실행 전후에 데이터베이스가 일관된 상태 유지
• 고립성(Isolation): 동시 실행되는 트랜잭션들이 서로 영향을 주지 않음
• 지속성(Durability): 커밋된 트랜잭션의 결과는 영구적으로 반영

COMMIT

트랜잭션의 모든 변경사항을 데이터베이스에 영구적으로 저장한다.

-- 데이터 변경
INSERT INTO DEPT VALUES (80, 'RESEARCH', 'LA');
UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 10;

-- 변경사항 저장
COMMIT;

ROLLBACK

트랜잭션의 모든 변경사항을 취소하고 이전 상태로 복원한다.

-- 데이터 변경
DELETE FROM EMP WHERE DEPTNO = 30;

-- 변경사항 취소
ROLLBACK;

-- 특정 SAVEPOINT로 복원
ROLLBACK TO SP1;

SAVEPOINT

트랜잭션 내에서 부분 복원 지점을 설정한다.

-- SAVEPOINT 설정과 활용
INSERT INTO DEPT VALUES (90, 'FINANCE', 'CHICAGO');
SAVEPOINT SP1;

UPDATE EMP SET SAL = SAL * 1.2;
SAVEPOINT SP2;

DELETE FROM EMP WHERE DEPTNO = 20;
SAVEPOINT SP3;

-- SP2 지점으로 복원 (DELETE 취소, UPDATE는 유지)
ROLLBACK TO SP2;

-- 최종 저장
COMMIT;

5. DCL (Data Control Language)

데이터베이스 객체에 대한 접근 권한을 제어하는 언어다.

권한의 종류

시스템 권한

데이터베이스 시스템 작업에 대한 권한이다.

권한	설명
CREATE TABLE	테이블 생성 권한
CREATE VIEW	뷰 생성 권한
CREATE SESSION	데이터베이스 접속 권한
CREATE USER	사용자 생성 권한
DROP ANY TABLE	모든 테이블 삭제 권한

객체 권한

특정 객체에 대한 작업 권한이다.

권한	설명
SELECT	조회 권한
INSERT	삽입 권한
UPDATE	수정 권한
DELETE	삭제 권한
ALTER	구조 변경 권한
INDEX	인덱스 생성 권한

GRANT

권한을 부여하는 명령어다.

-- 객체 권한 부여
GRANT SELECT ON EMP TO HR;
GRANT SELECT, INSERT, UPDATE ON DEPT TO HR;
GRANT ALL ON EMP TO HR;

-- 시스템 권한 부여 (관리자만 가능)
GRANT CREATE TABLE TO HR;
GRANT CREATE SESSION TO HR;

-- 다중 사용자에게 권한 부여
GRANT SELECT ON EMP TO HR, SCOTT, JONES;

-- WITH GRANT OPTION: 권한을 받은 사용자가 다른 사용자에게 권한 재부여 가능
GRANT SELECT ON EMP TO HR WITH GRANT OPTION;

-- WITH ADMIN OPTION: 시스템 권한을 받은 사용자가 다른 사용자에게 권한 재부여 가능
GRANT CREATE TABLE TO HR WITH ADMIN OPTION;

REVOKE

권한을 회수하는 명령어다.

-- 객체 권한 회수
REVOKE SELECT ON EMP FROM HR;
REVOKE ALL ON EMP FROM HR;

-- 시스템 권한 회수
REVOKE CREATE TABLE FROM HR;

-- 다중 사용자로부터 권한 회수
REVOKE SELECT ON EMP FROM HR, SCOTT;

ROLE

권한들의 집합으로, 권한 관리를 효율적으로 할 수 있다.

-- 롤 생성 (관리자 권한 필요)
CREATE ROLE DEVELOPER;

-- 롤에 권한 부여
GRANT CREATE TABLE, CREATE VIEW TO DEVELOPER;
GRANT SELECT, INSERT, UPDATE ON EMP TO DEVELOPER;

-- 사용자에게 롤 부여
GRANT DEVELOPER TO HR;

-- 롤로부터 권한 회수
REVOKE CREATE TABLE FROM DEVELOPER;

-- 사용자로부터 롤 회수
REVOKE DEVELOPER FROM HR;

-- 롤 삭제
DROP ROLE DEVELOPER;

WITH GRANT OPTION vs WITH ADMIN OPTION

구분	WITH GRANT OPTION	WITH ADMIN OPTION
적용 대상	객체 권한	시스템 권한, 롤
권한 회수	중간관리자만 회수 가능	관리자가 직접 회수 가능
연쇄 회수	중간관리자 권한 회수시 하위 권한도 함께 회수	중간관리자 권한 회수시 하위 권한은 유지

예제:

-- GRANT OPTION 테스트
GRANT SELECT ON EMP TO USER1 WITH GRANT OPTION;  -- ADMIN → USER1
-- USER1이 USER2에게 권한 부여
GRANT SELECT ON ADMIN.EMP TO USER2;              -- USER1 → USER2
-- ADMIN이 USER1 권한 회수하면 USER2 권한도 함께 회수됨
REVOKE SELECT ON EMP FROM USER1;

-- ADMIN OPTION 테스트  
GRANT CREATE TABLE TO USER1 WITH ADMIN OPTION;   -- ADMIN → USER1
-- USER1이 USER2에게 권한 부여
GRANT CREATE TABLE TO USER2;                     -- USER1 → USER2
-- ADMIN이 USER1 권한 회수해도 USER2 권한은 유지됨
REVOKE CREATE TABLE FROM USER1;