서브 쿼리 - 쿼리안에 있는 또 다른 쿼리
스칼라 서브쿼리 (Scalar Subaquery) - (주로 SELECT절에 사용) 한 행, 한 칼럼만을 반환하는 서브쿼리, 하나의 칼럼처럼 사용 됨
SELECT ...,
(SELECT COUNT(*) FROM 부양가족 Y WHERE Y.사원번호 = B.사원번호) AS 부양가족수
//칼럼을 다른 테이블에서 가져올 때 등등 사용
FROM ...,
뭔지 감 못잡다가 정미나님 영상으로 이해됨
뷰 - DB의 SELECT 문을 저장한 OBJECT, 쿼리문에서 테이블처럼 쓰임
테이블은 실제 데이터로 가지고 있는 반면 뷰는 실제 데이터를 가지고 있지 않다. 가상 테이블이라고도 함
//아래의 인라인 뷰의 테이블과 같이 임의로 만든 가상 테이블
-> 뷰 사용의 장점
- 독립성 - 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
- 편리성 - 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL 문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.
- 보안성 - 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.
인라인 뷰(INLINE VIEW) - 뷰 형태로서 테이블을 리턴하는 서브쿼리, 테이블명이 올 수 있는 곳에 사용 (일회성 뷰)
FROM 내 쿼리 또는 JOIN 할 테이블을 리턴할 때 사용하는 서브쿼리
FROM ...,
(SELECT *
FROM 사원
WHERE 입사년도 = '2014') B
WHERE ...
스칼라 서브 쿼리와 마찬가지로 다른 테이블에서 데이터를 가져오거나, 새로운 테이블을 만들어 가져올 때 사용 한다.
연관 서브쿼리(Correlated Subquery) - 서브쿼리 내에 메인 칼럼이 사용된 서브쿼리, WHERE 절에 사용 됨
중첩 서브쿼리 - WHERE 절에 사용
NVL - ORACLE 에서의 NULL 판별
NVL(B.가입일자, '-')
//B.가입일자의 데이터가 NULL인 경우 '-' 로 출력, NULL이 아니면 그대로 표시
CASE 문 - 조건에 따라 값을 지정해줌
CASE 컬럼
WHEN 조건1 THEN 값1
WHEN 조건2 THEN 값2
ELSE 값3
END
조건 1을 충족시 값1을 조건 2를 충족시 값2를 충족못할시 값3을 출력
//조건값에 1이 들어갈 수 있으나 0이 들어가면 안된다.
GROUPING - 소계, 합계로 집계된 행의 컬럼 NULL을 구분할 수 있다.
NULL인경우 1을 반환하고 아닌경우 0을 반환
CASE GROUPING(A.서비스ID) = 0 THEN A.서비스ID
ELSE '합계' END AS 서비스ID
//A.서비스ID의 칼럼의 값이 NULL이 아닌경우 A.서비스ID의 데이터를 출력하고 NULL인경우 '합계'로 출력
GROUPING SETS - 여러 그룹핑 쿼리를 UNION ALL한것과 같이 나타난다.
GROUP BY GROUPING SETS(A, B, C,()) :빈괄호는 총 합계를 의미 제외시켜도 됨
-(A, NULL, NULL) _A별 소계
-(NULL, B, NULL) _B별 소계
-(NULL, NULL, C) _C별 소계만 묶어놓은 느낌
괄호 묶은 집합 별 집계 가능
GROUP BY GROUPING SETS(A, (B, C))로 입력하게 되면
-(A, NULL)
-(NULL, (B,C))
GROUP BY ROLLUP - 합계와 소계를 구함
ROLLUP(A,B)
-(A,B)
-(A,NULL) :A별 소계
-(NULL,NULL) :전체합계
*A와 B의 배치에 따라 결과가 다르게 나온다
GROUP BY CUBE - GROUP BY 항목들간 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해서 집계를 수행
ROLLUP보다 더 상세한 결과를 낸다.
CUBE(A,B)
-(A,B)
-(A,NULL) :A별 소계
-(NULL,NULL) :전체합계
-(NULL,B) :B별 소계
#GROUPING COLUMNS이 가질 수 있는 모든 경우에 대하여 SUBTOTAL을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직 하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.
윈도우 함수(WINDOW FUNTION) - 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수
순위, 합계, 평균, 행 위치 등을 조작할 수 있다.
적용범위는 PARTITION을 넘을 수 없다.
PARTITION 함수 - 그룹 내 순위 및 그룹 별 집계를 구할 때 유용하게 사용할 수 있다.
GROUP BY구문과 의미적으로 유사
PARTITION구문이 없으면 전체 집합을 하나의 PARTITION으로 정의한 것과 동일
PARTITION BY A
A컬럼별로 목록 추출
- RANK - 특정 항목에 대한 순위를 구하는 함수, 동일 한 값에 대해서는 동일한 순위를 부여(1, 2, 2, 4)
- DENSE_RANK - 동일한 순위를 하나의 등수로 간주(1, 2, 2, 3)
- ROW_NUMBER - 동일한 값이라도 고유한 순위 부여(1, 2, 3, 4)
DESC - 내림차순
| 1 2 3 |
ASC - 오름차순
| 3 2 1 |
ORDER BY AVG(상품가격)
RANGE BETWEEN 10000 PREDECING AND 10000 FOLLOWING
평균 상품 가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품을 출력
LAG - 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
LEAD - 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.
저장 모듈(Stored Module) - SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다.
# ORACLE의 저장모듈에는 프로시저(Procedure), 사용자 정의 함수(UDF - User Defined Funtion), 트리거(Trigger)가 있다.
PL/SQL 특징
- Block 구조로 되어있어 각 기능별로 모듈화 가능
- Block 단위로 처리 -> 통신량을 줄일 수 있다.
- 변수, 상수, 등을 선언하여 SQL 문장 간 값을 교환
- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
- PL/SQL 은 ORACLE 에 내장되어 있으므로 호환성 굳
- 응용 프로그램의 성능을 향상시킨다.
TRIGGER - 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램. 사용자 호출이 아닌 DB 자동 수행
CREATE TRIGGER
Dynamic SQL 또는 DDL 명령을 PL/SQL에서 실행시키려면 'execute immediate'를 작성해야 함
execute immediate 'TRUNCATE TABLE DEFT'
이번주 (오늘) 안에 1차정리 다 끝내려고 급하게 새벽에 정리한 내용들이라 잘 정리한건진 잘 모르겠으나 일단 고단했던 SQL 활용 단원도 드디어 끝났다...!
'프로그래밍 > SQLD' 카테고리의 다른 글
| 10. SQLD 마지막 총 정리 - 최후의 발악 (2) | 2022.09.03 |
|---|---|
| 9. 2과목 제 3장 SQL 최적화 기본 원리 (0) | 2022.08.28 |
| 7. 2과목 제 2장 SQL 활용 (2) - 계층형 데이터 (0) | 2022.08.27 |
| 6. 2과목 제 2장 SQL 활용 (1) - alias (1) | 2022.08.27 |
| 5. 2과목 제 1장 SQL 기본 (3) 43~64 (0) | 2022.08.23 |