MySQL 데이터형식 및 변수

1. MYSQL에서 지원하는 데이터 형식의 종류

 1) 숫자 데이터 형식 : BIT(n), TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL(m, [d]), NUMERIC(m, [d])

 2) 문자 데이터 형식 : CHAR(n), VARCHAR(n), BINARY(n), VARBINARY(n), TEXT {TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT}, BLOB {TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB}, ENUM(값들..), SET(값들..)

  a) mysql charset 확인

 3) 날짜와 시간 데이터 형식 : DATE, TIME, DATETIME, TIMESTAMP, YEAR

select cast('2020-10-19 12:35:29.123' AS DATE) AS 'DATE', 
cast('2020-10-19 12:35:29.123' AS TIME) AS 'TIME', 
cast('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME';

 4) 기타 데이터 형식 : GEOMETRY => N/A 바이트 => 공간 데이터 형식으로 선, 점 및 다각형 같은 공간 데이터 객체를 저장하고 조작

 JSON => 8바이트 => JSON(JavaScript Object Notation)문서를 저장

 5) 대용량 데이터 형식 : LONGTEXT, LONGBLOB

  LOB(Large Object : 대용량의 데이터)

---

2. 변수설정

SET @변수이름 = 변수의값;
SELECT @변수이름
 
SET @myVar1 = '변수설정';
SET @myVar2 = 123;
SELECT @myVar1, @myVar2;

PREPARE와 EXECUTE문 활용

set @Var1 = 3;
prepare myquery from  'select * from departments limit ?';
execute myquery using @Var1;

---

3. 데이터 형식과 형 변환

- 가장 일반적으로 사용되는 데이터 형식 변환과 관련해서는 CAST(), CONVERT() 함수를 사용한다. CAST(), CONVERT()는 형식만 다를 뿐 거의 비슷한 기능을 한다.

- 데이터 형식 중에서는 가능한것은 BINARY, CHAR, DATA, DATATIME, DECIMAL, JSON, SIGNED INTEGER, TIME, USINGNED INTEGER 등이다.

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

select emp_no, avg(salary) AS '평균연봉',
cast(avg(salary) as signed integer) AS '평균연봉',
convert(avg(salary), signed integer) AS '평균연봉' 
from salaries group by emp_no;

select cast('2020%12%12' as DATE) AS 'DATE', 
cast('2020$12$12' as DATE) AS 'DATE',
cast('2020/12/12' as DATE) AS 'DATE',
cast('2020@12@12' as DATE) AS 'DATE'; 

select emp_no, 
cast(avg(salary) as signed integer) AS '평균연봉 인상전',
concat(cast(avg(salary) as signed integer), ' X ', cast('2' as signed integer),' =') AS '평균연봉 2배',
cast(avg(salary)*2 as signed integer) AS '결과'
from salaries group by emp_no;

명시적인 변환(Explicit Conversion) : CAST() 또는 CONVERT() 함수를 이용해서 데이터 형식을 변환하는 것

암시적인 변환(Implicit Conversion) : CAST(), CONVERT() 함수를 사용하지 않고 형이 변환되는 것을 말한다

SELECT '100'+'300', CONCAT('100','300'), CONCAT(100,'300'), 1>'2a', 3>'2a', 0='a';

문자 2a는 정수2로 처리, 문자 a는 0으로 변환된다. 그러므로 0=0 되고 TRUE(1) 라는값이 반환

---

4. MYSQL 내장 함수

-- 제어흐름함수
-- IF(수식, 참, 거짓) : 수식에 따라 참, 거짓 분기
SELECT IF(1>0, 'TRUE', FALSE);
 
-- IFNULL(수식1, 수식2) : 수식1이 NULL이 아니면 수식1이 반환, NULL이면 수식2 반환
SELECT IFNULL(NULL, 'TRUE');
 
-- NULLIF(수식1, 수식2) : 수식1과 수식2가 같음녀 NULL반환, 다르면 수식1 반환
SELECT NULLIF(100,100);
 
-- CAST ~ WHEN ~ ELSE ~ END
-- CAST는 내장함수가 아닌 연산자로 분류된다
SELECT CASE 10
  WHEN 1 THEN '일'
  WHEN 5 THEN '오'
  ELSE '모름'
END;

-- 문자열 함수
-- ASCII(아스키 코드), CHAR(숫자)
SELECT ASCII('A'), CHAR(65);
 
-- BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열) : 할당된 Bit 크기 또는 문자 크기를 반환
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');
 
-- CONCAT(문자열1, 문자열2, ..), CONCAT_WS(문자열1, 문자열2, ..) : 문자를 이어준다
SELECT CONCAT_WS('/', '2020', '01', '01');
-- 2020/01/01 반환
 
