230407 서른 다섯 번째 수업_SQL

mary·2023년 4월 10일

국비 수업

목록 보기

36/72

어제 수업과 이어서~~

숫자 처리 함수

1. ABS:

숫자의 절대값을 구하는 함수

[표현법]

ABS(NUMBER)

SELECT ABS(-10) FROM DUAL;
SELECT ABS(-3.14) FROM DUAL;

2. MOD:

두 수를 나눈 나머지값을 반환하는 함수

[표현법]

MOD(NUM1, NUM2)

SELECT MOD(10,3) FROM DUAL;
SELECT MOD(10.9, 2) FROM DUAL; -- 소수 나누기 잘 안 씀

3. ROUND,CEIL,FLOOR,TRUNC:

ROUND: 반올림한 결과를 반환하는 함수

[표현법]

ROUND(NUMBER, [위치])
위치생략시 기본값 0

SELECT ROUND(1234.567) FROM DUAL; --1235 반환
SELECT ROUND(1234.5678, 2) FROM DUAL; -- 1234.57 반환
SELECT ROUND(1234.5678, -2) FROM DUAL; -- 1200 반환

CEIL: 무조건 올림한 결과값을 반환하는 함수
FLOOR: 무조건 내림한 결과값을 반환하는 함수

[표현법]

CEIL(NUMBER)
FLOOR(NUMBER)

SELECT CEIL(123.456) FROM DUAL; -- 124 반환
SELECT CEIL(-123.456) FROM DUAL; -- -123 반환

SELECT FLOOR(123.678) FROM DUAL; -- 123 반환
SELECT FLOOR(-123.678) FROM DUAL; -- -124반환

TRUNC: 위치 지정이 가능한 버림처리 함수

[표현법]

TRUNC(NUMBER, [보일 위치])
위치 생략시 0이어서 무조건 버림

SELECT TRUNC(123.789) FROM DUAL; -- 123 반환
SELECT TRUNC(123.789, 1) FROM DUAL; -- 123.7반환
SELECT TRUNC(123.789, -1) FROM DUAL; -- 120반환
SELECT TRUNC(-123.857, -2) FROM DUAL; -- 100반환

날짜 처리 함수

스크립트에서 날짜 형식 바꿀 수 있는 것(서버에 데이터값 형식은 안 바뀜)

1. SYSDATE:

시스템의 날짜 및 시간 반환

SELECT SYSDATE FROM DUAL;

2. MONTHS_BETWEEN(DATE1, DATE2):

두 날짜 사이의 개월 수

SELECT EMP_NAME, HIRE_DATE, CEIL(SYSDATE-HIRE_DATE) 근무일수
FROM EMPLOYEE;

SELECT EMP_NAME, HIRE_DATE, CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월차' 근무개월수 
FROM EMPLOYEE;
--위에 거 CONCAT 함수로 표현
SELECT EMP_NAME, HIRE_DATE, CONCAT(CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)), '개월차') 근무개월수 
FROM EMPLOYEE;

3. ADD_MONTHS(DATE, NUMBER):

특정날짜에 해당 수만큼의 개월 수를 더해 날짜 반환

SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;

EMPLOYEE테이블에서 사원명, 입사일, 정직원된 날짜(입사일+6개월후) 조회

SELECT EMP_NAME 사원명, HIRE_DATE 입사일, ADD_MONTHS(HIRE_DATE, 6) "정직원된 날짜"
FROM EMPLOYEE;

4. NEXT_DAY(DATE, 요일(문자,숫자)):

특정 날짜 이후에 가까운 요일의 날짜를 반환
1: 일요일 2: 월요일 3: 화요일....

SELECT SYSDATE, NEXT_DAY(SYSDATE, '금') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 6) FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL; --현재 언어가 한국이라 오류

--언어 변경
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL; -- 23/04/14 반환

