DB 함수 정리

집합연산자 :

SELECT절

UNION

SELECT절;

 

SELECT 쿼리를 여러개 동시에 실행.
여러 쿼리의 결과를 같이 보여줌.

SELECT절 사이 사이 여러번 쓰기 가능.

SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION --쿼리문 연결 
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 20;

쿼리 결과 중복 데이터는 한번만 조회
중복된 데이터도 모두 조회하려면 UNION ALL 사용

SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

쿼리문의 컬럼 개수 동일, 컬럼의 자료형 동일 해야 한다.

아래 쿼리문 오류

SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION --쿼리문 연결 
SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO = 20;

SAL은 자료형이 같기 때문에 조회됨

꼭 같은 컬럼명으로 맞추지 않아도 된다!

SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION --쿼리문 연결 
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = 20;

더미테이블 :

쿼리문 연습을 위해 오라클에서 제공하는 테이블

SELECT 10 * 20
FROM EMP; --데이터에 들어간 수만큼 데이터가 나옴 > 불편 >

SELECT 10 * 20, '자바'
FROM DUAL; -- DUAL > 연습용테이블 : 더미테이블
--문자도 가능!

 

문자와 관련된 함수 :

*배운 함수 복습

UPPER(컬럼명) : 대문자로 변환
LOWER(컬럼명) : 소문자로 변환
INITCAP(컬럼명) : 단어의 첫글자만 대문자로 변환

SELECT 'hello java'
    , UPPER('hello java')
    , LOWER('hello java')
    , INITCAP('hello java')
FROM DUAL;

문자의 길이를 조회하는 함수 :

LENGTH : 글자의 수

LENHTHB : 바이트 수

SELECT LENGTH('hello java') --글자 수
    , LENGTHB('hello java')
    , LENGTH('한글')
    , LENGTHB('한글')--바이트 수
FROM DUAL;

문자의 일부를 추출하는 함수 :

SUBSTR ('글자', 순서지정)

SELECT 'HELLO WORLD'
    , SUBSTR('HELLO WORLD', 1, 3) --첫번째 글자부터 글자 3개
    , SUBSTR('HELLO WORLD', 2, 4) --두번째 글자부터 글자 4개
    , SUBSTR('HELLO WORLD', 1) --1부터 끝까지 글자 다 조회
FROM DUAL;

문자열 연결 함수 :

CONCAT , ||

CONCAT 사용은 번거로워 ||을 사용하여 더 간단하게 씀.

SELECT CONCAT('HELLO', 'WORLD')
    , CONCAT('HELLO', CONCAT('WORLD', 'ORACLE'))
    , 'HELLO' || 'WORLD' || 'ORACLE'
FROM DUAL;

문자열 대체 함수 :

REPLACE ('문자열', '변경할 해당 글자', '해당 글자를 대체할 글자')

SELECT '010-1111-2222'
    , REPLACE('010-1111-2222', '-', ' ')
    , REPLACE('010-1111-2222', '-', '')
FROM DUAL;

빈 공간을 특정 문자로 채우는 함수 : 

LPAD('문자열', 글자수, '빈공간을 뭐로 채울지')

문자열이 글자수보다 작으면 빈공간이 생기고 > 왼쪽부터 그 공간을 채우는 것.

RPAD('문자열', 글자수, '빈공간을 뭐로 채울지')

오른쪽부터 공간 채움.

SELECT 'ORACLE'
    , LPAD('OACLE', 10, '#')
    , RPAD('OACLE', 10, '!')
FROM DUAL;

공백을 제거하는 함수 :

LTRIM('문자열')

RTRIM('문자열')

TRIM('문자열')

SELECT LTRIM('   ORACLE   ') --왼쪽 여백 제거
    , RTRIM('   ORACLE   ') --오른쪽 여백 제거
    , TRIM('   ORACLE   ') --양쪽 여백 제거
FROM DUAL;

숫자와 관련된 함수

반올림 함수  : 

ROUND(숫자)

ROUND(숫자, 표시하고 싶은 소수점 자릿수)

