SQLD - SQL 기본 및 활용

관계형 데이터베이스는 릴레이션에 데이터를 저장하고 관리하며 집합 연산(합집합, 차집합, 교집합, 곱집합)과 관계 연산을 할 수 있다

계층형 데이터베이스는 1대N 관계 - 부모와 자식 관계 표현하기 쉬움

네트워크 데이터베이스는 1대N, M대N 관계 표현 모두 가능하다

 

트랜잭션(Transaction)은 DB 작업을 처리하는 단위이다

 

트랜잭션의 특성 - 원자성(Atomicity), 일관성(Consistency), 고립성(Isolation), 영속성(Durability)

원자성 - 트랜잭션이 안끝났으면 실행되지 않은 상태와 같아야 한다

일관성 - 트랜잭션 실행 후에도 일관성이 유지되어야 한다

고립성 - 실행 결과를 다른 트랜잭션이 볼 수 없다

영속성 - 트랜잭션이 완료되면 그 결과는 영구적 보장이 되어야 한다

 

SQL 실행 순서 - Parsing -> Execution -> Fetch

 

DDL(Data Definition Language) - 관계형 DB의 구조를 정의하는 언어 create, alter, drop, rename

Create Table EMP(

empno number(10),

ename varchar2(20),

sal number(10,2) default 0,

deptno varchar2(4) not null,

createdate date default sysdate,

constraint emppk primary key(empno),

constraint deptfk foreign key (deptno) references dept(deptno)

on delete cascade);

 

on delete cascade 때문에 dept의 데이터가 삭제되면 emp에 있는 데이터도 삭제된다. 이는 참조 무결성을 준수할 수 있게 한다.

 

테이블명 수정 - ALTER TABLE EMP RENAME TO NEW_EMP;

컬럼 추가 - ALTER TABLE EMP ADD (age number(2) default 1);

컬럼 수정 - ALTER TABLE EMP MODIFY (ename varchar2(40) not null);

컬럼 삭제 - ALTER TABLE EMP DROP COLUMN age;

컬럼명 변경 - ALTER TABLE EMP RENAME COLUMN ename to new_ename;

 

테이블 삭제 - DROP TABLE EMP;

참조된 제약사항까지 모두 삭제할 때 - DROP TABLE EMP CASCADE CONSTRAINT;

테이블 구조는 남기고 데이터만 삭제할 때 - TRUNCATE TABLE EMP;

 

뷰 - 테이블에서 유도된 가상의 테이블, 참조한 테이블이 변경되면 뷰도 변경된다, 뷰에 대한 입력, 수정, 삭제는 제약이 있다, 보안성 향상, ALTER 못 씀

뷰 만들기 - CREATE VIEW T_EMP AS SELECT * FROM EMP;

WITH랑 차이가 뭐지?

 

DML(Data Manipulation Language) - 데이터를 입력, 수정, 삭제, 조회(INSERT, UPDATE, DELETE, SELECT)

INSERT문 - INSERT INTO EMP(EMPNO, ENAME) VALUES(1000,'임베스트');

Nologging - 로그 기록 최소화를 통해 성능 향상, ALTER TABLE DEPT NOLOGGING;

UPDATE문 - UPDATE EMP SET ENAME='조조' WHERE ENAME=100;

DELETE문 - DELETE FROM EMP WHERE EMPNO=100;

SELECT문 - SELECT * FROM EMP WHERE 사원번호=1000 ORDER BY ENAME, SAL DESC;

SELECT문의 실행 순서는 FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 순서이며, 이것이 쿼리를 타이핑하는 순서는 아니다.

 

NULL의 특징 - 모르는 값, 값의 부재, 숫자, 날짜를 NULL이랑 연산하면 값은 NULL, 비교하면 '알 수 없음' 반환

NVL(MGR,0) MGR이 NULL이면 0으로 반환

NVL2(MGR,1,0) NULL이 아니면 1, NULL이면 0

