SQL : 05 함수 (1)

_플레이데이터 빅데이터캠프 공부 내용 _5/26

MySQL : 05 함수 (1) 단일 행 함수

1. 함수 종류

(1) 단일 행 함수

- 행 단위로 값을 처리하는 함수. 100행 처리 100행 결과.

- 한 행 처리하고 다음행. 한 행 처리하고 다음행. ...

- 단일행은 select, where 절에 사용 가능 (컬럼값 처리 필요할 때. 컬럼에 적용. 컬럼 들어가는 자리에.)

- 함수에 함수를 넣어 여러 처리를 한번에 할 수 있다.

    - CHAR_LENGTH(CONCAT('A','B'))

 

(2) 다중 행 함수 

- 여러 행의 값들을 묶어서 한번에 처리하는 함수. 100행 처리 100행 결과

- 집계함수(여러 행의 값을 집계하는), 그룹함수(묶어서 처리하는) 라고 한다.

- 집계하는 함수 뿐이다. (합계, 평균 ....)

- 여러 행을 집계하여 함수의 컬럼에 넣는 것.

- 다중행은 select, having 절에서 사용가능

    - where 절에는 사용할 수 없다. where절의 의미는 이거 자체가 행단위. 각각의 행단위로 조건을 부여하는 것이기에.

2. 문자열 처리 함수

: 함수에 전달하는 값이 문자열이거나 처리결과가 문자열인 함수들

2.1. 구문

(1) 글자 수 반환 char_length(v)

-  where 에서 많이 씀.

- 숫자, 한글 또한 한 글자씩.

-- select절에서
select char_length('abcd1&'), char_length('가나ㄱㅏ'); -- 6,  4

-- where절에서 조건으로
select emp_name, char_length(emp_name)
from  emp
where char_length(emp_name) >= 10;

 

(2) 대문자, 소문자 변환 upper(v), lower(v)

-- select절에서
select upper('DFdsfFKF'), lower('fdFLDJFdkf');

-- where절에서
select * from emp
where upper(emp_name) = 'PETER';

 

(3) 정수부 단위 구분자( , ) 넣기 format(대상값, 소수부 자릿수) 

- 정수부에 단위 구분자 "," 를 표시하고 지정한 소수부 자리까지만 문자열로 만들어 반환. 반올림함.

- 정수일 때는 소수점 자릿수 0 써주면 된다.

select format(10000000,0); -- 정수일 때는 소수점 자릿수 0 써주면 된다.
select format(12344.23123,3);

 

 

(4) 문자열 등 다른 타입 값 합쳐서 문자열로

    concat(값1, 값2, ...)  concat_ws(구분자, 값1, 값2, ...)   

- 문자열 아니더라도 가능. 문자열과 숫자 데이터 합쳐 문자열로 반환 가능

select concat('나이',30), concat('홍길동','님',' 안녕하세요', 122);
select concat('$', format(300000,0));

- CONCAT_WS(구분자, 값1, 값2, ....) 는 첫 번째로 전달한 구분자를 이용해 합친다.

SELECT CONCAT_WS('/','홍길동',20,'김나라',30)

>> 홍길동/20/김나라/30

 

 

(5) insert(기준문자열, 위치, 길이, 삽입문자열)

   : 기준 문자열의 위치(1부터 시작)에서부터 길이까지 지우고 삽입문자열을 넣는다. 

(바뀐다라고 생각하지 말고 길이까지 지우고 그 다음 넣는다고 생각하자. 지울 문자 길이, 삽입 문자열 길이 상관없다.)

- 어떤 위치의 값을 변경해주고 싶을 때

- 인덱스 1부터 시작하는 것을 주의하자. 세번째 매개변수는 인덱스가 아니라 길이를 말하는 것 주의.

-- 2번째 글자부터 4글자만 '안녕하세요' 변경
select insert('123456789',2,4,'안녕하세요');

(6) replace(기존문자열, 원래문자열, 바꿀 문자열)

   : 기준문자열의 원래문자열을 바꿀 문자열로 바꾼다.

   (바꾸고, 바뀌는 글자의 글자수는 상관없다. aaa를 가로 변경 이렇게 가능.)

