SQL 활용 - DML, TCL, DDL, DCL

DML

• DDL에서 정의한 대로 데이터를 입력하고, 입력된 데이터를 수정, 삭제, 조회하는 명령어.

1. INSERT

• 테이블에 데이터를 입력하는 명령어

//↓명시되지 않은 컬럼에는 NULL값이 입력됨.
INSERT INTO 테이블명(컬럼명1, 컬럼명2, ...) VALUES (데이터1, 데이터2, ...);

//↓순서대로 빠짐없이 나열되어야함. 순서 중요.
INSERT INTO 테이블명 VALUES (전체 컬럼에 입력될 데이터 리스트);

INSERT INTO 입사(부서명, 입사년월, 입사자사번) VALUES ('개발', '20240403', '20240403');

2. UPDATE

• 이미 저장된 데이터를 수정하는 명령어.

// ↓수정하고 싶은 컬럼이 많다면 SET절에 콤마로 연결하면 됨. WHERE에 조건이 없으면 모든 ROW가 변경됨.
UPDATE 테이블 SET 컬럼명 = 새로운 데이터 (WHERE 수정할 데이터에 대한 조건);

UPDATE 입사 SET 수분 = '경력' WHERE 입사자사번 = '220101';

3. DELETE

• 저장된 데이터를 삭제하고 싶을 때 사용하는 명령어.

// ↓ WHERE 조건이 없으면 모든 ROW 삭제됨.
DELETE FROM 테이블명 (WHERE 수정할 데이터에 대한 조건);

DELETE FROM 입사 WHERE 입사자사번 = '220101';

• 테이블 전체 데이터를 삭제하는 경우 TRUNCATE명령어가 시스템 부하 측면에서 유리함. 대신 별도의 로그가 쌓이지 않아 ROLLBACK 불가능하며, DELETE의 경우 COMMIT 전에 ROLLBACK이 가능함.

4. MERGE ( INSERT + UPDATE )

• 테이블에 새로운 데이터를 입력하거나
• 이미 저장되어 있는 데이터에 대한 변경 작업을 한 번에 할 수 있도록 해주는 명령어.

MERGE INTO 타겟 테이블명
	 USING 비교 테이블명
        ON 조건
     WHEN MATCHED THEN 
          UPDATE 
             SET 컬럼명 = 새로운 데이터 [, 컬럼명 = 새로운 데이터 ... ]
     WHEN NOT MATCHED THEN
   	      INSERT [(컬럼명1, 컬럼명2 ...)]
          VALUES (데이터1, 데이터2 ...);
          

// DEPARTEMENTS 테이블과 백업용 DEPARTMENTS_BACKUP 테이블이 있다고 가정하자. 지속적으로 두 테이블을 동기화 시켜줘야함.
// 전체 백업
MERGE
 INTO DEPARTMENTS_BACKUP DB
USING DEPARTMENTS D
   ON (DB.DEPARTMENT_ID = D.DEPARTMENT_ID)
 WHEN MATCHED THEN
      UPDATE
      	 SET DB.DEPARTMENT_NAME = D.DEPARTMENT_NAME,
             DB.MANAGER_ID = D.MANAGER_ID,
             DB.LOCATION_ID = D.LOCATION_ID
 WHEN NOT MATCHED THEN
      INSERT (DB.DEPARTMENT_ID, DB.DEPARTMENT_NAME, DB.MANAGER_ID, DB.LACATION_ID)
      VALUES (D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, D.LOCATION_ID);

//특정 조건 백업
MERGE
 INTO DEPARTMENTS_BACKUP DB
USING (SELECT * FROM DEPARTMENTS WHERE MANAGER_ID IS NOT NULL) D //여기에 조건 넣으면 됨.
   ON (DB.DEPARTMENT_ID = D.DEPARTMENT_ID)
 WHEN MATCHED THEN
      UPDATE
      	 SET DB.DEPARTMENT_NAME = D.DEPARTMENT_NAME,
             DB.MANAGER_ID = D.MANAGER_ID,
             DB.LOCATION_ID = D.LOCATION_ID
 WHEN NOT MATCHED THEN
      INSERT (DB.DEPARTMENT_ID, DB.DEPARTMENT_NAME, DB.MANAGER_ID, DB.LACATION_ID)
      VALUES (D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, D.LOCATION_ID);

TCL

• 트랜잭션을 제어하는 명령어 (COMMIT, ROLLBACK, SAVEPOINT)
• 트랜잭션: 쪼개질 수 없는 논리적인 업무처리의 단위.
• 특징

