3/5 [Database #9] DDL, DML

[ 데이터 정의어 (DDL) ]

* Data Definition Language 객체(Schema, Domain, Table, View, Index)를 정의, 변경 또는 삭제할 때 사용하는 언어

    → CREATE / DROP / ALTER / 

* ksc5601 == euc - kr    >> 한글: 2byte, 영어/숫자: 1byte >> 한글의 경우 char: 최대 1000자, vc2: 최대 2000자

* utf-8    >> 한글: 3byte, 영어/숫자: 1byte >> 한글의 경우 char: 최대 666자, vc2: 최대 1333자

1. 데이터 속성(Type)

1) 숫자형

▷ number [(n, m)]

- n은 자릿수, m은 소수 이하 자릿수

- n은 소수점 이하 자릿수를 포함한 전체 자릿수

- m의 default 값은 0 (생략시 정수만 저장함)

- n, m 모두 생략할 경우, 입력한 데이터 값 만큼 공간이 할당됨

- max 길이는 38자리

2) 문자형

▷ char(n)

- 고정길이 문자형

- 문자열의 길이가 n보다 작으면 나머지 공간은 공백으로 채워짐

    → 검색을 해야 하는 경우 문자열 길이가 n개로 잡히기 때문에 char로 지정하지 않음

        ex) char(10)일때, 'java'로 입력해도 문자열 길이가 10이기 때문에 'java'로 검색하면 불일치 나옴

- max길이는 2000byte

    → 데이터가 자주 업데이트 되는 경우에 유리

▷ vc2(n)

- 가변길이 문자형

- 저장공간을 효율적으로 이용할 수 있음

- max길이는 4000byte

- 컬럼 길이의 편차가 심한 경우, Null로 입력되는 경우가 많은 경우

    → 데이터가 자주 업데이트 되는 경우를 제외하고는 char에 비해 유리함

- 지정된 길이보다 짧은 데이터 입력시 나머지 공간은 채우지 않음

▷ long

- 가변길이 문자형

- max길이는 2Gbyte

▷ clob

- 대용량 텍스트

- max길이는 4Gbyte

3) 기타

▷ date

- 날짜와 시간 저장

- 별다른 설정이 없이 지정할 경우 년/월/일만 출력함

- 자릿 수 없음

▷ lob

- 가변 길이 바이너리 데이터

- 이미지, 실행파일 저장 가능

- 4GB까지 저장 가능

▷ blob

- 이미지, 사운드, 동영상 저장 가능

- 학생증 사진 등 용량이 적고 영속성이 보장되는 경우 정도만 사용함

    (but, 용량이 크면 db검색할 때 속도가 떨어지기에 일반적으로는 파일서버를 이용함)

▷ timestamp    (잘 사용하지 않음)

▷ rowid    (아마 사용할 일 없을 듯)

2. 테이블 생성, 변경 및 삭제 

    * 수업때는 테이블에만 실습했는데, 객체 전부에 가능한듯

1) 테이블 생성 : CREATE TABLE table_name ( colum data_type constraint [ , col2 .... colN ]);

(1) 테이블이름 및 컬럼이름 지정

- 숫자로 시작할 수 없음

- 특수문자는 _ 이외에는 사용하지 말 것

(2) 데이터 타입 지정

- char/varchar2의 문자 수는 일반적으로 8의 배수로 사용함 (1byte는 8bit라서..)

- 우편번호/전화번호 등을 number로 지정하면 0으로 시작하는 번호를 사용할 수 없음 (ex. 01247의 경우 1247이 되어버림)

- default 값 지정 가능

(3) 제약조건 지정

- not null/ unique : 나중에 관리할 일이 적어서 굳이 이름을 설정할 필요가 없기 때문에 컬럼이름 옆에 바로 입력해서 사용함

- primary key/ foreign key : 컬럼이름 옆에 바로 입력해서 사용하면 제약조건 이름이 임의로 지정되기 때문에 추후 관리하기 불편함

        >>> CONSTRAINT constraint_name PRAIMARY KEY | FOREIGN KEY ( colum) 

(4) 예제

-- 회원 (필수입력 정보) member

--  이름              name            varchar2(30)

--  아이디            id              varchar2(16)

--  비밀번호          pass            varchar2(16)

--  나이              age             numbere(3)

--  이메일아이디      emailid         varchar2(30)

--  이메일도메인      emaildomain     varchar2(30)

--  가입일            joindate        date

-- 회원 상세정보 (부가입력 정보) member_detail

--  아이디            id              varchar2(16)

--  우편번호          zipcode         varchar2(5)   ----------- number로 잡으면 01247의 경우 1247이 되어버림

--  일반주소          address         varchar2(100)

--  상세주소          address_detail  varchar2(100)

--  전화번호1         tel1            varchar2(3)

--  전화번호2         tel2            varchar2(4)

--  전화번호3         tel3            varchar2(4)

create table member

