MySQL 함수 모음zip

< @변수선언 > 

SET @VAR1 = '2022-12-12'; 
SET @VAR2 = NOW( );
SET @VAR3 = 3.14;   
=> 이런식으로 변수선언해두고 조회할 때 편하게 사용 
 ( connection 사용하는 동안만 유지 - 임시 ) 

SELECT * FROM emp WHERE hire_date = @VAR1; 
( @까지 포함해서 변수이름이다. )
  

< 형변환 함수  CAST / CONVERT / FORMAT > 
=> 셋다 문법만 다르고 기능은 똑같다.

  
SELECT CAST (10  AS  DECIMAL(10, 5));       => AS
select  CONVERT(10, decimal(10,5));        => , 쉼표 
select  FORMAT( 데이터,  소수점 몇개까지 );    => 정수부분에 , 쉼표로 끊어쓰기 해서 표현 

select format( 3.141592, 2); => 3.14  (반올림 된다)  

=> 10.00000

 

 ( 날짜 구분자 다른걸로 써놓은걸 정상으로 바꿔주기도 함 ) 
select cast('20220703' as date); 
select cast('2022$07$03' as date); 

date - 날짜   => 시간까지 있는 data 를 날짜까지만으로 짜를 수 있다. 
datetime - 날짜 + 시간

- as signed integer  - 정수로만 ( 반올림) 



< 자동형변환 > 

- 문자열 -> '숫자'로만 구성 => 자동으로 int로 변환 
- 문자열 -> '숫자.숫자' 로만 구성 => 자동으로 double로 변환
select '3.14' + 1;       => 4.14
select '100' + '200';    => 300 
select '100가나다' + '200가나다'  => 300 

< 참거짓 1/0 > 
1 - true 
0 - false
select 7=7; => 1 
select 10-5=4; =>0  


[ 조건함수  IFNULL / NULLIF / IF / CASE ] 

**< IFNULL( 데이터, 변환값 ) >
=> 만약 데이터가 NULL 이면 변환값으로 바꿔줘 
- 다른 DATA TYPE 으로 변환할 수 있다. 

SELECT last_name, ifnull(commission_pct,  '보너스 없음') from emp;

< NULLIF (데이터1, 데이터2 ) > 
=> ( 데이터1 = 데이터2 ) 같으면 null 리턴
     다르면 데이터1 리턴 
( 두개 값이 equal 한지 판단할 때 사용)  

select nullif(100, 100); => null
select nullif(100, 200); => 100


**< IF ( 조건, 참결과, 거짓결과 ) > 

select last_name, IF( 커미션 is not null, 커미션, '못받는다') from emp;

select last_name, if(salary>=15000, '임원', if(salary>=10000, '부장', if(salary>=5000, '과장', '대리/사원'))) from employees; 

=> ( if 의 거짓 부분에 if 다시 주면 => java 에서 else if 역할 ) 
( 거짓 = else )

 

<** CASE >   
select
case salary   
when salary>=15000 then '임원'
when salary>=10000 then '부장'
when salary>=5000  then '과장'
else '대리/사원'
end 직급
from employees;

- 알리아스 end 끝나고 쓴다 

- when 은 else 개념 내포. 15000 이상이 아니면, 15000미만이라는 else 의미 내포 
=> when 절 순서 중요. 걸러지는 개념 
   만약 처음에 salary>1000 then '임원'
   조건이 나오면, 다 처음에 걸러져서 => 나머지는 실행 안됨 



< length( ) > 문자열 byte 길이  
영어나 숫자일땐(1byte), 사실상 글자수와 같지만, 
한글 사용시 개당 3개(byte) 길이. 
select length('안녕하세요');   => 3*5 = 15
select char_length('안녕하세요');  => 5 
 
< char_length( ) > 문자열 길이

=> 글자수 길이 ( 한글 포함 글자수만 ) 
select char_length('hello world!')+length(1234); => 12+4=16

=> 이건 데이터 조회할 때, 이름처럼 데이터마다 길이가 다른거에 대해
길이만큼 뭐 해줘 하는 식으로 많이 쓰인다, 

