[SQL] DML, DDL, DCL, TCL

DML(Data Manipulation Language, 데이터 조작어)

데이터를 추가, 수정, 삭제하는 데 사용한다. DML 명령어가 데이터 손실을 초래할 수 있으므로 실행하기 전에 백업을 수행하는 것이 좋다. 또한 DML  권한 관리를 철저하게 하여 데이터베이스 보안을 유지해야 한다.
장점

• 간편성: SQL을 사용하여 데이터를 직관적이고 간편하게 조작이 가능하다.
• 효율성: 다양한 데이터 조작 작업을 효율적으로 수행할 수 있다.
• 유연성: WHERE 절과 같은 조건을 사용하여 특정 데이터를 선택적으로 조작할 수 있다.

단점

• 데이터 손실 가능성: DML명령어가 데이터 손실을 초래할 수 있으므로 주의해서 사용해야 한다.
• 보안 취약: DML 권한 관리가 적절하지 않을 경우 보안 취약점이 발생할 수 있다.
• 복잡성: 복잡한 데이터 조작 작업이 필요하다.

언어	의미	구문	Description
SELECT	조회	SELECT [ALL | DISTINCT] column_name [, column_name...] FROM table_name [, table_name...] [WHERE condition] [GROUP BY column_name [HAVING condition]] [ORDER BY column_name];	하나 또는 그 이상의 테이블에 데이터를 추출 원하는 정보를 조회하고 필터링할 때 사용
INSERT	입력	INSERT INTO 테이블명(컬럼명1, 컬럼명2, ...) VALUES (데이터1, 데이터2, ...)	테이블에 새로운 행 입력 명시되지 않은 컬럼에는 NULL값이 입력됨(PK나 NOT NULL 제약 조건이 있으면  NULL입력 안되니 주의할 것) 데이터 유형이 맞지 않거나 컬럼 개수 안맞으면 에러 발생
UPDATE	변경	UPDATE 테이블명 SET 컬럼명 = 새로운데이터(WHERE 수정할 데이터에 대한 조건);	기존 행에 있던 데이터 값 변경 수정하고 싶은 컬럼 많으면 SET절에 ,(콤마)로 이어 명시 WHERE 절이 없으면 모든 Row가 변경되니 주의
DELETE	삭제	DELETE FROM 테이블명(WHERE 수정할 데이터에 대한 조건);	WHERE 절이 없으면 모든 행이 삭제되므로 주의 WHERE 절 없이 전체 데이터를 삭제하고자 하는 경우에는 TRUNCATE 사용(TRUNCATE는 별도 로그 쌓지 않으므로 ROLLBACK이 불가능) DELETE는 COMMIT전에 ROLLBACK 가능
MERGE	병합	MERGE   INTO 타겟 테이블명   USING 데이터가져올소스테이블명      ON 일치하는 행 찾을 조건 WHEN MATCHED THEN      UPDATE              SET 컬럼명=새로운 데이터[, 컬럼명=새로운 데이터...] WHEN NOT MATCHED THEN       INSERT[(컬럼명1, 컬럼명2, ...)]        VALUES[(데이터1, 데이터2, ...)]	데이터 베이스에서 두 개의 테이블을 병합하거나 업데이트하는 데 사용

 
 

DDL(Data Definition Language, 데이터 정의어)

데이터베이스의 스키마를 정의하는 데 사용하는 SQL언어의 하위집합으로 DDL을 통해 TABLE, VIEW, INDEX, SCHEMA 등 데이터베이스의 논리적 구조를 생성, 수정, 삭제할 수있다.
 
DDL 데이터 타입

유형	데이터타입
숫자	NUMBER, TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, BIGINT, FLOAT, DOUBLE/REAL, DECIMAL/NUMERIC
문자	CHAR(n), VARCHAR(n), CHAR(n), BINARY(n), TEXT, BLOB, ENUM(값들..), SET(값들..)
날짜	DATE, TIME, DATETIME, TIMESTAMP, YEAR
논리	BOOLEAN

 

언어	의미	구문	Description
CREATE	테이블 생성	CREATE TABLE 테이블명(     컬럼명1   데이터타입(DEFAULT/NULL여부),     ... );	테이블, 뷰, 인덱스, 스키마 등 새로운 데이터베이스 객체를 생성하는 데 사용된다.
ALTER	테이블 수정	ALTER [OBJECT TYPE] [OBJECT NAME] [ALTERATION];	기존 테이블, 뷰, 인덱스, 스키마 등의 구조를 변경하는 데 사용된다.
DROP	테이블 삭제	DROP [OBJECT TYPE] [OBJECT NAME];	테이블, 뷰, 인덱스, 스키마 등 기존 데이터베이스 객체를 삭제하는 데 사용된다.
RENAME	테이블명 변경	ALTER [OBJECT TYPE] [OBJECT NAME] RENAME TO [NEW NAME];	테이블, 뷰, 인덱스, 스키마 등의 이름을 변경하는 데 사용된다.
TRUNCATE	테이블 데이터 모두 제거	TRUNCATE TABLE [TABLE NAME];	테이블의 모든 데이터를 삭제, 테이블 구조는 유지하지만 모든 데이터를 제거함.

 
(1) CREATE

