▶ 함수
컬럼값 | 지정된값을 읽어 연산한 결과를 반환하는 것
| 단일행 함수 | N개의 행의 컬럼 값을 전달하여 N개의 결과가 반환 |
| 그룹 함수 | N개의 행의 컬럼 값을 전달하여 1개의 결과가 반환 (그룹의 수가 늘어나면 그룹의 수 만큼 결과를 반환) |
* 함수는 SELECT절, WHERE절, ORDER BY절
GROUP BY절, HAVING절에서 사용 가능
▶ 단일행 함수
<문자열 관련 함수>
1) LENGTH(문자열|컬럼명) : 문자열의 길이 반환
SELECT 'HELLO WORLD', LENGTH('HELLO WORLD')
FROM DUAL; -- DUMMY TABLE(가상/가짜 테이블)
#결과
2) INSTR(문자열 | 컬럼명, '찾을 문자열' [, 찾을 시작 위치 [, 순번]])
: 찾을 시작 위치부터 지정된 순번째 찾은 문자열의 시작 위치를 반환
문자열에서 맨 앞에 있는 'B'의 위치 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B')
FROM DUAL; -- 3 (INDEX 아님, 1부터 시작하는 COUNT 개념)
문자열에서 5번 부터 검색 시작해서 처음 찾은 'B'의 위치 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B', 5)
FROM DUAL; -- 9
문자열에서 5번 부터 검색 시작해서 두번째로 찾은 'B'의 위치 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B', 5, 2)
FROM DUAL; -- 10
3) SUBSTR(문자열 | 컬럼명, 시작위치 [,길이])
- 문자열을 시작 위치부터 지정된 길이 만큼 잘라내서 반환
- 길이 미작성 시 시작 위치 부터 끝까지 잘라내서 반환
[시작 위치 + 길이 지정]
SELECT SUBSTR('ABCDEFG', 2, 3)
FROM DUAL; -- BCD
[시작 위치만 지정]
SELECT SUBSTR('ABCDEFG', 4)
FROM DUAL; -- DEFG
4) SUBSTR() + INSTR() 같이 사용하기
EMPLOYEE 테이블에서 사원명, 이메일 아이디 (@ 앞에까지 문자열)를
이메일 아이디 오름차순으로 조회
SELECT EMP_NAME,
SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') - 1) "이메일 아이디"
FROM EMPLOYEE
ORDER BY "이메일 아이디" ASC;
5) TRIM([ [옵션] 문자열 | 컬럼명 FROM ] 문자열 | 컬럼명)
: 주어진 문자열의 앞쪽|뒤쪽|양쪽에 존재하는 지정된 문자열을 제거
[옵션] : LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽, 기본값)
SELECT ' 기 준 ',
TRIM(LEADING ' ' FROM ' 기 준 '), --'기 준 '
TRIM(TRAILING ' ' FROM ' 기 준 '), --' 기 준'
TRIM(BOTH ' ' FROM ' 기 준 ') --'기 준'
FROM DUAL;
SELECT '##@기 준@##',
TRIM(LEADING '#' FROM '##@기 준@##'), -- '@기 준@##'
TRIM(TRAILING '#' FROM '##@기 준@##'), -- '##@기 준@'
TRIM(BOTH '#' FROM '##@기 준@##') -- '@기 준@'
FROM DUAL;
6) REPLACE(문자열 | 컬럼명, 찾을 문자열, 바꿀 문자열)
SELECT
NATIONAL_NAME,
REPLACE(NATIONAL_NAME, '한국', '대한민국')
FROM "NATIONAL"; -- "테이블명" == 이름이 "테이블명"과 같은 테이블
🪄 숫자 관련 함수
1) MOD(숫자 | 컬럼명, 나눌 값) : 결과로 나머지 반환
SELECT MOD(105, 100) FROM DUAL; -- 5
2) ABS(숫자 | 컬럼명) : 절대 값
SELECT ABS(10), ABS(-10) FROM DUAL; -- 10, 10
3) CEIL(숫자 | 컬럼명) : 올림 -> 정수 형태로 반환
FLOOR(숫자 | 컬럼명) : 내림 -> 정수 형태로 반환
SELECT CEIL(1.1), FLOOR(1.9) FROM DUAL; -- 2, 1
4) ROUND(숫자 | 컬럼명 [, 소수점 위치]) : 반올림
소수점 위치 지정 x : 소수점 첫째 자리에서 반올림 -> 정수 표현
- 소수점 위치 지정 O
1) 양수 : 지정된 위치의 소수점 자리까지 표현
2) 음수 : 지정된 위치의 정수 자리까지 표현
SELECT
123.456,
ROUND(123.456), -- 123
ROUND(123.456, 0), -- 123 (0이 ROUND 기본값)
ROUND(123.456, 1), -- 123.5
ROUND(123.456, 2), -- 123.46
ROUND(123.456, -1), -- 120
ROUND(123.456, -2) -- 100
FROM DUAL;
5) TRUNC(숫자 | 컬럼명 [,소수점 위치]) : 버림 (잘라내기)
SELECT
123.456,
TRUNC(123.456), -- 123
TRUNC(123.456, 1) -- 123.4 (둘째 자리부터 버림)
FROM DUAL;
※ 버림(TRUNC), 내림(FLOOR) 차이점
SELECT
-123.5,
TRUNC(-123.5), -- -123
FLOOR(-123.5) -- -124
FROM DUAL;
🪄 날짜 관련 함수
1) SYSDATE
SELECT SYSDATE
FROM DUAL;
2) MONTHS_BETWEEN
SELECT
MONTHS_BETWEEN( TO_DATE('2024-12-16', 'YYYY-MM-DD'), CURRENT_DATE)
FROM DUAL;
3) ADD_MONTHS
SELECT
CURRENT_DATE,
CURRENT_DATE + 31,
CURRENT_DATE + 61,
ADD_MONTHS(CURRENT_DATE, 1),
ADD_MONTHS(CURRENT_DATE, 2)
FROM DUAL;
4) LAST_DAY
SELECT
LAST_DAY(CURRENT_DATE),
LAST_DAY('2024-09-01')
FROM DUAL;
현재 날짜 : 2024-08-24
다음달 1일, 다음달 말일, 다음달 일 수 조회하기
SELECT
LAST_DAY(CURRENT_DATE) + 1 "다음달 1일",
LAST_DAY( ADD_MONTHS(CURRENT_DATE, 1) ) "다음달 말일",
LAST_DAY( ADD_MONTHS(CURRENT_DATE, 1) )
- LAST_DAY(CURRENT_DATE)
FROM DUAL;
5) EXTRACT (YEAR | MONTH | DAY FROM 날짜)
SELECT
EXTRACT(YEAR FROM CURRENT_DATE) 년,
EXTRACT(MONTH FROM CURRENT_DATE) 월,
EXTRACT(DAY FROM CURRENT_DATE) 일
FROM DUAL;
🪄 형변환 함수
1) TO_CHAR
[FORMAT]
| 9 | 숫자 한 칸을 의미, 오른쪽 정렬 |
| 0 | 숫자 한 칸을 의미, 오른쪽 정렬, 빈 칸에 0을 추가 |
| L | 현재 시스템이나 DB에 설정된 나라의 화폐 기호 |
| , | 숫자의 자릿수 구분 |
▷ 숫자 -> 문자열 변환 확인
SELECT 1234, TO_CHAR(1234) FROM DUAL;
▷ 지정된 칸 내부에서 문자열로 변환하기
SELECT 1234, TO_CHAR(1234, '999999999')
FROM DUAL; -- ' 1234'
SELECT 1234, TO_CHAR(1234, '000000000')
FROM DUAL; -- '000001234'
▷ 날짜 → 문자열
[FORMAT]
| YY | 년도(짧게) ex) 24 | RR | 년도(짧게) ex) 24 |
| YYYY | 년도(길게) ex) 2024 | RRRR | 년도(길게) ex) 2024 |
| MM | 월 | DD | 일 |
| AM/PM | 오전/오후 | HH | 시간 (12시간) |
| DAY | 요일(전체) ex) 월요일, MONDAY | HH24 | 시간 (24시간) |
| DY | 요일(짧게) ex) 월, MON | MI | 분 |
| SS |
초 |
오늘 날짜 YYYY/MM/DD 문자열로 변환
SELECT TO_CHAR(CURRENT_DATE, 'YYYY/MM/DD')
FROM DUAL;
2024.08.24 (토) 오후 21:15:05 형식으로 변환
SELECT TO_CHAR(CURRENT_DATE, 'YYYY.MM.DD (DY) PM HH24:MI:SS')
FROM DUAL;
24년 08월 24일 토요일 오후 09시 15분 59초 형식으로 변환
SELECT
TO_CHAR(CURRENT_DATE,
'YY"년" MM"월" DD"일" DAY PM HH"시" MI"분" SS"초"')
FROM DUAL;※ /, (), :, - 는 일반적으로 날짜 표기 시 사용하는 기호
→ 패턴으로 인식되어 오류가 발생하지 않음!
2) TO_DATE
SELECT
'2024-08-16' 문자열,
TO_DATE('2024-08-16') 날짜
FROM DUAL;
* 일반적인 형식이 아닌 경우 포맷 지정 필수!!!
SELECT TO_DATE('16082024', 'DDMMYYYY')
FROM DUAL;
연도 패턴 Y, R 차이점
연도가 두 자리만 작성되어있는 경우
- 50 미만 : Y,R 둘 다 누락된 연도 앞부분에 현재 세기(21C == 2000년대) 추가
- 50 이상 : Y : 현재 세기(2000년대) 추가
- R : 이전 세기(1900년대) 추가
-- 50 미만 확인
SELECT
TO_DATE('49-12-25', 'YY-MM-DD'), -- 2049
TO_DATE('49-12-25', 'RR-MM-DD') -- 2049
FROM DUAL;
-- 50 이상 확인
SELECT
TO_DATE('50-12-25', 'YY-MM-DD'), -- 2050
TO_DATE('50-12-25', 'RR-MM-DD') -- 1950
FROM DUAL;
3) TO_NUMBER
SELECT TO_NUMBER('$1,500', '$9,999')
FROM DUAL;
🪄 NULL 처리 연산 IS NULL / IS NOT NULL
1) NVL(컬럼명, 컬럼 값이 NULL일 경우 변경할 값)
EMPLOYEE 테이블에서 사번, 이름, 전화번호 조회
단, 전화번호가 없다면(NULL) '없음' 으로 조회
SELECT
EMP_ID,
EMP_NAME,
NVL(PHONE, '없음') AS PHONE
FROM
EMPLOYEE;
2) NVL2(컬럼명, NULL이 아닌 경우 변경할 값, NULL인 경우 변경할 값)
EMPLOYEE 테이블에서 사번, 이름, 전화번호 조회
전화 번호가 없으면 '없음' / 전화 번호가 있으면 '010********' 형식으로 변경해서 조회
* RPAD(문자열, 길이, 바꿀문자)
- : 문자열 전체에서 오른쪽을 지정된 길이 만큼 다른 문자로 변경
SELECT
EMP_ID,
EMP_NAME,
NVL2(PHONE,
RPAD(SUBSTR(PHONE,1,3), LENGTH(PHONE), '*'),
'없음') AS "전화번호"
FROM EMPLOYEE;
🪄 선택 함수
1) DECODE
비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환
SELECT
EMP_NAME,
EMP_NO,
DECODE( SUBSTR(EMP_NO, 8, 1),
'1', '남자', '2', '여자') AS "성별"
FROM EMPLOYEE;
2) CASE
비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환(조건은 범위 값 가능)
CASE
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
ELSE 결과
END
EMPLOYEE 테이블에서 사번, 이름, 급여, 구분을 조회
- 구분은 받는 급여에 따라 초급, 중급, 고급으로 조회
- 급여 600만 이상 = '고급'
-급여 400만 이상 ~ 600만 미만 = '중급'
- 급여 400미만 = '초급'
단, 부서코드가 D6, D9인 사원만 직급코드 오름차순으로 조회
SELECT
EMP_ID 사번,
EMP_NAME 이름,
SALARY 급여,
CASE
WHEN SALARY >= 6000000 THEN '고급'
WHEN SALARY >= 4000000 THEN '중급'
ELSE '초급'
END 구분
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D6', 'D9')
ORDER BY JOB_CODE ASC;
🪄 그룹 함수
하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 하나의 컬럼으로 반환하는 함수
1) SUM
모든 사원의 급여 합 조회
SELECT SUM(SALARY) FROM EMPLOYEE;
-- 94096240
2) AVG
모든 사원의 급여 평균 조회
SELECT FLOOR(AVG(SALARY)) FROM EMPLOYEE;
-- 4,091,140
3) COUNT
| COUNT(*) | 조회된 모든 행의 개수를 반환 |
| COUNT(컬럼명) |
지정된 컬럼 값이 NULL이 아닌 행의 개수를 반환
(NULL인 행 미포함) |
| COUNT(DISTINCT 컬럼명) | 지정된 컬럼에서 중복 값을 제외한 행의 개수를 반환 |
EMPLOYEE 테이블의 모든 행의 개수 조회
SELECT COUNT(*) FROM EMPLOYEE;
4) MAX / MIN
부서 코드가 'D6'인 사원들 중 제일 많은 급여와, 제일 적은 급여 조회
SELECT MAX(SALARY), MIN(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6';
모든 사원 중 가장 빠른 입사일, 최근 입사일
이름 오름차순에서 제일 먼저 작성되는 이름, 마지막에 작성되는 이름
SELECT
MIN(HIRE_DATE) "가장 빠른 입사일",
MAX(HIRE_DATE) "최근 입사일",
MIN(EMP_NAME) "제일 먼저 작성되는 이름",
MAX(EMP_NAME) "마지막에 작성되는 이름"
FROM EMPLOYEE;'BackEnd > MySQL' 카테고리의 다른 글
| [DB]SELECT (0) | 2024.08.24 |
|---|---|
| [DB]Oracle Database의 개요 (0) | 2024.08.24 |
| [DB] ORDER BY절 (0) | 2024.08.24 |
| [DB]WHERE절 (0) | 2024.08.24 |
| [DB] DDL(Data Definition Language) (ALTER, DROP) (0) | 2024.08.23 |