Part2 - Ch3. 관리 구문

01) DML

• Data Manipulation Language
• DDL에서 정의한 대로 데이터를 입력하고, 입력된 데이터를 수정, 삭제, 조회하는 명령어
• INSERT, UPDATE, DELETE, MERGE

1. INSERT

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

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

• 첫 번째 SQL의 경우 명시되지 않은 컬럼에는 NULL 값이 입력되는데 PK나 NOT NULL 제약조건이 걸린 컬럼에는 NULL 값이 입력될 수 없으니 주의해야 함.

• 두 번째 SQL의 경우 전체 컬럼에 대한 데이터가 테이블의 컬럼 순서대로 빠짐없이 나열되어야 하는데 순서가 뒤바뀌어 데이터 유형이 맞지 않거나 누락된 데이터가 있어 전체 컬럼 개수와 맞지 않았을 경우 데이터베이스는 에러를 발생시킴.

 
2. UPDATE

• 이미 저장된 데이터를 수정하고 싶을 때 사용하는 명령어
• 수정하고 싶은 컬럼이 많다면 SET 절에 ,(콤마)로 이어서 명시 
  • SET 컬럼명1 = 데이터, 컬럼명2 = 데이터, ...
• WHERE절이 없으면 모든 Row가 변경되니 주의

UPDATE 테이블명 SET 컬럼명 = 새로운 데이터 (WHERE 수정할 데이터에 대한 조건);

 
3. DELETE

• 이미 저장된 데이터를 삭제하고 싶을 때 사용하는 명령어
• WHERE 절이 없으면 테이블의 모든 Row가 삭제되니 주의

DELETE FROM 테이블명 (WHERE 수정할 데이터에 대한 조건);

• 만약 WHERE 절 없이 정말 테이블 전체 데이터를 삭제하고자 하는 경우 이전 장에 나와 있는 TRUNCATE 명령어를 사용할 수도 있는데,
• 삭제하고자 하는 마음이 확고하다면 TRUNCATE를 쓰는 게 시스템 부하 측면에서 유리
• 대신 TRUNCATE는 별도의 로그를 쌓지 않아 ROLLBACK이 불가능하며
• DELETE는 COMMIT 전에 ROLLBACK이 가능

INSERT, UPDATE, DELETE
명령어를 날리고 별도의 COMMIT 명령어를 실행시켜 주어야 데이터가 반영되며 ROLLBACK도 가능

“INSERT, UPDATE, DELETE 명령어를 실행해도 바로 반영되지 않고,
COMMIT을 날려야 최종 저장(영구 반영)되고, 그 전에 ROLLBACK을 실행하면 실행된 변경들을 모두 되돌릴 수 있다”

 
4. MERGE

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

MERGE
	INTO 타겟 테이블명 # 테이블의 데이터를 변경 또는 생성해라
   USING 비교 테이블명 # 변경 또는 생성할 때 테이블을 이용해라
   	  ON 조건 
   WHERE MATCHED THEN # 조건에 맞는 데이터가 있으면 
   		 UPDATE #변경해라
         	SET 컬럼명 = 새로운 데이터 [, 컬럼명 = 새로운 데이터 ...]
   WHEN NOT MATCHED THEN # 조건에 맞는 데이터가 없으면 그 데이터를 생성해라 
   		INSERT [(컬럼명1, 컬럼명2, ...)] 
        VALUES (데이터1, 데이터2, ...);

 

02) TCL

• 트랜잭션을 제어하는 명령어로 COMMIT, ROLLBACK, SAVEPOINT가 있음.
• Transaction Control Language
  • 트랜잭션이란 쪼개질 수 없는 업무처리의 단위
  • 한 세트로 묶일 수밖에 없는 논리적인 업무 단위

1. 트랜잭션의 특징

• 원자성
  • 트랜잭션으로 묶인 일련의 동작들은 모두 성공하거나 모두 실패해야 함. 즉, 살아도 같이 살고 죽어도 같이 죽는 관계
• 일관성
  • 트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 함.
  • ex) 이미 결제된 티셔츠의 수량과 남아있는 티셔츠의 재고 합은 항상 쇼핑몰이 처음 보유하고 있었던 티셔츠의 총 수량과 일치해야 함.
• 고립성
  •  하나의 트랜잭션은 고립되어 수행되어야 함. 
  • ex) 만약 내가 구매하고자 하는 티셔츠를 지금 다른 사람이 먼저 구매하고 있다면 나는 재고 데이터를 참조하거나 변경할 수 없고 그 사람의 트랜잭션이 끝날 때까지 대기해야 함.
