오라클-(2)

2023.07.25

where 절 : 정확하고 다양하게 결과를 출력하기 위한 조건절(from절 다음에 사용)

 

조건 : 부서번호가 30인 데이터만 출력

조건 --> where (위치 : from 다음에 코딩)

 

SELECT * FROM emp WHERE deptno = 30; -- = : 대입연산자가 아닌 비교연산자)

 

※기본 구조
         컬럼명
          from 테이블
         where 조건식(선택) -- select에서 where절은 선택이지만 update나 delete에서 where절은 필수
          order by (선택)

※AND,OR

AND : 조건식의 조건이 모두 참인경우 참
OR : 조건식의 조건이 하나라도 참인경우 참 

ex) 부서 번호가 30이면서 직업이 SALESMAN 인 데이터
SELECT * FROM emp WHERE deptno = 30 AND job = 'SALESMAN';

ex)사원 번호가 7499이고, 부서번호가 30인 사원 정보를 출력
SELECT * FROm emp WHERE EMPNO = 7499 AND DEPTNO = 30;

ex)부서 번호가 30이거나, 직업이 'CLERK'인 사원 정보 출력
SELECT * FROM emp WHERE DEPTNO = 30 OR JOB = 'CLERK';

ex) 부서번호가 30이거나, 직업이 'CLERK'인 사원 정보를 부서별로 오름차순, 내림차순 정렬 출력
SELECT * FROM emp WHERE DEPTNO = 30 OR JOB = 'CLERK' order by DEPTNO ASC;
SELECT * FROM emp WHERE DEPTNO = 30 OR JOB = 'CLERK' order by DEPTNO DESC;

연산자

1.산술연산자 : +,-,*,/
2.비교연산자 : >,<,>=, <=
     1. 숫자 데이터 비교
     2. 문자열 비교
3.등가비교연산자 : =,!=,<>,^=

1)산술연산자
SELECT * FROM emp WHERE sal*12 = 36000; 

2)비교연산자 : 숫자 데이터 비교

SELECT * FROM emp WHERE sal>=3000;

ex)급여가 2500 이상이고 직업이 'ANALYST'인 사원정보출력

SELECT * FROM emp WHERE sal >=2500 AND JOB = 'ANALYST';

3)비교연산자 : 문자열 비교(비교 문자열이 하나인 경우)

SELECT * FROM emp WHERE ename >= 'F'; -- 첫문자가 F이거나 그 뒤인경우

※비교문자열이 여러개인 경우

SELECT * FROM emp WHERE ename <= 'FORZ'; -- FORZ를 포함한 문자열보다 사전순으로 앞에 있는 데이터를 출력

4)등가비교연산자

SELECT * FROM emp WHERE sal != 3000;
SELECT * FROM emp WHERE sal <>= 3000;
SELECT * FROM emp WHERE sal ^= 3000;

5)논리부정 연산자

NOT 연산자와 IN,BETWEEN,IS NULL 연산자와 복합적으로 사용
SELECT * FROM emp WHERE NOT sal = 3000;

IN연산자 : OR연산자를 대신해 사용 -> IN연산자를 통해 특정열에 대한 조건을 여러개 지정
OR연산자 : where조건과 비교연산자, 등가비교연산자를 사용
SELECT * FROM emp 
WHERE job = 'MANAGER' OR job = 'SALESMAN' OR job = 'CLERK'; -- 식이 길어질 우려가 있음

※ IN연산자 기본 코드
select 컬럼명
from 테이블
where 컬럼명 IN(데이터1, 데이터 2, ... 데이터n);

※OR ---> IN연산자
SELECT * FROM emp WHERE job IN('MANAGER','SALESMAN','CLERK'); -- IN연산자를 이용한 연산

※AND연산자 : where 조건과 비교연산자, 등가비교연산자를 사용
SELECT *FROM emp
WHERE job != 'MANAGER' AND job <> 'SALESMAN' AND job ^= 'CLERK';

