이번에도 MySQL 공식 문서에 있는 employees.sql DB를 이용하여 연습
🔩 함수
> 숫자 함수
ABS(숫자) 절대값
select abs(-10);
--실행결과
--10CEIL(숫자) 소숫점 이하 올림
select ceil(123.45);
--실행결과
--124FLOOR(숫자) 소숫점 이하 내림
select floor(234.56);
--실행결과
--234ROUND(숫자, 자릿수) 자릿수를 기준으로 반올림
select round(1234.567); select round(1234.567, 0);
select round(1234.567, 1); select round(1234.567, -1);
--실행결과
--1235 1235
--1234.6 1230TRUNCATE(숫자, 자릿수) 자릿수를 기준으로 내림
select truncate(1234.567, 0);
select truncate(1234.567, 1);
select truncate(1234.567, -1);
--실행결과
--1234
--1234.5
--1230POW(x, y) or POWER(x, y) x의 y승
select power(2, 3)
--실행결과
--8MOD(분자, 분모) 분자를 분모로 나눈 나머지
select mod(20, 3)
--실행결과
--2GREATEST(숫자1, 숫자2, 숫자3, ...) 주어진 숫자 중 가장 큰 값 반환
LEAST(숫자1, 숫자2, 숫자3, ...) 주어진 숫자 중 가장 작은 값 반환
> 문자열 함수
concat(문자열1, 문자열2, 문자열3, ...) 문자열을 합친다.
select concat('hello', ' ', 'MySQL');
--실행결과
--hello MySQLinsert(문자열, 시작위치, 길이, 새로운문자열)
문자열의 시작위치부터 길이만큼의 문자열을 제거하고 그 자리에 새로운 문자열을 삽입한다.
select insert('aaaaa', 2, 0, 'bbb');
--실행결과
--abbbaaaareplace(문자열, 기존문자열, 새로운문자열)
instr(문자열1, 문자열2)
문자열1에서 문자열2를 찾아 위치를 반환한다.
위치는 1부터 시작하며 문자열2를 찾지 못하면 0을 반환한다.
select instr('aaa', 'a');
--실행결과
--1left(문자열, 개수) 문자열의 좌측부터 개수만큼 가져온다.
right(문자열, 개수) 문자열의 우측부터 개수만큼 가져온다.
mid(문자열, 시작위치, 개수) 문자열에서 시작위치부터 개수만큼 가져온다.
substring(문자열, 시작위치, 개수) 문자열에서 시작위치부터 개수만큼 가져온다.
ltrim(문자열) 문자열의 좌측 공백을 제거한다.
rtrim(문자열) 문자열의 우측 공백을 제거한다.
trim(문자열) 문자열의 좌우측 공백을 제거한다.
lcase(문자열), lower(문자열) 문자열을 모두 소문자로 변경
ucase(문자열), upper문자열) 문자열을 모두 대문자로 변경
reverse(문자열) 문자열을 반대로 가져온다.
> 날짜함수
now(), sysdate(), current_timestamp()
현재 날짜와 시간을 반환한다.
select current_timestamp();
--실행결과
--2020-09-03 17:26:00curdate(), current_date() 현재 날짜를 반환한다.
curtime(), current_time() 현재 시간을 반환한다.
date_add(날짜, interval 기준값) 날짜에서 기준값만큼 더한다.
(기준값: year, month, day, hour, minute, second)
--현재에서 100일 후
select date_add(now(), interval 100 day);date_sub(날짜, interval 기준값) 날짜에서 기준값만큼 뺀다.
(기준값: year, month, day, hour, minute, second)
year(날짜) 날짜의 연도를 가져온다.
month(날짜) 날짜의 월을 가져온다.
monthname(날짜) 날짜의 월을 영어로 가져온다.
dayname(날짜) 날짜의 요일을 영어로 가져온다.
dayofmonth(날짜) 그 달의 몇번째 날인지 알려준다.
--9월 3일
select dayofmonth(now())
--실행결과
--3dayofweek(날짜) 날짜의 주별 일자를 가져온다. (일요일 - 1, 월요일 - 2, ...)
--목요일
select dayofweek(now())
--실행결과
--5weekday(날짜) 날짜의 주별 일자를 가져온다. (월요일 - 0, 화요일 - 1, ...)
--목요일
select weekday(now())
--실행결과
--3dayofyear(날짜) 올해부터 날짜까지의 일 수
week(날짜) 올해 중 몇 번째 주
from_days(일 수) '0000년 00월 00일'에서 일 수만큼 지난 날짜
select from_days(370);
--실행결과
--0001-01-05to_days(날짜) '0000년 00월 00일'에서 날짜까지의 일 수
select to_days(now());
--실행결과
--738036date_format(날짜, 형식) 날짜를 형식에 맞게 만들어 반환한다.
--대소문자 유의
select date_format(now(), '%Y년 %m월 %d일 %H시 %i분 %S초');
--실행결과
--2020년 09월 03일 18시 11분 03초> 그룹 함수
select문을 통해 가져온 모든 row를 하나의 그룹으로 묶고
그 안에서 row의 count, sum, avg, max, min을 구한다.
--남자 사원의 수를 구한다.
select count(*)
from employees
where gender = 'M';
--현재 받고 있는 급여액의 총합 (현재: to_date = '9999-01-01')
select sum(salary)
from salaries
where to_date='9999-01-01';🔗 group by, having
> group by
select문을 통해 가져온 모든 row를 개발자가 정한 기준에 따라 그룹으로 나눌 수 있다.
그룹으로 나눈 후 그룹함수를 사용하면 각 그룹 내에서 row의 수, 총합, 최대, 최저값을 구할 수 있다.
--사원의 수를 성별로 조회
select gender, count(*) from employees group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| M | 179973 |
| F | 120051 |
+--------+----------+
--각 부서에 근무하고 있는 사원들의 수 (현재: to_date = '9999-01-01')
select dept_no, count(*)
from dept_emp
where to_date = '9999-01-01'
group by dept_no;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d001 | 14842 |
| d002 | 12437 |
| d003 | 12898 |
| d004 | 53304 |
| d005 | 61386 |
| d006 | 14546 |
| d007 | 37701 |
| d008 | 15441 |
| d009 | 17569 |
+---------+----------+> having
group by절을 이용하여 개발자가 정한 기준으로 그룹을 나눈 후
having절로 만든 조건에 맞는 그룹의 데이터만 가져올 수 있다
--10만명 이상이 사용하고 있는 직함의 이름과 사원의 수
select title, count(*)
from titles
group by title
having count(*) >= 100000;
+----------+----------+
| title | count(*) |
+----------+----------+
| Staff | 107391 |
| Engineer | 115003 |
+----------+----------+참고자료: 소프트캠퍼스
