[SQL] 관리 구문 - DML, TCL, DDL, DCL

 

 

1. DML (Data Manipulation Language)

DML은 Data Manipulation Language란 의미로 DDL에서 정의한 대로 데이터를 입력하고 입력된 데이터를 수정, 삭제, 조회하는 명령어

 

1) INSERT

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

INSERT INTO 테이블명 VALUES (전체 컬럼에 입력될 데이터 리스트);

 

또는

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

 

첫번째 쿼리에서 명시되지 않은 컬럼에는 NULL이 입력되는데 PK나 NOT NULL 제약 조건이 걸린 컬럼에는 NULL 값이 입력될 수 없으니 주의해야한다. 

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

 

 

2) UPDATE

이미 저장된 데이터를 수정하고 싶을 때 사용하는 명령어. 

수정하고 싶은 컬럼이 많으면 set 절에 , 로 이어서 명시해줄 수 있다. (set 컬럼명1 = 데이터, 컬럼명2 = 데이터...) where 절이 없으면 테이블의 모든 row가 변경되니까 주의해야한다.

 

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

 

3) DELETE

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

 

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

 

테이블 전체 데이터를 삭제하고 싶으면 truncate 명령어를 사용할 수 있다. truncate 를 사용하는 것이 시스템 부하 측면에서 유리하다. 대신 truncate는 별도의 로그를 쌓지 않아 ROLLBACK이 불가능하며 delete는 commit 전에 rollback이 가능하다. 

 

 

4) MERGE

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

Merge는 데이터베이스에서 데이터를 INSERT, UPDATE, DELETE 하는 통합 SQL 문이다. Merge를 사용하면 여러 개의 DML 문을 하나의 문으로 처리할 수 있어 코드 작성이 간단해지고 성능이 향상된다.

 

MERGE
INTO 대상테이블 별칭
USING 소스테이블 별칭
	ON (조인조건)
WHEN MATCHED THEN
  UPDATE SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHEN NOT MATCHED THEN
  INSERT (컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...)

• MERGE INTO: 데이터를 병합할 대상 테이블을 지정한다.
• USING: 데이터를 가져올 소스 테이블을 지정한다.
• ON: 대상 테이블과 소스 테이블을 연결할 조인 조건을 지정한다.
• WHEN MATCHED THEN: 대상 테이블과 소스 테이블의 데이터가 일치할 경우 UPDATE 작업을 수행.
• WHEN NOT MATCHED THEN: 대상 테이블과 소스 테이블의 데이터가 일치하지 않을 경우 INSERT 작업을 수행.

 

아래 데이터로 merge 테스트를 해보겠다. 

 

<Customer>

ID	NAME	EMAIL
1	John Doe	john.doe@example.com
2	Jane Smith	jane.smith@example.com

 

<new_customer>

ID	NAME	EMAIL
1	John Doe	john.doe@example.com
2	Bob Johnson	bob.johnson@example.com

 

MERGE INTO customer c
USING new_customer nc
ON (c.id = nc.id)
WHEN MATCHED THEN
  UPDATE SET c.name = nc.name, c.email = nc.email
WHEN NOT MATCHED THEN
  INSERT (c.id, c.name, c.email) VALUES (nc.id, nc.name, nc.email);

 

<결과>

 

고객 테이블(customer)
ID | NAME         | EMAIL
---+---------------+--------------------
1  | John Doe     | john.doe@example.com
2  | Jane Smith   | jane.smith@example.com
3  | Bob Johnson  | bob.johnson@example.com

 

 

2. TCL

 

TCL(Transaction Control Language)은 트랜잭션을 제어하는 명령어로 COMMIT, ROLLBACK, SAVEPOINT가 있다.

트랜잭션은 쪼개질 수 없는 업무 처리의 단위인데 무조건 하나로 묶일 수 밖에 없는 작업들이다.

 

예를 들어서, 은행 계좌 간 송금 작업은

• 계좌 A에서 금액 인출
• 계좌 B에 동일 금액 입금

이 두 작업은 하나의 트랜잭션으로 처리되어야 한다. 하나라도 처리되지 않으면 실패!!!!!!!!!!!!!!!!!!!!!

이런 작업을 트랜잭션이라고 생각하면 된다. 

 

• 온라인 쇼핑 주문 처리:
  • 상품 선택
  • 결제
  • 배송 정보 입력
  • 이 모든 작업이 하나의 트랜잭션으로 처리되어야 함

