이것이 MySQL이다. [Chapter 7 - SQL 고급(데이터 형식, 변수, 내장함수)]

7.1 MySQL의 데이터 형식

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

숫자형 형식

숫자형 데이터 형식은 정수, 실수 등의 숫자를 표현한다.

 

DECIMAL 데이터 형식은 정확한 수치를 저장하게 되고, FLOAT, DOUBLE은 근사치의 숫자를 저장한다. 

ex) DECIMAL(5,2)이면 -999.99 ~ +999.99까지의 숫자 범위를 지정할 수 있다.

FLOAT, DOUBLE은 상당히 큰 숫자를 저장할 수 있다는 장점이 있다.

 

부호 없는 정수를 지정할 때는 UNSIGNED 예약어를 뒤에 붙여준다.

부호 없는 정수로 지정하면 음수 숫자 범위가 양수 숫자 범위에 합쳐지게 된다.

ex) TYNYINT는 0 ~ 255, INT는 0 ~ 약  42억

 

 

문자 데이터 형식

CHAR 형식은 고정길이 문자형으로 자릿수가 고정되어 있다.

VARCHAR 형식은 가변길이 문자형으로 자릿수가 고정되어 있지 않다.

 

ex) CHAR의 경우 CHAR(100)에 'APPLE' 5글자만 저장하면 5자리만 사용하고 95자리는 낭비한다.

      VARCHAR의 경우 VARCHAR(100)에 'APPLE' 5글자만 저장하면 5자리만 사용하게 된다.

 

CHAR보다 VARCHAR 형식이 공간을 효율적으로 사용할 수 있다.

VARCHAR는 CHAR 타입에 비해 레코드의 길이가 몇이다. 라는 연산을 거치기 때문에 속도 측면에서는 CHAR가 더 빠르다.

 

BINARY와 BARBINARY는 바이트 단위의 이진 데이터 값을 저장하는데 사용한다.

TEXT 형식은 대용량의 글자를 저장하기 위한 형식이다.

BLOB 형식은 사진 파일, 동영상 파일, 문서 파일 등의 대용량의 이진 데이터를 저장하는데 사용할 수 있다.

ENUM 형식은 열거형 데이터를 사용할 때 사용한다.

 

VARCHAR와 TEXT의 차이점

max size limit을 정하는 것과 index의 일부로 쓰일 수 있는가, 속도에 대해 차이가 있다.

 

날짜와 시간 데이터 형식

 

 

기타 데이터 형식

 

 

LONGTEXT, LONGBLOB

MySQL은 LOB을 저장하기 위해 LONGTEXT, LONGBLOB 데이터 형식을 지원한다.

지원되는 데이터 크기는 약 4BG 크기의 파일을 하나의 데이터로 저장할 수 있다.

ex) LONGTEXT의 경우 영화 대본과 같은 파일, LONGLOB의 경우 영화 동영상 파일

 

 

 

7.1.2 변수의 사용

SQL도 다른 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.

 

변수 선언 예시

 

변수를 활용한 LIMIT

원칙적으로 LIMIT에는 변수를 사용할 수 없다. 하지만 PREPARE와 EXECUTE문을 활용하면 변수를 사용할 수 있다.

PREPARE, EXECUTE문 예시

LIMIT @변수 형식이면 오류가 발생한다.

PREPARE 쿼리이름 FROM 쿼리문에 쿼리문을 준비해놓고 실행하지 않는다.

EXECUTE USING @변수를 사용해 쿼리문에서 ?으로 처리된 곳에 대입된다.

EXECUTE에서 쿼리 이름을 만나는 순간에 실행된다.

 

 

7.1.3 데이터 형식과 형 변환

데이터 형식 변환 함수

가장 일반적으로 사용되는 데이터 형식 변환 함수는 CAST(), CONVERT()가 있다. 

CAST(), CONVERT()는 형식만 다르고 비슷한 기능을 한다.

 

 

