뷰

• 뷰는 데이터베이스에서 제공하는 가상의 테이블을 의미한다.
• 뷰를 사용하면 복잡한 쿼리문을 대신할 수 있기 때문에 개발의 용이성을 가질 수 있다.
• 뷰는 뷰를 만들 때 사용한 쿼리문을 저장하는 것이며 뷰를 조회할 때 뷰를 만들 때 사용한 쿼리문이 동작하게 된다.
• 원본 테이블에 로우를 삽입할 경우 뷰에도 로우가 추가 된다.
• create view 뷰이름
  as
  서브쿼리

ex)

– 사원의 사원번호, 이름, 급여, 근무부서이름, 근무지역을 가지고 있는 뷰를 생성한다.

create view emp_dept_view
as
select a1.empno, a1.ename, a1.sal, a1.deptno, a2.dname
from emp a1, dept a2
where a1.deptno = a2.deptno;

ex)

-- 뷰를 조회
select * from emp_dept_view;

ex)

-- 조인을 사용한 뷰인 경우 뷰를 통한 로우의 삽입은 불가능하다.
insert into emp100_dept100_view (empno, ename, sal, deptno)
values (6000, '김길동', 2000, 10);

create view emp200_view
as
select empno, ename, sal
from emp100;

insert into emp200_view (empno, ename, sal)
values (7000, '박길동', 3000);

select * from emp100;

Comment and share

인덱스

• 데이터 베이스에서 검색속도를 빠르게 하기 위해 사용하는 기능
• 인덱스 기능은 primary key를 설정하면 자동으로 설정된다.

  인덱스의 장점

• 검색속도가 빨라진다.
• 시스템의 부하를 줄여 성능을 향상시킨다.

  인덱스의 단점

• 추가적인 기억공간이 필요하다.
• 인섹스 생성 시간이 오래 걸린다.
• insert, update, delete와 같은 변경 작업이 자주 일어나면 오히려 성능저하를 가져올 수 있다.

  인덱스 정보 조회하기

  select index_name, table_name,
  column_name form user_ind_columns
  where table_name = ‘테이블명’;

ex)

-- 인덱스
create table test_table100(
data number constraint TEST_TABLE100_PK primary key,
data2 number not null
);

create index emp01_idx
on emp01(ename);

Comment and share

시퀀스

• 테이블 내의 컬럼 중에 primary key를 지정하기 애매한 경우 1부터 1씩 증가되는 값을 저장하는 컬럼을 추가하여 사용하는 경우가 있다.
• 이 때, 1부터 1씩 증가되는 값을 구하기 위해 시퀀스를 사용한다.
• create sequence 시퀀스이름
  start with 숫자
  increment by 숫자
  maxvalue 숫자 or nomaxvalue
  minvalue 숫자 or nominvalue
  cycle or nocycle
  cache 숫자 or nocache
• start with 숫자 : 시작 값. 시작 값은 절대 최소 값보다 작을 수 없다.
• increment by : 증가값
• maxvalue : 시퀀스가 가질 수 있는 최대 값.
  생략하거나 nomaxvalue일 경우 10의 27승.
• minvalue : 시퀀스가 가질 수 있는 최소 값
  생략하거나 nominvalue일 경우 1.
  cycle : 최대 혹은 최소값까지 갈 경우 순환한다.
  cache : 시퀀스를 메모리상에서 관리할 수 있도록 설정한다.
  메모리 상에서 관리를 하게 되면 속도가 빨라질 수 있다.

ex)

create table test_table1(
idx number constraint TEST_TABLE1_IDX_PK primary key,
number_data number not null
);

create sequence test_seq1
start with 0
increment by 1
minvalue 0;

select test_seq1.currval from dual;

insert into test_table1(idx, number_data)
values (test_seq1.nextval, 100);

insert into test_table1(idx, number_data)
values (test_seq1.nextval, 200);

select * from test_table1;

select test_seq1.currval from dual;

drop sequence test_seq1;

Comment and share

트랜잭션

• 트랜잭션이란 최종결과를 내기까지 위한 하나의 작업 단위를 의미한다.
• 오라클 데이터베이스는 개발자가 전달한 insert, update, delete 문을 메모리상에서만 수행하고 디스크에 반영하지 않는다.
• 이는 실수로 인한 데이터의 유실을 막기 위함이다.
• 데이터 베이스를 조작하는 작업이 완료되고 모두 정상적으로 되었다면 이를 디스크에 반영해야 한다.
• 작업이 시작되고 디스크에 반영될 때 까지의 작업의 단위를 트랜잭션이라 부르며 트랜잭션이 완료되면 디스크에 반영하여 저장하게 된다.

Comment and share

테이블 구조 변경하기

