231124 DB - 단일행 함수2

단일행 함수

substr(문자열, 시작위치, 반환할문자수) : 문자열의 일부분을 반환해주는 함수. 문자열을 시작위치부터 반환할 문자수만큼 반환함.

select substr('대한민국만세', 3,2);

그림 1. substr 함수 예시 1

 

select last_name, substr(last_name, 1,2) from employees; == select last_name, left(last_name,2) from employees;

그림 2. substr 함수 예시 2

 

select last_name, right(last_name,2) from employees; == select last_name,substr(last_name,-2, 2) from employees;

 

단일행함수 - 숫자, 날짜 함수

숫자 함수

round(숫자, 반올림할 자리) : 숫자를 반올림할 자리까지 반올림을 해주는 함수

truncate(숫자, 버림할자리) : 숫자를 버림할 자리까지 남기고 버려주는 함수. (돈 계산에 사용하니 자주 사용)

반올림할 자리 양수 소수, 음수는 정수

select round(45.923,2), round(45.923,0), round(45.923,-1);

그림 3. round 함수 예시

select truncate(45.923,2), truncate(45.923,0), truncate(45.923,-1);

그림 4. truncate 함수 예시

 

ceil(숫자) : 일의 자리로 올림을 해주는 함수

select ceil(45.923), ceil(3.1415);

그림 5. ceil 함수 예시

floor(숫자) : 일의 자리까지 버림을해주는 함수

select floor(45.923), floor(3.1415);

그림 6. floor 함수 예시

 

select truncate(45.923,0), truncate(3.1415,0);와 같다.

그림 7. 참고1

 

mod(숫자1, 숫자2) : 숫자 1을 숫자 2로 나눈 나머지를 반환해주는 함수

select mod(157,10);

그림 8. mod 함수 예시 1

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

그림 9 mod 함수와 mod

cf) select 157/10; 이는 몫을 나타냄.

 

select employee_id, last_name, salary, mod(salary,5000) from employees where job_id = 'SA_REP';

그림 10. mod 함수 예시 2

 

값이 짝수인지 홀수인지를 확인하는 용도로도 활용

select employee_id, mod(employee_id,2) from employees;

그림 11. mod 함수 활용

 

ABS(숫자) : 숫자의 절대값을 반환해주는 함수

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

그림 12. abs 함수 예시

 

power(숫자, 제곱값) = pow(숫자, 제곱값) : 숫자의 제곱 값을 계산하여 반환해주는 함수

select power(2,5), pow(11,2);

그림 13. power 함수 예시

 

sign(숫자) : 숫자가 양수이면 1, 음수이면 -1, 0이면 0을 반환해주는 함수

select sign(100), sign(-100), sign(0);

그림 14. sign 함수 예시

 

날짜 함수

now() = sysdate() = current_timestamp() : 현재 날짜/시간을 반환함. (년/월/일/시/분/초)

그림 15. now, sysdate, current_timestamp 함수

 

current_date() = curdate() : 현재 날짜를 반환해주는 함수(년/월/일)

select current_date(),curdate();

그림 16. current_date, curdate 함수

 

current_time() = curtime() : 현재 시간을 반환해주는 함수(시/분/초)

select current_time(), curtime();

그림 17. current_time, curtime 함수

 

year(날짜) : 날짜/시간에서 년도를 반환해 주는 함수
month(날짜) : 날짜/시간에서 월을 반환해 주는 함수
day(날짜) = dayofmonth(날짜) : 날짜/시간에서 일을 반환해 주는 함수
hour(시간) : 날짜/시간에서 시간을 반환해 주는 함수
minute(시간) : 날짜/시간에서 분을 반환해 주는 함수
second(시간) : 날짜/시간에서 초를 반환해주는 함수

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

그림 18. year, month, day, hour, minute, second 함수 예시 1

 

select last_name, hire_date, year(hire_date), month(hire_date), day(hire_date) from employees

그림 19. year, month, day, hour, minute, second 함수 예시 2

 

ex1) employees 테이블에서 1990년 입사한 사원들의 employee_id, last_name, hire_date, salary를 출력
select employee_id, last_name, hire_date, salary from employees where year(hire_date)=1990;

그림 20. ex1 결과

 

date(날짜와 시간) : 날짜/시간에서 날짜를 반환해 주는 함수(년/월/일)
time(날짜와 시간) : 날짜/시간에서 시간을 반환해 주는 함수(시/분/초)

select date(now()), time(now());

그림 21. date, time 함수

use shopdb;
select * from orders;
select orde_num, member_id, prod_id, date(order_date) as "주문일자" from orders;

그림 22. date, time 함수 예시

 

 

adddate(날짜, interval 기간) = date_add(날짜, interval 기간) : 날짜에서 차이를 더한 날짜를 반환해 주는 함수
subdate(날짜, interval 기) = date_sub(날짜, interval 기간) : 날짜에서 차이를 뺀 날짜를 반환해 주는 함수

select adddate('2022-01-01',interval 35 day), adddate('2022-01-01', interval 2 month), date_add('2022-01-01', interval 1 year) ;

그림 23. adddate, date_add 함수 예시

 

select subdate('2022-01-01',interval 35 day), subdate('2022-01-01', interval 2 month), date_sub('2022-01-01', interval 1 year) ;

그림 24. subdate, date_sub 함수 예시

 

select last_name, hire_date,

adddate(hire_date, interval 6 month) as "입사 6개월 후", subdate(hire_date, interval 7 day) as "입사 7일 전"

from employees 