-- 한글자일 필요 없음. aaa를 가로 변경 이렇게 가능. 여러 글자 지정할 수 있다.
select replace ('aaaaaabbbbcccccddddddaaaa','aaaa','가'); --가aabbbbcccccdddddd가 -- aa두개 남는 건 그대로
select replace('hello world','hello','안녕');  -- 안녕 world

 

(7) left(기준문자열, 길이)  right(기준문자열, 길이)

    : 기준문자열에서 왼쪽, 오른쪽의 길이만큼의 문자열을 반환한다.

select left('123456789',5); -- 대상문자열을 왼쪽에서 5글자 반환
select right('123456789',5); -- 대상문자열을 오른쪽에서 5글자 반환

(8) substring(기준문자열, 시작위치, 길이)

: 기준문자열에서 시작위치부터 길이 개수의 글자 만큼 잘라서 반환한다.

- 길이를 생략하면 마지막까지 잘라낸다.

select substring('123456789',4,3); -- 4번째 글자부터 3글자만 반환 456
select substring('1234567890',4);   -- 4번째 글자부터 나머지 모두 반환 4567890

 

 substring_index(기준문자열, 구분자, 개수): 

- 기준문자열을 구분자를 기준으로 나눈 뒤 개수만큼 반환. 

- 개수: 양수 – 앞에서 부터 개수,  음수 – 뒤에서 부터 개수만큼 반환

 

 

(9) ltrim(문자열), rtrim(문자열), trim(문자열)

: 문자열에서 왼쪽L, 오른쪽R, 양쪽TRIM 의 공백을 제거한다. 중간공백은 유지.!

-- 공백 제거
-- 별칭 준 것은 필수는 아니고 테이블 보았을 때 안 주면 공백 생긴 것 확인이 어려워서.
select trim('       aaa         ') as "v"; -- 좌우 공백 제거
select ltrim('       aaa         ') as "v"; -- 왼쪽 공백 제거
select rtrim('       aaa         ') as "v"; -- 오른쪽 공백 제거

- 공백 이외의 문자를 제거하고 싶을 때. 이 함수를 활용가능하다.

both '-' 양쪽 앞뒤     leading '-' 앞쪽     trailing '-' 뒤쪽

-- 공백 이외의 문자를 제거하고 싶을 때
select trim(both '-' from '--------adffa---------') as "v"; -- 양쪽에서 '-'를 없애겠다.
select trim(leading '-'from '--------adffa---------') as "v"; -- 앞쪽 '-' 제거
select trim(trailing '-'from '--------adffa---------') as "v"; -- 뒤쪽 '-' 제거

 

(10) lpad(기준문자열, 길이, 채울문자열), rpad(기준문자열, 길이, 채울 문자열)

- 기준문자열을 길이만큼 늘린 뒤 남는 길이만큼 채울문자열로 왼쪽l 오른쪽r에 채운다.

- 항상 일정한 글자수를 유지해야 할 때 쓰는 함수

- 기준문자열 글자수가 길이보다 많은 경우 나머지는 자른다.

- 테이블의 값 오른쪽 정렬(lpad)을 할 때 사용하기도 함.

select lpad('abcd',10, ' ') as "v"; -- 무조건 10자리수 맞추고 부족한 것은 공백을 왼쪽에 채워라. => 오른쪽 정렬됨.
select rpad('abcd',10, '-') as "v"; -- 무조건 10자리수 맞추고 부족한 것은 '-'을 오른쪽에 채워라.
select lpad('abcdfsdfdfsdsfd',10, ' ') as "v"; -- 무조건 10자리수 맞추기 위해 짜른다.

 

3. 숫자(수학) 처리 함수

3.1. 구문

(1) abs(값) : 절댓값 반환

select abs(-20);

 

(2) sign(값) : 값의 부호를 알려줌.

- 양수 : 1     ,  0  ,    음수  : -1

select sign(-10), sign(0), sign(120);

(3) mod(값1, 값2) : 나머지 연산 