• alter 구문을 이용하면 테이블 구조를 변경할 수 있다.
• alter table 테이블명
  add (컬럼명 자료형 제약조건)
• alter table 테이블명
  modify (컬럼명 자료형 제약조건)
• alter talbe 테이블명
  rename to 테이블명
• alter table 테이블명
  rename column 컬럼명1 컬럼명2
• alter table 테이블명
• drop column 컬럼명
• drop table 테이블명

ex)

-- 컬럼추가
alter table test_table1
add (data3 number not null);

-- 컬럼의 데이터 타입 변경
alter table test_table1
modify (data3 varchar2(100));

-- 테이블 이름 변경
drop table test_table2;

alter table test_table1
rename to test_table2;

-- 컬럼 이름 변경
alter table test_table2
rename column data3 to data4;

-- 컬럼의 삭제
alter table test_table2
drop column data4;

-- 테이블 삭제
drop table test_table2;

Comment and share

제약조건 추가하기

• 테이블을 생성한 후 제약 조건을 추가하거나 제거하고 싶다면 alter구문을 이용한다.
• alter table 테이블명
  add 제약조건
• alter table 테이블명
  drop 제약조건

ex)

-- 제약조건 추가하기
create table test_table20(
data1 number,
data2 number,
data3 number,
data4 number,
data5 number,
data6 number
    );

-- null 제약조건 추가
alter table test_table20
modify data1 not null;

insert into test_table20 (data1)
values (null);

-- primary key 제약조건 추가
alter table test_table20
add constraint TEST_TABLE20_DATA2_PK primary key(data2);

insert into test_table20 (data1, data2)
values (1, 10);

insert into test_table20 (data1, data2)
values (2, 10);

insert into test_table20 (data1, data2)
values (3, null);

-- 외래키 제약조건 추가
alter table test_table20
add constraint TEST_TABLE20_DATA3_FK foreign key(data3)
    references emp(empno);
    
insert into test_table20 (data1, data2, data3)
values (10, 100, 7369);

insert into test_table20 (data1, data2, data3)
values (11, 101, 100);

-- unique 제약조건 추가
alter table test_table20
add constraint TEST_TABLE20_DATA4_UK unique(data4);

insert into test_table20 (data1, data2, data4)
values (12,102,100);

insert into test_table20 (data1, data2, data4)
values (13,103,100);

-- check 제약조건
alter table test_table20
add constraint TEST_TABLE20_DATA5_CK check(data5 between 1 and 5);

insert into test_table20 (data1, data2, data5)
values (14, 104, 5);

insert into test_table20 (data1, data2, data5)
values (15, 105, 20);

alter table test_table20
add constraint TEST_TABLE20_DATA6_CK check(data6 in(10,20,30));

insert into test_table20 (data1, data2, data6)
values (16, 106, 20);

insert into test_table20 (data1, data2, data6)
values (17,107,50);

select * from test_table20;

ex)

-- 제약조건 제거
create table test_table30(
data1 number not null,
data2 number constraint TEST_TABLE30_DATA2_PK primary key,
data3 number constraint TEST_TABLE30_DATA3_FK
            references emp(empno),
data4 number constraint TEST_TABLE30_DATA4_UK unique,
data5 number constraint TEST_TABLE30_DATA5_CK
            check (data5 between 1 and 10),
data6 number constraint TEST_TABLE30_DATA6_CK
            check (data6 in (10,20,30))
);

-- not null 제약조건 수정
alter table test_table30
modify data1 null;

insert into test_table30 (data1,data2)
values (null, 100);

-- primary key 제약 조건 제거
alter table test_table30
drop constraint TEST_TABLE30_DATA2_PK;

insert into test_table30 (data2)
values (null);

-- 외래키 제약조건 제거
alter table test_table30
drop constraint TEST_TABLE30_DATA3_FK;

insert into test_table30 (data3)
values (1000);

-- unique 제약조건 제거
alter table test_table30
drop constraint TEST_TABLE30_DATA4_UK;


insert into test_table30 (data4)
values (100);

insert into test_table30 (data4)
values (100);

-- check 제약조건 제거
alter table test_table30
drop constraint TEST_TABLE30_DATA5_CK;

alter table test_table30
drop constraint TEST_TABLE30_DATA6_CK;

insert into test_table30 (data5, data6)
values (20, 100);

select * from test_table30;

ex)

-- 제약조건 활성/비활성
create table test_table40(
data1 number constraint TEST_TABLE40_DATA1_PK primary key
);

insert into test_table40 (data1)
values (100);

insert into test_table40 (data1)
values (100);

alter table test_table40
disable constraint TEST_TABLE40_DATA1_PK;

insert into test_table40 (data1)
values (100);

select * from test_table40;

