DATABASE_MYSQL-FUNCTION

📚 DB ( DataBase )

📚 MYSQL-FUNCTION

MYSQL-Function 내장 함수

MYSQL에서 사용하는 여러 내장 함수들이 있다.
가져오려는 데이터를 JAVA에서 조건을 주는 것보다 MYSQL FUNCTION을 사용해서 주는게 더 유용하다.

숫자 관련 함수

• ABS(숫자)
  절대값을 반환

-> 5 0 5

select abs(-5), abs(0), abs(5)
from dual;

• CEILING(숫자)
  값보다 큰 정수 중 가장 작은 수
  ceil, ceiling 둘 다 사용 가능

-> 13 -12

select ceil(12.2), ceiling(-12.2)
from dual;

• FLOOR(숫자)
  값보다 큰 작은 중 가장 큰 수

- 12 -13

select floor(12.6), floor(-12.6)
from dual;

• ROUND(숫자,자릿수)
  숫자의 자릿수를 기준으로 반올림

- 1526 1526 1526.4 1530

select round(1526.366), round(1526.366, 0), round(1526.366, 1), round(1526.366, -1)
from dual;

• TRUNCATE(숫자,자릿수)
  숫자의 자릿수를 기준으로 버림

- 1526 1526.3 1526.36 1520

select truncate(1526.366, 0), truncate(1526.366, 1), truncate(1526.366, 2), truncate(1526.366, -1)
from dual;

• POW(X,Y) or POWER(X,Y)
  X의 Y승
  pow, power 둘 다 사용 가능

- 8 8

select pow(2, 3), power(2, 3)
from dual;

• MOD(분자, 분모)
  분자를 분모로 나눈 나머지

- 2 2

select mod(8, 3), 8 % 3
from dual;

• GREATEST(숫자1, 숫자2, 숫자3 ..)
  주어진 수에서 가장 큰 수를 반환

- 9

select greatest(4, 3, 7, 5, 9)
from dual;

• LEAST(숫자1, 숫자2, 숫자3 ..)
  주어진 수에서 가장 작은 수를 반환

- 3

select least(4, 3, 7, 5, 9)
from dual;

문자 관련 함수

• ASCII(문자)
  문자의 아스키 코드 값 리턴

- 48 65 97

select ascii('0'), ascii('A'), ascii('a')
from dual;

• CONCAT(‘문자열1’, ‘문자열2’, ‘문자열3’)
  문자열들을 결합

- 100번 사원의 이름 Steven King

select concat(employee_id, '번 사윈의 이름 ', first_name, ' ', last_name)
from employees
where employee_id = 100;

• INSERT(‘문자열’, 시작위치, 길이, ‘새로운 문자열’)
  문자열의 시작위치부터 길이만큼 새로운 문자열로 대치

- hello js !!

