MySQL의 데이터 형식

JSON 데이터

• 웹과 모바일 응용프로그램 등과 데이터 교환하기 위한 개방형 표준 포맷
• 속성(Key)과 값(Value)으로 쌍을 이루며 구성
• JavaScript 언어에서 파생
• 특정한 프로그래밍 언어에 종속되어 있지 않은 독립적인 데이터 포맷
• 포맷이 단순하고 공개되어 있기 때문에 거의 대부분의 프로그래밍 언어에서 쉽게 읽거나 쓸 수 있도록 코딩 할 수 있다.

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

• Ddata Type 으로 표현
  • 데이터 형식, 데이터형, 자료형, 데이터 타입 등 다양하게 불림
• 데이터 형식에 대한 이해가 필요한 이유
  • SELECT문 더욱 잘 활용
  • 테이블의 생성 효율적으로 하기 위해 필요
• MySQL에서 데이터 형식의 종류는 30개 정도
  • 중요하고 자주 쓰는 형식에 대해 중점 학습

숫자 데이터 형식

• BIT
• TINYINT
• SMALLINT
• MEDIUMINT
• INT(정수형) - 약 -21억 ~ + 21억
• BIGINT
• FLOAT(실수형)
  • 소수점 아래 7자리 까지 표현
• DOUBLE
  • 소수점 아래 15자리 까지 표현

문자 데이터 형식

• CHAR(n)
  • 바이트 수 : 1~255
  • 고정길이 문자형. n을 1부터 255까지 지정
• VARCHAR(n)
  • 바이트수 : 1~65535
• TEXT
• LONGTEXT
  • 큰 텍스트 파일 최대 4GB
• BLOB
• LONGBLOB
  • 동영상 파일, 음성 파일과 같은 큰 바이너리 파일 최대 4GB

날짜와 시간 데이터 형식

• DATE
  • ‘YYYY-MM-DD’
• TIME
  • ‘HH:MM:SS’
• DATETIME
  • ‘YYYY-MM-DD HH:MM:SS’

기타 데이터 형식

• GEOMETRY
• JSON

 

변수의 사용

• Wordkbench를 재시작할 때까지는 계속 유지, Workbench를 닫았다가 재시작하면 소면
• 변수의 선언과 값의 대입 형식

SET @변수이름 = 변수의 값; -- 변수의 선언 및 값 대입
SELECT @변수이름 ; -- 변수의 값 출력

SET @myVar1 = 5;
SET @myVar2 = 3;
SET @myVar3 = 4.25;
SET @myVar4 = '가수 이름 ==> ';

SELECT @myVar1;

SELECT @myVar2 + @myVar3

SELECT @myVar4 , Name FROM usertbl WHERE height >180 ;

• LIMIT에는 원칙적으로 변수를 사용할 수 없으나 PREPARE와 EXECUTE문을 활용해서 변수 활용 가능

SET @myVar1 = 3;
PREPARE myQuery
	FROM 'SELECT Name, height FROM usertbl ORDER BY height LIMIT ?';
EXECUTE myQuery USING @myVar1 ;

• LIMIT는 LIMIT 3과 같이 직접 숫자를 넣어야 한다.
• PREPARE 쿼리이름 FROM ‘쿼리문’은 준비만 하고 실행하지 않는다.
• EXECUTE 쿼리이름을 만나는 순간에 실행
• EXECUTE는 USING @변수를 이용해 ‘쿼리문’에서 ? 으로 처리해 놓은 부분에 대입된다.
• 결국 LIMIT @변수 형식으로 사용된것과 동일

 

데이터 형식과 형 변환

• CAST(), CONVERT() 함수를 사용한다. 형식만 다를 뿐 거의 비슷한 기능을 한다.