alter table test_table40
enable constraint TEST_TABLE40_DATA1_PK;
-- 활성화시 하나라도 제약조건에 위배되는 값이 있으면 에러가 뜬다.
delete from test_table40;

insert into test_table40 (data1)
values (100);

insert into test_table40 (data1)
values (200);

select * from test_table40;

alter table test_table40
enable constraint TEST_TABLE40_DATA1_PK;

insert into test_table40 (data1)
values (100);

Comment and share

테이블 레벨 제약조건

• 제약 조건을 설정할 때 각 컬럼 마다 지정할수도 있지만 하단부분에 몰아서 지정할 수도 있다.
• 컬럼명 옆에 기술하는 것을 컬럼레벨, 하단에 몰아서 기술하는 것을 테이블 레벨 제약조건이라고 부른다.

ex)

-- 컬럼레벨

create table  test_table10(
data1 number constraint TEST10_TABLE10_DATA1_PK primary key,
data2 number not null constraint TEST_TABLE10_DATA2_UK unique,
data3 number not null constraint TEST_TABLE10_DATA3_FK
                    references emp(empno),
data4 number not null constraint TEST_TABLE10_DATA4_CK
                    check(data4 between 1 and 10),
data5 number not null constraint TEST_TABLE10_DATA5_CK
                    check(data5 in(10,20,30))
);

-- 테이블레벨 제약조건

create table test_table11(
data1 number,
data2 number not null,
data3 number not null,
data4 number not null,
data5 number not null,

constraint TEST_TABLE11_DATA1_PK primary key(data1),
constraint TEST_TABLE11_DATA2_UK unique(data2),
constraint TEST_TABLE11_DATA3_FK foreign key(data3)
                                references emp(empno),
constraint TEST_TABLE11_DATA4_CK check(data4 between 1 and 10),
constraint TEST_TABLE12_DATA5_CK check(data5 in(10,20,30))
);

복합키

• 테이블 레벨 제약조건을 설정할 때 하나이상의 컬럼을 하나의 primary key로 묶어서 사용할 수 있다.
• 복합키의 경우 각 컬럼에 중복된 데이터가 허용이 되지만 한 로우의 모든 복합키 컬럼이 중복되는 것은 허용하지 않는다.
• 복합키는 테이블레벨로만 설정이 가능하다.

ex)

-- 복합키

create table test_table12(
data1 number,
data2 number,
constraint TEST_TABLE12_COMBO_PK primary key(data1, data2)
);

insert into test_table12 (data1, data2)
values (100,200);

insert into test_table12 (data1, data2)
values (100,300);

insert into test_table12 (data1, data2)
values (400,200);

select * from test_table12;

insert into test_table12 (data1, data2)
values (100,200);

insert into test_table12 (data1, data2)
values (null,null);

Comment and share

제약조건

• 테이블의 데이터를 저장 혹은 수정할 때 컬럼의 값에 대한 조건을 설정하는 것을 제약조건이라고 한다.
• 설정된 조건에 위배되는 값을 컬럼에 저장할 수 없으며 데이터의 무결성을 위한 구문이다.
• not null : 컬럼에 null을 허용하지 않는다.
• unique : 중복된 값을 허용하지 않는다.
  null을 무한대로 저장할 수 있다.
• primary key : 중복된 값을 허용하지 않으며 null값을 허용하지 않는다. 각 로우를 구분하기 위한 유일한 값을 저장하기 위해 사용한다.
• foreign key : 다른테이블 혹은 같은 테이블의 칼럼을 참조하는 제약조건이다. 참조하는 컬럼에 저장되어 있는 값만 컬럼에 저장할 수 있다. 일반적으로 primary ket 제약 조건이 설정된 컬럼을 참조한다.
• check : 조건에 만족할 경우 컬럼에 저장할 수 있도록 한다.

ex)

-- NOT NULL : 해당 컬럼에는 NULL을 저장할 수 없다.

create table test_table1(
data1 number,
data2 number not null
);

insert into test_table1(data1, data2)
values (100, 101);

insert into test_table1 (data1)
values (200);
-- data2가 null값이므로 값이 입력이 되지 않는다.

insert into test_table1(data2)
values (201);
-- data1은 not null 조건이 없으므로 입력이 된다.
-- not null 제약조건은 오류메세지가 정확하게 나오기 때문에 명확히 알 수 있다.
-- 그러나 다른 제약조건은 오류메세지가 랜덤하게 나오므로 오류메세지를 지정해 주어야한다.

ex)

-- nuique : 중복된 값을 허용하지 않고 null은 무한대로 허용한다.
create table test_table2(
data1 number,
data2 number constraint TEST_TABLE2_DATA2_UK unique
);

insert into test_table2(data1, data2)
values (100, 101);

insert into test_table2(data1, data2)
values (200,201);

insert into test_table2(data1, data2)
values (300, 201);

