객체, 시퀀스, 조인, 뷰의 이해-2009/02/17

-- 오라클 객체
-- 1. table
-- 데이터를 저장하는 객체
-- 2. index
-- 데이터 검색속도 증가를 위한 객체
-- 3. view
-- 테이블(들)의 데이터를 보호할 목적으로 사용되는 객체
-- 4. sequence
-- 연속적인 일련번호 생성을 위한 객체
-- 5. synonym
-- 객체의 이름을 간략하게 하기위한 객체

-- Data Dictionary (데이터사전)
-- 데이터에 대한 데이터(MetaData)를 조회할 목적의 뷰들
-- dictionary 조회
select * from dictionary;
-- 사용자의 테이블들
select * from user_tables;
-- 사용자의 인덱스들
select * from user_indexes;
-- 사용자의 뷰들
select * from user_views;
-- 사용자의 제약조건들
select * from user_constraints;

-- 테이블 생성 기본문법
create table dept1 (
deptno number(2),
dname varchar2(13),
loc varchar2(14)
);
-- 테이블 복사
create table dept2
as
select * from dept1;
-- 테이블 목록
select * from tab;
-- 테이블의 컬럼 추가
alter table dept1 add (bigo varchar2(15));
desc dept1;
-- 테이블의 컬럼 변경
alter table dept1 modify (bigo varchar2(30));
-- 테이블의 데이터 삭제 (delete + commit)
truncate table dept1;
-- 테이블명 변경
rename dept2 to dept3;
select * from tab;
-- 테이블 제거
drop table dept3;
-- 주석 달기
comment on table dept1 is '부서정보 테이블입니다!';
select * from user_tab_comments where table_name='DEPT1';
comment on column dept1.deptno is '부서코드';
select * from user_col_comments where table_name='DEPT1';

-- 제약조건 (constraints)
-- 데이터 무결성(integrity)을 지킬 목적으로 컬럼레벨이나 테이블레벨에
-- 부여하는 제약 사항

-- 제약조건들
-- 1. primary key : not null + unique
-- 2. foreign key : 다른테이블의 primary key컬럼의 값을 참조하는 컬럼
-- 3. unique : 컬럼의 값이 유일, 단 null은 여러개 입력될 수 있음
select 1 from dual where null=null;
select 1 from dual where null is null;
-- 4. not null : 컬럼에 값이 null이 입력될 수 없음
-- 5. check : 컬럼에 입력되는 값에 대해 체크
-- 6. default : 컬럼에 입력되는 기본값

-- 제약조건의 특징
-- 사용자에 의해 발생한 잘못된 DML문을 수행되지 않도록 할 목적
-- 제약조건에 대한 모든 정보는 dictionary에 저장된다.
-- 제약조건을 활성화(enable), 비활성화(disable)할 수 있다.
-- 하나의 컬럼에 여러 제약조건을 부여할 수 있다.
-- 여러개의 컬럼에 하나의 제약조건을 부여할 수 있다.
-- 제약조건은 오라클 서버가 관리한다.

-- 제약조건의 유형
-- 1. column level constraints (컬럼레벨제약)
-- 하나의 컬럼에 제약 조건을 정의할 때 사용
-- create, alter 문장으로 컬럼을 정의할 때 데이터 타입 뒤에 정의
-- 위에 있는 모든 제약조건을 컬럼레벨로 모두 부여할 수 있음
-- 2. table level constraints (테이블레벨제약)
-- 하나의 컬럼에 여러개의 제약조건을 부여할때 주로 사용
-- not null제약조건은 table level로 부여할 수 없음

create table dept9 (
deptno number(2) constraint dept9_deptno_pk primary key,
dname varchar2(15),
loc varchar2(1));

drop table dept9;

-- 제약조건명 검색
select constraint_name from user_constraints;
-- 제약조건 추가
alter table dept9 add (constraint dept9_deptno_nn primary key(deptno));

create table dept10 (
deptno number(2) constraint dept10_deptno_pk primary key,
dname varchar2(15),
loc varchar2(1));

create table emp10 (
empno number(4) constraint emp10_empno_pk primary key,
ename varchar2(15),
deptno number(2) constraint emp10_deptno_fk references dept10(deptno));

create table emp11 (
empno number(4) constraint emp11_empno_pk primary key,
ename varchar2(15) constraint emp11_ename_uq unique,
deptno number(2)
);