만약 어떤 컬럼의 평균 값이 소수로 나오는데 정수의 결과를 보고 싶으면?

현재 amount의 AVG는 1.5000이다.

 

 

CAST와 CONVERT를 통해 반올림한 정수의 결과를 확인할 수 있다.

 

 

또한 다양한 구분자를 날짜 형식으로도 변경할 수 있다.

 

 

암시적인 형 변환

형 변환 방식에는 명시적인 변환과 암시적인 변환 두 가지가 있다.

명시적인 변환이란 CAST() 또는 CONVERT() 함수를 이용해서 데이터를 변환하는 것을 말한다.

암시적인 변환이란 CAST()나 CONVERT() 함수를 이용하지 않고 형이 변환되는 것을 말한다.

 

 

 

7.1.4 MySQL 내장 함수

제어 흐름 함수

제어 흐름 함수는 프로그램의 흐름을 제어한다.

 

IF(수식, 참 거짓)

수식이 참 또는 거짓인지 결과에 따라서 2중 분기한다.

IF 예시

 

 

IFNULL(수식1, 수식2)

수식1이 NULL이 아니면 수식1이 반환되고, 수식1이 NULL이면 수식2가 반환된다.

IFNULL 예시

 

 

NULLIF(수식1, 수식2)

수식1과 수식2가 같으면 NULL을 반환하고, 다르면 수식1을 반환한다.

NULLIF 예시

 

 

CASE ~ WHEN ~ ELSE ~ END

CASE는 내장 함수는 아니며 연산자로 분류된다.

CASE 값이 3이므로 두 번째 WHEN이 실행되며 해당 값인 셋이 반환된다.

아무것도 해당되지 않으면 '없음'이 반환된다.

 

 

문자열 함수

문자열을 조작하는 함수이다. 활용도가 높은 편이다.

 

ASCII(아스키 코드), CHAR(숫자)

문자의 아스키 코드 값을 돌려주거나 숫자의 아스키 코드값에 해당하는 문자를 돌려준다.

ASCII, CHAR 함수 예시

 

 

BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)

BIT_LENGTH는 할당된 Bit 크기 또는 문자 크기를 반환한다.

CHAR_LENGTH는 문자의 개수를 반환한다.

LENGTH는 할당된 Byte 수를 반환한다.

BIT_LENGTH, CHAR_LENGTH, LENGTH 함수 예시

 

 

CONCAT(문자열1, 문자열2,...), CONCAT_WS(구분자, 문자열1, 문자열2,...)

CONCAT은 문자열1과 문자열2의 문자열을 이어준다.

CONCAT은 구분자와 함께 문자열을 이어준다.

 

 

ELT(위치, 문자열1, 문자열2...,), FLELD(찻을 문자열, 문자열1, 문자열2, ...) FIND... IN SET(찾을 문자열, 문자열 리스트)

IN_SET(찾을 문자열, 문자열 리스트), INSTR(기준 문자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)

ELT는 위치 번째에 해당하는 문자열을 반환한다.

FIELD는 찾을 문자열의 위치를 찾아서 반환한다.

FILED는 매치되는 문자열이 없으면 0을 반환한다.

FIND_IN_SET은 찾을 문자열을 문자열 리스트에서 찾아서 위치를 반환한다.

INSTR은 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환한다.

LOCATE는 INSTR과 동일하지만 파라미터의 순서가 반대로 되어 있다.

ELT, FLELD, FIND_IN_SET, IN_SET, INSTR, LOCATE 예시

 

 

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

숫자를 소수점 아래 자릿수까지 표현한다.

1000단위마다 ,를 표시해준다.

FORMAT 예시

 

 

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

2진수, 16진수, 8진수의 값을 반환한다.

BIN, HEX, OCT 예시

 

 

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

기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열을 끼워 넣는다.

INSERT 예시

 

 

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

왼쪽 또는 오른쪽에서 문자열의 길이만큼을 반환한다.