select insert('helloabc!!, 6, 3, 'js ')
from dual;

• REPLACE(‘문자열’, ‘기존문자열’, ‘바뀔문자열’)
  문자열 중 기존 문자열을 바뀔 문자열로 변경

- hello js !!

select replace('helloabc!!', 'abc', ' js ')
from dual;

• INSTR(‘문자열’, ‘찾는 문자열’)
  문자열 중 찾는 문자열의 위치 값을 리턴

- 7

select insert('hello js !!, 'js')
from dual;

• MID(‘문자열’, 시작위치, 개수)
  문자열 중 시작위치부터 개수만큼 리턴

- js !!

select mid('hello js !!, 7, 5)
from dual;

• SUBSTRING(‘문자열’, 시작위치, 개수)
  문자열 중 시작위치부터 개수만큼 리턴

- js !!

select substring('hello js !!, 7, 5)
from dual;

• LTRIM(‘문자열’)
  문자열 중 왼쪽의 공백을 제거

- (hello js !! ) 

select ltrim(' hello js !! ')
from dual;

• RTRIM(‘문자열’)
  문자열 중 오른쪽의 공백을 제거

- ( hello js !!) 

select rtrim(' hello js !! ')
from dual;

• TRIM(‘문자열’)
  양쪽 모두의 공백을 제거

- (hello js !!) 

select trim(' hello js !! ')
from dual;

• LCASE(‘문자열’) or LOWER(‘문자열’)
  모든 문자를 소문자로 변경

- hello js !!

select lower('HeLLo Js !!'), lcase('HeLLo Js !!')
from dual;

• UCASE(‘문자열’) or UPPER(‘문자열’)
  모든 문자를 대문자로 변경

- HELLO JS !!

select upper('HeLLo Js !!'), ucase('HeLLo Js !!')
from dual;

• LEFT(‘문자열’, 개수)
  문자열 중 왼쪽에서 개수만큼 추출

- hello

select left('hello js !!', 5)
from dual;

• RIGHT(‘문자열’, 개수)
  문자열 중 오른쪽에서 개수만큼 추출

- js !!

select right('hello js !!', 5)
from dual;

• REVERSE(‘문자열’)
  문자열을 반대로 나열

- !! sj olleh

select reverse('hello js !!')
from dual;

날짜 관련 함수

• NOW() or SYSDATE() or CURRENT_TIMESTAMP()
  현재 날짜와 시간 리턴

- 2022-03-16 21:09:17 2022-03-16 21:09:17 2022-03-16 21:09:17

select NOW(), SYSDATE(), CURRENT_TIMESTAMP()
from dual;

• CURDATE() or CURRENT_DATE()
  현재 날짜 리턴

- 2022-03-16 2022-03-16 

select CURDATE(), CURRENT_DATE()
from dual;

• CURTIME() or CURRENT_TIME()
  현재 시간 리턴

- 21:09:17 21:09:17

select CURTIME() or CURRENT_TIME()
from dual;

• DATE_ADD(날짜, INTERVAL 기준 값)
  날짜에서 기준 값 만큼 더한다

- 23:09:17

select DATE_ADD(now(), INTERVAL 2 hour)
from dual;

• DATE_SUB(날짜, INTERVAL 기준 값)
  날짜에서 기준 값 만큼 뺀다

- 18:09:17

select DATE_SUB(now(), INTERVAL 2 hour)
from dual;

• YEAR(날짜)
  날짜의 연도 리턴

- 2022

select YEAR(now())
from dual;

• MONTH(날짜)
  날짜의 월 리턴

- 3

select MONTH(now())
from dual;

• MONTHNAME(날짜)
  날짜의 월을 영어로 리턴

- March

select MONTHNAME(now())
from dual;

• DAYNAME(날짜)
  날짜의 요일을 영어로 리턴

- Wednesday

select DAYNAME(now())
from dual;

• DAYOFMONTH(날짜)
  날짜의 월별 일자 리턴

- 16

select DAYOFMONTH(now())
from dual;

• DAYOFWEEK(날짜)
  날짜의 주별 일자 리턴[일요일(1),월요일(2)..토요일(7)]

- 4

select DAYOFWEEK(now())
from dual;

• WEEKDAY(날짜)
  날짜의 주별 일자 리턴[월요일(0),화요일(1)..일요일(6)]

- 2

select WEEKDAY(now())
from dual;

• DAYOFYEAR(날짜)
  일년을 기준으로 한 날짜까지의 일 수(365일중 x일)

- 75

select DAYOFYEAR(now())
from dual;

• WEEK(날짜)
  일년 중 몇 번째 주

- 11

select WEEK(now())
from dual;

• FROM_DAYS(날수)
  00년 00월 00일부터 날 수 만큼 경과한 날의 날짜 리턴

• TO_DAYS(날짜)
  00년 00월 00일부터 날짜까지의 일자 수 리턴

• DATE_FORMAT(날짜, ‘형식’)
  날짜를 형식에 맞게 리턴

- 2202 March 16 PM 09 09 17

select data_format(now(),'%Y %M %e %p %l %i %S')
from dual;

날짜 형식

format	description	format	description	format	description
%Y	년, 2020	%y	년, 20
%b	월, Jan..Dec	%M	월, January..December	%m	월, 01,02..12
%d	일, 00, 01, 02 .. 31	%e	일, 0, 1, 2 .. 31
%a	요일, Sun .. Sat	%W	요일, Sunday..Saturday	%w	요일, 0:일요일 .. 6:토요일
%p	AM or PM	%H
%H	시간, 01, 02 .. 23	%h	시간, 01, 02 .. 12	%l(알파벳 L 소문자)	시간, 01, 02 .. 12
%k	시간, 0, 1, 2 .. 23	%I(알파벳 i 대문자)	시간, 1,2 .. 12	%T	시간, 24-hours(hh:mm:ss)
%i	분, 00..59	%S	초, 00..59	%s	초, 00..59
%j	1년중 X일, 001..365

논리 관련 함수

• IF(논리식, 값1, 값2)
  논리식이 참이면 값1 리턴, 거짓이면 값2 리턴

• IFNULL(값1, 값2)
  값1이 NULL이면 값2로 대치, NULL이 아니면 값1리턴

• NULLIF(값1, 값2)
  값1 = 값2가 true면 NULL, 그렇지 않으면 값1리턴

- 크다 3 b a

select if(3 > 2, '크다', '작다'), nullif(3, 3), nullif(3, 5), ifnull(null, 'b'), ifnull('a', 'b')
from dual;

그룹 함수 ( Aggregation Function )

집계 ( 그룹, 집합 ) 함수는 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등을 하나의 결과로 반환한다.

• SUM(필드명)
  그룹의 누적 합계를 반환한다.

-> 급여의 총합을 검색

select sum(salary) 급여총합
from employees;

• AVG(필드명)
  그룹의 평균을 반환한다.
  NULL 값은 제외하고 평균을 구한다 !!

-> 급여의 평균 ( 봉급 * 커미션 비 ) 을 검색 ( 소수 2자리까지 ) 
-> 커미션 비가 NULL 인 값을 빼고 계산함

select round(avg(salary * commission_pct), 2) 평균급여
from employees;

-> 커미션 비가 NULL 값인 것은 0 으로 칭하고 평균 구하기
-> 커미션 비가 NULL 인 값을 0 으로 하고 계산함

select round(avg(salary * (IFNULL(commission_pct), 0)), 2) 평균급여
from employees;

• COUNT(필드명)
  그룹의 총 개수를 반환한다.
  NULL 값은 제외하고 카운팅 한다 !!

-> 사원수를 검색

select count(employee_id) 사원수
from employees;

-> 부서수를 검색

select count(department_id) 부서수
from employees;

-> 중복없이 부서수 검색

select count(distinct department_id) 부서수
from employees;

• MAX(필드명) & MIN(필드명)
  그룹의 최대값과 최소값을 반환한다.

-> 80번 부서에서 근무하는 최대 급여, 최소 급여 검색

select max(salary) 최대급여, min(salary) 최소급여
from employees
where department_id = 80;

DATABASE FUNCTION 마무리

DB에서 사용하는 함수에 대해 알아보았다.
거의 JAVA에서 사용하던 것과 비슷해서 어려움은 없었다.
날짜 관련 함수나 NULLIF, IFNULL 같은 경우는 좀 헷갈리긴 했다.
함수들 중에서도 자주 사용되는 것들이 있으니 잘 기억해서 사용해야 할 것 같다.
그리고 함수를 JAVA에서 조건을 주는 것보다 MYSQL FUNCTION을 사용해서 주는게 더 유용하다고 하니 잘 알아두면 좋을 것 같다.