오라클 강의(11강~13강)

11강

서브쿼리

-하나의 select 문장 내에 포함된 또 하나의 select 문장

-비교연산자의 오른쪽에 기술해야 하고, 반드시 괄호안에 넣어야 함

-메인쿼리가 실행되기 이전엔 한번만 실행됨

단일행 서브쿼리

-수행 결과가 오직 하나의 행만을 반환하는 것

-단일행 비교 연산자 : =, <>, >, >=, <, <=

다중행 서브 쿼리

-수행 결과가 두건 이상 출력되는 것

-다중행 연산자 : in, any or some, all, exists

in -> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과가 하나라도 일치하면 참

all -> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과가 모든 값이 일치하면 참

그래서 <all 최소값을 반환, >all 최대값을 반환

ex) select ename, sal from emp where sal > all(select sal from emp where deptno = 30);

30번 부서의 급여보다 모든 값이 큰 경우만 출력

단일행 서브쿼리로도 가능(max이용)

any -> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과와 하나 이상이 일치하면 참

ex) select ename, sal from emp where sal > any(select sal from emp where deptno = 30);

30번 부서의 급여의 최소값보다 크면 출력

단일행 서브쿼리로도 가능(min이용)

exists-> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과가 하나라도 일치하면 참

exists는 해당 행이 존재하는지의 여부만 확인

in은 실제 존재하는 데이터들의 모든값까지 확인

exists를 쓸때는 메인쿼리와 서브쿼리의 테이블간의 이퀄조인을 써야 한다는 것을 알고 있어야 한다

ex) select ename, deptno, sal from emp e where exists(select * from dept d where d.deptno in (10,20) and e.deptno 

= d.deptno);

ex) select ename, deptno, sal from emp e where exists(select * from dept d where d.deptno =40 and e.deptno = 

d.deptno);

12강

DDL(생성, 변경, 삭제)

create table 할때 사용할 수 있는 데이터형

char(size) - 고정길이 문자 데이터 / varchar2와 동일한 형태의 자료를 저장할 수 있고, 입력된 자료의 길이와는 상관없

이 정해진 길이만큼 저장 영역을 차지함 / 최소크기 1

varchar2(size) - 가변 길이 문자 데이터 / 실제 입력된 문자열의 길이만큼 저장 영역을 차지 / 최대 크기는 명시해야 하

며 최소 크기는 1

number - internal number format 최고 40자리까지의 숫자를 저장할 수 있음 / 이때 소수점이나 부호는 길이에 포함되지 

않음

number(w) - w자리까지의 수치로 최대 38자리까지 가능함(38자리가 유효숫자)

number(w,d) - w는 전체길이, d는 소수점 이하 자릿수 / 소수점은 자릿수에 포함되지 않음

date - BC 4712년 1월 1일~ AD 4712년 12월 31일까지의 날짜

long - 가변 길이의 문자형 데이터 타입 최대 2GB

lob - 2GB까지의 가변길이 바이너리 데이터를 저장시킬 수 있음, 이미지 문서, 실행파일을 저장할수 있음

rowid - tree-piece format을 가짐, DB에 저장되어 있지 않으며, DB data도 아님

bfile - 대용량의 바이너리데이터를 파일 형태로 저장함 최대 4GB

timestamp(n) - date형의 확장된 형태

interval year to month - 년과 월을 이용하여 기간을 저장

interval day to second - 일,시,분,초 를 이용하여 기간을 저장 / 두 날짜값의 정확한 차이를 표현

식별자 

-반드시 문자로 시작

-1~30자까지 가능

-A-Z까지의 대소문자, 0-9까지의 숫자, 특수 기호는 (_, $, #)만 포함가능

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

-공백 허용을 안함

DESC로 테이블 구조 파악

서브쿼리로 테이블 생성하기(테이블의 구조와 내용을 복사)

동일한 구조와 내용을 갖는 테이블을 생성할 수 있음

ex) create table emp02 as select * from emp;

일부의 구조와 내용을 갖는 테이블을 생성할 수 있음

ex) create table emp03 as select empno, ename from emp;

기존 테이블의 일부의행만 복사

ex) create table emp04 as select * from emp where deptno =10;

서브쿼리로 테이블 생성하기(테이블의 구조만 복사) 

-위의 서브쿼리를 where절에 항상 거짓이 되는 조건을 지정하면 됨

ex)create table emp05 as select * from emp where 1=0;

***********제약조건은 복사되지 않음(서브쿼리로 테이블 생성시)

가상 컬럼을 가지는 테이블 생성

create table vt001

(no1 number, no2 number, no3 number generated always as (no1+no2) virtual);

no3는 no1과 no2의 더한값이 저장됨

no1과 no2만 insert할수 있고 해당 컬럼의 값이 변경되면 no3값도 자동으로 변경됨

테이블 구조 변경하는 alter table

-add column절

ex)alter table emp01 add(job varchar(9));

새로운 컬럼은 테이블 맨 마지막에 추가됨

이미 이전에 추가해 놓은 행이 존재한다면, 그 행에도 컬럼이 추가되지만 컬럼값은 null값으로 입력됨

