이것이MySQL이다 - 01) SQL 고급

MySQL의 데이터 형

 

 

MySQL에서 지원하는 데이터 형식의 종류

Data Type으로 표현

• 데이터 형식, 데이터형, 자료형, 데이터 타입등 다양하게 불림

데이터 형식에 대한 이해가 필요한 이유

• SELECT문 더욱 잘 활용
• 테이블의 생성 효율적으로 하기 위해 필요

MySQL에서 데이터 형식의 종류는 30개 정도

• 중요하고 자주 쓰는 형식에 대해 중점 학습

 

 

MySQL에서 지원하는 데이터 형식의 종류

숫자 데이터 형식

 

 

 

문자 데이터 형식

 

 

 

날짜와 시간 데이터 형식

 

 

 

기타 데이터 형식

 

 

LONGTEXT, LONGBLOB

• LOB(Large Object, 대량의 데이터)을 저장하기 위해 LONGTEXT, LONGBLOB 데이터 형식 지원
• 지원되는 데이터 크기는 약 4GB의 파일을 하나의 데이터로 저장 가능
• LONGTEXT
  • ex) 장편소설과 같은 큰 텍스트 파일
• LONGBLOB
  • ex) 동영상 파일과 같은 큰 바이너리 파일

 

 

 

변수의 사용

Workbench를 재시작할 때까지는 계속 유지, Workbench를 닫았다가 재시작하면 소멸

변수의 선언과 값의 대입 형식

 

• 변수 사용 실습

 

 

 

 

 

데이터 형식과 형 변환

 

데이터 형식 변환 함수

• CAST( ), CONVERT( ) 함수를 가장 일반적으로 사용
• 데이터 형식 중에서 가능한 것은 BINARY, CHAR, DATE, DATETIME, DECIMAL, JSON, SIGNED INTEGER, TIME, UNSIGNED INTEGER
• 함수 사용법

 

• ex) sqlDB의 구매 테이블(buyTbl)에서 평균 구매 개수를 구하는 쿼리문

 

 

 

암시적인 형 변환

• CAST( )나 CONVERT( ) 함수를 사용하지 않고 형이 변환되는 것

 

 

 

 

MySQL 내장 함수

내장 함수

• 흐름 함수, 문자열 함수, 수학 함수, 날짜/시간 함수, 전체 텍스트 검색 함수, 형 변환 함수, XML 함수, 비트 함수, 보안/압축 함 수, 정보 함수, 공간 분석 함수, 기타 함수 등

제어 흐름 함수

• 프로그램의 흐름 제어
• IF (수식, 참, 거짓)
  • 수식이 참 또는 거짓인지 결과에 따라서 2중 분기

 

• IFNULL(수식1, 수식2)
  • 수식1이 NULL이 아니면 수식1이 반환되고 수식1이 NULL이면 수식2가 반환

 

 

 

제어 흐름 함수

NULLIF(수식1, 수식2)

• 수식1과 수식2가 같으면 NULL을 반환, 다르면 수식1을 반환

CASE ~ WHEN ~ ELSE ~ END

• CASE는 내장 함수는 아니며 연산자(Operator)로 분류
• 다중 분기에 사용되므로 내장함수와 함께 알아두자

 

 

• CASE 뒤의 값이 10이므로 세 번째 WHEN이 수행되어 ‘십’ 반환
• 만약, 해당하는 사항이 없다면 ELSE 부분이 반환

 

 

 

문자열 함수

• 문자열 조작, 활용도 높음
• ASCII (아스키 코드),
  • 문자의 아스키 코드값 반환
• CHAR(숫자)
  • 숫자의 아스키 코드값에 해당하는 문자 반환

• BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)
  • 할당된 Bit 크기 또는 문자 크기 반환
  • CHAR_LENGTH( )는 문자의 개수 반환
  • LENGTH( )는 할당된 Byte 수 반환

 

문자열 함수

• CONCAT(문자열1, 문자열2,…), CONCAT_WS(구분자, 문자열1, 문자열2,…)
  • CONCAT( ) : 문자열을 이어줌
  • CONCAT_WS( ) : 구분자와 함께 문자열을 이어주는 역할

 

 

• 2025/01/01 반환

 

 

문자열 함수

• ELT(위치, 문자열1, 문자열2, …), FIELD(찾을 문자열, 문자열1, 문자열2, …), FIND_IN_SET (찾을 문자열, 문자열 리스트), INSTR(기준 문자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)
  • ELT( ) : 위치 번째에 해당하는 문자열 반환
  • FIELD( ) : 찾을 문자열의 위치를 찾아 반환, 없으면 0
  • FIND_IN_SET( ) : 찾을 문자열을 문자열 리스트에서 찾아 위치 반환
    • 문자열 리스트는 콤마(,)로 구분되어 있고 공백이 없어야 함
  • INSTR( )는 기준 문자열에서 부분 문자열 찾아 그 시작 위치 반환
  • LOCATE( )는 INSTR( )와 동일하지만 파라미터의 순서가 반대

 

 

FORMAT(숫자, 소수점 자릿수)

• 숫자를 소수점 아래 자릿수까지 표현, 1,000단위마다 콤마 표시해 줌

BIN(숫자), HEX(숫자), OCT(숫자)

• 2진수, 16진수, 8진수의 값을 반환

INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)

• 기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열 끼워 넣음

• ‘ab@@@@ghi’와 ‘ab@@@@efghi’ 반환

 

 

LEFT(문자열, 길이), RIGHT(문자열, 길이)

• 왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환

• ‘abc’와 ‘ghi’ 반환

 

UPPER(문자열), LOWER(문자열)

• 소문자를 대문자로, 대문자를 소문자로 변경

LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)

