계층형 질의
start with ~~ : 1부터 시작!
connect by NOCYCLE : 루트노드의 조건 (상하계층이 이루어지는 조건)
- nocycle:조직도가 뱅글뱅글 도는 경우. 이런 경우 런타임오류 발생하므로 nocycle키워드 입력해서 한번만 돌도록 명시해줘야 함.
같은 레벨에 있는 것들은 형제 노드(SIBLINGS)
리프 노드 : 가장 끝에 위치한 노드 (leaf node). 가장 끝에 위치하므로 자식노트 가지지 않음!
CONNECT BY PRIOR 자식컬럼 = 부모컬럼 -> 부모에서 자식으로 트리 그리기 : 순방향 전개
CONNNECT BY 자식컬럼 = PRIOR 부모컬럼 -> 자식에서 부모로 트리 그리기 : 역방향 전개
CONNCET BY PRIOR 부모컬럼 = 자식컬럼-> 자식에서 부모로 트리 그리기 : 역방향 전개
CONNECT BY 부모컬럼 = PRIOR 자식컬럼 -> 부모에서 자식으로 트리 그리기 : 순방향 전개
프자부, 부프자 : 순방향
SELECT COUNT(*)
FROM T
WHERE COL3<>2 // <>: 같지 않다 =? col3가 레벨2가 되면 안돼!
START WITH COL3=4 //col3=4인 행이 레벨1. 가장 상위노드!
CONNECT BY COL1 = PRIOR COL2 // PRIOR ~~ : 현재 읽은 행의 컬럼을 지정. 객체들을 사용할 수 있도록 권한을 부여하는 DCL 명령문? GRANT
DML : DATA MANIPULATION LANGUAGE 테이블 검색, 수정, 삽입, 삭제
INSERT INTO, UPDATE, DELETE, MERGE(INSERT+UPDATE)
DDL : DATA SET DEFINITION LANGUAGE 테이블 변경시키는거! 생성, 수정, 삭제. AUTO COMMIT
CREATE ALTER, RENAME, DROP, TURNCATE
DCL : DATA USER CONTROL LANGUAGE 사용자에게 권한 관리!
GRANT(권한주기), REVOKE(권한뺏기)
-
TURNCATE는 DDL! 테이블의 모든 행을 삭제, 디스크 사용량 초기화, 메타데이터 => 데이터의 틀만 유지하고자 하는 경우에 이용!
-
DELETE, TRUNCATE, DROP
delete : 용량 있음, DML, 행삭제
truncate : DDL, 삭제, 구조 삭제, 용량은 삭제 안됨
drop : 걍 다 삭제
트랜젝션
: 현실세계에서 어떤 행위가 일어날 때 이를 데이터베이스에 반영하기 위해 필수적 발생 데이터 베이스의 조작들.
트랜젝션은 분리할 수 없는 개념으로 트랜젝션을 분리하는 경우 작업의 의미, 논리가 무너짐. 하나의 분리될 수 없는 논리 작업을 구성하는 세부적인 연산 작업들의 모임.
TCL : TRANSACTION CONTROL LANGUAGE 트랜젝션 관리, 제어
COMMIT : 트랜젝션 완료, 계약완료. 수정불가
ROLLBACK : 최신 commit까지 복귀 또는 지정 savepoint까지 복귀
SAVEPOINT : 복귀 지점 지정.
- rollbakc 시행 시 가장 최근 commit 시점, 또는 DDL 근처로 돌아감.
NULL
- null은 부등호 관련 모두 안됨! 무조건 is null / is not null
count(*), count(1) ... -> Null값 포함한 전체 테이블의 행의 수 출력
count(컬럼명) -> Null제외 행의 수
count(distinct 컬럼명) -> 컬럼이 가지고 있는 고유의 종류의 수 계산. 결측치 제외!
- count 제외 distinct만 사용하는 경우 null 또한 하나의 종류로 취급됨.
함수에서 null값 있으면 배제하고 계산.
Null의 연산은 모두 Null
- SUM(column1, column2) => 오류!
NULLIF(회원번호, 주문번호) : 회원번호와 주문번호가 같다면 null값, 아니라면 회원번호.
NVL(A,B) : NULL VALUE A의 값이 null value야? 그럼 B출력하자. 인수는 반드시 두 개만! (oracle)
ISNULL(A,B) : A 값이 null value면 B출력하자. (sql server)
COALESCE(A,B,C...) : A, B, C... 순서대로 검사해서 Null값 아닌 최초의 값 출력.
SET-OPERATOR 이용한 위치기반결합, 중복치 제거
UNION ALL : 합집합인데 중복치 인정함.
UNION : 합집합
INTERSECT : 교집합
MINUS(oracle) EXCEPT(sql server) : 차집합 (=not in, not exists. 이거 쓰면 속도 좀 더 빨라짐)
SELECT *
FROM SA1
UNION
SELECT*
FROM SA2;
=> 문장오류 아님!! 변수명 기반 아니고 위치기반결합이니까. 변수명 달라도 상관없음 GROUP함수
ROLLUP : 하나씩 증가하는 형태
rollup(a,b) : 전체합계, 컬럼a소계, 컬럼b소계, 컬럼a, b조합 소계
CUBE : 가능한 모든 조합의 소계 및 합계를 생성하기 때문에 시스템에 무리 갈 수 있음.
cube(a,b) : 전체합계, 컬럼a소계, 컬럼b소계, 컬럼 a,b조합 소계
GROUPING SETS
SELECT 상품분류코드, AVG(상품가격) AS 상품가격,
COUNT(*) OVER(ORDER BY AVG(상품가격)
RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS 유사계수
// range: 범위 / preceding and following : 쁠마10000사이
FROM 상품
GROUP BY 상품분류코드 // group by절에 쓰이면 select절에 그대로 나올 수 있음. 서브쿼리 이론
: 다른 테이블에 있는 정보를 사용하거나 문법적인 제한 때문에 입력하지 못하는 정보를 입력하기 위해. GROUP BY를 제외한 모든 곳에 자유롭게 사용 가능.
단일행 서브쿼리 (single row) : 결과가 1건 이하. 비교연산자(=, >=, <=) 사용
다중행 서브쿼리 (multi row) : 결과가 여러건. IN, ALL, ANY, SOME, EXIST 연산자 사용해야함
IN : 서브쿼리에 존재하는 값들과 일치하는 값 찾아냄
ALL : 서브쿼리에 존재하는 모든 값들에 대한 만족하는 조건.
ANY : 서브쿼리에 존재하는 값 중 어느 하나라도 만족하는 조건.
EXIST : 서브쿼리의 결과를 만족하는 값이 존재하는 지 여부 확인하는 조건.
조건을 만족하는 행이 여러개여도 단 1건만 찾으면 검색 더 안함. 조건 만족하는 지 확인만!
- 다중행 서브쿼리 비교연산자는 단일행 서브쿼리에서 사용 가능.
- 단일행 서브쿼리 비교 연산자는 다중행 서브쿼리에서 사용 불가.
- EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용됨.
- 서브쿼리는 메인쿼리 컬럼 사용 가능, 메인쿼리는 서브쿼리 컬럼 사용 불가.
EXISTS
SELECT X
FROM T1
WHERE EXISTS(SELECT * FROM T1);
// => 서브쿼리 내부의 where절을 만족하는 어떤 임의의 개체가 존재하는가?
// 존재(EXISTS)하면 해당 개체는 where조건절을 만족시킬 수 있음. 인덱스 생성 구문 : CREATE INDEX 인덱스이름 ON 테이블이름(컬럼이름);
JOIN
left, right 에 붙이고 그 기준은 변형되면 안됨 (중복 허용)
INNER : 교집합
CROSS : 행별로! 행*행
SELECT 문장에서 PROJECTION 행위 할 수 있음.
WHERE에는 집계함수 사용 안됨.
우선순위
() -> 비교연산자, SQL연산자 (BETWEEN A AND B, IN (A, B, C), LIKE 형태, ISNULL 등등 -> NOT -> AND -> OR
- 다 or 나 => '둘 중에 하나' 도 되지만 '다' 랑 '나' 도 됨!
WINDOW함수
행과 행 사이의 연산을 하기 위해 행을 분할/정렬/대상 행 지정
PARTTITION BY : 분할 (=GROUP BY)
ORDER BY : 정렬 (=ORDER BY)
ROWS 또는 RANGE : 지정 (=WHERE)
순위함수 : RANK, DENSE_RANK, ROW_NUMBER
윈도우 집계함수 : SUM, MAX, MIN, AVG, COUNT
비율함수 : RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTITLE
UNBONDED PRECEDING : 한 없이 위쪽
FOLLOWING : 아래쪽
범위지정
RANGE
: 컬럼의 값을 기준으로 연산에 참여할 행을 선택
range 150 preceding : 현재 컬럼 값 기준으로 작은 값에서 150이하로 차이가 나는 행들을 선택적으로 계산.
range unbounded preceding : 현재 컬럼의 값(포함하여)을 기준으로 작은 값들을 모두 선택해서 계산.
range between 150 preceding and 150 following : 현재 컬럼의 값보다 작은 값에서 150이하로 차이나고 현재 컬럼 값 기준으로 큰 값에서 150 이상으로 차이가 나는 행들 선택.
range between unvounded preceding and current row : 현재 컬럼의 값보다 작은 값을 가지는 행부터 현재 행까지 모두 선택. (=range unbounded preceding)
RANK()
: 공동등수 수여, 다음 등수 제거. 연속적이지 않은 등수 나올 수 있음. 1 2 2 4 5 ...
DENSE_RANK()
: 사람이 엄청 많을 때. 중복 허락. 연속등수. 1 2 2 3 4 4 5 ..
ROW NUMBER()
: 동일한 등수가 존재하지 않음. 무조건 1부터 차례대로 연속하는 등수. 1 2. 4 ...
