[SQLD_summary] 2과목

h j·2023년 6월 8일

study

목록 보기

6/9

~~↑이거 왜때문 여기에...?~~

[제1장] SQL 기본

📖 SQL

📑 종류

📑 참조 동작

<DELETE / MODIFY>

<INSERT ACTION 할 때>

📑 TRUNCATE / DROP / DELETE 차이점

📑 조회 (SELECT)

SELECT
	[ALL/DISTINCT]
    조회할_칼럼명, 조회할_칼럼명, ...
FROM
	해당_칼럼들이_있는_테이블명;

ALL : Default 옵션 (별도 표시 필요없음), 중복된 데이터 모두 출력
DISTINCT : 중복된 데이터는 1건으로 처리해서 출력

📑 데이터 입력

// 테이블에 데이터 입력하기

(COLUMN_LIST)
VALUES
	(COLUMN_LIST에 넣을 VALUE_LIST);

INSERT INTO
	테이블명
VALUES
	(전체 칼럼에 넣을 VALUE_LIST);

📑 수정 (칼럼, 데이터, 테이블명)

// 테이블 칼럼에 대한 정의 변경

[ORACLE]
ALTER TABLE
	테이블명
MODIFY
	(칼럼명1 데이터유형 [DEFAULT 식] [NOT NULL],
    칼럼명2 데이터유형 [DEFAULT 식] [NOT NULL], 
    ...);
    
[SQL SERVER]
ALTER TABLE
	테이블명
ALTER
	(칼럼명1 데이터유형 [DEFAULT 식] [NOT NULL],
    칼럼명2 데이터유형 [DEFAULT 식] [NOT NULL], 
    ...);

// 입력된 데이터 수정

UPDATE
	테이블명
SET
	수정할_칼럼명 = 새로운_값 ;

// (ANSI 표준 기준) 테이블명 변경

RENAME
	기존_이름
TO
	새_이름;

📑 칼럼 삭제

ALTER TABLE
	테이블명
DROP COLUMN
	삭제할_칼럼명;

📑 제약조건의 종류

// PK 제약조건 생성 구문
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건이름] PRIMARY KEY (칼럼명)

ex.
ALTER TABLE TEST ADD CONSTRAINT pk_id PRIMARY KEY (id);

// 제약조건 삭제 구문
ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건이름];

// FK 제약조건 생성 구문 
CREATE TABLE [테이블명] (
	칼럼...,
    CONSTRAINT [제약조건이름] FOREIGN KEY (칼럼)
    REFERENCES [참조할_테이블] (참조할_컬럼)
    [ON DELETE CASCADE | ON DELETE SET NULL]
    );

📑 테이블 생성의 주의사항

1) 테이블명은 객체를 의미할 수 있는 적절한 이름 사용 (단수형 권고)
2) 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다
3) 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다
4) 테이블 이름을 지정하고 각 칼럼들은 괄호 “( )” 로 묶어 지정한다
5) 각 칼럼들은 콤마 “ , “로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 “ ; “으로 끝난다
6) 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다 (데이터 표준화 관점)
7) 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다
8) 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다
9) 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다
10) A-Z, a-z, 0-9, _, $, # 문자만 허용된다

📖 트랜잭션(TRANSACTION)

데이터베이스의 논리적 연산단위로서 밀접하게 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조각

📑 트랜잭션의 종료를 위한 대표적 명령어

COMMIT
: 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영
: ORACLE에서는 DDL 문장 수행 후 자동으로 COMMIT을 수행한다.
(SQL SERVER는 자동 수행 아님)
ROLLBACK
: 데이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 취소할 수있는데 데이터베이스에서는 롤백(ROLLBACK) 기능을 사용
: 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터를 변경 할 수 있게 된다.

📑 트랜잭션 시작 / 종료

BEGIN TRANSACTION (= BEGIN TRAN)으로 트랜잭션을 시작하고

COMMIT TRANSACTION (= COMMIT) 또는 ROLLBACK TRANSACTION (= ROLLBACK)으로 트랜잭션을 종료한다.

ROLLBACK을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

📑 저장점(SAVEPOINT)

롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

[ORACLE]
SAVEPOINT SVPT1;
...
ROLLBACK TO SVPT1;

[SQL SERVER]
SAVE TRANSACTION SVTR1;
...
ROLLBACK TRANSACTION SVTR1;

📑 트랜잭션 특징

고원일지

