[DB] 4. SQL 고급(1) - 내장함수, NULL, 비교문, 부속질의

1) 내장함수, NULL, 비교문

 

SQL 함수의 종류

내장함수: DBMS가 제공
사용자 정의 함수: 사용자 필요에 따라 직접 만듦

 

MySQL에서 제공하는 주요 내장 함수

구분		함수
단일행 함수	숫자 함수	ABS, CEIL, COS, EXP, FLOOR, LN, LOG, MOD, POWER, RAND, ROUND, SIGN, TRUNCATE
	문자 함수(문자 반환)	CHAR, CONCAT, LEFT, RIGHT, LOWER, UPPER, LPAD, RPAD, PTRIM, RTRIM, REPLACE, REVERSE, RIGHT, SUBSTR, TRIM
	문자 함수(숫자 반환)	ASCII, INSTR, LENGTH
	날짜, 시간 함수	ADDDATE, CURRENT_DATE, DATE, DATEDIFF, DAYNAME, LAST_DAY, SYSDATE, TIME
	변환 함수	CAST, CONVERT, DATE_FORMAT, STR_TO_DATE
	정보 함수	DATABASE, SCHEMA, ROW_COUNR, USER, VERSION
	NULL 관련 함수	COALESCE, ISNULL, IFNULL, NULLIF
집계 함수		AVG, COUNT, MAX, MIN, STD, STDDEV, SUM
윈도 함수(or 분석 함수)		CUME_DIST, DENSE_RANK, RIST_CALUE, LAST_CALUE, LEAD, NTILE, RANK, ROW_NUMBER

 

• 윈도 함수 = "행 간 비교와 누적 계산 도구"로 사용되는 고급 문법
• GROUP BY와 윈도우 함수는 같이 사용 X

 

숫자 함수

 

• ABS(숫자): 절댓값
• CEIL(숫자): 숫자보다 크거나 같은 최소의 정수 (↔ FLOOR(): 작거나 같은)
• ROUND(숫자, m): 숫자의 반올림 (m은 반올림 기준 자릿수)
• SQRT(숫자):  숫자의 제곱근 값을 계산(숫자는 양수)
• SIGN(숫자): 숫자가 음수 ➔ -1 / 양수 ➔ 1 반환

/* ==  ROUND() 예시 == */

-- 소수 첫째 자리까지 반올림한 값을 구하시오.
SELECT @VALUE := 1.879; -- 변수 선언
SELECT REOUND(@VALUE, 1);


-- 100원 단위로 반올림한 값을 구하시오.
SELECT custid '고객번호', ROUND(SUM(saleprice)/COUNT(*), -2) '평균금액'
FROM Orders
GROUP BY custid;

 

문자 함수  

 

• CONCAT(s1, s2): 두 문자열 연결
• LPAD(s,n,c): 대상 문자열(s)의 왼쪽부터 지정한 자릿수(n)까지 지정한 문자(c)로 채움. (↔ RPAD(s,n,c))

LPAD('Page 1', 10, '*') -- 10자리까지 *로 채워라
-- -> '****Page1'

 

• SUMSTR(s,n,k): 대상 문자열(s)의 지정된 자리(n)에서부터 지정된 길이(n)만큼 잘라서 반환

SUBSTR('ABCDEF', 3, 4) -- 3자리부터 4개 
-- -> 'CEDF'

 

• TRIM(c FROM s): 대상 문자열(s)의 양쪽에서 지정된 문자(c)를 삭제함

-- 문자열만 넣으면 -> (default) 공백 제거
TRIM('=' FROM '==BROWNING==')
-- -> 'BROWNING'

 

• LOWER(s): 대상 문자열 전부 소문자 변환 (↔ UPPER(s))
• LENGTH(s): 대상 문자열의 byte 반환

한글: 3byte (UTF-8) / 알파벳: 1byte

 

