Oracle 명령어

오라클은 계정 한개가 데이터 베이스 한 덩어리 라고 생각하면 된다.필드명, 컬럼명은 영어로 작성 하는게 좋다.한줄의 데이타를 record라고 부른다.not null외에 표시가 안된 것 들은 모두 null이다

 

ed를 치면 메모장이 뜸 그 메모장에서 원하는 수식을 적은 다음에 저장하고 끄고cmd창에서 /를 치면 실행이 된다. 이때 커맨드 창과는 달리 수식의 마지막에 ;을 적으면 안된다

 

문자 선택시에는 “ “가 아닌 ‘ ‘ 사용그리고 대소문자를 가린다.

select ename,job from emp where job='SALESMAN' or job='MANAGER';

select ename from emp where ename>'CLARK';

글자 비교도 가능하다.@d:/ggg/ghj.sql 해당 위치에 있는 sql파일을 실행시킨다.

 

기본 명령어 (테이블 구조확인)

select * from tab; 테이블 목록 확인

 

dsec 테이블명 테이블 구조 확인number(5,3) 유형에 있는 괄호 안에 있는 숫자가 확보해놓은 자리 총 5자리 소수이하 3자리 가능.number int 4바이트로 표현할수 있는 정수varchar2<14> 문자 14글자 가능.date 컴퓨터가 가지고 있는 날자 정보.

 

select * from 테이블명; 레코드 확인set linesize 120; 한줄에 표시할 글자수 120 글자.set pagesize 20; 한번에 20줄씩 출력

 

select ename, sal from emp;

emp테이블에 ename과 sal필드의 내용만 본다.select 필드명1,필드명2,...여러개 가능하다 from 테이블명;

 

 

where 조건식

where - 조건식 내가 필요한 데이터만 불러들인다.오라클에서 = 같다의 표시!= 같지 않다의 표시.급여가 3천 이상인 사람들만 보고 싶을 경우

select sal

from 테이블명

where sal>=3000;

and 사용법 empno 7500 보다 크고 deptno가 30인 레코드 선택

select * from emp where empno>=7500 and deptno>=30;

between 은 이상 이하를 가르킨다. 초과 미만이 아님.

select * from emp where empno between 7500 and 7800;

in 사용법

select * from emp where mgr=7839 or mgr=7698 or mgr=7782;

위와 동일

select * from emp where mgr in(7839,7782,7782);

not in은 안에 조건에 있는것을 제외하고 출력한다

select * from emp where empno not in(7900,7876,7782);

%표시는 뒤의 글자는 뭐가 와도 상관 없다는 표시이다(윈도우에서 *과 마찬가지)_ 는 한 글자를 표시한다.

select ename from emp where ename like'J%';

select ename from emp where ename like'%J'; J로 끝나는 이름을 가진사람select ename from emp where ename like'%A%'; 중간에 a가 있는 사람을 출력select ename from emp where ename like' _ A%'; 2번째 글자에 A가 있는 사람 출력

select ename from emp where ename like'%A%S%';

 

버퍼관련

L

을 누르면 버퍼에 있는 쿼리문을 보여준다버퍼에 있는 쿼리문중 3번째 줄

L 3

2번재 줄부터 4번째 줄까지

L 2 4

c /comm/comm,ename

L4줄에 있는 comm을 comm,ename로 수정하라del 5 5번째 줄 삭제append 내용 추가input 줄을 한줄 추가한다 input대신 

i

 로도 가능.

input where deptno between 20 and 30 or mgr=7839;

NULL

데이터가 입력되지 않은 것은 null이다null을 선택 시 = 표시는 쓸 수 없고 is 를 써야 한다.잘못된 방법 = select ename , comm from emp where comm=null;올바른 방법 = select ename , comm from emp where comm is null;null이 아닌 것을 고를 때에는 is not null을 써야 한다.

select ename,comm from emp where comm is not null;

