3. 관계형 데이터베이스

데이터베이스

• 넓은 의미에서의 데이터베이스는 일상적인 정보들을 모아 놓은 것 자체를 의미한다.
• 일반적으로 데이터베이스라고 말할 때는 특정 기업이나 조직 또는 개인이 필요에 의해(ex: 부가가치가 발생하는) 데이터를 일정한 형태로 저장해 놓은 것을 의미한다.
• 사용자들은 보다 효율적인 데이터의 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 소프트웨어를 필요로 하게 되었고 이러한 기본적인 요구사항을 만족시켜주는 시스템을 DBMS(Database Management System)라고 한다.

관계형 데이터 베이스

• 관계형 데이터베이스는 정규화를 통한 합리적인 테이블 모델링을 통해 이상(ANOMALY) 현상을 제거하고 데이터 중복을 피할 수 있으며, 동시성 관리, 병행 제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능을 제공
• 관계형 데이터베이스는 메타 데이터를 총괄 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법 등을 체계화할 수 있고, 데이터 표준화를 통한 데이터 품질을 확보할 수 있는 장점을 가지고 있음
• DBMS 는 인증된 사용자만이 참조할 수 있도록 보안 기능을 제공하고 있다. 테이블 생성 시에 사용할 수 있는 다양한 제약조건을 이용하여 사용자가 실수로 조건에 위배되는 데이터를 입력 한다든지, 관계를 연결하는 중요 데이터를 삭제하는 것을 방지하여 데이터 무결성(Integrity)을 보장
• DBMS 는 시스템의 갑작스런 장애로부터 사용자가 입력, 수정, 삭제하던 데이터가 제대로 반영될 수 있도록 보장해주는 기능과, 시스템 다운, 재해 등의 상황에서도 데이터를 회복/복구할 수 있는 기능을 제공

종류	명령어
데이터 조작어(DML : Data Manipulation Language)	SELECT, INSERT, UPDATE, DELETE
데이터 정의어(DDL : Data Definition Language)	CREATE, ALTER, DROP, RENAME
데이터 제어어(DCL : Data Control Language)	GRANT, REVOKE
트랜잭션 제어어(TCL : Transaction Control Language)	COMMIT, ROLLBACK

DDL 데이터 정의어

• 데이터 유형은 데이터베이스의 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준
• 선언한 유형이 아닌 다른 종류의 데이터가 들어오려고 하면 데이터베이스는 에러를 발생시킴
• 주의사항
  ◼ 테이블 명은 단수형 권고
  ◼ 테이블 명은 다른 테이블과 중복되면 안됨
  ◼ 한 테이블내에서 컬럼 명이 중복되면 안됨
  ◼ 테이블 생성 문 끝은 ‘;’로 끝나야 함
  ◼ 데이터 유형은 반드시 지정 해야함
  ◼ 테이블 명과 칼럼 명은 반드시 문자로 시작해야함
  ◼ A-Z, a-z, 0-9, _, $, # 문자만 허용

제약 조건

• 제약조건(CONSTRAINT)이란 사용자가 원하는 조건의 데이터만 유지하기 생성함
• 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약

타입	설명
기본 키(Primary Key)	테이블에 저장된 행을 고유하게 식별하기 위함 하나에 테이블에 단 하나의 기본 키만 정의 가능 기본 키 생성 시 DBMS 는 자동으로 UNIQUE 인덱스를 생성 기본 키 컬럼에는 NULL 입력 불가
고유 키(Unique Key)	테이블에 저장된 행 데이터를 고유하게 식별하기 위해 생성 NULL 은 입력 가능
NOT NULL	NULL 값의 입력을 금지 필수적으로 값이 들어가야하는 칼럼이 됨
CHECK	입력할 수 있는 값 종류 및 범위를 제한한다.
외래 키(Foreign Key)	다른 테이블의 기본 키를 외래 키로 지정하는 경우 생성함(참조무결성제약조건)