※AND ---> IN연산자
SELECT *FROM emp
WHERE job NOT IN('MANAGER','SALESMAN','CLERK');

※BETWEEN AND
대소비교연산자와 AND 연산자
SELECT * FROM emp 
WHERE sal >=2000 AND sal <=3000;

※BETWEEN 기본코드
    select 컬럼명
    from 테이블
    where 컬럼명 between 최소값 AND 최대값;

SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;

 

ex)급여 컬럼이 2000~3000 사이 이외의 값을 가진 데이터 출력
SELECT * FROM emp WHERE sal NOT BETWEEN 2000 AND 3000;

※LIKE 연산자/ 와일드카드(_, %) : 일부 문자열이 포함된 데이터를 검색할 때 사용
1.와일드카드 _ : 어떤 값이든 상관없이 한개의 문자 데이터를 의미
2.와일드카드 % : 길이와 상관없이(문자가 없는 경우도 포함)모든 문자 데이터를 의미

ex)사원이름이 대문자 S로 시작하는 데이터 출력
SELECT * FROM emp WHERE ename LIKE 'S%';

ex)사원이름의 두번째 글자가 L인 사원 출력
SELECT * FROM emp WHERE ename LIKE '_L%';

ex)이름에 'LA'가 포함된 사원의 데이터를 출력
SELECT * FROM emp WHERE ename LIKE '%LA%';

ex)와일드카드 문자가 데이터 일부에 포함이 되어 있는 경우 ('A_A'검색)
SELECT * FROM SOME_TABLE WHERE SOME_COLUMN LIKE 'A\_A%' ESCAPE '\';

※NULL은 어떤값인지 모르기 때문에 결과를 찾아 올 수 없다.(비교할 값 자체가 존재x) 
SELECT * FROM emp WHERE comm = NULL;

※IS NULL : 컬럼의 값이 존재하지 않는 데이터만 출력
SELECT * FROM emp WHERE comm IS NULL; --(comm이 null인 데이터만 출력)

※ NULL이 아닌 데이터만 출력
SELECT * FROM emp WHERE comm IS NOT NULL;

※IS NULL 연산자 : 특정 컬럼 또는 결과 값이 NULL인지의 여부를 확인
-- IS NULL , IS NOT NULL  : 데이터가 NULL인지 아닌지의 확인 용도로 주로 사용

ex) sal값이 null이 아니거나  comm이 null값인 경우 모두 출력
SELECT * FROM emp
WHERE sal > NULL OR COMM IS NULL;

※집합연산자 : 조회한 결과를 하나의 집합처럼 다룰 수 있다. 두 개 이상의 select 문의 결과 값을 하나로 연결할 때도 사용
종류
    1.UNION : 연결된 select 문의 결과 값을 합집합으로 묶어준다. 결과값은 중복제거해준다.
    2.UNION ALL : 연결된 select 문의 결과 값을 합집합으로 묶어준다. 결과값은 중복제거를 하지 않는다.
    3.MINUS : 먼저 작성한 select 문의 결과 값에서 다음 select 문의 결과값을 차집합 처리.
    4.INTERSECT : 교집합


집합연산자 :

1. UNION
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10
UNION 
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 20;

UNION : 컬럼의 개수가 다르면 오류가 난다.
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10
UNION
SELECT empno, ename, sal FROM emp WHERE deptno = 20; 

UNION : 컬럼의 자료형이 다를 경우 오류가 난다.
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10
UNION
SELECT ename, empno, sal, deptno FROM emp WHERE deptno = 20;

UNION : 컬럼의 이름이 다르고, 컬럼의 갯수와 자료형이 같을 경우 -> 출력은 되지만 첫번째 select 기준으로 출력
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10
UNION
SELECT sal, job, deptno, sal FROM emp WHERE deptno = 20;

2.UNION ALL(출력결과가 같은 경우 : 중복데이터 존재)
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10
UNION ALL
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10;

3.MINUS (차집합)
SELECT empno, ename, sal, deptno FROM emp
MINUS
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10;