ALTER SESSION SET NLS_LANGUAGE = KOREAN; -- 언어 다시 한국어로

5. LAST_DAY(DAT):

해당 월의 마지막 날짜를 반환해주는 함수

SELECT LAST_DAY(SYSDATE) FROM DUAL;

EMPLOYEE테이블에서 사원명, 입사일, 입사한 월의 마지막 날짜, 입사한 월의 근무한 일수

SELECT EMP_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE)"입사월 마지막날짜", LAST_DAY(HIRE_DATE)-HIRE_DATE "입사월 근무일수"
FROM EMPLOYEE;

6. EXTRACT:

특정 날짜로부터 년도 | 월 | 일 값을 추출하여 반환해주는 함수(반환형: NUMBER)

EXTRACT(YEAR FROM DATE): 년도만 추출
EXTRACT(MONTH FROM DATE): 월만 추출
EXTRACT(DAY FROM DATE): 일만 추출

EMPLOYEE테이블에서 사원명, 입사년도, 입사월, 입사일 조회

SELECT EMP_NAME
        , EXTRACT(YEAR FROM HIRE_DATE) 입사년도
        , EXTRACT(MONTH FROM HIRE_DATE) 입사월
        , EXTRACT(DAY FROM HIRE_DATE) 입사일
FROM EMPLOYEE
ORDER BY 입사년도, 입사월, 입사일;

형변환 함수:

TO_CHAR:

숫자 또는 날짜 타입의 값을 문자타입으로 변환시키는 함수.
반환결과를 특정 형식에 맞게 출력할 수도 있음.

[표현법]

TO_CHAR(숫자|날짜, [포맷])

숫자타입 => 문자타입

9: 해당 자리의 숫자를 의미
-값이 없을 경우 소수점 이상은 공백, 소수점 이하는 0으로 표기

0: 해당 자리의 숫자를 의미
-값이 없을 경우 0으로 표시하며 숫자의 길이를 고정으로 표시할 때 사용

L: LOCAL, 현재 설정된 나라의 화폐단위

FM: 좌우 9로 치환된 소수점 이상의 공백 및 소수점 이하의 0을 제거
해당자리에 값이 없을 경우 자리차지하지 않음

SELECT TO_CHAR(1234), TO_CHAR(1234,'999999') FROM DUAL; -- ()()1234반환
SELECT TO_CHAR(1234,'000000') FROM DUAL; -- 001234 반환

SELECT TO_CHAR(1234, 'L999999') FROM DUAL; -- \1234 반환
SELECT TO_CHAR(1234, '$999999') FROM DUAL; --직접 명시는 $만 가능

SELECT TO_CHAR(123.456, 'FM999990.999') --123.345 반환
        ,TO_CHAR(1234.56, 'FM9990.9') -- 1234.6 반환
        ,TO_CHAR(0.100, 'FM990.999') -- 0.1 반환
        ,TO_CHAR(0.100, 'FM999.999') -- .1 반환, 보통 일의 자리는 0으로 해주는 것이 좋음
        ,TO_CHAR(123, 'FM999.009') -- 123.00 반환
FROM DUAL;

SELECT TO_CHAR(123.456, '999990.999') --()()()123.456반환
        ,TO_CHAR(1234.56, '9990.9') --1234.6 반환
        ,TO_CHAR(0.100, '990.999') -- ()()0.100 반환
        ,TO_CHAR(0.100, '999.999') -- ()()().100 반환
        ,TO_CHAR(123, '999.009') -- 123.000 반환
FROM DUAL;

EMPLOYEE테이블에서 월급과 연봉을 가시성 좋게 조회

SELECT EMP_NAME, TO_CHAR(SALARY, 'L99,999,999') 급여, TO_CHAR(SALARY*12, 'L999,999,999') 연봉
FROM EMPLOYEE;

날짜타입 => 문자타입

