[SQL] SQL이란? DDL/ DML / DCL / TCL

 

[ SQL Structured Query Language 이란? ]

SQL이란 관게형 데이터베이스에서 데이터를 관리하고 조작하기 위해 사용되는 표준 프로그래밍 언어이다.

데이터베이스 언어는 크게 DDL, DML, DCL, TCL이 있다. 

---

[ DDL data definition language ]

DDL은 데이터베이스의 구조를 정의, 수정, 삭제하는 언어이다.

DDL의 명령어는 CREATE, ALTER, DROP, TRUNCATE, RENAME이 있다.

 

1. CREATE : DB 객체 생성

• TABLE
• DATABASE/SCHEMA
• INDEX
• VIEW
• SEQUENCE
• FUNCTION/PROCEDURE
• TRIGGER 
• USER/ROLE 

 1) 테이블 Table 생성

CREATE TABLE users (
  id           BIGINT PRIMARY KEY,
  email        VARCHAR(255) NOT NULL UNIQUE,
  name         VARCHAR(100) NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

 

 2) 인덱스 Index 생성

CREATE INDEX idx_users_created_at ON users(created_at);

 * 인덱스 INDEX : 데이터베이스의 테이블에 빠르게 접근할 수 있는 기능 

 

 3) 뷰 View 생성 

CREATE VIEW v_user_emails AS
SELECT id, email
FROM users;

뷰 VIEW
뷰 VIEW는 실제로 데이터를 저장하지 않고 쿼리 결과를 가상의 테이블 형태로 제공하는 논리적인 테이블이다.
SELECT 쿼리에 이름을 붙여둔거라고 생각하면 쉽다.

뷰를 사용하는 이유(장점)?
- 복잡한 쿼리를 단순화
- 보안 강화: 테이블 전체를 공개하지 않고, 필요한 컬럼이나 행만 보이도록 할 수 있다.
- 일관성 유지 : 여러 사용자가 동일한 데이터를 동일한 방시그로 조회할 수 있다.
- 유지보수 용이 : 테이블이 변경되어도 뷰를 수정하면 어플리케이션 코드는 변경하지 않을 수 있고, 자주 사용되는 쿼리를 한곳에서 관리할 수 있다. 

 

4) 트리거 Trigger 생성

• MySQL

-- users 테이블 변경 이력을 audit 테이블에 기록하는 트리거
DELIMITER //   -- 문장의 끝표시를 ';'가 아니라 '//'로 하겠다는 의미 

CREATE TRIGGER trg_users_update_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_audit (user_id, action, old_email, new_email, changed_at)
  VALUES (NEW.id, 'UPDATE', OLD.email, NEW.email, NOW());
END//

DELIMITER ; -- 문장의 끝표시를 ';'로 하겠다는 의미.

* DELIMITER는 MySQL에서만 쓰는 구분자(문장 끝 표시) 변경 명령어이다.
트리거나 프로시저처럼 ;가 많이 들어간 문장을 만들때 주로 사용한다..

트리거 TRIGGER
트리거 TRIGGER는 INSERT / UPDATE / DELETE 같은 이벤트가 발생할 때 자동으로 실행되는 SQL 로직이다.
즉, 특정 테이블에 변화가 생기면 자동으로 추가 작업을 수행하도록 하는 기능이다.
사용자가 직접 호출하는 것이 아니라 DBMS가 조건에 맞는 이벤트가 발생했을 때 자동으로 실행한다.

트리거를 사용하는 이유(장점)?
- 감사(Audit) 로그 기록: 누가/언제/무엇을 변경했는지 자동 기록
- 데이터 무결성 보조: 특정 조건에서 추가 검증/보정 처리
- 자동 후처리: 변경 시 다른 테이블 갱신 등(단, 과하면 유지보수 어려움)

 

5) 사용자 계정 User 생성

DB 사용자(USER)는 DB에 접속하는 주체이다.
권한을 부여하려면(GRANT) 먼저 사용자 계정이 생성되어 있어야 한다.

• MySQL

-- 로컬호스트 (DB와 애플리케이션이 같은 서버에서 도는 경우 흔히 사용)
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password1234';

-- 특정 IP(서버)만 허용
CREATE USER 'app_user'@'10.0.1.12' IDENTIFIED BY 'password1234';

-- IP 대역 허용
CREATE USER 'app_user'@'10.0.%' IDENTIFIED BY 'password1234';