• 지속성
  • 트랜잭션이 성공적으로 수행되었을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장되어야 함. 
  • 모든 트랜잭션이 로그에 남겨진 뒤 COMMIT되어야 하고, 그래서 시스템 장애가 발생하더라도 복구 가능해야한다는 의미.

2. COMMIT

• INSERT, DELETE, UPDATE 후 변경된 내용을 확정, 반영하는 명령어
• COMMIT을 실행하지 않으면 메모리까지만 반영되는데 메모리는 휘발성이기 때문에 언제든 사라질 수 있고 다른 사용자는 변경된 값을 조회할 수 X
• COMMIT을 실행해야 최종적으로 데이터 파일에 기록이 되고 비로소 트랜잭션이 완료되는 것
• UPDATE한 뒤 오랜 시간동안 COMMIT이나 ROLLBACK을 하지 않았을 경우 LOCK에 걸려서 다른 사용자가 변경할 수 없는 상황이 발생할 수 있으니 주의

3. ROLLBACK

• INSERT, DELETE, UPDATE 후 변경된 내용을 취소하는 명령어
• ROLLBACK을 하면 변경하기 이전 값으로 복구
• UPDATE를 한 뒤 오랜 시간동안 COMMIT이나 ROLLBACK을 하지 않았을 경우 LOCK에 걸려서 다른 사용자가 변경할 수 없는 상황이 발생할 수 있으니 주의

4. SAVEPOINT

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

---

• 예제

*가정: 작업 전 SAMPLE 테이블의 상태

COL1	COL2	COL3
1	가	A
2	나	B
3	라	C

 

1. INSERT INTO SAMPLE VALUES (4,'라','D');
   

   • 설명: (4,'라','D') 행이 추가됩니다.
   • 현재 상태: 1,2,3번 행 + 새로 추가된 4번 행

2. SAVEPOINT A;
   

   • 설명: “A”라는 이름의 저장점(savepoint)을 설정합니다.
   • 의의: 이 지점 이후의 작업을 언제든 “A 지점”으로 되돌릴 수 있습니다.

3. UPDATE SAMPLE SET COL2 = '나' WHERE COL1 IN (1,3);
   

   • 설명: COL1=1과 COL1=3 행의 COL2 값을 모두 '나'로 바꿉니다.
   • 현재 상태:
   • (1,'나',A), (2,'나',B), (3,'나',C), (4,'라',D)

4. SAVEPOINT B;
   

   • 설명: “B” 저장점을 설정합니다.
   • 의의: A 이후 변경 중 “B 지점”으로도 되돌릴 수 있지만, 이번엔 사용되지 않습니다.

5. DELETE FROM SAMPLE WHERE COL2 = '라';
   

   • 설명: COL2='라'인 행을 삭제합니다.
   • 이 시점의 ‘라’ 행들:
     • 원래 3번은 이미 ‘나’로 바뀌었으므로 삭제되지 않고,
     • 4번('라','D')만 삭제됩니다.
   • 현재 상태:
   • (1,'나',A), (2,'나',B), (3,'나',C)

6. ROLLBACK TO A;
   

   • 설명: “A” 저장점 이후의 모든 작업을 취소합니다.
   • 취소되는 작업:
     • 3번 단계의 UPDATE
     • 4번 단계의 SAVEPOINT B
     • 5번 단계의 DELETE
   • 롤백 후 상태:(4번 행도 복원되고, 1·3번의 COL2도 원래대로 돌아갑니다.)
   • (1,'가',A), (2,'나',B), (3,'라',C), (4,'라',D)

7. DELETE FROM SAMPLE WHERE COL2 = '나';
   

   • 설명: 현재 COL2='나'인 행을 삭제합니다.
   • 삭제 대상: 원래부터 '나'였던 2번 행만 삭제됩니다.
   • 현재 상태:
   • (1,'가',A), (3,'라',C), (4,'라',D)

8. COMMIT;
   

   • 설명: 지금까지의 변경 사항을 최종 확정하여 디스크에 영구 반영합니다.
   • 최종 결과:

     COL1	COL2	COL3
     1	가	A
     3	라	C
     4	라	D

---

03) DDL

