***연결
***사용자 접속
***테이블 생성,데이터 삽입
***라인 조절
***함수 사용법( ename과 job 붙이기
ex1)
select ename||job from emp;
***중복 제거
***계층쿼리
***라인조절/페이지 조절
***찾기
==========================================
--처음 시작할때 명령창에서
sqlplus "/as sysdba"
--- 연결
startup
---ejb사용자로 접속
conn ejb/ejb
---테이블 생성
create table 테이블명(
컬럼명1 데이터형,
...
컬럼명n 데이터형
);
ex1)
create talbe addr(
no number,
irum varchar2(20),
phone varchar2(20),
city varchar2(20)
);
---테이블 확인
select * from addr;
---테이블의 구조 확인
desc addr;
---데이터 삽입
insert into addr values(1,'홍길동1','02-3123-1231','서울');
insert into addr values(2,'홍길동2','02-4123-1231','충남');
insert into addr values(3,'홍길동3','02-5123-1231','제주');
---확인
select * from addr;
--- scott 사용자로 이동
conn scott/tiger
----------------
select * from tab;
select * from dept;
select * from bonus;
---라인조절 (가로)
set linesize 150
select * from emp;
---페이지크기조절 (세로)
set pagesize 50
---전에 입력한 명령어
/
---select 명령어
select * from salgrade;
==Select Statement 의 기본형식
Select [*|컬럼명1,컬럼명2,...] From 테이블명
[ Where 조건]
[ Group by 그룹절]
[ Having 그룹절의 조건]
[ Order By 정렬 ]
desc emp;
---원하는 곳만 찾을 때
select ename,empno,job,sal from emp;
select empno,job,sal,comm,deptno from emp;
결과값
EMPNO JOB SAL COMM DEPTNO
----- --------- ---------- ---------- ----------
7369 CLERK 800 20
7499 SALESMAN 1600 300 30
7521 SALESMAN 1250 500 30
7566 MANAGER 2975 20
7654 SALESMAN 1250 1400 30
7698 MANAGER 2850 30
7782 MANAGER 2450 10
7788 ANALYST 3000 20
7839 PRESIDENT 5000 10
7844 SALESMAN 1500 0 30
7876 CLERK 1100 20
7900 CLERK 950 30
7902 ANALYST 3000 20
7934 CLERK 1300 10
---sal+comm
select empno,job,sal,comm,sal+comm,deptno from emp;
결과값
EMPNO JOB SAL COMM SAL+COMM DEPTNO
----- --------- ---------- ---------- ---------- ----------
7369 CLERK 800 20
7499 SALESMAN 1600 300 1900 30
7521 SALESMAN 1250 500 1750 30
7566 MANAGER 2975 20
7654 SALESMAN 1250 1400 2650 30
7698 MANAGER 2850 30
7782 MANAGER 2450 10
7788 ANALYST 3000 20
7839 PRESIDENT 5000 10
7844 SALESMAN 1500 0 1500 30
7876 CLERK 1100 20
7900 CLERK 950 30
7902 ANALYST 3000 20
7934 CLERK 1300 10
---nvl 함수 -> 어떤값이 널이면 0
select empno,job,sal,nvl(comm,0),sal+comm,deptno from emp;
EMPNO JOB SAL NVL(COMM,0) SAL+COMM DEPTNO
------ --------- ---------- ----------- ---------- ----------
7369 CLERK 800 0 20
7499 SALESMAN 1600 300 1900 30
7521 SALESMAN 1250 500 1750 30
7566 MANAGER 2975 0 20
7654 SALESMAN 1250 1400 2650 30
7698 MANAGER 2850 0 30
7782 MANAGER 2450 0 10
7788 ANALYST 3000 0 20
7839 PRESIDENT 5000 0 10
7844 SALESMAN 1500 0 1500 30
7876 CLERK 1100 0 20
7900 CLERK 950 0 30
7902 ANALYST 3000 0 20
7934 CLERK 1300 0 10
---널값이 경우 sal 값을 집어 넣기
select empno,job,sal,nvl(comm,0),sal+nvl(comm,0),deptno from emp;
결과값
EMPNO JOB SAL NVL(COMM,0) SAL+NVL(COMM,0) DEPTNO
------ --------- ---------- ----------- --------------- ----------
7369 CLERK 800 0 800 20
7499 SALESMAN 1600 300 1900 30
7521 SALESMAN 1250 500 1750 30
7566 MANAGER 2975 0 2975 20
7654 SALESMAN 1250 1400 2650 30
7698 MANAGER 2850 0 2850 30
7782 MANAGER 2450 0 2450 10
7788 ANALYST 3000 0 3000 20
7839 PRESIDENT 5000 0 5000 10
7844 SALESMAN 1500 0 1500 30
7876 CLERK 1100 0 1100 20
7900 CLERK 950 0 950 30
7902 ANALYST 3000 0 3000 20
7934 CLERK 1300 0 1300 10
--- 필드 이름 다르게 표시
ex1)
select empno,job,sal,nvl(comm,0)as bonus,sal+nvl(comm,0)as Total,deptno from emp;
EMPNO JOB SAL BONUS TOTAL DEPTNO
------ --------- ---------- ---------- ---------- ----------
7369 CLERK 800 0 800 20
7499 SALESMAN 1600 300 1900 30
7521 SALESMAN 1250 500 1750 30
7566 MANAGER 2975 0 2975 20
7654 SALESMAN 1250 1400 2650 30
7698 MANAGER 2850 0 2850 30
7782 MANAGER 2450 0 2450 10
7788 ANALYST 3000 0 3000 20
7839 PRESIDENT 5000 0 5000 10
7844 SALESMAN 1500 0 1500 30
7876 CLERK 1100 0 1100 20
7900 CLERK 950 0 950 30
7902 ANALYST 3000 0 3000 20
7934 CLERK 1300 0 1300 10
ex2) 이중 따옴표 " " 안에 있는 이름은 그대로 나옴
select empno as 이름,job as "업 무",sal,nvl(comm,0)as bonus,sal+nvl(comm,0)
as "Total",deptno from emp;
결과값
이름 업 무 SAL BONUS Total DEPTNO
----- --------- ---------- ---------- ---------- ----------
7369 CLERK 800 0 800 20
7499 SALESMAN 1600 300 1900 30
7521 SALESMAN 1250 500 1750 30
7566 MANAGER 2975 0 2975 20
7654 SALESMAN 1250 1400 2650 30
7698 MANAGER 2850 0 2850 30
7782 MANAGER 2450 0 2450 10
7788 ANALYST 3000 0 3000 20
7839 PRESIDENT 5000 0 5000 10
7844 SALESMAN 1500 0 1500 30
7876 CLERK 1100 0 1100 20
7900 CLERK 950 0 950 30
7902 ANALYST 3000 0 3000 20
7934 CLERK 1300 0 1300 10
--- ename과 job 붙이기
ex1)
select ename||job from emp;
결과값
ENAME||JOB
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK
ex2)
select ename ||'의 업무는 '|| job || '입니다.' from emp;
결과값
ENAME||'의업무는'||JOB||'입니다.'
------------------------------------
SMITH의 업무는 CLERK입니다.
ALLEN의 업무는 SALESMAN입니다.
WARD의 업무는 SALESMAN입니다.
JONES의 업무는 MANAGER입니다.
MARTIN의 업무는 SALESMAN입니다.
BLAKE의 업무는 MANAGER입니다.
CLARK의 업무는 MANAGER입니다.
SCOTT의 업무는 ANALYST입니다.
KING의 업무는 PRESIDENT입니다.
TURNER의 업무는 SALESMAN입니다.
ADAMS의 업무는 CLERK입니다.
JAMES의 업무는 CLERK입니다.
FORD의 업무는 ANALYST입니다.
MILLER의 업무는 CLERK입니다.
ex3)
select ename ||'의 업무는 "'|| job || '"입니다.' from emp;
결과값
ENAME||'의업무는"'||JOB||'"입니다.'
-------------------------------------
SMITH의 업무는 "CLERK"입니다.
ALLEN의 업무는 "SALESMAN"입니다.
WARD의 업무는 "SALESMAN"입니다.
JONES의 업무는 "MANAGER"입니다.
MARTIN의 업무는 "SALESMAN"입니다.
BLAKE의 업무는 "MANAGER"입니다.
CLARK의 업무는 "MANAGER"입니다.
SCOTT의 업무는 "ANALYST"입니다.
KING의 업무는 "PRESIDENT"입니다.
TURNER의 업무는 "SALESMAN"입니다.
ADAMS의 업무는 "CLERK"입니다.
JAMES의 업무는 "CLERK"입니다.
FORD의 업무는 "ANALYST"입니다.
MILLER의 업무는 "CLERK"입니다.
---
ex1)
급여가 2000보다 큰 사람
select ename,job,sal from emp where sal >=2000;
결과값
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
---
ex2) job ANALYST 인 사람
select ename,job,sal from emp where job='ANALYST';
결과값
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
FORD ANALYST 3000
ex3)
-select ename,job,sal from emp where sal >=1000 and sal<=2000;
-select ename,job,sal from emp where sal between 1000 and 2000;
결과
ENAME JOB SAL
---------- --------- ----------
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ADAMS CLERK 1100
MILLER CLERK 1300
ex4)
select ename,job,sal from emp where sal <1000 or sal > 3000;
result
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
KING PRESIDENT 5000
JAMES CLERK 950
ex5)
---업무가 세이즈맨이거나 매니져인 사람 출력
-select ename,job,sal from emp where job like 'SALESMAN' or job like'MANAGER';
--IN연산자
select ename,job,sal from emp
where job in ('SALESMAN','MANAGER');
result
ENAME JOB SAL
---------- --------- ----------
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
TURNER SALESMAN 1500
select ename,job,sal from emp where job in('SALESMAN','MANAGER');
---------------------
==
% : 여러 문자열
_ : 문자 하나
------------------
--- A으로 시작하는 이름을 가진 사람 찾기
select ename,job,sal from emp where ename like 'A%';
ENAME JOB SAL
---------- --------- ----------
ALLEN SALESMAN 1600
ADAMS CLERK 1100
--R로 끝나는 이름을 가진 사람 찾기
select ename,job,sal from emp where ename like '%R';
ENAME JOB SAL
---------- --------- ----------
TURNER SALESMAN 1500
MILLER CLERK 1300
--A가 들어간 이름 찾기
select ename,job,sal from emp where ename like '%A%';
ENAME JOB SAL
---------- --------- ----------
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
ADAMS CLERK 1100
JAMES CLERK 950
--3번째 글자가 A인 사람
select ename,job,sal from emp where ename like '__A%';
ENAME JOB SAL
---------- --------- ----------
BLAKE MANAGER 2850
CLARK MANAGER 2450
ADAMS CLERK 1100
-------------------------------
select empno,ename from emp;
EMPNO ENAME
------ ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14개의 행이 선택되었습니다.
insert into emp(empno,ename) values(80,'happy_day');
insert into emp(empno,ename) values(81,'nice_day');
insert into emp(empno,ename) values(90,'happy%day');
insert into emp(empno,ename) values(91,'nice%day');
select empno,ename from emp;
EMPNO ENAME
------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
80 happy_day
81 nice_day
90 nice%day
91 happy%day
18개의 행이 선택되었습니다.
--- _가 들어가이름 찾기
select ename,job,sal from emp where ename like '%?_%' escape '?';
re:
ENAME JOB SAL
---------- --------- ----------
happy_day
nice_day
---%가 들어가 이름 찾기
select ename,job,sal from emp where ename like '%?%%' escape '?';
re:
ENAME JOB SAL
---------- --------- ----------
nice%day
happy%day
---입력작업 취소
rollback
---select empno,ename from emp;
re:
EMPNO ENAME
----- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
---정렬
select empno,job,sal,comm,deptno from emp order by deptno;
re:
EMPNO JOB SAL COMM DEPTNO
------ --------- ---------- ---------- ----------
7782 MANAGER 2450 10
7839 PRESIDENT 5000 10
7934 CLERK 1300 10
7369 CLERK 800 20
7876 CLERK 1100 20
7902 ANALYST 3000 20
7788 ANALYST 3000 20
7566 MANAGER 2975 20
7499 SALESMAN 1600 300 30
7698 MANAGER 2850 30
7654 SALESMAN 1250 1400 30
7900 CLERK 950 30
7844 SALESMAN 1500 0 30
7521 SALESMAN 1250 500 30
---내림차순
--select empno,job,sal,comm,deptno from emp order by deptno desc;
re:
EMPNO JOB SAL COMM DEPTNO
------ --------- ---------- ---------- ----------
7499 SALESMAN 1600 300 30
7521 SALESMAN 1250 500 30
7654 SALESMAN 1250 1400 30
7900 CLERK 950 30
7844 SALESMAN 1500 0 30
7698 MANAGER 2850 30
7369 CLERK 800 20
7902 ANALYST 3000 20
7876 CLERK 1100 20
7566 MANAGER 2975 20
7788 ANALYST 3000 20
7782 MANAGER 2450 10
7839 PRESIDENT 5000 10
7934 CLERK 1300 10
---번호가 같으면 다시 이름 순으로 정렬
select ename,empno,job,sal,comm,deptno from emp order by deptno,ename;
re:
ENAME EMPNO JOB SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ----------
CLARK 7782 MANAGER 2450 10
KING 7839 PRESIDENT 5000 10
MILLER 7934 CLERK 1300 10
ADAMS 7876 CLERK 1100 20
FORD 7902 ANALYST 3000 20
JONES 7566 MANAGER 2975 20
SCOTT 7788 ANALYST 3000 20
SMITH 7369 CLERK 800 20
ALLEN 7499 SALESMAN 1600 300 30
BLAKE 7698 MANAGER 2850 30
JAMES 7900 CLERK 950 30
MARTIN 7654 SALESMAN 1250 1400 30
TURNER 7844 SALESMAN 1500 0 30
WARD 7521 SALESMAN 1250 500 30
-- 번호 내림, 이름 오름차순
select ename,empno,job,sal,comm,deptno from emp order by deptno desc,ename
re:
ENAME EMPNO JOB SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ----------
ALLEN 7499 SALESMAN 1600 300 30
BLAKE 7698 MANAGER 2850 30
JAMES 7900 CLERK 950 30
MARTIN 7654 SALESMAN 1250 1400 30
TURNER 7844 SALESMAN 1500 0 30
WARD 7521 SALESMAN 1250 500 30
ADAMS 7876 CLERK 1100 20
FORD 7902 ANALYST 3000 20
JONES 7566 MANAGER 2975 20
SCOTT 7788 ANALYST 3000 20
SMITH 7369 CLERK 800 20
CLARK 7782 MANAGER 2450 10
KING 7839 PRESIDENT 5000 10
MILLER 7934 CLERK 1300 10
다른 데이터 입력 하는 부분
---카폐의 쿼리1을 복사하여 실행하세요.
conn ejb/ejb
카페의 쿼리1을 복사하여 실행하세요
http://cafe.naver.com/javada.cafe
오라클-쿼리1
==테이블 삭제==
drop table personnel;
drop table division;
drop table paygrade;
drop table bonus;
---
conn scott/tiger
select job from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
--중복제거
select distinct job from emp;
re:
JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
--값이 없는 경우 보여주기
select ename,mgr,sal,comm from emp where comm is null;
결과
ENAME MGR SAL COMM
---------- ---------- ---------- ----------
SMITH 7902 800
JONES 7839 2975
BLAKE 7839 2850
CLARK 7839 2450
SCOTT 7566 3000
KING 5000
ADAMS 7788 1100
JAMES 7698 950
FORD 7566 3000
MILLER 7782 1300
--널이 아닌 경우
select ename,mgr,sal,comm from emp where comm is not null;
결과
ENAME MGR SAL COMM
---------- ---------- ---------- ----------
ALLEN 7698 1600 300
WARD 7698 1250 500
MARTIN 7698 1250 1400
TURNER 7698 1500 0
--- 계층쿼리
conn scott/tiger
desc emp;
--
select rowid,empno,ename,job,mgr from emp;
re:
ROWID EMPNO ENAME JOB MGR
------------------ ---------- ---------- --------- ----------
AAAHW7AABAAAMUiAAA 7369 SMITH CLERK 7902
AAAHW7AABAAAMUiAAB 7499 ALLEN SALESMAN 7698
AAAHW7AABAAAMUiAAC 7521 WARD SALESMAN 7698
AAAHW7AABAAAMUiAAD 7566 JONES MANAGER 7839
AAAHW7AABAAAMUiAAE 7654 MARTIN SALESMAN 7698
AAAHW7AABAAAMUiAAF 7698 BLAKE MANAGER 7839
AAAHW7AABAAAMUiAAG 7782 CLARK MANAGER 7839
AAAHW7AABAAAMUiAAH 7788 SCOTT ANALYST 7566
AAAHW7AABAAAMUiAAI 7839 KING PRESIDENT
AAAHW7AABAAAMUiAAJ 7844 TURNER SALESMAN 7698
AAAHW7AABAAAMUiAAK 7876 ADAMS CLERK 7788
AAAHW7AABAAAMUiAAL 7900 JAMES CLERK 7698
AAAHW7AABAAAMUiAAM 7902 FORD ANALYST 7566
AAAHW7AABAAAMUiAAN 7934 MILLER CLERK 7782
--
select rownum,level,empno,ename,job,mgr from emp;
re:
ROWNUM LEVEL EMPNO ENAME JOB MGR
------- ---------- ---------- ---------- --------- ----------
1 0 7369 SMITH CLERK 7902
2 0 7499 ALLEN SALESMAN 7698
3 0 7521 WARD SALESMAN 7698
4 0 7566 JONES MANAGER 7839
5 0 7654 MARTIN SALESMAN 7698
6 0 7698 BLAKE MANAGER 7839
7 0 7782 CLARK MANAGER 7839
8 0 7788 SCOTT ANALYST 7566
9 0 7839 KING PRESIDENT
10 0 7844 TURNER SALESMAN 7698
11 0 7876 ADAMS CLERK 7788
12 0 7900 JAMES CLERK 7698
13 0 7902 FORD ANALYST 7566
14 0 7934 MILLER CLERK 7782
'ORACLE' 카테고리의 다른 글
| 오라클 연산/집합 (0) | 2024.01.16 |
|---|---|
| 오라클 문자열 검사 (2) | 2024.01.16 |
| 가지치기//날짜&시간 형식 바꾸기//계층쿼리//출력형식 맞추기 (0) | 2024.01.16 |
| 오라클 Database 생성 (0) | 2024.01.16 |
| 오라클 Database 생성 (0) | 2018.12.08 |