[SQLD] 관계형 데이터베이스, DML, DDL, TCL

2022.02.04 - [database] - [SQLD] SQL 문장들의 종류 - DML, DDL, DCL, TCL

[SQLD] SQL 문장들의 종류 - DML, DDL, DCL, TCL

---

테이블 생성의 주의사항

- 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.

- 테이블명은 다른 이름과 중복되지 않아야 한다.

- 한 테이블 내에서는 컬럼명이 중복되게 지정할 수 없다.

- 테이블 이름을 지정하고 각 컬럼들은 괄호 "()" 로 묶어 지정한다.

- 각 컬럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다.

- 컬럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관서 ㅇ있게 사용하는 것이 좋다. (데이터 표준화 관점)

- 컬럼 뒤에 데이터유형은 꼭 지정되어야 한다.

- 테이블명과 컬럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

- 벤더에서 사전에 정의한 예약어 (Reserved word)는 쓸 수 없다.

- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

 

제약조건 (Constraint)

- Check 제약 조건은 데이터베이스에서 데이터의 무결성을 유지하기 위하여 테이블의 특정 컬럼에 설정하는 제약이다.

- 기본키는 반드시 테이블 당 하나의 제약만을 정의할 수 있다.

- 고유키로 지정된 모든 컬럼들은 null값을 가질 수 있다.

- 외래키는 테이블간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키가 참조하도록 생성한다.

 

참조 동작(Refercntial Action) 정리

Delete(Modify) Action 

1. Cascade : Master 삭제 시 Child 같이 삭제

2. Set Null : Master 삭제 시 Child 해당 필드 Null 처리

3. Set Default : Master 삭제 시 Child 해당 필드 Default 값으로 설정

4. Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용

 

Insert Action 

1. Automatic : Master 테이블에 PK가 없는 경우에 Master PK를 생성 후 Child 입력

2. Set Null : Master 테이블에 PK가 없는 경우 Child 외부키를 Null값으로 처리

3. Set Default : Master 테이블에 PK가 없는 경우 Child 외부키를 Default 값으로 설정

4. Dependent : Master 테이블에 PK값이 존재할 때만 Child 입력 허용

 

테이블 불필요한 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;

 

RENAME

RENAME 테이블명 TO 바꿀 테이블 명;

 

DELETE vs TRUNCATE vs DROP

TRUNCATE 

테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납

- 해당 테이블의 데이터가 모두 삭제되지만 테이블 자체가 지워지는 것은 아님

- 해당 테이블에 생성되어 있던 인덱스도 함께 truncate 됨

 

DROP

테이블 자체를 삭제하는 명령어

- 테이블 자체가 모두 지워지며, 해당 테이블에 생성되어 있던 인덱스도 삭제됨

 

DELETE 후에는 데이터만 지워지고 쓰고 있던 디스크 상의 공간은 그대로 가지고 있다.

TRUNCATE 직업은 최초 테이블이 만들어졌던 상태, 즉 데이터가 1건도 없는 상태로 모든데이터 삭제, 컬럼값만 남아 있다.

그리고 용량또 줄고 인덱스 등고 모두 삭제됨

-> DELETE 보다 TRUNCATE가 더 좋아 보이나 DELETE는 원하는 데이터만 골라서 삭제가 가능하나 TRUNCATE는 그렇지 못한다.

DROP 명령어는 데이터와 테이블 전체를 삭제하게 되고 사용하고 있던 공간도 모두 반납하고 인덱스나 제약조건 등 오브젝트로 삭제된다.

 

트랜잭션 (Transaction)

특징

- 원자성 (atomicity) : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다. (all or nothing)

- 일관성 (consistency) : 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 된다.

- 고립성 (isolation) : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.

- 지속성 (durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

 

트랜잭션(Transaction)은 데이터베이스의 논리적 연산단위로서 밀접히 관련되어 불니될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

트랜잭션의 종료를 위한 대표적 명령어로서는 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영하는 COMMIT과 데이터에 대한 변경사항을 모두 폐기하고 변경전의 상태로 되돌리는 ROLLBACK이 있다.

테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 취소할 수 있는데 데이터베이스에서는 롤백(ROLLBACK) 기능을 사용한다.

롤백(ROLLBACK)은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.( 커밋 되지 않은 상태의 모든 트랜잭션을 롤백한다.)

 

BEGIN TRANSACTION으로 트랜잭션을 실행하고 COMMIT TRANSACTION 또는 ROLLBACK TRANSACTION으로 트랜잭션을 종료한다. ROLLBACK구문을 만나면 최초의 BEGIN TRANSACTION 시점까지(COMMIT되지 않은 상위의 모든 트랜잭션) 모두 ROLLBACK이 수행된다.

 

저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백 할 수 있다.

SAVE TRANSACTION A;
...
ROLLBACK TRANSCATION A;