Oracle/ 테이블 생성과 데이터 타입의 이해

<테이블(Table)>

: DB에서 데이터를 저장하는 가장 기본적인 구조

  오라클에는 테이블 이외에 뷰(View), 인덱스(Index), 시퀀스(Sequence)등 여러 개체가 존재
  이 중 물리적인 공간을 갖는 개체를 세그먼트(Segment)라고 분류 

  -> 테이블은 사용자가 직접 조회 가능한 데이터를 저장하는 유일한 세그먼트
  DB란 테이블에 저장된 데이터의 효율성과 무결성을 보장하기 위한 시스템
  
  

1. 테이블 생성과 삭제
테이블: 행(Row). 컬럼(열, Column)로 이루어진 자료구조 형태
   
정보시스템에서 문서의 형태였던 도표의 구조를 도입해서 테이블이라 명명하고 사용
초창기 파일 시스템을 기반으로 한 DB시스템에서 레코드와 필드로 구성된 파일이 도표를 대신
-> RDB(Relational DataBase)가 본격적으로 사용되면서부터 행(Row)과 컬럼(Column)으로 구성된 테이블이 이를 대신함

CREATE TABLE 테이블명(
    컬럼명    데이터타입 [DEFAULT 디폴트값] [컬럼 레벨 제약조건],
    컬럼명    데이터타입 [DEFAULT 디폴트값] [컬럼 레벨 제약조건],
  );

 

 - 테이블 생성  CREATE TABLE
  1) 데이터 타입
    컬럼에 입력될 데이터의 종류와 크기를 결정(자료형)
  2) DEFAULT
    입력이 누락되었을 때 기본 입력값을 정의
    디폴트값을 지정하지 않으면 null 값이 저장
  3) 컬럼 레벨 제약조건
    PK, FK, UK, CHECK, NOT NULL 등을 지정
  4) 테이블 레벨 제약조건
    PK, FK, UK, CHECK 만 지정
    NOT NULL 은 정의불가.
  

 

  DROP TABLE 테이블명
  [CASCADE CONSTRAINT];

 

-DROP
  1) 테이블 삭제
  2) CASCADE CONSTRAINT : 테이블이 다른 테이블로부터 참조되는 경우, 해당 제약조건을 먼저 삭제 후 테이블을 삭제

 

 

  SELECT table_name, column_name, 
         data_type, data_length
  FROM user_tab_columns   -- sys 계정의 소유 테이블
  WHERE table_name = 테이블명;

 

-SELECT
  1) user_tab_columns 데이터 딕셔너리(Data Dictionary)를 통해서 지정한 테이블의 구조를 자세히 검색
  2) data_type : 컬럼의 데이터 타입
  3) data_length : 컬럼의 길이
  
  

2. 테이블 생성시 이름 규칙
  1) 문자로 시작
  2) 30자이내
  3) 영문, 숫자, _,$, # 만 사용
  4) 한글 사용은 가능하지만 코드오류의 우려로 사용지양
  5) 테이블은 동일한 유저 (스키마 , 계정) 안에서 유일해야 한다.
  6) 예약어(명령어등) 사용불가
  7) 대소문자의 구별 X(사용문자와는 관계없이 모든 이름은 대소문자로 저장)
  
  

 

3. 데이터타입 *자주쓰는타입
  1) 문자타입
  - CHAR, *VARCHAR2, LONG, CLOB
  2) 숫자타입
  - *NUMBER
  3) 날짜타입
  - *DATE
  4) 이진타입
  - RAW, LONG RAW, BLOB, BFILE
  5) ROWID 타입 :  ROWID



4. 데이터타입 상세
1) VARCHAR2(n) : 가변길이 문자타입 (1 < n < 4000 byte)
2) CHAR(n) : 고정길이 문자타입 (1 < n < 4000 byte)
3) NUMBER(n, p) : 숫자타입
    n은 전체자리수, p는 소수점 이하 자리수
    전체자리수를 초과할 경우는 입력 거부, 소수점 이하자리수가 초과되면 반올림입력
    소수점 이하의 값이 없을 시 길이를 지정하지 않는 것이 좋다.
