[산대특] 데이터베이스, Oracle, SQL, SQLplus

0930

 

  데이터베이스(Database)

  데이터베이스를 사용하는 이유는 원시 데이터인 '자료(Raw Material)'와 그 외 컴퓨팅(계산능력) 및 비즈니스 인텔리전스 툴을 기반으로 수집해서 저장한 '데이터 저장소(Data Warehouse)'를 충분히 활용해서 '가공 작업(Data Mining)' 통해서 유익한 정보를 추출해서 비즈니스를 더 효율적으로 운영하고, 더 나은 의사결정을 내리며, 더 민첩하고 확장 가능한 기업으로의 발전을 도모하기 위해서이다.

 

  오늘날 운용되는 가장 공통적인 데이터베이스(database) 내의 데이터는 프로세싱과 데이터 쿼리 작업을 더 효율적으로 실행하기 위해서 일반적으로 일련의 테이블(table) 안에 행(row)과 열(column)로 모델링된다.

행과 열이 만나는 곳에 값(field)이 들어 있다.

하나의 행에 여러 열 값이 들어 있는 것을 레코드(record)라고 한다.

이런 구조로 원하는 데이터를 더 쉽게 액세스, 관리, 수정, 업데이트, 제어, 그리고 체계화할 수 있다.

대부분의 데이터베이스는 데이터 작성 및 쿼리 작업에 '구조화 질의 언어'(SQL: Structured Query Language)를 사용.

하나하나의 필드 값은 의미가 없고, 이것들이 Record 가 되어야 의미가 있다. 

데이터는 Record 가 중요 

 

  구조화 질의 언어(SQL)

거의 모든 관계형 데이터베이스(Relative DataBase)에서 사용

프로그래밍에 특화된 SQL을 PL(Programming Language)/SQL이라고 한다.

 

▪ 관계형 데이터베이스 

 관계형 데이터베이스 기술은 구조화된 정보에 액세스할 수 있는 가장 효율적이고 유연한 방식을 제공한다. 

 

▪ 객체 지향형 데이터베이스 

  객체 지향형 데이터베이스 내 정보는 객체 형태로 표현되며 이를 객체 지향형 프로그래밍이라 부른다.

 

▪ 분산 데이터베이스 <- Hadoop 

  분산 데이터베이스는 서로 다른 곳에 존재하는 두 개 이상의 파일로 이루어져 있다.

 

▪ 데이터 웨어하우스 

  데이터 중앙 보관소인 데이터 웨어하우스는 신속한 쿼리 및 분석을 위해 특별히 설계된 데이터베이스 유형 중 하나.

 

▪ NoSQL 데이터베이스 

  비정형 데이터와 반정형 데이터가 저장 및 조작될 수 있는 데이터베이스이다. 

 

▪ 그래프 데이터베이스 

  그래프 데이터베이스는 개체, 그리고 개체 간 관계 측면에서 데이터를 저장. 

(그래프 데이터베이스 => 수요가 별로 없다)

 

▪ OLTP(OnLine Transactional Processing)과 OLAP(OnLine Analytical Processing) 데이터베이스

 OLTP 데이터베이스는 빠른 속도의 분석용 데이터베이스로서,

다수의 사용자에 의해 수행되는 대량의 트랜잭션을 위해서 설계. 분석을 위주로 하는 OLAP도 있다. 

일단의 데이터처리 데이터 묶음. Like 코드블럭 == 트랜잭션 

 

▪ 오픈 소스 데이터베이스 

  오픈 소스 데이터베이스 시스템은

소스 코드가 오픈 소스인 데이터베이스로 SQL 또는 NoSQL 데이터베이스가 포함될 수 있다.

 

▪ 클라우드 데이터베이스 

  프라이빗, 퍼블릭 또는 하이브리드 클라우드 컴퓨팅 플랫폼에 존재하는 정형/비정형 데이터의 집합

모델 유형에는 전통 방식의 데이터베이스와 서비스형 데이터베이스(DBaaS:Database as a Service)가 있다. 

 

▪ 멀티모델 데이터베이스

  멀티모델 데이터베이스는 서로 다른 유형의 데이터베이스 모델을 단일 통합 백엔드에 결합

다양한 데이터 유형을 수용할 수 있다.

 

▪ 문서/JSON 데이터베이스 

  문서 기반 정보를 저장하고 검색하며 관리하기 위한 목적,

JSON 형식으로 데이터를 저장하는 최신 방식으로서 MongoDB가 대표적.