위와 같은 예시도 하나의 작업으로 묶이는 트랜잭션이다. 

트랜잭션은 한꺼번에 커밋됨. 예를 들어서 상품선택 후 커밋, 결제 후 커밋이 안된다. 온라인 쇼핑 주문 처리가 모두 되어야 커밋된다. 

 

 

1) 트랜잭션의 특징

 

트랜잭션의 특성(ACID) 독일지원 으로 외움 편함. 

• 원자성(Atomicity): 트랜잭션의 모든 작업이 성공적으로 완료되거나 전체가 취소됨.
  죽거나 살거나 두 경우 밖에 없음. all or nothing
• 일관성(Consistency): 트랜잭션 실행 전후 데이터베이스의 일관성이 유지됨 
  예를 들어 이미 결제된 티셔츠의 수량과 남아있는 티셔츠 재고의 합은 언제나 쇼핑몰이 처음 보유하고 있던 티셔츠의 총 수량과 일치해야함. 
• 독립성(Isolation): 트랜잭션 간 간섭 없이 독립적으로 실행됨
  만약 내가 구매하고자 하는 치셔츠를 지금 다른 사람이 먼저 구매하고 있다면 나는 재고 데이터를 참조하거나 변경할 수 없고 그 사람의 트랜잭션이 끝날 때 까지 대기해야한다. 
• 지속성(Durability): 트랜잭션이 성공적으로 완료되면 그 결과가 영구적으로 저장됨
  모든 트랜잭션이 로그에 남겨진 뒤 commit 되야 하고 시스템 장애가 발생해도 복구 가능해야한다. 

 

2) COMMIT

• Commit은 데이터베이스 트랜잭션에서 변경된 데이터를 영구적으로 저장하는 작업을 의미. 
• 트랜잭션 내에서 수행된 모든 작업들이 성공적으로 완료되면, Commit 명령을 통해 데이터베이스에 반영. 
• Commit은 트랜잭션의 변경 사항을 영구적으로 저장하고, 트랜잭션을 종료.

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

 

 

3) ROLLBACK

• Rollback은 데이터베이스의 이전 상태로 복구하는 작업을 의미. 
• Rollback은 특정 트랜잭션 또는 트랜잭션 집합을 취소하여 데이터베이스를 이전 상태로 되돌린다. 
• Rollback 작업을 통해 마지막 Commit 이후 수행된 모든 변경 사항이 취소되며, 데이터베이스가 이전 상태로 복구된다. 

 

4) SAVEPOINT

 

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

 

 

 

3. DDL

DDL(Data Definition Language)

DDL은 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 생성, 수정, 삭제하는 데 사용되는 SQL 언어.

 

1) CREATE

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

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

 

CREATE TABLE customer (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50) NOT NULL,
  email VARCHAR2(100)
);

 

테이블 생성 시 반드시 지켜야할 규칙은 다음과 같다. 지키지 않을 경우 에러가 발생한다. 

• 테이블명은 고유
• 한 테이블 내에서 컬럼명은 고유
• 컬럼명 뒤 데이터 유형과 데이터 크기가 명시
• 컬럼에 대한 정의는 괄호 안에 기술
• 각 컬럼들은 , 콤마로 구분
• 테이블명과 컬럼명은 숫자로 시작될 수 없다.
• 마지막은 ; 세미콜론으로 끝난다. 

CREATE TABLE 시 제약조건도 함께 정의해줄 수 있다. 제약조건을 통해 데이터의 무결성을 보장할 수 있다. NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT 등의 제약조건을 사용하여 테이블의 데이터 규칙을 정의할 수 있다. 이를 통해 데이터베이스의 데이터 품질을 높일 수 있다. 

 

주요 제약조건 종류

1. NOT NULL
   • 해당 컬럼에 NULL 값이 저장되는 것을 방지.
   • 예시: CREATE TABLE users (id INT NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(50));
2. UNIQUE
   • 해당 컬럼의 값이 중복되지 않도록 함.
   • 예시: CREATE TABLE users (id INT UNIQUE, name VARCHAR(50) NOT NULL, email VARCHAR(50) UNIQUE);
3. PRIMARY KEY
   • 해당 컬럼의 값이 고유하고 NULL 값이 허용되지 않음.
   • 예시: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(50));
4. FOREIGN KEY
   • 다른 테이블의 기본키를 참조하는 컬럼.
   • 예시: CREATE TABLE orders (id INT PRIMARY KEY, user_id INT FOREIGN KEY REFERENCES users(id), product VARCHAR(50));