4) DATE : 날짜타입. 출력이나 입력형식과 무관하게 YYYY/MM/DD:HH24:MI:SS 형태로 저장 (기원전 4712년 1월 1일 ~ 서기 9999년 12월 31일)
5) LONG : 2gb까지 저장가능한 가변길이 문자 타입. 조건 검색불가. 테이블당 하나의 LONG컬럼만 정의 가능 or can use CLOB
6) CLOB : LONG을 개선한 타입. 4gb저장 가능. 한테이블에 여러컬럼으로 정의 가능
7) BLOB : 4G까지 저장 가능한 가변길이 이진 타입
8) ROW(n) : 가변길이 이진타입(n < 2000)
9) BFILE : 외부 파일저장을 위한 이진 타입. 4G 이내 파일
10) ROWID : ROWID 를 저장하기 위한 데이터 타입. PL-SQL 프로그래밍에서 많이 사용되며 각 문자는 64진수로 Encoding.
 

 

 

 

 

-테이블생성 예시

--테이블 생성
--명 board, 컬럼구성: no 게시번호, name 작성자, sub 글제목, content 내용, wdate 작성날짜
 
CREATE TABLE board(
  no NUMBER,
  name VARCHAR2(50),
  sub VARCHAR2(100),
  content VARCHAR2(4000),
  wdate DATE DEFAULT sysdate
);

DESC board;

--테이블 컬럼테이터타입 확인
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'BOARD';
  
--데이터 입력
INSERT INTO board (no)
VALUES (1);

--확정
COMMIT;

SELECT * FROM board;



--테이블을 생성하고 데이터입력을 확인
CREATE TABLE t1(
  no NUMBER(4,2)
);

INSERT INTO t1 VALUES (12.12);
INSERT INTO t1 VALUES (1.789);
SELECT * FROM t1;
INSERT INTO t1 VALUES (123.1);--넘버자릿수 (4자리 소수점 2자리 )를 지정해줘서 입력불가 오류뜸

DROP TABLE t1;
  
  
  
  CREATE TABLE t2(
  name VARCHAR2(3)
);
INSERT INTO t2 VALUES ('AAA');
INSERT INTO t2 VALUES('장');
INSERT INTO t2 VALUES('장강');
INSERT INTO t2 VALUES ('A');

 

 

 

 

 

- CHAR와 VARCHAR2의 차이점

CREATE TABLE comp(
  co1 CHAR(4),
  co2 VARCHAR2(4)
);
INSERT INTO comp VALUES('AA', 'AA');
INSERT INTO comp VALUES('AAAA', 'AAAA');
SELECT * FROM comp;

SELECT * FROM comp WHERE co1='AA';
SELECT * FROM comp WHERE co2='AA';
SELECT * FROM comp WHERE co1=co2;

아래 2개는 서로 다른 데이터로 판단됨

--co1 CHAR(4)     'AA'   AA__

--co2 VARCHAR2(4) 'AA'   AA

=> CHAR의 사용은 드뭄, VARCHAR2 를 일관적으로 사용

 

 

 

 

 

- DATE 타입

CREATE TABLE hd(
  no NUMBER,
  hdate DATE
);

INSERT INTO hd VALUES (1, sysdate); --1)시분초가 같이 저장됨으로

SELECT * FROM hd;

SELECT *
FROM hd
WHERE hdate='2020/02/27'; --2)이렇게는 검색되지 않음

-->범위검색 해줘야함
SELECT  *
FROM hd
WHERE hdate BETWEEN '2020/02/27' AND '2020/02/28';


  
--비추천방법
--hdate를 문자열로 자동형변환하므로 대용량 테이블에서는 심각한 성능 저하가 일어날 수 있다.
SELECT *
FROM hd
WHERE hdate LIKE '20/02/27%';

 

1) 우리나라에서 날짜는 DATE 보다 VARCHAR2(8)로 정의. (시분초 사용불가)

2) 외국에서는 TRUNC(sysdate)이용. 시분초를 00:00:00으로 저장

3) sysdate-> 범위검색 요구

 

 

 

 

 

 

<Exercise>

--client 테이블을 삭제하라
DROP TABLE client;

--client 테이블을 생성하라
CREATE TABLE client(
  cnum NUMBER,
  cname VARCHAR2(50),
  addr VARCHAR2(200),
  tel VARCHAR2(20)
);

--server 테이블을 삭제하라
DROP TABLE server;

--server 테이블을 생성하라
CREATE TABLE server(
  cnum NUMBER,
  cname VARCHAR2(50),
  addr VARCHAR2(200),
  tel VARCHAR2(20)
);