alter table emp11 add (constraint emp11_deptno_fk foreign key(deptno)
references dept10(deptno));

alter table emp11 add (constraint emp11_deptno_uq unique(deptno));

create table dept12 (
deptno number(2),
dname varchar2(15),
loc char(1) constraint dept12_loc_ck check (loc in ('1','2'))
);

alter table dept12 add (constraint dept12_loc_ck2 check (loc in ('1','2')));

create table dept13 (
deptno number(2),
dname varchar2(15) not null,
loc char(1)
);

select * from user_constraints;

-- 제약조건 삭제
alter table dept12 drop constraint dept12_loc_ck2;
-- 제약조건 비활성화
alter table dept12 modify constraint dept12_loc_ck disable;
-- 제약조건 활성화
alter table dept12 modify constraint dept12_loc_ck enable;

---------------------------------------------------------
-- index
---------------------------------------------------------
--인덱스는 테이블 검색속도를 향상시키기 위한 객체
--주로 B*Tree인덱스를 사용
--- 인덱스를 사용해야 할 때
--1. where절에 자주 등장하는 컬럼
--2. 검색결과가 전체 행의 10%미만인 경우
--- 인덱스를 사용하지 말아야 할 때
--1. 데이터가 적은 (보통 행이 몇천개 이하) 경우
--2. where절에 자주 등장하지 않는 컬럼
--3. 테이블이 자주 수정,삽입,삭제될때
--- 인덱스의 종류
--1. single index : 단일컬럼으로 만들어지는 인덱스
--create index i_emp_ename on emp(ename)
--2. concanated index : 복합컬럼(두개 이상의 컬럼)으로 만들어지는 인덱스
--create index i_emp_empno_ename on emp(empno, ename)
--3. unique index : 인덱스를 생성할 컬럼의 값이 중복되지 않는 경우
--create unique index i_emp_empno on emp(empno)
--4. non-unique index : 인덱스를 생성할 컬럼의 값이 중복되는 경우
--create index i_emp_ename on emp(ename)
--5. function-based index : 컬럼들의 연산결과를 토대로 만든 인덱스
--create index i_emp_sal_comm on emp(sal-comm)

-- 인덱스 생성
create index i_emp_ename on emp(ename);
create unique index i_emp_empno on emp(empno);

-- 인덱스 확인
select * from user_indexes;

-- 인덱스 제거
drop index i_emp_ename;

---------------------------------------------------
-- view
---------------------------------------------------
-- 테이블 데이터를 보호할 목적으로 테이블(들)의 일부데이터를
-- 추출해서 만든 가상 테이블
-- materialized view를 제외하면 view는 물리적으로 데이터를
-- 저장하지 않음. 간단히 말해서 view는 테이블(들)의 select문
select e.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

-- 뷰 생성
create or replace view view_emp
as
select e.empno, e.ename, d.dname, d.deptno
from emp e, dept d
where e.deptno = d.deptno;

-- 사용자 뷰 확인
select * from user_views;

-- view를 통한 테이블 select
select * from view_emp;

-- force : 테이블이 없어도 뷰를 생성 가능
create or replace force view emp_chk
as
select * from employees
where deptno=10;

-- with check option : 뷰를 생성할때 제약조건을 부여
-- 뷰 생성시 부여된 조건을 뷰의 결과가 계속 유지되도록 하겠다.
create or replace view emp_chk1
as
select e.empno, e.ename, d.dname, d.deptno
from emp e, dept d
where e.deptno = d.deptno and e.deptno=30
with check option constraint emp_v_chk1;

select * from user_constraints;
select * from emp_chk1;

-- with check option을 가진 뷰에 변경이 일어나게 되므로 수행 안됨
--update emp_chk1 set deptno=20 where deptno=30;
--update emp_chk1 set deptno=30 where deptno=20;

-- 읽기 전용 뷰
create or replace view emp_chk2
as
select e.empno, e.ename, d.dname, d.deptno
from emp e, dept d
where e.deptno = d.deptno and e.deptno=30
with read only;

-- update emp_chk2 set deptno=20 where deptno=30;
-- delete from emp_chk2;

-- 뷰 변경 : 없음 => create or replace를 통해 재생성

-- 뷰 제거
drop view emp_chk2;

