Oracle Table

Jun 10, 2020 Kim Byungin

Table Space

Table space 란?

오라클은 데이터를 관리하는 데이터베이스입니다. 데이터를 어딘가에 저장해놓고 사용하는 시스템이라고 볼 수 있습니다. 그리고 데이터 저장 단위 중 가장 상위에 있는 단위를 테이블 스페이스라고 합니다. 데이터 저장 단위는 물리적, 논리적단위로 나눌 수 있습니다. 물리적 단위는 파일을 의미하고 논리적 단위는 데이터블록 -> 익스텐트 -> 세그먼트 -> 테이블스페이스 이렇게 나뉩니다. 데이터 블록 여러개가 모여 익스텐트 하나를 만들고, 익스텐트 여러개가 모여 하나의 세그먼트를 구성하는 식입니다.

테이블 스페이스는 가장 상위개념입니다.

Table Space 생성

create tablespace TABLESPACE3               [테이블 스페이스명]
DATAFILE 'C:\test\TEST_Test_03.DBF'         [파일경로]
SIZE 10M                                    [초기 데이터 파일 크기 설정]
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED     [추가되는 용량]
LOGGING                                     [로깅을 사용]
EXTENT MANAGEMENT LOCAL AUTOALLOCATE        [로컬]
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Table Space 수정

이름 수정

ALTER TABLESPACE TABLESPACE3
RENAME TO TB_TEST_NEW;

용량 수정

ALTER DATABASE
DATAFILE 'C:\TEST\TEST_TBS1_03.DBF' RESIZE 7M;

Table Space 삭제

DROP TABLESPACE TB_TEST_NEW
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

Table

Table create

CREATE TABLE TB_TEST01(
    COL_01 VARCHAR2(10),
    COL_02 VARCHAR2(10),
    COL_03 VARCHAR2(10),
    COL_04 VARCHAR2(10)
);

칼럼은 생성하고 자료형태와 용량을 생성시 기재해 준다.

CREATE TABLE TB_TEST02(
    COL_01 VARCHAR2(10),
    COL_02 VARCHAR2(10),
    COL_03 VARCHAR2(10),
    COL_04 VARCHAR2(10)
)
TABLESPACE TABLESPACE2;

Table Space를 지정하여 테이블을 생성할 수 있다.

Table Copy

CREATE TABLE TB_TEST03
AS
SELECT employee_id, SALARY, first_name
FROM employees;

select from 을 이용하여 불러오고 싶은 칼럼과 테이블을 지정하여 데이터를 똑같이 가진 테이블을 생성 할 수 있다.

CREATE TABLE TB_TEST04
AS
SELECT *
FROM departments
WHERE 1 = 2;

마지막 WHERE 절에서 성립하지 않는 식을 기재하면 해당 테이블이 생성될때 값은 불러오지 않고 칼럼만 끌어와 테이블을 생성한다.

CREATE TABLE DEPT_INFO(EMPNO, SAL, DNAME, LOC) -- 컬럼명을 바꿀 수 있다.
AS
SELECT e.employee_id, e.salary,d.department_name,d.location_id
FROM employees E, departments D
WHERE e.department_id = d.department_id;

JOIN한 내용의 값을 가진 Table로 생성할 수 있다. 생성시 컬럼 명을 변경하여 생성 할 수 있다.

Table 수정

1. 테이블 이름 수정

ALTER TABLE TB_TEST04
RENAME
TO TB_TEST99;

2. 단일 column 추가

ALTER TABLE TB_TEST99
ADD
COL_NEW1 VARCHAR2(30);

3. 다중 column 추가

ALTER TABLE TB_TEST99
ADD (COL_NUM2 NUMBER, COL_NUM3 DATE);

4. 단일 column 수정

ALTER TABLE TB_TEST99 MODIFY
COL_NEW1 VARCHAR2(20);

자료형태, 크기 등을 변경할 수 있다.

5. 다중 column 수정

ALTER TABLE TB_TEST99
MODIFY
(COL_NUM2 VARCHAR2(30), COL_NUM3 VARCHAR2(20));

컬럼 두개의 크기를 변경하였다.

6. 단일 column 삭제

ALTER TABLE TB_TEST99
DROP
COLUMN COL_NEW1;

7. 다중 column 삭제

ALTER TABLE TB_TEST99
DROP
(COL_NUM2,COL_NUM3);

8. column명 수정

