SQLD - 3. SQL 기본 및 활용

관계형 데이터베이스 개요

데이터 베이스

• 데이터를 일정한 체계에 따라 통합하여 디스크나 메모리에 저장한 것으로 응용 프로그램에 종속적이지 않도록 일종의 미들웨어 현태로 만든 것
• 자료의 독립성, 중복 저장의 최소화, 통합 처리, 자체적 관리 기능에 이점
• 데이터베이스를 구축하고 관리할 수 있는 기능을 제공하는 시스템 소프트웨어를 데이터베이스 관리 시스템(DBMS, Database Management System)이라고 부름

관계형 데이터베이스(RDB, Relation Database)

• 데이터의 일관성 문제를 해결하기 위해 E.F Codd 박사에 의해 만들어진 데이터베이스
• ACID(Atomic - 원자성, Consistency - 일관성, Isolation - 고립성, Durability - 영속성, 지속성) 특성을 가짐
• 테이블(Table) 형태로 데이터 모델을 다룸
• SQL(Structure Query Language)이라는 공통 질의언어를 사용

테이블(TABLE)

• 2차원 구조의 행(Row)과 열(Column)로 표현된 형태
• 행(Row)은 해당 테이블의 스키마를 가지는 하나의 인스턴스, 레코드(Record) 또는 튜플(Tuple)이라고도 함
• 열(Column)은 필드(Field)라고도 하며 속성(Attribute)에 해당

SQL(Structure Query Language)

• 구조화된 질의언어로 데이터베이스의 구조를 정의하고 데이터를 조작, 제어할 수 있는 절차적 + 비절차적 언어
• How(어떻게)의 관점보다 What(무엇을)의 관점에서 절차를 명기하지 않고 원하는 결과만을 서술하는 언어로서 비절차적 언어라고 할 수 있지만 프로시저, 함수, 트리거 등 절차적 언어의 특징도 포함

DDL(데이터 정의 언어, Data Definition Language)

• 데이터의 구조(스키마)를 정의하는 명령어
• CREATE, ALTER, DROP, RENAME, TRUNCATE 등

DML(데이터 조작 언어, Data Manipulation Language)

• 테이블에 데이터를 조회, 입력, 수정, 삭제하는 명령어
• SELECT, INSERT, UPDATE, DELETE, MERGE 등
• DROP, TRUNCATE는 디스크 저장공간을 릴리즈하여 재사용 가능하며 로그를 남기지 않음
• DELETE는 저장공간을 릴리즈 하지 않아 롤백이 가능하며 로그를 남김

DCL(데이터 제어 언어, Data Control Language)

• 사용자 접근 권한과 같이 보안과 제어를 다루는 명령어
• GRANT, REVOKE 등

TCL(트랜잭션 제어 언어, Transaction Control Language)

• 트랜잭션에 대해 제어를 다루는 명령어(DCL의 일부로 보기도 함)
• COMMIT, ROLLBACK, SAVEPOINT 등

---

SELECT문

SELECT

• 데이터를 조회하는 명령어
• FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 순서로 실행

SELECT 조회할 칼럼 FROM 테이블 WHERE 조건 GROUP BY 칼럼 HAVING 조건 ORDER BY 칼럼;

산술연산자

• 더하기, 곱하기 등 산술연산을 수행

연산자	설명	연산 우선순위
()	먼저 계산할 식을 묶음	1
*	곱셈을 수행	2
/	나눗셈을 수행	2
%	moc 연산(나머지 연산)을 수행	2
+	덧셈을 수행	3
-	뺄셈을 수행	3

• Null의 산술연산 결과는 Null
• 0으로 나누셈을 수행할 경우 오류 발생

합성연산자

• 문자열을 연결하는 연산자(“||”)

SELECT 'GOOD' || ' ' || 'MORNING' AS HELLO FROM DUAL;
=> | HELLO        |
   | GOOD MORNING |

---

함수

