Oracle 기초1

oracle 명령어 select문

Q) scott 사용자가 관리하는 목록

sql> select * from tab;

Q) 특정 테이블 구조 확인(필드리스트, 데이터 형식, 제약 조건)

sql> desc table명

Q) 특정 테이블의 data 표시

sql> select * from 테이블명;

Q) 모든 컬럼(필드명)말고, 필요한 컬럼만 출력

sql> select empno,ename,job from emp;

Q) 각각의 필드명에 별칭을 주어서 출력 ("대소문자 구분 가능")

sql> select deptno (as) "DePtNuM" from dept;

sql> select deptno (as) "DePtNuM", dname from dept;

Q) 사원들의 직업명(job)을 중복 제거 후 출력

sql> select distinct job from emp;

Q) 급여가 3천이상인 사원의 정보 출력

sql> select empno, ename, sal from emp

sql> where sal >= 3000;

Q) 이름 scott인 사원 출력

sql> select * from emp

sel> where ename = 'SCOTT';   <- 대소문자 구분

Q) 1985년도 이후 입사원 사원들 정보

sql> select empno, ename, sal from emp

sql> where hiredate >= '1985/01/01';

Q) 부서번호가 10 그리고 직업이 매니저인 사람

sql> select empno, ename, deptno, job from emp

sql> where deptno = 10 and job = 'MANAGER';

Q) 부서번호가 10 이거나 직업이 매니저인 사람

sql> select empno, ename, deptno, job from emp

sql> where deptno = 10 or job = 'MANAGER';

Q) 부서번호가 10이 아닌 사원 출력

sql> select  deptno, ename from emp 

sql> where not (deptno = 10);  //where deptno <> = 10;   //where deptno != 10;

Q) 급여가 1000~3000$ 사이인 사원 출력

sql> select ename, sal

sql> from emp

sql> where sal >= 1000 and sal <= 3000;

sql>

select ename, sal

from emp

where sal between 1000 and 3000;

Q) 급여가 1300 또는 1500 또는 1600인 사원

sql>

select ename, sal

from emp

where sal = 1300 or sal = 1500 or 1600;

sql>

select ename, sal

from emp

where sal in (1300,1500,1600);

Q) 이름이 'k'로 시작되는 사원 출력( ' ' <-대소문자 구분)

sql>

select empno, ename

from emp

where ename like 'K%';

Q) 이름이 'k'로 끝나는 사원 출력( ' ' <-대소문자 구분)

sql>

select empno, ename

from emp

where ename like '%K'

Q) 이름에 'k'가 들어가는 사원 출력( ' ' <-대소문자 구분)

sql>

select empno, ename

from emp

where ename like '%K%'

Q) 두번째 자리에 'A'가 들어가는 사원 출력

sql>

select empno, ename

from emp

where ename like '_A%'; ( ' _ ' 언더바 는 반드시 한글자는 포함해야함)

Q) 커미션을 받지않은 사원 출력

sql>

select ename, comm

from emp

where comm is null;

Q) 커미션을 받는 사원

sql>

select ename, comm

from emp

where comm is not null;

AND ROWNUM = 1

Q) 사번의 정렬(오름차순)

sql>

select empno, ename

from emp

order by empno asc; <-default

Q) 사번의 정렬(내림차순)

sql>

select empno, ename

from emp

order by empno desc;

Q) 사원의 연봉계산 출력

sql>

select ename, sal, sal*12

from emp;

Q) 커미션을 포함한 연봉출력

sql>

select ename, sal, comm, sal*12 sal*12+comm

from emp;

Q) ↑오류해결

sql>

select ename, sal, comm, sal*12, sal*12+comm, nvl(comm, 0), sal*12+nvl(comm, 0)

from emp;

Q) ↑별칭부여

sql>

select ename, sal, comm, sal*12, sal*12+comm 연봉1, nvl(comm, 0), sal*12+nvl(comm, 0) 연봉2

from emp;

==================================================================

오라클 명령어 group함수

합계: sum()

sql>