NVL(필드명,상수)=필드에 있는 null값을 상수로 출력한다.각종 계산식에서 null이 들어가면 계산이 안되기 때문에 유용하게 쓰인다select ename 이름, sal 급여,comm 보너스,sal+nvl(comm,0) 총지급액 from emp;

 

 

SPOOL

내가 작업한 모든것을 문서로 남기는 방법

spool d:/day1_1.txt

spool을적고 경로명과 만들 파일명을 적은후에 작업을 한다.그리고 마지막에 spool off;를 적으면 문서가 완성.

ORDER BY (정 렬)

Desc 내림차순Asc 오름차순

select * from emp order by ename desc;

select * from emp order by ename asc;

asc는 안적어도 자동 오름차순 적용정렬을 2개 하려고 할때= 콤마로 구분을 해서 정렬 조건을 나열한다.

select ename,job,sal from emp order by job,sal desc;

정렬 3개 할때.

select ename,job,sal from emp order by job,sal desc,ename desc;

별칭 붙이기

존재하지 않는 sal+comm. 등을 표시하고자 할때 별칭을 넣는게 편하다넣지 않아도 출력은 됨.select ename 이름, job 직업 from emp;이름 직업

---------- ---------

SMITH CLERK

ALLEN SALESMAN

 

 

select ename as 이름 from emp;이름

----------

SMITH

ALLEN

 

select ename "이 름" from emp;이 름

----------

SMITH

ALLEN

 

문자열 만들기

select ename||'의 직업은 '||job||'이고 입사일은 '||hiredate||'입니다' "문자열제목AA" from emp;문자열제목AA

---------------------------------------------------------

SMITH의 직업은 CLERK이고 입사일은 80/12/17입니다ALLEN의 직업은 SALESMAN이고 입사일은 81/02/20입니다WARD의 직업은 SALESMAN이고 입사일은 81/02/22입니다JONES의 직업은 MANAGER이고 입사일은 81/04/02입니다

 

 

SAVE

rollback; 사용시 commit하기 전의 내용을 제거한다savepoint aa; 세이브 포인트를 생성한다.rollback to aa; 세이브 포인트로 롤백을 한다

 

commit설정

set autocommit on;

set autocommit off;변경된 내용을 저장할려면

commit;

명령어를 써줘야 한다.Exit 를 사용하여 종료할 경우에도 저장이 된다.

 

데이터 타입

숫자형 데이터 타입numberField number 4byte의 정수를 저장할 수 있는 공간 확보Field는 필드명Field number(5) 5자리의 정수를 저장할 수 있는 공간확보Field number(10,2) 총 10자리 소수 이하 2자리 확보

 

문자 데이터 타입char <st2:sn w:st="on">고<st2:givenname w:st="on"></st2:givenname>정형</st2:sn> 문자 테이터 타입(최대 2000바이트)-대략 한글 1200자varchar2 가변형 문자 데이터 타입(최대 4000바이트)long 2기가까지 저장할 수 있는 데이터 타입

 

날자형 데이터date 날자 데이터를 저장할 수 있다.

 

논리형 데이터 타입boolean true, false 를 저장할 수 있다. (1바이트 확보)

 

이진 데이터 타입blob, clob 문자 데이터를 저장할 수 있다(최대 4기가 바이트 확보)primary key 식별자 (중복되면 안된다)

 

Table

Create table 테이블명(필드명1 데이터타입1 ,필드명2 데이터타입2 [not null | default | primary key | forerain key…],

);

create table emp1

Emp1이라는 테이블을 생성한다.create table emp1 as select * from emp;Emp테이블을 복사해서 Emp1테이블을 만들어라.

 

not null 테이블 생성시 특정필드에 조건으로 not null을 주게 된다면 추후에 자료 입력시그 필드값은 무조건 지정해 주어야 한다.

create table member(

num number(5) not null primary key ,

name varchar2(10) not null ,

tel varchar(15),

birth date default sysdate

);

default 테이블 생성시 특정 필드에 default값을 넣어놓는다나중에 데이터 입력시 그 필드값을 안적으면 미리 정해놓은 값이 들어간다.default sysdate 시스템의 현재 시간이 찍힌다.primary key 테이블 생성시 특정 필드에 primary key을 넣는다나중에 데이터 입력시에 그 필드값은 중복되어서는 안된다.(고유번호와 마찬가지)primary key 삭제