NULLIF(exp1,exp2) exp1=exp2면 NULL, 다르면 exp1 반환

COALESCE(exp1,exp2,exp3) NULL이 아닌 최초의 인자 값 반환

 

GROUP BY는 테이블에서 소규모 행을 그룹화한다. HAVING을 이용해 조건문 사용, ORDER BY로 정렬 가능

SELECT DEPTNO, SUM(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING SUM(SAL)>10000; --GROUP BY 결과에서 급여합계가 10000이상만 조회하는 조건

 

STDDEV()	표준편차
VARIAN()	분산
TO_CHAR(숫자OR날짜,[FORMAT])	숫자OR날짜를 문자로
TO_DATE(문자열,[FORMAT])	날짜로
SUBSTR(문자열,m,n)	문자열에서 m번째 위치부터 n개 자른다
CONCAT(문자열1, 문자열2)	두 문자열 결합, ||로도 가능
LENGTH(), LEN()	공백 포함한 문자열 길이
L(R)TRIM(문자열, (지정문자))	왼(오른)쪽에서 지정된 문자 삭제, 지정문자 생략하면 공백 삭제
TRIM(문자열, (지정문자))	양쪽에서 지정된 문자 삭제, 생략하면 공백 삭제
SIGN(숫자)	양수, 음수, 0 구분
MOD(숫자1,숫자2)	숫자1나누기 숫자2의 나머지
CEIL(10.5), CEILING(숫자)	11
FLOOR(10.5)	10
ROUND(10.543,1)	10.5 (소수점 둘째자리에서 반올림, 첫째자리까지 보임)
TRUNC(10.543,1)	10.5 (소수점 둘째자리부터 버림, 첫째자리까지 보임)

 

DECODE(EMPNO, 1000, 'TRUE', 'FALSE') -- EMPNO=1000이면 TRUE 아니면 FALSE

CASE WHEN EMPNO=1000 THEN 'A' WHEN EMPNO=1001 THEN 'B'

 

ROWNUM - SELECT문 결과에 대해 논리적인 일련번호를 부여하는 것

 

WITH구문은 서브쿼리(Subquery)를 사용해 임시 테이블이나 뷰처럼 사용할 수 있는 구문

 

DCL(Data Control Language) - DB 사용자에게 권한을 부여하거나 회수하는 명령어

GRANT SELECT, INSERT, UPDATE, DELETE ON EMP TO LIMBEST WITH GRANT OPTION;

 

TCL(Transaction Control Language) - 트렌잭션을 제어하는 명령어, COMMIT, ROLLBACK, SAVEPOINT

 

해시 조인(Hash Join) - 선행 테이블을 결정하고 그 테이블에서 where절에 부합하는 행을 선택한다 -> 조인 키를 기준으로 해시 함수를 이용해 해시 테이블을 메인 메모리에 생성하고, 후행 테이블에서 주어진 조건에 만족하는 행을 찾는다 -> 후행 테이블의 조건 키를 이용해 해시 함수를 적용해 해당 버킷을 검색한다, equi join에서만 사용할 수 있음, 두 테이블 중 작은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해 해시 테이블을 생성한다. CPU 연산이 많다. 메모리가 여유 있어야 한다.

Equi join - 두 테이블 간의 교집합, '='만 사용, inner join과 같음, 

Cross join - 조건구 없이 조인, 따라서 카테시안 곱이 발생한다

 

UNION - 두 테이블을 하나의 테이블로, 이 때, 컬럼 수, 데이터 형식 모두 일치해야 한다, UNION은 중복 제거, UNION ALL은 중복 포함

SELECT DEPTNO FROM EMP

UNION (ALL)

SELECT DEPTNO FROM EMP;

 

MINUS, INTERSECT - 차집합, 교집합

SELECT DEPTNO FROM EMP

MINUS(INTERSECT)

SELECT DEPTNO FROM EMP;

 

계층형 조회(Connect by) - 트리 형태의 구조를 위에서 아래로 탐색하면서 조회하는 것으로 START WITH구는 시작 조건, CONNECT BY PRIOR는 조인 조건

SELECT LEVEL, EMPNO, MGR, ENAME

FROM LIMBEST.EMP

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO=MGR;

LEVEL	레벨 보여줌, 가장 상위가 1
CONNECT_BY_ROOT	가장 최상위 값 보여줌
CONNECT_BY_ISLEAF	가장 최하위 표시
SYS_CONNECT_BY_PATH	전체 전개 경로 표시
NOCYCLE	순환구조가 발생지점까지만 전개된다
CONNECT_BY_ISCYCLE	순환구조 발생 지점을 표시한다

 

서브쿼리 - SELECT문 내에서 다시 SELECT문을 쓰는 것

FROM에 쓰면 인라인 뷰(Inline view), SELECT문에 쓰면 스칼라 서브쿼리(Scala subquery), WHERE에 쓰면 서브쿼리(subquery)

 

단일 행 서브쿼리(Sigle row subquery) - 결과는 한 행만 나옴

다중 행 서브쿼리(Multi row subquery) - 결과는 여러 행이 나옴

 

ROLLUP - GROUP BY 컬럼에 대해 Subtotal을 만들어준다.

SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO), SUB(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO);