select sum(sal) from emp;

카운트: count()

sql>

select count(*) from emp;

평균: avg()

sql>

select avg(sal) from emp;

최댓값: max()

sql>

select max(sal) from emp;

최소값: min()

select min(sal) from emp;

Group by 절: 직업별 급여 평균

sql>

select job, avg(sal) 

from emp

Group by job;

Having 절: 직업별 급여평균(단, 급여 평균이 2000이상 출력)

sql>

select job, avg(sal) 

from emp

Group by job

 having avg(sal) >= 2000;

================

임시 데이터 출력

sql>

select 1234*1234 from dual;

반올림: round()

sql>

select demp, ename, round(sal, -3)

from emp

where detpno = 30;

sql> 

select round(12.345678, 3) 양수는 소수점에서 오른쪽

from dual;

lower(): 모든 문자를 소문자로 변환

sql>

select lower('AbCdEfG') as 소문자

from dual;

upper(): 모든 문자를 대문자로 변환

sql>

select upper('AbCdEfG') as 대문자

from dual;

initcap(): 첫글자 대문자로 변환

sql>

select initcap('abCdEfG') as 첫글자

from dual;

concat(): 문자열 연결

sql>

select concat('춥고 ', '잠온다') from dual;

length('글자 수'), lengthb('바이트 수'): 문자열 길이 반환

sql>

select length('아아아아아아아'), length('Aaaaaaaaaa'), lengthb('아아아아아아아'), lengthb('Aaaaaaaaaa')

from dual;  

substr(), substb(): 문자열 추출

sql>

select substr('아이우에오', 2, 5) from dual;

instr(): 문자열 시작 자리

sql>

select instr('아이우에오', '우') from dual;

lpad(), rpad(): 자리 채우기

sql>left, right

select lpad('oracle', 20, '#') from dual;

select rpad('oracle', 20, '#') from dual;

trim(): 컬럼이나 대상 문자열에서 특정 문자가 첫번째 글자이거나

마지막 글자이면 잘라내고 남은 문자열만 반환

sql>

select trim('a' from 'aaaOracleaaaaa') from dual;

select trim(' ' from ' Oracle ') from dual;

abs(): 절대값

sql>

select abs(-10) from dual;

floor(): 소수자리 버리기

sql>

select floor(12.123456) from dual;

trunc(): 소수 자리 수 자르기

sql>

select trunc(12.123456, 3) from dual;

mod(): 나머지

sql>

select mod(3, 5) from dual;

Q) 사번이 홀수인 사람들 검색

sql>

select empno, ename

from emp

where mod(empno, 2) = 1;

============================================

오라클 날짜관련 함수

sysdate : 시스템 날짜

sql>

select sysdate from dual;

// 날짜 포멧 출력변경

sql>

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

months_between(): 개월 수 구하기

sql>

select ename, months_between(sysdate, hiredate)   //현재시간 입사일 차

from emp

where deptno = 10;

add_months(): 개월 수 더하기

sql>

select add_months(sysdate, 4) from dual;

next_day(): 다가 올 요일에 해당하는 날짜 보여줌

sql>

select next_day(sysdate, '일요일') from dual;

last_day(): 해당 달의 마지막 일 수 반환

sql>

select last_day(sysdate) from dual;

select last_day(add_months(sysdate, 1)) from dual;

to_char() : 날짜 -> 문자 / 숫자 -> 문자 로 반환

sql>(문자열로 반환)

select to_char(sysdate, 'yyyy-mm-dd') from dual;

to_date() : 날짜형으로 반환

sql>

select to_date('2009/12/31', 'yyyy/mm/dd') from dual;

nvl(): null인 데이터를 다른 데이터로 변경

sql>

select ename, nvl(comm, 0) from emp; 

decode() : Java switch()문과 같은 기능

sql>//부서번호가 10번이면 ACCOUNT 출력

select ename, deptno, decode(deptno,

 10, 'ACCOUNT',

 20, 'RESEARCH',

 30, 'SALES',

 40, 'OPERATIONS') as "부서번호"  

