11. 데이터 베이스 요약 - 패스트캠퍼스 백엔드 부트캠프 3기

Oracle 데이터 베이스 요약

기본 용어

RDB : 데이터를 테이블 형식으로 저장하고, 테이블 간 관계를 정의하여 관리하는 시스템
Table: 데이터의 저장 구조.
Primary Key: 고유 식별자, 유일하며 NULL 불가.
Foreign Key: 다른 테이블의 기본 키를 참조.
View: 실제 데이터를 저장하지 않는 가상 테이블 (읽기 전용).
Sequence: 자동으로 고유한 숫자 생성.
Index: 데이터 검색을 빠르게 해주는 객체 (소량/범위 검색, 정렬).
Synonym: 객체의 별명.
Program Unit: 작업을 수행하는 코드 단위.
SQL: 데이터베이스 명령어를 위한 언어.
Retrieve(쿼리): SELECT를 통한 데이터 검색.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

테이블 생성 및 데이터 조작

DDL (Data Definition Language) : 데이터베이스의 구조를 정의하고 수정하는 명령어들의 집합
CREATE: 테이블 생성
ALTER: 테이블 수정
DROP: 테이블 삭제
RENAME: 테이블/컬럼 이름 수정
TRUNCATE: 테이블 데이터 삭제 (구조는 유지)
COMMENT: 테이블/컬럼 주석 추가

DML (Data Manipulation Language) : 데이터베이스에 저장된 데이터를 조작하는 명령어들의 집합
SELECT: 데이터 조회
INSERT: 데이터 삽입
UPDATE: 데이터 수정
DELETE: 데이터 삭제

DCL (Data Control Language) : 데이터베이스 객체에 대한 권한을 부여하거나 제거하는 명령어들의 집합
GRANT: 권한 부여
REVOKE: 권한 제거

Transaction Control : 데이터베이스 트랜잭션의 시작과 종료를 제어하는 명령어들의 집합
COMMIT: 작업을 영구 저장
ROLLBACK: 작업 취소 (COMMIT 이전 상태로 복구)
SAVEPOINT: 특정 지점으로 되돌리기

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

데이터 검색

SELECT 명령어 : 데이터베이스에서 데이터를 검색하거나 조회하는 데 사용되는 명령어
DISTINCT: 중복 제거
ALIAS: 별명 부여
FROM: 테이블 명시
ORDER BY: 정렬
WHERE: 조건 필터링
BETWEEN: 범위 검색
IN: 여러 값 검색
LIKE: 패턴 검색
NOT NULL: 값이 존재하는 데이터 검색

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

SQL 함수

단일 행 함수 (Single Row Function) : 각 행에 대해 계산을 수행하고, 결과를 반환하는 함수
문자형 함수: 문자열을 변환하거나 조작하는 함수.
UPPER(대문자 변환), LOWER(소문자 변환), CONCAT(문자열 결합), SUBSTR(문자열 자르기), LENGTH(문자 길이 반환)

숫자형 함수: 숫자 데이터를 처리하는 함수.
ROUND(반올림), MOD(나머지 구하기), POWER(제곱), SQRT(제곱근), SIGN(양수/음수 구별), CHR(ASCII 문자 반환)

날짜형 함수: 날짜 데이터를 처리하는 함수.
SYSDATE(현재 시스템 날짜), LAST_DAY(월 마지막 날짜), MONTHS_BETWEEN(두 날짜 사이 개월수), ADD_MONTHS (몇 개월 후 날짜 계산)

변환형 함수: 데이터 타입을 변환하는 함수.
TO_CHAR(날짜/숫자 -> 문자열), TO_NUMBER(문자열 -> 숫자), TO_DATE(문자열 -> 날짜)

다중 행 함수 (Multi Row Function) : 여러 행에 대해 집계 연산을 수행하고, 결과를 반환하는 함수
GROUP 함수: 여러 행에 대해 집계 연산을 수행하는 함수.
COUNT(행 개수), AVG(평균), SUM(합계), MIN(최솟값), MAX(최댓값)

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

데이터 분류

SUBGROUP : 데이터를 그룹화하고, 그룹에 조건을 적용하여 데이터 집합을 세분화하는 작업
GROUP BY: 그룹화할 열 지정
HAVING: 그룹 조건 지정