-- inline view (인라인뷰:from절내에 있는 서브쿼리)
select a.empno, a.ename, a.sal, a.hiredate
from emp a,  (select deptno, max(hiredate) maxdate
              from emp
              group by deptno) b
where a.deptno = b.deptno;

-- top-n query
select rownum, a.empno, a.ename, a.sal, a.hiredate
from emp a,  (select rownum rn, deptno, hiredate from emp) b
where a.deptno = b.deptno and b.rn<5;

--------------------------------------------
-- sequence
--------------------------------------------
-- 연속적인 일련번호 생성
create table seq_test (
seq number,
name varchar2(20)
);
-- 시퀀스 생성
create sequence seq_test_seq
increment by 1
start with 1000
maxvalue 10000
nocache
nocycle;
-- 시퀀스 확인
select * from user_sequences;
-- 시퀀스 사용
insert into seq_test(seq,name)
values (seq_test_seq.nextval,'홍길동');
select * from seq_test;
-- 시퀀스 현재값
select seq_test_seq.currval from dual;
-- 시퀀스 제거
drop sequence seq_test_seq;

-------------------------------------
-- synonym
-------------------------------------
-- 객체의 이름이 길때 줄여쓰기 위해서
rename emp to employees;
select * from tab;
-- 시노님 생성
create public synonym emp for employees;
-- 시노님 확인

-- 시노님 제거
drop public synonym emp;

------------------------------------------
-- 사용자 관리
------------------------------------------
-- 사용자 생성
create user hong identified by pass;
-- 권한 부여
-- 접속, 자원사용 role(권한의 모음)을 hong에게 부여
grant connect,resource to hong;
-- dba롤 부여
grant dba to hong;
-- 전체사용자에게 권한 부여
grant connect,resource,dba to public;
-- 권한 제거
revoke dba from hong;
-- with admin option : 부여받은 권한을 부여할 수 있는 옵션
-- sys
grant create session to scott with admin option;
-- scott
grant create session to hong;
-- 객체 권한
rename employees to emp;
grant select,insert,update,delete on emp to hong;
revoke insert,update,delete on emp from hong;
-- role(롤)
create role conn;

---------------------------------------
-- join
---------------------------------------

-- 1. cross join (cartesion product)
-- 두 테이블에서 가능한 모든 행의 조합을 생성
select count(*) from emp;
select count(*) from dept;
select empno, ename
from emp cross join dept;

-- 2. inner join (natural join, equi join)
-- cross join의 결과 중 조인조건에 맞는 행들을 추출
select e.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

-- 3. outter join
-- cross join의 결과 중 조인조건에 맞는 행들을 추출하고
-- 조인조건에 맞지 않는 행들도 추출
create table t1 (no number,name char(3));
create table t2 (no number,name char(3));
insert into t1 values (10,'AAA'); insert into t1 values (20,'BBB');
insert into t2 values (10,'CCC'); insert into t2 values (30,'DDD');
select * from t1; select * from t2;
-- left outer join
select t1.no, t1.name, t2.no, t2.name
from t1 left outer join t2
on t1.no = t2.no;
-- right outer join
select t1.no, t1.name, t2.no, t2.name
from t1 right outer join t2
on t1.no = t2.no;
-- full outer join
select t1.no, t1.name, t2.no, t2.name
from t1 full outer join t2
on t1.no = t2.no;

-- subquery
-- =, >, < 등의 비교연산일 경우는 서브쿼리의 결과가 한 개 나와야 함
select * from emp where job = (select job from emp where empno=7900);
-- 서브쿼리의 where절에는 그룹함수를 사용할 수 없습니다.
--select ename, sal
--from emp
--where sal = (select sal from emp where sal>avg(sal));

select e.ename,e.sal,e.deptno,d.dname
from emp e, dept d
where e.sal in (select max(sal) from emp group by deptno)
and e.deptno = d.deptno;

-- 직업이 MANAGER인 사람들과 같은 봉급을 받는 사원
select empno, ename, job, sal from emp
where sal = any (select sal from emp where job='MANAGER');

-- 직업이 MANAGER인 사람들의 최소봉급보다 작은 봉급을 받는 사원
select empno, ename, job, sal from emp
where sal < all (select sal from emp where job='MANAGER');

-- 서브쿼리의 결과가 존재하는지
select dname, deptno from dept
where exists (select * from emp where emp.deptno=10);

20090217-lsj403.sql

20090217_home-lsj403.sql