SQL 기본 이해

1. DDL

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

• 데이터베이스 스키마를 컴퓨터가 이해할 수 있도록 기술하는데 사용
• 테이블 구조 또는 객체의 정의/생성, 변겅, 삭제를 담당
• 데이터 정의어에 해당되는 주요 명령어는 CREATE, DROP, ALTER, RENAME 등이 있음

SQL문	내용
CREATE	- 데이터베이스 객체를 생성 - 테이블, 뷰, 인덱스를 정의하거나 스키마, 함수 등을 정의 - 테이블 생성시 테이블 생성과 함께 제약조건 정의 가능
DROP	- 데이터베이스 객체를 삭제 - 테이블 삭제시 해당 테이블과 관계가 있었던 찹조되는 제약조건을 먼저 삭제해야 함. 오라클의 경우 CASECADE CONSTRAINT 옵션 사용시 동시 삭제 가능
ALTER	- 기존에 존재하는 데이터베이스 객체를 변경 - 테이블 컬럼 추가, 이름 변경 등 테이블의 구조를 변경

 

오라클/Postgresql 데이터베이스 객체(Objects)

Object	설명
Table (테이블)	- Row(행)과 column(컬럼)의 2차원 구조를 가진 데이터베이스의 가장 기본적 데이터의 저장 단위
View (뷰)	- 하나 또는 그 이상의 테이블에서 원하는 데이터만을 모아 가상적으로 만든 테이블 - 자주 사용되는 질의를 정의해 둠으로써 반복되는 데이터 조작을 효율적으로 수행 - 사용자가 관심을 가지는 데이터에만 초점을 맞출 수 있음 - 계산된 정보나 파생된 정보를 보여줄 수도 있음 - 사용자가 볼 수 있는 데이터 제한 가능
Sequence (시퀀스)	- 유일(UNIQUE)한 값을 생성해주는 오라클 객체 - 기본키와 같이 순차적으로 증가하는 컬럼을 자동으로 생성 가능 - 테이블과는 독립적으로 저장되고 생성됨
Index (인덱스)	- 검색 연산을 빠르게 수행하기 위해 데이터베이스 레코드의 정보를 구성하는 데이터 구조 - 인덱스 사용시 전체 데이터를 검색하지 않고 원하는 정보를 빠르게 검색 가능 - 인덱스는 인덱스를 생성한 칼럼 값으로 정렬되어 있으며, 테이블 내에 실제 값들이 저장된 위치를 갖고 있음 - 인덱스의 가장 중요한 기능은 접근 경로를 단축함으로써 데이터의 탐색 속도를 향상시키는 것
Synonym (동의어)	- 오라클 객체(테이블, 뷰, 시퀀스, 프로시저 등)에 대한 별칭(alias)을 의미 - 직접적인 객체가 아닌 객체에 대한 직접적인 참조 - 데이터베이스의 투명성을 제공하기 위해 사용(다른 유저의 객체 참조시 유용) - 객체의 긴 이름을 사용하기 쉬운 짧은 이름으로 SQL 코딩 단순화 가능 - 객체를 참조하는 사용자의 오브젝트를 감출 수 있어 보안에 유용

 

2. DML

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

• 데이터베이스에 데이터를 입력, 수정, 삭제, 조회하기 위해 사용
• 기본연산, 그룹연산, 조인연산으로 구분됨
  • DML 기본연산: INSERT, UPDATE, DELETE, SELECT, FROM, WHERE 등의 명령어로 구성되며, 입력, 수정, 삭제, 조회 연산에 사용
  • DML 그룹연산: AVE, SUM, COUNT 명령어를 활용하여 데이터들을 그룹으로 분류하여 그룹에 대한 항목별 통계 정보를 얻기 위해 사용
  • DML 조인연산: 하나의 SQL 명령문에 의해 여러 테이블에 저장된 데이터를 한번에 조회할 수 있는 기능

 

DML 기본 연산 구문

SQL문	내용
INSERT	데이터베이스 객체에 데이터를 입력
DELETE	데이터베이스 객체에 데이터를 삭제
UPDATE	기존에 존재하는 데이터베이스 객체 안의 데이터 수정
SELECT	데이터베이스 객체로부터 데이터를 조회/검색

 

3. DCL

데이터 제어어(DCL, Data Control Language)

• 데이터베이스를 공동으로 사용하기 위한 데이터 제어를 정의하고 기술하기 위해 사용
  * TCL(Transaction Control Language): 트랜젝션 제어어(COMMIT/ROLLBACK)를 별도 분리하기도 함

 

데이터 제어어 역할

