SQL 관리 구문

SQL 관리 구문

   SQL 관리 구문은 데이터베이스 시스템의 구조와 동작, 보안, 일관성을 유지하고 제어하는 데 핵심적인 역할을 한다. 일반적으로 SQL 명령어는 그 목적과 기능에 따라 네 가지로 분류된다. 데이터 정의어(DDL), 데이터 조작어(DML), 트랜잭션 제어어(TCL), 그리고 데이터 제어어(DCL)이다. 각 구문은 데이터베이스 설계부터 데이터의 입력·수정·삭제, 트랜잭션 처리, 사용자 권한 관리에 이르기까지 데이터베이스 운영 전반에 걸쳐 유기적으로 작동한다. 따라서 이 네 가지 구문의 개념과 역할을 명확히 이해하는 것은 데이터베이스를 안정적이고 효율적으로 관리하는 데 필수적인 요소이다.

---

DML (데이터 조작 언어)

    DML (Data Manipulation Language)은 데이터베이스에 저장된 데이터를 조작하는 SQL 명령어 집합이다. 주로 테이블의 행(ROW) 단위로 데이터를 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하거나, 조건에 따라 삽입 또는 갱신(MERGE)할 수 있다.

INSERT – 데이터 삽입

    새로운 행을 테이블에 추가할 때 사용한다.

INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...);

UPDATE – 데이터 수정

   기존 테이블의 행을 조건에 따라 수정할 때 사용한다.  WHERE절 없으면 전체 행이 수정되니 주의해야 한다.

UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2
WHERE 조건;

DELETE – 데이터 삭제

   조건에 맞는 행을 삭제할 때 사용한다. WHERE절 없으면 전체 행 삭제하니 주의해야 한다.

DELETE FROM 테이블명
WHERE 조건;

MERGE – 조건에 따라 삽입 또는 갱신 (UPSERT)

   두 테이블 간 데이터를 비교하여, 조건이 맞으면 UPDATE, 없으면 INSERT하는 복합 명령어이다. Oracle, SQL Server에서 자주 사용되고, PostgreSQL에서는 INSERT ... ON CONFLICT가 대체된다. 만약 같은 ID가 있으면 갱신, 없으면 새로 삽입 (Upsert)한다.

MERGE INTO 타겟테이블 T
USING 소스테이블 S
ON (T.id = S.id)
WHEN MATCHED THEN
    UPDATE SET T.name = S.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (S.id, S.name);

---

TCL (트랜잭션 제어어)

   TCL (Transaction Control Language)은 트랜잭션의 실행 결과를 확정하거나 취소하는 명령어들로, 데이터베이스의 일관성과 안정성을 유지하는 데 핵심적인 역할을 한다. 트랜잭션 단위로 데이터 변경을 확정하거나 되돌리는 작업을 처리하는 SQL 명령어 집합이다. TCL은 주로 트랜잭션을 직접 제어할 수 있는 환경(예: SQLPlus, 트리거, 프로시저 등)에서 사용한다. 즉, 일반 SELECT 문은 트랜잭션에 영향을 주지 않는다. (DML만 해당)

트랜잭션의 특징 (ACID)

   트랜잭션은 다음 4가지 속성을 만족해야 한다.

A - Atomicity (원자성)	모든 작업이 전부 수행되거나 전혀 수행되지 않아야 함
C - Consistency (일관성)	트랜잭션 전후 데이터가 무결한 상태여야 함
I - Isolation (격리성)	여러 트랜잭션이 동시에 수행돼도 서로 간섭 없어야 함
D - Durability (지속성)	COMMIT된 결과는 장애가 나도 유지돼야 함

COMMIT – 변경 내용 확정

   지금까지 수행한 DML 작업(INSERT, UPDATE, DELETE)을 완전히 저장한다. COMMIT 이후에는 ROLLBACK 불가하다.

COMMIT;

ROLLBACK – 변경 내용 취소

   트랜잭션 시작 이후의 작업을 모두 취소하고, 이전 상태로 되돌린다. 아직 COMMIT하지 않은 변경 내용만 되돌릴 수 있다.

ROLLBACK;

SAVEPOINT – 중간 지점 저장

   트랜잭션 중간에 SAVEPOINT를 설정하고, 해당 지점까지만 ROLLBACK 가능하다. 주로 부분 취소할 때 유용하다.

SAVEPOINT 지점이름;

-- 일부 작업 수행

ROLLBACK TO 지점이름;

---

DDL (데이터 정의어)

   DDL (Data Definition Language)은 데이터베이스의 구조(테이블, 뷰, 인덱스 등)를 정의하거나 변경하는 명령어들이다. 즉, 데이터를 다루기 전에 그릇(스키마)을 설계하거나 수정하는 도구이다. 특히, 테이블, 컬럼, 인덱스, 제약조건 등 데이터 구조 자체를 정의하고 관리하는 데 사용되고 DML처럼 트랜잭션(COMMIT/ROLLBACK)의 영향을 받지 않으며, 즉시 반영된다.

CREATE – 객체 생성

   테이블, 뷰, 시퀀스, 인덱스 등을 새로 만들 때 사용한다.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

ALTER – 객체 수정

   이미 생성된 테이블(또는 기타 객체)에 컬럼 추가, 삭제, 변경 등 구조 변경할 때 사용한다. ALTER 뒤에 추가 문구를 적어 명령을 상세히 할 수 있다.

-- 컬럼 추가
ALTER TABLE users ADD phone VARCHAR(20);

-- 컬럼 수정 (데이터 타입 변경 등)
ALTER TABLE users MODIFY name VARCHAR(100);

-- 컬럼 삭제
ALTER TABLE users DROP COLUMN phone;

1. ADD COLUMN – 컬럼 추가

