#20. SQL(SELECT)

20-1. 함수

< 함수 FUNCTION >

- 자바로 따지면 메소드와 같은 존재

- 전달된 값들을 읽어서 계산한 결과를 반환

 

#1. 문자열

LENGTH(STR) : 전달된 문자열의 글자 수 반환

LENGTHB(STR) : 전달된 문자열의 바이트 수 반환(한글은 3Byte, 나머지는 1Byte)

SELECT LENGTH('오라클!'), LENGTHB('오라클!')
  FROM DUAL;

INSTR(STR) : 문자열로부터 특정 문자의 위치값 반환

SELECT INSTR('AABBBCCBABCAA', 'B', 1, 2)
  FROM DUAL;

SUBSTR(STR, POSITION, LENGTH) : 문자열로부터 특정 문자열을 추출해서 반환

시작 위치가 음수일 경우 뒤에서부터 N번째 위치로부터 문자를 추출한다.

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

LPAD / RPAD(STR, 최종적으로 반환할 문자의 길이(바이트), 덧붙이고자하는 문자)
    : 제시한 문자열에 임의의 문자를 왼쪽 또는 오른쪽에 덧붙여서 최종 N길이만큼의 문자열을 반환

SELECT RPAD('880101-2', 14, '*')
  FROM DUAL;

LTRIM / RTRIM(STR, 제거시키고자 하는 문자)
    : 문자열의 왼쪽 또는 오른쪽에서 제거시키고자 하는 문자들을 찾아서 제거한 나머지 문자열을 반환

SELECT LTRIM(',233425', ',')
  FROM DUAL;

TRIM(BOTH/LEADING/TRAILING '제거시키고자 하는 문자' FROM STR)
    : 문자열의 앞/뒤/양쪽에 있는 특정 문자를 제거
    TRIM(BOTH/LEADING/TRAILING) 생략 갸능, 단 생략 시 기본값이 BOTH

SELECT TRIM('Z' FROM 'ZZZBKZZSJMZZ')
  FROM DUAL;
  
SELECT TRIM(LEADING 'Z' FROM 'ZZZJGJZZKZZ')
  FROM DUAL; -- LEADING : 앞쪽
  
SELECT TRIM(TRAILING 'Z' FROM 'ZZZKFJZZDKZZ')
  FROM DUAL; -- TRAILING : 뒤쪽

- LOWER(STR)
    : 다 소문자로 변경
    - UPPER(STR)
    : 다 대문자로 변경
    - INITCAP(STR)
    : 각 단어 앞글자만 대문자로 변경
    결과값은 CHARACTER 타입으로 반환

SELECT LOWER('HELLO WORLD!')
  FROM DUAL;
  
SELECT UPPER('hello world')
  FROM DUAL;
  
SELECT INITCAP('hello world!')
  FROM DUAL;

 CONCAT(STR1, STR2)
    : 전달된 두 개의 문자열을 하나로 합친 결과를 반환

SELECT CONCAT('123', 'ABC')
  FROM DUAL;

REPLACE(STR, 찾을 문자, 바꿀 문자)
    : STR로부터 찾을 문자를 찾아서 바꿀 문자로 바꾼 문자열을 반환

SELECT REPLACE('서울시 중구 남대문로 120', '120', '125')
  FROM DUAL;

#2. 숫자열

ABS(NUMBER) : 절대값을 구해주는 함수

SELECT ABS(-10)
  FROM DUAL;

 MOD(NUMBER1, NUMVER2) : 두 수를 나눈 나머지값을 반환해주는 함수

SELECT MOD(10, 3)
  FROM DUAL;

ROUND(NUMBER, 위치) : 반올림 처리해주는 함수

SELECT ROUND(123.456)
  FROM DUAL; -- 123
  
SELECT ROUND(123.456, -1)
  FROM DUAL; -- 120

CEIL(NUMBER) : 소수점 아래의 수를 무조건 올림 처리해주는 함수

SELECT CEIL(123.156)
  FROM DUAL; -- 124

 FLOOR(NUMBER) : 소수점 아래의 수를 무조건 버림 처리해주는 함수

SELECT FLOOR(123.999)
  FROM DUAL; -- 123

TRUNC(NUMBER, 위치) : 위치 지정가능한 절삭 처리 함수

SELECT TRUNC(123.585, 2)
  FROM DUAL;

- 날짜 관련 함수

SYSDATE : 현재 시스템 날짜 반환

SELECT SYSDATE
  FROM DUAL;

MONTHS_BETWEEN(DATE1, DATE2) : 두 날짜 사이의 개월 수 반환(NUMBER타입 반환, DATE2가 더 미래일 경우 음수가 나올 수 있다)

SELECT EMP_NAME
       , FLOOR(SYSDATE - HIRE_DATE) || '일' 근무일수
       , FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월' 근무개월수
  FROM EMPLOYEE;

ADD_MONTHS(DATE, NUMBER) : 특정 날짜에 해당 숫자만큼의 개월 수를 더한 날짜를 반환(DATE 타입 반환)