구분	역할
데이터 보안 (Security)	불법적인 사용자로부터 데이터를 보호
무결성 (Integrity)	데이터의 정확성 유지
회복 (Recovery)	시스템 장애에 대비
병행수행제어 (Concurrency Control)	여러 사용자가 DB에 동시 접근 가능(사용)

 

주요 SQL문

SQL문	내용
GRANT	데이터베이스 객체에 권한을 부여
REVOKE	이미 부여된 데이터베이스객체의 권한을 취소
DENY	사용자의 객체에 대한 권한을 거부할 때 사용하며, GRANT와 DENY가 동시에 부여될 경우 DENY가 우선

 

4. TCL

트랜잭션(Transaction)의 정의

• 데이터베이스의 논리적 연산단위로 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 의미
• 트랜잭션은 의미적으로 분할할 수 없는 최소의 단위
• 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함됨
• DBMS는 트랜잭션을 근거로 데이터의 일관성을 보증 -> 전부 적용하거나 전부 취소(TRANSACTION은 ALL OR NOTHING)

 

트랜잭션의 ACID 특징

특징	기본개념
Atomicity (원자성)	- 연산 전체가 성공적으로 처리되거나 또는 한 가지라도 실패할 경우 전체가 취소되어 무결성을 보장(All or Nothing) - Commit/Rollback 연산 [관리: 트랜잭션관리자]
Consistency (일관성)	- 트랜잭션이 실행을 성공적으로 완료하면 언제나 모순 없이 일관성 있는 데이터베이스 상태를 보존함 [관리: 무결성제어기]
Isolation (고립성)	- 트랜잭션이 실행 중에 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근할 수 없음 [관리: 동시성(병행)제어관리자]
Durability (영속성)	- 성공이 완료된 트랜잭션의 결과는 영구(속)적으로 데이터베이스에 저장됨 [관리: 회복기법, 연관회복관리자]

 

트랜잭션 종료시 연산

• 트랜잭션 완료(Commit) 연산
  • 트랜잭션 안에서 수행한 모든 SQL 문장들의 결과를 데이터베이스에 영구적으로 반영하면서 해당 트랜잭션을 종료하는 연산
  • 명시적 commit: 사용자에 의해 완료 명령(Commit)이 수행되었을 때
  • 자동 commit: DDL, DCL 문장이 완료될 때, autocommit option이 On인 경우
• 트랜잭션 복귀(Rollback) 연산
  • 지금까지 수행해왔던 트랜잭션 안에서 변경된 모든 데이터 값을 트랜잭션 수행 이전 상태로 되돌리는 연산(즉, 트랜잭션을 철회(abort)하는 연산)
  • 명시적 Rollback: 사용자에 의해 반영 취소 명령(rollback)이 수행되었을 때
  • 자동 Rollback: 명시적 commit 트랜잭션의 수행 도중에 치명적인 오류가 있어 더 이상 진행할 수 없거나 (비정상적 종료, 시스템 장애 등)

 

트랜잭션을 제어하는 SQL

SQL문	내용
COMMIT	변경된 데이터를 데이터베이스에 영구적으로 반영
ROLLBACK	변경사항의 취소(변경 전 데이터로 복귀)
SAVEPOINT	데이터 변경을 사전에 지정한 저장점까지만 롤백

 

• 명시적 COMMIT/ROLLBACK의 사용 효과
  • 논리적으로 연관된 작업을 그룹핑하여 처리 가능
  • 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
  • 데이터 무결성 보장

 

• COMMIT
  • INSERT 문장, UPDATE 문장, DELETE 문장 사용 후에 일련의 변경 작업이 완료되었음을 데이터베이스에 알려주기 위해 사용
• COMMIT (트랜잭션 완료) 이후 데이터 상태
  • 데이터에 대한 변경 사항이 데이터베이스에 반영
  • 이전 데이터는 영원히 잃어버리게 됨(별도 로그 보관시 복구 가능)
  • 모든 사용자는 변경된 결과를 볼 수 있음
  • 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 됨
  • postgresql에서는 BEGIN; 으로 시작해야 트랜잭션이 생성됨

 

• ROLLBACK
  • 테이블 내 입력한 데이터, 수정한 데이터, 삭제한 데이터(DML문장)에 대하여 변경 사항을 취소할 때 사용
• ROLLBACK (트랜잭션 취소)이후 데이터 상태
  • 데이터에 대한 변경 사항은 취소
  • 이전 데이터는 다시 재저장 됨
  • 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 됨

 

• SAVEPOINT(저장점)
  • 트랜잭션에 포함된 전체 작업(스텝)을 롤백하는 것이 아니라 현 시점에서 미리 지정한 SAVEPOINT(저장점)까지 트랜잭션의 일부만 롤백
  • 복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효
  • SAVEPOINT savepoint_name; (저장이름)
    * commit을 수행하면 savepoint는 없어짐