JOIN : 두 개 이상의 테이블을 결합하여 관련 데이터를 조회하는 방식(이중 포문)
EQUIJOIN: 값이 일치하는 경우
NON-EQUIJOIN: 값이 일치하지 않는 경우
OUTER JOIN: 조건 미충족 시 NULL 반환
SELF JOIN: 같은 테이블 간의 조인

SET 연산자 : 여러 쿼리의 결과를 합치거나 교집합, 차집합 등을 구하는 연산자
UNION: 합집합 (중복 제거)
UNION ALL: 합집합 (중복 포함)
INTERSECT: 교집합
MINUS: 차집합

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

서브 쿼리

SUBQUERY: 쿼리 안에 포함된 또 다른 쿼리로, 조건을 동적으로 설정하거나 다른 테이블의 데이터를 기준으로 작업을 수행하는 데 사용
제약: 괄호로 묶여야 하며, ORDER BY 포함 불가, 연산자의 오른쪽에서 사용, 단일행 연산자(=, >, < 등) 또는 다중행 연산자(IN, NOT IN 등) 사용.

Single Row SubQuery: 단일 행 연산자 사용, 메인 쿼리로 전달되는 행이 하나일 때.
Multi Row SubQuery: 다중 행 연산자 사용, 메인 쿼리로 전달되는 행이 여러 개일 때.
Multi Column SubQuery: 여러 칼럼을 비교할 때 사용.
FROM의 SubQuery (INLINE VIEW): FROM 절에서 일부 행과 컬럼을 선택하여 검색.
HAVING의 SubQuery: 그룹화된 데이터를 서브쿼리로 필터링.
CREATE의 SubQuery: 기존 테이블을 이용해 새로운 테이블을 생성.
DML의 SubQuery: 데이터 조작 명령어에서 조건을 설정하거나 다른 테이블의 데이터를 기준으로 작업 수행.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

데이터 무결성

CONSTRAINT: 데이터 무결성을 유지하기 위해 사용자가 지정하는 제약조건으로, 모든 제약조건은 데이터 사전에 저장되며, 동일 사용자 내에서 중복될 수 없다

제약조건 정의 방법
컬럼 레벨: 각 컬럼에 대해 제약조건 정의 (모든 유형 정의 가능).
테이블 레벨: 여러 컬럼에 대해 제약조건을 정의, NOT NULL 제외.

제약조건 종류
NOT NULL: 컬럼에 NULL 값이 허용되지 않음 (컬럼 레벨에서만 정의).
UNIQUE: 컬럼의 값이 고유해야 함.
PRIMARY KEY: 컬럼 값이 각 행을 유일하게 구별.
FOREIGN KEY: 다른 테이블의 컬럼을 참조 (참조하는 컬럼은 PRIMARY KEY 또는 UNIQUE여야 함).
CHECK: 컬럼 값에 특정 조건을 적용.

데이터 조작 시 제약조건 적용
참조 무결성 오류: 자식 테이블에 부모 테이블에 없는 값을 입력하거나 수정할 때 발생.
기타 제약조건 오류: 제약조건에 위배되는 데이터 입력, 수정, 삭제 시 오류 발생.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

트랜잭션