DDL 데이터 정의어 종류

• CREATE
• ALTER
• RENAME
• TRUNCATE
• DROP

DML 데이터 조작어

• INSERT
• UPDATE
• DELETE
• SELECT
• SELECT DISTINCT : 칼럼 값 기준 중복을 제거한 유일한 값 만을 출력함
  ◼ SELECT DISTINCT A.ISSUE_INSTI_NM FROM SQLD.TB_CERTI A;
  ◼ SELECT * : 모든 컬럼이 조회됨
• ALIAS 지정 : AS 를 이용하여 컬럼의 이름을 지정할 수 있다.

합성 연산자를 이용한 문자열 연결

SELECT
 A.CERTI_NM || '(' || A.CERTI_CD || ')' || '-' || A.ISSUE_INSTI_NM AS CERTI_INFO 
FROM SQLD.TB_CERTI A;

“ || “ 연산자를 이용하여 문자열을 연결 할 수 있다.

TCL

• 트랜잭션은 데이터베이스의 논리적 연산단위이다.
• 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다. 트랜잭션은 분할할 수
  없는 최소의 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소한다. 즉, TRANSACTION 은 ALL OR NOTHING 의 개념

특성	설명
원자성	트랜잭션에서 정의된 연산들은 모두 성공적으로 끝나거나 모두 실패해야 한다.
일관성	트랜잭션이 실행되기 전의 데이터베이스의 내용이 잘못되어 있지 않다면 실행된 이후에서 데이터베이스의 내용에 잘못이 있으면 안된다.
고립성	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다

COMMIT

• 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고
  판단되었을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료할 수 있다.

• COMMIT 이나 ROLLBACK 이전의 데이터 상태
  ◼ 단지 메모리 BUFFER 에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구
  가능하다.
  ◼ 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
  ◼ 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
  ◼ 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.
  
  

• COMMIT 이후의 상태
  ◼ 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
  ◼ 이전 데이터는 영원히 잃어버리게 된다.
  ◼ 모든 사용자는 결과를 볼 수 있다.
  ◼ 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게
  된다.

ROLLBACK

• 테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는
  변경 사항을 취소할 수 있는데 데이터베이스에서는 롤백(ROLLBACK) 기능을 사용한다.
• 롤백(ROLLBACK)은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된
  행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.

SAVE POINT

• 저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을
  롤백 하는 것이 아니라 현 시점에서 SAVEPOINT 까지 트랜잭션의 일부만 롤백 할 수
  있다.
• 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT 까지의 트랜잭션만
  롤백하고 실패한 부분에 대해서만 다시 실행

COMMIT 및 ROLLBACK 처리와는 상관없이 트랜잭션 처리가 일어나는 상황

• CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후
  시점에 자동으로 커밋 된다.
• DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋 된다.
• 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋 된다.
• 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이
  자동으로 롤백 된다.
  
  

WHERE 조건절

• 사용자들은 자신이 원하는 자료만을 검색하기 위해서 SQL 문장에 WHERE 절을 이용하여
  자료들에 대하여 제한할 수 있다. WHERE 절은 FROM 절 다음에 위치하며, 조건식은
  아래 내용으로 구성된다
• 연산자의 우선순위

우선순위	설명
1	() 괄호
2	NOT 연산자
3	비교연산자, SQL 비교연산자
4	AND
5	OR

BETWEEN, IN , LIK, IS NULL 의 사용

• BETWEEN : A 와 B 사이의 값
• IN : 리스트에 있는 값 중 하나라도 있으면 된다.
• LIKE : 비교문자열의 형태와 일치하면 된다.
  ◼ 와일드카드
  - ◆ % : 0 개 이상의 어떤 문자를 의미한다.
  - ◆ ㅡ : 1 개인 단일 문자를 의미한다.
• IS NULL : 값이 NULL 이면 된다.

함수