where department_id = 60;

그림 25. adddate, subdate 함수 예시

 

addtime(날짜 시간, 시간) : 날짜/시간에서 시간을 더한 결과를 반환하는 함수
select addtime('2022-01-01 23:59:59', '1:1:1'), addtime('15:00:00', '2:10:10');

그림 26. addime 함수 예시

 

subtime(날짜 시간, 시간) : 날짜/시간에서 시간을 뺀 결과를 반환하는 함수

select subtime('2022-01-01 23:59:59', '1:1:1'), subtime('15:00:00', '2:10:10');

그림 27. subtime 함수 예시

 

datediff(날짜1, 날짜2) : 날짜 1 - 날짜 2를 반환하는 함수

select datediff('2022-12-31', now()), datediff(now(),'2022-12-31');

그림 28. datediff 함수 예시 1

 

select last_name, hire_date, datediff(now(),hire_date) as "근무일수" from employees;

그림 29. datediff 함수 예시 2

 

timediff(시간1, 시간2) : 시간 1 - 시간 2를 반환하는 함수

select timediff('23:23:59', '12:11:10'), timediff('12:11:10', '23:23:59');

그림 29. timediff 함수 예시

 

dayofweek(날짜) : 날짜의 요일을 반환해 주는 함수(1-일, 2-월,... 7-토)
monthname(날짜) : 날짜의 월의 영문 이름을 반환해주는 함수
dayofyear(날짜) : 날짜가 1년 중 몇 번째 날짜인지 반환해 주는 함수

select dayofweek(now()), monthname(now()), dayofyear(now());

그림 30. dayofweek, monthname, dayofyear 함수 예시 1

 

select employee_id, last_name, hire_date, monthname(hire_date) from employees;

그림 31. dayofweek, monthname, dayofyear 함수 예시 2

 

last_day(날짜) : 날짜가 속한 월의 마지막 날짜를 반환하는 함수

select last_day(now());

그림 32. last_day 함수 예시 1

 

select employee_id, last_name, hire_date, last_day(hire_date) from employees;

그림 33. last_day 함수 예시 2

 

quarter(날짜) : 날짜가 4분기 중에서 몇 분기인지를 반환하는 함수

select quarter(now()), quarter('2023-1-1');

그림 34. quarter 함수 예시

 

연습문제1
employees 테이블로부터 전 사원의 employee_id, last_name, salary를 출력하고 마지막 컬럼에는 15.5%인상된 급여(일의 자리에서 반올림)를 New salary라는 제목으로 출력하는 구문을 작성

select employee_id, last_name, salary, truncate(salary*1.155, 0) as "New salary" from employees;

연습문제2
employees 테이블로부터 사원들의 employee_id, last_name, salary, 15.5% 인상된 급여(New Salary), 새 급여에서 이전 급여를 뺀 값(Increase)을 출력하는 구문을 작성 (단, 4,5번째 컬럼은 일의 자리에서 반올림하여 정수로 표현)

select employee_id, last_name, salary, truncate(salary*1.155, 0) as "New salary", 
truncate((salary*1.155)-salary,0) as "Increse" 
from employees;

 

연습문제 3
employees 테이블로 부터 2월에 입사한 사원들의 employee_id, last_name, job_id, hire_date, department_id를 출력

select employee_id, last_name, job_id, hire_date, department_id 
from employeeswhere month(HIRE_DATE)=2;

 

연습문제 4
employees 테이블로부터 1990년부터 1995년에 입사한 사원들의 employee_id, last_name, hire_date, salary, department_id를 출력

select employee_id, last_name, hire_date, salary, department_id
from employees
where year(hire_date) in (1990, 1991, 1992, 1993, 1994, 1995);

 

연습문제 5
employees 테이블로부터 오늘 날짜 기준으로 근무한 주수가 1400주 미만인 사원들의
last_name, hire_date, 근무한 일수, 근무한 주수를 출력하는 구문을 작성

select last_name, hire_date, 
datediff(now(), hire_date) as "근무한 일수",  truncate(datediff(now(), hire_date)/7, 0)  as "근무한 주수"from employees
where truncate(datediff(now(), hire_date)/7, 0)<1400;

 

연습문제 6
employees 테이블로부터 전 사원들의 employee_id, last_name, hire_date, 입사한 날짜에 해당되는 분기를 출력하는 구문을 작성 (단, 입사한 날짜에 해당되는 분기를 출력해야하는 4번째 컬럼은 예를 들어 입사일이 2000-05-05인 경우 2분기라고 출력될 수 있도록 작성)

select employee_id, last_name, hire_date, concat(quarter(hire_date),"분기")  as "입사한 분기"
from employees;

 

---

연습문제3
employees 테이블로부터 last_name과 급여 액수를 별표(*)로 나타내는 쿼리를 작성 각 별표는 $1,000를 의미하며 백단위 이하는 표시하지 않는다. 또한 급여의 내림차순으로 데이터를 정렬하여출력하고 컬럼 제목을 EMP~로 지정
select concat(last_name,' ',repeat('*',truncate(salary,-3)/1000)) as "EMPLOYEES_AND_THEIR_SALARIES" 

select concat(last_name,' ',repeat('*',truncate(salary,-3)/1000)) as "EMPLOYEES_AND_THEIR_SALARIES" 
from employees
order by salary;

또는

select salary, concat(last_name,rpad(' ',truncate(salary/1000,0)+1,'*')) as "EMPLOYEES_AND_THEIR_SALARIES"
from employees
order by salary DESC;