JSON의 데이터를 <key:value> 포맷으로 저장. 

 

▪ 자율구동 데이터베이스

 클라우드 기반 데이터베이스로써 AI와 M/L을 사용해 관리자가 수행하던

일상적인 관리 업무를 자동으로 처리하게 한 데이터베이스이다.

 

 

  테이블의 구조

  테이블 구조를 알기 위해서는 Schema를 알아야 하는데 스키마는 논리적인 데이터 구조나 스키마 객체의 모음이다.

스키마는 데이터베이스 사용자가 지정하면 해당 사용자와 같은 이름으로 저장된다.

각 사용자는 하나의 스키마를 가지고 있고, 스키마 객체는 SQL로 생성되고 조작된다.  

  테이블은 데이터베이스에 저장되는데 이 공간을 Tablespace로 부른다. 각 디스크에 데이터베이스가 저장되고, 데이터베이스 안에 각각의 스키마로 지정된 테이블들이 저장되게 되는데 하나의 테이블의 데이터가 한 곳에 저장되는 것이 아니라 여러 곳에 분산되어 저장된다!! 이는 문제가 있을 때 오류를 복구하기 위해서 이다. 

 

=>현재 대부분의 데이터는 '관계형데이터베이스'에 존재하고 '빅데이터' 분석은 특정 데이터베이스를 사용하지 않지만 빅데이터 분석의 전처리(pre-processing)을 위해서 데이터를 사전에 가공할 때에는 'SQL'을 통해서 미리 다듬어 둘 필요가 있다. 

 

  SQL 명령어의 종류

  다음처럼 SQL을 기능적인 면에서 분류하기도 한다.

a. DDL(Data Definition Language : 데이터 정의어)

  데이터베이스 관리자나 응용 프로그래머가 데이터베이스의 논리적 구조를 정의하기 위한 언어로서 이런 정보는 데이터 사전(Data Dictionary)에 저장 된다.

create : 테이블 생성	alter : 테이블 구조 변경	drop : DB / 테이블 삭제
rename : 테이블 이름 변경	truncate : 테이블의 모든 내용 제거(테이블 남김)

 

b. DML(Data Manipulation Language : 데이터 조작어)

  데이터베이스에 저장된 데이터를 조작하기 위해 사용하는 언어로서 데이터 검색, 추가, 삭제, 갱신 작업을 수행한다. 

insert : 데이터 삽입	delete : 데이터 삭제	update : 데이터 수적
select : 테이블에 저장된 데이터 조회

대부분 SQL 문에는 이 select가 들어 있어야 한다.

 

c. DCL(Data Control Language : 데이터 제어어)

  데이터에 대한 접근 권한 부여 등의 데이터베이스 시스템의 트랜잭션(TCL)을 관리하기 위한 목적으로 사용된다. 

grant : 사용자에게 특정 권한 부여 (~to)

revoke : 사용자로부터 특정 권한 제거 (~from)

 

d. TCL(Transaction Control Language : 트랜잭션 제어어)

  DML(조작)에 의해 변경 된 내용을 완성 및 저장이나 취소한다.

commit(OR /) : 변경된 내용을 영구 저장	rollback : 변경되기 전 상태로 돌림
savepoint : 특정 위치까지 영구 저장  or 이전 상태로 돌릴 수 있도록 트랜잭션 중 복원을 위해 임시 저장 지점을 만듦(redo, undo)

=>SQL 쿼리를 실행하면 결과가 임시로 메모리 상에 저장되기 때문에 일단의 작업 완료나 취소가 가능하다.

 

 

  Oracle

  오라클은 Oracle 사에서 만든 RDBMS 소프트웨어인데 현재 오픈소스인 MySQL도 소유.

  관계형 데이터베이스(RDBMS)라 하는 것은 하나의 많은 요소들이 들어 있는 원본 테이블을 목적에 맞게 여러 개의 테이블로 분할해서 각각의 테이블로 만들어 두고 이들 테이블의 한 컬럼_명을 원본 테이블의 한 컬럼_명과 관계(Relation)를 만들어서(PK, FK, ...) 두 테이블이 서로 연결되게 한 기법이다. 테이블간에 관계가 있어서 관계형 데이터베이스라고 부른다. 이런 관계가 있어서 나중에 JOIN이 가능한 것이다.

  각 테이블에는 not null, unique 조건을 갖춘 열이 P.K가 되는데 P.K는 여러 개 있을 수도 있다. 그리고 F.K는 여러 개가 있을 수 있다.

