SQL 고급

SQL 고급 문법을 이용하면 기초 문법을 사용할 때보다 더 다양하고 고도화된 고급 기능을 구현할 수 있다.

내장함수

SQL에서 내장함수는 상수 또는 속성 이름을 입력받아 단일 값을 출력한다. MySQL에는 다양한 기능을 제공하는 내장함수가 있다. MySQL 8.4 Built-In Function and Operator Reference

문자열 함수

함수	설명	예시
`CONCAT(s1, s2, ...)`	여러 문자열을 하나로 연결	`SELECT CONCAT('Hello', ' ', 'World');` -> 'Hello World'
`LENGTH(s)`	문자열의 바이트 길이를 반환	`SELECT LENGTH('SQL');` -> 3
`CHAR_LENGTH(s)`	문자열의 문자 개수를 반환 (멀티바이트 문자열에 유용)	`SELECT CHAR_LENGTH('MySQL');` -> 5
`UPPER(s)` / `UCASE(s)`	문자열을 모두 대문자로 변환	`SELECT UPPER('data');` -> 'DATA'
`LOWER(s)` / `LCASE(s)`	문자열을 모두 소문자로 변환	`SELECT LOWER('DATA');` -> 'data'
`SUBSTRING(s, start, len)`	문자열의 특정 부분(부분 문자열)을 반환	`SELECT SUBSTRING('Database', 1, 4);` -> 'Data'
`TRIM(s)`	문자열 양쪽 끝의 공백을 제거 (`LTRIM`, `RTRIM`도 있음)	`SELECT TRIM(' SQL ');` -> 'SQL'
`REPLACE(s, from, to)`	문자열 내의 특정 문자열을 다른 문자열로 대체	`SELECT REPLACE('Hello', 'l', 'x');` -> 'Hexxo'

숫자 함수

함수	설명	예시
`ABS(x)`	숫자의 절댓값을 반환	`SELECT ABS(-10);` -> 10
`ROUND(x, d)`	숫자를 소수점 이하 `d` 자리에서 반올림	`SELECT ROUND(3.14159, 2);` -> 3.14
`CEIL(x)` / `CEILING(x)`	숫자보다 크거나 같은 가장 작은 정수(올림)를 반환	`SELECT CEIL(3.14);` -> 4
`FLOOR(x)`	숫자보다 작거나 같은 가장 큰 정수(내림)를 반환	`SELECT FLOOR(3.99);` -> 3
`MOD(N, M)`	`N`을 `M`으로 나눈 나머지 값을 반환	`SELECT MOD(10, 3);` -> 1
`POW(x, y)` / `POWER(x, y)`	`x`의 `y` 제곱 값을 반환	`SELECT POW(2, 3);` -> 8

날짜 및 시간 함수

날짜와 시간 데이터를 처리하고 계산하는 데 사용

함수	설명	예시
`NOW()` / `SYSDATE()`	현재 날짜와 시간(`YYYY-MM-DD HH:MM:SS`)을 반환	`SELECT NOW();`
`CURDATE()`	현재 날짜(`YYYY-MM-DD`)만 반환	`SELECT CURDATE();`
`DATE_FORMAT(date, format)`	날짜/시간을 지정된 형식으로 포맷팅	`SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일');`
**`DATEDIFF(date1, date2)`	두 날짜 간의 일(Day) 수를 반환	`SELECT DATEDIFF('2025-12-31', '2025-12-01');` -> 30
`DATE_ADD(date, INTERVAL value unit)`	날짜에 지정된 간격(Interval)을 추가	`SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);`
`YEAR(date)` / `MONTH(date)` / `DAY(date)`	날짜에서 연도, 월, 일 부분을 추출	`SELECT YEAR(CURDATE());`

흐름 제어 함수

쿼리 내에서 조건부 논리(Conditional Logic)를 적용할 때 사용

