[DB] Function

● Function 데이터베이스 도큐먼트에서 확인가능 https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions002.htm#SQLRF51178 1) Single Row functions : 1개의 레코드(단일행) - Numeric Functions round() : 소수점 반올림  trunc() : 소수점 버리기 floor() : 소수점 내림 ceil() : 소수점 올림 mod() : 나머지 구하기 power() : 거듭제곱 sign() : 부호표시( 음수인지 , 양수인지) - Character Functions lower(), upper() : 대문자를 소문자로 , 소문자를 대문자로 바꿈   substr() : 데이터 추출 length() : 문자열의 크기 instr() : 문자열의 위치 ltrim(), rtrim(), trim() : 문자, 공백제거( 왼쪽, 오른쪽, 양쪽 제거) translate(), replace() : 문자열의 재배치 chr(), ascii() : 아스키 코드 변환 - Datetime Functions sysdate() : 현재 날짜와 시간 months_between() : 날짜와 날짜 사이의 간격을 개월수로 알려줌 add_months() : 개월수를 더함 next_day() : 선택한 일자의 그주 요일에 대한 날짜 last_day() : 해당 달의 마지막 날짜 round(), trunc() :  trunc 함수를 사용하면 sysdate 를 출력된 날짜에서 시/분/초를                         제외한 현재 날짜 출력 - Conversion Functions to_date() : 날짜로 바꿀때 사용 to_number() : 숫자로 바꿀때 사용 to_char() : 문자로 바꿀때 사용 cast() : 형식을 원하는 방식으로 바꿀때 사용 - 기타 함수 nvl() : null값을 바꿔줄수 있는 함수 decode() : 조건문 2) Aggregation functions : 여러개의 레코드(집합행)         sum() : 더하기          avg() : 평균         max() : 최대값         min() : 최소값         count() : 레코드 개수                  - SELECT의 확장문법               기본문법 group by 필드명[, 필드명,...] 3) Analytic functions ◈ Single Row functions ▶ 소수점 함수 Test -> oracle에서 테스트용으로 만든 dual이란 테이블에서 실행 □ round() select round(4567.678), round(4567.678, 0) from dual; select round(4567.678, 2) from dual; select round(4567.678, -2) from dual;   □ trunc() select trunc(4567.678), trunc(4567.678,2) from dual; □ floor()  -> 소수점 자리 지정 불가능 select floor(4567.678) from dual; □ ceil()  -> 소수점 자리 지정 불가능 select ceil(4567.378) from dual; □ mod() select mod(10, 3) from dual; □ power() select power(2, 5) from dual; □ sign() select sign(100), sign(-15), sign(0) from dual; ▶ 문자 함수 Test □ lower(), upper() 이름이 scott인 직원의 이름, 부서, 급여를 조회 단, 대소문자 구별없이 조회 가능하도록 select ename, deptno, sal from emp where ename= upper('ScOtT'); □ substr() -> 위치를 지정해서 데이터를 가져올수 있다. 다음의 주민번호에서 성별에 해당하는 것을 추출 select substr('9012311234567', 7, 1) from dual; select substr('9012311234567', 7) from dual; □ length() 문자열의 길이 select length('안녕하세요...sql입니다') from dual; □ instr() 문자열의 위치 select instr('MILLER', 'L') from dual; select instr('MILLER', 'K') from dual; -> 위치가 없을때 0부터 시작한다. select instr('MILLER', 'L', 1, 1) from dual; select instr('MILLER', 'L', 1, 2) from dual; □ ltrim(), rtrim(), trim()  문자, 공백제거( 왼쪽, 오른쪽, 양쪽 제거) select ltrim('MILLER', 'M') from dual; select rtrim('MILLER', 'R') from dual; select ltrim('             MILLER') from dual; □ translate(), replace() 문자열의 재배치 select translate('MILLER', 'L', '*') from dual; select replace('MILLER', 'L', '*') from dual; select translate('JACK and JUE', 'J', 'BL') from dual; -> 바꾸려는 데이터 값을 1대1로 매치 select replace('JACK and JUE', 'J', 'BL') from dual; -> 바꾸려는 데이터를 전체를 모두 바꾸려고한다. □ chr(), ascii() 아스키 코드 변환 select chr(65), chr(97) from dual; select ascii('A'), ascii('a') from dual; ▶ 날짜 함수 Test □ sysdate() 현재 날짜와 시간 select sysdate from dual; select sysdate+100 from dual; □ months_between() 날짜와 날짜 사이의 간격을 개월수로 알려줌 select months_between('2017/12/31', '2017/2/5') from dual; □ add_months() 개월수를 더함 select add_months(sysdate, 10) from dual; □ next_day() 선택한 일자의 그주 요일에 대한 날짜 select next_day('2017/1/8', '금') from dual; □ last_day() : 해당 달의 마지막 날짜 select last_day(sysdate) from dual; □ round(), trunc()   날짜가 반나절 지나면 하루로 처리 select round(sysdate) from dual;    //시간을 반올림 select round(to_date('2017/8/4')) from dual;     select round(to_date('2017/8/16'), 'MONTH') from dual;    // 월단위로 반올림 select round(to_date('2017/8/16'), 'YEAR') from dual;    //년단위로 반올림 ▶ 캐스팅 함수 Test □ to_char() select ename, sal, to_char(sal) from emp; select ename, sal, to_char(sal, '999,999') from emp; select ename, sal, to_char(sal, '$999,999') from emp; select ename, sal, to_char(sal, 'L999,999') from emp;    // L => 지역에 맞는 통화 표시 select to_char(sysdate, 'YYYY MM DD HH"시" MI"분" SS"초"') from dual; ▶ 기타 함수 Test □ nvl() 직원의 이름, 급여, 커미션, 총급여를 검색 select ename, sal, comm, (sal+nvl(comm,0)) as 총급여 from emp;     //만약 comm값이 null이면 0으로 바꾸라는 뜻 □ decode() 조건문 if(job.equals("SALESMAN")) sysout("맞다"); else sysout("틀리다"); ↓ select ename, decode(job, 'SALESMAN', '맞다', '틀리다') from emp; -> 제한조건은 조건이 반드시 같아야한다. if(deptno==10) sysout("10번"); else if(deptno==20) sysout("20번"); else if(deptno==30) sysout("30번"); else if(deptno==50) sysout("없는 부서"); ↓ select ename, decode(deptno, 10, '10번', 20, '20번', 30, '30번', '없는 부서' ) from emp;       ◈ Aggregation functions □ sum(), max(), min(), avg() 업무가 salesman인 직원들의 급여평균, 최고액, 최저액, 합계를 조회 select avg(sal), max(sal), min(sal), sum(sal) from emp; □ group by 부서별로 급여평균, 최저급여, 급여합계를 조회 select distinct deptno from emp; select avg(sal), min(sal), sum(sal) from emp where deptno=10; select avg(sal), min(sal), sum(sal) from emp where deptno=20; select avg(sal), min(sal), sum(sal) from emp where deptno=30; 4줄을 쳐야되는 일을 group by로 1줄로 표시 가능 => select deptno, avg(sal), min(sal), sum(sal) from emp group by deptno; 부서별로 직원의 수를 조회 select deptno, count(empno) from emp group by deptno; 부서별로 급여평균, 최고급여를 조회하는데 단, 급여평균이 높은 순으로 조회 select deptno, avg(sal), max(sal) from emp group by deptno order by  avg(sal) desc; 전체 급여의 합계가 5000을 초과하는 업무에 대해 급여 합계 조회.(단, salesman은 제외) select job, sum(sal) from emp group by job having sum(sal)>5000 and job!='SALESMAN'; select job, sum(sal) from emp where job='SALESMAN' group by job having sum(sal)>5000 ; □ count() 직원이 총 몇명인가? select count(empno) from emp; select count(comm) from emp; -> Null값은 세지 않고 카운트한다. select count(*) from emp; -> * 가장 많은 값을 가진 데이터를 카운트한다.