4.INTERSECT(교집합)
SELECT empno, ename, sal, deptno FROM emp
INTERSECT
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10;
    
연산자 우선순위
    산술연산자(*,/) > (+,-) > 비교연산자(대소비교 > (IS NULL, LIKE, IN) >  BETWEEN연산자 > NOT > AND > OR


함수
-내장 함수 : 오라클에서 기본적으로 제공되는 함수
    1.단일행 함수 : 데이터가 한 행씩 입력되고 입력된 한 행마다 결과가 출력
    2.다중행 함수 : 여러 행이 입력되고 하나의 행으로 결과가 나오는 함수
    
-사용자 정의 함수 : 사용자 필요에 의해 작성되는 함수

-문자를 가공하는 문자함수
    1.대소문자를 바꿔주는 함수 : upper(대문자로 변환), lower(소문자로 변환), initcap(첫번째는 대문자로 나머지는 소문자로 변환)
    2.문자열 길이를 나타내는 함수 : length
    3.문자열 일부를 추출하는 함수 : substr(문자열 데이터, 시작위치, (추출길이))
     --> 추출길이는 생략가능하며 생략시 끝가지 가져온다.
    4.특정문자의 위치를 찾기 : instr
        기본 구조
        INSTR([대상 문자열 데이터(필수)],
        [위치를 찾으려는 부분 문자(필수)],
        [위치를 찾기 시작할 대상 문자열데이터 위치(선택),]
        [시작위치에서 찾으려는 문자가 몇번째 문자인지 지정(선택)])
    5.특정문자를 다른 문자 치환 : replace
        기본 구조
        REPLACE([문자열 데이터 또는 컬럼명],[찾는문자(필수)],[대체문자(선택)])
        
    6.데이터의 빈 공간을 특정 문자로 채운다 : LPAD(Left Padding), RPAD(Right Padding)
        기본 구조
        LPAD([문자열데이터 또는 컬럼명(필수)],[데이터자릿수(필수)],[빈공간 채울 문자(선택)])
        RPAD([문자열데이터 또는 컬럼명(필수)],[데이터자릿수(필수)],[빈공간 채울 문자(선택)])  
    7. 두 문자열을 합치는 함수 : concat
    8. 두 문자열을 연결하는 연산자 : ||
    9. 특정 문자를 지우는 함수 : trim,ltrim,rtrim
        기본 구조
        trim([삭제옵션(선택)],[삭제할 문자(선택)] from [원본 문자열(필수)])
        -삭제옵션 : LEADING(왼쪽삭제), TRAILING(오른쪽 삭제), BOTH(양쪽삭제)
        
        ltrim([원본문자열(필수)], [삭제할 문자집합(선택)]) : 원본문자의 왼쪽에서 삭제할 문자열 지정
        rtrim([원본문자열(필수)], [삭제할 문자집합(선택)]) : 원본문자의 오른쪽에서 삭제할 문자열 지정
        
SELECT ename, upper(ename), lower(ename),initcap(ename) FROM emp;

ex)사원 이름에 smith 단어를 포함한 데이터 출력
SELECT * from emp WHERE upper(ename) LIKE upper('%smith%');

ex)문자열 길이 : length, lengthb
SELECT ename, length(ename) FROM emp;

ex)사원이름의 길이가 5이상인 사원 출력
SELECT ename, length(ename) FROM emp WHERE length(ename)>=5;

ex)문자열 길이 : lengthb(바이트 길이 영문은 한글자에 1바이트 이외에는 2바이트)
SELECT length('한글'), lengthb('한글') FROM dual;

dual 테이블
- dummy 테이블
- 연산이나 함수의 단일 결과를 확인할 때 사용하는 테이블


ex)문자열 일부를 추출 : substr
SELECT job, substr(job,1,2), substr(job,3,2),substr(job,5)  FROM emp;

