[데이터베이스] 5주차 강의 요약

1. 내장함수, NULL, 비고문

SQL 내장함수

• SQL 함수의 분류
  • 내장함수는 DBMS가 제공
  • 사용자 정의 함수: 사용자가 필요에 따라 직접 만듦
• SQL 내장 함수
  • 상수나 속성 이름을 입력값으로 받아 단일 값을 결과로 반환함
  • 모든 내장 함수는 사용될 때 유효한 입력값을 받아야 함
  • SELECT 절과 WHERE 절 , UPDATE 절 등에서 모두 사용 가능

• 숫자 함수
  • SQL 문에서는 수학의 기본적인 사칙 연산자 (+, --, * , 와 나머지 (%) 연산자 기호를 그대로 사용
  • MySQL 은 이러한 연산자 중 사용 빈도가 높은 것을 내장 함수 형태로 제공
• ABS 함수
  • 절댓값을 구하는 함수
  • 다음 질의는 SELECT 문에서 상수 값을 대상으로 ABS 함수를 수행한 결과
• ROUND 함수
  • 반올림한 값을 구하는 함수
  • 다음 질의는 SELECT 문에서 상수 값을 대상으로 ROUND 함수를 수행한 결과
• 숫자 함수의 연산
  • 숫자 함수에는 직접 숫자를 입력할 수도 있지만 열 이름을 사용할 수도 있음
  • 여러 함수를 복합적으로 사용할 수도 있음
  • 다음 질의는 ROUND 함수 , SUM 함수와 COUNT 함수를 사용하여 연산을 수행
• 문자 함수

LENGTH 쓰면 한글은 3바이트로 나옴

• REPALCE 함수 : 문자열을 치환하는 함수
  • 예 ) 담당자의 실수로 도서의 제목을 잘못 입력한 경우 REPLACE 함수를 사용하면 일일이 변경하지 않고 한꺼번에
    변경할 수 있음
• LENGTH, CHAR_LENGTH 함수
  • LENGTH( ) 는 바이트 수를 가져오는 함수
  • CHAR_LENGTH 함수는 문자의 수를 가져오는 함수
• 날짜 · 시간 함수
  • 날짜와 시간 부분을 나타내는 인수는 format ’으로 표기
  • format 은 날짜 형식 지정자로 날짜와 시간 부분을 표기하기 위해 특별한 규칙을 가짐
• 날짜, 시간함수 사용 시 주의할 점
  • DBMS마다 날짜/시간 관련 함수의 이름, 동작 방식, 의미가 다름
  • 타임존(Time Zone) 문제
    • NOW, CURRENT_TIMESTAMP는 DB 서버의 타임존 기준으로 반환됨
    • 서버와 사용자의 위치가 다르면 시간 불일치 발생 가능
    • 필요 시 CONVERT_TZ(MySQL) 같은 함수로 변환하거나 애플리케이션에서 타임존 처리 필요
  • 날짜 포맷 출력
    • DATE_FORMAT(MySQL), TO_CHAR(Postgres) 등을 사용하여 포맷 지정 가능
  • NULL 처리
    • 날짜 컬럼이 NULL이면 함수 적용 시 에러 발생 가능
    • 기본값 설정 습관 필요
    • IFNULL(MySQL), COALESCE(표준 SQL) 사용
  • 성능 고려
    • SELECT에서는 조회용으로 함수 사용 가능
    • WHERE, JOIN, ORDER BY에서는 컬럼에 함수 적용 지양
    • 컬럼에 함수 적용 시 인덱스를 활용하지 못하고 풀 테이블 스캔 가능성 증가
    • 예:
      • 비추천: WHERE DATE(order_date) = '2026-03-28'
      • 권장: WHERE order_date >= '2026-03-28 00:00:00' AND order_date < '2026-03-29 00:00:00'

 

SQL 내장함수 - 날짜 시간

