[SQL] 데이터형

숫자형

# database/p240605/select-function.sql

절대값

실제 저장된 데이터가 아닌 임으의 데이터이기 때문에 from 생략 !

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

 

소수점 이하 올림

소수점 첫째 자리가 반올림이 되든 안되든 무조건 올림

select ceil(10.4), ceil(10.5), ceil(10.6);

소수점 이하 내림

floor 의 사전적 의미는 바닥 !

select floor(10.432), floor(10.5), floor(10.6);

 

반올림

소수점 기준으로 오른쪽 반올림 결정 !

select round(10.432), round(10.5), round(10.6);

 

근데 여기서 알아야 할 점 !

생략된 부분이 있다 !

select round(166.555, 0), round(166.555, 1), round(166.555, -1);

 

위 코드처럼 (숫자, 0) 이 생략된 것 !

소수점 기준으로 오른쪽을 반올림하는데 소수점이 왼쪽으로 이동된다고 한다 !

-1 같은 경우 소수점의 위치가 정수쪽으로 한 칸 옮겨졌기 때문에 170 의 결과가 나옴 !

 

버림

0 : 소수점 기준으로 0 자리부터 삭제

1 : 소수점 기준으로 1 자리부터 삭제 (소수점이 왼쪽으로 이동)

-1 : 소수점 기준으로 왼쪽으로 이동, 1의 자리는 버릴 수 없으니 0 처리

select truncate(166.555, 0), truncate(166.555, 1), truncate(166.555, -1);

 

문자형

# database/p240610/select_char_function.sql

replace

[문법] replace(컬럼명, 기존문자, 새문자);

select replace('abcdefg', 'cd', 'one');

 

왼쪽을 기준으로 cd 를 찾아서 one 으로 바꾼 것 !

replace 는 해당하는 기존 문자가 여러 개 있다면, 다 치환해줌 !

select replace('abcdefgcd', 'cd', 'one');

 

이것을 응용한다면, 삭제하는 작업도 가능함

# [문제] cd 를 삭제해라
select replace('abcdefg', 'cd', '');

 

instr

위치 : 왼쪽 기준으로 위치는 1부터 시작, 없으면 0 반환
[문법] instr(컬럼명, 찾고자하는문자);

select instr('abcdefg', 'b');  	# 2 반환 --> 왼쪽 기준
select instr('abcdefgb', 'b'); 	# 2 반환 --> 첫번째로 찾은 위치 반환
select instr('abcdefgb', 'cde');# 3 반환 --> 시작하는 문자 위치 반환

# [문제] k 를 찾아라
select instr('abcdefg', 'k'); # 0 반환 --> 없다

문자 반환

[문법]
left(컬럼명, 개수);
right(컬럼명, 개수);
mid(컬럼명, 시작 위치, 개수);

select 'abcdefg', 
    left('abcdefg', 3),   # abc
    right('abcdefg', 3),  # efg
    mid('abcdefg', 3, 2); # cd

 

지금 코드는 단순해서 그렇지 데이터가 여러 개인 경우에는 아래 코드처럼 끝까지 추출할 때는 아무것도 지정안해줘도 됨 !

# [문제] mid 를 이용하여 c부터 끝까지 추출
select mid('abcdefg', 3); # cdefg

select first_name, mid(first_name, 3)
from employees;

 

substring, substr

mid 와 동작은 똑같음 !

substringm, substr, mid 음수 사용 가능

[문법] substring(컬럼명, 시작위치, 개수);

 

음수는 오른쪽 기준으로 -3 !

select substring('abcdefg', 3, 2); 	# cd
select substring('abcdefg', 3); 	# cdefg
select substring('abcdefg', -3); 	# efg
select substring('abcdefg', -3, 2);	# ef

substring_index

[문법] substring_index(컬럼명, '구분자', 구분자 번호);
ex) 전화번호

 

3번째는 없지만 다 출력됨 !

select substring_index('ab-cd-efg', '-', 1); # ab
select substring_index('ab-cd-efg', '-', 2); # ab-cd
select substring_index('ab-cd-efg', '-', 3); # ab-cd-efg

 

음수로 하게 된다면 오른쪽 기준으로 출력된다

# 오른쪽 기준으로 출력
select substring_index('ab-cd-efg', '-', -1); # efg
select substring_index('ab-cd-efg', '-', -2); # cd-efg
select substring_index('ab-cd-efg', '-', -3); # ab-cd-efg

공백 제거

[문법]
ltrim(컬럼명) 	: 왼  쪽 공백 제거
rtrim(컬럼명) 	: 오른쪽 공백 제거
trim(컬럼명) 	: 양  쪽 공백 제거

select 'abc', '   abc    ', concat('|', '   abc    ', '|');

select concat('|', ltrim('   abc    '), '|');

왼쪽 공백 제거

select concat('|', rtrim('   abc    '), '|');

 

