220124 oracle_sql함수_숫자,문자,날짜

2. select
   : 데이터를 조회하기 위한 명령어.

 * like 연산자★
    : 검색하고자 하는 값의 일부만으로 관련 있는 값을 전부 검색 -> 검색 기능 시 사용!
    : where column_name like pattern (pattern에 와일드카드를 사용)
   - 와일드카드
      01. [ % ]
         : 문자가 없어도 되고 하나 이상의 문자 어떤 값이 와도 괜찮음
         select* from emp where ename like 'F%'; // 첫글자가 F라면 앞뒤에 어떤 값이 오든 검색
         select* from emp where ename like '%A%'; // 앞뒤 어디든 A가 포함되는 값이면 검색
      02. [ _ ]
         : 하나의 문자에 어떤 값이 와도 괜찮음 -> 검색하는 조건의 위치를 정해줄 때 사용
         select* from emp where ename like '_A%'; // 이름 두번째 글자가 A인 데이터 검색
      03. not like
         select* from emp where ename NOT like '%A%'; // 이름에 A가 포함되지 않은 사람 검색

 * NULL 검색
    : null을 검색할 때는 = 연산자로 판단할 수 없음 -> IS NULL 사용!
      ex. 커미션을 받지 않는 사원 검색
           select* from emp where COMM = null; -> 검색 안 됨 (알수없는 값과 같다는게 말이 안되니까)
           select* from emp where COMM = ''; -> 검색 안 됨 (number형 컬럼이라 ''-문자는 검색 불가능)
           select* from emp where COMM IS NULL; -> 검색 됨

 * order by
    : 정렬 명령어 - 크기 순서대로 내열
    - ASC(defalt) : 오름차순, 작은 값부터 정렬 / 빠른 값부터 정렬 / 오래된 날짜 / null은 가장 끝에 출력
    - DESC : 내림차순, 큰 값부터 정렬 / 늦은 값부터 정렬 / 최근 날짜 / null은 가장 처음에 출력
      ex. 급여 순으로 내림차순 정렬 (단, 동일 급여일 경우 이름의 철자 빠른 사람부터 출력)
           select* from emp order by sal desc, ename asc;
           //첫번째 정렬이 우선적으로 적용되고, 중복일 경우 두번째 정렬이 적용됨
      ex. 커미션을 받는 사람(not null)만 오름차순 정렬
           select* from emp where comm is not null order by comm;
           //조건 먼저 걸고 그 조건에 해당하는 값만 정렬시켜야 하므로 where문 끝나고 order by 들어가야함!

이미지 출처: https://myjamong.tistory.com/172

※dual 테이블 (sys.dual)
 desc(ribe) dual;
  1. sys 계정 소유 테이블이고, 공개 동의어로 설정되어있음
  2. 공개(공용) 동의어---> 누구나 사용 가능
    //다른 계정의 테이블을 참조할 경우 sys.dual 로 명시해야 하지만
      dual은 공개되어있기 때문에 테이블명으로 간결하게 사용할 수 있음
  3. dual 테이블은 데이터가 1개 -> row=1이므로 연산 결과를 1번만 출력


