오라클 테이블 작성과 내용 삽입, 삭제, 조회, 수정하기 insert, delete, select, update / 다른 테이블 참조할수 있도록 기본키, 외래키 이용해 테이블 작성하기 Foreign Key , Unique Key, Primary Key

    Table : 기본적인 저장 단위
            Row(행), Column(열)
            
    Table  -  TableSpace  -  Data File

    table - object
           테이블 생성(create) / 테이블 삭제 (drop) / 테이블 수정 (alter) 
            
    data -  
           데이터 삽입(insert) / 데이터 삭제 (delete) / 데이터 조회 (select) / 데이터 수정 (update)

 

 

테이블 생성하기

CREATE TABLE TB_TEST01(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(10),
    COL3 VARCHAR2(10)
);

CREATE TABLE TB_TEST02(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(10),
    COL3 VARCHAR2(10)
)
TABLESPACE TABLESPACE1;

 

테이블 복제의 두가지 방법

1: 복제한 테이블의 데이터를 포함해서 복제

2: 복제한 테이블의 데이터는 제외하고 복제

1) Table 복제 : 데이터 포함
CREATE TABLE TB_TEST03
AS 
SELECT employee_id, first_name, salary 
FROM employees;


2) Table 복제 : 데이터 미포함
CREATE TABLE TB_TEST04
AS 
SELECT *
FROM employees
WHERE 1 = 2;     -- 성립되지 않는 조건을 작성

2-1) Table 복제 : 데이터 미포함
CREATE TABLE TB_TEST04( empno, name, money )
AS 
SELECT employee_id, first_name, salary
FROM employees
WHERE 1 = 2;

 

테이블 삭제하기

DROP TABLE TB_TEST03;

DROP TABLE TB_TEST04;

PURGE RECYCLEBIN; <-삭제된 테이블이 담긴 휴지통 비우기

 

테이블 수정하기 RENAME, ADD, MODIFY, DROP

테이블 수정 : 테이블 명
ALTER TABLE TB_TEST04
RENAME TO TB_TEST99;

테이블 수정 : 컬럼추가
ALTER TABLE TB_TEST99
ADD 
DEPTNAME NUMBER(3);

테이블 수정 : 컬럼수정
ALTER TABLE TB_TEST99
MODIFY 
DEPTNAME VARCHAR(20);

테이블 수정 : 컬럼삭제
ALTER TABLE TB_TEST99
DROP 
COLUMN DEPTNAME;

위의 정리를 활용해서 부서 정보 담은 테이블 만들고 원하는 정보 삽입, 저장해보기

CREATE TABLE DEFT(DEPTNO, DNAME, MGR, LOC)
AS
SELECT department_id, department_name, manager_id, location_id 
FROM departments
WHERE 1=2;

-- insert
INSERT INTO deft(deptno, dname, mgr, loc)
VALUES(10, '기획부', 100, 123);

INSERT INTO deft(deptno, dname)
VALUES(20, '관리부'); 

INSERT INTO deft
VALUES(30, 'IT부', 333, 234);

INSERT INTO deft(dname, deptno, mgr, loc)
VALUES('경리부', 40, 200, 345);

INSERT INTO deft    -- not enough values
VALUES(30, 'IT부', 333);

INSERT INTO deft(deptno, dname, mgr, loc)
VALUES('40', '영업부', '400', '678');

INSERT INTO deft(deptno, dname, mgr, loc)   -- ok
VALUES(40, 100, 400, 678);

INSERT INTO deft(deptno, dname, mgr, loc)   -- column not allowed here
VALUES(40, "영업부", 400, 678);

-- delete
DELETE 
FROM deft
WHERE dname = '100';

DELETE
FROM deft
WHERE mgr IS NULL;

-- update
UPDATE deft
SET mgr = 300
WHERE deptno = 30;

UPDATE deft
SET deptno = 50, mgr = 500
WHERE dname = '영업부';

 

 

 

---

    무결성 : Constraint
            Column을 지정하는 성질

    Primary Key : 기본키. NULL(빈칸)을 허용하지 않는다. 중복을 허용하지 않는다.  EX )id, 주민번호
    Unique Key : 고유키. NULL(빈칸)을 허용한다. 중복을 허용하지 않는다.         EX) email
    Foreign Key : 외래키. 테이블과 테이블을 연결하는 목적의 성질이다.
                  Employees(외래키:department_id) Departments(기본키:department_id)  
                  외래키로 설정된 컬럼은 PK나 UK로 설정되어 있어야 한다.
    CHECK : 범위를 지정, 지정된 값외에 NULL을 사용한다.
    NOT NULL : NULL을 허용하지 않는다. (빈칸을 허용하지 않음)

 

