SQL 04 테이블/시퀀스/인덱스

데이터 타입

• LONG - 통계 데이터에 자주 사용
• CLOB - 자료를 그대로 DB 안에 넣음 (4GB)
• BLOB - 자료를 이진화해서 DB 안에 넣음 (4GB)
• BFILE - 이진화 파일 (4GB)

테이블 생성

create table 테이블명 (
  컬럼1 타입 not null primary key,
  컬럼2 타입
  constraint pk_dept primary key (
    컬럼명
  )
);

• 테이블명, 컬럼명 은 숫자로 시작하거나 예약된 단어는 생성되지 않음.

테이블 수정

alter table 테이블 (
 
)

테이블 생성

create table t1 (
        seq number primary key,
        user_name varchar2(10) not null,
        user_id varchar2(10) not null, 
        user_pw varchar2(10) not null,
        user_addr varchar2(20) 
);

• DDL (Data Definition Language) 데이터 정의 언어

• ex) create, alter, drop, rename, truncate

• 자동 commit이 일어남.(rollback이 없음)

• 트랜잭션이 안 일어남.

• DML (Data Manipulation Language) 데이터 조작 언어

• ex) select, insert, update, delete

• DCL (Data Control Lnaguage) 데이터 제어 언어

• ex) rollback, commit

emp 테이블의 구조만 가져와서 테이블 생성 (복제)

create table tttt1 as (
        select * 
        from emp 
        where 1=0
);

emp 테이블의 구조 중 컬럼 empno, ename, sal의 구조만 가져와서 테이블 생성

create table tttt2 as (
        select empno, ename, sal 
        from emp 
        where 1=0
);

emp 테이블 구조 중 컬럼 empno, ename, sal과 연봉을 추가한 구조로 테이블 생성

create table tttt3 as (
      select empno, ename, sal, sal*12 ysal
      from emp 
      where 1=0
);

emp 테이블과 dept 테이블을 조인한 상태의 테이블 생성

create table tttt4 as (
      select e.ename, d.dname
      from emp e, dept d
      where e.deptno = d.deptno
);

t1 테이블에 rdate 컬럼 추가

alter table t1 add 
rdate date default sysdate;

t1 테이블에 컬럼 중 user_id를 userid로 변경

alter table t1 rename column 
user_id to userid;

t1 테이블에 user_name 컬럼의 타입을 변경

alter table t1 modify
user_name varchar2(30);

t1 테이블에 rdate 컬럼을 삭제

alter table t1 drop column 
rdate;

t1 테이블에 user_pw 컬럼을 unused로 표시 (접근 불가)

alter table t1 set unused(user_pw);
alter table t1 drop unused columns;

t1 테이블 명을 tt1으로 변경

rename t1 to tt1;

tttt1 테이블 삭제

drop table tttt1; --삭제

tttt2 테이블의 모든 데이터 삭제 (구조는 남아있음)

truncate table tttt2 cascade; --자르기

---

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

select *
from user_tables;

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

select *
from all_tables;

데이터베이스 관리자만 접근 가능한 객체 등의 정보 조회

select *
from dba_tables;

---

sequence는 유일한 값을 생성해주는 오라클 객체

• 번호표 같은 개념

 seq_t1 시퀀스 생성

create sequence seq_t1
start with 1 -- 1부터 시작
increment by 1; -- 하나씩 증가

시퀀스의 다음 값

select seq_t1.nextval
from dual;

시퀀스의 현재 값 (nextval을 한 번이라도 써야 사용 가능)

select seq_t1.currval
from dual;

seq_t1 시퀀스 생성

create sequence seq_t2
start with 1
increment by 1;

오류남, 한 번이라도 nextval 했어야 currval 실행가능.

select seq_t2.currval
from dual;

t1 테이블 생성

create table t1 (
        seq number primary key,
        uname varchar2(10) not null,
        uids varchar2(10) not null, 
        upw varchar2(10) not null,
        addr varchar2(20) 
);

