table 생성 및 셋팅

명령
    Object - create, drop, select, alter    
    Data - insert, delete, select, update

 

 

 

--SYS

--Query로 Tablespace 만들기

CREATE TABLESPACE TABLESPACE2
DATAFILE 'C:\TEST\TEST_TBS1_02.DBF' SIZE 10M    --저장위치
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED         --추가되는 용량
LOGGING                                         --로깅 사용
EXTENT MANAGEMENT LOCAL AUTOALLOCATE            --자동 할당(로컬)
BLOCKSIZE 8K                                    --BLOCK 크기 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 

 

--TABLE SPACE 수정

--1. 스페이스명을 수정
ALTER TABLESPACE TABLESPACE2
RENAME TO TB_TEST_NEW;

--2. 데이터파일의 용량을 수정
ALTER DATABASE
DATAFILE 'C:\TEST\TEST_TBS1_02.DBF' RESIZE 7M;

--3. TABE SPACE 삭제
DROP TABLESPACE TB_TEST_NEW
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

 

 

 

 

 

--HR

--TABLE 생성

CREATE TABLE TB_TEST01(         --지정 안하면 자동으로 USER가 들어감
    COL_01 VARCHAR2(10),
    COL_02 VARCHAR2(10),
    COL_03 VARCHAR2(10),
    COL_04 VARCHAR2(10)
);

 

-- TABLE SPACE를 지정해서 생성

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

 

--TABLE COPY : 데이터 포함하여 복사하여 새로생성

--전체 복사
DROP TABLE TB_TEST03;

CREATE TABLE TB_TEST03
AS
SELECT employee_id, salary, first_name          --원하는 데이터
FROM employees;

--TABLE COPY : 데이터 미포함( 컬럼만 복사)
CREATE TABLE TB_TEST04
AS
SELECT
    *
FROM departments
WHERE 1 = 2;            --성립이 안되는 조건을 작성하면 틀만 가져오게 됨

 

--JOIN하여 새 테이블 생성, 컬럼명 지정

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;

 

 

 

 

 

--테이블 / 컬럼 수정: 

 

테이블명 변경

ALTER TABLE TB_TEST04
RENAME
TO
TB_TEST99;

컬럼명 수정

ALTER TABLE TB_TEST99
RENAME
COLUMN 
DEPARTMENT_ID TO DEPTNO;

 

 

 

 

단일컬럼 추가

ALTER TABLE TB_TEST99
ADD
COL_NEW1 VARCHAR2(30);

단일컬럼 수정

ALTER TABLE TB_TEST99
MODIFY
COL_NEW1 VARCHAR2(20);

 

 

 

 

다중컬럼 추가

ALTER TABLE TB_TEST99
ADD
(COL_NEW2 NUMBER, COL_NEW3 DATE);

다중컬럼 수정

ALTER TABLE TB_TEST99
MODIFY
(COL_NEW1 VARCHAR2(20),COL_NEW2 VARCHAR2(10),COL_NEW3 VARCHAR2(30));

 

 

 

 

 

--삭제 

단일 컬럼 삭제

ALTER TABLE TB_TEST99
DROP
COLUMN COL_NEW1;

다중 컬럼 삭제

ALTER TABLE TB_TEST99
DROP
(COL_NEW2, COL_NEW3);

테이블 삭제

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

 

 

 

 

 

--데이터

--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', 65, 220, '인사부');

 

--DATA DELETE

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

DELETE FROM tb_test99
WHERE manager_id IS NULL;

 

--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;

 

 

 

 

 

--테이블 삭제

DROP TABLE DEPT_INFO;

 

--휴지통 비우기

PURGE RECYCLEBIN;