SELECT 123.456
    , ROUND(123.456) --소수점 첫번째 자리에서 반올림
    , ROUND(123.456, 1) --반올림해서 소수점 첫번째자리까지 표시 즉, 두번째 자리에서 반올림
    , ROUND(123.456, 2) 
FROM DUAL;

올림 함수 : 

CEIL(숫자)

버림 함수 : 

FLOOR(숫자)

SELECT 123.456
    , CEIL(123.456) 
    , FLOOR(123.456)
FROM DUAL;

 

나머지 구하는 함수 :

MOD(숫자, 숫자)

*자바에서는 %

SELECT MOD(10, 4) FROM DUAL;

★★형 변환 함수

숫자 <-> 문자 <-> 날짜
조건에서 날짜를 비교할 때는 숫자는 안되고 문자는 가능하다.
다만, 가장 좋은 방법은 날짜 타입으로 형 변환 후 비교를 하는 게 적절!

--연습
--입사일이 07/03/01인 사원의 모든 데이터 조회
SELECT * 
FROM EMP
WHERE HIREDATE = '20070301'; --문자로 비교 가능하지만 좋은 방법은 아님.
--WHERE HIREDATE = '07/03/01'

--2005년 1월 1일 이후로 입사한 사원의 모든 정보 조회
SELECT *
FROM EMP
WHERE HIREDATE >= '20050101'; --조회 가능하나 자료형이 안 맞음

형 변환 정석 방법 : 

- 둘 다 날짜로 변환

SELECT *
FROM EMP
WHERE HIREDATE >= TO_DATE('20050101')

 

-둘 다 문자로 변환

--시,분,초까지 같게 하고 싶은 경우
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYYMMDDHH24MISS') >= ('20050101101010') 
--시,분,초 입력하고 내가 입력한 것과 똑같이 날짜를 문자로 맞춰줌.

 

★숫자> 문자, 문자>숫자

*숫자는 왼쪽 정렬 문자는 오른쪽에 정렬됨. > 다시 확인

TO_CHAR(문자로 바꿀 숫자)

TO_NUMBER(숫자로 바꿀 문자)

SELECT 1001
    , TO_CHAR(1001) 
    , '1001'
    , TO_NUMBER('1001')
FROM DUAL;

★날짜 > 문자, 문자 > 날짜 형 변환

- 날짜를 문자로 변환 :

TO_CHAR(날짜 입력, 날짜를 어떻게 표시할 것인지)

날짜 입력하는 곳에 문자 넣으면 안 됨!

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
    , TO_CHAR(SYSDATE, 'YYYY')
    , TO_CHAR(SYSDATE, 'HH24:MI:SS') --시간만 뽑기도 가능
    , TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') --기호 생략해도 가능
    , TO_CHAR(SYSDATE, 'YYYY/MM/DD')
    , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') --가장 기본적인 표현 방법
FROM DUAL;

--연습
--오늘 날짜를 '2023년 02월 21일'로 출력
SELECT TO_CHAR(SYSDATE, 'YYYY') ||'년 '||
    TO_CHAR(SYSDATE, 'MM') ||'월 '||
    TO_CHAR(SYSDATE, 'DD') ||'일' AS 오늘날짜 --컬럼명 길어짐 별칭
FROM DUAL;

- 문자를 날짜로 변환 : 

TO_DATE('변경할 글자')

SELECT TO_DATE('20230221')
FROM DUAL;

연습

테이블 데이트 테스트 생성

CREATE TABLE DATE_TEST (
    NUM NUMBER PRIMARY KEY
    , MY_DATE DATE
);

데이터 삽입

INSERT INTO DATE_TEST VALUES(1, SYSDATE);
INSERT INTO DATE_TEST VALUES(2, '20230221');
INSERT INTO DATE_TEST VALUES(3, '20230221101010'); --DATE 타입에 문자를 넣으면 가능하지만 시간 입력 > 오류

1행 2행은 삽입 가능하나 3행 오류

아래처럼은 가능

SELECT NUM, MY_DATE
    , TO_CHAR(MY_DATE, 'YYYY-MM-DD')
    , TO_CHAR(MY_DATE, 'YYYY-MM-DD HH24:MI:SS')
FROM DATE_TEST;