SQL 명령 분류와 테이블·데이터·권한 제어 | 정보처리기사 필기

SQL 명령은 제어 대상에 따라 분류한다

SQL(Structured Query Language)은 관계형 데이터베이스에서 객체의 구조를 정의하고, 행을 조회·변경하며, 권한과 트랜잭션 상태를 제어하는 언어다. 문제에서는 명령 이름만 외우기보다 무엇을 대상으로 무엇을 바꾸는가를 기준으로 판단해야 한다.

분류	주 대상	대표 명령	시험에서의 판별 기준
DDL(데이터 정의어)	테이블·뷰·인덱스 등 객체의 구조	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`	개별 행이 아니라 객체의 정의나 구조를 다룬다.
DML(데이터 조작어)	테이블의 행	`SELECT`, `INSERT`, `UPDATE`, `DELETE`	데이터를 조회하거나 행을 삽입·수정·삭제한다.
DCL(데이터 제어어)	사용자·역할의 권한	`GRANT`, `REVOKE`	객체 사용 권한을 부여하거나 회수한다. 3분류에서는 트랜잭션 제어도 DCL에 포함할 수 있다.
TCL(트랜잭션 제어어)	트랜잭션의 상태	`COMMIT`, `ROLLBACK`, `SAVEPOINT`	TCL을 별도 분류로 제시한 자료에서 사용한다.

분류 문제에서는 다음 두 가지 예외를 먼저 확인한다.

3분류와 4분류: DDL·DML·DCL만 제시되면 COMMIT과 ROLLBACK을 DCL로 분류할 수 있다. TCL이 별도로 제시되면 권한 제어는 DCL, 트랜잭션 제어는 TCL로 나눈다.
DML과 DQL: SELECT는 정보처리기사의 일반적인 3분류에서 DML에 포함되지만, 일부 자료는 조회어인 DQL(Data Query Language)로 따로 분류한다. 문제의 보기와 전제를 우선한다.

SQL 명령의 제어 대상

DDL은 CREATE·ALTER·DROP으로 구조를, DML은 SELECT·INSERT·UPDATE·DELETE로 행을, DCL은 GRANT·REVOKE로 권한을 제어한다. TCL을 따로 분류하면 COMMIT·ROLLBACK·SAVEPOINT가 속한다. 3분류에서는 TCL을 DCL에 포함하며 SELECT를 DQL로 구분하는 자료도 있다.

DDL: 테이블과 제약조건의 구조를 정의한다

DDL(Data Definition Language)은 데이터베이스 객체를 만들거나 구조를 변경하고 제거하는 명령이다.

CREATE: 테이블·뷰·인덱스 등의 객체를 생성한다.
ALTER: 이미 존재하는 객체의 열이나 제약조건 등을 변경한다.
DROP: 객체의 정의를 제거하며, 테이블이라면 저장된 행도 함께 제거된다.
TRUNCATE: 테이블 구조는 남기고 모든 행을 제거한다. 시험에서는 일반적으로 DDL로 분류한다.

다음은 표준 SQL에 가까운 테이블 생성 예다. 자료형과 일부 세부 문법은 DBMS에 따라 달라질 수 있다.

SQL코드 영역 안에서 좌우로 이동할 수 있습니다.

CREATE TABLE department (
    dept_id   INTEGER PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employee (
    emp_id   INTEGER PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id  INTEGER,
    salary   DECIMAL(10, 2) CHECK (salary >= 0),
    CONSTRAINT fk_employee_department
        FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

제약조건은 테이블에 저장할 수 있는 데이터의 범위를 제한한다.

NOT NULL: 해당 열에 NULL을 허용하지 않는다.
UNIQUE: 지정한 열 또는 열 조합의 중복을 제한한다. NULL의 세부 취급은 DBMS마다 차이가 있을 수 있다.
PRIMARY KEY: 각 행을 유일하게 식별하며 NULL을 허용하지 않는다. 한 테이블의 기본키는 하나지만 여러 열로 구성할 수 있다.
FOREIGN KEY: 자식 테이블의 값이 부모 테이블의 참조 가능한 키와 대응하도록 하여 참조 무결성을 유지한다.
CHECK: 행의 값이 지정한 조건을 만족하도록 제한한다.

ALTER TABLE은 열 추가·삭제, 자료형 변경, 제약조건 추가·삭제 등에 사용한다. 정확한 구문과 변경 가능 범위는 DBMS마다 다르다. DROP TABLE은 테이블 자체를 제거하므로 DELETE와 달리 테이블 구조가 남지 않는다.

DDL이라는 이유만으로 항상 자동 커밋된다고 단정해서는 안 된다. DDL의 트랜잭션 참여 여부와 암시적 커밋 동작은 DBMS에 따라 다르다.

DML: 행을 조회하고 삽입·수정·삭제한다

DML(Data Manipulation Language)은 테이블에 저장된 행을 대상으로 한다.

SQL코드 영역 안에서 좌우로 이동할 수 있습니다.

INSERT INTO department (dept_id, dept_name)
VALUES (10, '개발');

INSERT INTO employee (emp_id, emp_name, dept_id, salary)
VALUES (1001, '김민수', 10, 3500000);

SELECT emp_id, emp_name, salary
FROM employee
WHERE dept_id = 10;

UPDATE employee
SET salary = 3700000
WHERE emp_id = 1001;

DELETE FROM employee
WHERE emp_id = 1001;

각 명령의 대상과 결과는 다음과 같다.

INSERT는 새 행을 추가한다. 열 목록을 생략하면 값의 순서와 생략 가능한 열을 정확히 알아야 하므로, 열 목록을 명시하는 편이 안전하다.
SELECT는 조건에 맞는 행을 조회한다. DQL을 별도 분류로 사용하는 문제에서는 DQL로 본다.
UPDATE는 조건에 맞는 기존 행의 값을 변경한다.
DELETE는 조건에 맞는 행을 제거하지만 테이블 구조는 유지한다. UPDATE와 DELETE에서 WHERE절은 선택 사항이다. WHERE절을 생략해도 문법 오류가 발생하는 것이 아니라 대상 테이블의 모든 행이 수정되거나 삭제된다. 반면 DROP과 TRUNCATE에는 특정 행만 고르는 WHERE절을 사용할 수 없다.

DCL과 TCL: 권한과 트랜잭션을 구분한다

권한 제어

GRANT는 사용자나 역할에 권한을 부여하고, REVOKE는 이미 부여한 권한을 회수한다. 다음 예에서는 analyst_role 역할이 이미 존재한다고 가정한다.

SQL코드 영역 안에서 좌우로 이동할 수 있습니다.

GRANT SELECT, UPDATE ON employee TO analyst_role;
REVOKE UPDATE ON employee FROM analyst_role;

WITH GRANT OPTION으로 객체 권한을 받은 사용자는 그 권한을 다른 사용자에게 다시 부여할 수 있다. 따라서 일반 권한보다 영향 범위가 크다. REVOKE는 권한을 회수하는 명령이며, 사용자가 과거에 수행해 이미 커밋된 데이터 변경을 되돌리는 명령은 아니다.

트랜잭션 제어

트랜잭션은 논리적으로 하나로 처리해야 하는 작업 단위다.

COMMIT: 현재 트랜잭션에서 수행한 변경을 확정한다.
ROLLBACK: 아직 확정하지 않은 변경을 취소한다.
SAVEPOINT: 트랜잭션 내부에 중간 지점을 설정하여 전체가 아닌 일부 변경만 되돌릴 수 있게 한다.

정보처리기사의 3분류 체계에서는 이 명령들을 DCL에 포함할 수 있고, 4분류 체계에서는 TCL로 분리한다. 또한 클라이언트나 DBMS의 자동 커밋 설정이 켜져 있으면 각 문장이 즉시 확정될 수 있으므로, 자동 커밋 설정과 명령 분류를 같은 개념으로 보아서는 안 된다.

비슷한 명령을 구분한다

DELETE·TRUNCATE·DROP

명령	제거 대상	`WHERE` 사용	테이블 구조	일반적인 시험 분류
`DELETE`	조건에 맞는 행 또는 모든 행	가능	유지	DML
`TRUNCATE`	모든 행	불가	유지	DDL
`DROP`	테이블 객체 전체	불가	제거	DDL

TRUNCATE의 롤백 가능 여부, 트리거 실행, 식별자 초기화 같은 세부 동작은 DBMS마다 다르므로 공통 규칙처럼 단정하지 않는다.

자주 혼동하는 짝

ALTER는 테이블 구조를 바꾸고, UPDATE는 행의 값을 바꾼다.
CREATE는 객체를 만들고, INSERT는 이미 존재하는 테이블에 행을 추가한다.
REVOKE는 권한을 회수하고, ROLLBACK은 미확정 데이터 변경을 취소한다.
GRANT는 권한을 부여하고, COMMIT은 트랜잭션의 변경을 확정한다.
DELETE는 행을 제거하지만, DROP은 객체의 정의까지 제거한다.

구조 변경·행 변경·문장 실패의 구분

열 이름을 바꾸는 ALTER TABLE … RENAME COLUMN …은 열의 이름을 바꾸는 구조 변경이다. 그 명령 자체가 모든 행을 삭제하거나 열의 값을 다른 값으로 바꾸는 것은 아니다. 정확한 지원 문법과 종속 객체의 처리 방식은 DBMS를 확인한다.

기존 행이 있는 테이블에 새로운 제약조건을 적용하려면 이미 저장된 행도 그 조건을 만족하는지 확인해야 한다. 필수 입력 열로 만들 열에 NULL이 남아 있다면, 정상적인 값으로 정제하거나 정책을 정하기 전에 단순히 NOT NULL만 붙여 해결할 수는 없다. 잘못된 값을 조용히 버리는 것과 제약을 검증하는 것은 다르다.

SQL 문장 하나의 원자성과 트랜잭션 전체의 원자성을 구분한다. SQLite의 기본 ABORT 충돌 처리에서는 여러 행을 수정하는 한 문장 중 제약 위반이 발생하면 그 문장이 변경한 내용 전체를 취소하지만, 같은 트랜잭션에서 먼저 성공한 다른 문장의 변경까지 자동으로 취소하지는 않는다. OR IGNORE, OR FAIL 등 다른 정책과 혼용하지 않는다.

뷰의 출력 열 이름을 명시하면 SELECT 목록의 위치에 대응한다. 예를 들어 CREATE VIEW v(code, total) AS SELECT id, amount FROM payment에서 code는 id, total은 amount에 대응한다. 열 이름을 명명하는 일은 원본 테이블의 열 이름을 바꾸는 일이 아니다.