DB/MYSQL
MySQL 데이터형식 및 변수
AKI
2020. 1. 23. 02:51
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;
반응형