오른쪽 공백 제거

select concat('|', trim('   abc    '), '|');

 

문자 사이 공백 제거 하고 싶다면 !

select replace('   a   bc    ', 'a   bc', 'abc');

 

대/소문자 변경

[문법]
ucase(컬럼명);
lcase(컬럼명);

select ucase('aBcDe'), lcase('aBcDe'); # ABCDE, abcde

역순

select reverse('aBcDe');	# eDcBa

문자 길이(문자 수)

select length('aBcDefg');   # 7
select length('aBcDe  fg'); # 9 (공백 포함)

 

여기서 문제 !

[문제 1] 부서명 길이를 구해라 !

# [문제 1] 부서명 길이 구하기
select dept_name, length(dept_name) as '부서명 길이' from departments;

 

[문제 2] 공백이 있는 부서명 조회

select dept_name as '공백이 있는 부서명'
from departments
where instr(dept_name, ' ');

 

날짜형

# database/p240610/select_date_function.sql

현재 날짜와 시간

select now(); 		# 2024-06-10 11:33:06
select sysdate();	# 2024-06-10 11:33:11, 서버 기준

select now(), sysdate(); # 실행에 있어서 조금 차이는 있음, 시간은 같음

# now : 쿼리 실행 뒤 시간 리턴
select now(), sleep(5), now();		  # 쿼리가 끝난 시간이 2개 찍힘

# sysdate : 함수가 실행되는 시점의 시간 리턴
select sysdate(), sleep(5), sysdate();  # now 가 실행이 되고, sleep 5초가 끝나면 now 가 실행됨 !

현재 날짜

select curdate();	# 2024-06-10, current_date 의 축약
select current_date();	# 2024-06-10

현재 시간

select current_time(), curtime(); 	# 11:35:46, current_time 의 축약

특정 날짜를 기준으로 더하거나 빼기한 값

[문법] date_add(날짜, interval 값 기준) : 날짜 또는 시간에서 interval 만큼 더한 날짜 반환
[문법] date_sub(날짜, interval 값 기준) : 날짜 또는 시간에서 interval 만큼 빼기한 날짜 반환
interval 기준 : year, month, day, hour, minute, second

 [년, 월, 일, 시, 분, 초] 조회

select now(), year(now()) as '년', month(now()) as '월', day(now()) as '일';
select now(), hour(now()) as '시', minute(now()) as '분', second(now()) as '초';

월을 영어로 리턴

select now(), monthname(now()) as '월';

요일을 숫자로 리턴

1) dayofweek(날짜);
일(1), 월(2), 화(3), 수(4), 목(5), 금(6), 토(7)

select now(), dayofweek(now()) as '요일';
select now(), dayofweek(now()) as '2024-06-10 요일', dayofweek('2024-06-11') as '2024-06-11 요일';

2) weekday(날짜)
월(0), 화(1), 수(2), 목(3), 금(4), 토(5), 일(6)

select now(), weekday(now()) as '요일';
select now(), weekday(now()) as '2024-06-10 요일', weekday('2024-06-11') as '2024-06-11 요일';

일년을 기준으로 일 수 : dayofyear(날짜);

select now(), dayofyear('2024-01-01') as '2024-01-01', dayofyear(now()) as '현재';

일년을 기준으로 몇 번째 주 : week(날짜);

select now(), week('2023-01-01') as '2023-01-01 주', week('2024-01-01') as '2024-01-01 주', week(now()) as '현재 주';

 

23년 과 24년의 1월 1일이 다른 것은 시작하는 요일이 달라서 !

 

cf) 날짜 시간에 덧셈, 뺄셈 연산 작업 가능

select now(), now() + 1, curdate() + 1, curtime() + 1, year(now()) + 1;

 

형식 지정

select now(), date_format(now(), '%Y') as '년 (4자리)'; 	# 2024
select now(), date_format(now(), '%y') as '년 (2자리)'; 	# 24
select now(), date_format(now(), '%Y %y %m %M %b'); 		# 2024 24 06 June Jun
select now(), date_format(now(), '%Y년 %m월 %d일 %H시 %i분 %s초') as '데이터 가공'; # 2024년 06월 10일

[문제 1] 오늘 기준으로 1일 뒤

select now(), date_add(now(), interval 1 day);

 

[문제 2] 오늘 기준으로 100일 뒤

select now(), date_add(now(), interval 100 day);

 

[문제 3] 현재 시간 기준으로 1시간 뒤

select now(), date_add(now(), interval 1 hour);

 

[문제 4]오늘 기준으로 1일 전

select now(), date_sub(now(), interval 1 day);

[문제 5] 오늘 기준으로 100일 전

select now(), date_sub(now(), interval 100 day);

[문제 6] 사원들의 발령 7일 전 날짜 조회

select hire_date, date_sub(hire_date, interval 7 day) as '발령 7일 전'
from employees;