-- ELT(위치, 문자열1, 문자열2, ..), FIELD(찾을 문자열, 문자열1, 문자열2, ...), FIND_IN_SET(찾을 문자열, 문자열 리스트), INSTR(기준 문자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)
-- FIND_IN_SET 콤마(,)로 구분되어야하며 공백이 없어야한다 / INSTR)은 기준 문자열에서 부분 문자열을 찾아서 그 시작위치를 반환, LOCATE()는 INSTR와 동일하지만 파라미터의 순서가 반대
SELECT ELT(2, '하나', '둘', '셋'), FIELD('둘','하나','둘','셋'), FIND_IN_SET('둘','하나,둘,셋'), INSTR('하나둘셋','둘'), LOCATE('둘','하나둘셋');
 
-- FORMAT(숫자, 소수점 자릿수)
SELECT FORMAT(123456.123456, 4)
 
-- BIN(숫자), HEX(숫자), OCT(숫자)
SELECT BIN(31), HEX(31), OCT(31)
 
-- INSTERT(기준 문자열, 위치, 길이, 삽입할 문자열)
SELECT INSERT('abcdefghi' ,3, 4, '@@@@'), INSERT('abcdefhi',3,2,'@@@@');
 
-- LEFT(문자열, 길이), RIGHT(문자열, 길이)
SELECT LEFT('abcdefghi', 3), RIGHT('abdef', 3);
 
-- UCASE(문자열), LCASE(문자열)
SELECT UCASE('abcdEFGH'), LCASE('abcdEFGH');
 
-- UPPER(문자열), LOWER(문자열)
SELECT UPPER('abCD'), LOWER('abCD');
 
-- LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)
SELECT LPAD('이것이', 5, '##'), RPAD('이것은', 7, '@');
 
-- LTRIM(문자열), RTRIM(문자열)
SELECT LTRIM('     123'), RTRIM('123     ');
 
-- TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열)
-- LEADING(앞), BOTH(양쪽), TRAILING(뒤)
SELECT TRIM('   이것이   '), TRIM(BOTH 'ㅋ' FROM 'ㅋㅋㅋGOODㅋㅋㅋㅋㅋㅋㅋ');
 
-- REPEAT(문자열, 횟수)
SELECT REPEAT('*' 3);
 
-- REPLACE(문자열, 원래 문자열, 바꿀 문자열)
SELECT REPLCAT('이것이 Mysql', '이것이', 'This is');
 
-- REVERSE(문자열) : 문자열의 순서 거꾸로 만듬
SELECT REVERSE('abcd');
 
-- SPACE(길이)
SELECT CONCAT('123', SPACE(3), '456');
 
-- SUBSTRING(문자열, 시작위치, 길이) 또는 SUBSTRING(문자열 FROM 시작위치 FOR 길이)
-- 시작위치부터 길이만큼 문자를 반환
SELECT SUBSTRING('1234', 1,3);
 
-- SUBSTRING_INDEX(문자열, 구분자, 횟수)
-- 문자열에서 구분자가 왼쪽부터 횟수 번째까지 나오면 그 이후 오른쪽은 버린다. 횟수가 음수이면 오른쪽부터 센다
SELECT SUBSTRING_INDEX('www.naver.com', '.', 2);

-- 수학함수
-- ABS(숫자) : 절대값 반환
SELECT ABS(-100);
 
-- ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)
 
-- CEILING(숫자), FLOOR(숫자), ROUND(숫자)
-- 올림, 내림, 반올림
-- CEILING()와 CEIL()은 동일한 함수
 
-- CONV(숫자, 원래 진수, 변환할 진수)
SELECT CONV(5, 10, 2);
 
-- DEGREES(숫자), RADIANS(숫자), PI()
-- 라디안 값을 각도값으로, 각도값을 라디안 값으로 변환 / PI는 3.141592를 반환
 
-- EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)
 
-- POW(숫자1, 숫자2), SQRT(숫자)
-- 거듭제곱값, 제곱근을 구한다
-- POW()와 PWOER()는 동일한 함수
 
-- RAND() : 0 이상 1 미만의 실수를 구한다. // m <= 임의의 정수 < n // FLOOR(m+(RAND()*(n-m))
SELECT FLOOR(1 + (RAND()*(10-1));
 
-- SIGN(숫자) : 숫자가 양수, 0, 음수인지를 구한다. 1, 0, -1을 반환
 
-- -- 수학함수
-- ABS(숫자) : 절대값 반환
SELECT ABS(-100);
 
-- ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)
 
-- CEILING(숫자), FLOOR(숫자), ROUND(숫자)
-- 올림, 내림, 반올림
-- CEILING()와 CEIL()은 동일한 함수
 
-- CONV(숫자, 원래 진수, 변환할 진수)
SELECT CONV(5, 10, 2);
 
-- DEGREES(숫자), RADIANS(숫자), PI()
-- 라디안 값을 각도값으로, 각도값을 라디안 값으로 변환 / PI는 3.141592를 반환
 
-- EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)
 
-- POW(숫자1, 숫자2), SQRT(숫자)
-- 거듭제곱값, 제곱근을 구한다
-- POW()와 PWOER()는 동일한 함수
 
