[데이터베이스] 22일차 - sql의 분류, 데이터타입, rock, 제약조건(FK삭제), Index

데이터베이스 10일차

 

 

2012/08/13 - [Dev. 640시간 뭉개기/강의내용정리] - [데이터베이스] 13일차 - 오라클 설치, 명령어, 권한, 롤

 

1. SQL의 분류

• DDL(Data Definition Language) : 데이터 정의어
• create
• alter
• drop
• truncate : 모든 행의 데이터 삭제
• DML (Data Manipulation) : 데이터 조작어
• insert
• delete
• update
• TCL (=DCL, Transaction Control Language) : 데이터 제어어
• commit
• rollback
• grant
• revoke

 

-- 테이블 복사 (제약조건은 복사되지 않음)
create table emp2 as select * from emp;
create table dept2 as select * from dept;

 

-- 테이블 내용 가져와서 넣기
insert into emp2 select * from emp;

 

-- savepoint : 저장점 지정
commit;
delete from emp2;
savepoint a;
select * from dept2;
delete dept2;
savepoint b;
rollback to a;  -- a시점으로 되돌아가기
select * from dept2;
select * from emp2;
rollback; -- 마지막 commit 을 실행한 시점으로 되돌아감

 

-- DDL : 오라클 객체를 만들고, 수정하고, 지우는 등의 명령
-- create, alter, drop, rename, comment, trucate 등..
-- 오라클 객체 : table, index, view, sequence, synonym...

 

-- drop : 삭제
drop table emp2;
select * from tab;
select * from recyclebin;

purge table emp2;
purge recyclebin;   -- 쓰레기통 비우기
drop table emp2 purge;  -- 쓰레기통에 넣지 않고 바로 삭제
flashback table emp2 to before drop;  -- 쓰레기통에 있는 객체 복구

 

-- truncate : 데이터를 지운다. (늘어난 용량을 리셋시킨다.)
truncate table emp2;

 

-- alter : 개체 추가, 변경

alter table emp2 add test varchar2(2000);     -- 컬럼 추가
alter table emp2 modify test varchar2(4000);  -- 컬럼 변경
alter table emp2 rename column test to test2; -- 컬럼 이름 변경
alter table emp2 drop column test2;           -- 컬럼 삭제
alter table emp2 set unused column sal;       -- 컬럼 사용 불가 (복구불능)
select * from user_tab_cols;                  -- 모든 테이블의 컬럼 검색
alter table emp2 drop unused column;          -- 히든 컬럼 삭제

 

 

2. 오라클 데이터 타입
    -- 문자계열 : char(nchar), varchar2(nvarchar2), clob
    -- 숫자계열 : number(3,2) - 2,3 : 0.012

create table typetbl (
ccc char(4)             -- byte 단위(고정형)
,ncc nchar(4)           -- 유니코드 단위(글자수)(고정형)
,vvv varchar2(4000)     -- byte 최대 4000바이트(가변형)
, nvv nvarchar2(2000)   -- 유니코드로 2000자(가변형)
, c1 clob               -- 대용량 문자
, n1 number(3)          -- 정수 3자리
, n2 number(3,2)        --정수1자리, 소수점 2자리
, n3 number(2,3)        -- 소수점 3자리 중에 첫자리는 반드시 0
, ddd date              -- 날짜 타입
);

 

insert into typetbl(ccc,ncc) values ('abc','a씨씨');
select ccc||'끝', ncc||'끝' from typetbl; -- 공백으로라도 무조건 자리수 채움

insert into typetbl(vvv, nvv) values('배고파', 'ㅍㅍㅍㅍㅎㅎㅎㅎ');
select vvv||'끝', nvv from typetbl;

insert into typetbl(n1,n2,n3) values(555.5, 1.955, 0.099);
select n1,n2,n3 from typetbl;

 

 

 

 

3. Rock

(1) row level rock : 변경된 행에 대해 다른 세션에서 DML 실행시 멈추는 현상

  세션 A
    SQL> delete from dept where deptno=99;

 