형식:
CAST ( expression AS 데이터형식 [ (길이) ]
CONVERT ( expression , 데이터형식 [ (길이) ]

SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl;

SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl;
SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl;

SELECT CONVERT(AVG(amount) , SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl;

SELECT CAST('2020/12/12' AS DATE);

SELECT 
	num, 
    CONCAT(CAST( price AS CHAR(10)),'x',CAST(amount AS CHAR(4)),'=') AS '단가x수량', 
    price * amount AS '구매액' 
    FROM buytbl;

암시적인 형 변환

• 알고만 지나가기 ! 권하지 않음
  • MySQL에서만 기호를 우선해서 기준으로 SELECT해준다.

SELECT '100' + '200';

• concat안에 있는 숫자를 문자형으로 바꿔준다.

SELECT CONCAT(100,'200');

• 정수인 2로 변환되어서 비교

SELECT 1 > '2mega'; -- 1이 2보다 큰것은 거짓 = 0

• ‘’안에 문자가 먼저오면 무조건 0으로 바꿔준다.

SELECT 1 = 'mega2'; -- 1과 0은 같지 않다. = 0

MySQL 내장 함수

제어 흐름 함수

• 프로그램의 흐름 제어

 

조건문

IF (수식, 참, 거짓)

• 수식이 참 또는 거짓인지 결과에 따라서 참이면 두번째, 거짓이면 세번째

SELECT IF (100>200, '참이다', '거짓이다');

IFNULL(수식1, 수식2)

• 수식1이 NULL이 아니면 수식 1이 반환되고 수식1이 NULL이면 수식2가 반환

SELECT IFNULL(NULL, '널이군요'), IFNULL(100,'널이군요');

 

 

NULLIF(수식1, 수식2)

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

SELECT NULLIF(200,100);

 

CASE ~ WHEN ~ ELSE ~ END

SELECT 
	CASE 10
		WHEN 1 THEN '일'
		WHEN 5 THEN '오'
		WHEN 10 THEN '십'
		ELSE '모름'
END AS 'CASE 연습';

 

 

문자열

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

 

SELECT ASCII('A');

SELECT CHAR(65);

 

 

개수 - BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열) 

• 문자 개수를 반환

SELECT bit_length('abc');
SELECT char_length('abc');
SELECT length('abc');

연결 - CONCAT(문자열1, 문자열 2…), CONCAT_WS(구분자, 문자열1, 문자열2 …)

SELECT CONCAT('2023','/','7','/','18');
SELECT CONCAT_WS('/', '2023','7','18');

 

 

문자 검색

FIELD(찾을 문자열, 문자열1, 문자열2, …)

• 같은 문자열의 인덱스 가져옴

SELECT FIELD('하나', '하나','둘','셋');

 

FIND_IN_SET(찾을 문자열, 문자열 리스트)

• 하나의 문자열 안에 콤마로 구분되었을때 구분해줌

SELECT find_in_set('둘','하나,둘,셋');

 

INSTR(기준문자열, 부분문자열)

SELECT INSTR('하나둘셋','둘');

 

 LOCATE(부분문자열, 기준문자열)

SELECT LOCATE('둘','하나둘셋');

 

 

문자 추출

ELT(위치, 문자열1, 문자열2. ,,,),

SELECT ELT(3, '하나', '둘','셋');

 

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

SELECT LEFT('abcdefgjiklmn', 3);
SELECT RIGHT('abcdefgjiklmn', 3);

 

SUBSTRING(문자열, 시작위치, 길이)

SELECT SUBSTRING('이것이 MySQL이다.', 5, 7);

 

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

SELECT SUBSTRING_INDEX('cafe.naver.com', '.', 2);

 

SELECT SUBSTRING_INDEX('cafe.naver.com', '.', -2);

 

 

문자 변경

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

SELECT LOWER('ABCDEFGHIJKLMN');

 

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

SELECT LPAD('이것이',5, '##');

 

 

공백 제거

LTRTIM(문자열) RTRIM(문자열)

 

TRIM(문자열)

SELECT TRIM('      이것이     ');

 

 

문자 반복

REPEAT(문자열, 횟수)

SELECT repeat('A', 5);

 

 

문자열 변경

FORMAT(숫자, 소수점 자릿수) 반올림 해줌

SELECT FORMAT(123456.123456,4);

 

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

• 4개 지우고 ‘@@@@’ 삽입

SELECT INSERT('abcdefgjiklmn', 3, 4, '@@@@');

• 2개 지우고 ‘@@@@’ 삽입

SELECT INSERT('abcdefgjiklmn', 3, 2, '@@@@');

 

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

SELECT REPLACE('이것이 MySQL이다.', '이것이', 'This is');

 

REVERSE(문자열)

SELECT reverse('MySQL이다.');

 

SPACE(길이)

SELECT CONCAT('이것이', SPACE(10), 'MySQL이다');

 

 

수학

CEILING(숫자) - 올림

 

FLOOR(숫자) - 내림

 

ROUND(숫자) - 반올림

 

MOD(숫자1, 숫자2) - 나머지

SELECT MOD(157, 10);
SELECT 157%10;

 

RAND()

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

SIGN(숫자)

• 양수 , 0, 음수를 구한다. 결과는 1,0,-1 셋 중에 하나를 반환

 

TRUNCATE(숫자, 정수) - 

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

 

 

날짜 및 시간

ADDDATE(날짜, 차이)

• 날짜를 기준으로 차이를 더한 날짜

 

SUBDATE(날짜, 차이)

• 날짜를 기준으로 차이를 뺀 날짜

 

ADDTIME(날짜/시간, 시간)

• 날짜/시간을 기준으로 시간을 더한 시간

 

SUBTIME(날짜/시간, 시간)

• 날짜/시간을 기준으로 시간을 뺀 시간

 

CUDATE()

• 현재 연-월-일을 구한다.

 

CURTIME()

• 현재 시:분:초를 구한다.

 

NOW(), SYSDATE()

• 현재 연-월-일 시:분:초 를 구한다.

SELECT YEAR(CURDATE());
SELECT MONTH(CURDATE());
SELECT DAY(CURDATE());

SELECT HOUR(CURTIME());
SELECT MINUTE(CURTIME());
SELECT SECOND(CURTIME());

 

DATEDIFF(날짜1, 날짜2)

• 날짜1 - 날짜2 의 일수를 결과로 구한다.

 

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

• 시간1 - 시간2 의 결과를 구한다.

 

LAST_DAT(날짜)

• 주어진 날짜의 마지막 날자를 구한다.

 

MAKEDATE(연도, 정수)

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

 

MAKETIME(시, 분, 초)

• 시, 분, 초를 이용해서 ‘시:분:초’의 TIME 형식을 만든다.

 

QUARTER(날짜)

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

 

TIME_TO_SEC(시간)

• 시간을 초 단위로 구한다.

 

시스템 정보 함수

USER(), DATABASE()

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

FOUND_ROWS()

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

ROW_COUNT()

• 바로 앞의 INSERT, UPDATE, DELETE문에서 입력, 수정, 삭제된 행의 개수를 구한다.
• CREATE, DROP문은 0을 반환하고, SELECT문은 -1을 반환한다.

VERSION()

• 현재 MySQL의 버전을 구한다.

SLEEP(초)

• 쿼리의 실행을 잠깐 멈춰준다.