시험을 준비하면서 핵심적인 부분만 노트를 하여 공개한다.
SQLD / SQLP 공통 : 제2과목은 SQL 기본 및 활용 이다.
2과목. SQL 기본 및 활용
- SQL 기본
- SQL 활용
- 관리 구문
DDL
- 데이터 정의어 (Data Definition Language)
- CREATE, ALTER, DROP, RENAME, TRUNCATE
DML
- 데이터 조작어 (Data Manipulation Language)
- SELECT, INSERT, UPDATE, DELETE, MERGE
DCL
- 데이터 제어어 (Data Control Language)
- GRANT, REVOKE
TCL
- 트랜잭션 제어어 (Transaction Control Language)
- COMMIT, ROLLBACK, SAVEPOINT
| MS-SQL 은 자동 커밋으로, begin tran 을 사용하지 않으면, DML 문장이 바로 커밋된다. 오라클 은 반대로, DML 문장 사용후, commit / rollback 을 하지 않으면, Lock 이 걸린다. |
제약조건
PK (Primary Key)
- 데이터를 고유하게 식별하기 위한 기본키, NULL 값은 불가
Unique Key
- 데이터를 고유하게 식별하기 위한 고유키로, NULL 값은 제약 대상에서 제외
NOT NULL
- NULL 값을 금지
CHECK
- True / False 의 논리식으로 값의 범위를 지정
FK (FOREIGN KEY)
- 테이블간의 관계에서 기본키를 다른 테이블의 외래키로 지정하여 사용
함수
ABS
- 절대값 : 음수 -> 양수
FLOOR
- 내림 함수
- 3.14 => 3, -3.14 => -4
TRUNC
- 숫자 : 지정한 자리수(없으면 소수점) 을 날려버리는 함수
- 날짜 : 날짜의 시간을 날려 버림, YY : 년도 빼고 나머지 절사, MM : 년/월 빼고 나머지 절사
- 3.14 => 3, -3.14 => -3
ROUND
- 반올림
TO_NUMBER, TO_DATE, TO_CHAR
- 숫자, 날짜, 문자 변환
LOWER, UPPER
- 소문자, 대문자 변환
SUBSTR, LENGTH
- 문자열 자르기, 문자열 길이
LTRIM, RTRIM, TRIM
- 문자열 왼쪽 공백 제거, 오른쪽 공백 제거, 양쪽 공백 제거
COALESCE
- COALESCE(v1, v2, v3) : 앞에서 부터 NULL 이 아닌 최초값
NVL
- NVL(expr, v1) : expr 이 NULL 이면 v1, NULL 이 아니면 expr
NVL2
- NVL2(expr, v1, v2) : expr 이 NULL 이면 v2, NULL 이 아니면 v1
ISNULL
- MSSQL 함수, NVL 과 동일
NULLIF
- NULLIF(expr, v1) : expr 과 v1 이 같으면 NULL, 같지 않으면 expr
DECODE
- DECODE(expr, c1, v1, c2, v2, v3) : expr 이 c1 이면 v1, c2 일때 v2, 그외에는 v3
LPAD, RPAD
- 왼쪽에 문자 채우기, 오른쪽에 문자 채우기
- LPAD('ABCD', 10, ' ') => ' ABCD' => 왼쪽에 스페이스를 채워서 10자리 문자로 만든다.
집계함수
- COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN
- 갯수, 합계, 평균, 가장큰값, 가장작은값, 표준편차, 분산
- NULL 은 제외된다.
CASE 문
- case expr when 1 then '참' else '거짓' end : expr 이 1이면 참 아니면 거짓
- case when expr <= 10 then '참' else '거짓' end : expr 이 10 이하면 참, 아니면 거짓
GROUP BY / HAVING
- 집계함수 Count 에서 데이터가 없으면 0 이 나오지만, having 절에 의해 데이터가 없으면, no records 이다.
ORDER BY
- 기본정렬은 오름차순 ASC 이고, 내림차순은 DESC 이다.
- 오라클은 NULL 이 가장 큰 값으로 간주하여, 오름차순에서 가장 아래에 위치한다.
- MSSQL 은 NULL 이 가장 작은 값으로 간주하여, 오름차순에서 가장 위에 위치한다.
SELECT 실행순서
5. SELECT
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
6. ORDER BY
TOP N 쿼리
MSSQL
select top 10 * from order by col1
Oracle
select *
from (
select *
from
order by col1
)
where rownum <= 10
INNER JOIN
- 조건에 일치하는 행만 반환한다.
from A inner join B on A.col1 = B.col1
from A join B on A.col1 = B.col1
from A, B where A.col1 = B.col1
- using 사용
from A join B using(col1)
OUTER JOIN
- Join 조건에 일치하는 행과, 일치하지 않는 행도 반환한다.
left outer join
from A left outer join B on A.col1 = B.col1
from A left join B on A.col1 = B.col1
from A, B where A.col1 = B.col1(+)
- 조건에 일치하는 행과 왼쪽 테이블 기준으로 오른쪽에는 일치하지 않는 행도 반환한다.
- A, B 테이블에 일치하는 데이터와, A에만 있고 B에는 없는 행도 반환
right outer join
from A right outer join B on A.col1 = B.col1
from A right join B on A.col1 = B.col1
from A, B where A.col1(+) = B.col1
- 조건에 일치하는 행과 오른쪽 테이블 기준으로 왼쪽에는 일치하지 않는 행도 반환한다.
- A, B 테이블에 일치하는 데이터와, B에만 있고 A에는 없는 행도 반환
full outer join
from A full outer join B on A.col1 = B.col1
- 조건에 일치하는 행과 양쪽 테이블의 조건에 일치하지 않는 행도 반환한다.
- A, B 테이블에 일치하는 데이터와, A에만 있고 B에는 없는 행, B에만 있고 A에는 없는 행도 반환
NATURAL JOIN
- A, B 테이블에 같은 이름의 모든 컬럼이 자동으로 연결된다.
- SELECT 절에 EMP.col 테이블명을 지정해서 쓰면 오류가 발생한다.
from A natural join B
CROSS JOIN
- join 조건이 없는 경우, 테이블간에 모든 데이터 조합으로 행을 반환한다.
- M * N 의 행 조합
from A cross join B
집합 연산자
UNION
- 합집합, 중복데이터는 제외
UNION ALL
- 합집합, 중복데이터 유지
INTERSECT
- 교집합, 중복데이터는 제외
EXCEPT (오라클 MINUS)
- 차집합
- A 에서 B 의 데이터를 제외한 결과
계층형질의
START WITH
CONNECT BY
조직도와 같은 Tree 구조의 데이터를 조회할 때 사용한다.
가상컬럼
level : 해당 행이 ROOT 데이터이면 1, 하위 데이터이면 2, 그 하위이면 +1 씩 증가
connect_by_isleaf : 해당 행이 LEAF 데이터 이면 1, 아니면 0
connect_by_iscycle : 중복 참조이면 1, 아니면 0 : 현 데이터의 하위 데이터가, 하위 데이터의 현데이터로 지정한 경우, 무한 루프에 빠지게 된다.
select level, ( lpad(' ', 4 * (level-1)) || empno ) as empno
, connect_by_isleaf as isleaf
from emp
start with empno is null
connect by prior mgr = empno;
start with 조건에는 root 노드를 지정하고
connect by 에는 하위노드를 찾는 방법을 지정한다.
prior 에 지정된 컬럼에서 상대편 컬럼으로 찾아간다.
CONNECT BY PRIOR 하위 컬럼 = 상위 컬럼 : 상위 -> 하위로 가는 순방향 전개
CONNECT BY PRIOR 상위 컬럼 = 하위 컬럼 : 하위 -> 상위로 가는 역방향 전개
ORDER SIBLINGS BY
- order by 와 같이 정렬을 수행하나, 같은 level 간의 정렬 순위를 지정한다.
NOCYCLE
- connect by NOCYCLE prior mgr = empno
- 데이터 입력실수로, 이전에 전개된 데이터가 다시 나타나는 현상이 발생하면 무한루프에 빠지게 된다.
- 이전에 전개된 데이터가 다시 나오면 전개를 멈추는 옵션.
서브쿼리
- 괄호로 묶인 SELECT 절
단일행 서브쿼리
- 결과행이 1개만 나오는 서브쿼리
- select 쿼리 안에 단일 값 조회시 사용되거나, wehere 절의 비교 연산자 등에 온다.
다중행 서브쿼리
- 결과행이 n 개 나오는 서브쿼리
- from 절의 쿼리에 오거나, where 절의 in , all, any, some, exists 등에 온다.
뷰(VIEW)
독립성 - 테이블구조가 변경되어도, 뷰를 사용하는 응용프로그램은 변경할 필요가 없다.
편리성 - 복잡한 질의나 자주 사용하는 질의를 뷰로 만들면, 쿼리가 간단해 진다.
보안성 - 숨기고 싶은 정보는 제외하고, 정보(컬럼) 를 제공할 수 있다.
그룹 함수
ROLLUP
- 지정된 컬럼의 소계와 합계를 구하는 함수
- 인수의 순서에 따라 결과가 달라진다.
group by rollup(dname, job)
- dname 별 소계, 전체 합계를 구한다.
CUBE
- 가능한 모든 조합의 소계와 합계를 구한다.
- 인수의 순서는 상관이 없다.
group by cube(dname, job)
- 모든 dname 별 소계, job 별 소계, 전체 합계를 구한다.
GROUPING SETS
- 소계 / 합계를 직접 선택하여 지정할 수 있는 함수
- () 또는 null 을 쓰면 전체 합계를 구한다.
group by grouping sets( (dname, job), (dname), () )
- 이렇게 쓰면 group by rollup(dname, job) 과 동일하다.
group by grouping sets( (dname, job), (dname), (job), () )
- 이렇게 쓰면 group by cube(dname, job) 과 동일하다.
GROUPING 함수
- select 절에서 해당 행이 소계에 사용된 행인지 여부를 판단할 때 사용한다.
select (case when grouping(job) = 1 then 'JOB합계' else job end )
- 부서별 모든 소계행인경우, JOB합계로 표시하고, 아닌경우 job 명칭을 표시한다.
Window 함수
partition by
sum(sal) over(partition by deptno)
sum(sal) over(partition by deptno group by emp range between unbounded preceding and current ro)
rows
- 물리적인 결과 행의 수를 지정한다.
range
- 논리적인 값에 의한 범위를 지정한다.
n preceding
- n 개 앞의 행
unbounded preceding
- 첫번째 행
n following
- n 개 뒤의 행
unbounded following
- 마지막 행
current row
- 현재 행
RANGE UNBOUNDED PRECEDING
SUM(sal) OVER(partition by mgr order by sal range unbounded preceding)
= RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 와 동일하다.
- 파티션 내에서 첫번째 행 부터 현재 행까지의 합계 즉 누적합을 표시
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
SUM(sal) OVER(partition by mgr order by sal rows between 1 preceding and 1 following)
- 현재행+앞에1행+뒤에1행 합계
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
SUM(sal) OVER(partition by mgr order by sal range between current row and unbounded following)
- 현재행부터 마지막 행까지 합계
CTE(Common Table Expression)
- 일시적인 결과 세트를 정의하고 이를 나중에 쿼리에서 참조할 수 있게 하는 방법
CTE와 서브쿼리의 차이점
- 가독성: CTE는 복잡한 쿼리를 여러 부분으로 나누어 가독성 높여준다.
- 재사용성: CTE는 동일한 쿼리 내에서 여러 번 재사용 가능
- 성능: CTE와 서브쿼리의 성능 차이는 데이터베이스 시스템과 쿼리의 복잡도 따라 다름
- CTE는 재귀적 쿼리에 유리
WITH kor_restaurants AS (
SELECT *
FROM restaurants
WHERE rest_type = 'kor'
)
재귀적 CTE
WITH cte (n) AS
(
SELECT 1
UNION ALL
SELECT n+1 FROM cte WHERE n < 5
)
NTILE
- 전체건수를 지정한 건수로 N등분
LAG
- N개 앞의 행 값 조회
LEAD
- N개 뒤의 행 값 조회
where in (list) 에서 list 에 null 이 있으면, 실제 null 인 데이터는 제외 된다.
- where ID in (1, 2, null) : ID 가 null 인 데이터는 제외되고, 1이나 2인 데이터만 조회된다.
순위
ROW_NUMBER()
- 1,2,3 순차적으로 순위를 매김
RANK()
- 1,1,1,4,5,6 동일 값이면 같은 순위를 갖고, 다음순위는 건너뜀.
DENSE_RANK()
- 1,1,1,2,3,4 동일 값이면 같은 순위를 갖고, 다음순위로 넘어감.
PIVOT
- 행을 열로 변환한다.
SELECT *
FROM 테이블
PIVOT ( 그룹합수(집계컬럼) FOR 피벗컬럼 IN (피벗컬럼값 AS 별칭 ... )
SELECT job, d1, d2, d3
FROM emp PIVOT (
SUM(sal) FOR deptno IN ('10' AS d1, '20' AS d2, '30' AS d3)
)
UNPIVOT
- 열을 행으로 변환한다.
SELECT *
FROM 테이블
UNPIVOT ( 컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... )
SELECT col_nm, col_val
FROM emp UNPIVOT (
col_val FOR col_nm IN (col1, col2, col3)
)
'데이터베이스' 카테고리의 다른 글
| [SQLD/SQLP 핵심노트] 1과목 데이터 모델링의 이해 (0) | 2025.02.25 |
|---|---|
| mysql / mariadb 슬로우쿼리(slowquery) 탐지하기 (0) | 2022.03.29 |