• CHAR_LENGTH(s): 문자열의 문자 수 반환
• REPLACE( 열명, '치환대상값', '치환되길 원하는 값'): 문자 치환

SELECT bookid, REPLACE(bookname, '야구', '농구') bookname, publisher, price
FROM book;

-- bookname 열에 '야구'값을 -> '농구'값으로 치환한다.

 

날짜, 시간 함수

 

• 인수를 format으로 표기한 경우
  (DBMS마다 함수 이름과 동작이 다르다. 아래 표는 MariaDB 기준 작성됨.)

함수	반환형	설명
STR_TO_DATE(string, format)	DATE	문자열 데이터 ➔ 날짜형 반환 STR_TO_DATE('2024-12-07', '%Y-%m-%d') -- -> 2024-12-07​
DATE_FORMAT(date, format)	STRING	날짜형 ➔ 문자열(VARCHAR) 반환 DATE_FORMAT('2024-12-07', '%Y-%m-%d') -- -> '2024-12-07'​
ADDDATE(date, interval)	DATE	DATE형의 날짜 ➔ INTERVAL 지정한 시간만큼 더함 ADDDATE('2024-12-07', INTERVAL 10 DAY) -- -> 2024-12-17
DATEDIFF(date1, date2)	INTEGER	DATE형의 date1 - date2 날짜 차이 반환
DATE(date)	DATE	DATE형의 날짜 부분 반환 (time x)
SYSDATE	DATE	DBMS 시스템상의 오늘 날짜를 반환

 

[!] 주의할 점
1. NOW() vs. SYSDATE()
- NOW(): 동일한 값 유지
- SYSDATE(): 호출시점마다 다름

2. NULL 처리
- 날짜 컬럼이 NULL ➔ 에러 발생
➔ 기본값 지정하자!

 

• format의 주요 지정자

요일	%w	요일 순서(0~6, Sunday=0)	날짜	%d	한 달 중 날짜(00~31)
	%W	요일(Sunday~Saturdaty)		%j	1년 중 날짜(001~366)
	%a	요일의 약자(Sun~Sat)	월	%m	월 순서(01~12, January=01)
시간	%h	12시간(01~12)		%b	월 이름 약어(Jan~Dec)
	%H	24시간(00~23)		%M	월 이름(January~December)
	%i	분(0~59)	연도	%Y	4자리 연도
	%s	초(0~59)		%y	4자리 연도의 마지막 2자리

 

-- DBMS 서버에 설정된 현재 날짜와 시간, 요일을 확인하시오.

SELECT SYSDATE(),
	DATE_FORMAT(SYSDATE(), '%Y/%m/%d %a %h:%i') 'SYSDATE_1';
    
-- -> 2026/04/03 Fri 15:53

 

NULL 값

 

• 특징
  • 비교연산자(=, <, >)로 비교할 수 없다.
  • 연산 결과도 다 NULL로 반환한다.

집계 함수를 계산할 때, NULL이 포함된 행은 집계에서 빠진다.

 

• IFNULL 함수 (MySQL, MariaDB)

IFNULL (속성, 값)
-- 속성값이 NULL이면 '값'으로 대치한다.

 

• COALESCE 함수 (표준 SQL)

COALESCE(expr1, expr2, expr3, ... )
-- 첫 번째로 NULL이 아닌 값을 반환

 

+ 변수 설정하기

-- 변수 앞에 @ / 치환문에는 SET과 := 기호를 사용한다.

SET @seq:=0;

SELECT (@seq:=seq+1) '순번', custid, name, phone
FROM Customer
WHERE @seq < 2;

 

CASE WHEN: 조건문

CASE
	WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
   	...
    ELSE 기본값
END AS 별칭

 

 

---

2) 부속질의

 

부속질의

: 하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의

[!] 실제 실무에서는: 가독성, 클립코드 << 응답시간!

 

+ EXPLAIN

• Rows: 쿼리가 훑고 지나간 행의 수
• Filtered: 실제 결과로 남은 데이터의 비율
• Extra: Using index or Using filesort

 