(

    name varchar2(30) not null, ----- 나중에 관리할 일이 적어서 굳이 이름을 설정할 필요가 없기 때문에 컬럼에 바로 제약조건 입력한 것임

    id varchar2(16),

    pass varchar2(16) not null,

    age number(3) check (age < 150),

    emailid varchar2(30),

    emaildomain varchar2(30),

    joindate date default sysdate,   ----------- 기본값 설정

    constraint member_id_pk primary key (id)    ---- 위의 컬럼명 데이터타입 옆에다가 primary key라고 입력해도 되지만,

);                                           ----------- 그러면 primary key 이름이 자동으로 지정이 되어버리기에 따로 이름을 지정해 주는거임

create table member_detail

(

    id varchar2(16),

    zipcode varchar2(5),

    address varchar2(100),

    address_detail varchar2(100),

    tel1 varchar2(3),

    tel2 varchar2(4),

    tel3 varchar2(4),

    constraint member_detail_id_fk foreign key (id) references member (id)  -- fk제약조건을 걸었기 때문에 위 테이블 생성하기 전에 먼저 생성하면 에러 남

);

2) subquery를 이용한 create : CREATE TABLE table_name AS SUBQUERY ;

    * DBA가 DB 테스트 할 때 정도나 사용함. 그냥 이런게 있다고 알아둘 것

(1) 참고사항

- 테이블 구조 및 데이터 모두를 서브쿼리로 가져와서 테이블 생성

create table emp_all

as

    select *

    from employees;

    > 테이블 복사한거랑 마찬가지임

- 기존 테이블 구조만 가져와서 테이블 생성

create table emp_blank

as

    select *

    from employees

    where 1=0;

    > 데이터 타입과 제약조건도 모두 가져오게 됨

- 컬럼이름 지정

create table emp_50

as

    select e.employee_id eid, e.first_name name, e.salary sal, d.department_name dname

    from employees e, departments d

    where e.department_id = d.department_id(+)

            and e.department_id = 50;

    > 서브쿼리의 alias가 새로운 테이블의 컬럼이름이 됨

3) 테이블 삭제

create table table_name

(

    colum data_type constraint [ , col2 .... colN ]

);

(1) 참고사항

- 테이블을 생성하고 삭제할 때 순서를 주의해야 함. 특히 foreign key를 설정하는 경우, 참조하는 테이블을 참조 테이블보다 먼저 생성하거나 나중에 삭제하는 경우에 에러 발생함

- CASCADE 이용해서 제약조건을 무시하고 강제 삭제할 수도 있으나, 권장되지 않음

- drop 후에, 모든 테이블을 select 해보면 삭제한 테이블의 쓰레기 값이 목록에 나오는 것을 확인할 수 있음

    > purge recyclebin; 으로 정리하면 됨!

    

(2) 예제

drop table member;

drop table member_detail;

4) ALTER

    * 수업시간엔 다루지 않음

[ 데이터 조작어(DML) ]

* Data Manipulation Language 데이터를 검색, 삽입, 삭제 및 변경할 때 사용하는 언어

1. 데이터 갱신

1) 데이터 입력 : INSERT

INSERT INTO table_name [( col_name [, ...] )]

VALUES ( constant [, ...] ) ;

INSERT INTO table_name [( col_name [, ...] )]

SELECT ( col_name [, ...] | * )

FROM table_name

[WHERE predicate] ;

(1) 기본사항

- 컬럼 순서대로 입력 or table_name 다음에 컬럼 순서를 지정하여 입력

    → 이때, 값의 순서가 달라서 데이터 타입에 위배되거나, 값의 수가 적으면 에러 발생함

- 일부 컬럼을 지정하지 않으면 null 값이 입력됨

insert into member

values ('정현정', 'kitri', '1234', 31, '8972hj', 'gmail.com', sysdate);

insert into member (id, name, age, pass, emailid, emaildomain, joindate)

values ('hong', '홍길동', '25', 123, '8972hj', 'gmail.com', sysdate);

insert into member (id, name, pass, joindate)

values ('hong2', '홍길동', 123, sysdate);

- INSERT ALL : 한번에 여러개의 테이블에 insert!

    * insert를 여러번 쓰면 각 insert마다 DB에 연결이 되기에 시간이 많이 걸림. 그래서 insert all !

    * 하나라도 에러 발생하면 전체가 insert 안됨

insert all

    into member (id, name, age, pass, emailid, emaildomain, joindate)

    values ('oracle', '오라클', '30', 'a123456', 'oracle', 'oracle.com', sysdate)

    into member_detail (id, zipcode, address, adress_detail, tel1, tel2, tel3)

    values ('oracle', '25085', '서울시 구로구 오라클밸리', '오라클 연구소', '010', '1234', '5678')

select * from dual;

    > select 절 입력안하면 에러 발생함 (다중 테이블 insert 구문을 사용할 때는 반드시 서브쿼리가 동반되어야 함)

    

(2) 에러코드

▷ not enough values : 값의 수가 적으면 에러 발생함

▷ 무결성 제약조건 위배 - primary key

▷ 무결성 제약조건 위배 - foreign key

    > 위 에러코드와 다른 것은 제약조건 이름 뿐! 때문에 위에서 나왔던 것처럼 제약조건 이름을 설정해야 알아보기 편함

- end.