함수	설명	예시
`IF(condition, true_val, false_val)`	조건이 참(True)이면 `true_val`, 거짓(False)이면 `false_val`을 반환	`SELECT IF(price > 10000, '비쌈', '저렴');`
`CASE`	여러 조건을 순차적으로 검사하여 조건에 맞는 값을 반환. 복잡한 조건 처리에 유용	`CASE WHEN score >= 90 THEN 'A' ELSE 'B' END`
`IFNULL(expr1, expr2)`	`expr1`이 `NULL`이 아니면 `expr1`을 반환하고, `NULL`이면 `expr2`를 반환.	`SELECT IFNULL(commission, 0);`
`NULLIF(expr1, expr2)`	`expr1`과 `expr2`가 같으면 `NULL`을 반환하고, 그렇지 않으면 `expr1`을 반환.	`SELECT NULLIF(a, b);`

집계 함수

여러 행의 데이터를 그룹으로 묶어 단일 요약 값을 계산 (GROUP BY 절과 함께 사용)

함수	설명	예시
`COUNT(*)` / `COUNT(column)`	행의 개수를 계산 (컬럼을 지정하면 NULL이 아닌 값만 셈)	`SELECT COUNT(*)`
`SUM(column)`	특정 컬럼 값의 합계를 계산	`SELECT SUM(saleprice)`
`AVG(column)`	특정 컬럼 값의 평균을 계산	`SELECT AVG(saleprice)`
`MAX(column)` / `MIN(column)`**	컬럼에서 최댓값 또는 최솟값을 반환	`SELECT MAX(price)`
`GROUP_CONCAT(...)`	그룹 내의 문자열을 지정된 구분 기호로 연결	`SELECT GROUP_CONCAT(name SEPARATOR ', ');`

부속질의

주질의 안에 독립된 다른 쿼리를 괄호로 묶어 작성하고, 그 실행 결과를 주질의에 사용하는 문법이다. 부속질의의 결과는 하나의 값일수도, 집합일수도, 테이블일수도 있다.

부속질의는 어느 절에 들어가냐에 따라 세 종류로 나뉜다.

WHERE 부속질의(중첩질의)
SELECT 부속질의(스칼라 부속질의)
FROM 부속질의(인라인 뷰)

WHERE 부속질의

주질의에 사용된 자료 집합의 조건을 WHERE 절에 서술한다. 이 부속질의에 사용할 수 있는 조건 연산자는 다음과 같은 것이 있다.

술어	연산자	반환 행	반환 열	상관	기타
비교	=,<,>,>=,<=,<>	단일	단일	가능	크기 비교
집합	IN, NOT IN	다중	다중	가능	포함, 미포함
한정	ALL, SOME	다중	단일	가능	비교연산과 함께 사용 -> 모든 행의 값들보다 큰지 작은지(ALL), 아니면 최소 하나의 값보다 큰지 작은지(SOME)
존재	EXIST, NOT EXIST	다중	다중	필수	주질의 속성이 부속질의에서 조건이 만족하면 참

상관 여부

상관이란 부속질의의 결과가 주질의의 값에 의존하는지에 대한 여부이다.

SELECT 부속질의

SELECT 또는 UPDATE SET 절에 사용되는 부속질의로 단일 행, 단일 열의 스칼라 값(하나의 값)으로 반환한다.

SELECT a, (SELECT b FROM R WHERE pk=1), c
FROM ...

FROM 부속질의 (인라인 뷰)

FROM 절에 사용하는 부속 질의로, 앞의 두 부속질의와 달리 가상의 테이블로 취급된다. 따라서 쿼리문 괄호 밖에 AS t1 처럼 테이블 이름을 붙여야 구문 오류가 안난다. 대부분의 경우 조인 시 한쪽 테이블에 조건을 걸어주기 위해 사용된다.

아래 예제는 품절된 메뉴가 주문된 주문들을 찾는 쿼리이다.

SELECT od.menuid, so.menuname
FROM (
  SELECT *
  FROM Menu
  WHERE status="soldout"
) AS so, Orders od
WHERE so.menuid=od.menuid;

뷰