-modify column절

ex)alter table emp01 modify(job varchar(30));

해당 컬럼에 자료가 없을 경우는

데이터 타입과 컬럼의 크기를 변경할 수 있음

해당 컬럼의 자료가 있을 경우는 

데이터 타입 변경할수 없고 크기는 늘릴수는 있지만 현재 가지고 있는 데이터 크기보다 작은 크기로는 변경할 수 없음

***데이터가 존재하는데도 데이터의 타입을 변경할 수 있는 경우는 오직 char와 varchar2사이의 타입을 변경하는 경우^^(

같거나 크게만 변경)

-drop column절

ex) alter tabe emp01 drop column job; 

set unused 옵션 사용하기

-컬럼을 삭제하지 않지만, 컬럼의 사용을 논리적으로 제한할 수 있음

-테이블에 저장된 내용이 많을 경우 해당 테이블에서 컬럼을 삭제하려면 꽤 오랜시간 소용됨

-그 시간동안 lock 걸려서 다른 작업을 할 수 없음 -> 이때 set unused 옵션 사용(락 걸리지 않음)

ex) alter table emp02 set unused(job);

사용빈도가 가장 낮은 시간에 삭제 작업을 진행

ex)alter table emp02 drop unused columns;

drop table

기존 테이블의 존재를 제거(구조와 내용을 함께)

테이블의 모든 로우를 제거하는 truncate table

내용만 제거함 / 구조는 남아있음

테이블명 변경하는 rename

ex) rename emp05 to test;

delete과 truncate와 drop의 차이점

delete는 테이블 용량은 줄어들지 않음 / 내용만 지워짐 / 원하는 데이터만 골라서 삭제 / 되돌릴수 있음

truncate 최초 테이블이 만들어진 상태로 만듦 / 용량 줄어들고 index모두 지워짐 / 데이터만 삭제 / 되돌릴 수 없음

drop 는 공간/ 객체(index, 제약조건등) / 내용과 구조 삭제 / 되돌릴수 없음

데이터 딕셔너리와 데이터 딕셔너리 뷰

데이터 딕셔너리 : 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블

-테이블을 생성하거나 변경하는 때에 자동으로 갱신되는 테이블

-사용자가 직접 수정삭제 할 수 없음

데이터 딕셔너리 뷰:데이터 딕셔너리의 내용을 사용자가 이해할 수 있는 내용으로 변환하여 제공

-user_데이터 딕셔너리 뷰(user_tables 데이터 딕셔너리 뷰, …..)

자신의 계정이 소유한 객체 등에 관한 정보를 조회

ex) desc user_tables;

ex) select table_name from user_tables order by table_name desc;

-all_데이터 딕셔너리 뷰

자신 계정 소유 또는 권한을 부여 받는 객체 등에 관한 정보를 조회

ex) desc all_tables;

ex) select owner, table_anme from all_tables;

-dba_데이터 딕셔너리 뷰

데이터 베이스 관리자만 접근 가능한 객체 등의 정보를 조회(DB의 모든 객체 접근 가능)

시스템 권한 가진 사람만 접근가능

ex)select owner, table_name from dba_tables;

13강

insert

ex)insert into dept03 (컬럼명들) values (값들);

ex)insert into dept03 select * from dept;

insert할때 컬럼명을 생략가능하기고 가능함

다중 테이블에 다중행 입력

insert all 서브쿼리의 결과를 조건없이 여러테이블에 입력 가능(서브쿼리의 컬럼명과 테이블의 컬럼명이 동일해야 함)

ex)insert all 

into emp_hir values(empno, ename, hiredate)

into emp_mgr values(empno, ename, mgr)

select empno, ename, hiredate, mgr  from emp where deptno = 20;

ex)insert all

when hiredate>’1982/01/01’ then into emp_hir01 values(empno, ename, hiredate)

when sal >= 2000 then into emp_sal values(empno, ename, sal)

select empno, ename, hiredate, sal from emp;

update

update문에 where절을 주지않으면 모든행의 값이 변경되므로 주의하기

ex) update emp01 set deptno =40 where deptno = 10;

ex) update dept01 set loc=(select loc from dept01 where deptno=40) where deptno =20;

delete

delete문에 where절을 주지않으면 모든행이 삭제되므로 주의하기

ex) delete from emp01 where deptno =30;

ex) delete from emp01 where deptno = (select deptno from dept where dname=’sales’);

테이블을 합병하는 merge 문

구조가 같은 두개의 테이블을 하나의 테이블로 합치는 기능

기존 테이블에 자료가 존재하는 경우 - 새로운 값으로 갱신(update)

기존 테이블에 자료가 존재하지 않는 경우 - 새로운 행으로 추가(insert)

ex) merge into emp01

using emp02

on(emp01.empno = emp02.empno)

when matched then

update set

emp01.ename=emp02.ename

emp01.job=emp02.job

emp01.mgr = emp02.mgr

emp01.hiredate = emp02.hiredate

when not matched then

insert values(emp02.empno, emp02.ename, emp02.job, emp02.mgr, emp02.hiredate);