ALTER TABLE TB_TEST99
RENAME
COLUMN DEPARTMENT_ID TO DEPTNO;

Table 삭제

DROP TABLE TB_TEST01;
DROP TABLE TB_TEST02;
DROP TABLE TB_TEST03;

Table data

DATA INSERT

INSERT INTO TB_TEST99(DEPTNO,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
VALUES(100, '기획',20, 200);

INSERT INTO TB_TEST99(DEPTNO,DEPARTMENT_NAME)
VALUES(101, '관리부');

INSERT INTO TB_TEST99
VALUES(102, '영업부',45, 320);

INSERT INTO TB_TEST99(DEPTNO,MANAGER_ID,LOCATION_ID,DEPARTMENT_NAME)
VALUES(103,520, 203,'인사부');

위의 코드 모두 데이터가 추가가 되며 각 컬럼에 맞게 같은 순서로 추가가 된다.

DATA DELETE

DELETE FROM TB_TEST99
WHERE department_name = '인사부';

DELETE FROM TB_TEST99
WHERE manager_id IS NULL;

delete를 이용하여 FROM 뒤에 테이블 명 그리고 WHERE 조건을 통해 삭제할 행을 지시할 수 있다.

DATA UPDATE

UPDATE TB_TEST99
SET manager_id = 70   -- 바꿔야할 컬럼과 값
WHERE department_name = '영업부';  -- 조건을 걸어주고 맞는 행을 찾는다

UPDATE TB_TEST99
SET manager_id = 30, location_id = 150
WHERE deptno = 100;

Table의 무결성(CONSTRIANT)

- 무결성이란?

column의 값을 지정하는 성질을 설정

종류

1. Primary Key (기본키) : Null을 허용하지 않음.(반드시 값을 넣어야한다), 중복을 허용하지 않는다.(회원 ID, 주민번호)

2. Unique Key (고유키) : Null을 허용한다. 중복을 허용하지 않음.(E-mail)

3. Foreign Key (외래키) : 테이블과 테이블을 연결하는 목적의 성질이다.(JOIN의 목적이다)(Null을 허용)(JOIN되는 테이블에 값이 있어야함)
외래키로 설정된 column은 연결된 테이블에서 PK나 UK로 설정되어 있어야 한다.

4. CHECK : 범위를 설정. NULL을 허용

5. NOT NULL : null을 허용하지 않는다.

Primary Key (기본키)

식별자, 한 테이블에서 최대 32개 컬럼 까지 지정할 수 있다.

= Unique + Not null

생성방법 1

CREATE TABLE TEST_01(
  PKCOL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY, 
 ★ PK_TEST_01 는 사용자 마음대로 설정 가능( 나중에 성질을 삭제할때 이 이름을 사용해야한다)
  COL1 VARCHAR2(20),
  COL2 VARCHAR2(30)
);

생성방법 2

CREATE TABLE TEST_01(
  PKCOL VARCHAR2(10)PRIMARY KEY, 
  ★  PK_TEST_01 로 설정을 안하고 생성할 경우 시스템에서 임의로 이름을 붙여주고 생성한다.
  COL1 VARCHAR2(20),
  COL2 VARCHAR2(30)
);

생성방법 3

다중 기본키를 지정할때 아래와 같이 해야한다.

CREATE TABLE TEST_01(
  PKCOL VARCHAR2(10), 
  COL1 VARCHAR2(20),
  COL2 VARCHAR2(30),
  CONSTRAINT PK_TEST_01 PRIMARY KEY(PKCOL,COL1)           -- 최대 32개 까지 지정 가능
);

만약 아래와 같이 구현을 하면 에러가 난다.

CREATE TABLE TEST_01(
    PKCOL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY, 
    COL1 VARCHAR2(20)CONSTRAINT PK_TEST_02 PRIMARY KEY,
    COL2 VARCHAR2(30)
);

생성 방법 4 (실무)

CREATE TABLE TEST_01(
    PKCOL VARCHAR2(10), 
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(30)
);

ALTER TABLE TEST_01
ADD CONSTRAINT PK_TEST_01 PRIMARY KEY(PKCOL,COL1);

만약 기본키 설정을 해제하기 위해서는 아래와 같이 소스코드를 기재하면된다.

ALTER TABLE TEST_01
DROP CONSTRAINT PK_TEST_01

사용자가 지정하였던 Primary Key 이름을 사용해서 설정을 해지한다.

UNIQUE Key

고유키 . 중복된 값은 입력불가. NULL은 허용
생성 방법
```
CREATE TABLE TEST_02(
  UKCOL VARCHAR2(10) CONSTRAINT UK_TEST_02 UNIQUE,
  COL_01 VARCHAR2(20),
  COL_02 VARCHAR2(30)
);
```
방법은 Primary Key 생성과 동일하며 끝에 UNIQUE만 붙여주면 된다.

고유키 설정 해지

ALTER TABLE TEST_02
DROP CONSTRAINT UK_TEST_02;

FOREIGN KEY (외래키)

테이블과 테이블을 연결하기 위한 무결성 제약 조건이다.
다른 테이블(부모테이블)에서는 PK, UK로 컬럼이 설정되어 있어야한다.
NULL 허용

우선 외래키는 JOIN을 하기위한 목적으로 설정되는 경우가 많다. 따라서 우선 기본키, 고유키를 가진 테이블을 하나 우선 생성하여 활용해 보겠다.

Table 1 (PK)

CREATE TABLE TB_PARENT(
    PKCOL01 VARCHAR2 (10),
    COL_01 VARCHAR2 (20),
    COL_02 VARCHAR2 (30),
    CONSTRAINT PK_TB_PARENT PRIMARY KEY(PKCOL01)
);

TB_PARENT 에 값을 넣어주는 코드

INSERT INTO TB_PARENT(PKCOL01,COL_01,COL_02)
VALUES('AAA','aaa','가가가');

INSERT INTO TB_PARENT(PKCOL01,COL_01,COL_02)
VALUES('BBB','bbb','나나나');

INSERT INTO TB_PARENT(PKCOL01,COL_01,COL_02)
VALUES('CCC','ccc','다다다');

Table 2 (FK)

CREATE TABLE TB_CHILD(
    KEY_01 VARCHAR2(10),
    KEY_02 VARCHAR2(20),
    FKCOL01 VARCHAR2(10),
    CONSTRAINT FK_TB_CHILD FOREIGN KEY(FKCOL01)
    REFERENCES TB_PARENT(PKCOL01)
);

※ 외래키를 가진 테이블을 생성시 PK를 가진 테이블의 PK가 설정된 컬럼의 용량, 자료형태는 FK가 설정된 컬럼과 동일하게 진행 되어야 한다.

한 먄약 PARENT 테이블에 기본키나 고유키가 없으면 생성시 에러가 난다.

TB_CHILD 에 값을 넣어주는 코드

INSERT INTO TB_CHILD(KEY_01,KEY_02,FKCOL01)
VALUES('123','가나다','BBB');

INSERT INTO TB_CHILD(KEY_01,KEY_02,FKCOL01)
VALUES('456','라마바','');

만약 TB_PARENT에 없는 값을 TB_CHILD 테이블에 값을 넣으려고 하면 에러가 난다.

외래키를 사용한 목적( JOIN )

SELECT C.KEY_01, C.KEY_02, C.FKCOL01,P.PKCOL01, P.COL_01,P.COL_02
FROM TB_CHILD C , TB_PARENT P
WHERE C.FKCOL01 = P.PKCOL01;

JOIN을 하여 값을 구할 수 있는것을 확인 할 수 있다.

123 가나다 BBB BBB bbb 나나나

CHECK

지정된 값(범위)만 입력할 수 있고 NULL을 허용, 중복 허용

CREATE TABLE TB_CHECK(
    COL_01 VARCHAR2(10),
    KEY_01 VARCHAR2(20),
    CONSTRAINTS CHK_TB_CHECK01 CHECK(COL_01 IN('사과','배','바나나')),
    CONSTRAINTS CHK_TB_CHECK02 CHECK(KEY_01 > 0 AND KEY_01 <= 100)
);

위 테이블은 두개의 컬럼을 가지고 있고 COL_01 은 ‘사과’, ‘배’, ‘바나나’ 중 하나 선택하여 데이터를 넣을 수 있다.

또한 KEY_01은 값의 범위를 지정하여 0부터 100까지의 숫자만 넣을 수 있다.

중복이 가능하며, NULL값을 넣을 수 있다.

NOT NULL

CREATE TABLE TB_TEST(
    COL_01 VARCHAR2(10) NOT NULL,
    COL_02 VARCHAR2(20)
);

테이블 생성시 NOT NULL을 지정하게 되면 해당 컬럼은 NULL값을 넣을 수 없고 무조건 값을 넣어 주어야 한다.