7장 MySQL의 데이터 형식 및 내장 함수
이 글은 학습용으로 '이것이 MySQL이다' 7장을 정리한 글입니다.
7.1.1 MySQL에서 지원하는 데이터 형식의 종류
숫자 데이터 형식
문자 데이터 형식
BINARY와 VARBINARY는 바이트 단위의 이진 데이터 값을 저장하는 데 사용
TEXT 형식은 대용량의 글자를 저장하기 위한 형식
BLOB(Binary Large Object)은 사진 파일, 동영상 파일, 문서 파일 등의 대용량의 이진 데이터를 저장하는 데 사용될 수 있다.
필요한 크기에 따라 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB 등의 형식을 사용할 수 있다.
ENUM은 열거형 데이터를 쓸 때 사용된다.
예 : 월, 화, 수, 목, 금, 토, 일을 설정할 수 있다.
SET은 최대 64개를 준비한 후에 입력은 그 중에서 2개씩 세트로 데이터를 저장시키는 방식을 사용한다.
MySQL 5.7의 기본 문자 세트는 my.ini또는 my.cnf 파일에 기본적으로 설정되어 있다.
날짜와 시간 데이터 형식
기타 데이터 형식
LONGTEXT, LONGBLOB
LOB(Large Object : 대량의 데이터)을 저장하기 위해서 LONGTEXT, LONGBLOB 데이터 형식을 지원한다.
지원되는 데이터 크기는 약 4GB의 파일을 하나의 데이터로 저장할 수 있다.
예 ) 장편 소설과 같은 큰 텍스트 파일 --> LONGTEXT 형식으로 저장된 하나의 컬럼에 넣을 수 있다.
예 ) 동영상 파일 --> LONGBLOB 형식으로 지정된 하나의 컬럼에 넣는다.
7. 1. 2 변수의 사용
((스토어드 프로시저나 함수 안에서의 변수를 사용하는 방법은 DECLARE문으로 선언한 후에 사용가능.
스토어드 프로시저 : 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
: MySQL에서 제공해주는 프로그래밍 기능, SQL문을 하나로 묶어서 편리하게 사용하는 기능.
또한 스토어드 프로시저나 함수 안에서는 @변수명 형식이 아닌 그냥 변수명만 사용한다.
@변수명은 '전역 변수'처럼 DECLARE 변수명은 스토어드 프로시저나 함수 안에서 '지역 변수'처럼 사용한다. ))
변수는 Workbench를 재시작하면 소멸된다.
실습 1 - 간단히 변수의 사용을 실습하자.
step1 - 변수 설정 후 출력
step2 - LIMIT에는 원칙적으로 변수를 사용할 수 없으나 PREPARE와 EXECUTE문을 활용해서 변수를 활용할 수 있다.
LIMIT는 직접 숫자를 넣어야 하며 LIMIT @변수 형식으로 사용하면 오류가 발생한다.
PREPARE 쿼리이름 FROM '쿼리문'을 통해 준비를 해놓는다.
EXECUTE 쿼리이름을 통해 쿼리문이 실행이 된다.
EXECUTE는 USING @변수를 이용해서 쿼리문에 ? 처리해 놓은 부분이 대입된다.
7. 1. 3 데이터 형식과 형 변환
데이터 형식 변환 함수
가장 일반적으로
개수이기 때문에 정수로 보기 위해서 CAST() 함수나 CONVERT() 함수를 사용
다양한 구분자를 날짜 형식으로도 변경할 수 있다.
쿼리 결과를 보기 좋도록 처리할 때도 사용된다. 단가와 수량을 곱한 실제 입금액을 표시하는 쿼리
암시적인 형 변환
형 변환 방식에는 명시적인 변환, 암시적인 변환
명시적인 변환 : CAST() 또는 CONVERT() 함수를 이용해서 데이터 형식을 변환하는 것
암시적인 변환 : CAST() 또는 CONVERT() 함수를 사용하지 않고 형이 변환되는 것
첫 번째 결과인 문자열 + 문자열은 더하기 연산자 때문에 문자열이 숫자로 변경되어서 계산
두 번째 문자열을 연결해주는 CONCAT() 함수이기에 문자열이 그대로 문자열로 처리되었다.
세 번째도 CONCAT() 함수 안의 숫자는 문자열로 변환되어 처리되었다.
네 번째와 비교 연산자인데 앞에 '2'가 들어간 문자열이 숫자로 변경되어서 결국 '1 >2' 비교가 된다.
결과는 거짓(0)이 나왔다.
다섯 번째도 마찬가지 방식이다. 마지막 'mega2' 문자열은 숫자로 변경되어도 그냥 0이 되기 때문에 결국 '0=0'이 되어 true(1)의 결과가 나왔다.
7. 1. 4 MySQL 내장 함수
제어 흐름 함수
: 제어 흐름 함수는 프로그램의 흐름을 제어
IF(수식, 참, 거짓)
수식이 참 또는 거짓인지 결과에 따라서 2중 분기한다.
IFNULL(수식1, 수식2) : 수식1이 NULL이 아니면 수식1이 반환되고, 수식1이 NULL이면 수식2가 반환된다.
NULLIF(수식1, 수식2) : 수식1과 수식2가 같으면 NULL을 반환하고, 다르면 수식1을 반환한다.
CASE~WHEN~ELSE~END : CASE는 내장 함수는 아니며 연산자로 분류된다. 다중 분기에 사용될 수 있으므로 내장 함수와 함께 알아두자.
문자열 함수
문자열 조작시 사용
ASCII(아스키 코드), CHAR(숫자) : 문자의 아스키 코드값을 돌려주거나 숫자의 아스키 코드값에 해당하는 문자를 돌려준다.
BLOB --> 우클릭 --> Open Value in viewer
CONCAT(문자열1, 문자열2, ...), CONCAT_WS(문자열1, 문자열2) :
문자열을 이어준다. CONCAT_WS()는 구분자와 함께 문자열을 이어준다.
ELT(위치, 문자열1, 문자열2, ...) , FIELD(찾을 문자열, 문자열1, 문자열2, ...), FIND_IN_SET(찾을 문자열, 문자열 리스트), INSTR(기준 문자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)
ELT() : 위치 번째에 해당하는 문자열 반환
FIELD() : 찾을 문자열의 위치를 찾아서 반환, 매치되는 문자열이 없으면 0을 반환
FIND_IN_SET() : 찾을 문자열을 문자열 리스트에서 찾아서 위치를 반환, 문자열 리스트는 콤마(,)로 구분되어 있어야 하며 공백이 없어야 한다.
INSTR() : 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환한다.
LOCATE()는 INSTR()와 동일하지만 파라미터의 순서가 반대로 되어 있다. POSITION()과 동일한 함수이다.
FORMAT(숫자, 소수점 자릿수) : 숫자를 소수점 아래 자릿수까지 표현한다. 또한 1000 단위마다 콤마(,)를 표시해준다.
BIN(숫자), HEX(숫자), OCT(숫자) : 2진수, 16진수, 8진수의 값을 반환한다.
INSERT(기준 문자열, 위치, 길이, 삽입할 문자열) : 기준 문자열의 위치부터 길이만큼을 지우고 삽입할 문자열을 끼워 넣는다.
LEFT(문자열, 길이), RIGHT(문자열, 길이) : 왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환한다.
UCASE(문자열), LCASE(문자열) : 소문자를 대문자로, 대문자를 소문자로 변경한다.
UPPER(문자열), LOWER(문자열) : 소문자 --> 대문자, 대문자 --> 소문자로 변경
LOWER()는 LCASE()와, UPPER()는 UCASE()와 동일한 함수다.
LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)
: 문자열을 길이만큼 늘린 후에 빈 곳을 채울 문자열로 채운다.
LTRIM(문자열), RTRIM(문자열) :
문자열의 왼쪽/오른쪽 공백을 제거한다. 중간의 공백은 제거되지 않는다.
TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열)
: TRIM(문자열)은 문자열의 앞뒤 공백을 모두 없앤다. TRIM(방향 자를_문자열 FROM 문자열)에서 방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤)이 나올 수 있다.
REPEAT(문자열, 횟수)
: 문자열을 횟수만큼 반복한다.
REPLACE(문자열, 원래 문자열, 바꿀 문자열)
: 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔준다.
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.141459를 반환한다.
파이의 각도값인 180과 180의 라디안 값이 출력된다.
EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)
: 지수, 로그와 관련된 함수를 제공한다.
MOD(숫자1, 숫자2) 또는 숫자1 %숫자2 또는 숫자1 MOD 숫자2
: 숫자1을 숫자2로 나눈 나머지 값을 구한다.
POW(숫자1, 숫자2), SQRT(숫자)
: 거듭제곱값 및 제곱근을 구한다.
RAND()
: 0 이상 1미만의 실수를 구한다. 만약 'm<= 임의의 정수 < n'를 구하고 싶다면 FLOOR(m + (RAND() * (n-m))을 사용하면 된다.
SIGN(숫자)
: 숫자가 양수, 0, 음수인지를 구한다. 결과는 1, 0, -1 셋 중에 하나를 반환한다.
TRUNCATE(숫자, 정수)
: 숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버린다.
날짜 및 시간 함수
날짜와 시간을 조작
ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)
: 날짜/시간을 기준으로 시간을 더하거나 뺀 결과를 구한다.
CURDATE(), CURTIME(), NOW(), SYSDATE()
: CURDATE(): 현재 연-월-일, CURTIME():현재 시:분:초, NOW(),SYSDATE()는 현재 연-월-일-시:분:초
: CURDATE(), CURRENT_DATE(), CURRENT_DATE는 모두 동일
: CURTIME(), CURRENT_TIME(), CURRENT_TIME 모두 동일
: NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMPO, LOCALTIMESTAMP()도 모두 동일
YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)
: 날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초를 구한다.
DAYOFMONTH()와 DAY()는 동일한 함수다.
DATE(), TIME()
: DATETIME 형식에서 연-월-일 및 시:분:초만 추출한다.
DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1 또는 시간1, 날짜1 또는 시간2)
: 날짜1-날짜2의 일수를 결과로 구한다. 즉, 날짜2에서 날짜1까지 몇 일이 남았는지 구한다.
: TIMEDIFF()는 시간1-시간2의 결과를 구한다.
DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)
: 요일(1:일, 2:월~7:토) 및 1년 중 몇 번째 날짜인지를 구한다.
LAST_DAY(날짜)
: 주어진 날짜의 마지막 날짜를 구한다. 주로 그 달이 몇 일까지 있는 지 확인할 때 사용한다.
MAKEDATE(연도, 정수)
: 연도에서 정수만큼 지난 날짜를 구한다.
MAKETIME(시, 분, 초)
: 시, 분, 초를 이용해서 '시:분:초'의 TIME 형식을 만든다.
PERIOD_ADD(연월,개월수), PERIOD_DIFF(연월1, 연월2)
: ADD는 연월에서 개월만큼의 개월이 지난 연월을 구한다. DIFF는 연월1-연월2의 개월수를 구한다.
QUARTER(날짜)
: 날짜가 4분지 중에서 몇 분기인지를 구한다.
TIME_TO_SEC(시간)
: 시간을 초 단위로 구한다.
시스템 정보 함수
시스템의 정보를 출력
USER(), DATABASE()
:현재 사용자 및 현재 선택된 데이터베이스를 구한다.
USER(), SESSION_USER(), CURRENT_USER()는 모두 동일하다.
DATABASE()와 SCHEMA() 도 동일한 함수다.
FOUND_ROWS()
: 바로 앞의 SELECT문에서 조회된 행의 개수를 구한다.
ROW_COUNT()
: 바로 앞의 INSERT, UPDATE, DELETE문에서 입력, 수정, 삭제된 행의 개수를 구한다.
CREATE, DROP문은 0을 반환하고 SELECT문은 -1은 반환한다.
version()
: 현재 MySQL의 버전을 구한다.
sleep(초)
: 쿼리의 실행을 잠깐 멈춘다.
그 외의 함수
: 비트, 전체 텍스트 검색, 보안 및 압축, 암호화, XML, 공간 분석, JSON 함수 등 필요할 때 소개.
: 공식문서 참조
실습2 : TEXT 데이터 형식을 이용해서 대량의 데이터를 입력해보자
step1 : max 형의 데이터를 정의한다.
step2 : 각각 백만개의 문자의 대량 데이터를 입력하자.
영문자는 1Byte, 한글은 utf-8코드 - 3Byte 차지
step3 : 천만개 문자 대량 데이터 입력
오류 발생
stpe4 : max_allowed_packet 시스템의 변숫값을 변경해서 다시 입력
my.ini 파일 확인
NOTEPAD my.ini --> max_all 검색 --> 용량 변경 400
다시 입력 --> 확인
step6 : 쿼리 결과를 파일로 저장하거나 저장된 내용을 다시 테이블에 입력하기
my.ini에서 Secure File Priv 옵션 추가
적용 확인
select 파일로 저장
userTBL과 동일한 구조의 memberTBL을 만들고 파일의 내용을 memberTBL에 한 번에 입력시키기
피벗의 구현
: 피벗은 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환하여 테이블 반환식을 회전하고 필요하면 집계까지 수행하는 일을 한다.
판매자 이름, 판매 계절, 판매 수량 --> 각 판매자가 계절별로 몇 개 구매했는지 표현하고 싶을 때 사용
실습3 : 간단한 피벗 테이블 실습
테이블 생성 --> 데이터 입력 --> 확인
SUM(), IF(), GROUP BY 활용
JSON 데이터
JSON_OBJECT() 또는 JSON_ARRAY() 함수 사용
JSON 내장함수 사용 해보기
: 문자열이 JSON형식을 만족하면 1, 그렇지 않으면 0반환
: 3번째 파라미터에 주어진 문자열의 위치
: one은 처음으로 매치된느 하나만 반환, all은 매치된느 모든 것을 반환
: index는 0부터 시작
: 지정된 위치의 값을 추출
: 새로운 값을 추가
: 첫 번째(0)에 mDate 추가
: 값을 변경
: 첫 번째 name을 임재범 -> 홍길동으로 변경
: 지정된 항목을 삭제
: 첫 번째(0) 항목 통째로 삭제