-- 특정 도메인 허용
CREATE USER 'app_user'@'app1.mycorp.internal' IDENTIFIED BY 'password1234';

-- 모든 호스트 허용
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password1234';

• PostgreSQL

CREATE USER app_user WITH PASSWORD 'password1234';

 

• USER의 스프링부트 사용 예시

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
    username: app_user
    password: password1234
    driver-class-name: com.mysql.cj.jdbc.Driver

 

6) 역할 Role 생성

• MySQL

CREATE ROLE 'readonly_role';

• PostgreSQL

CREATE ROLE readonly_role;

---

2. ALTER : DB 객체 구조 변경

컬럼 추가         ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입;
컬럼 삭제         ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
컬럼 변경 (타입) | MySQL : ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 새_데이터타입;
              | PostgreSQL : ALTER TABLE 테이블명 ADD CONSTRAINT 컬럼명 새_데이터타입;(P)
컬럼 변경 (이름)	ALTER TABLE 테이블명 RENAME COLUMN 기존명 TO 새이름;
테이블 이름 변경	ALTER TABLE 테이블명 RENAME TO 새이름;
제약 조건 추가  	ALTER TABLE 테이블명 ADD CONSTRAINT 제약명 제약조건 (컬럼명);
제약 조건 삭제   | MySQL : ALTER TABLE 테이블명 DROP 제역조건타입 제약명;
              | PostgreSQL : ALTER TABLE 테이블명 DROP CONSTRAINT 제약명;
기본값 추가        ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 SET DEFAULT 값;
기본값 제거    	ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 DROP DEFAULT;

---

3. DROP : DB 객체 삭제

DROP VIEW v_user_emails;
DROP INDEX idx_users_created_at; -- DB별로 문법 차이 있을 수 있음
DROP TABLE orders;
DROP TABLE app_users;

---

4. TRUNCATE : 테이블의 데이터 전체 삭제 (구조는 유지)

TRUNCATE TABLE orders;

---

5. RENAME : DB 객체 이름 변경 

• MySQL

RENAME TABLE users TO app_users;

 

• PostgreSQL

ALTER TABLE users RENAME TO app_users;

---

6. CONSTRAINT : 제약 조건 정의 

제약조건은 테이블이나 컬럼에 적용할 수 있다.

제약조건 종류	설명
NOT NULL	각 행은 해당 컬럼의 값을 반드시 포함해야 하며, NULL 값은 허용되지 않음
UNIQUE	컬럼에 중복된 값을 저장할 수 없음. 단, NULL 값은 허용됨 (DBMS에 따라 다를 수 있음)
PRIMARY KEY	기본키. 컬럼에 중복 값과 NULL 값을 허용하지 않음. 레코드를 구분하기 위한 값으로 사용
FOREIGN KEY	다른 테이블의 PRIMARY KEY를 참조하는 값. 참조 대상이 없는 값은 저장할 수 없으며, 관계 무결성을 보장
DEFAULT	레코드 입력 시 해당 컬럼에 값이 없으면 자동으로 저장될 기본값을 지정
CHECK	컬럼에 저장 가능한 값의 범위를 제한하는 제약조건

 

---

[ DML Data Management Language]

DML 은 데이터베이스 내 데이터를 삽입, 조회, 수정, 삭제하는 언어이다.

DML의 명령어는 SELECT, INSERT, UPDATE, DELETE가 있다.

 

1. SELECT : 데이터 조회

SELECT * FROM users;

SELECT name, age FROM users;

 

 DISTINCT
: SELECT절에서 레코드의 중복을 제거하는 키워드

• MySQL

SELECT DISTINCT name, age 
FROM users;

 

• PostgreSQL

SELECT DISTINCT ON (name) name, age
FROM users
ORDER BY name, age DESC;
 → 같은 name 중에서 age가 가장 큰 행(내림차순 DESC) 1개만 출력된다.

 

 SELECT 쿼리의 실행 순서 
  : FROM, ON, JOIN  > WHERE, GROUP BY, HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