from emp;

case() : Java에서 if() else if() else

sql>

select ename, deptno, 

case 

when deptno = 10 then 'ACCOUNT'

when deptno = 20 then 'RESEARCH'

when deptno = 30 then 'SALES'

when deptno = 40 then 'OPERATIONS'

end as "부서명"

from emp;

==================================================================

==================================================================

* 오라클 자료형 타입

1. 정수형 타입 : number(2)는 총자리수가 2자리인 정수형 값이 필드에 저장된다.

2. 실수형 타입 : number(6, 2)는 소수점을 포함한 총자리수가 6자리이고,

                       소수점 둘째자리까지 있는 실수형 값이 저장된다.

                       첫번째 인자값 6은 소수점을 포함한 총 자리수를 의미하고,

                       두번째 인자값 2는 소수점 자리수를 의미한다.

3. 가변형 문자열 : varchar2가 가변형 문자열 자료형이다.

                          입력 데이터가 실제 크기를 넘어서면 넘어선 크기만큼 자료형 크기가

                          늘어나지 않는다. 만약에 varchar2(10)으로 만들어진 필드에는

                          10자리까지만 저장된다. 영문자로 5자의 레코드를 삽입한다면

                          varchar2은 크기가 실제 레코드 크기가 5인 만큼 줄어들어 저장된다.

                          그러므로 기억장소 절약의 장점이 있다.

4. 고정형 문자열 : char 자료형을 사용.

                          char(10)으로 만들어진 필드에 영문자 5크기 만큼 레코드를 삽입한다면

                          char 자료형은 고정형이기 때문에 크기가 실제 레코드만큼(10) 줄어들지 않고

                          고정형으로 할당이 된다.

* 테이블명, 컬럼명 명명 규칙

1. 반드시 문자로 시작해야 됨.

2. 1~30자까지 가능함.

3. A~Z까지의 대소문자와 0~9까지의 숫자 조합.

    특수 기호는 (_, $, #)만 포함 할 수 있음.

4. 오라클에서 사용되는 예약어나 다른 객체명과 중복 불가.

5. 공백 허용 안됨.

   cf) 오라클의 객체 : 테이블, 뷰, 시퀀스, 인덱스

==================================================================

==================================================================

오라클 SQL문 : 테이블 생성/수정/삭제

테이블 생성 : create문

sql>

create table exam01(

exno number(2),

exname varchar2(20),

exsal number(7, 2)  7자리 소수점2자리

);

기존 테이블괴 동일한 테이블 생성

SQL>

create table exam02

as

select * from emp;

기존 테이블 새 칼럼 추가 / 수정 : alter문

sql>칼럼추가

alter table exam01

add(

exjob varchar2(10)

);

테이블 구조 수정: 필드 수정

sql>

alter table exam01

modify(

exjob varchar2(20)

);

테이블 구조 수정: 필드 삭제

sql>

alter table exam01 drop column exjob;

테이블 삭제

sql>

drop table exam02;

휴지통 비우기

purge recyclebin;

//오라클10g 버전 이상부터는 테이블 삭제시 임시테이블 생김

테이블 완전 삭제

sql>

drop table exam02 purge;

테이블 이름 변경

sql>

alter table exam01 rename to test01;

or

rename test01 to exam01;

테이블내 모든 레코드 삭제

sql>delete from exam02 + create table exam02 (테이블 명만잇음)

truncate table exam02;

=======================================================

=======================================================

=======================================================

오라클 SQL문 : 데이터 입력 / 출력(select) / 수정 / 삭제

샘플 테이블 생성

sql>

create table test01(

deptno number(2),

dname varchar2(14),

loc varchar2(14)

);

데이터 입력 : insert into ~ values 문

sql>

insert into test01(deptno, dname, loc)

values(10, 'ACCOUNTING', 'NEW YORK');

insert into test01(deptno, dname, loc)

values(20, 'RESEARCH', 'DALLAS');

데이터 입력 : insert into ~ values 문(행 생략)