고립성 (Isolation)
: 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다. (영향 X)
원자성 (Atomicity)
: 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은
상태로 남아 있어야 한다. ( All or Nothing )
일관성 (Consistency)
: 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 실행 이후에도
데이터베이스의 내용에 잘못이 있으면 안된다.
지속성 (Durability)
: 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

📑 트랜잭션 격리성 낮은 경우 문제점

Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

📖 WHERE 절

FROM 절 다음에 위치
조건식의 구성
: 칼럼명 = 보통 조건식의 좌측에 위치
: 비교 연산자
: 문자, 숫자, 표현식 = 보통 조건식의 우측에 위치
: 비교 칼럼명 (JOIN 사용 시)

📖 NULL

📑 특성

NULL (ASCⅡ 코드 00번)은 0 (숫자) 또는 공백 (BLANK, ASCⅡ 코드 32번, 문자)과는 전혀 다른 값
조건에 맞는 데이터가 없을 때의 공집합과도 다르다
아직 정의되지 않은 미지의 값 (모르는 값) 또는 현재 데이터를 입력하지 못하는 경우 (값의 부재)를 의미
테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형을 NULL값을 포함 가능
정렬 시, ORACLE에서는 가장 큰 값, SQL SERVER에서는 가장 작은 값
결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용
※ NULL값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로,
문자 유형 데이터인 경우는 블랭크보다는 ‘X’ 같이 해당 시스템에서 의미 없는 문자로 바꾼다.
NULL 의 부정 = IS NOT NULL
INSERT ‘ ‘ 으로 입력할 때
[ORACLE] NULL로 취급
[SQL SERVER] 공백으로 취급

📑 NULL 연산

NULL 값과의 연산(+, -, *, / 등)은 NULL 값을 리턴
NULL 값과의 비교연산은 거짓(FALSE)을 리턴 (특정 값보다 크다/적다 라고 표현 할 수 없음)
집계함수(SUM, COUNT, MIN, MAX…)에서 NULL은 데이터 대상에서 제외 (무시함)

ex.

📖 연산자

📑 종류

📑 우선순위

괄호로 묶은 연산
부정 연산자 (NOT)
비교 연산자 (=, >, >=, <, <=)와 SQL 비교 연산자 (BETWEEN a AND b, IN (LIST), LIKE, IS NULL )
논리 연산자 중 AND, OR 순서로 처리

📖 함수

📑 단일행 함수 종류

📑 단일행 문자형 함수

📑 숫자 함수

ROUND(a,b) : 소수점 b번째 자리에서 반올림 (소수점 아래 순번 = 0,1,2,….)
ex.
Round(221.95, 1) ⇒ 소수점 둘째자리에서 반올림하여 첫째자리까지 출력
Round(321.12, 0) ⇒ 소수점 첫째자리에서 반올림하여 정수만 출력
Round(226.91, -1) ⇒ 1의 자리에서 반올림하여 정수를 출력
[ORACLE] CEIL | [SQL SERVER] CEILING : 올림
FLOOR : 버림

📑 단일행 NULL 관련 함수

📑 단일행 함수 특징

추출되는 각 행마다 작업을 수행
각 행마다 하나의 결과를 반환 ( ↔ 다중행 함수 : 여러 개의 행에 입력하고 하나의 값 반환 )
SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용 가능
데이터 타입 변경 가능
중첩 사용 가능

📑 다중행 집계 함수

※ ROWNUM : [Oracle] , where절에 사용, order by 보다 먼저 실행됨

※ TOP : [SQL Server] , select절에 사용
= SELECT TOP (n) 컬럼명 : 컬럼명에서 상위 n개 출력
= TOP (n) WITH TIES : 상위 3명 뽑는데 3등 2명이면 모두 뽑기위해 사용

📖 조건문 (Decode / Case when ~ then ~ end)

ex. daul이라는 테이블에서 col1이 A면 1, B면 2, 아니면 3을 출력하라

//
SELECT
	DECODE(col1, 'A', 1, 'B', 2, 3)
FROM
	dual;
    
//
case when col1 = 'A' then 1
	when col1 = 'B' then 2
    else 3 end;
    
//
case col1 when 'A' then 1
	when 'B' then 2
    else 3 end;

📖 GROUP BY / HAVING

SELECT [DISTINCT]
	칼럼명 [ALIAS명]
FROM 
	테이블명
[WHERE 조건식]
[GROUP BY
	칼럼이나 표현식]