alter table member2

drop primary key;primary key가 있는 필드가 사라지는게 아니라 primary key를 넣어야 할 옵션이 사라진다.

 

desc emp4; 테이블의 구조를 보는 명령어이다

 

distinct - 선택한 필드의 중복 데이터를 제거하고 화면에 표시해준다

select distinct job from emp1;

Insert into 원하는 테이블에 데이터를 넣는다.모든 필드에 데이터를 추가할꺼면 필드명을 안 적어도 된다.insert into 테이블명 (필드명1, 필드명2, 필드명3,,,,,,) -- 원하는 필드명만 적으면 된다.values(데이터1, 데이터2, 데이터3,,,,,); -- 위에 적은 필드명에 대한 데이터.Update table set 테이블에 원하는 데이터를 업데이트(변경)한다.update 테이블명set 필드1=데이터1 ,필드2=데이터2,,,,,,,,[where 조건식] (생략가능)업데이트 할 조건이 2개 이상이면 콤마로 구분하면서 조건을 늘린다.Ex) update emp1 set job='큰형님' , mgr='0000' where ename='KING';Delete - 레코드 삭제delete from 테이블명 [where 조건식]drop - 테이블 삭제drop table emp2;modify - 필드의 속성 변경한다.

alter table emp4 modify(job varchar(20));

 

sequence

sequence 여러 개를 생성할 수 있다.primary key의 입력을 편하게 하기 위해 중복되지 않게 수를 뽑아주는 객체이다한번 생성했던 수는 두번 다시 생성하지 않는다. 한 객체를 생성해서 여러곳에서 사용이 가능하다. 여러곳에서 사용시 그 여러곳의 수도 서로중복되지 않는다.= sequence는 테이블에 소속된 것이 아니다.

create sequence memNum

여기까지 적으믄 memNum이라는 sequence생성 1에서 시작하고 1씩 증가한다

 

create sequence memNum

start with 10increment by 2;10에서 시작하고 2씩 증가한다.

 

sequence 객체 확인

select * from user_sequences;

 

sequence 사용nextVal 다음 숫자를 리턴 memNum.nextvalcurrVal 현재 숫자를 리턴 memNum.currval

 

sequence 삭제drop sequence memnum;

 

 

 

숫자 함수

avg - 평균 구하기

select avg(sal) from emp;

abs – 절대치를 구하는 함수 = 정수로 표현이 된다.select abs(-100) from dual;(dual은 가상의 테이블 명)ceil(n) 무조건 올림floor(n) 무조건 내림round(n,m) 반올림 m은 어디에서(몇 번째 자리에서) 반올림 할 것인지 표현m – 마이너스 이면 정수를 반올림 ex) round(1124.124, -2) = 1100m – 플러스 이면 소수점을 반올림 ex) round(1124.124, -2) = 1124.12mod(m,n) - 나머지 구하기 (m을 n이 나눈 나머지를 구함)select mod(10124,24) from dual; =10124/24 의 나머지를 구함.power(n,m) - 제곱 구하기 (n의 m제곱을 구한다)select power(4,5) from dual; 4의 5제곱인 1024를 구한다.sqrt(n) - 루트 구하기select sqrt(10) from dual; 10의 루트 값인 3.16227766 이 나온다.trunc(n,m) - 버림

select trunc(112.421, 2) test from dual;

112.42 를 반환한다(소수점 기준 왼쪽 2째 자리)

select trunc(112.421, -2) test from dual;

100을 반환한다 (소수점 기준 오른쪽 2째 자리)

 

 

--급여가 평균보다 작은 레코드 삭제

delete from emp1 where sal<(select avg(sal) from emp);

 

--emp1테이블의 데이터 중 급여를 9% 인상하여 정수 첫 번째 자리를 버린 값으로 수정하기

update emp1 set sal=trunc(sal*1.09,-1);

 

 

