숫자 함수, 문자열 함수, 날짜 함수, 그룹 함수, Group by & Having 절

1. 숫자 함수

숫자 함수는 다양한 수학적 계산을 실시하는 함수들이다.

• abs(숫자): 절대값을 구한다.
• ceil(숫자): 소수점 이하 올림.
• floor(숫자): 소수점 이하 버림.
• round(숫자, 자릿수): 자릿수를 기준으로 반올림.
• truncate(숫자, 자릿수): 자릿수를 기준으로 반올림.
• pow(x, y) or power(x, y): x의 y승.
• mod(분자, 분모): 분자를 분모로 나눈 나머지 값.
• greatest(숫자1, 숫자2, ... , 숫자100): 주어진 숫자 중 가장 큰 값을 반환.
• least(숫자1, 숫자2, ... , 숫자100): 주어진 숫자 중 가장 작은 값을 반환.

select abs(100), abs(-100);

select ceil(10.0), ceil(10.1);

select floor(10.0), floor(10.1);

select round(10.0), round(10.4), round(10.5), round(10.7), round(10.77);
select round(173.555, 0), round(173.555, 1), round(173.555, 2), round(173.555, 3), round(173.555, -1);
 
-- select truncate(10.0), truncate(10.4), truncate(10.5), truncate(10.7); => truncate()는 자릿수 설정이 반드시 필요한듯...?
select truncate(173.555, 0), truncate(173.555, 1), truncate(173.555, 2), truncate(173.555, 3), truncate(173.555, -1);

select pow(10, 2);
select power(10, 2);

select mod(10, 3);

select greatest(10, 20, 55, 10, 60);
select least(10, 20, 55, 10, 60);

-- 1. 사원들의 사원번호와 급여를 가져온다. 급여는 10% 인상된 급여를 가져오며 소수점 이하는 올린값, 버린값, 반올림한 값 모두 가져온다.
select emp_no, salary * 1.1, ceil(salary * 1.1), floor(salary * 1.1), round(salary * 1.1)
from salaries;

 

2. 문자열 함수

문자열 함수는 문자열 데이터에 대한 작업을 실시할 수 있다.

• concat(문자열1, 문자열2, 문자열3...): 문자열을 합친다.
• insert(문자열, 시작위치, 길이, 새로운 문자열): 문자열을 선택, 시작위치 선택, 시작위치부터의 길이 선택, 시작위치 ~ 길이까지의 문자열 제거, 시작위치에 새로운 문자열 삽입
• replace(문자열, 기존 문자열, 새로운 문자열): 문자열을 선택, 기존 문자열 선택, 선택된 기존 문자열을 새로운 문자열로 대체
• instr(문자열1, 문자열2): 문자열1에서 문자열2를 찾아 위치를 반환함. 위치는 1부터 시작하며, 문자열2를 찾지 못할 경우 0을 반환.
• left(문자열, 개수): 문자열의 좌측부터 개수만큼 가져온다.
• right(문자열, 개수): 문자열의 우측부터 개수만큼 가져온다.
• mid(문자열, 시작위치, 개수): 문자열의 시작위치부터 개수만큼 가져온다.
• substring(문자열, 시작위치, 개수): 문자열의 시작위치에서 개수만큼 가져온다.
• ltrim(문자열): 문자열의 좌측 공백을 제거한다.
• rtrim(문자열): 문자열의 우측 공백을 제거한다.
• trim(문자열): 문자열의 좌우측 공백을 제거한다.
• lcase(문자열), lower(문자열): 모든 문자열을 소문자로 변경한다.
• ucase(문자열), upper(문자열): 모든 문자열을 대문자로 변경한다.
• reverse(문자열): 문자열을 정반대로 가져온다.

select concat('aaa', 'bbb', 'ccc');

select insert('aaaaa', 2, 2, 'ccc');
select insert('aaaaa', 2, 0, 'ccc'); 

select replace('aabbcc', 'bb', 'ff');

select instr('asdqweasd', 'a');

select left('abcdefg', 3);

select right('abcdefg', 3);

select mid('abcdefg', 3, 3);

select substring('abcdefg', 3, 3);

select concat('[', '           a b c         ', ']');
select concat('[', ltrim('           a b c         '), ']');
select concat('[', rtrim('           a b c         '), ']');
select concat('[', trim('           a b c         '), ']');

select lcase('abcdefgABCDEFG');
select ucase('abcdefgABCDEFG');
select reverse('abcdefgABCDEFG');

-- 사원의 이름을 가져온다. 성과 이름을 하나의 문자열로 가져온다.
select concat(first_name, ' ', last_name)
from employees;

 

3. 날짜 함수

날짜 함수는 날짜 데이터에 대한 작업을 실시할 수 있는 함수이다.

날짜 함수는 데이터베이스에 따라 관리하는 방법이 다소 상이하다.

 

• now(), sysdate(), current_timestamp(): 현재의 날짜와 시간을 반환한다.
• curdate(), current_date(): 현재의 날짜를 반환한다.
• curtime(), current_time(): 현재의 시간을 반환한다.

 