SELECT ADD_MONTHS(SYSDATE, 5)
  FROM DUAL;

NEXT_DAY(DATE, 요일) : 특정 날짜에서 가장 가까운 요일 찾아 그 날짜 반환

SELECT NEXT_DAY(SYSDATE, '일요일')
  FROM DUAL;

LAST_DAY(DATE) : 해당 특정 날짜 달의 마지막 날짜를 구해서 반환(DATE 타입 반환)

SELECT LAST_DAY(SYSDATE)
  FROM DUAL;

-- 언어변경

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

EXTRACT : 년도 또는 월 또는 일 정보를 추출해서 반환(NUMBER타입으로 반환)

    - EXTRACT(YEAR FROM DATE) : 특정 날짜로부터 년도만 추출
    - EXTRACT( MONTH FROM DATE) : 특정 날짜로부터 월만 추출
    - EXTRACT(DAY FROM DATE) : 특정 날짜로부터 일만 추출

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

 

#3. 형변환

< 형변환 함수 >

TO_CHAR(NUMBER/DATE, 포맷) : 숫자형 또는 날짜형 데이터를 문자형 데이터 타입으로 변환(CHARACTER타입 변환)

0으로 지정 : 빈 공간을 0으로 채움

9로 지정 :  빈 공간을 공백으로 채움

맨 앞 L 입력 : 현재 설정된 나라(LOCAL)의 화폐단위가 나옴

, 입력 : 3자리마다 ,로 구분

SELECT TO_CHAR(1234, '00000')
  FROM DUAL; -- 1234 => '01234' : 빈 공간을 0으로 채움
  
SELECT TO_CHAR(1234, '99999')
  FROM DUAL; --1234 => '1234' : 빈 공간을 공백으로 채움
  
SELECT TO_CHAR(1234, 'L00000')
  FROM DUAL; -- 1234 => '￦01234' : 현재 설정된 나라(LOCAL)의 화폐단위가 나옴
  
SELECT TO_CHAR(1234, 'L99999')
  FROM DUAL; -- 1234 =>  '￦1234' : 현재 설정된 나라(LOCAL)의 화폐단위가 나옴
  
SELECT TO_CHAR(1234, 'L99,999')
  FROM DUAL; -- 1234 => '￦1,234' : 3자리마다 ,로 구분

 

DATE(년월일시분초) => CHARACTER

YYYY-MM-DD : 년 - 월 - 일

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
  FROM DUAL;

PM HH:MI:SS : AM/PM은 오전/오후 출력

SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS')
  FROM DUAL;

HH24 : 24시간 형식

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
  FROM DUAL;

MON DY, YYYY : MON은 몇 월 형식, DY는 일 빼는 형식

SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY')  
  FROM DUAL;

YEAR은 영어로 년도수 출력

(** YY는 무조건 앞에 20이 붙어서 2000년대가 되고,

RR은 반올림되어서 월일 두자리가 50이상/ 이하에 따라 나뉜다 => 50이상이면 이전 세기, 50미만이면 현재세기)

SELECT TO_CHAR(SYSDATE, 'YYYY')
      ,TO_CHAR(SYSDATE, 'RRRR')
      ,TO_CHAR(SYSDATE, 'YY')
      ,TO_CHAR(SYSDATE, 'RR')
      ,TO_CHAR(SYSDATE, 'YEAR')
  FROM DUAL;

RM은 로마숫자로 표현

(** MM은 약칭(12월, DEC), MON은 풀네임(12월, DECEMBER))

SELECT TO_CHAR(SYSDATE, 'MM')
      ,TO_CHAR(SYSDATE, 'MON')
      ,TO_CHAR(SYSDATE, 'MONTH')
      ,TO_CHAR(SYSDATE, 'RM')
  FROM DUAL;

D는 1주일 기준 몇 일째(일요일부터), DD는 1일 기준 몇일째(1일부터), DDD는 1년 기준으로 몇일 째 되었는지 계산

SELECT TO_CHAR(SYSDATE, 'D')
      ,TO_CHAR(SYSDATE, 'DD')
      ,TO_CHAR(SYSDATE, 'DDD') -- DDD는 1년이 기준되는 날
  FROM EMPLOYEE;

DY는 요일뺀 거 DAY는 요일을 붙힌 것

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

TO_DATE(NUMBER / CHARACTER, 포맷) : 숫자형 또는 문자형 데이터를 넣으면 날짜형으로 변환(DATE타입 반환)

기본 포맷은 YY/MM/DD로 반환이 된다. ' 따옴표' 를 꼭 붙여주도록 한다.

SELECT TO_DATE(20230101)
 FROM DUAL;
 
SELECT TO_DATE('140630', 'RRMMDD')
  FROM DUAL;

TO_NUMBER(CHARACTER, 포맷) : 문자형 데이터를 숫자형으로 변환(NUMBER타입 반환)

정수이기 때문에 앞에 0이 날라감

SELECT TO_NUMBER('0123')
  FROM DUAL;