sql>

insert into test01

values(30, 'SALES', 'CHICAGO');

insert into test01

values(40, 'OPERATIONS', 'BOSTON');

-NULL 값 입력

insert into test01

values(50, 'DEVELOPER', NULL);

=========================================================

데이터 수정 : 부서번호 변경(필드의 데이터를 변경)

sql>

update test01

set deptno = 60;

Q)--급여 10% 인상

sql>

update exam02

set sal = sal * 1.1;

Q) 부서번호가 10인 사원의 부서번호를 40으로 변경

sql>

update exam02

set deptno = 40

where deptno = 10;

Q) 급여가 3000이상인 사원만 급여를 10% 인상

sql>

update exam02

set sal = sal * 1.1

where sal >= 3000;

Q) 이름이 scott인 사원인 사람의 부서번호를 40, 직급을 MANAGER로 변경

sql>

update exam02

set deptno = 40, job = 'MANAGER'

where ename = 'SCOTT';

전체 데이터 삭제

sql>

delete from 테이블명;

Q) 30번 부서의 사원을 삭제

sql>데이터만 삭제

delete from exam02

where deptno = 30;

===========================================

@@트랜잭션 transaction : 전 상태로 되돌리기 또는 완전 수정/ 삭제

<ex. ATM 돈 출금시 

rollback; - 통장은 까이고 기계가 맛가서 안 주는것을 방지 

commit; - 둘다 정상이면 저장>

===========================================

오라클 SQL구문 : 조인(join)

Q) 원하는 정보가 두 개 이상의 테이블에 나뉘어져 있을때 결과 출력

join 

1. cross join : 2개 이상의 테이블을 단순 연결.

SQL>

select * from emp, dept;

2. equi join

sql>

select * from emp, dept

where emp.deptno = dept.deptno;

sql>

select ename, dname, emp.deptno

from emp, dept

where emp.deptno = dept.deptno

and ename = 'SCOTT';

sql>

select e.ename, d.dname, e.deptno

from emp e, dept d

where e.deptno = d.deptno

and e.ename = 'SCOTT';

3. non-equi join : 조인 조건에 특정범위 내에 있는지를 조사하기 위해서

조건절에 조인 조건을 " = 연산자 이외의" 비교연산자를 이용.

sql>

select ename, sal, grade 

from emp, salgrade

where sal between losal and hisal;

select ename, sal, grade 

from emp, salgrade

where sal >= losal and sal <= hisal;

sql>

select ename, sal, grade, dname

from emp, salgrade, dept

where emp.deptno = dept.deptno

and sal >= losal and sal <= hisal;

4. self join : 하나의 테이블내에서 조인을 해야 원하는 자료를 얻는 join방법

sql>

select ename, mgr, from emp;

sql>

select empno, ename from emp;

sql>

select employee.ename, employee.mgr, manager.ename

from emp employee, emp manager

where employee.mgr = manager.empno;

5. outer join

sql>

select employee.ename, manager.ename

from emp employee, emp manager

where employee.mgr = manager.empno(+);

==========================================================

==========================================================

모든 DBMS에서 사용 가능

@@ANSI join

// 1. ANSI cross join 

sql>

select * from emp cross join dept;

// 2. ANSI Inner join

sql>

select ename, dname

from emp inner join dept

on emp.deptno = dept.deptno;

Q)

select ename, dname

from emp inner join dept

on emp.deptno = dept.deptno

where ename = 'SCOTT';

+sql>

select ename, dname

from emp inner join dept

using (deptno);

+sql>

select ename, dname

from emp natural join dept;

//emp, dept 뒤져서 공통되는 컬럼 찾음 = Inner join

// 3. ASNI outer join

연습용 테이블 생성

create table dept01(

deptno number(2),

dname varchar2(14)

)

;

insert into dept01 values(10, 'ACCOUNTING');

insert into dept01 values(20, 'RESEARCH');

create table dept02(

deptno number(2),

dname varchar2(14)

)

;