--DEPTNO SUM값 다 나오고 마지막 행에 각 SUM(SAL)의 전체합계가 나옴

 

GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해서 만들어진 함수다. 소계, 합계 등이 계산되면 GROUPING 함수는 1을 반환하고 그렇지 않으면 0을 반환한다.

 

GROUPING SETS 함수는 GROUP BY에 나오는 칼럼의 순서와 상관없이 다양한 소계를 만들 수 있다

 

CUBE 함수는 제시한 칼럼에 대해 결합 가능한 모든 집계를 계산한다

SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB);

-- 전체 합, JOB 별 합, DEPTNO 별 합, DEPTNO-JOB 별 합 반환

 

윈도우 함수는 행과 행 관계를 정의하기 위해 제공되는 함수다. 포기

 

파티션 - 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장한다 -> 성능 향상, 파티션별로 독립적으로 관리할 수 있다

Range Partition - 값의 범위를 기준으로 데이터를 나누어 저장할 수 있다

List Partition - 특정 값을 기준으로 분할

Hash Partition - 해시 함수를 이용해 테이블을 분리

Global Index	여러 개의 파티션에서 하나의 인덱스 사용
Local Index	해당 파티션 별로 각자의 인덱스 사용
Prefixed Index	파티션 키와 인덱스 키가 동일
Non Prefixed Index	파티션 키와 인덱스 키가 다르다

 

옵티마이저 - SQL 실행 계획을 수립하고 SQL을 실행하는 DB 관리 시스템의 소프트웨어다, SQL 실행 계획을 PLAN_TABLE에 저장

 

옵티마이저 실행 방법 - 개발자가 SQL을 실행하면 파싱해서 SQL 문법 검사와 구문 분석을 함 -> 옵티마이저가 규칙 기반 혹은 비용 기반 실행 계획을 수립함(기본은 비용 기반으로 함. 비용 기반 옵티마이저는 통계정보를 활용해 최적의 실행 계획을 수립하는 것) -> 실행 계획 수립이 완료되면 SQL을 실행하고 실행이 완료되면 데이터를 인출(Fetch)한다.

 

Nested Loop 조인 - 하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식, 먼저 조회되는 테이블을 외부 테이블, 그다음 조회되는 테이블을 내부 테이블, 선행 테이블을 먼저 찾는것이 좋음. RANDOM ACCESS가 많이 발생하면 성능 지연이 발생한다.

 

Sort Merge 조인 - SORT_AREA라는 메모리 공간에 모두 로딩하고 SORT 수행, 따라서 데이터가 많아지면 성능이 저하된다. 메모리 공간보다 용량이 크면 디스크로 옮겨지고 이는 더 큰 성능 저하를 가져온다.