문자 함수

concat (char1, char2) - 두 문자를 합한다

select concat( 'oracle' , 'korea' ) from dual; ->oraclekorea

upper , lower , initcap - 대소문자 처리 함수lower : 소문자로 update emp1 set job=lower(job);upper : 대문자로initcap : 첫 번째 문자만 대문자lpad , rpad - 남는 자리 수를 특정한 문자로 채운다.

select lpad ( job,10,'*') from emp1;

select lpad ( rpad (job,10,'='),15,'=') from emp1;

substr (문자열,시작위치,갯수) 특정위치의 문자열 구하기갯수가 -값인 경우 마지막 문자에서 추출한다.

select substr(job,1,1) from emp1;

length 문자열의 길이를 구한다.

select length(ename) from emp1;

instr (문자열, 위치를 찾을 문자열);특정 문자열의 위치를 구한다. 찾는 문자가 없을 경우 0을 구한다.

select instr('say202n@naver.com','@') from dual;

replace(문자열, 원래 문자, 새로운 문자) 특정한문자열을 다른 문자열로 치환한다.

select replace(job,'e','\') from emp1;

trim 특정 문자열 제거좌측이나 우측에 있는 문자만 제거 가능하고 중간에 끼어있는 것은 안된다.

select trim(0 from 0000700080000) from dual;

ltrim , rtrim 좌우 문자열 제거

select ltrim('xyxyxXy','xy') from dual;

 

 

--본인 이메일 주소를 이용하여 아이디와 도메인을 구하여 출력하라

--say202n@naver.com

--아이디 도메인

--say202n naver.com

select job,

substr ( job , 1 , instr ( job , '@' )-1 ) //아이디,substr ( job , instr ( job , '@' )+1 , length(job) ) //도메인

from emp4;

 

날짜 함수

Sysdate – 시스템상의 현재 날짜,시간을 보여준다.

select sysdate from dual;

sysdate – 10 : 현재 날짜에서 10일을 뺀다.Sysdate + 10 : 현재 날짜에서 10일을 더한다.Sysdate 에서 가입시간 등을 빼는 것은 가능하지만 더하는 것은 안된다.last_day - 그달의 마지막 날

select last_day(sysdate) from dual;

add_months( ) - 월에 대한 더하기 연산

select add_months(sysdate,15) from dual;

yyyy/mm/dd hh : mm : ss 연도 월 일 시간 분 초 (연도만4자리)연도만 4자리로 표시

select to_char(hiredate,'yyyy') from emp1;

연도2자리, 월 2자리, 일2자리, 시간2자리 표시

select to_char(hiredate,'yy/mm/dd hh') from emp1;

 

round(date , ‘조건’ ) date를 조건을 기준으로 반올림한다.조건이 year이면 월을 반올림하여 그해 1월이 되거나 다음해 1월(연도에 영향미침)조건이 month이면 일을 반올림하여 다음달이거나 1일이 된다.조건이 day이면 요일을 계산해서 주를 반올림한다. (수요일이 기점이다).연도에 대한 반올림(월을 계산해서 연도를 반올림한다)

select round(sysdate,'year') from dual;

decode 함수

decode (바꿀필드, if1, then1, if2, then2,,,,,,, else) from 테이블;if1 는 원래 테이블에서 바꿀 조건then1 은 if1에 해당할 경우 바꾸어 표현할 값.Else 는 아무 조건에도 해당하지 않은 경우 표시할 값

 

deptno 10이면 'accounting',20이면 'research',30이면 'sales'40이면 'opreation;이라고 바꾸어 출력하라.이름 deptno출력Select eame, decode(deptno,10,'accounting',20,'research',30,'sales',40,'operation','aaaa') from emp1; --'aaaa'부분은 위에서else에 해당된다.

 

group by (그룹함수)

통계함수가 추가된 필드와 추가되지 않은 필드를 동시에 출력할 수 없다.(출력 줄 수가 다르기 때문)묶고싶은 그룹을 정하면 그 그룹에 대한 통계를 구할수 있다.

Select job , max(sal) , min(sal) from emp1 group by job;

(3개의 필드가 선택되어 한 개는 그룹으로 묶이고 나머지 2개는 통계를 낸다.)

JOB MAX(SAL) MIN(SAL)

--------- ---------- ----------

닭집주인

CLERK 1300 800

SALESMAN 1600 1250

PRESIDENT 5000 5000

max( ) 최대값 구하기

select max(sal) from emp1;

min( ) 최소값 구하기

select min(sal) from emp1;

안에 문자를 넣으면 아스키 코드값이 제일 작은것으로 구한다.avg( ) 평균 구하기

select avg(sal) from emp1;

sum( ) 합계 구하기

select sum(sal) from emp1;

having 절 - group by 절 의 조건식을 설정한다

select job, max(sal),min(sal) from emp1 group by job having avg(sal)>=3000;

rollup 오름차순으로 정렬을 해주고 통계 낸 결과에 대한 통계를 다시 한번 내준다.

select deptno, max(sal),min(sal),avg(sal),sum(sal) from emp1

group by rollup(deptno) having sum(sal)>=5000;

 

 

오라클 휴지통 비우기

 오라클 DBMS 를 이용 할때,drop table 등으로 발생한 휴지통 데이터를 정리 하는 기능..SHOW RECYCLEBIN; 휴지통 내용보기PURGE RECYCLEBIN; 휴지통 비우기PURGE TABLE 테이블명; 휴지통에서 특정 테이블 삭제PURGE INDEX 인덱스명; 휴지통에서 인덱스 완전삭제PURGE TABLESPACE USERS; 휴지통에서 특정 테이블의 전체 객체 삭제PURGE DBA_RECYCLEBIN; DBA권한으로 테이블 전체 삭제FLASHBACK TABLE 테이블명 TO BEFORE DROP; 테이블 복원하기DROP TABLE RECYCLETEST PURGE; 테이블 완전 삭제PURGE TABLESPACE USERS USER SCOTT; 휴지통에서 특정 테이블의 객체 삭제

 

 

Merge

merge into 테이블명(target table)using 테이블명(source table)on 조건식

when matched then

조건식에 만족할때 실행할 쿼리문

when not matched then

조건식에 만족하지 않을때 실행할 쿼리

 

 

merge into emp_testusing emp

on (emp.empno = emp_test.empno)

when matched then

update set emp_test.sal=emp_test.sal*1.2

when not matched then

insert values(emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm, emp.deptno);

 

위와 아래의 차이점은 테이블에 별칭을 부여하여 긴 테이블명을 다 적지 않고 간략하게 별칭을 통해서 테이블에 접근하였다.

 

merge into emp_test2 e2using emp e

on (e2.empno=e.empno)

when matched then

update set e2.sal=e2.sal*1.2

when not matched then

insert values (e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno);

 

 

Subquery

반드시 가로로 묶어야 하며 하나의 쿼리문에 다른 쿼리문이 중복된 것을 의미한다.

 

단일 행 서브쿼리한개의 값을 반환하는 쿼리문을 의미(가로안에서 반환되는 값이 한개여야 한다)단일행 연산자( =,>=,<=,<>,!=,>,< )만 사용가능

select empno,ename,job from emp

where job=(select job from emp where empno=7934);

 

ex) 사원번호가 7521인 사원과 업무(job)가 같고 급여가 7934인 사원보다 많은사원의 사원번호 , 이름, 업무, 입사일자, 급여를 출력하라