• NULL: 공백(' ')과는 다르며 존재하지 않는 값이라는 의미.
• DEFAULT: 데이터의 기본값. DEL_YN(삭제여부) 컬럼의 DEFAULT를 'N'로 정의해놓으면 DEL_YN 값을 별도로 명시하지 않았을때 NULL대신 'N' 저장된다
• CREATE TABLE 할 때 제약조건(CONSTRAINT)도 함께 정의해 줄 수 있다.

예시코드

CREATE TABLE employees (
  employee_id NUMBER(10) PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER(10,2) CHECK (salary > 0),
);

employee_id 컬럼은 PRIMARY KEY 제약 조건으로 설정되어 null값을 허용하지 않으며, 모든 행에 고유한 값을 가져야 한다. salary 컬럼은 CHECK 제약 조건으로 설정되어 0보다 큰 값만 허용함. 이와 같이 여러 제약 조건을 함께 사용할 수 있다.
 
제약조건의 종류

제약조건	구문	Description
PRIMARY KEY(기본키)	CREATE TABLE table_name (   column1 datatype,   column2 datatype,   PRIMARY KEY (column_name) );	테이블의 각 행을 고유하게 식별하는 컬럼 또는 컬럼 그룹, NULL값을 허용하지 않는다. 테이블에 하나만 설정 가능하다.
UNIQUE KEY(고유키)	CREATE TABLE table_name (   column1 datatype,   column2 datatype,   UNIQUE KEY (column_name) );	테이블의 모든 행에서 고유해야하는 컬럼 또는 컬럼 그룹, 고유성 보장하지만 NULL값 허용한다. 테이블에 여러 개 설정 가능하다.
NOT NULL	CREATE TABLE table_name (   column1 datatype NOT NULL,   column2 datatype );	해당 컬럼이 NULL이 될 수 없음을 지정한다.
CHECK	CREATE TABLE table_name (   column1 datatype CHECK (condition) );	컬럼 값이 특정 조건을 만족해야 함을 지정한다.
FOREIGN KEY(외래키)	CREATE TABLE table_name (   column1 datatype,   FOREIGN KEY (column_name) REFERENCES other_table_name (column_name) );	다른 테이블의 PRIMARY KEY 를 참조하는 컬럼, FOREIGN KEY는 참조하는 테이블에 존재하는 값만 허용한다.

 
 
(2) ALTER
 
ALTERATION

• ADD COLUMN: 새로운 컬럼 추

ALTER TABLE 테이블명 ADD 컬럼명 데이터유형;

• DROP COLUMN

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

• MODIFY COLMUN

ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터유형 [DEFAULT 값] [NOT NULL], 컬럼명2 데이터유형 ...);

• RENAME COLUMN

ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼명 TO 변경할 컬럼명;

• ADD CONSTRAINT

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명); 

 
(3) DROP TABLE
테이블을 삭제할 때 사용하는 명령어로 해당 테이블을 참조하고 있는 다른 테이블을 참조하고 있는 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않는다.
 
(4) RENAME TABLE

RENAME TABLE employees RENAME TO employees_new;

 
(5) TRUNCATE TABLE
DELETE는 WHERE절에 따라 조건에 맞는 데이터만 제거한다. 로그 기록이 남아 롤백이 가능하다.
하지만, TRUNCATE는 테이블의 모든 데이터를 즉시 제거하고 로그 기록이 남지 않아 ROLLBACK이 불가능하다.
DELETE, TRUNCATE 모두 테이블 구조는 유지된다.

구분	TRUNCATE	DELETE
데이터 제거 방식	즉시	조건에 맞는 데이터
데이터 구조	유지(자동 증가 컬럼 초기화)	유지
속도	빠름	조건에 따라 다름
주의 사항	데이터 손실 가능성 높음	조건 오류 시 의도하지 않은 삭제 가능성

 

DCL(Data Control Language)

데이터베이스 사용자의 권한과 접근 권한을 관리하는 데 사용된다.
DCL은 사용자가 데이터베이스 객체(테이블, 뷰, 프로시저 등)를 생성, 수정, 삭제하고 데이터에 대한 접근 권한을 부여하거나 제거하는 데 필요한 권한을 제공한다.

[DCL 명령] [대상] [권한] [TO] [사용자] [WITH GRANT OPTION];

 
(1) USER 관련

언어	의미	구문	Description
CREATE USER	사용자 생성	CREATE USER 사용자 IDENTIFIED BY '비밀번호';	새로운 사용자 계정을 생성한다. CREATE USER 권한이 있어야 수행 가능하다.
ALTER USER	사용자 변경	ALTER USER 사용자 IDENTIFIED BY '새로운 비밀번호';	사용자 계정 정보를 수정한다.
DROP USER	사용자 삭제	DROP USER 사용자;	사용자 계정을 삭제한다.

 
(2) 권한관련 관련