[HAVING 
	그룹조건식];

GROUP BY절을 통해 소그룹 별 기준을 정한 후, SELECT절에 집계함수를 사용
집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행
GROUP BY절에서는 SELECT절과는 달리 ALIAS명(별칭) 사용 불가
집계 함수는 WHERE절에는 올 수 없음
(집계함수를 사용할 수 있는 GROUP BY절보다 WHERE절이 먼저 수행)
HAVING절은 GROUP BY절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시
GROUP BY절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING절에서 제한조건을 두어 조건을 만족하는 내용만 출력한다
HAVING절은 일반적으로 GROUP BY절 뒤에 위치
GROUP BY 절에 2가지 조건 불가 (중첩된 그룹함수의 경우 최종 결과값이 무조건 1건이기때문)

📖 ORDER BY

SELECT 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼/표현식]
[HAVING 그룹조건식]
[ORDER BY 칼럼/표현식] [ASC/DESC];

기본적인 정렬 순서는 오름차순(ASC), 생략가능
숫자형 데이터 타입은 오름차순 정렬 시 가장 작은값부터 출력
날짜형 데이터 타입은 오름차순 정렬 시 빠른 날짜부터 먼저 출력
ASC : 조회한 데이터를 오름차순으로 정렬 / DESC : 조회한 데이터를 내림차순으로 정렬

📖 SELECT 문장 실행 순서

FROM : 발췌 대상 테이블을 참조한다
WHERE : 발췌 대항 데이터가 아닌 것을 제거한다
GROUP BY : 행들을 소그룹화 한다
HAVING : 그룹핑된 값의 조건에 맞는 것만을 출력한다
SELECT : 데이터 값을 출력/계산한다
ORDER BY : 데이터를 정렬한다

[제2장] SQL 활용

📖 JOIN

📑 개념

두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것
일반적인 경우, 행들은 PK 나 FK 값의 연관에 의해 성립된다
PK, FK의 관계가 없어도 논리적인 값들의 연관만으로도 성립이 가능하다
EQUI JOIN은 테이블 간의 컬럼값들이 정확하게 일치해야 하며, ‘=’ 연산자에 의해서만 수행된다. (그 외의 비교 연산자를 사용하는 경우에는 모두 NON EQUI JOIN)

📑 EQUI JOIN

두 테이블의 컬럼을 Equal 연산자(=)로 연결

// EQUI JOIN 문장 : WHERE절에 JOIN 조건
SELECT
	테이블1.칼럼명, 테이블2.칼럼명,...
FROM
	테이블1, 테이블2
WHERE
	테이블1.칼럼명1 = 테이블2.칼럼명2;

// (ANSI/ISO SQL 표준) EQUI JOIN 문장 : ON절에 JOIN 조건
SELECT
	테이블1.칼럼명, 테이블2.칼럼명, ...
FROM
	테이블1
INNER JOIN 테이블2
ON
	테이블1.칼럼명1 = 테이블2. 칼럼명2;

📑 ANSI/ISO SQL에서 표시하는 JOIN 형태

INNER JOIN : 교집합 / JOIN 조건에서 동일한 값이 있는 행만 반환 (↔ OUTER JOIN)
NATURAL JOIN : 칼럼명 같은 거 다 조인함 (무조건 동일한 이름과 데이터값의 컬럼이 존재해야함)
USING 조건절
: FROM절 안에 사용
: NATURAL JOIN과 달리 같은 이름을 가진 컬럼들 중, 원하는 컬럼에 대해서만 선택적으로 EQUI JOIN이 가능
※ 기준컬럼은 맨 앞에 출력, ALIAS 사용 불가, 기준컬럼 중복제거, USING 조건에 참여하지 않은 컬럼은 중복출력
ON 조건절
: 컬럼명이 다르더라도 JOIN 조건을 사용
: 접두사를 사용하여 SELECT에 사용되는 컬럼을 논리적으로 명확하게 지정해야함
CROSS JOIN
: 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합
: 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생
OUTER JOIN (LEFT, RIGHT, FULL)
: Oracle에서는 (+) 기호를 사용해 처리할 수 있음
⇒ (+)가 붙은쪽 X, 반대쪽을 기준으로 outer join
: ANSI문장으로 변경 / INNER쪽 테이블에 조건절을 ON절에 함께 위치시켜야 함

// [LEFT/RIGHT/FULL] OUTER JOIN
SELECT
	X.KEY1, Y.KEY2
