dual
-
오라클의 기본적인 테이블로써 일반유저가 사용가능한 테이블
-
산술 연산이나 가상 칼럼 등의 값을 한번만 출력하고 싶을 때 많이 사용하는 아주 유용한 테이블로서 DUMMY라는 한개의 칼럼으로 구성되어 있다.
형식) desc dual;
select * from dual;
기존의 테이블을 이용=>복잡(기존의 데이터와 섞어서 나옴) 오늘 날짜를 구할때도 사용=>sysdate
SQL> desc dual
이름 널? 유형
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
<문1> select 24*23*12 from dual;
select sysdate from dual;
SQL> select 24*23*12 from dual;
24*23*12
----------
6624SQL> select sysdate from dual;
SYSDATE
--------
20/07/08
함수를 이용 => 계산, 검색
형식) select 함수명(처리해야할 값,,,),함수명2(~),,,
from 테이블명(dual,검색대상테이블,,,)
where 조건식=>검색하는 방법
<문2>직원 14명중에서 사원의 이름의 길이가 5개이상인 직원을 검색해서 그 사원의 사번,이름,업무순으로 출력
문자길이->length(필드명)=>필드에 적용(레코드 갯수만큼)
length("aabbdd")
SQL> select empno,ename,job
from emp
where length(ename)>=5; //where 함수를 이용 조건체크
SQL> select empno, ename,job from emp where length(ename)>=5;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7934 MILLER CLERK
11 개의 행이 선택되었습니다.
<문3>사용자로부터 이름을 입력=>allern,scott
SQL> select ename, sal, comm from emp where ename=upper('&ename'); => 대문자로 변환해서 찾기
where lower(ename)='&ename'; => 소문자로 변환해서 찾기
SQL> select ename, sal, comm from emp where ename=upper('&ename');
ename의 값을 입력하십시오: scott
구 1: select ename, sal, comm from emp where ename=upper('&ename')
신 1: select ename, sal, comm from emp where ename=upper('scott')
ENAME SAL COMM
---------- ---------- ----------
SCOTT 3000
<문4> 문자열 취급함수->substr(적용문자열,시작위치(1부터),뽑아낼갯수)
형식) select substr('abcde',1,2) from dual;
SQL> select substr('abcde',1,2) from dual;
SQL> select substr('abcde',1,2) from dual;
SU
--
ababcde의 1부터 2개를 출력
<문5> emp테이블에서 사원이름의 첫글자가 'K'보다 크고(and), Y'자보다 적은 사원을 검색해서 그 찾은 사원의 사번,
이름,업무순으로 출력하시오.(단 이름순으로 정렬할것)=>order by ename(오름차순)
-
사원이름의 첫글자가 'K'보다 크고(and) => substr(ename,1,1)>K'
-
Y'자보다 적은 사원 => substr(ename,1,1)<'Y'
select empno,ename,job from emp
where substr(ename,1,1) > 'K' and
substr(ename,1,1) < 'Y'
order by ename;
SQL> select empno,ename,job from emp where substr(ename,1,1) > 'K' and substr(ename,1,1) < 'Y' order by ename;
EMPNO ENAME JOB
---------- ---------- ---------
7654 MARTIN SALESMAN
7934 MILLER CLERK
7788 SCOTT ANALYST
7369 SMITH CLERK
7844 TURNER SALESMAN
7521 WARD SALESMAN
6 개의 행이 선택되었습니다.
<문6> 문자열 검색중에서 특정문자가 어디에 위치하는지 알고싶다.?
형식) instr(적용대상문자열,찾고자하는 문자열)=>위치 번호를 알려준다.
sql>select ename,instr(ename,'O') =>자바에서는 indexOf
from emp;
SQL> select ename,instr(ename,'O') from emp;
ENAME INSTR(ENAME,'O')
---------- ----------------
SMITH 0 // 찾는 위치를 모름(없음)
ALLEN 0
WARD 0
JONES 2 // 문자열중에서 2번째 위치에 O가 있다.
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 3 // 문자열 중에서 3번째 위치에 O가 있다.
KING 0
TURNER 0
ADAMS 0
ENAME INSTR(ENAME,'O')
---------- ----------------
JAMES 0
FORD 2
MILLER 0
14 개의 행이 선택되었습니다.중첩함수=>함수 내부에 또 다른 함수가 있는 경우 실행하는 순서는 내부함수부터 시작
SQL> select ename,instr(ename,upper('o')) from emp;
<문7>숫자함수->계산,반올림,내림,,,=>round,trunc
-
round
select round(4567.678),round(4567.678,0),
round(4567.678,2),
round(4567.678,-2) from dual;
SQL> select round(4567.678),round(4567.678,0),round(4567.678,2), round(4567.678,-2) from dual;
ROUND(4567.678) ROUND(4567.678,0) ROUND(4567.678,2) ROUND(4567.678,-2)
--------------- ----------------- ----------------- ------------------
4568 4568 4567.68 4600=>반올림 자리수(양수)->소수점 자리수 계산
(음수)->소수점 바로 앞의 자리수를 가지고 계산
반이상 ->반올림
-
trunc
-
소수점을 잘라내는 함수(=절삭)
-
소수점의 자리수에 맞춰서 잘라내는 함수
-
SQL> select trunc(4567.678),trunc(4567.678,0),trunc(4567.678,2), trunc(4567.678,-2) from dual;
TRUNC(4567.678) TRUNC(4567.678,0) TRUNC(4567.678,2) TRUNC(4567.678,-2)
--------------- ----------------- ----------------- ------------------
4567 4567 4567.67 4500
-
mode, sign
-
mod함수 => 나머지 구해주는함수
-
sign함수 => 양수(1),음수(-1),0일때 0을 반환
-
SQL> select mod(10,3),sign(100),sign(-100),sign(0) from dual;
MOD(10,3) SIGN(100) SIGN(-100) SIGN(0)
---------- ---------- ---------- ----------
1 1 -1 0
<문8> sqlplus 중에서 보고서에 관련된 명령어
null =>데이터가 저장되지 않은 상태 , 눈에 X => not null (데이터를 반드시 입력하라)
' ' =>데이터가 저장, 눈에 O
SQL> desc dept //부서테이블
SQL> desc dept;
이름 널? 유형
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)SQL> select * from dept;
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-컬럼 길이 설정
형식) col[umn] 적용필드명 format 길이(an)
SQL> col dname format a8; // 8글자로 설정하겠다.(exit-> 다시 로그인하면 자동해제 된다.)
SQL> col dname format a8;
SQL> /
DEPTNO DNAME LOC
---------- -------- -------------
10 ACCOUNTI NEW YORK
NG
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIO BOSTON
NS
<문9> 적용시킨 필드의 값을 원래상태로 되돌리기(=초기화)
형식) col[umn] 해제시킬 필드명 clear
SQL> col dname clear;
SQL> col dname clear
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
<문10> 가상필드도 설정이 가능
=> e_name, e_empno
SQL> col e_name format a23
SQL> col e_empno format a15
SQL> select empno,ename,
concat(empno,ename) e_name,
concat(ename,empno) e_empno
from emp
where deptno=&deptno;
SQL> col e_name format a23
SQL> col e_empno format a15
SQL> select empno,ename,
2 concat(empno,ename) e_name,
3 concat(ename,empno) e_empno
4 from emp
5 where deptno=&deptno;
deptno의 값을 입력하십시오: 10
구 5: where deptno=&deptno
신 5: where deptno=10
EMPNO ENAME E_NAME E_EMPNO
---------- ---------- ----------------------- ---------------
7782 CLARK 7782CLARK CLARK7782
7839 KING 7839KING KING7839
7934 MILLER 7934MILLER MILLER7934
<문11>파라미터 인수질의 &deptno, '&deptno' emp테이블에서 사원번호는 반드시 출력하고,나머지 필드는 동적으로 입력받아서 출력하시오. (단 조건식도 동적으로 입력받을것)
형식) where 조건식=>&필드명, '&필드명'
select &필드명
from &필드명
where 조건=>&필드명
SQL> select empno,&column_name
2 from emp
3 where &condition;
column_name의 값을 입력하십시오: ename, sal, job
구 1: select empno,&column_name
신 1: select empno,ename, sal, job
condition의 값을 입력하십시오: ename like '%L%'
구 3: where &condition
신 3: where ename like '%L%'
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7499 ALLEN 1600 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7934 MILLER 1300 CLERK
<문12>lpad->특정문자열을 왼쪽으로 기준점설정=>지정한 특수기호를 출력
,rpad,replace함수
emp테이블에서 ename=>lpad(출력대상자문자열,자릿수,특수기호)
사번,이름,급여만 출력(단 부서번호는 20번인 데이터만 출력할것)
select empno,ename,lpad(ename,10,'*'),
sal,rpad(sal,10,'#')
from emp
where deptno=20;
SQL> select empno,ename,lpad(ename,10,'*'), sal,rpad(sal,10,'#') from emp where deptno=20;
EMPNO ENAME LPAD(ENAME,10,'*') SAL RPAD(SAL,10,'#')
---------- ---------- -------------------- ---------- --------------------
7369 SMITH *****SMITH 800 800#######
7566 JONES *****JONES 2975 2975######
7788 SCOTT *****SCOTT 3000 3000######
7876 ADAMS *****ADAMS 1100 1100######
7902 FORD ******FORD 3000 3000######replace(적용필드명,변경전문자열,변경후문자열)->emp =>실제 물리적변경X , 가상으로 보여주는 역할
<문13>replace함수
SQL> select ename,replace(ename,'A','$') as "Change Name"
from emp;
SQL> select ename,replace(ename,'A','$') as "Change Name"
2 from emp;
ENAME Change Nam
---------- ----------
SMITH SMITH
ALLEN $LLEN
WARD W$RD
JONES JONES
MARTIN M$RTIN
BLAKE BL$KE
CLARK CL$RK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS $D$MS
ENAME Change Nam
---------- ----------
JAMES J$MES
FORD FORD
MILLER MILLER
14 개의 행이 선택되었습니다.날짜함수 => 계산 => 날짜 + 숫자 => 더한 날짜
날짜 - 숫자 = 뺀날짜
날짜 - 날짜 = 일수
<문14> emp테이블에서 각 사원들의 근무일수(오늘날짜-입사일)
->months_between
-> 사원명,입사일,근무일수 순으로 출력 (단, 부서번호는 10번 부서)
-> 근무일수가 제일 많은순으로 정렬? (내림차순)
SQL> select ename,hiredate,
months_between(sysdate,hiredate) 근무일수
from emp
where deptno=&deptno
order by 근무일수 desc;
SQL> select ename,hiredate,
2 months_between(sysdate,hiredate) 근무일수
3 from emp
4 where deptno=&deptno
5 order by 근무일수 desc;
deptno의 값을 입력하십시오: 10
구 4: where deptno=&deptno
신 4: where deptno=10
ENAME HIREDATE 근무일수
---------- -------- ----------
CLARK 81/06/09 469
KING 81/11/17 463.764008
MILLER 82/01/23 461.57046
round 적용
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 select ename,hiredate,
2 round(months_between(sysdate,hiredate)) 근무일수
3 from emp
4 where deptno=&deptno
5* order by 근무일수 desc
SQL> /
deptno의 값을 입력하십시오: 20
구 4: where deptno=&deptno
신 4: where deptno=20
ENAME HIREDATE 근무일수
---------- -------- ----------
SMITH 80/12/17 475
JONES 81/04/02 471
FORD 81/12/03 463
SCOTT 87/04/19 399
ADAMS 87/05/23 398
<문15>add_months(날짜,개월수)->지정 날짜 개월수를 더한 날짜
emp테이블에서 입사일 기준 10개월후의 날짜를 계산 (hiredate)
(단 sal이 2000 이상인 사원들만 출력->이름,급여,10개월후 순으로 출력)
SQL> select ename,sal,hiredate,
add_months(hiredate,10) "10개월 후"
from emp
where sal>=2000;
hiredate
SQL> select ename,sal,hiredate,
2 add_months(hiredate,10) "10개월 후"
3 from emp
4 where sal>=2000;
ENAME SAL HIREDATE 10개월
---------- ---------- -------- --------
JONES 2975 81/04/02 82/02/02
BLAKE 2850 81/05/01 82/03/01
CLARK 2450 81/06/09 82/04/09
SCOTT 3000 87/04/19 88/02/19
KING 5000 81/11/17 82/09/17
FORD 3000 81/12/03 82/10/03
sysdate
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 select ename,sal,sysdate,
2 add_months(sysdate,10) "10개월 후"
3 from emp
4* where sal>=2000
SQL> /
ENAME SAL SYSDATE 10개월
---------- ---------- -------- --------
JONES 2975 20/07/09 21/05/09
BLAKE 2850 20/07/09 21/05/09
CLARK 2450 20/07/09 21/05/09
SCOTT 3000 20/07/09 21/05/09
KING 5000 20/07/09 21/05/09
FORD 3000 20/07/09 21/05/09
6 개의 행이 선택되었습니다.
<문16> next_day(지정한 날짜,'요일') => 지정한 요일에 해당하는 날짜구하기
=> 월요일,화요일,,, -> 내부적으로 숫자
일(1), 월(2), 화(3), 수(4), 목(5), 금(6), 토(7)
SQL> select deptno,ename,hiredate,
next_day(hiredate,'금요일') =>요일에 해당되는 날짜를 이용해서 특정날짜의 요일을 구하는 경우
from emp;
SQL> select deptno, ename, hiredate, next_day(hiredate, '금요일') from emp;
DEPTNO ENAME HIREDATE NEXT_DAY
---------- ---------- -------- --------
20 SMITH 80/12/17 80/12/19
30 ALLEN 81/02/20 81/02/27
30 WARD 81/02/22 81/02/27
20 JONES 81/04/02 81/04/03
30 MARTIN 81/09/28 81/10/02
30 BLAKE 81/05/01 81/05/08
10 CLARK 81/06/09 81/06/12
20 SCOTT 87/04/19 87/04/24
10 KING 81/11/17 81/11/20
30 TURNER 81/09/08 81/09/11
20 ADAMS 87/05/23 87/05/29
DEPTNO ENAME HIREDATE NEXT_DAY
---------- ---------- -------- --------
30 JAMES 81/12/03 81/12/04
20 FORD 81/12/03 81/12/04
10 MILLER 82/01/23 82/01/29
14 개의 행이 선택되었습니다.금요일 입력대신 6으로 입력해도 같은 결과가 나온다.
SQL> select deptno, ename, hiredate, next_day(hiredate, 6) from emp;
DEPTNO ENAME HIREDATE NEXT_DAY
---------- ---------- -------- --------
20 SMITH 80/12/17 80/12/19
30 ALLEN 81/02/20 81/02/27
30 WARD 81/02/22 81/02/27
20 JONES 81/04/02 81/04/03
30 MARTIN 81/09/28 81/10/02
30 BLAKE 81/05/01 81/05/08
10 CLARK 81/06/09 81/06/12
20 SCOTT 87/04/19 87/04/24
10 KING 81/11/17 81/11/20
30 TURNER 81/09/08 81/09/11
20 ADAMS 87/05/23 87/05/29
DEPTNO ENAME HIREDATE NEXT_DAY
---------- ---------- -------- --------
30 JAMES 81/12/03 81/12/04
20 FORD 81/12/03 81/12/04
10 MILLER 82/01/23 82/01/29
14 개의 행이 선택되었습니다.
<문17>last_day(지정한 날짜)=>지정한 날짜의 월의 마지막 날짜를 계산 =>윤년,평년으로 자동으로 계산
emp테이블에서 입사한 달의 근무일수를 계산?(5일,토,일->포함)
사원명이 두번째 글자가 A인 사원찾기
=> 근무일수가 많은 순으로 정렬(이름,입사일,근무일수 순으로 출력)
SQL> select ename,hiredate,
last_day(hiredate)-hiredate "근무일수"
from emp
where ename like '_A%'
order by 3 desc; // 3은 3번째 열을 기준으로 정렬하라는 의미
SQL> select ename, hiredate, last_day(hiredate)-hiredate "근무일수" from emp where ename like '_A%' order by 3 desc;
ENAME HIREDATE 근무일수
---------- -------- ----------
JAMES 81/12/03 28
WARD 81/02/22 6
MARTIN 81/09/28 2
오라클의 변환함수=>변환(문자,숫자) => 문자 -> 숫자 숫자 -> 문자,날짜
to_date 문자 -> 날짜형으로 변경(X) to_number 문자 -> 숫자로 변경(X)
to_char 날짜,숫자 -> 문자로 변경(O) => 잘 사용하는 경우(출력양식) 2020-07-01 => 20/07/01
<문18>emp테이블에서 20번 부서에 근무하는 사원의 급여앞에 $를 표시하고 3자리마다 ,를 표시해서 출력하는 SQL구문을 작성? (사번,이름,급여,출력서식)
sal -> 2370 -> $2,300 => to_char(적용대상필드,출력서식)
숫자의 자리수 -> 0 또는 9를 사용
SQL> select empno,ename,sal,to_char(sal,'$999,999')
from emp ￦표시할때 => $ 대신에 대문자 L
where deptno=20
order by sal desc;
SQL> select empno, ename, sal, to_char(sal,'$999,999') from emp where deptno=20 order by sal desc;
EMPNO ENAME SAL TO_CHAR(S
---------- ---------- ---------- ---------
7788 SCOTT 3000 $3,000
7902 FORD 3000 $3,000
7566 JONES 2975 $2,975
7876 ADAMS 1100 $1,100
7369 SMITH 800 $800
SQL> select empno, ename, sal, to_char(sal,'L999,999') from emp where deptno=20 order by sal desc;
EMPNO ENAME SAL TO_CHAR(SAL,'L999,
---------- ---------- ---------- ------------------
7788 SCOTT 3000 ￦3,000
7902 FORD 3000 ￦3,000
7566 JONES 2975 ￦2,975
7876 ADAMS 1100 ￦1,100
7369 SMITH 800 ￦800숫자,날짜=>내가 원하는 출력양식으로 출력->to_char( ) HH=>12시간 HH24->24시간 표시
select to_char(sysdate,'YYYY/MM/DD, HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYY
--------------------
2020/07/01, 15:16:46
<문19> 일반함수 nvl함수 => null값을 어떤 특정한 값으로 변환시킬때 사용하는 함수
형식) nvl(null값이 포함된 필드명,변경시킬 값)
emp=>comm(값 O, 값 X (null값))
SQL> select ename,sal,comm from emp;
SQL> select ename,sal,comm from emp;
ENAME SAL COMM
---------- ---------- ----------
SMITH 800
ALLEN 1600 300
WARD 1250 500
JONES 2975
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0
ADAMS 1100
ENAME SAL COMM
---------- ---------- ----------
JAMES 950
FORD 3000
MILLER 1300
14 개의 행이 선택되었습니다.
SQL> select ename,sal,comm,nvl(comm,0) from emp;
SQL> select ename,sal,comm,nvl(comm,0) from emp;
ENAME SAL COMM NVL(COMM,0)
---------- ---------- ---------- -----------
SMITH 800 0
ALLEN 1600 300 300
WARD 1250 500 500
JONES 2975 0
MARTIN 1250 1400 1400
BLAKE 2850 0
CLARK 2450 0
SCOTT 3000 0
KING 5000 0
TURNER 1500 0 0
ADAMS 1100 0
ENAME SAL COMM NVL(COMM,0)
---------- ---------- ---------- -----------
JAMES 950 0
FORD 3000 0
MILLER 1300 0
14 개의 행이 선택되었습니다.emp필드의 comm칼럼에 값이 있으면 그 값을 나타내고, 값이 없으면 null값이 나오는데 그 값을 어느 특정한 값(0)으로 변환 시켰다.
<문20>
형식) nvl2(null값을 체크할 필드명,인수1,인수2)
=> null이 아닌경우(값이 있으면) -> 인수1선택
=> null을 만나게 되면 => 인수2를 선택 -> 적용
=> 삼항연산자와 비슷한 기능
sal,comm
800 0=>800 =>보너스가 없으면 총급여(급여만 계산)
900 40=>940 =>보너스가 있으면 총급여(급여+보너스)
SQL> select ename,sal,comm,nvl2(comm,comm+sal,sal) from emp;
ENAME SAL COMM NVL2(COMM,COMM+SAL,SAL)
---------- ---------- ---------- -----------------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 0 1500
ADAMS 1100 1100
ENAME SAL COMM NVL2(COMM,COMM+SAL,SAL)
---------- ---------- ---------- -----------------------
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
14 개의 행이 선택되었습니다.
<문21> 부서별로 매출에 따라서 급여를 올려줄려고 한다.
ANALYST -> 급여 => 10%
CLERK -> 급여 => 15%
MANAGER -> 급여 => 20%
공식 -> 다중 if문 -> 각각의 조건에 따른 계산식을 적용
Decode함수 또는 case when구문을 이용해서 계산
형식) decode (적용시킬 필드명(job),조건식(분석가,,),계산값,
조건식2,계산값2,,,
디폴트값)
sql>select ename,job,sal,decode(job,'ANALYST',salx1.1,
'CLERK',sal*1.15,
'MANAGER',salx1.2,sal) "급여인상액"
SQL> select ename, job, sal, decode(job,'ANALYST',sal*1.1,'CLERK',sal*1.15,'MANAGER',sal*1.2,sal) "급여 인상액" from emp order by sal desc;
ENAME JOB SAL 급여인상액
---------- --------- ---------- ----------
KING PRESIDENT 5000 5000
FORD ANALYST 3000 3300
SCOTT ANALYST 3000 3300
JONES MANAGER 2975 3570
BLAKE MANAGER 2850 3420
CLARK MANAGER 2450 2940
ALLEN SALESMAN 1600 1600
TURNER SALESMAN 1500 1500
MILLER CLERK 1300 1495
WARD SALESMAN 1250 1250
MARTIN SALESMAN 1250 1250
ENAME JOB SAL 급여인상액
---------- --------- ---------- ----------
ADAMS CLERK 1100 1265
JAMES CLERK 950 1092.5
SMITH CLERK 800 920
14 개의 행이 선택되었습니다.decode함수와 동일한 기능
형식) case 적용대상필드명 when 조건식 then 결과1
when 조건식 then 결과2
...
else 결과 n
end
=>하나의 테이블가지고 검색하는 방법(연산자,함수를 이용)
'KIC > ORACLE' 카테고리의 다른 글
| [ Oracle] 서브쿼리, 집합연산자 (0) | 2020.10.12 |
|---|---|
| [Oracle] 조인개요 및 작성법 (0) | 2020.10.12 |
| [Oracle] 그룹함수, DDL, 테이블 제약조건 (0) | 2020.10.12 |
| [Oracle] select를 이용한 검색방법 및 SQLPlus 명령어 사용법 (0) | 2020.10.12 |
| [Oracle] (SQL 기초) (0) | 2020.09.21 |