WHERE 부속질의(= 중첩질의)

• 중첩질의 연산자:
  • 비교(=, >, <, >=, <=, <>) ➔ 반환 행/열: 모두 단일
  • 집합(IN, NOT IN) ➔ 반환 행/열: 모두 다중
  • 한정(ALL, SOME (ANY))
  • 존재(EXISTS, NOT EXISTS)

-- 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 판매금액을 보이시오.

SELECT orderid, saleprice
FROM Orders
WHERE saleprice > ALL (SELECT saleprice
			FROM Orders
    			WHERE custid='3');

 

SELECT 부속질의(= 스칼라 부속질의)

• 특징
  • 부속질의의 결과 값 ➔ 단일 행, 단일 열의 스칼라 값으로 반환
    if 결과 값이 다중 행/열 ➔ ERROR
    if 결과 x ➔ NULL 
  • UPDATE 문에서도 사용 가능하다.

/* == 1. SELECT문 예시 == */

SELECT custid, (SELECT name
		FROM Customer cs
        	WHERE cs.custid=od.custid), SUM(saleprice)
FROM Orders od
GROUP BY custid;

-- 실행 순서
-- 1) FROM -> 2) GROUP BU -> 3) 부속질의 -> 4) 주질의

/* == 2. UPDATE문 예시 ==*/

UPDATE Orders
SET bname = (SELECT bookname
		FROM Book
        	WHERE Book.bookid=Orders.bookid);

 

FROM 부속질의(= 스칼라 부속질의)

➔ '뷰' : 기존 테이블로부터 일시적으로 만들어진 가상테이블

SELECT cs.name, SUM(od.saleprice) 'total'
FROM (SELECT custid, name
	FROM Customer
        WHERE custid <= 2) cs,
       Orders od
WHERE cs.custid = od.custid
GROUP BY cs.name;

 

WITH CTE명 AS (SELECT ...)

: 복잡한 SQL 쿼리 내에서 일시적인 결과 집합 정의 ➔ 가독성 상승, 쿼리 구조화

/* == CTE 예시 == */

-- 마당서점의 고객별 판매액을 나타내시오(고객이름, 고객별 판매액 출력)

WITH sales_summary AS (
	SELECT od.custid, cs.name,
	SUM(od.saleprice) AS total_sales
   	FROM orders od
    	JOIN customer cs
    		ON od.custid = cs.custid
    	GROUP BY od.custid, cs.name
) -- [!] 세미콜론 없음


-- 매출별로 내림차순하여 보기

SELECT custid, name, total_sales
FROM sales_summary -- CTE 사용
ORDER BY total_sales DESC;

 

SQL 윈도우 함수

: 테이블의 행-행 간의 관계를 정의 -> 데이터를 윈도유(틀)로 그룹화하여 사용하는 함수

• 특징
  • 행의 개수 유지 + 그룹 내 계산 결과를 각 행에 표시한다. (↔ GROUP BY: 행이 합쳐진다.)
  • OVER() 절과 함께 사용, PARTITION BY/ORDER BY로 범위/순서 지정 

-- OVER() 절

SELECT 함수명() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명)
FROM 테이블명;

-- PARTITION BY: 계산을 수행할 그룹을 나눈다.
-- ORDER BY: 그 그룹 안에서 계산을 수행할 순서를 정한다.

예시)
SELECT ~,
	RANK() OVER (PARTITION BY b.publisher ORDER BY SUM(o.saleprice) DESC) AS rank_i

 

• 주요 유형
  • 순위 함수(Ranking)
    ➔ ROW_NUMBER(), RANK(), DENSE_RANK(), ...
  • 집계 함수(Aggregate)
    ➔ SUM(), AVG(), COUNT(), MAX(), MIN(), ...
  • 분석/값 함수(Value)
    ➔ LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), ...

 

 

[목차여기]