-- RAND() : 0 이상 1 미만의 실수를 구한다. // m <= 임의의 정수 < n // FLOOR(m+(RAND()*(n-m))
SELECT FLOOR(1 + (RAND()*(10-1));
 
-- SIGN(숫자) : 숫자가 양수, 0, 음수인지를 구한다. 1, 0, -1을 반환
 
-- TRUNCATE(숫자, 정수) : 숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버린다
 

-- 날짜 및 시간함수
 
-- ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)
SELECT ADDDATE('2020-01-01', INTERVAL 31 DAY), SUBDATE('2020-01-01', INTERVAL 31 DAY);
 
-- ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간)
SELECT ADDTIME('2020-01-01 23:59:59', '1:1:1'), SUBTIME('2020-01-01 23:59:59', '1:1:1'),
ADDTIME('23:59:59', '1:1:1'), SUBTIME('23:59:59', '1:1:1');
 
-- CURDATE(), CURTIME(), NOW(), SYSDATE()
-- CURDATE는 현재 연-월-일 / CURTIME는 시:분:초 / NOW와 SYSDATE는 연-월-일 시:분:초
 
-- YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)
-- 날짜 연,월,일,시,분,초,밀리초를 구한다
-- DAYOFMONTH()와 DAY()는 동일한 함수다.
--  CURRENT_DATE()는 CURDATE()의 별칭이다.
SELECT year(curdate()), month(curdate()), dayofmonth(curdate()),
hour(current_time()), minute(current_time()), second(current_time()), microsecond(current_time(6)), 
DATE_FORMAT( NOW(6), '%Y-%m-%d %H:%i:%s.%f' ),
DATE_FORMAT( CURRENT_TIMESTAMP(6), '%Y-%m-%d %H:%i:%s.%f' );
 
-- DATE(), TIME() : DATETIME 형식에서 연-월-일 및 시:분:초만 추출한다
SELECT DATE(NOW()), TIME(NOW());
 
-- DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1 또는 시간1, 날짜2 또는 시간2)
SELECT DATEDIFF('2020-12-31', NOW());
 
-- DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)
select dayofweek(current_time()), monthname(current_time()), dayofyear(current_time());
 
-- LAST_DAY(날짜) : 주어진 날짜의 마지막 날짜를 구한다.
select LAST_DAY('2020-02-01');
 
-- MAKEDATE(연도, 정수) : 연도에서 정수만큼 지난 날짜를 구한다.
select MAKEDATE(2020, 31);
 
-- MAKETIME(시,분,초) : 시분초를 이용해서 시:분:초의 TIME형식을 만든다
select maketime(13,1,10);
 
-- PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2) : ㅇ연월에서 개월만큼의 개월이 지난 연월을 구한다
select period_add(202001,11); -- YYYYMM + MM
select period_diff(202001, 201812); -- YYYYMM - YYYYMM
 
-- QUARTER(날짜) : 날짜가 4분기 중에서 몇 분기인지 확인
select QUARTER('2020-01-01');
 
-- TIME_TO_SEC(시간) : 시간을 초단위로 구한다.
select time_to_sec('12:11:10');

-- 시스템 정보 함수
-- USER(), DAATEBASE() : 현재사용자와 데이터베이스를 구한다.
select current_user(), database();
-- user(), session_user(), current_user()는 모두 동일하다.
-- database(0와 schema()도 동이한 함수다
 
-- found_rows() : 바로 앞의 select문에서 조회된 행의 개수를 구한다.
select * from salaries limit 1000;
select found_rows();
 
-- row_count() : 바로 앞의 insert, update, delete문에서 입력,수정,삭제된 행의 개수를 구한다. create, drop문은 0을 반환하고 select문은 -1을 반환
update 테이블명 set 컬럼명=값;
select found_rows();
 
-- version()
select version();
 
-- sleep(초) : 쿼리의 실행을 잠깐 멈춘다.
select sleep(5); -- 5초
 

그외 다양한 내장함수 : https://dev.mysql.com/doc/refman/8.0/en/functions.html

---

5. JSON 데이터

-- JSON 데이터
 
-- 검색
select JSON_OBJECT('emp_no', salary, 'from_date', 'to_date') AS 'JSON변환' from salaries limit 50;
 
set @json = '{"userTBL" :
[
	{"name":"임재범", "height":182},
    {"name":"이승기", "height":182},
    {"name":"성시경", "height":186}
]
}';
 
select JSON_VALID(@json) as 'valid', -- JSON 형식이 맞으면 true(1)을 반환
json_search(@json, 'one', '성시경') as 'search', -- 세번째 파라미터에 일치하는 문자열의 위치 반환, 두번째 파라미터 'one', 'all'중 하나 // one은 처음으로 매치되는 하나만 반환이지만 all은 매치된 모든것을 반환
json_extract(@json, '$.userTBL[2].name') as 'extract', -- 데이터 조회(추출)
json_insert(@json, '$.userTBL[0].mDate', '2009-09-09') as 'insert', -- 데이터 삽입
json_replace(@json, '$.userTBL[0].name', '홍길동') as 'replace', -- 데이터 교체
json_remove(@json, '$.userTBL[0]') as 'remove'; -- 데이터 삭제
 
select @json;

---