insert into test_table2(data1, data2)
values (200, null);

insert into test_table2(data1)
values (200);

ex)

-- primary key : 중복된 값을 허용하지 않으며 null값을 허용하지 않는다.
-- 각 로우를 구분하기 위한 유일한 값을 저장하기 위해 사용한다.

create table test_table3(
data1 number,
data2 number constraint TEST_TABLE3_DATA2_PK primary key
);

insert into test_table3 (data1, data2)
values (100,101);

insert into test_table3 (data1, data2)
values (100,101);

insert into test_table3 (data1)
values (100);

ex)

-- foreign key : 다른테이블 혹은 같은 테이블의 컬럼을 참조하는 제약 조건이다.

create table test_table4(
data1 number constraint TEST_TABLE4_PK primary key,
data2 number not null
);

insert into test_table4(data1, data2)
values (100,101);

insert into test_table4(data1, data2)
values (200,201);

create table test_table5(
data3 number not null,
data4 number constraint TEST_TABLE5_DATA4_PK
            references test_table4(data1)
);

insert into test_table5 (data3, data4)
values (1,100);

insert into test_table5 (data3, data4)
values (2,100);

insert into test_table5 (data3, data4)
values (3,200);

insert into test_table5 (data3, data4)
values (4,200);

insert into test_table5 (data3, data4)
values (5,null);

insert into test_table5 (data3)
values (6);

insert into test_table4(data3, data4)
values (7, 300);
-- 부모키가 없으므로 에러다.

ex)

--check : 컬럼에 저장될 값을 지정한다.
create table test_table6(
data1 number constraint TEST_TABLE6_DATA_CK
            check (data1 between 1 and 10),
data2 number constraint TEST_TABLE6_DATA2_CK
            check (data2 in(10, 20, 30))
);

insert into test_table6 (data1, data2)
values (1,10);


insert into test_table6 (data1, data2)
values (2,20);

select * from test_table6;

insert into test_table6 (data1, data2)
values (20,10);

insert into test_table6 (data1,data2)
values (5,100);

Comment and share

테이블 만들기

• create table 테이블 명(
  컬럼명 자료형 제약조건,
  컬럼명 자료형 제약조건
  )

컬럼의 자료형

• char(size) : 고정길이 문자열 타입
• varchar2(size) : 가변 길이 문자열 타입
• number : 최고 40자리까지 저장할 수 있는 숫자 타입
• char는 고정된 메모리를 할당 받는다.
  varchar2는 가변길이의 메모리를 할당 받는다.
• date : 날짜
• long : 가변길이 문자열 타입, 최대 2Gbyte

ex)

-- 다음과 같은 정보를 저장하기 위한 테이블을 만든다.
-- 학생번호, 학생이름, 학생나이, 학생국어점수, 영어점수, 수학점수

create table stu_table(
stu_idx number,
stu_name char(10),
stu_age number,
stu_kor number,
stu_eng number,
stu_math number
);

ex)

create table number_table(
number1 number,
number2 number(3),
number3 number(5, 2)
);

insert into number_table(number1)
values (10000);

select * from number_table;

insert into number_table(number2)
values(100);
-- number2는 3자리까지만 가능하다.

insert into number_table(number3)
values (100.111111);

-- number3은 총5자리를 저장하나 소수점 2자리 까지만 저장한다.

ex)

-- emp테이블을 복제한 emp01테이블을 만드시오

create table emp01
as
select * from emp;

ex)

-- emp테이블에서 사원의 번호, 이름, 급여 정보를 가지고 있는 테이블을 생성하시오

create table emp02
as
select empno, ename, sal
from emp;

ex)

-- 30번 부서에 근무하고 있는 사원들의 사원번호, 이름, 근무부서이름을 가지고 있는 테이블을 생성한다.

create table emp03
as
select a1.empno, a1.ename, a2.dname 
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.deptno = 30;

ex)

-- 각 부서별 급여 총합, 평균 최고액, 최저액, 사원수를 가지고 있는 테이블을 생성하시오

create table emp04
as
select sum(sal) as sum, trunc(avg(sal)) as avg, max(sal) as max,
    min(sal) as min, count(sal) as count
from emp
group by deptno;

Comment and share

delete

• 테이블 내의 로우를 삭제하는 구문이다.
• delete from 테이블명
  where 조건문

ex)

-- 사원번호가 7499인 사원의 정보를 삭제한다.
delete from emp01
where empno = 7499;

ex)

-- 사원의 급여가 평균급여 이하인 사원의 정보를 삭제한다.
delete from emp01
where sal <= (select avg(sal)
            from emp01);
            
select * from emp01;

ex)

-- 커미션을 받지 않는 사원들의 정보를 삭제한다.
delete from emp01
where comm is null;

Comment and share