• 입력된 값에 대해서 어떤 연산을 수행한 후 결과를 반환하는 일련의 코드 덩어리
• 내장함수(Built-in Function): DBMS별로 이미 만들어져 제공되는 함수
  
  • 단일행 함수(Single-Row Function): 하나의 행에 대해서 연산을 수행한 후 결과를 반환하는 함수
  • 다중행 함수((Multi-Row Function): 여러 행에 대해서 연산을 수행한 후 결과를 반환하는 함수
  • 두 함수 모두 여러 입력 인자에 대해 단일값을 반환
• 사용자 정의 함수(User Defined Function): 사용자가 직접 SQL문을 작성하여 정의한 함수

문자 함수

함수	설명
LOWER(arg)	문자열 arg를 모두 소문자로 반환
UPPER(arg)	문자열 arg를 모두 대문자로 반환
CHR(arg)	ASCII 코드값 arg에 대응하는 문자를 반환
TRIM(arg1 arg2 FROM arg3)	문자열 arg3 양 끝에서 문자열 arg2 또는 공백을 제거하고 반환(arg2 생략 시 공백 제거) ※ arg1: LEADING(앞에서), TRAILING(뒤에서), BOTH(양쪽에서), 기본값은 BOTH
LTRIM(arg1, arg2)	문자열 arg1 왼쪽 끝에서 문자열 arg2 또는 공백을 제거하여 반환(arg2 생략 시 공백 제거)
RTRIM(arg1, arg2)	문자열 arg1 오른쪽 끝에서 문자열 arg2 또는 공백을 제거하여 반환(arg2 생략 시 공백 제거)
SUBSTR(arg1, arg2, arg3)	문자열 arg1을 arg2자리 부터 arg3자리를 추출하여 반환(arg3 생략 arg2자리부터 문자열 끝까지 추출)
LENGTH(arg)	문자열 arg의 길이를 반환
REPLACE(arg1, arg2, arg3)	문자열 arg1의 문자열 arg2를 문자열 arg3으로 대체하여 반환(arg3 생략 시 arg2 문자열 삭제)

숫자 함수

함수	설명
ABS(arg)	숫자의 절댓갑을 반환
MOD(arg1, arg2)	숫자 arg1을 arg2로 나눈 나머지 반환
ROUND(arg1, arg2)	숫자 arg1을 소수점 아래 arg2자리까지 반올림하여 반환(arg2 생략 시 소수점 모두 반올림)
TRUNC(arg1, arg2)	숫자 arg1을 소수점 아래 arg2자리까지 버림한 후 결과 반환(arg2 생략 시 소수점 모두 버림)
SIGN(arg)	숫자 arg가 양수이면 1, 음수이면 -1, 0이면 0을 반환
CEIL(arg)	숫자 arg보다 크거나 같은 최소의 정수를 반환
FLOOR(arg)	숫자 arg보다 작거나 같은 최대의 정수를 반환

날짜 함수

함수	설명
SYSDATE	오늘의 날짜를 날짜형으로 반환
EXTRACT(arg1 FROM arg2)	날짜 arg2로부터 년, 월, 일을 추출해서 반환 ※ arg1: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

변환 함수

• SQL 작성자가 함수를 명시하여 수행하는 명시적 형변환, 시스템 내부적으로 임의로 수행하는 암시적 형변환 존재
• 암시적 형변환은 결과를 명확하게 예측할 수 없고, 성능 저하나 에러가 발생할 가능성이 있어 주의해야 함

함수	설명
TO_NUMBER(arg)	문자열 arg를 숫자형으로 변환
TO_CHAR(arg1, arg2)	숫자형, 날짜열 arg1을 문자열형으로 변환(arg2는 날짜 포맷 패턴)
TO_DATE(arg1, arg2)	문자열 arg1을 arg2 포맷의 날짜형으로 변환(arg2는 날짜 포맷 패턴)

NULL 관련 함수

함수	설명
NVL(arg1, arg2)	arg1이 Null이 아니면 그대로 반환, Null이면 arg2를 반환(arg1과 arg2는 같은 타입)
NULLIF(arg1, arg2)	arg1과 arg2가 같으면 Null 반환, 다르면 arg1 반환(arg1과 arg2는 같은 타입)
COALESCE(arg1, arg2, …)	입력된 인자들을 순서대로 평가하여 Null이 아닌 첫 번째 인자를 반환

CASE

• 칼럼이 특정 값을 가지면 이를 대체하는 새 값을 반환하는 연산을 정의

CASE
    WHEN 컬럼 = 특정 값 THEN 새 값
    WHEN 컬럼 = 특정 값 THEN 새 값
    ...
END
또는
CASE
    컬럼 WHEN 특정 값 THEN 새 값
    컬럼 WHEN 특정 값 THEN 새 값
    ...
END

---

WHERE절

• 특정 조건의 행(Row)만을 대상으로 연산을 수행하기 위해 조건을 설정하는 구문
• SELECT, UPDATE, DELETE문에 사용 가능
• NULL과의 비교는 IS NULL, IS NOT NULL만 사용 가능
• FROM절에서 정의한 별명(Alias)은 사용 가능, SELECT절에서 정의한 별명은 사용 불가
• 우선순위: 산술 연산자 → 연결 연산자 → 비교 연산자 → NOT 연산자 → AND 연산자 → OR 연산자

비교연산자

• 두 값의 값이나 크기를 비교하여 참(true), 거짓(false)을 반환

단일행 비교연산자

연산자	설명
A = B	A와 B의 값이 같으면 참(true)을 반환
A < B	A가 B보다 작으면 참(true)을 반환
A <= B	A가 B보가 작거나 같으면 참(true)을 반환
A > B	A가 B보다 크면 참(true)을 반환
A >= B	A가 B보가 크거나 같으면 참(true)을 반환
A IS NULL	A가 Null이면 참(true)을 반환

다중행 연산자

연산자	설명
A IN (arg1, arg2 …)	입력된 인자들 중 A와 같은 값이 하나라도 있으면 참(true)을 반환
EXISTS (SUB QUERY)	서브 쿼리 결과가 한 건이라도 있으면 참(true)을 반환
A 비교연산자 ALL(arg1, arg2 …)	입력된 인자들과 A를 비교하여 모두 참이면 참(true)을 반환
A 비교연산자 ANY(arg1, arg2 …)	입력된 인자들과 A를 비교하여 하나라도 참이면 참(true)을 반환

부정 비교연산자

단일행 부정 비교연산자

연산자	설명
A !=, ^=, <> B	A와 B가 서로 다르면 참(true)을 반환
A IS NOT NULL	A가 Null이 아니면 참(true)을 반환

다중행 부정 비교연산자

연산자	설명
A NOT IN (arg1, arg2 …)	입력된 인자들 중 A와 같은 값이 하나도 없으면 참(true)을 반환
NOT EXISTS (SUB QUERY)	서브 쿼리 결과가 한 건도 없으면 참(true)을 반환

SQL 연산자

연산자	설명
BETWEEN A AND B	A보다 크거나 같고 B보다 작거나 같으면 참(true)을 반환
A LIKE arg	문자열 A에서 인자값을 검색 예) LIKE ‘%m%’ → ‘m’이 있는 문자열 / LIKE ‘_m’ → ‘m’으로 끝나는 2자리의 문자열 (%은 0개 이상의 문자, _는 임의의 문자 1개를 의미)