--시간
SELECT TO_CHAR(SYSDATE, 'AM') KOREA
        ,TO_CHAR(SYSDATE, 'PM', 'NLS_DATE_LANGUAGE=AMERICAN') AMERICA
FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL; --12시간 형식
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; --24시간 형식

--날짜
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY DY') FROM DUAL;
--DAY: 금요일 DY: 금
SELECT TO_CHAR(SYSDATE, 'MON, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DL') FROM DUAL; -- 해당 년월일요일 반환

--년도
SELECT TO_CHAR(SYSDATE, 'YYYY')
        ,TO_CHAR(SYSDATE, 'YY')
        ,TO_CHAR(SYSDATE, 'RRRR')-- =YYYY
        ,TO_CHAR(SYSDATE, 'RR')-- =YY
        ,TO_CHAR(SYSDATE, 'YEAR') -- TWENTY TWENTY-THREE 반환
FROM DUAL;

--월
SELECT TO_CHAR(SYSDATE, 'MM')
        ,TO_CHAR(SYSDATE, 'MON')--4월 반환
        ,TO_CHAR(SYSDATE, 'MONTH')--4월 반환
        ,TO_CHAR(SYSDATE, 'RM') -- IV 반환, 로마자 월값
FROM DUAL;

--일
SELECT TO_CHAR(SYSDATE, 'DDD') --097반환, 년을 기준으로 며칠째인지
        ,TO_CHAR(SYSDATE, 'DD') -- 07반환, 월을 기준으로 며칠째인지
        ,TO_CHAR(SYSDATE, 'D') --6반환, 주 기준으로 며칠째인지
FROM DUAL;

-- 요일
SELECT TO_CHAR(SYSDATE, 'DAY')
        ,TO_CHAR(SYSDATE, 'DY')
FROM DUAL;

EMPLOYEE테이블에서 사원명, 입사일(형식 '22-04-07')

SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YY-MM-DD') 입사일
FROM EMPLOYEE;

EMPLOYEE테이블에서 사원명, 입사일(형식 '2023년 4월 7일 금요일')

SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'DL') 입사일
FROM EMPLOYEE;

EMPLOYEE테이블에서 사원명, 입사일(형식 '2223년 04월 07일')

SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') 입사일
FROM EMPLOYEE;

숫자,문자타입 => 날짜타입

TO_DATE:

숫자 또는 문자 타입을 날짜타입으로 변환

TO_DATE(숫자|문자, [포맷])

SELECT TO_DATE(20230407) FROM DUAL;
SELECT TO_DATE(230407) FROM DUAL; --년도 두자리만 써도 똑같이 반환
SELECT TO_DATE(010223) FROM DUAL; --첫숫자가 0이면 오류
SELECT TO_DATE('010223') FROM DUAL; --문자타입으로 넣어줌

SELECT TO_DATE('980630', 'YYMMDD') FROM DUAL; --2098/06/30 반환
SELECT TO_DATE('980630', 'RRMMDD') FROM DUAL; --1998/06/30 반환
--RR: 해당 두 자리가 50미만이면 현재 세기, 이상이면 이전세기

문자타입 => 숫자타입

TO_NUMBER:

문자 타입을 숫자타입으로 변환
자동형변환 되기 때문에 잘 안 쓰긴 함

TO_NUMBER(문자, [포맷])

SELECT TO_NUMBER('01234567') FROM DUAL; --1234567 반환
SELECT '1000' + '5000' FROM DUAL;
SELECT '1,000' + '5,000' FROM DUAL; -- , 때문에 자동변환 불가하여 오류
SELECT TO_NUMBER('1,000,000', '9,999,999') + TO_NUMBER('550,000', '999,999')
FROM DUAL; --1550000 반환, 포맷 설정으로 연산 가능

NULL처리 함수

NVL(컬럼, 해당컬럼값이 NULL일때 반환할 값)

NVL(NULL VALUE)

SELECT EMP_NAME, NVL(BONUS, 0)
FROM EMPLOYEE;

