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

1. 날짜 / 시간

 select now();

select ename, hiredate, now()
from emp;

select '2025-02-25'; -- 얘는 날짜가 아니다

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-%h:%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('ssarmango', 15, '*');

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

6. 문제 풀기

6.문제풀기
select name, replace(
tel,
substr(tel, instr(tel, ")")+1, instr(tel, "-")-instr(tel, ")")-1),
repeat('*', LENGTH(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 name, tel, LENGTH(substr(tel, instr(tel, ")")+1, instr(tel, "-")-instr(tel, ")")-1))
from student;

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

2. 날짜 포맷

3. 조건문

-- 조건문 (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;

4. 정렬

select *
from emp
where deptno = 20
-- 오름차순 asc
order by SAL asc;

select *
from emp
where deptno = 20
-- 내림차순 desc
order by SAL desc, ename asc;