[데이터 베이스]3.SELECT 단일행 함수

-- SELECT 단일행 함수

-- 1.날짜/시간
select now();

select ename, hiredate, now()
from emp;



select date('2025-02-25 12:30:35');
select time('2025-02-25 12:30:35');
select year('2025-02-25 12:30:35');
select month('2025-02-25 12:30:35');
select day('2025-02-25 12:30:35');
select hour('2025-02-25 12:30:35');
select minute('2025-02-25 12:30:35');
select second('2025-02-25 12:30:35');

-- 2.날짜 포맷
select date_format(now(), '%Y/%m/%d');

select date_format(now(), '%Y/%m/%d/%i/%s');

-- 3.날짜 연산하기 (더하기,빼기,간격,마지막날짜)
select date_add(now(), interval 4 year);
select date_add(now(), interval 4 month);
select date_add(now(), interval 4 week);
select date_add(now(), interval 4 day);
select date_add(now(), interval 4 hour);
select date_add(now(), interval 4 minute);
select date_add(now(), interval 4 second);

select date_sub('2025-02-25', interval 4 day);

select datediff('2025-02-25', '2025-03-01');

select timediff(now(), '2025-02-25 12:50:00');

select last_day(now());

-- 4. 수학 함수(절대값 올림값 내림값 반올림값)
select floor(101.5);
select ceil(101.5);
select round(101.5);
select mod(101,10);

-- 5. 문자열 함수
-- substr(시작번지1~,개수)
select substr(hiredate,1,4)
from emp;

select year(hiredate)
from emp;

select replace('010/2222/7777','/','-');

select instr('abcde','c');

select rpad('ssalmango',10,'*');

select rpad(substr('ssarmango',1,4), LENGTH("ssarmango"), '*');
select lpad(substr('ssarmango',1,4), LENGTH("ssarmango"), '*');

-- 6.문제 풀기
select name, replace(tel, '381','***')
from student;

select now();

select ename, hiredate, now()
from emp;



select date('2025-02-25 12:30:35');
select time('2025-02-25 12:30:35');
select year('2025-02-25 12:30:35');
select month('2025-02-25 12:30:35');
select day('2025-02-25 12:30:35');
select hour('2025-02-25 12:30:35');
select minute('2025-02-25 12:30:35');
select second('2025-02-25 12:30:35');

-- 2.날짜 포맷
select date_format(now(), '%Y/%m/%d');
select date_format(now(), '%Y/%m/%d/%i/%s');

-- 3.날짜 연산하기 (더하기,빼기,간격,마지막날짜)
select date_add(now(), interval 4 year);
select date_add(now(), interval 4 month);
select date_add(now(), interval 4 week);
select date_add(now(), interval 4 day);
select date_add(now(), interval 4 hour);
select date_add(now(), interval 4 minute);
select date_add(now(), interval 4 second);

select date_sub('2025-02-25', interval 4 day);

select datediff('2025-02-25', '2025-03-01');

select timediff(now(), '2025-02-25 12:50:00');

select last_day(now());

4. 수학 함수

select floor(101.5);
select cell(101.5);
select round(101.5);
select mod(101.10);

5. 문자열 함수

- 5. 문자열 함수
-- substr(시작번지1~,개수)
select substr(hiredate,1,4)
from emp;

select year(hiredate)
from emp;

select replace('010/2222/7777','/','-');

select instr('abcde','c');

select rpad('ssalmango',10,'*');

select rpad(substr('ssarmango',1,4), LENGTH("ssarmango"), '*');
select lpad(substr('ssarmango',1,4), LENGTH("ssarmango"), '*

-- 6.문제 풀기
select *
from student;

select name, replace(tel, '381','***')
from student;

select tel
from student;

select substr(tel,instr(tel,')')+1,5)
from student;

select substr(tel, instr(tel, ')')+1, instr(tel,'-')-instr(tel, ')')-1)
from student;

select name, tel, substr(tel, instr(tel, ")")+1, instr(tel, "-")-instr(tel, ")")-1)
from student;

select replace(tel, substr(tel, instr(tel, ")")+1, instr(tel, "-")-instr(tel, ")")-1),'***')
from student;

-- 조건문 (if - mysql case when - 모든 DB)

select if(10>5, '참','거짓');


-- 2500 (고액연봉), (일반연봉)


select ename, SAL, 
case 
	when SAL>2500 then '고액연봉'
    when SAL<2000 then '일반연봉'
    else '중간연봉'
    end '연봉그룹'
from emp;

-- 정렬

select *
from emp
where deptno = 20
order by SAL asc;

select *
from emp
where deptno = 20
order by SAL desc;