교육 19일차

권재현·2021년 4월 11일

교육

목록 보기

13/49

1.DUAL

임시 VIEW : VIEW인척하지만 데이터가 없다.

SELECT 1+2*3
FROM DUAL;
--출력 : 7

2.NVL(NULL VALUE LESS) 과 AS

NULL 은 공간만 있고 ,안에 값은 없는 것.
- NULL + a => NULL

--각 직원들의 실 지급액을 출력하시오.
--실 지급액 : 급여+커미션
--출력 : 이름, 급여, 커미션, 실 지급액
--NULL+A는 NULL
SELECT ENAME, SAL,COMM,SAL+COMM
FROM EMP;

--NVL(값1,값2) : 값1이 NULL이면 값2로 교체 <오라클>
--IFNULL(값1,값2) <MYSQL>
--ISNULL(값1,값2) <MSSQL>
--AS+명칭 : SELECT에서만 사용가능
SELECT ENAME, SAL, COMM, SAL + NVL(COMM,0) AS REAL
FROM EMP
--결과 REAL컬럼에서 COMM이 NULL 이면 0처리라서 그대로 SAL 값이 고대로 나온다.
--REAL SAL로 작성하면 띄어쓰기 때문에 다른 문법으로 인식 -> " "로 묶는다.
--' '는 문자열 " "는 문법
SELECT ENAME, SAL,COMM,SAL+NVL(COMM,0) AS "REAL SAL"
FROM EMP;

3.문자열 처리

개수기반 1부터 시작한다. 자바랑 다르게

a.더하기

--문자열 더하기
SELECT 'HELLO' || 'WORLD!!' || '!!'
FROM DUAL
;
--출력 : HELLOWORLD!!

--CONCAT 문자열 더하기 필요는 없음
SELECT CONCAT ('HELLO', 'WORLD!!')
FROM DUAL
;
--출력 : HELLOWORLD!!

자바 ||는 OR을 표시하지만 SQL에서는 더하기 다.

b.자르기

--자르기
SELECT SUBSTR('HELLO WORLD!!', 4)
FROM DUAL
;
--출력 : LO WORLD!!
SELECT SUBSTR('HELLO WORLD!!', 8, 5)
FROM DUAL
;
--출력 : ORLD!

c.길이

--길이
SELECT LENGTH('KAY')
FROM DUAL
;
--출력 : 3

d.변경

--변경
SELECT REPLACE ('HELLO WORLD!!', 'L', 'K')
FROM DUAL
;
--출력: HEKKO WORKD!!

SELECT REPLACE('KKKKI', 'K', 'J')
FROM DUAL;
--출력: JJJJI

글자 순번 찾기

SELECT INSTR('KWON', 'N')
FROM DUAL;
-- 출력 : 4

--나올 수 없는 숫자는 0
SELECT INSTR('HELLO WORLD!!', 'K')
FROM DUAL;
--해당글자 없으면 0으로 출력

-- 몇번 째 글자인지찾기
SELECT INSTR ('HELLO WORLD!!', 'L',4)
FROM DUAL
;
--출력 : 4, 숫자번째부터 찾는다.

SELECT INSTR ('HELLO WORLD!!', 'L',4, 2)
FROM DUAL
;
--출력 : 10,4번 글자 부터 확인하고, 똑같은 글자가 있다면 2번째 나오는 것 을 출력
-- 2번째 나오는 게 없으면 그대로 0 출력

숫자로 변환

--숫자로 변환 
SELECT TO_NUMBER('123') +4
FROM DUAL
;
--출력 : 127

SELECT '123' + 4
FROM DUAL
;
--출력 : 127

대 소문자

--대소문자
SELECT LOWER ('HELLO'), UPPER('world')
FROM DUAL
;
--출력: hello WORLD

날짜 처리

--문자열을 날짜로 변환
SELECT TO_DATE('2021-04-09')
FROM DUAL
;
--출력 : 값으로 보면 달력 날짜로 표시

SELECT '2021-04-09'
FROM DUAL
;
--출력 : 그냥 값으로 표시

--현재시간
SELECT SYSDATE
FROM DUAL
;

5.문자열 형태변화 TO_CHAR(값, 형태)

--문자열 형태변화 TO_CHAR(값, 형태) 
--Y , YY, YYYY: 연도
--MM : 월
--MON : 월
--DD : 일
--DAY : 요일
--D : 몇번 째 날
--유의사항 TO-CHAR 사용 시 형태에 한글 사용 안됨
--사용가능한 것: -(하이픈), :(콜론), .(점), ,(컴마) 사용 가능
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD-DAY D')
FROM DUAL
;
--출력: 2021-04-09, 금요일, 1

--AM,PM : 오전 ,오후
--HH : 시간 (12시간 기준)
--HH24: 시간 (24시간 기준)
--MI : 분
--SS : 초
SELECT TO_CHAR(SYSDATE, 'AM HH: MI :SS')
FROM DUAL
;
--출력: 오후 04:25: 15

--몇 주차
--W : 해당 월의 1일 기준 몇주차인지
--WW : 1월 1일 기준 몇주차 인지
-- IW: 첫째 월요일 기준 몇주차 인지
SELECT TO_CHAR(SYSDATE, 'W'),TO_CHAR(SYSDATE, 'WW'),TO_CHAR(SYSDATE, 'IW')
FROM DUAL
;
--출력: w: 2 ww:15 Iw :14

--Q : 분기
SELECT TO_CHAR(SYSDATE, 'Q')
FROM DUAL
;
--출력: 2