전사원의 이름, 보너스포함 연봉

SELECT EMP_NAME, (SALARY*NVL(BONUS,0)+ SALARY)*12
FROM EMPLOYEE;

전사원의 이름, 부서유무

SELECT EMP_NAME, NVL(DEPT_CODE,'부서없음') 부서유무
FROM EMPLOYEE
ORDER BY EMP_NAME ASC;

★NVL2(컬럼, 반환값1, 반환값2):

컬럼값이 존재하면 반환 1
컬럼값이 NULL이면 반환값 2
3항 연산자와 비슷한 연산으로 생각하면 됨

SELECT EMP_NAME, BONUS, NVL2(BONUS, 0.7, 0.2)
FROM EMPLOYEE;

SELECT EMP_NAME, NVL2(DEPT_CODE, '부서있음', '부서없음')
FROM EMPLOYEE;

NULLIF(비교대상1, 비교대상2):

두 대상이 일치하면 NULL 반환
일치하지 않으면 비교대상1 값을 반환

SELECT NULLIF('123', '123') FROM DUAL;
SELECT NULLIF('123', '456') FROM DUAL;

선택함수

DECODE(비교하고자하는 대상(컬럼|산술연산|함수식), 비교값1, 결과값1, 비교값2, 결과값2...)

자바에서는

    SWITCH(비교대상) {
        CASE 비교값1 : 
            실행구문(결과값1);
        CASE 비교값2 : 
            실행구문(결과값2);
        ...
        DEFAULT:
            실행구문;
    }

EMPLOYEE테이블에서 사번, 사원명, 주민번호, 성별

SELECT EMP_ID, EMP_NAME, SUBSTR(EMP_NO, 1, 8) || '******' 주민번호
        , DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남성', '2','여성','3', '남성', '4','여성') 성별
FROM EMPLOYEE;

직원의 급여 조회시 각 직급별로 인상하여 조회

-- J7인 사원은 급여를 10%인상 (SALARY 1.1)
-- J6인 사원은 급여를 15%인상 (SALARY 1.15)
-- J5인 사원은 급여를 20%인상 (SALARY 1.2)
--그 외 사원은 급여를 5%인상(SALARY 1.05)

SELECT EMP_NAME, DECODE(JOB_CODE, 'J7',SALARY*1.1
                                , 'J6',SALARY*1.15
                                , 'J5',SALARY*1.2
                                      , SALARY*1.05)"인상된 급여" --그 외는 디폴트로 해주면 됨
FROM EMPLOYEE;

CASE WHEN THEN END:

자바에서 IF ELSE문과 유사

CASE WHEN 조건식1 THEN 결과값1
         WHEN 조건식2 THEN 결과값2
         ...
         ELSE 나머지 결과값
    END

급여가 500만원 이상인 고급, 350~500만원 미만은 중급, 그외는 초급

SELECT EMP_NAME, SALARY
        ,CASE WHEN SALARY >= 5000000 THEN '고급'
              WHEN SALARY >=3500000 THEN '중급'
              ELSE '초급' 
        END "급여 수준"
FROM EMPLOYEE;

그룹 함수

SUM(숫자타입컬럼) :

해당 컬럼 값들의 총합을 반환하는 함수

남자 사원의 총 급여의 합

SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8,1) IN ('1','3');

부서코드가 D5인 사원들의 총 연봉(보너스포함)의 합

SELECT SUM((SALARY*NVL(BONUS,0)+SALARY)*12) 
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

AVG(숫자타입컬럼):

해당 컬럼 값의 평균을 반환해주는 함수

전사원 급여의 평균

SELECT TO_CHAR(ROUND(AVG(SALARY)), 'L999,999,999')
FROM EMPLOYEE;

MIN, MAX:

MIN(모든컬럼): 해당 컬럼 값들 중 가장 작은값 반환
MAX(모든컬럼): 해당 컬럼 값들 중 가장 큰 값 반환

