기본 SQL 작성

1. 데이터 정의어(DDL)

1. 데이터 정의어(DDL; Data Definition Language)의 개념

• 데이터 정의어는 데이터를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들을 데이터 정의어라고 부른다.

2. DDL의 대상

• 도메인(Domain) : 속성의 데이터 타입과 크기, 제약조건 등의 정보
• 스키마(Schema) : DBMS 특성과 구현을 감안한 데이터 구조
• 테이블(Table) : 데이터 저장 공간
• 뷰(View) : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
• 인덱스(Index) : 검색을 빠르게 하기 위한 데이터 구조

3. DDL 명령어

• crate(생성), ALTER(수정), DROP(데이터 오브젝트 삭제), TRUNCATE(데이터베이스 오브젝트 내용삭제)

4. 제약조건 적용

• 테이블 생성 시 부적절한 자료가 입력되는 것을 방지하기 위해 사용된다.
• 제약조건은 종속성이 존재할 경우 테이블 삭제 등을 방지한다.
  CF: 종속성(Dependency) : 데이터 베이스의 릴레이션(Relation)에서 두 개의 애트리뷰트(Attribute) 집합 간 제약의 일종이다.
  릴레이션 R에서, 애트리뷰트 X의 값 각각에 대해 애트리뷰트 Y의 값이 하나만 연관되는 관계를 Y는 X에 함수 종속이라고 하고, X -> Y로 표현
• 테이블의 행이 삽입, 갱신, 삭제될 때마다 테이블에서 규칙을 적용한다.

1. PRIMARY KEY

• 테이블의 기본 키를 정의
• 유일하게 테이블의 각 행을 식별
• CF: PRIMARY KEY(기본키) : 테이블의 각 튜플들을 고유하게 식별하는 키이다.

2. FOREIGN KEY

• 외래 키를 정의
• 참조 대상을 테이블(컬럼 명)으로 표소
• 열과 참조된 테이블의 열 사이의 외래 키 관계를 적용하고 설정
  CF : FOREIGN KEY : : 한 릴레이션의 컬럼이 다른 릴레이션의 기본 키로 이용되는 키이다.

3. UNIQUE

• 테이블 내에서 얻은 유일한 값을 가져야함
• 테이블 내에서 동일한 값을 포함하지 않음을 지정

4. check

• 개발자가 정의하는 제약조건
• 참(TRUE)이어야 하는 조건을 지정

테이블 생성을 위한 CREATE문에 제약 조건을 명시하는 형태로 사용되며, ALTER를 통해 테이블 제약조건의 변경이 가능하다.

2. 관계형 데이터 모델

1. 관계형 데이터 모델(Relation Data Model 개념)

• 관계형 데이터 모델은 보편적인 데이터 모델로, 계층 모델과 망 모델의 복잡한 구조를 단순화시킨 모델이다.
• 관계 모델의 대표적 언어로는 SQL이 있다.
• 데이터 간의 관계를 기본 키와 이를 참조하는 외래 키로 표현한다.
• 테이블 간 관계를 1:1, 1:N, M:N 관계로 목적에 맞게 표현한다.

2. 관계형 데이터 모델의 표현

• 고객 테이블, 주문 테이블, 주문서 테이블을 관계 데이터 모델로 표현한다.
• 고객 테이블의 Primary Key는 번호이고, 주먼서 테이블의 Primary Key는 예약 번호이다.
• 고객 테이블과 주문 테이블은 1:N 관계에 있고, 주문 테이블과 주문서 테이블은 1:1 관계에 있다.

3. 트랜잭션

1. 트랜잭션(Transaction)의 개념

• 인가 받지 않은 사용자로부터 데이트럴 보장하기 위해 DBMS가 가져야 하는 특성이자, 데이터 베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위이다.

2. 트랜잭션의 특징

1. 원자성(Atomicity)

• 분해가 불가능한 작업의 최소 단위
• 연산 전체가 성공 또는 실패(All or Nothing)
• 하나라도 실패할 경우 전체가 취소되어야 함
• 주요 기법 : Commit/Rollback, 회복성 보장

2. 일관성(Consistency)

• 트랜 잭션이 실행 성공 후 항상 일관된 데이터베이스 상태 보존해야 함
• 주요기법 : 무결성 제약조건, 동시성 제어

3. 격리성(Isolation)

• 트랜잭션 실행 중 생성하는 연산ㄴ의 중간 결과를 다른 트랜잭션이 접근 불가
• 주요 기법 : Read Uncommitted , Read Commit, Phantom Read, Serializable

4. 영속성(Durability)

• 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터 베이스에 저장
• 주요기법: 회복 기법

cf. 데이터 베이스의 고립화 수준(Isolation Level)

1. Read Uncommitted

• 한 트랜잭션에서 연산(갱신) 중인(아직 커밋되지 않은) 데이터를 다른 트랜잭션이 읽는 것을 허용, 연산(갱신) 중인 데이터에 대한 연산은 불허

2. Read Committed

• 한 트랜잭션에서 연산(갱신)을 수행 할 때, 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한, 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용 한다.

3. Repeatable Read

• 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신/삭제를 제한한다.

4. Serializable Read

• 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제한이다.

3. 트랜잭션의 Commit, Rollback 연산