select empno,ename, job, hiredate,sal from emp

where job=(select job from emp where empno='7521')

and sal>(select sal from emp where empno='7934');

 

 

다중행 서버쿼리하나 이상의 행을 반환하는 서브쿼리로서 다중행 연산자는 하나 이상의 값을 요구한다.in , any, all, exists 다중행 서브쿼리 연산자

 

in

 연산자 사용하기 ( ( )안의 조건과 같은것을 출력한다)부서별로 급여를 가장많이 받는 사람의 정보를 출력하라.

select ename, sal,job from emp

where sal in(select max(sal) from emp group by job);

sal을 job으로 그룹지었을 때 그룹별 sal의 최고값과 같은것 들이 출력됨

 

any

 연산자 : 서브쿼리의 결과값중 어느 하나의 값이라도 만족이 되면 결과값을 반환한다.

select empno,ename,job,sal,deptno from emp

where sal> any(select min(sal) from emp group by deptno);

그룹으로 묶어서 최소값을 ( ) 안에서 구하고 그 그룹별로 최소값이 나오게 된다.그러면 부서등과 상관없이 ( )안에 숫자중 최소값보다 큰 sal을 지닌 레코드는 다 나온다.

 

all

 연산자. ()안의 모든 조건이 and로 묶인다.

select ename, sal,job from emp