--날짜 + -숫자 : 해당일자 만큼 증감
--숫자 기준으로 날짜 빼줌
SELECT SYSDATE -30
FROM DUAL
;
--출력 : 4월9일 기준으로 21-03-10 

--TO_CHAR에서 형태에 9가 오는 경우 해당 위치에 숫자를 넣는다는 의미
--  소수점을 표시할 경우 아래 자리수는 자동 반올림
-- L : 해당 오라클 설치 장치의 언어 셋팅에 따른 통화기호제공 

SELECT TO_CHAR(123456, '999999')
FROM DUAL;
--출력 : 123456, 9가 자리수 보다 적으면 #으로 나옴

SELECT TO_CHAR(123456.789, 'L999,999,999.99')
FROM DUAL
;
--출력: ￦123,456.79

6. 값 처리

--DBMS_RANDOM.VALUE(값1, 값2):
--값1부터 값2까지 사이의 난수를 구한다.
--DBMS_RANDOM.STRING(형태, 개수) : 
--형태로 지정된 규칙대로 개수만큼 불특정 문자열을 만든다. 비밀번호 설정 시 사용
--U : 대문자, L : 소문자, A : 대소문자, 
--X : 대문자숫자, P : 출력 가능한 모든 경우의 수
SELECT DBMS_RANDOM.STRING('X', 10), DBMS_RANDOM.VALUE(1, 10)
FROM DUAL;

7.CASE

if문 과 동일하게 사용

--급여가 3000이상인 사원들의 급여를 10% 인상하고, 나머지는 그대로 출력하시오.
--출력 : 이름, 급여, 변경급여
--CASE WHEN 조건1 THEN 값1  ELSE 값 N : 
--조건 1이 TRUE면 값1을 돌려줌. TRUE가 없으면 값N을 돌려줌
--CASE WHEN THEN END  필수, ELSE 선택

SELECT ENAME, SAL,
    CASE WHEN SAL >= 3000
        THEN SAL * 1.1
        ELSE SAL
        END AS REAL
FROM EMP;
--출력 : 3000이상 사원들 급여 10&인상
-- 미만인 사람들은 인상없이  원래급여 나옴

--업무가 점원인 사원들의 급여를 10%인상하고, 
--업무가 세일즈맨인 사원들의 급여 50% 인하 나머지는 그대로 출력하시오.
SELECT ENAME, SAL, JOB,
            CASE WHEN JOB = 'CLERK'
                    THEN SAL *1.1
                 WHEN JOB = 'SALESMAN'
                THEN SAL * 0.5
                    ELSE SAL
            END AS RSAL
FROM EMP;

--출력 : 업무가 점원인 사람들만 10% 씩 올랐다.

8.DECODE

switch문 과 동일
값이 값1과 같으면 값 1-1을 돌려준다.
같은 것이 없으면 값 N 을 돌려준다.

--DECODE(값, 값1, 값1-1, ..., 값N)
--값이 값1과 같으면 값1-1을 돌려준다.  값, 값1, 값1-1필수
--같은 것이 없으면 값 N을 돌려준다.
SELECT ENAME, JOB, SAL,
            DECODE(JOB, 'CLERK', SAL * 1.1, SAL) AS RSAL
FROM EMP;

예제

--홀수월에 입사한 사원들의 급여를 50%인상하고, 
--짝수월에 입사한 사원들의 급여를 50%감봉하여 사원 정보를 출력하시오.
--출력 : 이름, 입사월, 기존급여, 변경급여
--case 문법
SELECT ENAME, TO_CHAR(HIREDATE,'MM'),SAL,
		CASE WHEN MOD(TO_CHAR(HIREDATE,'MM'),2) =1
			THEN SAL*1.5
			ELSE SAL*0.5
		END AS RSAL
FROM EMP;

--DECODE 문법
SELECT ENAME, TO_CHAR(HIREDATE,'MM'),SAL,
	DECODE(MOD(TO_CHAR(HIREDATE,'MM'),2) 1, SAL*1.5, SAL) AS RSAL
FROM EMP;

9.서브쿼리(sub Query)

쿼리안의 쿼리, 조회결과를 재가공하는 것,
넣을 위치에 (쿼리)를 넣어주면 해당위치에 쿼리결과를 넣어준다.

서브쿼리 위치별 조건,

A. select - 실행결과 : 단일 column,단일 row

B. from - 실행결과 : Anything

C. where - 실행결과 : 기본적으로 단일 column,단일 row

단, in으로 사용할 경우 단일 column,복수 row가능

간단하게 생각해서 VIEW를 재가공하는 것

--점원들의 급여를 50%인상하고 나머지 급여는
--그대로 적용하여 변경급여가 1500이하인 사원들을 구하시오.
--불가능
SELECT ENAME, JOB, SAL,
    DECODE(JOB, 'CLERK', SAL*1.5, SAL) AS REAL
FROM EMP
;
--서브쿼리 사용, 서브쿼리는 효율성을 위해 FROM에서 쓰고 정 안될경우, 
--WHERE에서 IN 사용해주기
SELECT ENAME, JOB, SAL, RSAL
FROM (SELECT ENAME, JOB, SAL, DECODE(JOB, 'CLERK', SAL * 1.5, SAL) AS RSAL
            FROM EMP)
WHERE RSAL<=1500;
--응용
SELECT ENAME, JOB, SAL, RSAL
FROM(SELECT ENAME, JOB, SAL,
    DECODE(JOB, 'CLERK', SAL*1.5, SAL) AS RSAL
FROM EMP)
WHERE RSAL<3000;