Courses/데이터베이스

데이터베이스 개론과 실습 (MySQL로 배우는) | Chapter 04: SQL 고급

noweahct 2025. 1. 16. 11:04

01. 내장 함수

SQL 내장 함수

  • 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환한다
  • 모든 내장함수는 최초에 선언될 때 유효한 입력 값을 받아야 한다
  • 선언에 위배된 값이 입력되면 질의는 실행을 중지하고 에러 메시지를 출력한다
  • MYSQL에서 제공하는 내장 함수

 

구분    함수
단일행 함수 숫자 함수 ABS, CEIL, COS, EXP, FLOOR, LN, LOG, MOD, POWER, RAND, ROUND, SIGN, TRUNCATE
문자 함수(문자 반환) CHAR, CONCAT, LEFT, RIGHT, LOWER, UPPER, LPAD, RPAD, LTRIM, RIRIM, REPLACE, REVERSE, RIGHT, SUBSTR, TRIM
문자 함수(숫자 반환) ASCI, 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
윈도우 함수(혹은 분석 함수)    

숫자 함수

 

함수  설명  예시
ABS(숫자) 숫자의 절댓값을 계산 ABS(-4.5) → 4.5
CEIL(숫자) 숫자보다 크거나 같은 최소의 정수 CEIL(4.1) → 5
FLOOR(숫자) 숫자보다 작거나 같은 최소의 정수 FLOOR(4.1) → 4
ROUND(숫자, m) 숫자의 반올림, m은 반올림 기준 자릿수 ROUND(5.36, 1) → 5.40
LOG(n, 숫자) 숫자의 자연로그 값을 반환 LOG(10) → 2.30259
POWER(숫자, n) 숫자의 n제곱 값을 계산 POWER(2,3) → 8
SQRT(숫자) 숫자의 제곱근 값을 계산(숫자는 양수) SORT(9.0) → 3.0
SIGN(숫자) 숫자가 음수면 -1, 0이면 0, 양수면 1 SIGN(3.46) → 1

 

  • 문자 함수: CHAR나 VARCHAR의 데이터 타입을 대상으로 단일 문자나 문자열을 가공한 결과를 반환
    반환 구분 함수 함수 설명 예시
    문자값 반환 함수(s: 문자열, c: 문자, n: 정수, k: 정수) CONCAT(s1, s2) 두 문자열을 연결 CONCAT (’마당’, ‘서점') → ‘마당 서점’
    LOWER(s) 대상 문자열을 모두 소문자로 변환 LOWER(’MR.SCOTTY’) → ‘mr. scott’
    LPAD(s, n, c) 대상 문자열의 왼쪽부터 지정한 자리수까지 지정한 문자로 채움 LPAD('Page 1', 10, ‘*’) → ‘****Page 1’
    REPLACE(s1, s2, s3) 대상 문자열의 지정한 문자를 원하는 문자로 변경 REPLACE(’JACK & JUE’, ‘J’, ‘BL') → ‘BLACK & BLUE’
    RPAD(s, n, c) 대상 문자열의 오른쪽부터 지정한 자리수까지 지정한 문자로 채움 RPAD(’AbC’, 5, ‘*') → ‘AbC**
    SUBSTR(s, n, k) 대상 문자열의 지정된 자리에서부터 지정된 길이 만큼 잘라서 반환 SUBSTR(’ABODEFG’, 3.4) → 'CDEF’
    TRIM(c FROM s) 대상 문자열의 양쪽에서 지정된 문자를 삭제 (문자열만 넣으면 기본값으로 공백 제거) TRIM(’=’ FROM ‘== BROWNING ==’) → ‘BROWNING’
    UPPER(s) 대상 문자열을 모두 대문자로 변환 UPPER(’mr. soott’) → ‘MR. SCOTT’
    숫자값 반환 함수 ASCII(c) 대상 알파벳 문자의 아스키 코드 값을 반환 ASCII(’D’) → 68
    LENGTH(s) 대상 문자열의 Byte 반환, 알파벳 1byte, 한글 3byte (UTF8) LENGTH(’CANDIDE’) → 7
    CHAR_LENGTH(s) 문자열의 문자 수를 반환 CHAR_LENGTH(’데이터’) → 3

날짜, 시간 함수

  • 날짜를 문자열로 저장하는 것보다 날짜형 데이터로 저장하여 관리하면, 날짜의 연산을 쉽게 처리할 수 있다
  • 날짜, 시간 함수는 날짜형(DATE) 데이터를 가진 열을 대상으로 연산을 수행한다
  • 문자형(CHAR, VARCHAR) 데이터와 날짜형 데이터 간 연산을 수행할 때는 DATE_FORMAT, STR_TO_DATE 함수를 사용해 데이터형을 상호변환하여 수행한다
  • 종류함수 반환형 설명 예시

 

함수  반환형  설명  예시
STR_TO_DATE(string, format) DATE 문자열(STRING) 데이터를 날짜형으로 반환 STR_TO_DATE(’2019-02-14', %Y-%m-%d’) → 2019-02-14
DATE_FORMAT(date, format) STRING 날짜형 데이터를 문자열(VARCHAR)로 반환 DATE FORMAT (’2019-02-14’, ‘%Y-%m-%d’) → ‘2019-02-14’
ADDDATE(date, interval) DATE DATE 형의 날짜에서 INTERVAL 지정한 시간만큼 더함 ADDDATE(’2019-02-14’, INTERVAL 10 DAY) → 2019-02-24
DATE(date) DATE DATE 형의 날짜 부분을 반환 DATE(’2003-12-31 01:02:03’); → 2003-12-31
DATEDIFF(date1, date2) INTEGER DATE 형의 date1 - date2 날짜 차이를 반환 DATEDIFF (’2019-02-14’, ‘2019-02-04’) → 10
SYSDATE DATE DBMS 시스템상의 오늘 날짜를 반환하는 함수 SYSDATE() → 2019-06-30 21:47:01
  • 날짜형 데이터는 ‘-’와 ‘+’를 사용해 원하는 날짜로부터 이전(-)과 이후(+)를 계산할 수 있다!
  • format의 주요 지정자(specifier)인자 설명
인자  설명
%w 요일 순서(0~6, Sunday=0)
%W 요일(Sunday~Saturday)
%a 요일의 약자(Sun~Sat)
%d 1달 중 날짜(00~31)
%j 1년 중 날짜(001~366)
%h 12시간(01~12)
%H 24시간(00~23)
%i 분(0~59)
%m 월 순서(01~12, January=01)
%b 월 이름 약어(Jan~Dec)
%M 월 이름(January~December)
%s 초(00~59)
%Y 4자리 연도
%y 4자리 연도의 마지막 2자리
  • format 사용 예시
SELECT SYSDATE(), DATE_FORMAT(SYSDATE(), '%Y%m%d:%H%i%s');
# '20190707:142014'
# format 사이에 넣고 싶은 기호를 추가해서, 출력되는 형식을 별도로 지정할 수 있다!!!

NULL 값 처리

  • NULL 값: 아직 지정되지 않은 값
    • 0, 빈문자, 공백 등과 다른 특별한 값이다
    • 비교 연산자로 비교가 불가능하다
    • NULL 값의 연산을 수행하면 결과도 NULL 값이다
  • NULL 값에 대한 연산과 집계 함수
    • ‘NULL+숫자’의 연산 결과는 NULL이다
    • 집계 함수를 계산할 때 NULL이 포함된 행은 집계에서 빠진다
    • 해당되는 행이 하나도 없을 경우, SUM, AVG 함수의 결과는 NULL, COUNT 함수의 결과는 0이 된다
  • IS NULL(=), IS NOT NULL(< >): NULL 값과 NULL이 아닌 값을 찾을 수 있다
  • IFNULL: NULL 값을 다른 값으로 대치하여 연산하거나, 다른 값으로 출력하는(NULL 값을 임의의 다른 값으로 변경하는) 함수
IFNULL(속성, 값) /* 속성 값이 NULL이면 '값'으로 대치한다.*/

행번호 출력

  • MYSQL에서는 변수를 사용해 SQL 문 결과로 나오는 행에 번호를 붙이거나 행번호에 따라 결과의 개수를 조절할 수 있다
    • 변수는 이름 앞에 @ 기호를 붙이고, 치환문에는 SET과 := 기호를 사용한다
  • 예시
# 고객 목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오.
    
SET @seq:=0;
SELECT (@seq:=@seq+1) '순번', custid, name, phone
FROM Customer
WHERE @seq < 2;

02. 부속질의

  • 부속질의: 하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의
    • 다른 테이블에서 가져온 데티터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용
  • 조인과 부속질의의 비교
    • 조인을 사용할 경우: Customer 테이블과 Orders 테이블의 고객 번호로 조인한 후 필요한 데이터를 추출
    • 부속질의를 사용할 경우: Customer 테이블에서 박지성 고객의 고객번호를 찾고, 찾은 고객번호를 바탕으로 Orders 테이블에서 확인
    → 데이터의 형태에 따라 조인 혹은 부속질의를 선택한다
  • : 데이터가 대량일 경우는 필요한 데이터만 찾아서 공급해주는 부속질의의 성능이 더 좋다!
  • 부속질의의 구성: 주질의(main query, =외부질의) + 부속질의(subquery, =내부질의)
  • 부속질의의 구분: 위치와 역할에 따라 구분명칭 위치 영문 및 동의어 설명

  • 보통 부속질의라고 하면 WHERE 부속질의를 말한다
  • 동작하는 방식에 따라 상관 부속질의와 비상관 부속질의(일반 부속질의), 반환하는 결과의 형태에 따라 단일행 부속질의와 다중행 부속질의로 구분하기도 한다

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

  • 결과 값이 다중 행이거나 다중 열이면 에러를 출력한다
  • 결과가 없는 경우에는 NULL 값을 출력한다
  • 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용할 수 있다
  • 일반적으로 SELECT 문과 UPDATE SET 절에 사용된다
  • 주질의와 부속질의의 관계는 상관/비상관 모두 가능하다

인라인 뷰 - FROM 부속질의

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

  • FROM 절에서 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있다
  • 부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없다
  • 뷰 형태로 제공되기 때문에 상관 부속질의로는 사용될 수 없다
  • *** 상관 부속질의: 주질의의 특정 컬럼 값을 부속질의가 상속받아 사용하는 형태

중첩질의 - WHERE 부속질의

  • (WHERE 절은 보통 데이터를 선택하는 조건 또는 술어(predicate)와 같이 사용되기 때문에) 중첩질의를 술어 부속질의라고도 부른다
  • 주질의에 사용된 자료 집합의 조건을 WHERE 절에 서술한다
  • 주질의의 자료 집합에서 한 행씩 가져와 부속질의를 수행하고 연산 결과에 따라 WHERE 절의 조건이 참인지 거짓인지 확인하여 참일 경우 주질의의 해당 행을 출력한다
  • 중첩질의 연산자 종류술어 연산자 반환 행 반환 열 상관 
    술어 연산자 반환 행 반환 열 상관
    비교  =, >, <. >=, <=, <> 단일 단일 가능
    집합 IN, NOT IN 다중 다중 가능
    한정 ALL, SOME(ANY) 다중 단일 가능
    존재 EXISTS, NOT EXISTS 다중 다중 필수
  • IN, NOT IN
    • IN 연산자: 주질의의 속성 값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할
    • 주질의는 WHERE 절에 사용되는 속성 값을 부속질의의 결과 집합과 비교해 하나라도 있으면 참이 된다
    • NOT IN은 값이 존재하지 않으면 참이 된다
  • ALL, SOME(ANY)
    • 비교 연산자와 함께 사용한다
    • ALL(모든), SOME(어떠한, 최소한 하나라도)
    • ANY는 SOME과 동일한 기능을 하지만 SOME은 ISO 표준 연산자이다
    • 구조:
      • 비교 연산자의 왼쪽에는 스칼라 값이나 열 이름 등이 위치하고 오른쪽에는 부속질의문이 위치한다
      • ALL이나 SOME은 부속질의의 대상 범위를 지정하는 역할을 한다
      scalar_expression { 비교 연산자 (=, <>, !=. >, !>, <, <=, !<) }
				{ ALL | SOME | ANY } (부속질의)
  • EXISTS, NOT EXISTS
    • 데이터의 존재 유무를 확인하는 연산자
    • 주질의에서 부속질의로 제공된 속성의 값을 가지고 부속질의에 조건을 만족하여 값이 존재하면 참이 되고, 주질의는 해당 행의 데이터를 출력한다(NOT EXISTS는 이와 반대로 동작)
    • 구조
WHERE [NOT] EXISTS (부속질의)

03. 뷰

뷰(view)

  • 하나 이상의 테이블을 합하여 만든 가상의 테이블로, 실제 테이블처럼 사용할 수 있도록 만든 데이터베이스 개체
  • 장점
    1. 편리성 및 재사용성
    2. 보안성
    3. 독립성
  • SELECT 문을 제외한 일부 물리적인 테이블의 갱신작업을 수행하는 데 제약이 있다(INSERT, UPDATE, DELETE 등의 DML 작업은 경우에 따라 수행되지 않는다)

뷰의 생성

CREATE VIEW 뷰이름 [(열이름 [,... n])]
AS SELECT 문

# 뷰이름: 생성할 뷰의 이름
# 열이름: 뷰에서 사용할 열의 이름
# 열 이름과 SELECT 문에서 추출하는 속성은 일대일로 대응된다!

뷰의 수정

CREATE OR REPLACE VIEW 뷰이름 [(열이름 [,... n])]
AS SELECT 문

뷰의 삭제

DROP VIEW 뷰이름 [(열이름 [,... n])];

04. 인덱스

데이터베이스의 물리적 저장

  • 데이터가 실제로 저장되는 곳은 보조기억장치, 그 중에서도 대부분 하드디스크이고, 하드디스크는 구조적인 요인에 의해 데이터를 읽고 저장할 때 속도 문제가 발생한다
  • 이러한 속도 문제를 줄이기 위해 주기억장치에 DBMS가 사용하는 공간 중 일부를 버퍼 풀(Buffer Pool Memory)로 만들어 사용하는 방법이 있다
  • DB는 버퍼에 자주 사용하는 데이터를 저장해두며 LRU(Least Recently Used) 알고리즘을 이용하여 사용빈도가 높은 데이터 위주로 저장하고 관리한다
  • DBMS는 데이터베이스별로 하나 이상의 데이터 파일을 생성한다
  • 테이블은 생성 시 정의된 내용에 따라 논리적으로 구분지어 각각의 데이터 파일에 저장한다
  • MYSQL의 저장장치 엔진은 플러그인 방식으로 선택할 수 있고 InnoDB 엔진이 기본으로 설치되어 있다
  • MYSQL InnoDb 엔진 데이터베이스의 파일
    파일 설명
    데이터 파일(ibdata) 사용자 데이터와 개체를 저장
    - 테이블과 인덱스로 구성
    - 확장자는 *.ibd
    폼파일(frm File) - 테이블에 대한 각종 정보와 테이블을 구성하는 필드, 데이터 타입에 대한 정보 저장
    - 데이터베이스 구조 등의 변경사항이 있을 때 자동으로 업데이트됨

인덱스와 B-tree

  • 인덱스
    • 자료를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조
    • 데이터베이스에서는 원하는 데이터를 빨리 찾기 위해 투플의 키 값에 대한 물리적 위치를 기록해둔 자료구조
    • 특징
      1. 테이블에서 한 개 이상의 속성을 이용하여 생성한다
      2. 빠른 검색과 함께 효율적인 레코드 접근이 가능하다
      3. 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지한다
      4. 저장된 값들은 테이블의 부분집합이 된다
      5. 일반적으로 B-tree 형태의 구조를 가진다
      6. 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요하다
  • B-tree
    • 데이터의 검색 시간을 단축하기 위한 자료구조
    • 루트 노드, 내부 노드, 리프 노드로 구성되며, 리프 노드가 모두 같은 레벨에 존재하는 균형 트리
    • 검색할 때 루트 노드에서부터 값을 비교하여 중간 단계인 내부 노드에서 해당 노드를 찾고, 이런 단계를 거쳐 최종적으로 마지막 레벨인 리프 노드에 도달한다. 리프 노드에는 해당 데이터의 저장 위치에 대응하는 rowid(RID, Row Identify, 테이블의 행에 대한 논리적 위치)를 가지고 있어 찾고자하는 행을 바로 찾을 수 있다
    • 한 번 검색할 때마다 검색 대상이 줄어 접근 시간이 적게 걸려서, DBMS에서 인덱스의 기본 구조로 B-tree를 많이 사용한다. 하지만 데이터의 변경이나 추가가 잦을 경우 B-tree의 모양을 유지하기 위해 노드의 분할 및 이동이 자주 발생하는 문제가 있다

MYSQL 인덱스

  • 클러스터 인덱스와 보조 인덱스로 나누어지며, 모두 B-tree 인덱스를 기본으로 한다
  • 클러스터 인덱스(clustered index)
    • 연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법
    • 테이블당 하나만 생성할 수 있다
    • 키 값에 의한 동등 및 범위(BETWEEN) 검색에 모두 유리하다
    • B-tree 인덱스의 리프 노드에서 페이지의 주소 값 대신 테이블의 열 자체가 저장되는 형태 → 검색이 쉽다
    • 인덱스 페이지가 단순해져 인덱스 저장 시 차지하는 공간도 작다
    • 테이블 생성 시 기본키(PK)를 생성하면 자동으로 생성된다
  • 보조 인덱스
    • 속성의 값으로 B-tree 인덱스를 구성하며 리프 노드의 각 행은 해당 페이지의 주소 값을 저장한다
  • 클러스터 인덱스와 보조 인덱스는 보통 같이 사용된다(클러스터 인덱스로 저장된 데이터의 순서를 가능한 유지하면서 데이터의 삽입과 삭제에 대한 인덱스 관리 비용을 줄이기 위해서)

인덱스의 생성

  • 인덱스 생성 전 고려사항
    1. 인덱스는 WHERE 절에 자주 사용되는 속성이어야 한다
    2. 인덱스는 조인에 자주 사용되는 속성이어야 한다
    3. 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.(테이블 당 4~5개 정도 권장)
    4. 속성이 가공되는 경우 사용하지 않는다
    5. 속성의 선택도가 낮을 때 유리하다(속성의 모든 값이 다른 경우)
  • 문법
CREATE [UNIQUE] INDEX [인덱스이름]
ON 테이블이름 (컬럼 [ASC | DESC] [{, 컬럼 [ASC | DESC]} ...])[;]

인덱스의 재구성과 삭제

  • B-tree 인덱스는 데이터의 수정, 삭제, 삽입이 잦으면 노드의 갱신이 주기적으로 일어나 단편화(fragmentation, 삭제된 레코드의 인덱스 값 자리가 비게 되는 상태, 검색 시 성능 저하로 이어짐) 현상이 나타난다
  • 단편화 현상은 ANALYZE TABLE 명령을 사용한 재구성을 통해 인덱스를 다시 생성해서 해결한다
ANALYZE TABLE 테이블이름;