언어	의미	구문	Description
GRANT	사용자 권한 부여	GRANT 권한 ON 대상 TO 사용자 [WITH GRANT OPTION];	사용자에게 특정 데이터베이스 객체에 대한 권한을 부여
REVOKE	사용자 권한 회수	REVOKE 권한 ON 대상 FROM 사용자;	사용자에게 부여된 권한을 제거(회수)

 
(3) ROLE관련
ROLE이란 특정 권한들을 하나의 세트처럼 묶는 것이다. 사용자 그룹에 대한 권한을 설정하고 관리하는 데 사용되는 논리적 객체이다. Role을 통해 특정 작업을 수행하는 데 필요한 권한을 한 번에 여러 사용자에게 부여할 수 있다. ROLE의 이름은 상황에 따라 적절히 짓는다.
Role을 통해 사용자 그룹에 대한 권한을 한 번에 설정하여 관리 작업을 간소화하고, 사용자별 권한 설정 대신 Role을 사용하여 접근 권한을 제어하여 보안을 강화한다. 또한 Role을 통해 동일한 작업을 수행하는 사용자 그룹에게 일관된 권한을 부여하여 데이터 무결성을 유지한다.
 
1. ROLE 생성

CREATE ROLE role_name;

 
2. ROLE 권한 부여

GRANT 권한 TO role_name;

 
3. ROLE을 사용자에게 부여

GRANT role_name TO user_name;

 

TCL(Transaction Control Language)

트랜잭션(Transaction)을 제어하는 명령어, 트랜잭션이란 쪼개질 수 없는 업무처리 단위
트랜잭션은 여러 데이터 작업을 하나의 단위로 처리하여 데이터 무결성을 보장하고,  동시에 실행되는 여러 작업관 일관성을 유지하고, 트랜잭션 실패 시 데이터 베이스를 이전 상태로 되돌려 오류를 복구한다.
 
ACID 특성

특징	Description
원자성 (Atomicity)	트랜잭션 내 모든 작업은 하나의 단위로 처리. 모든 작업이 성공적으로 완료되거나, 하나라도 실패하면 전체 트랜잭션이 실패하고 이전 상태로 돌아감.
일관성 (Consistency)	트랜잭션 실행 전후 데이터베이스의 일관성이 유지. 트랜잭션 시작 전 데이터베이스 규칙이 만족되었다면, 트랜잭션 종료 후에도 규칙이 만족.
고립성 (Isolation)	하나의 트랜잭션은 고립되어 수행되어야 함. 동시에 실행되는 여러 트랜잭션은 서로 영향을 주지 않는다. 각 트랜잭션은 마치 다른 트랜잭션이 존재하지 않는 것처럼 독립적으로 실행된다.
지속성 (Durability)	트랜잭션이 성공적으로 종료되면, 트랜잭션이 변경한 데이터는 영구적으로 저장되어야 한다. 시스템 장애나 오류 발생에도 데이터 손실이 발생하지 않음. 모든 트랜잭션이 로그에 남겨진 뒤 COMMIT되어야 하고, 그래서 시스템 장애가 발생해도 복구 가능해야한다.

 

언어	의미	Description
COMMIT	트랜잭션 성공 종료	INSERT, DELETE, UPDATE 후 변경된 내용을 확정, 반영 COMMIT을 실행하지 않으면 메모리까지만 반영되어 메모리는 휘발성이 있으므로 사라질 수 있고 다른 사용자는 변경된 값 조회할 수 없다. 그러므로 COMMIT을 실행해야 최종적으로 데이터 파일에 기록되고 트랜잭션이 완료된다.
ROLLBACK	트랜잭션 취소	트랜잭션의 변경사항을 취소하고 트랜잭션을 시작하기 전 상태로 되돌린다. ROLLBACK이 실행되면 트랜잭션이 종료되고 변경사항이 데이터베이스에 반영되지 않는다.
SAVEPOINT	트랜잭션 내 특정 지점 저장(옵션)	트랜잭션 내에서 특정 지점을 임시 저장한다.

 
사용 예

-- TRANSACTION 시작
START TRANSACTION;

-- 데이터 수정
-- UPDATE employees 로 'Sales' depeartment의 employees의 급여를 10% 인상
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

-- SAVEPOINT 설정
-- 트랜잭션 지점에 "my_savepoint"라는 이름의 저장 지점 설정
SAVEPOINT my_savepoint;

-- 데이터 수정
-- UPDATE employees로 'Marketing' 부서 직원들의 급여를 20% 인상
UPDATE employees SET salary = salary * 1.2 WHERE department = 'Marketing';

-- 오류 발생
-- 의도하지 않은 오류 발생하여 롤백 필요, "my_savepoint" 저장 지점으로 ROLLBACK
ROLLBACK TO SAVEPOINT my_savepoint;

-- 데이터 복원
SELECT * FROM employees WHERE department = 'Sales';

-- 데이터 수정
-- UPDATE employees로 'Marketing' 부서 직원들의 급여를 30%  인상
UPDATE employees SET salary = salary * 1.3 WHERE department = 'Marketing';

-- 변경 사항 저장
-- COMMIT을 통해 트랜잭션 종료 및 변경 사항 저장
COMMIT;