뷰는 임시로 생성하는 가상 테이블로, 자주 필요한 임시 테이블을 매번 조인이나 부속질의를 사용하면 번거롭기도 하고 성능 저하를 초래할 수 있어 사용한다.

뷰 생성

부속질의에서 SELECT문을 괄호로 묶어 사용했다면, 뷰는 이름을 정해 만들어두고 여러번 접근할 수 있는 형태이다.

CREATE VIEW viewname(colname...);
AS SELECT ...

뷰 수정

수정은 구문에 OR REPLACE를 붙여 수행한다.

CREATE VIEW OR REPLACE viewname(colname...);
AS SELECT

뷰 삭제

DROP VIEW viewname;

인덱스

데이터베이스 개념에서 인덱스는 데이터를 찾을 때 성능을 높이기 위해, 튜플의 키 값에 대한 물리적 위치를 기록해 둔 자료 구조를 말한다.

B-tree

B트리는 이진 탐색 원리에 기반한 자동 정렬 균형 트리이다.

루트 노드, 내부 노드, 리프 노드로 구성된다. 각 노드는 키 값과 포인터를 가진다. 키 값은 오름차순 정렬 기준이 되고, 포인터는 양쪽 노드의 주소이다. 이진 탐색 트리는 각 노드가 하나의 값을 가질 수 있지만, B트리는 각 노드가 하나의 배열이라서 여러 값을 가질 수 있다. 삽입/삭제 시 노드의 분할 및 통합이 이루어지는 원리로 트리의 균형을 유지한다.

인덱스 특징

인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성한다.
빠른 검색, 효율적인 레코드 접근이 가능하다.
순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 적은 공간을 차지한다.
저장된 값들은 테이블의 부분집합이 된다.
B-tree 형태를 가진다.
데이터에서 수정, 삭제등의 변경이 발생하면 인덱스를 재구성해야 한다.

클러스터 인덱스

연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법이다. 쉽게 말해 테이블 자체를 여러 페이지로 쪼개 B-tree로 저장하는 것이다. 즉 각각의 리프 노드들이 정렬된 상태로 저장된 테이블 자체가 된다.

이 클러스터 인덱스는 테이블 생성 시 기본키를 지정하면 자동으로 생성된다.

논-클러스터 인덱스 (보조 인덱스)

데이터베이스가 실무에 사용되면 데이터 갱신이 발생하면서 정렬 상태가 깨지게 된다. 이를 해결하기 위해 B-tree의 리프 노드엔 pk값이 오는게 아닌 Block-Raw 번호 조합과 같은 데이터 위치 정보를 기록하여 순서와 상관 없이 원하는 데이터를 빠르게 찾을 수 있다.

MySQL 인덱스

MySQL은 이 두가지 인덱스를 조합해 사용한다. PK 외에 자주 검색하는 속성을 논-클러스터 인덱스로 생성하고 해당 튜플의 PK를 저장한다. 그리고 해당 인덱스에서 찾은 PK로 클러스터 인덱스에 접근해 실제 튜플을 찾는다. 논-클러스터 인덱스가 데이터의 물리적 위치가 아닌 논리적 위치인 PK를 저장하므로 관리 효율이 증가한다.

인덱스 생성

즉 MySQL 문법에서 인덱스를 생성한다는 것은 테이블의 어떠한 속성으로 보조 인덱스를 구성한다는 의미이다. 인덱스를 생성할 땐 다음과 같은 사항들을 고려할수록 좋다.

WHERE 절에서 자주 사용되는가
조인에 자주 사용되는가
단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.
속성이 가공되는 경우에는 X
속성의 모든 값들이 다를수록 유리

CREATE INDEX indexname ON tablename(colname...);

인덱스 보기

SHOW INDEX FROM tablename;

인덱스 재구성, 삭제

삭제가 발생하면 레코드의 인덱스 값 자리가 비게 되는데, 이를 단편화라고 한다. 성능 저하를 초래하므로 초기화를 해주어야 한다.

ANALYZE TABLE tablename;

사용하지 않는 인덱스는 삭제해야 한다.

DROP INDEX indexname ON tablename;