[SQL] SQLD 공부 정리 중....

● DBMS (Database Management System)
= 효율적인 데이터 관리와 데이터 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 소프트웨어

● 테이블(Table: 테이블) - 행과 컬럼의 2차원 구조를 가진 데이터의 저장 장소.
- 데이터베이스의 가장 기본적인 개념
열(Column: 컬럼,세로 줄) - 2차원 구조를 가진 테이블에서 세로 방향으로 이루어진 하나하나의 특정 속성 (더 이상 나눌 수 없는 특성)
행(Row: 가로 줄)- 2차원 구조를 가진 테이블에서 가로 방향으로 이루어진 연결된 데이터
필드(Field: 값 하나) 


● 테이블을 분할하여 데이터의 불필요한 중복을 줄이는 프로세스 = 정규화 (Normalization)
- 데이터의 정합성 확보와 데이터 입력/수정/삭제 시 발생할 수 있는 이상현상(Anomaly)을 방지하기 위해 정규화는 관계형 데이터베이스 모델링에서 매우 중요한 프로세스이다.
기본키(Primary Key: PK) - 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 컬럼
외부키(Foreign Key: FK) - 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 컬럼

● CREATE TABLE: 테이블 생성
   CREATE TABLE 테이블이름 ( 
     칼럼명 1 DATATYPE [DEFAULT 형식]
   , 칼럼명 2 DATATYPE [DEFAULT 형식]
   , 칼럼명 2 DATATYPE [DEFAULT 형식] 
   ) ;
● CONSTRAINT: 제약조건
- PRIMARY KEY(기본 키)
: 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의. 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다. 기본키 제약 = 고유키(UNIQUE) 제약 & NOT NULL 제약
- UNIQUE KEY (고유키)
: 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의. NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있떠라도 고유키 제약 위반이 되지 않음
- NOT NULL
: NULL 값의 임력을 금지 (필수 입력 컬럼으로 만듦), CHECK 제약의 일부분으로 이해할 수도 있음
- CHECK
: 입력할 수 있는 값의 범위 등을 제한한다. 
- FOREIGN KEY (외래키)
: 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성됨. 외래키 지정시 '참조 무결성 제약 옵션'을 선택할 수 있음.
● 생성된 테이블 구조 확인
- Oracle: DESCRIBE 테이블명; or DESC 테이블명;
- SQL Server: sp_help 'dbo.테이블명'
● CTAS 기법: Create Table ~ As Select ~ 
(NOT NULL 제약 조건을 제외한 모든 제약 조건은 없어진다. 필요시 ALTER TABLE로 설정해야 함. (SQL Server: Select ~ Into ~, 컬럼 속성에 Identity를 사용했다면 같이 적용됨)

● TCL (Transaction Control Language)
- 트랜잭션(TRANSACTION)
: 데이터베이스의 논리적 연산 단위. 
: 하나의 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합.

- 트랜잭션의 4가지 특성
1. Atomiecity; 원자성 = 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다. (ALL or NOTHING)
2. Consistency; 일관성 = 트랜잭션이 실행되기 전의 DB내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 DB 내용에 잘못이 있으면 안 된다.
3. Isolation; 고립성 = 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
4. Durability; 지속성 = 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장된다.
(계좌이체는 트랜잭션의 적절한 예)
- 트랜잭션 3가지 명령어
: COMMIT, ROLLBACK, SAVEPOINT 

<정리>
해당 테이블에 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제(DELETE) 수행시 그 변경되는 데이터의 무결성을 보장하는 것이 커밋(COMMIT)과 롤백(ROLLBACK)의 목적이다. 
커밋(COMMIT)은 “변경된 데이터를 테이블이 영구적으로 반영해라”라는 의미를 갖는 것이고, 롤백(ROLLBACK)은 “변경된 데이터가 문제가 있으니 변경 전 데이터로 복귀하라”라는 의미이다. 저장점(SAVEPOINT/SAVE TRANSACTION)은 “데이터 변경을 사전에 지정한 저장점까지만 롤백하라”는 의미이다. 

Oracle 의 트랜잭션은 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, COMMIT 또는 ROLLBACK 을 실행한 시점에서 종료된다. 단, 다음의 경우에는 COMMIT 과 ROLLBACK 을 실행하지 않아도 자동으로 트랜잭션이 종료된다.
- CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점 에 자동으로 커밋된다. 
- 부연하면, DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다. 
- 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다. 
- 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.

SQL Server 의 트랜잭션은 DBMS 가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT 이 기본 방식이다. 다음의 경우는 Oracle 과 같이 자동으로 트랜잭션이 종료된다.
- 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.

 

● WHERE 조건 절
- SELECT [DISTINCT/ALL] 칼럼명 [ALIAS 명] FROM 테이블명 WHERE 조건식;
- WHERE 절은 FROM 절 다음에 위치하며, 조건식은 아래 내용으로 구성된다.
: 컬럼(Column)명-비교 연산자-문자,숫자,표현식-비교 컬럼명(JOIN 사용 시)

◎ 연산자 종류
- 비교 연산자: =, >, >=, <, <=
- 논리 연산자: AND, OR, NOT
- 부정 비교 연산자: !=, ^=, <>, NOT 칼럼명 =, NOT 칼럼명 >
- SQL 연산자: BETWEEN A AND B, IN (LIST), LIKE '비교 문자열', IS NULL
- 부정 SQL 연산자: NOT BETWEEN A AND B, NOT IN (LIST) IS NOT NULL

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

◎ ROWNUM, TOP절
- ROWNUM (Oracle)
: 컬럼과 비슷한 성격의 Pseudo Column, SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호.
: 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 갯수를 제한하는 목적으로 사용.
: 테이블 내의 고유한 키나 인덱스 값을 만들 수 있다.
- TOP 절 (SQL Server)
: 결과 집합으로 출력되는 행의 수를 제한할 수 있다.
: 표현식 TOP (Expression) [PERCENT] [WITH TIES]
* Expression: 반환할 행의 수를 지정하는 숫자.
* PERCENT: 쿼리 결과 집합에서 처음 Expression%의 행만 반환 됨을 나타냄
* WITH TIES: ORDER BY절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.
 

● 함수
- 특징: 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계

 

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

 

* 단일행 함수(Single-Row Function)
1. 문자형 함수 = 문자를 입력하면 문자나 숫자 값을 반환한다.
  (LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII)
2. 숫자형 함수 = 숫자를 입력하면 숫자 값을 반환한다.
  (ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN)
3. 날짜형 함수 = DATE 타입의 값을 연산한다.
  (SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d,'YYYY'|'MM'|'DD')) / YEAR|MONTH|DAY)