FROM
	TAB1 X
[LEFT/RIGHT/FULL] OUTER JOIN TAB2 Y
ON
	(X.KEY1 = Y.KEY2);

셀프조인 (Self Join)
: 동일 테이블 사이의 조인 / FROM절에 동일 테이블이 두번 이상 나타남
: 칼럼이름이 모두 동일하기 때문에 반드시 테이블 별칭(ALIAS)를 사용해야한다
※ ALIAS : select절에서 사용 가능, where절에 사용 불가

// SELF JOIN
SELECT
	ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...
FROM
	테이블 ALIAS명1, 테이블 ALIAS명2
WHERE
	ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

📑 JOIN 수행 원리 (대충 특징만)

Hash join : 등가 join만 사용, 선행테이블이 작다, 별도 공간 필요
NL join : 랜덤 엑세스, 대용량 sort 작업, 선행테이블 작을수록 유리
Sort merge : join키 중심으로 정렬, 등가/비등가

📖 연산자

📑 순수 관계 연산자

종류 : SELECT, PROJECT, JOIN, DIVIDE 셀프조디
순수 관계 연산자와 SQL 문장 비교
: SELECT 연산은 WHERE절로 구현
: PROJECT 연산은 SELECT절로 구현
: (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현
: DIVIDE 연산은 현재 사용되지 않음

📑 일반 집합 연산자

SELECT 칼럼명1, 칼럼명2, ...
FROM 테이블명1
WHERE 조건식
GROUP BY 칼럼/표현식
HAVING 그룹조건식
집합연산자
[SELECT 절]
ORDER BY ~ [ASC/DESC];

※ ORDER BY는 집합 연산을 적용한 최종 결과에 대한 정렬
⇒ 가장 마지막 줄에 한 번만 기술

(종류)

UNION : 여러 개의 SQL문의 결과에 대한 합집합, 모든 중복된 행은 하나의 행으로(중복 X)
UNION ALL : 여러 개의 SQL문의 결과에 대한 합집합, 중복된 행도 그대로 결과로 표시 (중복 허용)
INTERSECT : 여러 개의 SQL문의 결과에 대한 교집합, 중복된 행은 하나의 행으로(중복 X)
EXCEPT (Minus) : 앞의 SQL문의 결과에서 뒤에 SQL문의 결과에 대한 차집합, 중복된 행은 하나의 행으로(중복 X)

📑 일반 집합 연산자를 SQL과 비교

UNION 연산은 UNION 기능으로 구현
INTERSECTION 연산은 INTERSECT 기능으로 구현 (교집합)
DIFFERENCE 연산은 EXCEPT(Oracle:MINUS) 기능으로 구현 (제외)
PRODUCT 연산은 CROSS JOIN 기능으로 구현 (M*N)

※ JOIN은 옆으로, 집합연산자는 아래로(행) 합치는 것

📖 계층형 질의

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 사용
계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 의미
ex. 사원 테이블에서 관리자(상위)-사원(하위)
PRIOR
: CONNECT BY절에 주로 사용되며, 현재 읽은 칼럼을 지정
(SELECT, WHERE절에서도 사용가능)
※ CONNECT BY 절의 결과에는 LEVEL 이라는 컬럼이 있으며, 이는 계층의 깊이를 의미

: [PRIOR 자식=부모]형태를 사용하면 계층구조에서
부모데이터→자식데이터 방향으로 전개(순방향)

: [PRIOR 부모=자식]형태를 사용하면
자식데이터→부모데이터 방향으로 전개(역방향)
Oracle 계층형 질의
: START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문.
즉, 루트 데이터를 지정(액세스)
: ORDER SIBLINGS BY = 형제 노드(동일 LEVEL) 사이에서 정렬을 수행
※ 루트 노드의 LEVEL 값은 1이다

📖 서브쿼리

📑 반환되는 데이터의 형태에 따라 분류

📑 사용위치에 따른 명칭

SELECT절 / 스칼라 서브쿼리 : 결과는 하나의 행
FROM절 / 인라인 뷰
: 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용 가능
: SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰, DB에 해당 정보가 저장되지 않음
WHERE절 / 중첩 서브쿼리 : 서브쿼리 결과에 따라 단일행, 복수행이라고 함

📑 서브쿼리 사용 시 주의사항

서브쿼리를 괄호로 감싸서 사용
서브쿼리는 단일행 또는 복수행 비교 연산자와 함께 사용가능
(단일행-서브쿼리 결과가 반드시 1건 이하)
서브쿼리에서는 ORDER BY 사용불가
(메인쿼리의 마지막 문장에 위치해야함 ∵SELECT절에 오직 1개만 존재가능)

📑 뷰 (View)

정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행
특징
: 독립성 = 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다
: 편리성 = 복잡한 질의를 단순하게 작성 가능, 해당 형태의 SQL문을 자주 사용할 때 용이
: 보안성 = 숨기고 싶은 정보(칼럼)을 제외하고 뷰를 생성함으로써 사용자에게 정보를 감출 수 있다

📖 그룹 함수

📑 ROLLUP

그룹 함수로 집계된 데이터에서 소계, 합계를 쉽게 구하기 위해서 사용
GROUP BY ROLLUP(그룹컬럼) = GROUP BY 그룹컬럼 WITH ROLLUP
ROLLUP(a,b) : a,b별 합계 (a,b) / a별 합계 (a,null) / 전체 합계 (null,null)
(단, a와 b 인자 순서가 변하면 결과도 달라짐)

📑 GROUPING SETS

다양한 소계 집합을 만들 수 있음
ROLLUP과 비슷. 다른점: 인자의 순서가 바뀌어도 결과는 동일함
정렬 할 시, OORDER BY절에 명시적으로 정렬 칼럼이 표시되어야 함

📑 GROUPING

결과가 NULL일때 1, NULL이 아닐때 0의 값을 반환

📑 CUBE

결합 가능한 모든 값에 대하여 다차원 집계를 생성 (모든 경우의 수)
결과에 대한 정렬이 필요한 경우는 ORDER BY절에 명시적으로 정렬 칼럼이 표시가 되어야 함
Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야하는 경우에 사용
ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의
인자 순서는 결과에 영향을 주지 않음

📖 윈도우 함수

📑 특징

Partition과 Group By 구문은 의미적으로 유사
Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일
윈도우 함수 처리 후 결과 건수는 변함없음
윈도우 함수 적용 범위는 Partition을 넘을 수 없음

📑 순위 함수 (RANK)

ORDER BY를 포함한 쿼리문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수
동일한 값에 대해서는 동일한 순위를 부여 (1등, 1등, 3등, ..)
DENSE_RANK 함수는 동일한 값에 동일한 순위 부여, 중간 순위 건너뛰기 없음 (1등, 1등, 2등, ..)
ROW_NUMBER 함수는 동일한 값이라도 고유한 순위를 부여

📑 행 순서 함수

FIRST_VALUE
: FIRST_VALUE 칼럼명 OVER ~
: 파티션별 윈도우에서 가장 먼저 나온 값
: SQL Server 에서는 지원하지 않는 함수
: MIN을 활용했을 때와 같은 결과
: 공동 등수 인정 X
※ RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정
LAST_VALUE
: LAST_VALUE 칼럼명 OVER ~
: 파티션별 윈도우에서 가장 나중에 나온 값
: SQL Server 에서도 지원
※ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정
LAG
: 파티션별 윈도우에서 이전 몇 번째 행의 값, 현재 읽혀진 데이터의 이전 값을 알아내는 함수
: LAG(인수1,인수2,인수3)
: 인수1 = 입력 칼럼
인수2 = 몇 번째 앞의 행을 가져올지 (DEFAULT 1)
인수3 = 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값
LEAD
: 파티션별 윈도우에서 이후 몇 번째 행의 값, 이후 값을 알아내는 함수
: LEAD(인수1,인수2,인수3)
: 인수1 = 입력 칼럼
인수2 = 몇 번째 후 의 행을 가져올지 (DEFAULT 1)
인수3 = 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값
ex. LEAD(E,A)’는 E에서 A번째 행의 값을 호출하는 형태로도 쓰인다
[ORACLE] NTILE
: 행 데이터를 그룹별로 나누어 차례대로 행 번호를 부여하는 분석 함수
: SELECT NTILE[그룹으로나눌정수] OVER (PARTITION BY [컬럼1] ORDER BY [컬럼2]);
: partition by를 생략하면 전체 행에 대해서 그룹화가 수행됨

📑 그룹 내 비율 함수

RATIO_TO_REPORT : 칼럼 값에 대한 백분률
PERCENT_RANK : 행의 순서에 대한 (0~1사이 값) 백분률
CUME_DIST : 1/(파티션) 전체 건수로 표현하는 백분률

📑 윈도우 함수와 특징

PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹 으로 나눌 수 있다.
UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우 에서 윈도우가 시작
UNBOUNDED FOLLOWING : PARTITION의 마지막 로우 에서 윈도우가 시작
CURRENT ROW : 윈도우의 시작이나 끝 위치가 현재 로우
ROWS : 물리적 인 ROW 단위로 행 집합을 지정 (현재행을 기준으로 몇개의 행을 포함하는지)
RANGE : 논리적 인 상대번지로 행 집합을 지정 (현재행을 기준으로 어떤 값의 범위를 포함하는지)

📖 PL/SQL

PL/SQL은 Block구조로 되어있어 각 기능별로 모듈화가 가능
변수, 상수 등을 선언하여 sql 문장 간 값을 교환
IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함
DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능
PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램 이동 가능
PL/SQL은 응용 프로그램의 성능을 향상시킴
PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량 줄이기 가능
Procedure, User Defined Function, Trigger 객체를 PL/SQL로 작성 가능
Procedure 내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고
일반적인 SQL 문장은 SQL실행기가 처리
PL/SQL로 작성된 Procedure, User Defined Function은 작성자의 기준으로 트랜잭션 분할 가능
동적 SQL / DDL 문장을 실행 할 때 EXECUTE IMMEDIATE를 사용해야 함

📖 저장 모듈 (Stored Module)

SQL 문장을 DB서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램 ( SQL 명령문의 집합)
독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램
Oracle의 저장 모듈
: Procedure, User Defined Function, Trigger

📑 트리거 (Trigger)

DB에 의해 자동으로 호출되고 수행됨
특정 테이블에 대해서 INSERT, UPDATE, DELETE문이 수행되었을 때 호출되도록 정의 가능
DB에 로그인 하는 작업에도 정의 가능

📑 프로시저와 트리거 비교

h j

... . _._. ._. . _

이전 포스트

[SQLD_summary] 1과목

다음 포스트

[SQLD_summary] 2과목

study

[제1장] SQL 기본

📖 SQL

📑 종류

📑 참조 동작

📑 TRUNCATE / DROP / DELETE 차이점

📑 조회 (SELECT)

📑 데이터 입력

📑 수정 (칼럼, 데이터, 테이블명)

📑 칼럼 삭제

📑 제약조건의 종류

📑 테이블 생성의 주의사항

📖 트랜잭션(TRANSACTION)

📑 트랜잭션의 종료를 위한 대표적 명령어

📑 트랜잭션 시작 / 종료

📑 저장점(SAVEPOINT)

📑 트랜잭션 특징

📑 트랜잭션 격리성 낮은 경우 문제점

📖 WHERE 절

📖 NULL

📑 특성

📑 NULL 연산

📖 연산자

📑 종류

📑 우선순위

📖 함수

📑 단일행 함수 종류

📑 단일행 문자형 함수

📑 숫자 함수

📑 단일행 NULL 관련 함수

📑 단일행 함수 특징

📑 다중행 집계 함수

📖 조건문 (Decode / Case when ~ then ~ end)

📖 GROUP BY / HAVING

📖 ORDER BY

📖 SELECT 문장 실행 순서

[제2장] SQL 활용

📖 JOIN

📑 개념

📑 EQUI JOIN

📑 ANSI/ISO SQL에서 표시하는 JOIN 형태

📑 JOIN 수행 원리 (대충 특징만)

📖 연산자

📑 순수 관계 연산자

📑 일반 집합 연산자

📑 일반 집합 연산자를 SQL과 비교

📖 계층형 질의

📖 서브쿼리

📑 반환되는 데이터의 형태에 따라 분류

📑 사용위치에 따른 명칭

📑 서브쿼리 사용 시 주의사항

📑 뷰 (View)

📖 그룹 함수

📑 ROLLUP

📑 GROUPING SETS

📑 GROUPING

📑 CUBE

📖 윈도우 함수

📑 특징

📑 순위 함수 (RANK)

📑 행 순서 함수

📑 그룹 내 비율 함수

📑 윈도우 함수와 특징

📖 PL/SQL

📖 저장 모듈 (Stored Module)

📑 트리거 (Trigger)

📑 프로시저와 트리거 비교

[SQLD_summary] 1과목

SOAP, REST, REST API, RESTful API

0개의 댓글

관련 채용 정보