SQL> conn scott/tiger
연결되었습니다.
SQL> select sysdate from emp;
--문자함수
SYSDATE
--------
05/03/25
05/03/25
05/03/25
05/03/25
05/03/25
05/03/25
05/03/25
05/03/25
05/03/25
05/03/25
05/03/25
SYSDATE
--------
05/03/25
05/03/25
05/03/25
14 개의 행이 선택되었습니다.
SQL> select sysdate from dual;
SYSDATE
--------
05/03/25
==========================================
--대문자,소문자 첫글자만 대문자
SQL> select ename,upper(ename),lower(ename),initcap(ename)
ENAME UPPER(ENAM LOWER(ENAM INITCAP(EN
---------- ---------- ---------- ----------
SMITH SMITH smith Smith
ALLEN ALLEN allen Allen
WARD WARD ward Ward
JONES JONES jones Jones
MARTIN MARTIN martin Martin
BLAKE BLAKE blake Blake
CLARK CLARK clark Clark
SCOTT SCOTT scott Scott
KING KING king King
TURNER TURNER turner Turner
ADAMS ADAMS adams Adams
ENAME UPPER(ENAM LOWER(ENAM INITCAP(EN
---------- ---------- ---------- ----------
JAMES JAMES james James
FORD FORD ford Ford
MILLER MILLER miller Miller
14 개의 행이 선택되었습니다.
===============================================
--문자열의 수 검사
SQL> select length('abcde'),length('가나다라마')from dual;
LENGTH('ABCDE') LENGTH('가나다라마')
--------------- --------------------
5 5
-----------------------------------------
===============================================
--바이트 크기로 문자열 검사
SQL> select lengthb('abcde'),lengthb('가나다라마')from dual;
LENGTHB('ABCDE') LENGTHB('가나다라마')
---------------- ---------------------
5 10
select substr('abcdefghijkl',1),
substr('abcdefghijkl',2),
substr('abcdefghijkl',5,2) from dual;
re:
SUBSTR('ABCD SUBSTR('ABC SU
------------ ----------- --
abcdefghijkl bcdefghijkl ef
select instr('oraoraoraoraora','o'),
instr('oraoraoraoraora','o',2),
instr('oraoraoraoraora','o',2,2),
instr('oraoraoraoraora','o',2,4) from dual;
re:
INSTR('ORAORAORAORAORA','O') INSTR('ORAORAORAORAORA','O',2)
---------------------------- ------------------------------
INSTR('ORAORAORAORAORA','O',2,2) INSTR('ORAORAORAORAORA','O',2,4)
-------------------------------- --------------------------------
1 4
7 13
===========================================================
SQL> select concat(ename,job),ename|| '의 업무는 '|| job from emp; --concat는 컬럼 두개 밖에 합치지 못함
CONCAT(ENAME,JOB) ENAME||'의업무는'||JOB
------------------- ------------------------------
SMITHCLERK SMITH의 업무는 CLERK
ALLENSALESMAN ALLEN의 업무는 SALESMAN
WARDSALESMAN WARD의 업무는 SALESMAN
JONESMANAGER JONES의 업무는 MANAGER
MARTINSALESMAN MARTIN의 업무는 SALESMAN
BLAKEMANAGER BLAKE의 업무는 MANAGER
CLARKMANAGER CLARK의 업무는 MANAGER
SCOTTANALYST SCOTT의 업무는 ANALYST
KINGPRESIDENT KING의 업무는 PRESIDENT
TURNERSALESMAN TURNER의 업무는 SALESMAN
ADAMSCLERK ADAMS의 업무는 CLERK
CONCAT(ENAME,JOB) ENAME||'의업무는'||JOB
------------------- ------------------------------
JAMESCLERK JAMES의 업무는 CLERK
FORDANALYST FORD의 업무는 ANALYST
MILLERCLERK MILLER의 업무는 CLERK
14 개의 행이 선택되었습니다.
=========================================
SQL> select ename,lpad(ename,10,'#'),rpad(ename,10,'?')from emp; --- 10자리로 표시하고 남는 자리는 좌측 #으로 표시, 10자리 표시하고 남는 자리는 우측으로 ? 표시
ENAME LPAD(ENAME,10,'#') RPAD(ENAME,10,'?')
---------- -------------------- --------------------
SMITH #####SMITH SMITH?????
ALLEN #####ALLEN ALLEN?????
WARD ######WARD WARD??????
JONES #####JONES JONES?????
MARTIN ####MARTIN MARTIN????
BLAKE #####BLAKE BLAKE?????
CLARK #####CLARK CLARK?????
SCOTT #####SCOTT SCOTT?????
KING ######KING KING??????
TURNER ####TURNER TURNER????
ADAMS #####ADAMS ADAMS?????
ENAME LPAD(ENAME,10,'#') RPAD(ENAME,10,'?')
---------- -------------------- --------------------
JAMES #####JAMES JAMES?????
FORD ######FORD FORD??????
MILLER ####MILLER MILLER????
14 개의 행이 선택되었습니다.
==================================
--trim 특정 문자 제거
SQL> select rtrim('abbbbbbbbbbbbbb','b')from dual;
R
-
a
SQL> select ltrim('abbbbbbbbbbbbbb','b')from dual;
LTRIM('ABBBBBBB
---------------
abbbbbbbbbbbbbb
==========================
--문자 바꾸기
SQL> select replace('나는 자짱면이 좋아','자','짜')from dual;
REPLACE('나는자짱
------------------
나는 짜짱면이 좋아
=============================++++++++++++++++++++++++++++++++++++++==========================
--숫자함수
---반올림 함수(round)
SQL> select round(3.141592),round(3.141592,2),
2 round(3.141592,4),round(314.1592,-2)from dual;
ROUND(3.141592) ROUND(3.141592,2) ROUND(3.141592,4) ROUND(314.1592,-2)
--------------- ----------------- ----------------- ------------------
3 3.14 3.1416 300
======================================
--trunc( 버림)
SQL> select trunc(3.141592),trunc(3.141592,2),
2 trunc(3.141592,4),trunc(314.1592,-2)from dual;
TRUNC(3.141592) TRUNC(3.141592,2) TRUNC(3.141592,4) TRUNC(314.1592,-2)
--------------- ----------------- ----------------- ------------------
3 3.14 3.1415 300
=========================================
--ceil(올림// 자리수 정하는거 없음)
SQL> select ceil(3.141592),ceil(3.141592),
2 ceil(3.141592),ceil(314.1592)from dual;
CEIL(3.141592) CEIL(3.141592) CEIL(3.141592) CEIL(314.1592)
-------------- -------------- -------------- --------------
4 4 4 315
=============================================
--mod(나머지 구하는 함수)
SQL> select mod(10,5),mod(10,3),mod(10,7) from dual;
MOD(10,5) MOD(10,3) MOD(10,7)
---------- ---------- ----------
0 1 3
=============================================
-----수치값 구하는 함수들
SQL> select abs(-100),abs(100)from dual;
ABS(-100) ABS(100)
---------- ----------
100 100
==============================
SQL> select sign(-10),sign(100),sign(0) from dual;
SIGN(-10) SIGN(100) SIGN(0)
---------- ---------- ----------
-1 1 0
=======================================================++++++++++++++++====================
--그룹함수
SQL> select count(*),min(sal),max(sal),sum(sal),avg(sal) from emp;
COUNT(*) MIN(SAL) MAX(SAL) SUM(SAL) AVG(SAL)
---------- ---------- ---------- ---------- ----------
14 800 5000 29025 2073.21429
=============================================
--부서번호,이름,부서인원
--업무별
SQL> select job,count(*),min(sal),max(sal),sum(sal)from emp group by job;
JOB COUNT(*) MIN(SAL) MAX(SAL) SUM(SAL)
--------- ---------- ---------- ---------- ----------
ANALYST 2 3000 3000 6000
CLERK 4 800 1300 4150
MANAGER 3 2450 2975 8275
PRESIDENT 1 5000 5000 5000
SALESMAN 4 1250 1600 5600
--부서번호별
SQL> select deptno,count(*),min(sal),max(sal),sum(sal),avg(sal)
2 from emp group by deptno;
DEPTNO COUNT(*) MIN(SAL) MAX(SAL) SUM(SAL) AVG(SAL)
---------- ---------- ---------- ---------- ---------- ----------
10 3 1300 5000 8750 2916.66667
20 5 800 3000 10875 2175
30 6 950 2850 9400 1566.66667
=====================
SQL> select deptno,job,count(*) from emp group by deptno,job;
DEPTNO JOB COUNT(*)
---------- --------- ----------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 CLERK 2
20 ANALYST 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
9 개의 행이 선택되었습니다.
=================================================
480(p)
--rollup
SQL> select deptno,job,count(*) from emp group by rollup(deptno,job);
DEPTNO JOB COUNT(*)
---------- --------- ----------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
10 3
20 CLERK 2
20 ANALYST 2
20 MANAGER 1
20 5
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
DEPTNO JOB COUNT(*)
---------- --------- ----------
30 6
14
===========================================
SQL> select deptno,job,count(*)from emp group by cube(deptno,job);
DEPTNO JOB COUNT(*)
---------- --------- ----------
14
CLERK 4
ANALYST 2
MANAGER 3
SALESMAN 4
PRESIDENT 1
10 3
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 5
DEPTNO JOB COUNT(*)
---------- --------- ----------
20 CLERK 2
20 ANALYST 2
20 MANAGER 1
30 6
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
18 개의 행이 선택되었습니다.
-->(deptno,job),(deptno),(job)()
==========================================
SQL> select deptno,job,count(*),grouping(deptno),grouping(job) from emp group b
rollup(deptno,job);
DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
10 CLERK 1 0 0
10 MANAGER 1 0 0
10 PRESIDENT 1 0 0
10 3 0 1
20 CLERK 2 0 0
20 ANALYST 2 0 0
20 MANAGER 1 0 0
20 5 0 1
30 CLERK 1 0 0
30 MANAGER 1 0 0
30 SALESMAN 4 0 0
DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
30 6 0 1
14 1 1
13 개의 행이 선택되었습니다.
=============
--부서별 직급
--group by 는 Having 절에서 조건 검색
SQL> select deptno,count(*) from emp
2 group by deptno having deptno<>20;
DEPTNO COUNT(*)
---------- ----------
10 3
30 6
===============================
--날짜함수
date-number =date
date-number=date
date-date=Number of days
date+number/24=date
SQL> select sysdate+3 from dual;
SYSDATE+
--------
05/03/28
SQL> select sysdate-3 from dual;
SYSDATE-
--------
05/03/22
=============================
--3시간 더하기
--현재시간오후 4:29 2005-03-25
SQL> select sysdate+3/24 from dual;
SYSDATE+3/24
-------------------
2005-03-25 19:29:04
===================
--20분 더하기
현재 시간오후 4:30 2005-03-25
SQL> select sysdate+20/1440 from dual;
SYSDATE+20/1440
-------------------
2005-03-25 16:49:56
==================================
---몇일 지나갔는지
SQL> select sysdate-to_date('2005-01-01') from dual;
SYSDATE-TO_DATE('2005-01-01')
-----------------------------
83.6903819
SQL> select sysdate-to_date('2005-01-01','yyyy-mm-dd') from dual;
SYSDATE-TO_DATE('2005-01-01','YYYY-MM-DD')
------------------------------------------
83.6909722
===============================================================
--몇달 지나갔는가
--MONTHS_BETWEEN == 자동으로 날짜 형식으로 변환
SQL> select months_between(sysdate,'2005-01-01') from dual;
MONTHS_BETWEEN(SYSDATE,'2005-01-01')
------------------------------------
2.79652554
===============================================================
---마지막 날 구하기
SQL> select last_day(sysdate),last_day(sysdate)+1 from dual;
LAST_DAY(SYSDATE) LAST_DAY(SYSDATE)+1
------------------- -------------------
2005-03-31 16:39:45 2005-04-01 16:39:45
===========================================
--오늘 날짜를 기준으로 가장 가까운 일요일,월요일 찾기
SQL> select next_day(sysdate,'일'),next_day(sysdate,'월요일')from dual;
NEXT_DAY(SYSDATE,' NEXT_DAY(SYSDATE,'
------------------- -------------------
2005-03-27 16:41:22 2005-03-28 16:41:22
================================================================
-- 날짜반올림
SQL> select round(sysdate,'month')from dual;
ROUND(SYSDATE,'MONT
-------------------
2005-04-01 00:00:00
============================
conn ejb/ejb
SQL> select pname,manager from personnel;
PNAME MANAGER
--------------- ----------
SMITH 1001
ALLY 1116
WORD 1116
JAMES 1001
BILL
JOHNSON 1001
MARTIN 1111
KIM 1114
LEE 1116
PARK 1111
HELLO_KIM 1116
============================================================
--manager 가 없으면 없다고 나오게
--숫자형식을 문자형식으로 (to_char)
SQL> select pname,nvl(to_char(manager),'없음')from personnel;
PNAME NVL(TO_CHAR(MANAGER),'없음')
--------------- ----------------------------------------
SMITH 1001
ALLY 1116
WORD 1116
JAMES 1001
BILL 없음
JOHNSON 1001
MARTIN 1111
KIM 1114
LEE 1116
PARK 1111
HELLO_KIM 1116
================================
---있음 없음 구별 해서 출력
---NVL2를 사용함
SQL> select pname,nvl2(to_char(manager),'있음','없음')from personnel;
PNAME NVL2
--------------- ----
SMITH 있음
ALLY 있음
WORD 있음
JAMES 있음
BILL 없음
JOHNSON 있음
MARTIN 있음
KIM 있음
LEE 있음
PARK 있음
HELLO_KIM 있음
==========================
--부서번호별 월급 올려주기?
SQL> select dno,pname,pay,decode(dno,10,pay*1.1,20,pay*1.5,pay*2)인상액 from pe
sonnel order by dno;
DNO PNAME PAY 인상액
---------- --------------- ---------- ----------
10 SMITH 1000 1100
10 BILL 7000 7700
10 MARTIN 3450 3795
10 PARK 2500 2750
20 JAMES 3975 5962.5
20 KIM 4000 6000
20 HELLO_KIM 5000 7500
30 ALLY 1600 3200
30 WORD 1450 2900
30 LEE 1200 2400
30 JOHNSON 3550 7100
11 개의 행이 선택되었습니다.
=================+++++++++++++++++++++++++++++++++====================
--카폐의 퀴러2 실행
select * from student;
select * from part;
-- 접수번호,이름,출신학교,지원학과명
-----------------
ex1)
SQL> select bunho,irum,school,partname from student s, part p where s.partcd=p.partcd;
BUNHO IRUM SCHOOL PARTNAME
---------- -------------------- -------------------- --------------------
2003120001 강감찬 백제고 컴퓨터공학과
2003120002 산만해 강원 멀티미디어학과
2003120003 김희선 서울여고 가정관리학과
2003120004 최진실 서여고 컴퓨터공학과
2003120005 최부자 서울 멀티미디어학과
2003120006 강영실 고구려 컴퓨터공학과
2003120007 소찬휘 한라 국어국문학과
2003120008 김주희 한성 가정관리학과
2003120009 박두열 남고 원예학과
2003120010 박문수 고구려 컴퓨터공학과
================================================================
ex2)
SQL> select s.bunho,s.irum,s.school,p.partname from student s, part p where s.pa
rtcd=p.partcd;
BUNHO IRUM SCHOOL PARTNAME
---------- -------------------- -------------------- --------------------
2003120001 강감찬 백제고 컴퓨터공학과
2003120002 산만해 강원 멀티미디어학과
2003120003 김희선 서울여고 가정관리학과
2003120004 최진실 서여고 컴퓨터공학과
2003120005 최부자 서울 멀티미디어학과
2003120006 강영실 고구려 컴퓨터공학과
2003120007 소찬휘 한라 국어국문학과
2003120008 김주희 한성 가정관리학과
2003120009 박두열 남고 원예학과
2003120010 박문수 고구려 컴퓨터공학과
'ORACLE' 카테고리의 다른 글
| 오라클 연산/급여계산 (1) | 2024.01.16 |
|---|---|
| 오라클 연산/집합 (0) | 2024.01.16 |
| 가지치기//날짜&시간 형식 바꾸기//계층쿼리//출력형식 맞추기 (0) | 2024.01.16 |
| 오라클 사용자 접속/ 테이블 생성/ 데이터 삽입/ 라인조절/ 중복제거/ 찾기 (0) | 2024.01.16 |
| 오라클 Database 생성 (0) | 2024.01.16 |