SELECT MIN(SALARY),MIN(EMP_NAME), MIN(HIRE_DATE)-- 가장 오래된 날짜
FROM EMPLOYEE;

SELECT MAX(SALARY),MAX(EMP_NAME), MAX(HIRE_DATE)-- 가장 최근 날짜
FROM EMPLOYEE;

COUNT(*|컬럼|DISTINCT컬럼):

행의 개수 반환

COUNT(*): 조회된 결과의 모든 행의 개수
COUNT(컬럼): 제시한 컬럼값의 NULL값을 제외한 행의 개수
COUNT(DISTINCT 컬럼): 해당 컬럼값 중복을 제거한 후의 행의 개수

여자 사원 수

SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) IN ('2','4');

보너스를 받는 사원 수

SELECT COUNT(BONUS)
FROM EMPLOYEE;

현재 사원들이 총 몇개의 부서에 분포되어 있는지

SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;

mary

내 인생을 망치러 온 나의 구원, 개발

이전 포스트

230410 서른 여섯 번째 수업_SQL

다음 포스트

230407 서른 다섯 번째 수업_SQL

국비 수업

숫자 처리 함수

1. ABS:

2. MOD:

3. ROUND,CEIL,FLOOR,TRUNC:

날짜 처리 함수

1. SYSDATE:

2. MONTHS_BETWEEN(DATE1, DATE2):

3. ADD_MONTHS(DATE, NUMBER):

EMPLOYEE테이블에서 사원명, 입사일, 정직원된 날짜(입사일+6개월후) 조회

4. NEXT_DAY(DATE, 요일(문자,숫자)):

5. LAST_DAY(DAT):

EMPLOYEE테이블에서 사원명, 입사일, 입사한 월의 마지막 날짜, 입사한 월의 근무한 일수

6. EXTRACT:

EMPLOYEE테이블에서 사원명, 입사년도, 입사월, 입사일 조회

형변환 함수:

TO_CHAR:

숫자타입 => 문자타입

EMPLOYEE테이블에서 월급과 연봉을 가시성 좋게 조회

날짜타입 => 문자타입

EMPLOYEE테이블에서 사원명, 입사일(형식 '22-04-07')

EMPLOYEE테이블에서 사원명, 입사일(형식 '2023년 4월 7일 금요일')

EMPLOYEE테이블에서 사원명, 입사일(형식 '2223년 04월 07일')

숫자,문자타입 => 날짜타입

TO_DATE:

문자타입 => 숫자타입

TO_NUMBER:

NULL처리 함수

NVL(컬럼, 해당컬럼값이 NULL일때 반환할 값)

전사원의 이름, 보너스포함 연봉

전사원의 이름, 부서유무

★NVL2(컬럼, 반환값1, 반환값2):

NULLIF(비교대상1, 비교대상2):

선택함수

DECODE(비교하고자하는 대상(컬럼|산술연산|함수식), 비교값1, 결과값1, 비교값2, 결과값2...)

EMPLOYEE테이블에서 사번, 사원명, 주민번호, 성별

직원의 급여 조회시 각 직급별로 인상하여 조회

CASE WHEN THEN END:

급여가 500만원 이상인 고급, 350~500만원 미만은 중급, 그외는 초급

그룹 함수

SUM(숫자타입컬럼) :

남자 사원의 총 급여의 합

부서코드가 D5인 사원들의 총 연봉(보너스포함)의 합

AVG(숫자타입컬럼):

전사원 급여의 평균

MIN, MAX:

COUNT(*|컬럼|DISTINCT컬럼):

여자 사원 수

보너스를 받는 사원 수

현재 사원들이 총 몇개의 부서에 분포되어 있는지

230410 서른 여섯 번째 수업_SQL

230412 서른 일곱 번째 수업_SQL

0개의 댓글