• 데이터를 정의하는 명령어로 CREATE, ALTER, DROP, RENAME, TRUNCATE가 있음.
• Data Definition Language란 의미로, 데이터 정의어, 즉 데이터를 정의하는 SQL
• CREATE 쿼리를 수행할 때 테이블을 생성하면서 그 안에 담게 될 데이터에 대한 데이터 유형을 정해주도록 되어 있는데, 예를 들면 'NAME 컬럼에는 문자를 담을 거야', 'OLD 컬럼에는 숫자를 담을 거야'라고 정하고 SQL에 명시해 줌으로써 데이터를 정의해주는 것
• 데이터 유형은 문자, 숫자, 날짜 타입으로 나뉨. 데이터베이스가 데이터를 어떤 방식으로 저장할지 정하는 기준
  • ex) 20210101을 데이터베이스에 저장한다고 했을 때 이것은 데이터베이스 입장에서 문자도 될 수 있고 숫자도 될 수 있고 날짜도 될 수 있음. 실제로 실문에서는 날짜 데이털르 문자형으로 정의하여 쓰는 경우가 많은데 그럴 경우 입력 시 ' ' 로 감싸서 '20210101' 형식으로 입력해야 함. 만약 선언해놓은 유형이 아닌 다른 유형의 데이터를 저장하려고 하면 데이터베이스는 에러를 발생시키기 때문에 주의가 필요함. 
  • 데이터 유형을 정의하면서 크기(Size)도 함께 정해주게 되는데 NAME 컬럼의 경우 이름이 매우 길다고 해도 수십 글자가 되는 사람은 없을 것이므로 그것에 맞게 적절한 크기로 정의해주면 됨.
  • 여기서 주의할 점은 영어와 한글의 BYTE 수가 다르다는 점인데 영문이 입력될 컬럼인지 한글이 입력될 컬럼인지를 고려하여 크기를 계산해야 함.
  • 정의된 크기보다 더 큰 데이터를 저장하려고 할 때에도 데이터베이스는 에러를 발생시키기 때문에 주의가 필요.

유형	데이터 타입
문자	CHAR (고정) VARCHAR (가변) CLOB
숫자	NUMBER
날짜	DATE

---

1. CREATE

• 테이블을 생성하기 위한 명령어

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

• NULL: 공백과는 다르며, 존재하지 않는 값
• DEFAULT: 데이터의 기본값
  • DEL_YN(삭제여부) 컬럼의 DEFAULT를 'N'으로 정의해 놓으면 DEL_YN 값을 별도로 명시하지 않았을 때 NULL 대신 'N'이 저장
• 테이블 생성 시 반드시 지켜야 할 규칙
  1. 테이블명은 고유해야 함
  2. 한 테이블 내에서 컬럼명은 고유해야 함
  3. 컬럼명 뒤에 데이터의 유형과 크기가 명시
  4. 컬럼에 대한 정의는 () 안에 기술
  5. 각 컬럼은 , 로 구분
  6. 테이블명과 컬럼명은 숫자로 시작 X
  7. 마지막은 ; 로 끝
• CREATE TABLE을 할 때 CONSTRAINT도 함께 정의해줄 수 있는데,
• CONSTRATINT는 테이블에 저장될 데이터의 무결성, 즉 데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하기 위해 해놓는 장치

제약 조건의 종류

PIMARY KEY(기본키)
- 테이블에 저장된 각각의 Row에 대한 고유성 보장.
- 한 테이블에 하나씩만 정의 가능하며 PK로 지정된 컬럼에는 NULL 값이 입력될 수 없고 자동으로 UNIQUE 인덱스로 생성됨.
* 테이블당 1개씩만 생성할 수 있고 생성을 하지 않는 것도 가능함.

UNIQUE KEY(고유키)
- PRIMARY KEY와 유사하게 테이블에 저장된 각각의 Row에 대한 고유성 보장하기 위한 제약조건
- NULL 값이 허용된다는 차이점

NOT NULL
- 해당 컬럼에는 NULL 값이 입력되는 것을 허용하지 않는 제약조건
- CHECK 컬럼에 저장될 수 있는 값의 범위를 제한

FOREIGN KEY(외래키)
- 하나의 테이블이 다른 테이블을 참조하고자 할 때 FK를 정의.

• 완전히 새로운 테이블을 생성하는 것이 아니고 기존에 존재하던 테이블을 복사해서 생성하고 싶은 경우 CTAS (CREATE TABLE ~ AS SELECT ~) 문을 활용
  • 컬럼별로 데이터 유형 다시 명시 안해도 됨.
  • 제약 조건의 100%가 복사되는 것은 아니고 NOT NULL 조건만 되며 PRIMARY KEY, UNIQUE KEY, CHECK 등의 제약조건은 초기화되므로 필요할 경우 별도로 ALTER 명령어 써서 정의해야 함.

CREATE 테이블명 AS SELECT * FROM 복사할 테이블명;

 
2. ALTER

• 보통 한 번 생성된 테이블의 구조는 변경하지 않는 것이 일반적이지만, 시대의 흐름과 업무 요건의 변화로 인해 변경이 불가피한 상황이 생길 수 있음. 
• ex) 우편번호가 예전엔 6자리였지만 현재 5자리로 변경됨.

---