• year(날짜): 년도를 가져온다.
• quater(날짜): 분기를 가져온다.
• month(날짜): 월을 가져온다.
• monthname(날짜): 월을 영문으로 가져온다.
• week(날짜): 한해의 몇번쨰 주인지 가져온다.
• day(날짜): 일을 가져온다(월 기준).
• dayname(날짜): 요일을 가져온다.
• dayofyear(날짜): 일을 가져온다(년 기준).
• dayofmonth(날짜): 일을 가져온다(월 기준).
• dayofweek(날짜): 일을 가져온다(주 기준, 1~7, 일~토).
• weekday(날짜): 일을 가져온다(주 기준, 0~6, 월~일).
• hour(날짜): 시간을 자겨온다.
• minute(날짜): 분을 가져온다.
• second(날짜): 초를 가져온다.

 

• date_add(날짜, interval expr type): 날짜에서 type의 expr만큼 더한다. 
• date_sub(날짜, interval expr type): 날짜에서 type의 expr만큼 더한다. 
• type, 의미, expr
  • second, seconds, 초
  • minute, minutes, 분
  • hour, hours, 시간
  • day, days, 일
  • month, months, 월
  • year, years, 년
  • year_month, years months, '년 월'
  • day_hour, days hours, '일 시'
  • day_minute, days hours:minutes, '일 시:분'
  • day_second, days hours:minutes:seconds, '일 시:분:초'
  • hour_minute, hours:minutes, '시:분'
  • hour_second, hours:minutes:seconds, '시:분:초'
  • minute_second, minutes:seconds, '분:초'

 

• date_format(날짜, '형식..'): 날짜를 형식에 따라 가져온다.
  • 년도 형식: %Y(2021), %y(21)
  • 월 형식: %M(July), %m(07), %b(Jul)
  • 일 형식: %D(11th), %d(11), %j(192)
  • 시 형식: %H(16), %h(04)
  • 분 형식: %i(09)
  • 초 형식: %S(04)
  • 요일 형식: %W(Sunday), %a(Sun), %w(0)

 select date_format(now(), '%i %S');
 
select now();
select curdate();
select curtime();

select now(), date_add(now(), interval 100 day), date_sub(now(), interval 100 day);
select hire_date, date_add(hire_date, interval 100 day), date_sub(hire_date, interval '1 1' year_month)
from employees;

select day(now());

select year(now()), quarter(now()), month(now()), monthname(now()), week(now()), day(now()), dayname(now()), dayofyear(now()), dayofmonth(now()), dayofweek(now()), weekday(now()), hour(now()), minute(now()), second(now());

 

 

4. 그룹 함수

컬럼의 로우에 대한 집계 값을 구하는데 사용된다.

• count(컬럼): 컬럼의 로우 개수를 반환한다.
• sum(컬럼): 컬럼의 로우 값의 합을 반환한다.
• avg(컬럼): 컬럼의 로우 값의 평균응ㄹ 반환한다.
• max(컬럼): 컬럼의 로우 값 중 최대 값을 반환한다.
• min(컬럼): 컬럼의 로우 값 중 최소 값을 반환한다.

-- 사원의 수를 구한다.
select count(*)
from employees;

-- 남자 사원의 수를 구한다.
select count(*)
from employees
where gender = 'M';

-- 현재 d005 부서에 근무하고 잇는 사원들의 수를 구한다.
select count(*)
from dept_emp
where dept_no = 'd005' and to_date = '9999-01-01';

-- 현재 받고 있는 급여의 합, 평균, 최대 급여액, 최소 급여액을 구한다.
select sum(salary), avg(salary), max(salary), min(salary)
from salaries
where to_date = '9999-01-01';

 

5. Group by, Having 절

Group by절은 select 문을 통해 가져온 로우들을 개발자가 정한 기준에 따라 그룹으로 나눌 수 있다.

그리고 나눠진 그룹에 대해 그룹함수를 적용하여, 그룹 마다의 집계 값을 구할 수 있다.

-- 성별별 사원의 수를 가져온다.
select gender, count(*)
from employees
group by gender;

-- 각 부서에 근무하고 있는 사원들의 수를 가져온다.
select dept_no, count(*)
from dept_emp
where to_date = '9999-01-01'
group by dept_no;

-- 각 부서별 과거의 매니저의 수를 가져온다.
select dept_no, count(*)
from dept_manager
where to_date <> '9999-01-01'
group by dept_no;

-- 급여 수령 시작일별 급여 총합, 평균, 최고액, 최저액을 구한다.
select from_date, sum(salary), avg(salary), max(salary), min(salary) 
from salaries
group by from_date;

 

Group by 절을 통해 그룹을 나눈 후, having 절로 조건을 만들어 조건에 맞는 그룹의 데이터만 가져올 수 있다.

5만명 이상이 사용하고 있는 직함의 이름과 직원의 수를 가져온다.

-- 5만명 이상이 사용하고 있는 직함의 이름과 직원의 수를 가져온다.
select title, count(*)
from titles
where to_date = '9999-01-01'
group by title
having count(*) >= 50000;

-- 5만명 이상이 근무하고 있는 부서의 부서 번호와 부서 소속 사원의 수를 가져온다.
 select dept_no, count(*)
 from dept_emp
 where to_date = '9999-01-01'
 group by dept_no
 having count(*) >= 50000;

 

[출처]윤재성의 처음 시작하는 MySQL DataBase - 인프런 | 강의 (inflearn.com)