insert into dept02 values(10, 'ACCOUNTING');

insert into dept02 values(30, 'SALES');

//oracle outer join

sql>

select *

from dept01, dept02

where dept01.deptno = dept02.deptno(+);  

ANSI 권고

sql> // left Table all show

select *

from dept01 left outer join dept02

on dept01.deptno = dept02.deptno;

sql> // right Table all show

select *

from dept01 right outer join dept02

on dept01.deptno = dept02.deptno;

sql> // all Table show

select *

from dept01 full outer join dept02

on dept01.deptno = dept02.deptno;

==============================================

==============================================

오라클 SQL 구문 : 서브쿼리

- 서브쿼리는 하나의 select 문장의 절안에 포함된 또 하나의 select문이다.

- 메인 쿼리/ 서브 쿼리

- 서브쿼리는 비교연산사자의 오른쪽에 기술해야 되고, 반드시 괄호로 감싸주어야한다.

- 서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행 된다.

- 단일행 서브쿼리 / 다중행 서브쿼리

Q) SCOTT 사원의 근무 부서 찾기

sql>

select dname

from dept

where deptno = (

select deptno

from emp

where ename = 'SCOTT'

)

;

Q) SCOTT와 동일한 직급(job)을 가진 사원을 출력

단일행 서브쿼리

sql> 

select ename, job

from emp

where job = (

select job

from emp

where ename = 'SCOTT'

);

@@서브쿼리 & 그룹함수

Q) 평균 급여보다 더많은 급여를 받는 사원 출력

sql>

select ename, sal

from emp

where sal > (

select avg(sal)

from emp

);

Q) 급여를 3000$ 이상받는 사원이 소속된 부서와 동일한 부서에 근무하는 사원들의 정보 출력

다중행 서브쿼리

-- in 연산자 : 메인쿼리의 비교조건('=' 연산자로 비교할 경우)이 서브쿼리의

결과중에서 하나라도 일치하면 참이다.

sql>

select ename, sal, deptno

from emp

where deptno in (

select deptno

from emp

where sal >= 3000

);

Q) in 연산자를 이용해서 부서별로 가장 급여를 많이 받은 사원의 사원번호, 사원명, 급여, 부서번호 출력

sql>

select empno, ename, sal, deptno

from emp

where sal in (

select max(sal)

from emp

group by deptno

);

+--[cf.] 

sql>

select empno, ename, sal, deptno

from emp

where sal >= any(

select max(sal)

from emp

group by deptno

); 

-- all 연산자 : 메인쿼리의 비교 조건이 서브쿼리의 검색결과와 모든 값이 일치하면 참이다.

- 찾아진 값에 대해서 and 연산을 해서 모두 참이면 참이된다.

- > all은 '모든 비교값보다 크냐'고 묻는 것이 되므로 최대값보다 크면 참이 된다.

Q) 30번 부서 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과

급여 출력

sql>

select max(sal)

from emp

where deptno = 30;

-- 단일 서브쿼리 & 그룹함수

sql>

select ename, sal

from emp

where sal > (

select max(sal)

from emp

where deptno = 30

);

-- 다중 서브쿼리

sql>

select ename, sal

from emp

where sal > all(

select sal

from emp

where deptno = 30

);

-- any 연산자 

- any 조건은 메인 쿼리의 비교조건이 서브쿼리의 검색결과와 하나 이상만

일치하면 참이다.

- > any 는 찾아진 값에 대해서 하나라도 크면 참이 된다.

그러므로 찾아진 값 중에서 가장 작은값, 최소값보다 크면 참이 된다.

Q) 30번 부서 사원들의 급여 중에서 가장 낮은 급여를 받는 사람보다 

높은 급여를 받는 사원의 이름, 급여를 출력

<단일 서브쿼리>

sql>

select ename, sal

from emp

where sal > (

select min(sal)       <- 그룹함수

from emp

where deptno = 30

);

<다중 서브쿼리>

select ename, sal

from emp

where sal > any(

select sal

from emp

where deptno = 30

);

======================================================