- n1 % n2  = n1 mod n2 = mod(n1, n2) 셋 다 같음. 연산자 쓰면 되지만 함수 있다는 것을 알아야 함.

select 10 % 3,  10 mod 3,  mod(10,3);

 

(4) ceil()  floor() 

: 결과값이 정수. 무조건 소수점 첫째 자리에서 올림 or 내림. 정수값을 원할 때.

select ceil(3.231); 
select floor(3.834);

-- 15% 인상된 급여는 올림해서 정수로 표시하고, 별칭을 "SAL_RAISE"로 지정. 인상 급여(sal_raise)와 급여(salary) 간의 차액 조회.
select emp_id, emp_name, salary, ceil(salary*1.15) "SAL_RAISE", ceil(salary * 1.15)- salary "임상금액"
from emp;

 

(5) round(), truncate()

: 기준숫자에서 지정한 자릿수 이하에서 반올림 or 버린다. 반올림 버림 하는 자리를 지정할 수 있는 것.

- 정수부의 첫 번째 자리가 0. 이게 기준.(위치자리 기본값 = 0) 

    =>   -3   -2   -1   0 .  1   2   3   4  

자릿수가 음수이면 정수부분 자릿수   /    자릿수가 양수이면 소수부분 

- round() 함수는 자릿수 안 적어주면 default값(0)으로 인지한다. 

- truncate() 함수는 자릿수 꼭 줘어줘야 함. 정수부 첫 번째 자리라도 0 꼭 적어주기.

-- 반올림함수. 위치를 지정

select round(50.123), -- 50  -- 지정 안 하면 정수까지. 반올림. 
	   round(30.663), -- 31
       round(50.2323,2), -- 50.23 -- 소수점 2번째 자리 '아래서' 반올림. 2번째 자리까지 보여줌.
       round(30.3343,0), -- 30
       round(3423423.3234,-2); -- 3423400 -- 정수부 2번째(백의) 자리 '아래서' 반올림
       
-- 내림함수. 위치를 지정
select truncate(50.12,0), -- 50
	   truncate(50.67342,3), -- 50.673   -- 소수부도 3자리 '아래에서' 자름.
       truncate(3423423,-3); -- 3423000  -- 정수부도 -3자리 '아래에서' 자름.

 

4. 날짜, 시간 처리 함수

4.1. 구문

(1) date/time/datetime   :   +,  -  연산

=> 마지막 항목(date:일, time: 초, datetime: 초)의 값을 +/- => 계산 결과가 정수형으로 반환된다.

(2) 쿼리문 실행 시점의 현재 날짜와 현재 시간을 반환하는 함수
now()  : 현재 datetime   년-월-일 시:분:초
curdate()  : 현재 date    년-월-일
curtime()  : 현재 time    시:분:초

select now();  
select curdate();
select curtime();

 

- 조회보다는 삽입 insert 할 값으로 많이 씀.

(3) 날짜 또는 일시의 년, 월, 일 을 반환한다.
- year(날짜), month(날짜), day(날짜)

(4) 시간 또는 일시의 시, 분, 초, 밀리초를 반환한다.

- hour(시간), minute(시간), second(시간), microsecond(시간)  -- 1/1000

select year(now()), month(now()), day(now());
select hour(now()),minute(now()), second(now()), microsecond(now()); -- now가 밀리초 반환을 안 해서 0으로

 

(5) datetime( 년-월-일 시:분:초 ) 에서 날짜(date), 시간(time)만 추출한다.

  - date()  년-월-일

  - time()   시:분:초

 

(6) dayofweek(날짜)

: 날짜의 요일을 정수로 반환 (1: 일요일 ~ 7: 토요일)

select dayofweek(now());

-- 요일을 숫자로 반환 -> 3
select dayofweek(hire_date) "입사요일"
from emp

-- 출력형식문자열 함수 사용 -> 영문자로.Tuesday
select date_format(hire_date, '%W') "입사요일"
from emp

-- 한글 요일 표시할 방법  -> 화요일
select concat(substring('일월화수목금토', dayofweek(hire_date), 1), '요일') "입사요일"
from emp
where emp_id = 100;

 