논리연산자

연산자	설명
AND	AND로 연결된 모든 조건이 참이면 참(true)을 반환
OR	OR로 연결된 조건중 하나라도 참이면 참(true)을 반환
NOT	뒤에 오는 식의 결과(참, 거짓)의 반대를 반환

---

GROUP BY, HAVING절

GROUP BY

• 데이터를 그룹화하는 구문
• 필터링을 수행하고자 한다면 부하가 높은 연산이므로 조회 성능이 나빠질 수 있으므로 가급적 WHERE절을 사용해서 먼저 필터링을 수행

집계함수

• GROUP BY절을 통해 데이터를 그룹화한 후 개수, 합, 평균 등의 통계값을 계산하는 함수

연산자	설명
COUNT(칼럼)	칼럼에서 값이 Null인 행(Row)을 제외한 행의 개수를 반환
SUM(칼럼)	칼럼의 합을 반환
AVG(칼럼)	칼럼의 평균을 반환
NIM(칼럼)	칼럼의 최솟값을 반환
MAX(칼럼)	칼럼의 최대값을 반환

HAVING

• 필터링할 조건을 명시하는 구문
• WHERE절에서는 집계함수가 사용이 불가능하지만 HAVING절에서는 사용 가능
• GROUP BY와 HAVING은 SELECT절 보다 먼저 실행되므로 SELECT절에서 지정한 별명(Alias) 사용 불가
• 일반적으로 GROUP BY절과 함께 사용되지만 GROUP BY 없이도 사용 가능

---

ORDER BY절

ORDER BY

• 정렬 조건을 명시하는 구문, 생략 시 임의의 순서로 출력
• SELECT 절보다 나중에 수행되므로 칼럼에 대한 별명(Alias)이나 순서를 나타내는 정수 사용 가능
• GROUP BY절보다 나중에 수행되므로 집계함수 사용 가능
• 칼럼의 값이 Null인 경우 DBMS마다 정렬 방식이 다름(Oracle은 최댓값, SQL Server는 최솟값으로 처리)

---

조인(Join)