3. SQL 함수
 : 함수명은 대소문자를 구분하지 않아요
 * 숫자 함수
    - abs() : 절대값
        select ABS(-10) from dual;   // 10 출력

    - floor() : 소수점 아래 버림 -> 정수 출력 (only)
        select FLOOR(34.5678) from dual;          //34 출력

    - trunc() : 소수점 지정 자릿수 이하 버림
        select TRUNC(34.5678) from dual;          //34 출력
        select TRUNC(34.5678, 2) from dual;       //34.56 출력 (버림이라 7 아님)
        select TRUNC(34.5678, -1) from dual;     //30 출력

    - round() : 소수점 지정 자릿수 반올림 가능!
        select ROUND(34.5678) from dual;         //35 출력
        select ROUND(34.5678, 2) from dual;      //10^-2까지 출력 -> 34.57 출력
        select ROUND(34.5678, -1) from dual;    //10^1까지 출력 -> 30 출력

    - mod() : 나머지
        select MOD(27, 2) from dual;               //27=2*13+1 ---> 1 출력
        select MOD(27, 7) from dual;               //27=7*3+6  ---> 6 출력
      ex. 사원번호가 홀수인 사원들을 검색하는 sql문
        select * from emp where mod(empno,2)=1; //empno의 나머지가 1인 조건만 출력

 * 문자 함수
    - upper(): 대문자 변환
        select UPPER('Welcome') from dual;       //WELCOME 

    - lower(): 소문자 변환
        select LOWER('WelCome') from dual;      //welcome 

    - initcap(): 첫 글자만 대문자로 변환
        select INITCAP('welCOME to oraCle') from dual;     //Welcome To Oracle

    - length(): 데이터의 길이
        select LENGTH('오라클') from dual;         //3
        select LENGTH('oracle') from dual;         //6
    +) lengthb(): 데이터의 길이(byte 단위로 출력) -> 한글 byte가 버전에 따라 2,3이 되므로 잘 안 씀

    - substr(): 문자열, 데이터의 시작위치부터 선택한 개수만큼 문자를 추출
        select SUBSTR('Welcome to Oracle', 4, 3) from dual;  //4번째 문자부터 3개 추출 -> com 
        select SUBSTR('Welcome to Oracle', -4, 3) from dual;  //뒤에서 4번째 문자부터 3개 추출 -> acl
      ex. 사원 입사일(hiredate)을 연 월 일 별로 출력
        select SUBSTR(hiredate,1,2) "연", SUBSTR(hiredate,4,2) "월", SUBSTR(hiredate,7,2) "일" from emp; 
        // 80/12/17 형식이기 때문에 연 시작 1, 월 시작 4, 일 시작 7임
      ex. 87년에 입사한 사원만 검색
        select * from emp where SUBSTR(hiredate,1,2)=87;
      ex. 이름이 E로 끝나는 사원 검색
        select * from emp where SUBSTR(ename,-1,1)='E';
        select * from emp where ename like '%E';

    - instr(): 특정 문자의 위치
        select INSTR('WELCOME TO ORACLE', 'O') from dual;  //5
        select INSTR('WELCOME TO ORACLE', 'O', 6, 2) from dual;
        //6번 글자 이후로 두번째 O를 검색 -> 6번째 글자(M)이후로 두번째 O 위치 -> 12 (공백 포함, T'O' 아님)
      ex. 이름의 3번째 이니셜이 R인 사원 검색
        select * from emp where SUBSTR(ename, 3, 1)='R'; //세번째 글자 1개 추출한게 R과 같은 조건
        select * from emp where INSTR(ename,'R',3,1)=3;  //3번째부터 처음 발견된 R 위치가 3인 조건
        select * from emp ename LIKE '__R%';

    - lpad() / rpad() : 명시된 자릿수 기준 왼/오른쪽에 값을 넣고 남은 자리를 특정 기호로 채움
        lpad('Oracle', 10, '#') //####Oracle : Oracle 6글자 넣고 10에서 남은 4자는 왼쪽에 #으로
        rpad('Oracle', 10, '#') //Oracle#### : Oracle 6글자 넣고 10에서 남은 4자 오른쪽에 #로

    - ltrim() / rtrim() : 왼/오른쪽 공백 삭제
        select ltrim('    oracle    ') from dual; // 'oracle    ' 출력
        select rtrim('    oracle    ') from dual; // '    oracle' 출력
    - trim() : 문자열 양쪽의 공백 삭제 + 특정 문자를 잘라내는 함수
        select TRIM('    oracle    ') from dual; // 'oracle' 출력
        select TRIM('a' from 'aaaaaaoracleaaaaaaa') from dual; //양쪽 a 지우고 'oracle' 출력

 * 날짜 함수
    - sysdate : 시스템 날짜      //!주의! oracle은 괄호 없음 mysql은 괄호 있음
        select SYSDATEfrom dual;
        select SYSDATE+1 내일 from dual;  //하루 뒤 날짜 출력 -> 연산 가능!
      ex. 각 사원들이 현재까지 근무한 일수를 출력
        select ROUND(SYSDATE-hiredate) from emp;
        //날짜끼리 연산 가능 + round로 반올림(or trunc로 버림)해서 정수로 표시

    - months_between(date1, date2) : 날짜 사이 개월 수
        select * from TRUNC(MONS_BETWEEN(SYSDATE, hiredate)) from emp;

    - add_months(date, number): 특정 개월수를 더한 날짜
        select ename, hiredate, ADD_MONTHS(hiredate, 6) from emp; //입사날+6개월 뒤 날짜 출력

    - next_day() : 해당 날짜 기준으로 돌아오는 요일에 해당되는 날짜를 반환
        select NEXT_DAY(SYSDATE, '수') from dual;  // 오늘 기준(220124)으로 수요일은 며칠인가 -> 220126

    - last_day() : 해당 날짜의 달의 마지막 날 반환
        select hiredate, LAST_DAY(hiredate) from emp; //입사한 달의 마지막날 출력 810220 입사 -> 810228

과제
--3 가장 최근 입사한 사원명 출력
select ename,hiredate from emp order by hiredate desc;
select ename from (select * from emp order by hiredate desc, ename asc) where rownum=1; --중복일 경우 하나만 출력되는 문제 있음
select ename, hiredate from emp where hiredate=(select max(hiredate) from emp); --최근 입사일 검색해서/ 그 값과 같은 hiredate의 조건일 때/ 출력

--4 최대 급여 받는 사원명과 최대급여 금액 출력
select ename, sal from emp order by sal desc;
select ename, sal from (select * from emp order by sal desc, ename asc) where rownum=1; --중복일 경우 하나만 출력되는 문제 있음
select ename, sal from emp where sal=(select max(sal) from emp); --최대 급여를 검색해서/ 그 값과 같은 sal의 조건일 때/ ename, sal 출력