=>표시는 P.K는 P(주키)로 표시되고, F.K는 F(외래키)가 아니라 R(관계)로 표시된다.

 

  Oracle에서의 관리자적 작업

∎ ADDM(Automatic Database Diagnostic Monitor)은 각 AWR 스냅샷 생성 후마다 실행되는데 인스턴스의 병목지점(CPU, Oracle Net, Lock, I/O, Memory, Overloaded SQL, Overloaded PL/SQL 등)을 점검해서 처리해주는 도구이다.

∎ AWR(Automatic Workload Repository)은 ADDM을 수행하게 하기 위해서 구성 요소의 문제 감지와 자체 튜닝을 위한 통계 수집, 유지, 관리 등을 제공하는 서비스이다.

∎ SQL Access Advisor는 스키마를 튜닝하고 쿼리의 성능을 향상시키는 도구인데 주어진 작업에 대한 성능 향상의 권장사항을 제공한다.

∎ Optimizer Statistics는 데이터베이스와 데이터베이스 객체에 대한 자세한 설명 데이터의 모음이다. Query Optimizer는 이런 통계를 이용해서 최적화된 SQL 문의 실행 계획을 진단한다.

∎ SQL Tuning Advisor는 ATO(Automatic Tuning Optimizer)를 사용해서 통계분석, SQL 프로파일링, 액세스 경로 분석, SQL 구조 분석을 통한 분석을 수행한다.

 

  Oracle에서의 관리자 계정에는 

▪ SYS : 데이터베이스 관리 작업을 수행하는 일반적인 관리자. Windows의 Administrator나 Linux의 root 권한과 유사 

▪ SYSDBA : 데이터베이스의 (최초의)구조, 설계 등을 관리하는 관리자 

▪ SYSTEM : 데이터베이스를 위한 순수 데이터베이스 관리자로서 Oracle에서는 SYSTEM 계정으로 작업하는 것보다 개별 데이터베이스 관리자 계정을 생성해서 백업, 복원 등 데이터베이스 작업을 수행하는 것을 권장한다. 

 

  그리고 데이터베이스 아래에는 여러 테이블들이 저장되어 있는데 이 공간을 테이블스페이스라고 부른다. 중요한 것은 하나의 테이블이 하나의 파일로 저장되는 것이 아니라 하나의 테이블에 있는 정보가 테이블스페이스의 각 공간에 적절히 Oracle이 알아서 저장해두고 Data Dictionary에 그런 정보를 가지고 있어서 사용자는 이들 정보를 볼 수는 있지만 조작할 수는 없다.

 

  제약(Constraints)조건에는 U(Unique), C(Check), R(Reference:Foreign key), P(Primary key)가 있어서 이들 항목의 데이터를 입력하거나 삭제하는 등의 작업을 함부로 수행할 수 없다. 

하나의 테이블에는 대부분 하나(나 하나 이상)의 PK:Unique+Not NULL가 존재하지만 하나의 테이블은 또 여러 테이블들과 관계(Relation)를 맺고 있기 때문에 여러 FK가 존재할 수 있다. 이 FK는 해당 테이블의 PK가 되어서 이 테이블과 관계를 맺고 있기 때문에 어느 한 테이블에서 함부로 해당 열을 삭제하거나 변경할 수 없는 제약(constraints)이 있다. 이렇게 테이블 간에 항목(열)별로 관계가 있기 때문에 관계형 데이터베이스로 부른다.  

 

왜 제약 조건이 필요한가? 

만약 저기 MANAGER 꼴 보기 싫어 삭제하려 해도 삭제가 안된다. 다른 테이블과 관련/지칭되어 있기 때문에. 그래서 제약 조건이 중요하다 => 제약 조건을 통해 함부로 변경할 수 없는 것인지, 지워도 되는 것인지(ex. U 붙은거) 확인이 가능해서.

 

  SQL(Structured Query Language)

  SQL은 사용자가 데이터베이스와 대화할 수 있는 언어이다. 

 

  중복된 데이터를 하나씩만 뽑아서 본다면 distinct 

 

  like, not like

 

  Escape 문자 \

 

  지금껏 알아본 연산자를 정리하면 

=, ==, !=, <, >, ^=, >=, <=, AND, OR, BETWEEN a AND b, IN(a, b), LIKE ~, NOT LIKE ~, IS NULL, IS NOT NULL 등이 있다.