[MySQL] 내장함수 - 숫자, 문자, 날짜, 논리, 그룹

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

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

-- 12  -13
select floor(12.6), floor(-12.2)
from dual;

-- 1526  1526  1526.2  1526.16  1530  2000
select round(1526.159), round(1526.159, 0), round(1526.159, 1), 
	   round(1526.159, 2), round(1526.159, -1), round(1526.159, -3)
from dual;

-- 1526  1526.1  1526.15  1520  1000
select truncate(1526.159, 0), truncate(1526.159, 1), 
	   truncate(1526.159, 2), truncate(1526.159, -1), truncate(1526.159, -3)
from dual;

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

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

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

---

-- 48	65	97
select ASCII('0'), ASCII('A'), ASCII('a')
from dual;

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

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

-- hello~~~!!!
select replace('helloabc!!!', 'abc', '~~~')
from dual;

-- 7
select instr('hello abc !!!', 'abc')
from dual;

-- abc abc
select mid('hello abc !!!', 7, 3), substring('hello abc !!!', 7, 3)
from dual;

-- hello abc !!!
select reverse('!!! cba olleh')
from dual;

-- hello abc !!!  hello abc !!!
select lower('hELlo ABc !!!'), lcase('hELlo aBc !!!')
from dual;

-- HELLO ABC !!!  HELLO ABC !!!
select upper('hELlo ABc !!!'), ucase('hELlo ABc !!!')
from dual;

-- hello  abc !!!
select left('hello abc !!!', 5), right('hello abc !!!', 7)
from dual;

---

 

       날짜 형식

 

-- 2020-08-01 23:17:11  2020-08-01 23:17:11  2020-08-01 23:17:11
select now(), sysdate(), current_timestamp()
from dual;

-- 2020-08-01  2020-08-01  23:18:33  23:18:33
select curdate(), current_date(), curtime(), current_time()
from dual;

-- 2020-08-01 23:23:19	2020-08-01 23:23:24	2020-08-02 04:23:19	2020-08-06 23:23:19
select now() 현재시간, date_add(now(), interval 5 second) 5초후,
	   date_add(now(), interval 5 hour) 5시간후, date_add(now(), interval 5 day) 5일후
from dual;

-- 2020	8	August	Saturday	1	7	5	214	30
select year(now()), month(now()), monthname(now()), 
       dayname(now()), dayofmonth(now()), dayofweek(now()), 
	   weekday(now()), dayofyear(now()), week(now())
from dual;

-- 2020-08-02 00:21:50  2020 August 2 AM 12 21 50  20-08-02 00:21:50  20.08.02 Sunday  00시21분50초
select now(), date_format(now(), '%Y %M %e %p %l %i %S'), date_format(now(), '%y-%m-%d %H:%i:%s'),
	   date_format(now(), '%y.%m.%d %W'), date_format(now(), '%H시%i분%s초')
from dual;

---

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

---

-- 사원의 총수, 급여의 합, 급여의 평균, 최고급여, 최저급여
select count(employee_id), sum(salary), avg(salary), max(salary), min(salary)
from employees;