< field( target, 데이터, 데이터, 데이터... ) > 인덱스 
select field('문자', '데이터, '데이터', '데이터' ); 
=> 문자가 데이터중 몇번 째 인덱스에 있는지
select field(2022, 2001,2013,1021,2022); => 4 

 

< instr( 데이터, target ) > 인덱스 
select instr('일이삼사', '이');   => 2

select ~ where instr(데이터, target)=5; 
=>인덱스 5번에 데이터에서 target 포함한거 찾아줘    
(5번 이내에) 

 

< locate( target, 데이터 ) > 인덱스 
select locate( '삼사', '일이삼사');   => 3 

 

 

=> 뭐 이런 함수 써서 인덱스를 찾았다면 

 

< ** substring ( 데이터, 인덱스부터, 몇개 ) > 
 
substring ( 데이터, 2 ,3 ); 2번째부터 3개 

2022-07-04 

select substring( hire_date, 1,4 ) from emp;   => 2022 

select substring( hire_date, 6,2 ) from emp;  => 07 


< round( 데이터, 자리수) > 반올림 
select round(3.141592, 3); => 3.142

select round(16666.42, -1);  => 16670  
- 자리수에서 -1 주면 10의자리까지 반올림  
 -2 주면 100의자리까지 반올림
( truncate 도 마찬가진데 버리면서 보여줌 ) 

< *truncate( 데이터, 자리수 )  > 버림 
select truncate(3.141592, 3); => 4.141 

< mod ( A , B ) > 나머지 

select mod(100, 3); => 1

select id, if(mod(employee_id, 2)=0, '짝수사번', '홀수사번'); 





<** insert  ( 데이터, 인덱스부터, 몇개,  이걸로 대체  >  -  지정 숫자 갯수만큼 삭제하고 insert  

select  insert( 'abcdef ',  3,  2,  ' - ' ); 
문자열에서 3번째 문자부터 2개를 삭제하고, 그자리에 - 를 넣어라 
( - 는 하나만 들어간다. => repeat 사용 ) 
( 인덱스로 줘서 안전 / 인덱스 세는 작업 필요 )  

< replace ( 데이터, 타겟, 이걸로 대체 ) >  - 지정한 문자열을 바꿔라 
select replace( 'abcedf' , 'cef' , '*' );
데이터에서 cef 문자열을 * 로 바꿔라 

 

( *주의- 이건 의도치 않게, 데이터의 다른 부분에 같은 문자열이 있을시 
그 부분을 바꿔버릴 수 도 있다. 
확실히 그런 경우가 아닐 때 사용 ) 


< repeat ( 데이터, 몇번 ) > 
select repeat( 'abc', 3) 
end;                          =>abcabcabc
데이터를 3번 반복해서 붙여서 보여주라. 
(end 해줘야 한다.) 

=> end 줄 때,      from ~ "여기" where~  
넣어줘야한다.

 from 끝나고 바로 넣어줌  

< 전화번호 번호 가리기 >
select insert (phone_number, 10, 13, repeat('*', 4) ) from emp
end;  
=> 010-1132-****

< upper, lower ( 문자열 ) >  대문자 소문자화 

( MySQL 은 조회할때 등, 문자열 알파벳 대소문자 구분 X 
  / ORACLE 에서는 대소문자 구분 O - 오라클에서  많이 사용 )

select upper('stRiNg');   => STRING
=> 조회해서 보여줄 때, 일괄적으로 바꿔서 보여줄 때 사용


< 날짜 함수 > 
now(); = sysdate(); 

=> 날짜 + 시간 

curdate(); 날짜 

curtime(); 시간 

date/ time / year / month / day / hour / minute / second

select date(now()), time(now()), year(now()), month(now()), day(now()), hour(now()), minute(now()), second(now()); 

now() 에서 year 만 보여줘 month만 보여줘 
이런식으로 뽑아서 쓸 수 있다. 

select hire_date from emp where year(hire_date) = '2006'; 
select hire_date from emp where month(hire_date) = '6'; / '06' 둘다됨 


< adddate / subdate ( 날짜데이터, interver 기간 ) > 날짜 계산 

select adddate( 날짜데이터, interver 4 year );  4년 더하기 

select subdate( 날짜데이터, interver 1 day );  하루 빼기 


< addtime / subtime ( 시간데이터, 시간 ) > 시간 계산 
select addtime( '2022-12-12 18:00:00', '2:00:00'); 
=> 시간은 그냥 쉼표로 바로 준다. 
=> 날짜전체로 줘도, 시간만 더한다.   
select addtime( curtime(), '2:00:00'); 

< datediff / timediff ( 데이터, 데이터 ) > 날짜,시간 차이 계산 
select datediff(now(), '2022-06-20'); 
select timediff(time(now()), '16:00:00'); 
( 차이는 앞에꺼에서 뒤에껄 뺀다. 
  앞 데이터 - 뒤 데이터  
더 미래랑 비교하는거면 음수로 나옴 ) 
- datediff 한거 7 로나눠서 ( format 으로 정수로 주면)  그게 week diff 가 되겠지 



< period_diff( 연월, 연월 ) > month 차이 
select period_diff('202307', '202207'); => 12
=> 데이터 연도랑월만 (- 없이) 붙여서 줘야 한다. 

< dayofweek(날짜) / weekday(날짜) > 
-dayofweek(now()) => 1 / 일요일 시작 
-weekday(now())  => 0 / 월요일 시작 

<** date_format( 날짜, '포맷' ) > 

select date_format(now(), '%Y&%m&%d%a');

- 구분자 마음대로 지정 

- 포맷 약속값 (대소문자 구분)   

%Y 4자리연도 %y 2자리연도 %m 2자리월 %c 1자리월 %M 영문월  
%d 2자리일 %e 1자리일
%a 요일(축약) %W 요일(전체) 
%H 24시간 %I 12시간
%i 2자리분 %S 2자리초 

=> select hire_date from emp where date_format(hire_date, %m)=06; 
      ( m 달 부분이 06 인 데이터를 조회 ) 

포맷만 잘 사용하면 사실상 날짜는 다 가지고 놀 수 있다.