조인의 개념

• 정규화를 통해 분리된 테이블을 PK 또는 FK 연관성에 의해 병합하는 것
• 건수에 제곱의 시간복잡도를 가지므로 많은 CPU 연산이 필요
• 조회 성능을 높이기 위해 조인을 줄이고 반정규화를 진행하는 것이 유리

EQUI JOIN

• 등식(=, 칼럼값이 정확하게 일치)을 조건으로 사용할 때 발생하는 조인

Non EQUI JOIN

• 등부등식(BETWEEN, >, >=, <, <=)을 사용해서 범위를 나타낸 조건을 사용할 때 발생하는 조인
• 설계상의 이유로 실행이 불가능할 때도 있으므로 주의해야 함

3개 이상 TABLE JOIN

• N개의 테이블 조인에서는 N-1번의 조인이 발생
• 연산량이 기하급수적으로 증가하므로 반정규화를 진행하는 것이 유리

OUTER JOIN

• 조인 조건에 맞지 않는 행까지 포괄적으로 병합하는 조인

---

표준 조인

• DBMS에 따라 SQL 문법이 약간씩 다르기 때문에 ANSI SQL이라는 표준 문법을 제작
• ANSI SQL의 문법에 따른 조인 쿼리

SELECT ~ FROM 테이블 조인 테이블 ON 조건식

INNER JOIN

• 교집합의 개념으로 기준이 되는 키에 따른 칼럼값이 존재하는 것만 병합하여 결과 반환

SELECT ~ FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.NAME = T2.NAME

OUTER JOIN

• 합집합의 개념으로 기준이 되는 키에 따른 칼럼값이 존재하지 않더라도 모든 튜플을 병합하여 결과 반환
• 조인의 대상이 되는 두 개의 테이블을 각각 왼쪽, 오른쪽 테이블이라고 할 때, 어떤 테이블의 행들을 모두 포함시킬 것인지에 따라 Left Outer Join, Right Outer Join, Full Outer Join으로 나뉨

SELECT ~ FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON T1.NAME = T2.NAME
SELECT ~ FROM TABLE1 T1 RIGHT OUTER JOIN TABLE2 T2 ON T1.NAME = T2.NAME
SELECT ~ FROM TABLE1 T1 FULL OUTER JOIN TABLE2 T2 ON T1.NAME = T2.NAME

NATURAL JOIN

• 조인의 대상이 되는 두 테이블에서 같은 이름의 칼럼에 대해서는 동일한 칼럼값을 가지는 행만 병합하여 결과 반환
• 조인 조건을 내포하고 있으므로 ON 절을 사용할 수 없고 SELECT절의 테이블 명칭(Alias)을 표시할 수 없음

SELECT ~ FROM TABLE1 T1 NATURAL JOIN TABLE2 T2 ON T1.NAME = T2.NAME

CROSS JOIN

• 왼쪽, 오른쪽 테이블 모든 행의 대응을 조합(경우의 수)하여 결과를 반환
• 카테시안 곱(Cartesian Product), 왼쪽 테이블이 M행, 오른쪽 테이블이 N행일 때 결과는 M x N행
• 조인의 조건은 별도로 지정하지 않음

SELECT ~ FROM TABLE1 T1 CROSS JOIN TABLE2 T2
SELECT ~ FROM TABLE1 T1, TABLE2 T2

SORT MERGE JOIN

• 조인의 조건이 되는 칼럼에 인덱스가 없어서 인덱스 스캔을 할 수 없거나, 랜덤 액세스 방식이 부담될 경우 전체 테이블 스캔 방식을 사용하여 조인 칼럼에 대해 먼저 정렬을 수행한 후 조인을 수행

NESTED LOOP JOIN

• 중첩 루프를 사용한 반복문과 유사항 방식으로 수행되는 조인
• 인덱스 스캔을 사용하여 데이터를 랜덤 액세스 방식으로 읽어 들이므로 대량의 데이터에 경우 많은 디스크 I/O가 발생하여 성능이 느려질 수 있음

HASH JOIN

• NESTED LOOP JOIN과 SORT MERGE JOIN의 부담 문제를 개선한 조인
• 조인 칼럼에 해시함수를 적용하여 인덱스가 없을 때 별도로 정렬을 수행하지 않고도 빠르게 조인 대상을 찾는 것이 가능하지만 해시함수의 특성에 따라 EQUI JOIN에서만 사용할 수 있음
• 해시함수를 사용하므로 CPU연산 부하가 높음