LEFT, RIGHT 예시

 

 

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

소문자를 대문자로, 대문자를 소문자로 변경한다.

UPPER, LOWER 예시

 

 

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

문자열을 길이 만큼 늘린 후에, 빈 곳을 채울 문자열로 채운다.

LPAD, RPAD 예시

 

 

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

문자열의 왼쪽/오른쪽 공백을 제거한다. 중간의 공백은 제거되지 않는다.

LTRIM, RTRIM 예시

 

 

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

TRIM은 문자열의 앞 뒤 공백을 모두 없앤다.

방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤)가 있다.

TRIM예시

 

 

REPEAT(문자열, 횟수)

문자열을 횟수만큼 반복한다.

REPEAT 예시

 

 

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

문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔준다.

REPLACE 예시

 

 

REVERSE(문자열)

문자열의 순서를 거꾸로 만든다.

REVBRSE 예시

 

 

SPACE(길이)

길이만큼의 공백을 반환한다.

SPACE 예시

 

 

 

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

시작 위치부터 길이 만큼 문자를 반환한다.

길이가 생략되면 문자열의 끝까지 반환한다.

SUBSTRING 예시

 

 

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

문자열에서 구분자가 왼쪽부터 횟수 번째 나오면 그 이후의 오른쪽은 버린다.

횟수가 음수면 오른쪽부터 세고 왼쪽을 버린다.

SUBSTRING_INDEX 예시

 

 

수학 함수

ABS 숫자(숫자)

숫자의 절댓값을 계산한다.

ABS 예시

 

 

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

삼각 함수와 관련된 함수를 제공한다.

 

 

CELING(숫자), FLOOR(숫자), ROUND(숫자)

올림, 내림, 반올림을 계산한다.

CELING, FLOOR, ROUND 예시

 

 

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

숫자를 원래 진수에서 변환할 진수로 계산한다.

CONV 예시

 

 

DEGREES(숫자), RADIANS(숫자), Pl()

라디안 값을 각도값으로, 각도값을 라디안 값으로 변환한다. PI()는 파이값인 3.141592를 반환 한다.

DEGRESS, RADIANS 예시

 

 

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

지수, 로그와 관련된 함수를 제공한다.

 

 

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

숫자1을 숫자2로 나눈 나머지 값을 구한다.

MOD 예시

 

 

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

거듭제곱값 및 제곱근을 구한다.

POW, SQRT 예시

 

 

RAND

RAND ()는 0 이상 1 미만의 실수를 구한다.

만약 임으의 정수를 구하고 싶다면 FLOOR(m + (RAND() * (n - m)))을 사용하면 된다.

RAND 예시

 

 

SIGN(숫자)

숫자가 양수, 0, 음수인지를 구한다. 결과는 1, 0, -1 셋 중에 하나를 반환한다.

SIGN 예시

 

 

TRUNCATE(슷자, 정수)

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

TRUNCATE 예시

 

날짜 및 시간 함수

날짜 및 시간을 조작하는 다양한 함수이다.

 

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

날짜를 기준으로 차이를 더하거나 뺀 날짜를 구한다.

SUBTIME 예시

ADDDATE의 경우 날짜를 더하고, SUBDATE의 경우 날짜를 뺀다.

 

 

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

날짜/시간을 기준으로 시간을 더하거나 뺀 결과를 구한다.

ADDTIME SUBTIME 예시

ADDTIME과 SUBTIME은 날짜가 아닌 시간을 기준으로 더하거나 뺀다.

 

 

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

CURDATE()는 현재 연-월-일을, CURTIME()은 현재 시:분:초를 구한다.

NOW()와 SYSDATE()는 현재 '연-월-일 시:분초'를 구한다.

CURDATE, CURTIME, NOW, SYSDATE 예시

 

 

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

날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초를 구한다.

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 예시

 

 

DATE(), TIME()