ALTER TABLE users ADD phone VARCHAR(20);

2. DROP COLUMN – 컬럼 삭제

   users 테이블에서 phone 컬럼 제거(일부 DBMS에서는 복구 불가)

ALTER TABLE users DROP COLUMN phone;

3. MODIFY COLUMN – 컬럼 속성 변경

   name 컬럼의 데이터 타입(길이)을 변경

ALTER TABLE users MODIFY name VARCHAR(100);

4. RENAME COLUMN – 컬럼 이름 변경

    name 컬럼 이름을 full_name으로 바꿈

ALTER TABLE users RENAME COLUMN name TO full_name;

5. ADD CONSTRAINT – 제약조건 추가

   데이터 무결성 보장에 사용됨

-- 기본키 추가
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- 유니크 제약 추가
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

-- 외래키 제약 추가
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

DROP – 객체 삭제

   테이블, 뷰, 인덱스 등을 완전히 삭제한다. 복구가 불가하니 주의해야 한다.

DROP TABLE users;

RENAME – 객체 이름 변경

   테이블이나 뷰 등의 이름을 바꿀 수 있다.

RENAME users TO members;

TRUNCATE – 데이터 전체 삭제 (구조는 유지)

   테이블의 모든 데이터를 빠르게 삭제하지만, 테이블 구조는 남긴다. DELETE보다 훨씬 빠르지만 ROLLBACK 불가하다.

TRUNCATE TABLE users;

---

 DCL (데이터 제어어)

   DCL (Data Control Language)은 데이터베이스 사용자(User)와 권한(Permission)을 관리하는 명령어들이고, 보안, 접근 제어, 역할 관리에서 중요한 기능을 담당한다. 관리자(DBA)나 권한 있는 사용자가 주로 사용한다.

USER 관련 명령어

   사용자 계정을 생성하거나 삭제할 때 사용한다. (※ 일부 DBMS에서는 CREATE USER가 제한되거나 다른 방식으로 처리될 수도 있음) 사용자는 기본적으로 아무 권한도 없으며 GRANT로 권한 부여가 필요하다.

-- 사용자 생성
CREATE USER scott IDENTIFIED BY tiger;

-- 사용자 삭제
DROP USER scott;

-- 사용자 삭제 + 해당 소유 객체까지 모두 삭제
DROP USER scott CASCADE;

CREATE USER – 사용자 계정 생성

   사용자를 생성하는 작업을 한다. 그러나 생성만으로는 아무 작업도 못 하기에 추후에 GRANT로 권한 부여가 필요하다.

CREATE USER scott IDENTIFIED BY tiger;

ALTER USER – 사용자 속성 변경

   사용자의 속성을 변경할 수 있다. 추가적으로 디폴트 테이블스페이스나 계정 잠금/해제도 가능하다.

ALTER USER scott IDENTIFIED BY newpass;

ALTER USER scott ACCOUNT LOCK;
ALTER USER scott DEFAULT TABLESPACE users;

DROP USER – 사용자 삭제

   사용자를 삭제한다. 단, 해당 계정이 만든 테이블, 데이터는 남아있을 수 있다. 이럴 때 CASCADE와 함께 입력하면 사용자 계정과 함께 해당 사용자가 만든 모든 객체(테이블, 뷰 등)도 함께 삭제된다. 복구가 불가하니 주의해야 한다.

DROP USER scott;

DROP USER scott CASCADE;

권한 관련 명령어 (GRANT / REVOKE)

특정 사용자가 테이블, 뷰, 시퀀스 등 데이터 객체에 접근하거나 조작할 수 있도록 허용 또는 회수한다.

GRANT – 권한 부여

-- SELECT 권한을 사용자에게 부여
GRANT SELECT ON employees TO scott;

-- INSERT, UPDATE, DELETE 권한 부여
GRANT INSERT, UPDATE, DELETE ON employees TO scott;

-- 다른 사용자에게 권한 부여도 허용 (WITH GRANT OPTION)
GRANT SELECT ON employees TO scott WITH GRANT OPTION;

REVOKE – 권한 회수

-- 권한 회수
REVOKE SELECT, INSERT ON employees FROM scott;

ROLE 관련 명령어

   여러 권한을 묶어서 일괄적으로 관리할 수 있는 권한 묶어 (역할, Role) 처리할 수 있다. 즉,여러 사용자에게 동일 권한을 일괄 부여할 때 효율적이다.

ROLE 생성 / 권한 부여 / 사용자에게 역할 할당

-- ROLE 생성
CREATE ROLE staff_role;

-- ROLE에 권한 부여
GRANT SELECT, INSERT ON employees TO staff_role;

-- ROLE을 사용자에게 부여
GRANT staff_role TO scott;

-- ROLE 회수
REVOKE staff_role FROM scott;

ROLE 생성

CREATE ROLE role명;

ROLE에 권한 부여

GRANT 권한 ON 객체 TO 역할명;

사용자에게 ROLE 부여

GRANT 역할명 TO 사용자명;

---

요약

분류	한국어	주요 역할	명령어
DML	데이터 조작어	테이블에 저장된 데이터를 삽입, 수정, 삭제, 조회	SELECT, INSERT, UPDATE, DELETE, MERGE
TCL	트랜잭션 제어어	데이터 조작 결과를 확정(COMMIT)하거나 취소(ROLLBACK)	COMMIT, ROLLBACK, SAVEPOINT
DDL	데이터 정의어	테이블, 뷰, 인덱스 등 데이터 구조를 생성/수정/삭제	CREATE, ALTER, DROP, TRUNCATE, RENAME
DCL	데이터 제어어	사용자, 권한, 역할 등 접근 권한을 관리	GRANT, REVOKE, CREATE USER, CREATE ROLE