FROM : 각 테이블을 확인한다.
→ ON : JOIN 조건을 확인한다.
→ JOIN  : JOIN이 실행되어 데이터가 SET으로 모아지게 된다.
→ WHERE : 데이터셋을 형성하게 되면 WHERE의 조건이 개별 행에 적용된다.
→ GROUP BY : WHERE의 조건 적용 후 나머지 행은 GROUP BY절에 지정된 열의 공통 값을 기준으로 그룹화된다. 쿼리에 집계 기능이 있는 경우에만 이 기능을 사용해야 한다.
→ HAVING : GROUP BY절이 쿼리에 있을 경우 HAVING 절의 제약조건이 그룹화된 행에 적용된다.
→ SELECT :  SELECT에 표현된 식이 마지막으로 적용된다.
→ DISTINCT : 표현된 행에서 중복된 행은 삭제
→ ORDER BY : 지정된 데이터를 기준으로 오름차순, 내림차순 지정
→ LIMIT : LIMIT에서 벗어나는 행들은 제외되어 출력된다.

---

2. INSERT : 데이터 삽입

INSERT INTO users (id, email, name)
VALUES
  (2, 'b@test.com', '박영희'),
  (3, 'c@test.com', '이민수');

---

3. UPDATE : 데이터 수정(갱신)

UPDATE users
SET name = '김철수', email = 'new@test.com'
WHERE id = 1;

---

4. DELETE : 데이터 삭제

DELETE FROM users
WHERE age < 14
  AND status = 'INACTIVE';

 

 

DROP vs TRUNCATE vs DELETE
- DELETE: 조건에 맞는 행(데이터) 삭제하고 구조는 유지한다. COMMIT되지 않은 경우 ROLLBACK이 가능하다.
- TRUNCATE: 테이블의 모든 행(데이터) 전체 삭제하고, 구조는 유지한다. 백업이나 복구 작업이 어렵다.
- DROP: 테이블의 모든 행(데이터)와 함께 구조까지 삭제한다. 백업이나 복구 작업이 어렵다.
- 속도 : DELETE < TRUNCATE(빠름, 전체 초기화) < DROP(매우빠름) 

 

---

[ DCL Data Control Language ]

DCL은 데이터베이스 접근 권한과 보안을 제어하는 언어이다.
주로 사용자(User)나 역할(Role)에 대한 권한 부여 및 회수에 사용된다.

 

1. GRANT : 권한 부여

1) Role에 권한 부여

GRANT SELECT ON users TO readonly_role;

2) User에게 Role 부여

• MySQL

GRANT 'readonly_role' TO 'app_user'@'%';
SET DEFAULT ROLE 'readonly_role' TO 'app_user'@'%';

• PostgreSQL
   

GRANT readonly_role TO app_user;

 

3) 모든 권한 부여

GRANT ALL PRIVILEGES ON app_users TO user1;

---

2. REVOKE : 권환 회수

REVOKE ALL PRIVILEGES ON app_users FROM user1;

• 모든 권환 회수

REVOKE INSERT ON app_users FROM user1;

---

[ TCL Transaction Control Language ]

TCL은 트랜잭션 Transaction 을 제어하는 언어이다.
트랜잭션이란 하나의 논리적인 작업 단위를 의미한다.

TCL의 명령어는 COMMIT, ROLLBACK, SAVEPOINT가 있다.

 

1. COMMIT : 변경사항 확정

트랜잭션 내에서 수행된 변경 내용을 영구적으로 저장한다.

COMMIT 이후에는 ROLLBACK으로 되돌릴 수 없다.

COMMIT;

 

2. ROLLBACK : 변경사항 취소

트랜잭션 내에서 수형된 변경 내용을 모두 취소한다.

ROLLBACK;

 

3. SAVEPOINT : 중간 지점 생성

트랜잭션 중간에 되돌아갈 수 있는 지점을 생성한다.

SAVEPOINT sp1;

 

• 트랜잭션의 전체 흐름

BEGIN; 
INSERT INTO users VALUES (1, 'a@test.com'); 
SAVEPOINT sp1; 

UPDATE users 
SET email = 'b@test.com' 
WHERE id = 1; 

ROLLBACK TO sp1; -- 해당 지점으로 되돌아가 그 지점의 상태로 되돌린다. 
COMMIT;          -- 그 되돌린 상태를 확정한다.

 

---

[ SQL 명령어 요약 ]

분류	역할	주요 명령어
DDL	구조 정의	CREATE, ALTER, DROP, TRUNCATE
DML	데이터 조작	SELECT, INSERT, UPDATE, DELETE
DCL	권한 제어	GRANT, REVOKE
TCL	트랜잭션 제어	COMMIT, ROLLBACK, SAVEPOINT

 

 

[출처: https://dev-coco.tistory.com/158 [슬기로운 개발생활:티스토리] ]