• ADDDATE( date, interval )
  • 지정한 날짜에 일 (day) 또는 시간 (interval 을 더해 새로운 날짜를 반환하는 함수
• format 의 주요 지정자

• STR_TO_DATE 함수 , DATE_FORMAT 함수
  • STR_TO_DATE 함수는 CHAR형 문자열 으로 저장된 날짜를 DATE 형으로 변환함
  • DATE_FORMAT 함수는 STR_TO_DATE 함수와 반대로 날짜형을 문자형으로 변환함

 

행 번호 출력

• MySQL 에서 변수는 이름 앞에 @ 기호를 붙이며 치환문에는 SET 과 := 기호를 사용함

 

CASE WHEN

•  CASE WHEN: 조건에 따라 다른 값을 반환하는 표현식
  • IF THEN ELSE와 유사하며, 집계 함수와 함께 사용하거나 출력 컬럼을 가공할 때 유용함
  • WHEN: 조건을 지정
  • THEN: 조건이 참일 때 반환할 값
  • ELSE: 모든 조건이 거짓일 때 반환할 기본값
  • END: CASE 문 종료

---

2. 부속질의

부속질의

• 부속질의(Subquery): 하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의
• 주로 메인 쿼리의 조건에 따라 서브쿼리 결과를 가져와 메인 쿼리에서 사용하는 방식으로 활용됨
• 다른 테이블의 데이터를 이용해 현재 테이블의 정보를 조회하거나 가공할 수 있음
• 예시: 마당서점 데이터베이스
  • Orders 테이블만으로는 주문 내역은 알 수 있지만, 고객 이름은 확인할 수 없음
  • 고객 이름과 주문 내역을 함께 보려면 Orders 테이블과 Customer 테이블을 연결해야 함
  • 특정 고객(박지성)의 주문 내역을 확인하는 방법
• 조인을 사용할 경우
  • Customer 테이블과 Orders 테이블을 고객번호로 조인한 뒤 필요한 데이터 조회
• 부속질의를 사용할 경우
  • Customer 테이블에서 박지성 고객의 고객번호를 조회
  • 해당 고객번호를 이용해 Orders 테이블에서 주문 내역 조회
• WHERE 부속질의 : 중첩질의, 보통 데이터를 선택하는 조건 혹은 술어와 같이 사용됨

 

스칼라 부속질의: SELECT 부속질의

 

• 스칼라 부속질의(Scalar Subquery): SELECT 절에서 사용되는 부속질의
• 부속질의의 결과를 단일 행, 단일 열의 스칼라 값으로 반환함
• 결과가 다중 행 또는 다중 열일 경우, 어떤 값을 반환해야 할지 결정할 수 없어 에러 발생
• 결과가 없는 경우에는 NULL 값을 반환함

 

• UPDATE 문에서 스칼라 부속질의를 활용하면 다른 테이블의 값을 조회하여 특정 컬럼 값을 갱신할 수 있음

 

CTE(Common Table Expression, WITH)

• WITH CTE명 AS (SELECT …): 공통 테이블 표현식(Common Table Expression)을 정의하는 구문
• 복잡한 SQL 쿼리에서 일시적인 결과 집합(임시 테이블)을 정의하여 가독성을 높이고 쿼리를 구조화할 수 있음
• 메인 쿼리에서 일반 테이블처럼 재사용 가능하며, 재귀 쿼리(Recursive) 구현에도 활용됨
• 쿼리 실행 시에만 존재하며 데이터베이스에 영구적으로 저장되지 않음

 

윈도우함수(Window Functions)

• SQL 윈도우 함수: 테이블의 행과 행 간의 관계를 정의하여 데이터를 윈도우(틀)로 그룹화하여 사용하는 함수
  • 각 행에 대해 집계 결과나 순위 계산 값을 추가하여 사용할 수 있음
  • GROUP BY와 달리 행의 개수를 유지하면서 그룹 내 계산 결과를 각 행에 표시함
  • 복잡한 조인 없이 행 간 계산, 순위 매기기, 누적 합계 등을 수행할 때 유용함
  • OVER() 절과 함께 사용되며, PARTITION BY와 ORDER BY로 범위와 순서를 지정함
• 주요 윈도우 함수 유형
  • 순위 함수 (Ranking): ROW_NUMBER(), RANK(), DENSE_RANK()
  • 집계 함수 (Aggregate): SUM(), AVG(), COUNT(), MAX(), MIN()
  • 값 함수 (Value): LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()