1. ADD COLUMN
   • 새로운 컬럼을 추가할 때 쓰는 명령어
   • 추가된 컬럼의 위치는 늘 맨 끝이 되며 별도로 위치 지정X
   • ALTER TABLE 테이블명 ADD 컬럼명 데이터 유형;
2. DROP COLUMN
   • 기존에 있던 컬럼이 필요 없어졌을 때 삭제하는 명령어
   • 한번 삭제한 컬럼은 복구할 수 없으므로 주의
   • ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
3. MODIFY COLUMN
   • 기존에 있던 컬럼을 변경하고 싶을 때 쓰는 명령어
   • 데이터 유형, DEFAULT 값, NOT NULL 제약조건에 대한 변경이 가능
   • 컬럼에 저장된 모든 데이터의 크기가 줄이고자 하는 컬럼의 크기보다 작을 경우에만 줄일 수 있고, 컬럼에 저장된 데이터가 없는 경우에만 데이터 유형을 변경할 수 있음. (크기를 늘리는 것은 데이터 상관없이 가능)
   • DEFAULT 값 변경 시에는 변경 이후 저장되는 데이터에만 적용되며 현재 NULL 값이 저장되어 있지 않은 컬럼에만 NOT NULL 제약 조건 추가가 가능
   • ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형 [DEFAULT 값] [NOT NULL], 컬럼명2 데이터 유형 ...);
4. RENAME COLUMN
   • 기존에 있던 컬럼의 이름을 변경하고 싶을 때 쓰는 명령어
   • ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼명 TO 변경할 컬럼명;
5. ADD CONSTRAINT
   • 제약조건을 추가하고 싶을 때 쓰는 명령어
   • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);

---

3. DROP TABLE

• 테이블을 삭제할 때 쓰는 명령어
• 만약 해당 테이블을 참조하고 있는 다른 테이블이 존재하는 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않음.
• CASCADE CONSTRAINT는 참조 제약조건도 함께 삭제한다는 의미

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 
4. RENAME TABLE

• 테이블명 변경할 때 쓰는 명령어

RENAME 기존 테이블명 TO 변경할 테이블명;

 
5. TRUNCATE TABLE

• 테이블에 저장되어 있는 데이터를 모두 제거하는 명령어
• DELETE 명령어와 유사하지만 저장 공간이 재사용되도록 초기화된다는 차이점이 있고 
• ROLLBACK이 불가능해 DDL로 분류
• 데이터는 삭제하지만 테이블을 삭제하지는 않음. (테이블 삭제하는 명령어는 DROP)

TRUNCATE TABLE 테이블명;

 

04) DCL

• USER을 생성하고 권한을 부여하는 명령어로 CREATE USER, ALTER USER, DROP USER가 있음.
• Data Control Language란 의미로, USER를 생성하고, USER에게 데이터를 컨트롤할 수 있는 권한을 부여하거나 회수하는 명령어

---

1. USER 관련 명령어

• 하나의 DATABASE는 여러 개의 USER를 가질 수 있음.

(1) CREATE USER

• 사용자를 생성하는 명령어
• CREATE USER 권한이 있어야 수행 가능

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

 
(2) ARTER USER

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

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

 
(3) DROP USER

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

DROP USER 사용자명;

 

---

2. 권한 관련 명령어
 
(1) GRANT

• 사용자에게 권한을 부여하는 명령어

GRANT 권한 TO 사용자명;

 
(2) REVOKE

• 사용자에게 권한을 회수하는 명령어

REVOKE 권한 FROM 사용자명;

---

3. ROLE 관련 명령어

• ROLE이란 특정 권한들을 하나의 세트처럼 묶는 것
• CREATE SESSION, CREATE USER, CREATE TABLE 권한을 묶어서 CREATE_R이라고 지정할 수 있음.

(1) ROLE을 이용한 권한 부여

• ROLE을 생성한다

CREATE ROLE 롤명;

• ROLE에 권한을 부여한다

GRANT 권한 TO 롤명;

• ROLE을 사용자에게 부여한다

GRANT 롤명 TO 사용자명;

---

분류	역할	주요 명령어
DDL (Data Definition Language)	데이터베이스 객체(테이블, 뷰, 인덱스 등)의 생성·수정·삭제	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML (Data Manipulation Language)	테이블에 저장된 데이터의 조회·삽입·수정·삭제	SELECT, INSERT, UPDATE, DELETE, MERGE
DCL (Data Control Language)	사용자·권한 관리	GRANT, REVOKE
TCL (Transaction Control Language)	트랜잭션(작업 단위) 시작·확정·취소	COMMIT, ROLLBACK, SAVEPOINT,SET TRANSACTION