ex)실제 사용 예 : substr과 length 를 함께 사용
-- -length()는 오른쪽 끝에서부터 왼쪽으로 인덱스 부여 
-- 오른쪽 끝에서부터 -1,-2, -3, -4, -5,,, 방식으로 인덱스 부여
-- 기본 상태는 왼쪽부터 1,2, 3,4 ,5방식으로 인덱스 부여
SELECT job, substr(job,-length(job)) FROM emp; -- ->  인덱스부터 끝까지 출력
SELECT job, substr(job,length(job)) FROM emp; -- -> -length(job) 인덱스부터 끝까지 출력
SELECT job, substr(job,-5,2) FROM emp; -- -> -5번째 인덱스부터 2글자 출력

ex)특정 문자의 위치를 찾기 : instr 
SELECT instr('HELLO, ORACLE!','L')AS INSTR_1,  -- L의 위치는 3번째
    instr('HELLO, ORACLE!','L',5) AS INSTR_2, -- 5번째글자 이후에 나오는 L의 위치는 12번째
    INSTR('HELLO, ORACLE!', 'L',2,2) AS INSTR_3, -- 2번째글자 이후에 나오는 L의 2번째 위치는 4
    INSTR('HELLo, ORACLE!', 'L',2,3) AS INSTR_4  -- 2번째글자 이후에 나오는 L의 3번째 위치는 12
    FROM dual;
    
ex)실제 사용예 : Instr과 LIKE, WHERE절 함께 사용
-- instr 함수로 사원이름에 s가 있는 데이터 출력
SELECT * FROM emp WHERE instr(ename,'S') != 0;
SELECT * FROM emp WHERE instr(ename,'S') >0;

ex) LIKE 연산자로 사원이름에 S가 있는 데이터 출력
SELECT * FROM emp WHERE ename LIKE '%S%';

ex) 특정 문자를 다른 문자 치환 : replace

SELECT '010-1111-1111' AS re_before, 
replace ('010-1111-1111', '-', ' ') as re_1, --치환문자를 공백으로 지정
replace ('010-1111-1111', '-') as re_2 -- 치환문자를 지정하지 않을시 붙어서 나옴
from dual;

ex)데이터의 빈공간을 채우는 함수(LPAD, RPAD)
SELECT 'ORACLE', LPAD('ORACLE',10,'#')as LPAD_1, -- 전체 10자리를 만들고 왼쪽의 빈공간을 '#'으로 채움
    RPAD('ORACLE',10,'*') as RPAD_1, -- 전체 10자리를 만들고 오른쪽의 빈공간을 '#'으로 채움
    LPAD('ORACLE',10) as LPAD_2, -- 전체 10자리를 만들고 왼쪽의 빈공간을 공백으로 채움
    RPAD('ORACLE',10) as RPAD_2 -- 전체 10자리를 만들고 오른쪽의 빈공간을 공백으로 채움
    FROM dual;

ex)실제 사용 예 : RPAD로 주민번호 가리기
SELECT RPAD('970101-',14,'*') as RPAD_1,
    RPAD('010-1234-',13,'*') as RPAD_2
    FROM dual;
    
ex)두 문자열을 합치는 함수 : concat
SELECT concat(empno, ename), 
    concat(empno, concat(' : ', ename))
    FROM emp WHERE ename = 'SMITH';
    
ex) 두 문자열을 연결하는 연산자 : ||
SELECT empno || ename,
    empno || ' : ' || ename
    FROM  emp
    WHERE ename = 'SMITH';

ex)특정 문자를 지우는 함수 : trim, ltrim, rtrim
-- trim(삭제할 문자가 없을 경우)
SELECT '[' || trim(' _ _Oracle_ _ ') || ']' as trim_trim, -- 양옆 공백 하나씩 제거
       '[' || trim(LEADING FROM ' _ _Oracle_ _ ') || ']' as trim_leading, -- 왼쪽 공백하나제거
       '[' || trim(TRAILING FROM ' _ _Oracle_ _ ') || ']' as trim_trailing, --오른쪽 공백하나제거
       '[' || trim(BOTH FROM ' _ _Oracle_ _ ') || ']' as trim_both -- 양옆 공백 하나씩제거
    FROM dual;

