날짜(DATE)관련 함수
SYSDATE : 시스템에 현재 시간(년,월,일,시,분,초)을 반환
SELECT SYSDATE FROM DUAL;
SYSTIMESTAMP: SYSDATE + MS 단위 추가
SELECT SYSTIMESTAMP FROM DUAL;
-- TIMESTAMP : 특정 시간을 나타내거나 기록하기 위한 문자열
MONTHS_BETWEEN(날짜, 날짜) : 두 날짜의 개월 수 차이 반환
SELECT ROUND( MONTHS_BETWEEN(SYSDATE, '2023-07-10') , 3) "수강 기간(개월)"
FROM DUAL;
-- 소수점 3째 자리까지 나타냄
-- EMPLOYEE 테이블에서 사원의 이름, 입사일, 근무한 개월 수, 근무 년차 조회
SELECT EMP_NAME, HIRE_DATE,
CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "근무한 개월 수",
CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 ) || '년차' "근무 년차"
FROM EMPLOYEE;
ADD_MONTHS(날짜, 숫자) : 날짜에 숫자만큼의 개월 수를 더함. (음수도 가능)
SELECT ADD_MONTHS(SYSDATE, 4) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;
LAST_DAY(날짜) : 해당 달의 마지막 날짜를 구함.
SELECT LAST_DAY('2024-02-01') FROM DUAL;
EXTRACT : 년, 월, 일 정보를 추출하여 리턴
SELECT EMP_NAME,
EXTRACT(YEAR FROM HIRE_DATE) || '년' ||
EXTRACT(MONTH FROM HIRE_DATE) || '월' ||
EXTRACT(DAY FROM HIRE_DATE) || '일' AS 입사일
FROM EMPLOYEE;
형변환 함수
: 문자열(CHAR), 숫자(NUMBER), 날짜(DATE) 끼리 형변환 가능
문자열로 변환
: TO_CHAR(날짜, [포맷]) : 날짜형 데이터를 문자형 데이터로 변경
: TO_CHAR(숫자, [포맷]) : 숫자형 데이터를 문자형 데이터로 변경
숫자 변환 시 포맷 패턴
- 9 : 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬
- 0 : 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬 + 빈칸 0 추가
- L : 현재 DB에 설정된 나라의 화폐 기호
SELECT TO_CHAR(1234, '99999') FROM DUAL; -- ' 1234'
SELECT TO_CHAR(1234, '00000') FROM DUAL; -- '01234'
SELECT TO_CHAR(1000000, 'L9,999,999') FROM DUAL; -- ￦1,000,000
SELECT TO_CHAR(1000000, '$9,999,999') FROM DUAL;
날짜 변환 시 포맷 패턴
- YYYY : 년도 / YY : 년도(짧게)
- RRRR : 년도 / RR : 년도(짧게)
- MM : 월
- DD : 일
- AM / PM : 오전/오후 표시
- HH : 시간 / HH24 : 24시간 표기법
- MI : 분 / SS : 초
- DAY : 요일(전체) / DY : 요일(요일명만 표시)
-- 2023-01-10 10:21:00 화요일
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS DAY') FROM DUAL;
-- 2023년 01월 10일 (화)
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" (DY)') FROM DUAL;
날짜로 변환 TO_DATE
TO_DATE(문자형 데이터, [포맷]) : 문자형 데이터를 날짜로 변환
TO_DATE(숫자형 데이터, [포맷]) : 숫자형 데이터를 날짜로 변환
SELECT TO_DATE('2023-01-10')FROM DUAL; -- DATE 타입으로 변환
SELECT TO_DATE(20230110) FROM DUAL; -- DATE 타입으로 변환
SELECT TO_DATE('510505', 'YYMMDD') FROM DUAL; --2051-05-05
SELECT TO_DATE('510505', 'RRMMDD') FROM DUAL; --1951-05-05 과거를 따짐
-- Y 패턴 : 현재 세기(21세기 == 20XX년 == 2000년대)
-- R 패턴 : 1세기를 기준으로 절반(50년) 이상인 경우 이전세기 (1900년대)
-- 절반(50년) 미만인 경우 현재 세기(2000년대)
-- EMPLOYEE 테이블에서 각 직원 태어난 생년월일 (1990년 05월 13일) 조회(별칭 생년월일)
SELECT EMP_NAME,
TO_CHAR(TO_DATE ( SUBSTR( EMP_NO, 1, INSTR(EMP_NO, '-') -1), 'RRMMDD'),
'YYYY"년" MM"월" DD"일"') AS 생년월일-- TO_DATE로 감싸기 전엔 문자열
FROM EMPLOYEE;
숫자 형변환
TO_NUMBER(문자데이터, [포맷]) : 문자형데이터를 숫자 데이터로 변경
SELECT '1,000,000' + 500000 FROM DUAL;
-- SQL Error [1722][42000]: ORA-01722: 수치가 부적합합니다
SELECT TO_NUMBER('1,000,000', '9,999,999') + 5000000 FROM DUAL;
NULL 처리 함수
NVL(컬럼명, 컬럼값이 NULL 일때 바꿀 값) : NULL인 컬럼값을 다른 값으로 변경
NULL과 산술 연산을 진행하면 결과는 무조건 NULL
SELECT EMP_NAME, SALARY, NVL(BONUS, 0), SALARY * NVL(BONUS, 0)
FROM EMPLOYEE; -- 보너스가 NULL인자리 다 0값으로 변함
NVL2(컬럼명, 바꿀값1, 바꿀값2)
- 해당 컬럼에 값이 있으면 바꿀값1로 변경
- 해당 컬럼이 NULL이면 바꿀값2로 변경
EMPLOYEE테이블에서 보너스를 받으면 'O' ,안받으면 'X' 조회
SELECT EMP_NAME, BONUS, NVL2(BONUS, 'O', 'X') FROM EMPLOYEE;
선택 함수
: 여러가지 경우에 따라 알맞은 결과를 선택 할 수 있음
DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2......., 아무것도 일치하지 않을 때 )
: 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환
: 일치하는 값을 확인(자바의 SWITCH와 비슷함)
직원의 성별 구하기(남:1 / 여:2)
SELECT EMP_NAME, EMP_NO,
DECODE( SUBSTR(EMP_NO, 8 , 1), '1', '남성', '2', '여성' ) 성별
FROM EMPLOYEE;
-- 직원의 급여를 인상하고자 한다 직급 코드가 J7인 직원은 20% 인상,
직급 코드가 J6인 직원은 15% 인상, 직급 코드가 J5인 직운은 10% 인상,
그 외 직급은 5% 인상 이름, 직급코드, 원래 급여, 인상률, 인상된 급여
SELECT EMP_NAME, JOB_CODE , SALARY ,
DECODE(JOB_CODE, 'J7', '20%', 'J6','15%','J5','10%','5%') 인상률,
DECODE(JOB_CODE, 'J7', SALARY 1.2,
'J6', SALARY 1.15,
'J5', SALARY 1.1,
SALARY 1.05) "인상 된 급여"
FROM EMPLOYEE;
CASE WHEN 조건식 THEN 결과값
WHEN 조건식 THEN 결과값
ELSE 결과값
END
: 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환
: 조건은 범위 값 가능
-- EMPLOYEE테이블에서 급여가 500만원 이상이면 '대',
급여가 300만원 이상 500만 미만 이면 '중', 급여가 300만원 미만이면 '소'로 조회SELECT EMP_NAME, SALARY,
CASE
WHEN SALARY >= 5000000 THEN '대'
WHEN SALARY >= 3000000 THEN '중'
ELSE '소'
END "급여 받는 정도"
FROM EMPLOYEE;
그룹 함수
: 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등의 하나의 결과 행으로 반환하는 함수
SUM(숫자가 기록된 컬럼명) : 합계
-- 모든 직원의 급여 합
SELECT SUM(SALARY) FROM EMPLOYEE;
AVG(숫자가 기록된 컬럼명) : 평균
SELECT ROUND( AVG(SALARY) ) FROM EMPLOYEE;
-- 부서 코드가 'D9'인 사원들의 급여 합, 평균
SELECT SUM(SALARY), ROUND(AVG(SALARY)) --3
FROM EMPLOYEE -- 1
WHERE DEPT_CODE = 'D9'; --2
-- 해석 순서
MIN(컬럼명) : 최소값
MAX(컬럼명) : 최대값
--> 타입 제한 없음 ( 숫자 : 대/소, 날짜 : 과거/미래 , 문자열 : 문자 순서 )
-- 급여 최소값, 가장빠른 입사일, 알파벳순서가 가장빠른 이메일
SELECT MIN(SALARY), MIN(HIRE_DATE), MIN(EMAIL)
FROM EMPLOYEE;반대는 MAX(SALARY), MAX(HIRE_DATE), MAX(EMAIL)
-- EMPLOYEE 테이블에서 급여를 가장 많이 받는 사원의
-- 이름, 급여, 직급 코드 조회
SELECT EMP_NAME, SALARY, JOB_CODE
FROM EMPLOYEE
WHERE SALARY = ( SELECT MAX(SALARY) FROM EMPLOYEE );
-- 서브쿼리 + 그룹함수
COUNT( | 컬럼명) : 행 개수를 헤아려서 리턴
COUNT([DISTINCT] 컬럼명) : 중복을 제거한 행 개수를 헤아려 리턴
COUNT(*) : NULL을 포함한 전체 행 개수 리턴
COUNT(컬럼명) : NULL을 제외한 실제 값이 기록된 행 개수를 리턴
-- EMPLOYEE 테이블의 행의 갯수
SELECT COUNT(*) FROM EMPLOYEE;
-- BONUS를 받는 사원 수
SELECT COUNT(BONUS) FROM EMPLOYEE; -- 9
SELECT COUNT(*) FROM EMPLOYEE
WHERE BONUS IS NOT NULL; -- 9
--> 좋은 방법은 아닌데 이런 방법도 있다
-- EMPLOYEE 테이블에서 성별이 남성인 사원의 수 조회
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '1';