(7) date_format(일시, 형식문자열)

: 일시를 원하는 형식의 문자열로 변환해서 반환한다.

-- 날짜 시간 형식 
select date_format(now(), '%Y년 %m월 %d일 %H시 %i분 %s초 %W');

 %Y  연도 4자리      %m 월 2자리 (01~12) %d 일 2자리 (01~31)

 %H  시간(00~23)    %h 시간(00~12)

 %i   분 (00~59)      %s 초 (00~59)

 %W 요일(영단어)    %w 요일 정수(0:일 , 1:월 ...)

 

 

 

_ 날짜 시간 연산 _

(1) adddate / subdate(DATETIME/DATE/TIME,  INTERVAL  값  단위)
날짜에서 특정 일시만큼 더하고(add) 빼는(sub) 함수.

기준일시 : 기준 DATETIME/DATE/TIME 

값 : 더하고 뺄 값

단위: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER (분기-3개월), YEAR

ex) addate('2020-12-12 12:12:12', interval 10 year) 기준 일시에서 년도를 더하고 10년 후     addate('2020-12-12 12:12:12', interval -10 year) 기준 일시에서 년도를 빼고

 

-- 날짜 연산 특정날짜만큼 더하고 빼고
select adddate(now(), interval 2 day);  -- now()에서 2일만큼 더하라. 시간은 동일하고.
select adddate(now(), interval 3 year); -- 3년 후
select adddate(curtime(), interval 3 hour); 3시간 뒤

-- 3년 빼기 adddate에서 -3해도 상관없어서 굳이.
select subdate(now(), interval 3 year); -- 3년전
select adddate(now(), interval -3 year); -- 3년 전

-- 부서이름(dept_name)이 'IT'인 직원들의 
-- '입사일(hire_date)로 부터 10일전', 입사일, '입사일로 부터 10일 후' 의 날짜를 조회. 

-- select dept_name, subdate(hire_date, interval 10 day), hire_date, adddate(hire_date, interval 10 day) from emp;
select dept_name, adddate(hire_date, interval -10 day), hire_date, adddate(hire_date, interval 10 day)
from emp
where dept_name = 'IT';

 

_ 두 시점에 대한 차이에 대한_

(2) datediff(날짜1, 날짜2)

    : 날짜1 – 날짜2 한 일수를 반환.   - 두 날짜의 차이를 날짜수를 반환

-- now() - 2021/5/26  뺀 날짜수
select datediff(now(), '2021-05-26');

 

(3) timediff(시간1, 시간2)

: 시간1-시간2 한 시간을 계산해서 반환 (뺀 결과를 시:분:초 로 반환) 두 시간의 차이를 반환

select timediff(curtime(), '11:40:50'); -- 현 시점보다 미래를 넣으면 - 값으로 반환

 

(4) timestampdiff(단위, 일시1, 일시2)

  :  일시2 - 일시1 연산을 단위 기준으로 계산하여 정수로 반환.

단위: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER (분기-3개월), YEAR

-- timestampdiff() 둘 날짜의 차이를 지정한대로 month, year
select timestampdiff(month, '2022-12-12', '2022-10-12'); # 뒤 - 앞 # 2
select timestampdiff(year, '2020-12-12', '2022-10-12'); # 뒤 - 앞 # 1
select timestampdiff(day, '2020-12-12', '2022-10-12'); # 뒤 - 앞 # 669

-- 근무 개월수 (입사일에서 현재까지의 달 수)를 계산하여 조회. 근무개월수 내림차순으로 정렬.
select emp_name, timestampdiff(month, hire_date, curdate()) "근무개월수"
from emp
order by 2 DESC;

5. 조건 처리 함수

5.1. if  ifnull  nullif

(1) if (조건수식, 참, 거짓)

    : 조건수식이  True이면 참을,  False이면 거짓을 출력한다.

select if(1 != 0, '참', '거짓'), if (1=0, '참', '거짓');

-- comm_pct 값이 null이면 '커미션이 없습니다'를 출력하고 아니면 comm_pct 값 출력.
select if(comm_pct is null, '커미션이 없습니다.', comm_pct) from emp;

 