SELECT '[' || trim('_' FROM'_ _Oracle_ _') || ']' as trim_trim, --(양쪽의 '-'문자 하나씩 제거)
       '[' || trim(LEADING '_' FROM '_ _Oracle_ _') || ']' as trim_leading, --(왼쪽의 '-'문자 하나제거)
       '[' || trim(TRAILING '_' FROM '_ _Oracle_ _') || ']' as trim_trailing, --(오른쪽의 '-'문자 하나제거)
       '[' || trim(BOTH '_' FROM '_ _Oracle_ _') || ']' as trim_both --(양쪽의 '_'문자 하나씩 제거)
    FROM dual;

-- ltrim, rtrim
SELECT '[' || trim('  --Oracle--  ') || ']' as trim_trim,
       '[' || ltrim('  --Oracle--  ') || ']' as trim_l,
       '[' || ltrim('<--Oracle-->', '-<') || ']' as trim_l2, --(-, <)를 조합해서 모든 경우를 삭제해준다.
       '[' || rtrim('  --Oracle--  ') || ']' as trim_r,
       '[' || rtrim('<--Oracle-->', '>-') || ']' as trim_r2 -- (>, -)를 조합해서 모든 경우를 삭제해준다.
       FROM dual;
       
숫자 함수
- 데이터를 연산하고 수치 조정
    1.특정위치에서 반올림 : round
    2.버림함수 : trunc
    3.지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환: ceil
    4.지정딘 숫자보다 작은 정수 중 가장 큰 정수를 반환: floor
    5.나머지함수 : mod
    
1. 특정위치에서 반올림 : round
SELECT round(1234.5678) as r_1,
round(1234.5678,0) as r_2,
round(1234.5678,1)as r_3, -- 소수점 첫째자리에서 반올림
round(1234.5678,2) as r_4, -- 소수점 둘째자리에서 반올림
round(1234.5678,-1) as r_5, --  -기호는 정수부분을 의미( 1의자리에서 반올림)
round(1234.5678,-2) as r_6 -- 10의자리에서 반올림
FROM dual;

2. 특정위치에서 버림 : trunc
SELECT trunc(1234.5678) as r_1,
trunc(1234.5678,0) as r_2,
trunc(1234.5678,1)as r_3, -- 소수점 첫째자리에서 버림
trunc(1234.5678,2) as r_4, -- 소수점 둘째자리에서 버림
trunc(1234.5678,-1) as r_5, --  -기호는 정수부분을 의미( 1의자리에서 버림)
trunc(1234.5678,-2) as r_6 -- 10의자리에서 버림
FROM dual;

3.ceil : 큰 정수중에서 가장 작은수
4.floor : 작은 정수중에서 가장 큰 수
SELECT ceil(3.14),
    floor(3.14),
    ceil(-3.14),
    floor(-3.14)
    FROM dual;

5.나머지 함수 : mod
SELECT mod(15,6),
    mod(10,2)
    FROM dual;

날짜 함수
날짜 데이터를 다루는 함수
    1.시스템 날짜 출력 : sysdate
    2.몇 개월 이후 출력 : add_months
    3.두 날짜간의 개월 수 차이 : months_between
    4.돌아오는 요일 : next_day
    5.달의 마지막 날짜 : last_day
    6.날짜의 반올림, 버림 : round, trunc


1. 시스템 날짜 출력 :sysdate
SELECT sysdate as now, sysdate - 1 as yesterday, sysdate + 1 as tomorrow FROM dual;

2. 몇 개월 이후 출력 : add_months
SELECT sysdate, add_months(sysdate,3) from dual;

ex)실제 사용 :add_months

SELECT empno,ename, hiredate, sysdate FROM emp
WHERE add_months(hiredate,500) > sysdate; -- scott 데이터가 만들어진 시기를 기준으로 검색