• 커밋(Commit)이란 하나의 트랜잭션이 성공적으로 끝나고, 데이터베이스가 일관성 있는 상태에 있거나 하나의 트랜잭션이 끝났을 때 사용하느느 연산이ㅏㄷ.
• 롤백(Rollback)이란 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산이다.

4. 트랜잭션의 상태 변화

1. 활동 상태(Active) : 초기 상태, 트랜잭션이 실행 중일 때 가지는 상태

2. 부분완료 상태(Partially Committed) : 마지막 명령문이 실행된 후에 가지는 상태

3. 완료 상태(Committed) : 트랜잭션이 성공적으로 완료된 후 가지는 상태

4. 실패 상태(Falied) : 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태

5. 철회 상태(Aborted) : 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

5. 트랜잭션 제어

• 트랜잭션 제어 언어는 TCL(Transaction Control Language)라고 하며, 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어를 지칭

1. 커밋(Commit)

• 트랜잭션 확정
• 트랜잭션을 메모리에 영구적으로 저장

2. 롤백(ROLLBACK)

• 트랜잭션 취소
• 트랜잭션 내역을 저장 무효화시킴

3. 체크포인트(CHECKPOINT)

• 저장 시기 설정
• ROLLBACK을 위한 시점을 지정

4. 테이블

1. TABLE의 개념

• 각 데이터들을 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체를 테이블이라고 한다.
• 하나의 DB 내에 여러 개의 테이블들로 구성될 수 있고, 릴레이션(Relation)혹은 엔터티(Entity)라고도 불린다.

2. 테이블 구성 조건

• 테이블에 포함된 행들은 유일해야하고 중복된 행이 존재하지 않아야 한다.
• 테이블에 포함된 행들 간에는 순서가 존재하지 않는다.
• 테이블을 구성하는 열들 간에는 순서가 존재하지 않는다.

3. 테이블의 구조

• 컬럼<테이블<데이터베이스

4. 테이블의 용어

• 튜플(Tuple)/행(Row) : 테이블 내의 행을 의미하여 레코드라고도 함, 튜플은 릴레이션에서 같은 값을 가질 수 없음
• 애트리뷰트(Attribute)/열(Column) : 테이블 내의 열을 의미, 열의 개수를 Degree라고 함
• 식별자(Identifier) : 여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적인 개념
• 카디널리티(Cardinality) : 튜플(Tuple)의 개수
• 치수(Degree) : Attribute의 개수

5. 데이터 사전

1. 데이터 사전(Data Dictionary) 개념

• 데이터 사전에는 데이터베이스의 데이터를 제외한 모든 정보가 존재한다.
• 데이터 사전의 내용을 변경하느 권한은 시스템이 가지며, 사용자에게는 읽기 전용 테이블 형태로 제공되므로 단순 조회만 가능하다.
• 데이터 사전은 데이터의 테이블 의미하는 메타데이터(Metadata)로 구성된다.

2. 데이터 사전 내용

• 데이터 사전 내 존재하는 메타데이터의 유형은 다음과 같다(DBMS 공통)
• 사용자 정보(아이디, 패스워드, 성별 등)
• 데이터베이스 객체 정보(테이블, 뷰, 인덱스 등)
• 무결성 제약 정보(Constraints)
• 함수, 프로시저 및 트리거

3. 데이터 사전 목적

• 사용자에게 데이터 사전은 단순 조회의 대상일 뿐이다.
• 데이터베이스 엔진을 이루는 컴파일러, 옵티마이저 등과 같은 구성요소에 데이터 사전은 작업을 수행하는데 필요한 참조 정보이다.
  cf : 컴파일러(Compiler) : 특정 프로그래밍 언어로 쓰여진 소스 코드를 다른 프로그래밍 언어(목적 코드)로 바꿔주는 번역 프로그램이다.
  cf : 옵티마이저(Optimizer) : 사용자가 질의한 SQL문을 처리 할 수 있는 실행 계획을 탐색하고 각 실행 계획에 대한 비용을 추정하여 최적의 실행계획을 수립하는 DBMS의 핵심 엔진이다.

4. 데이터 사전 검색

1. Oracle 데이터 사전 검색

• Oracle 사용자는 뷰로 데이터 사전에 접근할 수 있다.
• 오라클에서 데이터 사전과 관련된 뷰는 DBA,ALL,USER 세 가지 영역이 있다.
  (우선순위 : DBA>ALL_>USER)
• 오라클(Oracle)에서는 영역 지시자(DBA,ALL,USER) 뒤에 오브젝트 명을 붙이는 형태로 뷰으이 이름이 결정된다.
• 오브젝트(Object) : 테이블, 뷰, 인덱스와 같은것을 의미한다.
• DBA_ : 데이터베이스의 모든 객체 조회가능(DBA는 시스템 접근 권한)
• ALL_ : 자신의 계정으로 접근 가능한 객체와 타계정의 접근 권한을 가진 모든 객체 조회 가능
• USER_ : 현재 자신의 계정이 소유한 객체 조회가능

2. MySQL 데이터 사전 검샘

• 데이터 사전은 테이블 형태로 구성되어 있다.
• MySQL에서 데이터 사전은 information_schema라는 데이터베이스 안에 존재한다.
• 테이블을 조회하기 위해서는, 우선 해당 데이터베이스로 이동해서 테이블 목록을 요청해야 한다.
• 테이블 목록으로 데이터 사전을 구성하는 테이블 이름을 확인하고, SELECT문을 통해 해당 테이블의 내용을 조회할 수 있다.