세션 B
    SQL> update dept set dname='wow' where deptno=99;
    --> 아무것도 진행되지 않고 멈춰있게 된다. (락이 걸려있다.)

 

 

(2) table level rock : 전체 삭제(수정)와 같은 DML 실행시 테이블 전체에 대해서 락이 걸리는 현상
-- 락이 걸렸을 경우 commit, rollback 을 해주어야 락이 풀린다.
-- *** 주의 : DML 실행 후 DDL 실행하면 자동으로 commit 된다.
insert into dept values(80, '밥줘', '서울');
rollback;
select * from dept;

 

 

 

4. 제약조건(FK삭제)

 

-- 제약조건 (PK, FK 가 연관되어 있는 경우 PK가 걸린 데이터를 마음대로 삭제할 수 없다.)
-- 처음 제약조건 부여시 PK 걸린 데이터를 삭제시 
--  1. FK를 null로 바꿔준다. (PK 10번 삭제 FK는 null로 바뀜)
--  2. FK에 해당하는 데이터 같이 삭제

-- emp2, dept2
create table emp2 as select * from emp;
create table dept2 as select * from dept;

-- PK를 주기
alter table dept2
add constraint PK_DEPT2 primary key (deptno);

-- FK를 주기
-- dept2에서 삭제가 일어나면 emp2에서도 해당 번호의 데이터가 같이 삭제됨
alter table emp2
add constraint FK_EMP2_DEPTNO foreign key (deptno) 
  references dept2(deptno) on delete cascade;
-- dept2에서 삭제가 일어나면 emp2에서 해당 번호가 null로 바뀐다.

alter table emp2 add constraint FK_EMP2_DEPTNO foreign key(deptno)
  references dept2(deptno) on delete set null;
  
alter table emp2 drop constraint fk_emp_deptno;
delete dept2 where deptno=10;
select * from emp2;
select * from dept2;
insert into emp2(empno, ename, deptno)
  values (1111,'완샘', null);
select * from user_constraints;

 

 

5. index(색인)

: 사전 ㄱ~ㅎ, 전화번호부 가~하

 

-- 인덱스를 써야 하는 경우
-- 1. 선택도(selectivity)가 높아야 한다. (예:이름, 아이디)
-- 2. where 절에 자주 등장하는 컬럼(join이 많이 걸리는 컬럼)
-- 3. 최소 1000건 이상의 데이터
--   (추후 테이블의 용량이 늘어날 주문테이블 종류는 예외)
-- 4. null값이 남발하는 컬럼

-- 인덱스 사용시 단점 : DML 속도가 느려진다.
create index idx_emp_ename on emp(ename);
select * from user_indexes;

-- 특징
-- 인덱스를 사용하는 걸 탄다(ride)라고 표현.
-- 인덱스를 타지 않고 검색되는 것을 full scan 이라고 부른다.
-- primary key에는 기본적으로 인덱스가 생성된다.
-- foreign key는 인덱스가 PK에 의해서 자동으로 타진다.
-- 오라클 고유 기능 - hint
--  /*+ index(emp idx_emp_ename) */ 를 써서 인덱스를 강제로 타게 한다.

-- 복합 인덱스 : 선행컬럼이 중요 (많이 검색되는 것을 선행 컬럼으로)
create index idx_emp_job_sal on emp(job, sal);
-- where job like ~~~~~는 위의 인덱스를 탄다.
-- where sal between ~~~ and ~~~ 는 인덱스를 타지 않는다.

 

 

 

6. 오라클 데이터 저장 단위

    *** 물리적인 단위 ***

1. block : 데이터를 읽고 쓰는 메모리 상의 단위
       (2/4/8/16/32KB - default : 8KB)
2. extent : block 이 8개 모인 단위
3. segment : table, index 등이 저장되는 단위
4. tablespace : 세그먼트의 집합체
5. Database : tablespace 의 집합체
6. Data Ware House : DB 의 집합체
     
     + quota : 테이블 스페이스에서 사용자에게 할당된 공간