3. 두 날짜간의 개월 수 차이 : months_between
SELECT empno,ename, hiredate,sysdate,months_between(hiredate,sysdate) as m1,
months_between(sysdate,hiredate) as m2,
trunc(months_between(sysdate,hiredate)) as m3
FROM emp;

4.돌아오는 요일 : next_day
5.달의 마지막 날짜 : last_day
SELECT sysdate, next_day(sysdate, '월요일'), last_day(sysdate) FROM dual;

6.날짜의 반올림, 버림 : round, trunc
SELECT sysdate,
round(sysdate, 'CC') as f_cc, -- 2050년을 기준으로 년도 이하이면 2001년으로 처리
round(sysdate, 'YYYY') as f_yyyy, -- 모든년도를 7월1일을 기준으로 7월1일 이상이면 1년을 더해줌
round(sysdate, 'Q') as f_q, -- 각 분기의 두번째 달의 16일 기준 
round(sysdate, 'DDD') as f_ddd, -- 해당일의 정오를 기준
round(sysdate, 'HH') as f_hh -- 해당일의 시간을 기준으로 반올림
FROM dual;

SELECT sysdate,
trunc(sysdate, 'CC') as f_cc,
trunc(sysdate, 'YYYY') as f_yyyy, 
trunc(sysdate, 'Q') as f_q,
trunc(sysdate, 'DDD') as f_ddd, 
trunc(sysdate, 'HH') as f_hh 
FROM dual;

형변환 함수
자료형 변환 함수
    1.숫자또는 날짜 -> 문자 : to_char
        CC : 세기
        YY : 2자리 년도
        YYYY : 4자리 년도
        MM : 월
        MON : 언어별 월이름
        DD : 날짜
        DDD : 1년중 며칠 
        DY : 언어별 요일이름 약자
        DAY : 언어별 요일이름 전체
        W : 1년 중 몇째 주
        HH2 : 24시간 표현
        HH, HH12 : 12시간 표현
        MI : 분
        SS : 초
        AM, PM, A.M, P.M : 오전, 오후
        9 : 숫자 
        0 : 빈자리 0으로 채움
        $ : 달러 표시 붙여서 출력
        L : 지역 화폐기호
        . : 소수점
        , : 천단위 기호
    2.문자 -> 숫자 : to_number
    3.문자 -> 날짜 : to_date


1.숫자 또는 날짜 -> 문자 : to_char
SELECT to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') FROM dual;
SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM dual; 

2.문자 -> 숫자 : to_number
SELECT 1300 - '1500', '1300'+'1500' FROM dual; -- 묵시적형변환(자동으로 형변환)
SELECT to_number('1,300', '999,999') - to_number('1,500','999,999') FROM dual; --포맷지정을 해줘야 계산가능

3.문자->날짜 : to_date
SELECT to_date('2023-07-25','YYYY-MM-DD')as date_1,
       to_date('20230725', 'YYYY-MM-DD')as date_2,
       to_date('2023/07/25','YYYY-MM-DD')as date_3,
       to_date('2023-07-25','YY-MM-DD')as date_4
       FROM dual;


NULL 처리 함수
    1.NVL(a, b) : a의 값이 NULL이면 b로 대체
    2.NVL2(a, b, c) : a의 값이 NULL이면 아니면 b로 출력, NULL이면 c로 출력
    3.NULLIF(a,b) : a와 b가 같으면 NULL출력, 다르면 a 값 출력 


ex)  NVL(a,b) : a의 값이 NULL이면 b로 대체
SELECT empno, ename, sal, comm, sal+comm, nvl(comm,0), sal+ nvl(comm,0) FROM emp; --comm의 null값을 0으로 대체

ex) NVL2(a,b,c) :  a의 값이 NULL이면 아니면 b로 출력, NULL이면 c로 출력
SELECT empno, ename, sal, comm,nvl2(comm,'o','x'), nvl2(comm,sal*12 + comm,sal*12) as annsal FROM emp;