NVL(컬럼명, 해당 컬럼값이 NULL일 경우 반환할 결과값)
    컬럼에 값이 존재할 경우 컬럼의 값을 변환, 해당 컬럼의 값이 NULL일 경우 내가 제시한 특정값을 반환

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

NVL2(컬럼명, 결과값1, 결과값2)

     해당 컬럼값이 존재할 경우 결과값 1 반환, 해당 컬럼값이 NULL일 경우 결과값 2 반환

SELECT EMP_NAME, DEPT_CODE, NVL2(DEPT_CODE, '부서배치완료', '부서없음')
  FROM EMPLOYEE;

NULLIF(비교대상1, 비교대상2)
    두 개의 값이 동일할 경우 NULL 반환, 두 개의 값이 동일하지 않을 경우 비교대상1을 반환

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

 

#4. 선택함수

 < 선택 함수 >

DECODE(비교대상(컬럼명/산술연산/함수식), 조건값1, 결과값1, 조건값2, 결과값2, ....., 결과값)

자바에서의 SWITCH문과 유사

SELECT EMP_ID, EMP_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') "성별"
  FROM EMPLOYEE;

 

CASE WHEN THEN 구문

(CASE WHEN 조건식1 THEN 결과값1
         WHEN 조건식2 THEN 결과값2
         .....
         ELSE 결과값
     END    )

자바에서의 IF-ELSE IF 문 같은 느낌

SELECT EMP_ID, EMP_NAME,
       CASE 
            WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남'
            ELSE '여'
        END "성별"
   FROM EMPLOYEE;

 

#5. 그룹함수

< 그룹 함수 >

N개의 값을 읽어서 1개의 결과를 반환(하나의 그룹별로 함수 실행결과 반환

1. SUM(숫자타입컬럼) : 해당 컬럼값들의 총 합계를 반환해주는 함수

SELECT SUM(SALARY)
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5';

2. AVG(숫자타입컬럼) : 해당 컬럼값들의 평균값을 구해서 반환

SELECT ROUND(AVG(SALARY))
  FROM EMPLOYEE;

3. MIN(ANY 타입 컬럼) : 해당 컬럼값들 중 가장 작은값 반환

SELECT MIN(SALARY) "최저급여", MIN(EMP_NAME), MIN(EMAIL), MIN(HIRE_DATE)
  FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;

4. MAX(ANY 타입 컬럼) : 해당 컬럼값들 중 가장 큰 값 반환

SELECT MAX(SALARY), MAX(EMP_NAME), MAX(EMAIL), MAX(HIRE_DATE)
  FROM EMPLOYEE;

5. COUNT(*/컬럼명/DISTINCT 컬럼명) : 조회된 행 개수를 세서 반환

- COUNT(*) : 조회 결과에 해당하는 모든 행 개수를 다 세서 반환

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

- COUNT(컬럼명) : 제시한 해당 컬럼값이 NULL이 아닌것만 행 개수를 세서 반환

SELECT COUNT(DEPT_CODE)
  FROM EMPLOYEE;

- COUNT(DISTINCT 컬럼명) 제시한 해당 컬럼값이 중복값이 있을 경우 하나로만 세서 반환

SELECT COUNT(DISTINCT DEPT_CODE)
  FROM EMPLOYEE;

 

20-2. GROUP BY ... HAVING

< GROUP BY 절 >

그룹을 묶어줄 기준을 제시할 수 있는 구문

** GROUP BY 안에 있어야 SELECT로 조회해볼 수 있다.

SELECT DEPT_CODE, COUNT(*)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE;

 < HAVING 절 >

 그룹에 대한 조건을 제시하고자 할 때 사용되는 구문

SELECT DEPT_CODE, ROUND(AVG(SALARY))
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING AVG(SALARY) >= 3000000;

 

 

   < 실행 순서 >
    5 : SELECT * / 조회하고자 하는 컬럼명 / 산술연산식 / 함수식 AS "별칭"
    1 : FROM 조회하고자 하는 테이블 명
    2 : WHERE 조건식
    3 : GROUP BY 그룹 기준에 해당하는 컬럼명 / 함수식
    4 : HAVING 그룹함수식에 대한 조건식
    6 : ORDER BY 정렬 기준에 해당하는 컬럼명 / 별칭 / 컬럼 순번 ASC/DESC NULLS FIRST / NULLS LAST

 

 

20-3. 집합 연산자

< 집합 연산자 SET OPERATOR >

굳이 잘 안쓴다고 함...

1. UNION(합집합 - 두 쿼리문 수행한 결과값을 더해서 중복되는 결과는 한 번만 조회)

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
 UNION
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE SALARY >= 3000000;

2. UNION ALL : 여러 개의 쿼리 결과를 무조건 더하는 연산자(중복값이 여러 개 들어갈 수 있음)

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
 UNION ALL
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE SALARY >= 3000000;

3. INTERSECT (교집합 - 여러 쿼리 결과의 중복된 결과만을 조회)

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
 INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE SALARY >= 3000000;

4. MINUS(차집합 - 선행 쿼리 결과에 후행 쿼리 결과를 뺀 나머지)

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
 MINUS
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
 WHERE SALARY > 3000000;