5. CHECK
   • 해당 컬럼의 값이 지정된 조건을 만족해야 한다.
   • 예시: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT CHECK (age >= 18));
6. DEFAULT
   • 해당 컬럼의 값이 지정되지 않으면 기본값이 저장.
   • 예시: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(50) DEFAULT 'example@email.com');

완전 새로운 테이블을 생성하는 것이 아니고 기존에 존재하던 테이블을 복사해서 생성하고 싶은 경우.

 

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

 

위와 같은 쿼리를 활용할 수 있다. 컬럼 별로 데이터 유형을 다시 명시해주지 않아도 되는 장점이 있지만 제약조건이 모두 복사되는 것이 아니라 not null 조건만 복사되기 때문에 primary key, unique key, check 등의 제약조간은 alter 명령어를 써서 정의해주어야 한다.

 

2) alter

컬럼추가, 컬럼 변경, 컬럼 삭제, 제약조건 추가, 제약조건 삭제 등을 할 수 있게 하는 테이블 구조를 변경할 때 쓸 수있는 명령어

1. add column

새로운 컬럼을 추가할 때 쓰는 명령어

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

 

2. drop column

컬럼을 삭제하는 명령어. 한번 삭제한 컬럼은 복구할 수 없다. 

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

 

3. modify column

기존에 있던 컬럼을 변경하고 싶을 때 쓰는 명령어.

 

데이터 유형, Default 값, not null 제약 조건에 대한 변경이 가능하다. 단 컬럼에 저장된 모든 데이터의 크기가 줄이고자 하는 컬럼의 크기보다 작을 경우에만 줄일 수 있고, 컬럼에 저장된 데이터가 없는 경우에만 데이터 유형을 변경할 수 있다. 크기를 늘리는 것은 데이터와 상관없이 가능. Defalut 값 변경 시에는 변경 이후 저장되는 데이터에만 적용되며 현재 null값이 저장되어 있지 않은 컬럼에만 Not null 제약 조건 추가가 가능하다.  

 

ALTER TABEL 테이블명 MODIFY (컬럼명1 데이터 유형 [defalut값] [not null] 컬럼명2 데이터 유형..);

 

4. rename column

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

ALTER TABEL 테이블명 RENAME COLUMN 변경할 컬럼명 TO 변경할 이름;

 

6. Drop Table

테이블을 삭제할 때 사용하는 명령어입니다. 이 명령어를 사용하면 테이블의 모든 데이터와 테이블 자체가 삭제.

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

 

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

7. Truncate table

테이블에 저장되어 있는 데이터를 모두 제거하는 명령어. delete 명령어와 유사하지만 저장공간이 재사용되도록 초기화된다는 차이점이 있고 rollback이 불가능해 ddl로 분류.

 

TRUNCATE TABLE 테이블명;

 

 

4. DCL

DCL은 Use를 생성하고 권한을 부여하는 명령어로 create user, alter user, drop user이 있음.

Data Control Language란 의미. 

 

1) user 관련 명령어

하나의 데이터베이스는 여러 개의 user를 가질 수 있다. 하지만 각각의 user의 각기 다른 비밀번호를 지정해주어야 한다. 

 

1. create user

사용자를 생성하는 명령어. create user 권한이 있어야 수행 가능하다. 

CREATE USER 사용자명 IDENTIFIED BY '비밀번호';

 

2. alter user

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

ALTER USER 사용자명 IDENTIFIED BY '새비밀번호';

 

3. drop user

DROP USER 사용자명;

 

 

2) 권한 관련 명령어

1. GRANT

GRANT 권한 TO 사용자명;

GRANT SELECT, INSERT ON 테이블명 TO john;

 

2. REVOKE

 

사용자에게 권한을 회수하는 명령어이다.

REVOKE 권한 FROM 사용자명;

REVOKE SELECT, INSERT ON 테이블명 FROM john;

 

 

3. ROLE 관련 명령어

role은 특정 권한들을 하나의 세트처럼 묶는 것. create session, create user, create table 권한을 묶어서 create_r이라고 지정할 수 있다. 

 

1. ROLE 생성

create role 롤명;

 

2. ROLE에 권한을 부여. 

grant 권한 to 롤명;

 

 

3. ROLE을 사용자에게 부여. 

grant 롤명 to 사용자명;