테이블 만들고 COLUMN에 NOT NULL 설정하기

 

CREATE TABLE TB_TEST1(
    COL1 VARCHAR2(10) NOT NULL,
    COL2 VARCHAR2(20)
);

INSERT INTO tb_test1(col1, col2)
VALUES('AAA', '111');

INSERT INTO tb_test1(col1)
VALUES('BBB');

INSERT INTO tb_test1(col2)
VALUES('111');

INSERT INTO tb_test1(col1, col2) -- COL1에는 NULL값이 들어가면 안되므로 허용안됨
VALUES('', '222');

 

기본키(Primary Key) = Unique + NOT NULL

기본키 COLUMN에 적용하기

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

INSERT INTO tb_test1(pkcol, col1, col2)
VALUES('AAA', '111', 'aaa');

INSERT INTO tb_test1(pkcol)
VALUES('BBB');

INSERT INTO tb_test1(pkcol, col1, col2)
VALUES('CCC', '222', 'bbb');

INSERT INTO tb_test1(pkcol, col1, col2)
VALUES('', '222', 'bbb');

INSERT INTO tb_test1(col1, col2)
VALUES('222', 'bbb');

DROP TABLE tb_test1
CASCADE CONSTRAINTS;

*CASCADE CONSTRAINTS; 

기본키의 특성마저 삭제하겠다는 말

 

 

 

 

고유키(UNIQUE KEY): 중복된 값은 입력불가(=PK), NULL을 허용

CHECK: 입력될 값의 형식을 제한하는 명령

CREATE TABLE tb_test1(
    UKCOL VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

INSERT INTO tb_test1(UKCOL, COL1, COL2)
VALUES('AAA', '111', 'aaa');

INSERT INTO tb_test1(COL1, COL2)
VALUES('111', 'aaa');


-- CHECK : 지정된 값만 허용. NULL을 허용. 
CREATE TABLE TB_CHECK(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(20),
    CONSTRAINT CHK_01 CHECK( COL1 IN('사과', '배', '바나나') ),
    CONSTRAINT CHK_02 CHECK( COL2 > 0 AND COL2 <= 10 )
);

INSERT INTO TB_CHECK(COL1, COL2)
VALUES('사과', 5);

INSERT INTO TB_CHECK(COL1)
VALUES('배');

INSERT INTO TB_CHECK(COL2)
VALUES(10);

INSERT INTO TB_CHECK(COL1, COL2) -- 오류 발생
VALUES('포도', 5);

INSERT INTO TB_CHECK(COL1, COL2) -- 오류발생
VALUES('사과', 0);

 

UK, PK 삭제가 필요한 경우

ALTER TABLE tb_test1
DROP 
CONSTRAINT  UK_TEST_01;

 

 

    FOREIGN KEY :   외래키
                    부모테이블, 자식테이블을 연결하는 목적
                    PK, UK로 설정되어 있어야 한다.
                    NULL을 허용

 

 

기본키와 외래키를 사용한 입력예시

DROP TABLE TB_DEPT
CASCADE CONSTRAINTS;

CREATE TABLE TB_DEPT(
    DEPARTMENT_ID VARCHAR2(10),
    DEPARTMENT_NAME VARCHAR2(20),
    LOCATION_ID NUMBER,
    CONSTRAINT PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID)
);

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('10', '기획부', 100);

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('20', '영업부', 200);

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('30', '개발부', 300);


DROP TABLE TB_EMP
CASCADE CONSTRAINTS;

CREATE TABLE TB_EMP(
    EMPNO VARCHAR2(10),
    ENAME VARCHAR2(20),
    DEPARTMENT_ID VARCHAR2(10),
    CONSTRAINT FK_EMP_TEST FOREIGN KEY(DEPARTMENT_ID) REFERENCES TB_DEPT(DEPARTMENT_ID)
);

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES(1, '홍길동', '10');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES(2, '성춘향', '30');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID) -- 부모 테이블에 등록된 값이 없다
VALUES(3, '홍두께', '40');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES(3, '일지매', '');

INSERT INTO TB_EMP(EMPNO, ENAME)
VALUES(4, '정수동');