• SELECT, WHERE, ORDER BY 절에 사용 가능하다.
• 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴 한다.
• 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴 한다.
• 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다.
• 특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능하다.

단일 행 함수의 종류

종류	설명
문자 형 함수	문자를 입력하면 문자나 숫자값을 반환한다. (LOWER, UPPER, SUBSTR, LENGTH, LTRIM, RTRIM, TRIM, ASCII)
숫자 형 함수	숫자를 입력하면 숫자값을 반환한다. (ABS, MOD, ROUND, TRUNC, SIGN, CHR, CEIL, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN)
날짜 형 함수	DATE 타입의 값을 연산한다. (SYSDATE, EXTRACT, TO_NUMBER)
변환 형 함수	문자, 숫자, 날짜형의 값의 데이터 타입을 변환한다. (TO_NUMBER, TO_CHAR, TO_DATE, CONVERT )
NULL 관련 함수	NULL 을 처리하기 위한 함수 (NVL, NULLIF, COALESE)

GROUP BY, HAVING 절

집계 함수

• 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.
• GROUP BY 절은 행들을 소 그룹화 한다.
• SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.

항목	설명
COUNT(*)	NULL 값을 포함한 행의 수를 출력
COUNT(표현식)	표현식의 값이 NULL 아닌 행의 수를 출력
SUM(표현식)	표현식이 NULL 값인 것을 제외한 합계를 출력
AVG(표현식)	표현식이 NULL 값인 것을 제외한 평균을 출력
MAX(표현식)	표현식이 NULL 값인 것을 제외한 최대값을 출력
MIN(표현식)	표현식이 NULL 값인 것을 제외한 최소값을 출력
STDDEV(표현식)	표현식이 NULL 값인 것을 제외한 표준편차를 출력
VARIAN(표현식)	표현식이 NULL 값인 것을 제외한 분산을 출력

GROUP BY 절

• GROUP BY 절을 통해 소그룹 별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다)
• WHERE 절은 전체 데이터를 GROUP 으로 나누기 전에 행들을 미리 제거시킨다.
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
• GROUP BY 절에 의한 소 그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.
  
  

HAVING 절

• WHERE 절에서는 집계 함수를 쓸 수 없다.
• 집계된 결과 집합을 기준으로 특정 조건을 주고 싶은 경우 HAVING 절을 이용하면 된다.
• HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있다.
  
  

ORDER BY 정렬

• ORDER BY 절은 SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용
• ORDER BY 절에 칼럼(Column)명 대신에 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능
• 별도로 정렬 방식을 지정하지 않으면 기본적으로 오름차순이 적용되며, SQL 문장의 제일
  마지막에 위치
• 숫자 형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력, 날짜 형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력
• NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에, 내림차순으로 정렬했을 경우에는 가장 먼저 위치

• ORDER BY – 널 포함 정렬
  ◼ 오라클은 널 값이 가장 크다고 인식한다
  ◼ 내림차순의 결과 널인 행이 가장 위에 위치한다.
• ORDER BY – SELECT 절에 존재하지 않는 컬럼으로 정렬
  ◼ SELECT 절에 기재하지 않은 칼럼을 기준으로 ORDER BY 해도 정상적으로 실행됨
  ◼ SELECT 절에 CERTI_NM 칼럼은 존재하지 않음
  ◼ ORDER BY 절에 CERTI_NM 칼럼으로 ORDER BY 함 정상 수행됨
• ORDER BY – HAVING 절의 결과를 정렬
  
  

조인

• 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것을 조인이라고 하며
  일반적으로 사용 되는 SQL 문의 상당수가 조인으로 이루어져 있다.
• 일반적인 경우 PRIMARY KEY 와 FOREIGN KEY 의 값 연관에 의해 조인이 이루어지며 PK,
  FK 관계와는 별도로 일반 칼럼 끼리 조인이 이루어지는 경우도 있다