/* ***********************************************
단일행 함수 : 행별로 처리하는 함수. 문자/숫자/날짜/변환 함수
- 단일행은 select, where절에 사용가능
다중행 함수 : 여러행을 묶어서 한번에 처리하는 함수 => 집계함수, 그룹함수라고 한다.
- 다중행은 where절에는 사용할 수 없다. (sub query 이용)
* ***********************************************/
/* ***************************************************************************************************************
함수 - 문자열관련 함수
char_length(v) - v의 글자수 반환
concat(v1, v2[, ..]) - 값들을 합쳐 하나의 문자열로 반환
format(숫자, 소수부 자릿수) - 정수부에 단위 구분자 "," 를 표시하고 지정한 소수부 자리까지만 문자열로 만들어 반환
upper(v), lower(v) - v를 모두 대문자/소문자 로 변환
insert(기준문자열, 위치, 길이, 삽입문자열): 기준문자열의 위치(1부터 시작)에서부터 길이까지 지우고 삽입문자열을 넣는다.
replace(기준문자열, 원래문자열, 바꿀문자열):기준문자열의 원래문자열을 바꿀문자열로 바꾼다.
left(기준문자열, 길이), right(기준문자열, 길이): 기준문자열에서 왼쪽(left), 오른쪽(right)의 길이만큼의 문자열을 반환한다.
substring(기준문자열, 시작위치, 길이): 기준문자열에서 시작위치부터 길이 개수의 글자 만큼 잘라서 반환한다. 길이를 생략하면 마지막까지 잘라낸다.
substring_index(기준문자열, 구분자, 개수): 기준문자열을 구분자를 기준으로 나눈 뒤 개수만큼 반환. 개수: 양수 – 앞에서 부터 개수, 음수 – 뒤에서 부터 개수만큼 반환
ltrim(문자열), rtrim(문자열), trim(문자열): 문자열에서 왼쪽(ltrim), 오른쪽(rtrim), 양쪽(trim)의 공백을 제거한다. 중간공백은 유지
trim(방향 제거할문자열 from 기준문자열): 기준문자열에서 방향에 있는 제거할문자열을 제거한다.
방향: both (앞,뒤), leading (앞), trailing (뒤)
lpad(기준문자열, 길이, 채울문자열), rpad(기준문자열, 길이, 채울문자열): 기준문자열을 길이만큼 늘린 뒤 남는 길이만큼 채울문자열로 왼쪽(lpad), 오른쪽(rpad)에 채운다.
기준문자열 글자수가 길이보다 많을 경우 나머지는 자른다.
*************************************************************************************************************** */
select char_length('가나다라마'); -- 글자수를 반환.
select * from emp where char_length(emp_name) >= 7;
select char_length(emp_name) >= 7 from emp;
select upper('abcdABCD'), lower('abcdABCD'); -- 문자열 -> 대문자 소문자로 변환
select format(123456789, 0); -- 자리 구분자를 쉼표(,)로 표현한다. 숫자를 문자열로 변환시킴. format(자리구분자를 넣을 숫자, 반올림할 자릿수)
select format(123456789.987654, 2);
select format(123456789.987654, 0); -- 소숫점 이하에서 반올림.
select concat('홍길동', ' 님'); -- 값 두 개를 하나의 문자열로 합친다.
select insert('123456789', 2, 3, '안녕'); -- 두번째 글자부터 3글자를 '안녕'으로 변경
select replace('123456789', '234', '안녕'); -- '234' 문자열을 '안녕'으로 변경
select substring('123456789', 4, 2); -- 4번째 글자부터 두글자를 반환, 길이(2) 생략시 나머지까지 반환
select substring_index('aaa-bbb-ccc-ddd-eee', '-', 3); -- 문자열을 '-' 구분자를 기준으로 나눴을 때 앞에서부터 3개를 반환
select substring_index('aaa-bbb-ccc-ddd-eee', '-', -3); -- 문자열을 '-' 구분자를 기준으로 나눴을 때 뒤에서부터 3개를 반환
select left('1234567890', 5); -- 왼쪽에서 5글자 반환
select right('1234567890', 5); -- 오른쪽에서 5글자 반환
select trim(' aaa '), char_length(trim(' aaa ')); -- 좌우 공백 제거
select ltrim(' aaa '), char_length(ltrim(' aaa ')); -- 왼쪽 공백 제거
select rtrim(' aaa '), char_length(rtrim(' aaa ')); -- 오른쪽 공백 제거
select trim(both '-' from '----------aaaa-------------') as 'b';
-- 어디에있는것 지울문자열 from 대상문자열
select trim(both '-+' from '-+-+-+-+aaaa-+-+-+-+-+') as 'b';
select trim(leading '-+' from '-+-+-+-+aaaa-+-+-+-+-+') as 'b'; -- 앞쪽에 있는것만 지운다.
select trim(trailing '-+' from '-+-+-+-+aaaa-+-+-+-+-+') as 'b'; -- 뒤쪽에 있는것만 지운다.
select lpad('test', 10, ' ') as 'b'; -- 10글자로 맞춘다. 모자랄 경우 왼쪽(lpad)에 ' '을 붙인다.
select rpad('test', 10, ' '), char_length(rpad('test', 10, ' ')) as 'b'; -- 10글자로 맞춘다. 모자랄 경우 오른쪽(rpad)에 ' '을 붙인다.
select lpad(3, 2, '0');
select rpad('aaaaaaaaaaaaaabb', 3, ' '); -- 모자라면 채우고 넘치면 버린다.
use hr; -- hr DataBase 사용
-- EMP 테이블에서 직원의 이름(emp_name)을 모두 대문자, 소문자, 이름 글자수를 조회
select upper(emp_name), lower(emp_name), char_length(emp_name), lpad(emp_name, 15, ' ') from emp;
/* **************************************************************************
함수 - 숫자관련 함수
abs(값): 절대값 반환
round(값, 자릿수): 자릿수이하에서 반올림 (양수 - 실수부, 음수 - 정수부, 기본값: 0-0이하에서 반올림이므로 정수로 반올림)
truncate(값, 자릿수): 자릿수이하에서 절삭-버림(자릿수: 양수 - 실수부, 음수 - 정수부, 기본값: 0)
ceil(값): 값보다 큰 정수중 가장 작은 정수. 소숫점 이하 올린다.
floor(값): 값보다 작은 정수중 가장 작은 정수. 소숫점 이하를 버린다. 내림
sign(값): 숫자 n의 부호를 정수로 반환(1-양수, 0, -1-음수)
mod(n1, n2): n1 % n2
************************************************************************** */
select abs(10), abs(-10);
select round(1.2345); -- 소수점 이하에서 반올림
select round(1.23789, 2); -- 소수점 2자리 이하에서 반올림
select round(188.123, -1); -- 자리 : 음수 -> 정수위치.
select ceil(50.9999); -- 올림 -> 정수 반환
select floor(50.9999); -- 내림 -> 정수 반환
select truncate(1234.567, 1); -- 절삭(내림)
select truncate(1234.567, 2);
select truncate(1234.567, 0);
select truncate(1234.567, -1);
select truncate(1234.567, -2);
select sign(-10), sign(0), sign(10); -- 부호 확인할 때 사용
/* ***************************************************************************************************************
함수 - 날짜관련 계산 및 함수
date/time/datetime: +, - 사용 => 마지막 항목(date:일, time: 초, datetime: 초)의 값을 +/- => 계산 결과가 정수형으로 반환된다.
ex) 2023-09-01 -> 20230901 + 5
now(): 현재 datetime
curdate(): 현재 date
curtime(): 현재 time
year(날짜), month(날짜), day(날짜): 날짜 또는 일시의 년, 월, 일 을 반환한다.
hour(시간), minute(시간), second(시간), microsecond(시간): 시간 또는 일시의 시, 분, 초, 밀리초를 반환한다.
date(), time(): datetime 에서 날짜(date), 시간(time)만 추출한다.
날짜 연산
adddate/subdate(DATETIME/DATE/TIME, INTERVAL 값 단위)
날짜에서 특정 일시만큼 더하고(add) 빼는(sub) 함수.
단위: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER(분기-3개월), YEAR
datediff(날짜1, 날짜2): 날짜1 – 날짜2한 일수를 반환
timediff(시간1, 시간2): 시간1-시간2 한 시간을 계산해서 반환 (뺀 결과를 시:분:초 로 반환)
dayofweek(날짜): 날짜의 요일을 정수로 반환 (1: 일요일 ~ 7: 토요일)
date_format(일시, 형식문자열): 일시를 원하는 형식의 문자열로 반환
*************************************************************************************************************** */
-- 실행시점의 일/시를 조회 함수
select now(); -- 일시 -> datetime
select curdate(); -- 날짜 -> date
select curtime(); -- 시간 -> time
select year(now()), month(now()), day(curdate()); -- 날짜 타입에서 년 월 일 조회
-- 시간 타입에서 시 분 초 조회
select hour(now()), minute(curtime()), second(curtime()), microsecond(now());
select date(now()); -- datetime -> date
select time(now()); -- datetime -> time
-- 날짜 연산
select adddate(now(), interval 2 day);
select adddate(now(), interval 2 month);
select adddate(now(), interval 2 year);
select adddate(now(), interval 2 quarter); -- 2분기 후 -> 6개월 후
select subdate(now(), interval 2 year); -- 2년 전
select adddate(now(), interval -2 year);
select adddate(curtime(), interval 10 hour);
select adddate(curtime(), interval 10 minute);
select datediff(curdate(), '2023-08-30'); -- curdate - 2023/8/30 일수 차이
select datediff('2023-08-30', curdate()); -- 앞의 것이 과거일 경우 음수 반환
select timediff(curtime(), '11:20:10'); -- curtime - '11:20:10' 차이가 나는 시간:분:초 반환
select dayofweek(now()); -- 요일 (1:일요일 ~ 7:토요일)
select now();
select date_format(now(), '%Y년 %m월 %d일 %h시 %i분 %s초 %p %W');
select date_format(curdate(), '%m/%d/%Y %w');
/* *************************************************************************************
함수 - 조건 처리함수
ifnull (기준컬럼(값), 기본값): 기준컬럼(값)이 NULL값이면 기본값을 출력하고 NULL이 아니면 기준컬럼 값을 출력
if (조건수식, 참, 거짓): 조건수식이 True이면 참을 False이면 거짓을 출력한다.
nullif(컬럼1, 컬럼2): 컬럼1과 컬럼2가 같으면 NULL을 반환, 다르면 컬럼1을 반환
coalesce(ex1, ex2, ex3, .....) ex1 ~ exn 중 null이 아닌 첫번째 값 반환.
************************************************************************************* */
select ifnull(null, '없음');
select comm_pct from emp;
select ifnull(comm_pct, 'no commission') from emp;
select salary, if(salary > 10000, '평균이상', '평균미만') from emp; -- 조건연산자 기능 함수.
select nullif(10, 10); -- 두 값이 같으면 null 반환
select nullif(100, 1); -- 두 값이 다르면 앞의 값을 반환.
select coalesce(null, null, null, 10, 20, 30); -- 값이 나열되었을 때 null이 아닌 첫번째 값 반환
/* *************************************
CASE 문
case문 동등비교
case 컬럼 when 비교값 then 출력값
[when 비교값 then 출력값]
[else 출력값]
end
case문 조건문
case when 조건 then 출력값
[when 조건 then 출력값]
[else 출력값]
end
************************************* */
/*
if dept_name==null:
return '부서없음'
elif dept_name=='IT':
return '전산실'
elif dept_name=='Finance':
return '회계부'
else:
return dept_name
*/
select dept_name from emp;
select case dept_name when 'IT' then '전산실'
when 'Finance' then '회계부'
when 'Sales' then '영업부'
else ifnull(dept_name, '부서없음')
end '부서명'
from emp -- 결과 확인할 때 NULL인 값들은 case 조건을 충족하지 못한 값들이다.
order by 1;
-- EMP테이블에서 급여와 급여의 등급을 조회하는데 급여 등급은 10000이상이면 '1등급', 10000미만이면 '2등급' 으로 나오도록 조회
select salary from emp;
select salary, case when salary > 10000 then '1등급'
else '2등급' end '급여등급'
from emp;
-- case 를 이용한 정렬
-- 직원들의 모든 정보를 조회한다. 단 정렬은 업무(job)가
-- 'ST_CLERK', 'IT_PROG', 'PU_CLERK', 'SA_MAN' 순서대로 먼저나오도록 한다. (나머지 JOB은 상관없음)
select * from emp
order by case job when 'ST_CLERK' then 1
when 'IT_PROG' then 2
when 'PU_CLERK' then 3
when 'SA_MAN' then 4
else job
end;
/*
형 변환 처리(타입 변환)
*/
select '1000' + '2000';
select curdate() + 10; -- 암시적 형변환
-- 명시적 형변환 cast(값 as 변환할 타입), convert(값, 변환할 타입)
select convert(curdate(), signed); -- 명시적 형변환
select convert(20230901, date);
select convert(102030, time);
select cast(102030 as time); -- cast 함수