1. 원자성(Atomicity) : 모 아니면 도. 트랜잭션으로 묶인 일련의 동작들이 모두 성공하거나 모두 실패해야한다는 특징.

2. 일관성(Consistency) : 트랜잭션이 완료된 후에도 DB가 가진 데이터에 일관성이 있어야한다.

3. 고립성(Isolation) : 하나의 트랜잭션은 고립되어 수행되어야 한다. 다른 사람이 작업하고 있으면 그 사람의 트랜잭션이 끝날 때까지 대기해야함.

4. 지속성(durability) : 트랜잭션이 성공적으로 수행되었을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장되어야 함을 의미한다. 모든 트랜잭션이 로그에 남겨진 뒤 commit 되어야하고, 시스템 장애가 발생하더라도 복구 가능해야한다는 의미.

5. COMMIT: DML 명령어 후 변경된 내용을 확정, 변경하는 명령어. UPDATE한 뒤 오랜 시간 동안 COMMIT, ROLLBACK하지 않았을 경우 락에 걸려서 다른 사용자가 변경할 수 없는 상황이 발생할 수 있음.

6. ROLLBACK: DML 명령어 후 변경된 내용을 취소하는 명령어. UPDATE한 뒤 오랜 시간 동안 COMMIT, ROLLBACK하지 않았을 경우 락에 걸려서 다른 사용자가 변경할 수 없는 상황이 발생할 수 있음.

7. SAVEPOINT : ROLLBACK수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능을 가진 명령어. ROLLBACK 뒤에 특정 SAVEPOINT를 지정해주면 그 지점까지만 데이터가 복구됨.

DDL

• 데이터를 정의하는 SQL.
• NAME 컬럼에는 문자를 담고, OLD컬럼에는 숫자를 담을 것이다 와 같은 정의하는 언어.
• 문자: CHAR(고정. 20byte로 정의할 경우 그대로 20byte를 사용), VACHAR(가변. 20byte로 정의할 경우에도 문자가 6byte짜리면 6byte만 사용.)

1. CREATE

CREATE TABLE 테이블명(
	컬럼명1 데이터 타입 (DEFAULT / NULL여부),
	...
);

2. ALTER

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

• ADD COLUMN: 새로운 컬럼을 추가할 때 사용. 추가된 컬럼의 위치는 늘 맨 끝이 되고, 위치지정불가.

• DROP COLUMN: 기존에 있던 컬럼이 필요 없어졌을 때 삭제하는 명령어. 한번 삭제한 컬럼 복구 불가.

• MODIFY COLUMN : 기존에 있던 컬럼을 변경하고 싶을 때 쓰는 명령어. DEFAULT, NOT NULL 제약 조건에 대한 변경이 가능. 단 컬럼에 저장된 모든 데이터의 크기가 줄이고자 하는 컬럼의 크기보다 작을 경우에만 줄일 수 있음. 컬럼에 저장된 데이터가 없는 경우에만 데이터 유형을 변경할 수 있음. 크기를 늘리는 것은 데이터와 상관없이 가능.

• DEFAULT 값 변경 시 변경 이후 저장되는 데이터에만 적용됨.

• 현재 NULL값이 저장되어 있지 않은 컬럼에만 NOT NULL 제약조건 추가 가능.

• RENAME COLUMN : 기존에 있던 컬럼의 이름을 변경하고 싶을 때 쓰는 명령어.

• ADD CONSTRAINT : 제약 조건을 추가하고 싶을 때 쓰는 명령어.

• DROP TABLE : 테이블 삭제할 때 사용하는 명령어. 만약 해당 테이블을 참조하고 있는 다른 테이블이 존재하는 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않음.

• CASCADE CONSTRAINT는 참조 제약조건도 함께 삭제한다는 의미.

• TRUNCATE TABLE : 테이블에 저장되어 있는 데이터를 모두 제거하는 명령어. 저장공간이 재사용되도록 초기화.

DCL

• USER를 생성, 권한 부여하는 명령어.

1. USER 관련 명령어

1) CREATE USER

• 사용자 생성하는 명령어

CREATE USER 사용자명 IDENTIFIED BY 패스워드;

2) ALTER USER

• 사용자를 변경하는 명령어

ALTER USER 사용자명 IDENTIFIED BY 패스워드

3) DROP USER

• 사용자를 삭제하는 명령어

DROP USER 사용자명;

2. 권한 관련 명령어

1) GRANT

GRANT 권한 TO 사용자명;

2) REVOKE

REVOKE 권한 FROM 사용자명;