where deptno !=20 and

sal > all(select sal from emp where job='SALESMAN')

deptno가 20이 아니고SALESMAN의 급여보다 큰 sal을 가진 사람만 출력하라.

 

exists

서브쿼리의 데이터가 존재하는 가의 여부를 먼져 따져존재하는 경우의 값들만을 결과로 반환사원을 관리할수 있는 사원의 정보를 보여라.

select empno, ename, sal

from emp e

where exists(select empno from emp where e.empno=mgr);

emp e테이블에 e.empno랑 emp mgr을 비교해서 같은값이 검색되는걸 반환.서로 다른테이블이면 굳이 별명을 안써도 된다.

 

다중열 서브쿼리업무별 (job) 최소급여를 받는 사원의 사번, 이름, 업무(job) 부서번호 (deptno)를 출력하라 단 업무별로 오름차순 정렬 사원번호 , 이름,업무(job), 급여, 부서번호를 출력.

select empno, ename, job,sal,deptno from emp

where (job, sal) in(select job,min(sal) from emp group by job)order by job

 

from 

절에 사용하는 서브쿼리

select ename,job from (select * from emp where sal>2000);

 

 

Join 

한 개 이상의 테이블에 같은 컬럼이 있을 경우에 조인이 가능하다Join절을 이용한 경우.select 테이블1.필드1, 테이블2.필드2....(겹치지 않는 컬럼은 소속관계를 표시안해도 된다)from 테이블1 join 테이블2on 조인할 컬럼에 대한 조건select를 이용한 경우select 테이블1.필드1, 테이블2.필드2....(겹치지 않는 컬럼은 소속관계를 표시안해도 된다)from 테이블1 , 테이블2where 조인할 컬럼에 대한 조건Ex) emp테이블의 이름, 직업, 급여, dept테이블에 있는 부서명을 선택하라select ename, job,sal,dname,e.deptno 겹치는 컬럼은 소속관계를 밝혀야 한다.

from emp e, dept d

where e.deptno=d.deptno and sal>=3000

 

Self join 하나의 테이블을 두개의 테이블인 것처럼 사용별명을 통해서 하나의 테이블을 가상으로 두개의 테이블 처럼 사용한다.

select e1.empno, e1.ename, e1.mgr, e2.empno, e2.ename

from emp e1 join emp e2

on e1.mgr=e2.empno;

 

out join

join을 쓰면 같은 것이 없을 때는 화면에 표시되지 않는다하지만 out join은 한쪽에 항목이 없을 때도 다른 쪽 테이블의 내용이 표시된다.

 

(+)를 이용

select ename, sal , dname from emp, dept

where emp.deptno(+)=dept.deptno;

모자라는 쪽에 (+) 표시를 넣어준다!right outer join & left outer join

select ename, sal , dname from emp right outer join dept

on emp.deptno=dept.deptno;

full outer join 을 적게 되면 양쪽 테이블의 레코드를 전부다 보여준다

select ename, sal , dname from emp full outer join dept

on emp.deptno=dept.deptno;

 

 

한 스키마 내에 모든 컬럼의 코맨트 출력select *from user_col_comments한 스키마 내에 모든 테이블의 코맨트 출력

 

select *from user_tab_comments

아즈라샤