트랜잭션이란? : 논리적인 업무의 작업단위,  여러 DML(데이터 조작 언어), DDL(데이터 정의 언어), DCL(데이터 제어 언어) 명령어들을 하나의 단위로 묶어 처리
명령어
SAVEPOINT: 트랜잭션 내에서 특정 지점을 지정하여, 나중에 그 지점으로 롤백할 수 있도록 합니다.
COMMIT: 트랜잭션 내의 변경사항을 영구적으로 데이터베이스에 반영하고 트랜잭션을 종료합니다.
ROLLBACK [TO SAVEPOINT명]: 특정 SAVEPOINT로 롤백하여, 그 시점까지의 변경사항을 취소합니다.
트랜잭션의 구성
DML 명령어: 데이터 변경을 일으키는 명령어 (예: INSERT, UPDATE, DELETE)
하나의 DDL 명령: 데이터 정의를 변경하는 명령어 (예: CREATE, ALTER, DROP)
하나의 DCL 명령: 데이터 제어 명령어 (예: GRANT, REVOKE)트랜잭션의 종료 : 최초의 실행가능한 SQL명령으로 시작해서 다음 사건 중 하나로 종료됨
1. COMMIT이나 ROLLBACK
2. DDL 이나 DCL 명령 실행(AUTOMATIC COMMIT)
3. DEADLOCK 등의 ERROR, EXIT, SYSTEM CRASH
COMMIT : 아직 저장되지 않은 모든 데이터 변경 사항을 DB에 저장하고 현재의 트랜잭션을 종료
SAVEPOINT 이름 : 현재의 트랜잭션에 SAVEPOINT를 지정
ROLLBACK [TO SAVEPOINT 이름] : 아직 저장되지 않은 모든 데이터 변경 사항을 취소 하고 현재 트랜잭션 종료
COMMIT 전과 후의 데이터 상태
COMMIT 전: 데이터 변경 불가, 다른 트랜잭션에서 변경 사항 미노출, LOCK 상태.
COMMIT 후: 데이터가 영구적으로 반영됨, 다른 트랜잭션에서 결과 반영, 트랜잭션 종료, LOCK 해제, 이전 데이터 영구 삭제.
ROLLBACK 후의 데이터 상태 : 데이터 변경 취소, 이전 데이터 재저장, LOCK 해제

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

DICTIONARY

DICTIONARY란? : DB구조와 관련된 메타데이터를 저장하는 시스템, ORACLE SERVER에 의해 생성 및 유지보수, 읽기 전용 뷰와 테이블의 집합, 데이터 사전 테이블은 SYS 사용자가 소유
DICTIONARY의 예 : 사용자명, 권한, DB 객체명 (테이블, 뷰, 인덱스 등), 테이블 제약조건, 감사정보.
DICTIONARY VIEW의 종류 : USER_, ALL_, DBA_, V$
USER_ : 사용자가 소유한 객체에 대한 정보
ALL_ : 사용자에게 허용된 객체에 관한 정보
DBA_ : DBA권한을 가진 사용자가 액세스 할 수 있는 정보를
V$ : 서버의 성능과 Locking에 관한 정보(일반적으로 DBA에게만 허용됨)
그외(동의어:SYNONYMS) : DICTIONARY, TABLE_PRIVILEGES, IND
DICTIONARY : 모든 데이터 사전 테이블, 뷰, 동의어
TABLE_PRIVILEGES : 사용자가 권한을 부여했거나(GRANTOR) 부여받은(GRANTEE) 객체에 대한 권한
IND : USER_INDEXES의 동의어(SYNONYMS)

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

데이터 정의어(DDL) - AUTO COMMIT

명령어의 종류 : CREATE, DROP, ALTER, TRUNCATE, RENAME, COMMENT
CREATE : 새로운 테이블 생성
DROP : 기존 테이블의 구조 및 모든 행 삭제, 종속된 제약조건 삭제
ALTER : 기존 테이블 변경, ADD(추가), DROP(삭제), MODIFY(수정), ENABLE(활성), DISABLE(비활성)
TRUNCATE : 모든 행 삭제(구조 삭제X)
RENAME : 이름 변경
COMMENT : 테이블, 컬럼에 주석 달기

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

데이터 제어어(DCL)

Privilege : 특정 작업에 대한 개별 권한을 의미
Role : 여러 권한을 묶어서 관리하는 역할을 의미, 사용자에게 허가할 수 있는 관련된 Privileges의 그룹

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

OBJECT

Object의 종류 및 기능
테이블(table) : 행과 열의 조합으로 구성된 기본 저장단위
시퀀스(sequence) : 자동으로 고유한 숫자값을 생성해주며 주로 기본키 값을 생성하기위해 사용, 중간에 GAP발생가능
인덱스(index) : 쿼리 검색 속도를 향상시키기 위해 사용
뷰(view) : 하나 이상의 테이블로부터 논리적으로 데이터를 추출한 가상 테이블로, 복잡한 쿼리를 단순화하고 보안성을 향상
시노님(synonym) : object에 대한 또다른 이름
프로그램 유닛(program unit) : 저장 프로시저, 함수, 패키지 등으로 구성되며, DB 내에서 반복적인 작업이나 복잡한 로직을 처리

