<실습> 2.mysql 내장함수(날짜,문자,숫자,JSON 등) 정리

-- 데이터 형식

-- 문자 데이터 형식

-- 날짜와 시간 데이터 형식

-- MYSQL 고급 / 내장함수 및 JSON 예제

-- 날짜 함수

select cast('2020-10-19 12:23:21.123' AS DATE) as 'DATE' ;

select cast('2020-10-19 12:23:21.123' AS TIME) as 'TIME' ;

select cast('2020-10-19 12:23:21.123' AS DATETIME) as 'DATETIME' ;

-- 변수

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 구문

set @myVar1 = 1;

prepare myQuery

from 'select @myVar4, Name from userTbl order by height limit ?';

execute myQuery using @myVar1;

-- 데이터 변환 함수 cast() , avg() --명시적인 형변환 방법

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/11' as date); 

select cast('2020$12$11' as date);

select cast('2020&12&11' as date);

-- 응용

select num, concat(cast(price as char(10)),'x',cast(amount as char(4)),'=') as '단가x수량',

price*amount as '구매액'

from buytbl;

-- 암시형 형변환

select '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 처리)

select concat('100','200'); -- 문자와 문자를 연결

select concat(100,'200'); -- 정수와 문자를 연결(정수가 문자로 연결되서 처리)

select 1 > '2mega'; -- 정수인 2로 변환되어 비교 틀린경우 0 반환

select 3 > '2MEGA'; -- 정수인 2로 변환되어 비교 맞는경우 1 반환

SELECT 0 = 'mega2'; -- 문자는 0으로 변환됨

-- mysql 내장함수

select if (100>200,'참이다','거짓이다'); -- 앞에 비교값이 참이면 두번째 값 출력, 거짓이면 세번째 값출력

select ifnull(null,'널이군요'), ifnull(100,'널이군요'); -- null 이면 두번째 값 출력

select nullif(100,100), ifnull(200,100); -- 두 값이 같으면 null 출력, 다르면 앞에 값 출력

select case 10

when 1 then '일'

        

when 5 then '오'

        

when 10 then '십'

        else '모름'

        end;

        

select ascii('A'), CHAR(65);        

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

SELECT bit_length('가나다'),char_length('가나다'),length('가나다');

SELECT concat_ws('/','2020','01','01'); -- / 로 합치기

select elt(1,'하나','둘','셋'),

field('둘','하나','둘','셋'),

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

instr('하나둘셋','둘'),

locate('둘','하나둘셋');

-- 문자열 함수

select format(123456.123456,4); -- 소숫점 4자리까지 출력 반올림되서 출력됨

select bin(31),hex(31),oct(31); -- 2진수 16진수 8진수

select insert('abcdefg',3,4,'@@'),insert('abcdefg',1,2,'@@'); -- 지정 위치에 문자 대체

select left('abcdefg',3),right('abcdefg',3); -- 왼쪽 3번째 까지 해당되는 문자 출력

select lower('abcdefg'),upper('abcdefg'); -- 소문자로 변환, 대문자로 변환

select lpad('안녕',5,'###'),rpad('안녕',5,'###'); -- 문자길이를 총 5개로 잡고 ### 추가

select ltrim('  안녕'),rtrim('  안녕'); -- 왼쪽오른쪽 공백 제거

select trim('   공백제거  '), trim(both 'ㅋ' from 'ㅋㅋㅋ안녕ㅋㅋㅋㅋ');

select repeat('안녕',3); -- 반복

select replace('안녕 친구','안녕','hello'); -- 문자열 치환

select reverse ('hello'); -- 거꾸로 출력

select concat('안녕',space(10),'친구');

select substring('안녕친구들',3,2); -- 3번째 문자 부터 2번째 까치 출력

select substring_index('hi.my.name.is','.',2), -- 두번째 점 이후로 버린다.

substring_index('hi.my.name.is','.',-2);

-- 수학함수

select abs(-400); -- 절대값 구하기

select ceiling(4.7),floor(4.7),round(4.7);-- 올림 내림 반올림

select conv('AA',16,2),CONV(100,10,8);-- 진수끼리 변환 하는 함수 AAR가 16진수 인데 2진수로 바꿔

SELECT MOD(157,10),157%10, 157 MOD 10; -- 나머지 값 구하기

SELECT RAND(), floor(1+(RAND() * (6-1)));-- 임의의 값 구하기

SELECT truncate(12345.12345,2),TRUNCATE(12345.12345,-2);

-- 날짜및 시간 함수

SELECT adddate('2020-01-01',INTERVAL 31 DAY),ADDDATE('2020-01-01',INTERVAL 1 MONTH);

SELECT subdate('2020-01-01',INTERVAL 31 DAY),subdate('2020-01-01',INTERVAL 1 MONTH);

SELECT addtime('2020-01-01 23:59:59', '1:1:1'),addtime('15:00:00','2:10:10');

SELECT subtime('2020-01-01 23:59:59', '1:1:1'),SUBTIME('15:00:00','2:10:10');

select year(curdate()),MONTH(curdate()),DAYOFMONTH(curdate());

SELECT hour(curtime()), MINUTE(current_time()), SECOND(current_time()), MICROSECOND(current_time());

SELECT DATE(NOW()),TIME(NOW());

SELECT datediff('2020-01-01',NOW()), timediff('23:23:59','12:11:10');

SELECT dayofweek(curdate()),monthname(curdate()),dayofyear(curdate());

SELECT LAST_DAY('2020-02-01');

SELECT makedate(2020,32);

SELECT maketime(12,11,10);

SELECT period_add(202001,11),period_diff(202001,201812); 

SELECT quarter('2020-07-07');

SELECT time_to_sec('12:11:10');

SELECT current_user(),database();

SELECT * FROM usertbl;

SELECT found_rows();

SELECT ROW_COUNT(); -- UPDATE, DELETE 했을때 몇개 했는지 반환

SELECT version();

SELECT sleep(5);

SELECT '5초후에 보입니다';

-- JSON 데이터

SELECT JSON_OBJECT('name',name,'height',height) AS 'JSON 값'

FROM usertbl

WHERE height >= 180;

set @json='{ "userTBL":

[

{"name":"임재범","height":182},

{"name":"이승기","height":182},

{"name":"성시경","height":186}

    ]

}';

select json_valid(@json); -- JSON 데이터가 맞으면 1 반환

select json_search(@json,'one','성시경'); -- 성시경이 있는 위치 를 숫자로 반환

select json_extract(@json,'$.userTBL[2].name'); -- 위치를 주면 값을 반환

select json_insert(@json,'$.userTBL[0].mDate','2009-09-09'); -- 데이터 추가하기

select json_replace(@json,'$.userTBL[0].name','홍길동'); -- 데이터 치환

select json_remove(@json,'$.userTBL[0]') -- 삭제