• 문자열을 길이만큼 늘린 후에 빈 곳을 채울 문자열로 채움

 

 

 

LTRIM(문자열), RTRIM(문자열)

• 문자열의 왼쪽/오른쪽 공백을 제거, 중간의 공백은 제거되지 않음

TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열)

• TRIM(문자열)은 문자열의 앞뒤 공백을 모두 없앰
• TRIM(방향 자를_문자열 FROM 문자열) 에서 방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤) 으로 표시

 

 

• ‘이것이’와 ‘재밌어요.’ 반환

 

 

REPEAT(문자열, 횟수)

• 문자열을 횟수만큼 반복

REPLACE(문자열, 원래 문자열, 바꿀 문자열)

• 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿈

 

• ‘This is MySQL이다’ 반환

 

REVERSE(문자열)

• 문자열의 순서를 거꾸로 바꿈

 

 

 

SPACE(길이)

• 길이만큼의 공백을 반환

SUBSTRING(문자열, 시작위치, 길이) 또는 SUBSTRING(문자열 FROM 시작위치 FOR 길이)

• 시작위치부터 길이만큼 문자를 반환, 길이가 생략되면 문자열의 끝까지 반환

• ‘민국’ 반환

 

SUBSTRING_INDEX(문자열, 구분자, 횟수)

• 문자열에서 구분자가 왼쪽부터 횟수 번째까지 나오면 그 이후의 오른쪽은 버림
• 횟수가 음수면 오른쪽부터 세고 왼쪽을 버림

 

 

수학 함수

ABS(숫자)

• 숫자의 절댓값 계산

ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)

• 삼각 함수와 관련된 함수 제공

CEILING(숫자), FLOOR(숫자), ROUND(숫자)

• 올림, 내림, 반올림 계산

CONV(숫자, 원래 진수, 변환할 진수)

• 숫자를 원래 진수에서 변환할 진수로 계산

DEGREES(숫자), RADIANS(숫자), PI ( )

• 라디안 값을 각도값으로, 각도값을 라디안 값으로 변환, PI( )는 3.141592 반환

EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)

• 지수, 로그와 관련된 함수 제공

 

MOD(숫자1, 숫자2) 또는 숫자1 % 숫자2 또는 숫자1 MOD 숫자2

• 숫자1을 숫자2로 나눈 나머지 값을 구함

POW(숫자1, 숫자2), SQRT(숫자)

• 거듭제곱값 및 제곱근을 구함

RAND( )

• RAND( )는 0 이상 1 미만의 실수 구함
• ‘m<= 임의의 정수 < n’를 구하고 싶다면 FLOOR(m + (RAND( ) * (n-m) ) 사용

SIGN(숫자)

• 숫자가 양수, 0, 음수인지 판별, 결과는 1, 0, -1 셋 중에 하나 반환

TRUNCATE(숫자, 정수)

• 숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버림

 

날짜 및 시간 함수

ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)

• 날짜를 기준으로 차이를 더하거나 뺀 날짜 구함

ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간)

• 날짜/시간을 기준으로 시간을 더하거나 뺀 결과를 구함

CURDATE( ), CURTIME( ), NOW( ), SYSDATE( )

• CURDATE( ) : 현재 연-월-일
• CURTIME( ) : 현재 시 : 분 : 초
• NOW( ), SYSDATE( ) : 현재 ‘연-월-일 시 : 분 : 초

YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)

• 날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초 구함

 

 

날짜 및 시간 함수

DATE( ), TIME( )

• DATETIME 형식에서 연-월-일 및 시 : 분 : 초만 추출

DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1 또는 시간1, 날짜1 또는 시간2)

• DATEDIFF( )는 날짜1-날짜2의 일수를 결과로 구함

DAYOFWEEK(날짜), MONTHNAME( ), DAYOFYEAR(날짜)

• 요일(1:일, 2:월~7:토) 및 1년 중 몇 번째 날짜인지 구함

LAST_DAY(날짜)

• 주어진 날짜의 마지막 날짜를 구함

 

 

날짜 및 시간 함수

MAKEDATE(연도, 정수)

• 연도에서 정수만큼 지난 날짜 구함

MAKETIME(시, 분, 초)

• 시, 분, 초를 이용해서 ‘시 : 분 : 초’의 TIME 형식 만듦

PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2)

• PERIOD_ADD( )는 연월에서 개월만큼의 개월이 지난 연월 구함
• PERIOD_DIFF( )는 연월1-연월2의 개월수 구함

QUARTER(날짜)

• 날짜가 4분기 중에서 몇 분기인지를 구함

TIME_TO_SEC(시간)

• 시간을 초 단위로 구함

 

 

피벗의 구현

피벗(Pivot) 이란?

• 한 열에 포함된 여러 값 출력, 이를 여러 열로 변환하여 테이블 반환식 회전, 필요하면 집계까지 수행

 

 

 

 

JSON 데이터

JSON (JavaScript Object Notation)이란?

 

• 웹과 모바일 응용프로그램 등과 데이터 교환하기 위한 개방형 표준 포맷
• 속성(Key) 과 값(Value)으로 쌍을 이루며 구성
• JavaScript 언어에서 파생
• 특정한 프로그래밍 언어에 종속되어 있지 않은 독립적인 데이터 포맷
• 포맷이 단순하고 공개되어 있기에 거의 대부분의 프로그래밍 언어에서 쉽게 읽거나 쓸 수 있도록 코딩 가능
• MySQL 5.7.8부터 지원
• MySQL 8.0에서는 인라인 패스라 불리는 → 연산자 및 JSON_ARRAYAGG(), JSON_OBJECTAGG(), JSON_PRETTY(), JSON_STORAGE_SIZE(), JSON_STORAGE_FREE(), JSON_MERGE_PATCH()등의 함수가 추가됨