시퀀스 사용법
NEXTVAL :  시퀀스의 다음 값을 반환하며, 호출할 때마다 고유한 값을 생성
CURRVAL : 현재 시퀀스 값을 반환, 반드시 NEXTVAL사용 후에 참조되어야 한다.

시퀀스 사용규칙
NEXTVAL과 CURRVAL을 사용할 수 있는 경우 : 서브쿼리가 아닌 SELECT문, INSERT문의 SELECT절, INSERT문의 VALUE절, UPDATE문의 SET절

인덱스의 구조 : 인덱스는 테이블의 각 행에 대응하는 주소(ROWID)와 인덱스 컬럼값으로 구성된다, B*TREE 구조
B*TREE의 특징 : 어떤 행에 대한 액세스 횟수도 동일하게 하는 이진 균형 탐색 구조
인덱스 생성 : 조건절이나 조인 조건에서 컬럼을 자주 이용할 때, 컬럼이 넓은 범위 값을 가질 때, 많은 NULL값을 갖는 컬럼일 때, 테이블의 데이터가 많고 그 테이블에서 조회되는 행의 수가 전체의 10-15%정도일 때
인덱스를 만들지 않아야 할때 : 테이블이 작을 때, 테이블이 자주 변경될 때

VIEW란? : 테이블이나 다른 뷰를 기초로 한 가상의 테이블
VIEW의 장점 : DB에 대한 엑세스 제한, 여러 테이블에 대한 데이터를 간단히 조회 가능
VIEW의 DML문 사용 규칙
간단한 뷰(테이블1개) 에서는 DML연산 수행가능
조인, 그룹 함수, GROUP BY절, DISTINCT 포함시 삭제 수정 추가 불가
식으로 정의된 컬럼을 포함할 경우 수정 불가
VIEW에 선택되지 않은 NOT NULL컬럼을 포함하는 경우 데이터 추가 불가
SYNONYM : 특정 OBJECT에 부여하는 또 다른 이름

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

ADVENCED(함수) SQL

NVL 함수 : NULL값을 지정된 값으로 변경, 모든 데이터 타입에 적용 가능
DECODE 함수 : 값을 비교하여 해당하는 값을 돌려주는 함수
CASE : 조건에 따라 다른 값을 반환하는 표현식
PIVOT 함수 : 행 데이터를 열 형식으로 변환, PIVOT테이블의 형태로 보여준다.(ORACLE 11G부터)
ROLLUP 함수 : 그룹별 합계를 보여주는 함수
CUBE 함수 : 그룹별 합계 & 소계를 보여주는 함수
RANK 함수 : 행별, 그룹별 순위를 계산해서 보여주는 함수
CORRELATED 쿼리(상관 서브쿼리) : 외부 쿼리의 각 행에 대해 내부 서브쿼리를 실행하여 값을 비교
Multi Row Comparison Operator : 서브쿼리에서 반환되는 여러 값과 비교
IN : LIST에 있는 값과 같은지 비교
NOT IN : LIST에 있는 값과 같지 않은지 비교
ANY : 서브쿼리에 의하여 RETURN되는 값 중 어느 하나와 비교
ALL : 서브쿼리에 의하여 RETURN되는 모든 값 비교
EXISTS : 서브쿼리에서 만족되는 값이 발견되면 TRUE가 되고 INNER QUERY에서의 SEARCH종료
NOT EXISTS : 서브쿼리에서 만족되는 값이 발견되지 않으면 TRUE가 되고 INNER QUERY에서의 SEARCH종료
PSEUDO COLUMN - ROWID, ROWNUM
ROWID : 테이블에 있는 각 행의 물리적 주소를 고유하게 지정
ROWNUM : 쿼리의 결과로 반환되는 각 행들에 대하여 테이블에서 선택된 순서로 ROWNUM이 지정됨, 쿼리에 의해서 RETURN되는 행의 수를 제한하는 경우에 사용가능

ROWID : 한 행 고유의 물리적 주소
오브젝트 번호(32BIT-6자리) : 해당 데이터가 속하는 오브젝트 번호
상대 파일 번호(10BIT-3자리) : 데이터가 저장된 DATAFILE의 번호
블록 번호(22BIT-6자리) : DATAFILE 안의 어느 블록인지를 의미
데이터 번호(16BIT-3자리) : 블록에서 데이터의 위치