DATETIME 형식에서 연-월-일 시:분:초만 추출한다.

DATE, TIME 예시

 

 

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

DATEDIFF。는 날짜1-날짜2의 일수를 결과로 구한다. 즉 날짜2에서 날짜1까지 몇 일이 남았 는지 구한다

DATEDIFF, TIMEDIFF 예시

 

 

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

요일（1：일, 2：월〜7：토） 및 1년 중 몇 번째 날짜인지를 구한다

DAYOFWEEK, MONTHNAME, DAYOFYEAR 예시

 

 

LAST_DAY(날짜)

주어진 날짜의 마지막 날짜를 구한다. 주로 그 달이 몇 일까지 있는지 확인할 때 사용한다.

LAST_DAY 예시

 

 

MAKEDATE(연도, 정수)

연도에서 정수만큼 지난 날짜를 구한다.

MAKEDATE 예시

 

 

MAKETIME(시, 분, 초)

人I 분, 초를 이용해서 '시:분:초의 TIME 형식을 만든다

MAKETIME 예시

 

 

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

PERIOD_ADD（）는 연월에서 개월만큼의 개월이 지난 연월을 구한다. 연월은 YYYY 또는 YYYYMM 형식을 사용한다.

PERIOD_ADD, PERIOD_DIFF 예시

 

 

QUARITER(날짜)

날짜가 4분기 중에서 몇 분기인지를 구한다

QUARITER 예시

 

 

TIME_TO_SEC(시간)

간을 초 단위로 구한다.

TIME_TO_SEC 예시

 

 

시스템 정보 함수

시스템의 정보를 출력하는 함수를 제공한다.

 

USER(), DATABASE()

현재 사용자 및 현재 선택된 데이터베이스를 구한다.

USER, DATABASE 예

 

FOUND_ROWS()

바로 앞의 SELECT문에서 조회된 행의 개수를 구한다.

FOUND_ROWS 예시

 

ROW_COUNT()

바로 앞의 INSERT. UPDATE, DELETE문에서 입력, 수정, 삭제된 행의 개수를 구한다

 

 

VERSION()

현재 MySQL의 버전을 구한다.

VERSION 예

 

SLEEP(초)

쿼리의 실행을 잠깐 멈춘다.

 

 

피벗의 구현

피벗은 한 열에 포함된 여러 값을 출력하고 이를 여러 열로 변환하여 테이블 반환 식을 회전하고 필요하면 집계까지 수행하는 것을 말한다.

pivot 테이블

pivot 테이블은 구매자 이름, 구매 과일, 구매 수량으로 구성된 테이블이다.

이를 각 구매자가 구매 과일 별로 몇 개를 구매하였는지 나타내고 싶을 때 SUM과 IF 함수를 활용할 수 있다.

피벗 예시

IF문으로 fruit의 값이 사과인 행만 선택한다.

그 선택된 행에 대해 amount 열의 값을 가져온다.

만약 fruit 열의 값이 사과가 아닌 경우 0을 반환한다.

이러한 형식으로 각각 과일의 개수와 전체 합계를 가져오게 된다.

 

피벗을 사용하면 큰 데이터의 집합을 다룰 때 데이터를 요약하고 집계하기가 훨씬 좋다.

즉, 복잡한 데이터를 간편하게 볼 수 있다는 장점이 있다.

 

 

JSON 데이터

JSON은 현대의 웹과 모바일 응용 프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷을 말한다.

속성과 값으로 쌍을 이루며 구성되어 있다.

 

MySQL은 JSON 관련된 다양한 내장 함수를 제공하여 다양한 조작이 가능하다.

JSON으로 변환하려면 JSON_OBJECT()나 JSON_ARRAY() 함수를 이용하면 된다.

JSON 변환 예시

name과 age의 값이 JSON 형식으로 변환된 것을 알 수 있다.

 

JSON을 MySQL에서 제공하는 다양한 함수를 사용해서 운영할 수 도 있다.