(2) ifnull (기준컬럼(값), 기본값)

    : 기준컬럼(값)이 NULL값이면 기본값을 출력하고 NULL이 아니면 기준컬럼의 값을 출력

 -- null 이라면 지정한 값으로 출력 아니면 기본 컬럼 값 출력 
select ifnull(20, '데이터 없음'), ifnull(null, '데이터 없음');  -- 20    -- 데이터 없음

 

(3) nullif(컬럼1, 컬럼2)

    : 컬럼1과 컬럼2가 같으면 NULL을 반환, 다르면 컬럼1 을 반환

-- 두 개의 값을 비교. 같으면 null. 다르면 앞의 값 반환. 
select nullif(10,20); -- 10
select nullif(10,10); -- null

select ifnull(nullif(10,10),'값은값');

 

(4) coalesce(ex1, ex2, ex3, .....) 

    : ex1 ~ exn 중 null이 아닌 첫번째 값 반환.

select coalesce(null, null, 1, null, 10, 20, 30); -- 1 반환.

 

** EX.

-- 커미션 (salary * comm_pct)을 조회. 커미션이 없는 직원은 0이 조회되록 한다.

-- ifnull문으로
select emp_id, emp_name, salary,
	    ifnull (salary*comm_pct, 0) "커미션",
        salary * ifnull(comm_pct, 0) "커미션2"
from emp;

-- if문으로
select emp_id, emp_name, salary, if (comm_pct is null, 0, salary*comm_pct) "커미션"
from emp;

5.2. CASE 구문

- 함수가 아닌 연산자임.

- 1. 동등비교 용으로 2. 동등비교가 아닌 일반적   => 구문 작성 방법이 다르다.

 

(1) 동등 조건 비교 구문

- 이 구문은 해당 컬럼의 값이 비교값인지 아닌지만을 비교할 수 있다. 해당 비교값이면 출력값.

  그 외 대소비교 등 안되기에 2번째 구문 처럼 작성해야 함.

case 컬럼 when 비교값 then 출력값
              [when 비교값 then 출력값]
              [else 출력값]
              end

select case ifnull(dept_name,1) 
when 'IT' then '전산실'  
when 'Finance' then '회계부'  
when 'Purchasing' then '구매부' 
								   
when 1 then '부서없음' 
-- when null then '부서없음' 이렇게 못함. 컬럼명 비교는 is null로 하기에
--   => ifnull(dept_name,1)
                                    
else dept_name end  -- dept_name 값 중 만족하는 조건이 없다면 그 값은 null로 출력되기에 else로 지정해주기.
as "부서명" -- 여기에 as 없어도 별칭인 거 알기.

from emp;

 

 

(2) 조건 비교 구문 (동등비교 제외)

case when 조건 then 출력값
       [when 조건 then 출력값]
       [else 출력값]
       end

-- 급여 등급은 10000이상이면 '1등급', 10000미만이면 '2등급' 으로 나오도록 조회

select salary,
	   case when salary >= 10000 then '1등급' else '2등급' end "salary level"
from emp;


-- 급여 인상분은 부서이름이 'IT' 이면 급여(salary)에 10%를 'Shipping' 이면 급여(salary)의 20%를 'Finance'이면 30%를 나머지는 0을 출력
select dept_name,
       case dept_name when 'IT' then salary*0.1 when 'Shipping' then salary*0.2 when 'Finance' then salary*0.3 else 0 end "급여 인상분"
from emp;

 

(3) case 를 이용한 정렬. 정렬 순서를 내가 지정하고 싶을 때(order by 에서 case문)

 

-  EX. 직원들의 모든 정보를 조회한다.

   단 정렬은 업무(job)가  'ST_CLERK', 'IT_PROG', 'PU_CLERK', 'SA_MAN' 순서대로 먼저나오도록 한다.

   (나머지 JOB은 상관없음)

select * 
from emp
order by case job 
        when 'ST_CLERK' then 1
        when 'IT_PROG' then 2
        when 'PU_CLERK' then 3
        when 'ST_MAN' then 4
        else job end asc;