3. 변환형 함수 = 문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.
  (TO_NUMBER, TO_CHAR, TO_DATE / CAST, CONVERT)
4. NULL 관련 함수 \ NULL을 처리하기 위한 함수
  (NVL/ISNULL, NULLIF, COALESCE)
   → 조건에 맞는 데이터가 한 건도 없는 경우을 공집합이라 하며, NULL 데이터와는 다름.
   → COALESCE(ANSI 표준 함수): 인수의 숫자가 한정되어 있지 않으며, 인수가 NULL이 아닌 경우 해당 인수의 값을, 그렇지 않으면 그 다음 인수를 같은 방식으로 계속 조사하여 반환
5. CASE 표현
- IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다.
   → ORACLE의 DECODE 함수와 같은 기능을 함

* 다중행 함수(Multi-Row Function)
= 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수

1. 집계 함수(Aggregate Function)
- COUNT, SUM, AVG, MAX, MIN, STDDEV(표준 편차), VARIAN(분산) 등
- COUNT는 NULL 값이 아닌 칼럼의 건수만 출력.
- COUNT(*)는 NULL값을 포함한 행의 수를 출력!!!!

2. 그룹 함수(Group Function)
- 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 절 뒤에 위치한다.
-AVING절은 SELECT 절에 사용되지 않은 칼럼이나 집계 함수가 아니더라도 GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.
- SELECT 리스트에 있는 컬럼명이나 표현식 중 집계 함수를 제외하고는 모두 GROUP BY절에 명시해야 한다.

 

3. ORDER BY 절
- ORDER BY절은 SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬하여 출력하는데에 사용한다.
- ORDER BY절에 컬럼명 대신 SELECT 절에서 사용한 ALIAS명이나 컬럼 순서를 나타내는 정수도 사용 가능하다.
- 별도로 정렬 방식을 지정하지 않으면 기본적으로 오름차순(ASC)이 된다.
(ASC: Ascending, 오름차순 / DESC: Descending, 내림차순)
- SQL 문장의 제일 마지막에 위치한다.
- ORACLE은 NULL값을 가장 큰 값으로 취급하고, SQL Server는 반대

 

★ SELECT 문장 실행 순서 ★
⑤ SELECT 컬럼명 [ALIAS명]
① FROM 테이블명
② WHERE 조건식
③ GROUP BY 컬럼명 | 표현식
④ HAVING 그룹조건식
⑥ ORDER BY 컬럼이나 표현식;
============================
① 발췌 대상 테이블을 참조한다. (FROM)
② 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)
③ 행들을 소그룹화 한다. (GROUP BY)
④ 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)
⑤ 데이터 값을 출력/계산 한다. (SELECT)
⑥ 데이터를 정렬한다. (ORDER BY)
==> 옵티마이저가 SQL 문장의 SYNTAX, SEMANTIC에러를 점검하는 순서.


: 윈도우 함수(Window Function)

 

 

 

.... 정리중