t1 테이블에 레코드 입력

insert into t1 (seq, uname, uids, upw)
values(3, 'dd', 'dd', '111');

seq 부분에 만들어 놓은 시퀀스 seq_t1을 입력 (그럼 자동으로 시퀀스 부분이 증가)

insert into t1
values(seq_t1.nextval, 'dd', 'dd', '111', '');

t1 테이블에서 seq 컬럼에 seq_t1을 입력

update t1
set seq = seq_t1.nextval;

t1 테이블에서 seq가 60인 정보 출력

select *
from t1
where seq = 60;

t1 테이블에서 seq가 60인 열에 데이터 삽입

insert into t1
select seq_t1.nextval, uname, uids, upw, '서울'
from t1
where seq = 60;

emp 테이블에서 empno, sal, hiredate를 t2 테이블에 삽입

insert into t2
select empno, sal, hiredate
from emp;

emp 테이블에서 seq_t1.nextval, sal, hiredate를 t2 테이블에 삽입

insert into t2
select seq_t1.nextval, sal, hiredate
from emp;

emp 테이블에서 seq_t1.nextval, sal를 t2 테이블에 삽입

insert into t2(seq, score)
select seq_t1.nextval, sal
from emp;

데이터 한 번에 삽입

insert into t2(seq, score)
select value1, value2 from dual union all
select value1, value2 from dual union all
select value1, value2 from dual;

seq_t1 시퀀스를 2씩 증가하도록 변경 (‘start with’는 사용 못함)

alter sequence seq_t1
increment by 2;

seq_t1.nextval을 출력

select seq_t1.nextval from dual;

seq_t1 시퀀스 삭제

drop sequence seq_t1;

seq_t1 시퀀스 생성

create sequence seq_t1
start with 1
increment by 1;

---

index는 검색 속도를 향상시키기 위해 칼럼에 대해 생성하는 객체

index가 효율적인 경우

• where 절이나 조인 조건 절에서 자주 사용되는 칼럼

• 전체 데이터 중에서 10-15% 이내의 데이터를 검색하는 경우

• 테이블에 저장된 데이터의 변경이 드문 경우

t1 테이블의 uname 컬럼에 idx_t1 인덱스 생성

create index idx_t1
on t1(uname);

idx_t1 인덱스 삭제

drop index idx_t1;

t1 테이블에서 uids가 kim이고 upw가 111인 데이터 출력

select * from t1
where uids='kim' and upw='111';

복합 인덱스

• 위의 예제에서 두 개의 컬럼의 인덱스 생성

create index idx_t1
on t1(uids, upw);

복합 주요키 생성

• 뒤에 constraint를 이용

create table ttt(
    ssn1 number,
    ssn2 number,
    CONSTRAINT pk_ttt PRIMARY KEY (
        ssn1, ssn2
    )
);

복합 주요키 삭제

alter table ttt
drop constraint pk_ttt;

주요키를 설정할 지 enable/disable로 설정 가능

alter table ttt
add constraint pk_ttt primary key (
    ssn1
)
disable;

emp 테이블 sal 컬럼에 idx_emp_sal 인덱스 생성

create index idx_emp_sal
on emp(sal);

sal이 인덱스 되었다고 가정하에, 웬만하면 인덱싱된 컬럼을 건드는 건 좋지 못함

select *
from emp
where sal > 2000/12;

emp 테이블에서 주소 컬럼의 앞 두글자가 서울인 데이터를 검색

• 이 경우 substring(addr, 1, 2)을 인덱스를 해줘야 효율적

• 데이터를 쪼개는 행위 시 인덱스도 같이 쪼개짐

select *
from emp
where substring(addr, 1, 2) = '서울'

인덱스 추가 시, where절에 많이 쓰이는 지 수정이 자주 일어나는지를 고려하여 인덱싱해야 함

select *
from t1
where uids = 'lee'
where upw = '111'
where uids = 'lee' and upw = '111'
where uname = '홍길동'