Oracle DB의 DDL

## 지금까지 학습 내용

-SELECT 
-WHERE 조건식
-함수:단일행,다중행
-조인
-서브쿼리:TOP_N 알고리즘
-SET 연산자
-DML : INSERT,UPDATE,DELETE

## DDL  

-오라클 데이터타입 : 20여 종류

# 문자
-- CHAR(N)
    N은 바이트
   고정길이 문자 / 최대 2000 BYTE / 디폴트 1 BYTE
   대체로 1BYTE 문자정보에 사용
   
-- VARCHAR2
   가변길이 문자 / 최대 4000 BYTE / 디폴트 1 BYTE
   1자 이상의 문자정보에 사용

-- LONG(N)
   최대 2GB 크기의 가변길이 문자열
   한 레코드에 한개만 사용가능
   요즘 권장하지 않음 => CLOB 권장

-- CLOB
   대용량 텍스트 데이터 타입
   최대 4GB 

# 숫자
-- 정수
   NUMBER(자릿수)
   NUMBER(3):0~999
-- 실수
   NUMBER(전체자릿수,소숫점이하자릿수)
   NUMBER(4,2):0.0~99.99
   NUMBER(2,2):0.00~0.99

# 날짜
-- DATE
   BC 4712면 1월1일 부터 9999년 12월 31일
   세기, 년, 월, 일, 시, 분, 초 까지 입력가능
   
-- TIMESTAMP
   날짜를 로그로 남길 때
   초이하 밀리세컨 단위까지 기록 가능

-----------------------------------------------------

## 테이블 생성

CREATE TABLE 테이블명(
 컬럼명 데이터타입(사이즈) 제약조건 제약조건 제약조건,
 컬럼명 데이터타입(사이즈) 제약조건,
 컬럼명 데이터타입(사이즈) 제약조건
);

# 학생 테이블

ST ( SNO, NAME, BIRTH)

CREATE TABLE ST(
SNO CHAR(8),
NAME VARCHAR2(50),
BIRTH DATE
);

-- 테이블 구조 확인
DESC ST;

-- 테이블 구조 변경:ALTER
- ADD : 컬럼 추가
- MODIFY : 컬럼의 타입과 크기 변경 => 보통 크기를 확대할 때
- DROP : 컬럼 삭제
- RENAME COLUMN : 컬럼명 수정

-- EMAIL, PHONE 두개의 컬럼을 추가

ALTER TABLE ST
ADD (EMAIL VARCHAR2(100), PHONE VARCHAR2(15));

-- PHONE 컬럼의 사이즈 15 -> 20 변경

ALTER TABLE ST
MODIFY (PHONE VARCHAR2(20));

-- BIRTH -> BIRTHDAY 변경

ALTER TABLE ST
RENAME COLUMN BIRTH TO BIRTHDAY;

-- PHONE 컬럼을 삭제

ALTER TABLE ST
DROP (PHONE);

ALTER TABLE ST
DROP COLUMN PHONE;

-- 테이블 삭제

DROP TABLE ST;
==> 테이블구조 + 입력자료 모두 삭제

TRUNCATE TABLE ST;
==> 테이블구조는 남고, 입력자료만 삭제

자료삭제 비교
--------------------------------------------------------------------------------------------------
명령어 구분 로그 명령취소 기능
--------------------------------------------------------------------------------------------------
DELETE DML YES YES 원하는 ROW 삭제
TRUNCATE DDL NO NO 입력자료 모두 삭제
DROP DDL NO NO 스키마+입력자료 모두 삭제
--------------------------------------------------------------------------------------------------

-- 테이블 이름 변경

RENAME 기존 테이블명 TO 새로운 테이블명;

RENAME ST TO STUDENT;

-- 테이블 딕셔너리

USER_TABLES => 유저가 작업한 테이블에 대한 정보를 기록한 테이블
TAB
SELECT * FROM USER_TABLES;

SELECT * FROM TAB;

## 테이블 이름 정의 규칙
-- 테이블과 컬럼 이름은 문자로 시작해야 되고, 최대 30자 이내
-- 이름은 문자, 숫자, 특수문자(_,$,#)를 사용가능.주로 '_'만 사용
-- 대소문자 구분하지 않는다.
-- 데이터 딕셔너리에는 대문자로 저장된다.
-- 동일한 유저안에서 중복이 될 수 없다.
-- 서로 다른 테이블에 동일한 데이터를 저장하는 컬럼명은 같은 이름을 사용하면 좋다.

======================================================

# 테이블 제약 조건

# 키 제약조건
-- 기본키:primary key:not null + unique
-- 외래키:foreign key:참조하는 테이블의 기본키 중에 하나이거나 NULL이 올 수 있다.

# 제약(constraints)
-- P : primary key
-- R : foreign key
-- U : unique : 중복불가, null 허용
-- C : check (조건식) : 데이터 검증조건
-- C : not null : null 불가

-- default : 기본값

부서 ( 부서번호, 부서명)
t_dept(dept_id,dept_name)

사원 ( 사번, 이름, 성별 ,이메일, 입사일,  부서번호)
t_emp(id,name,gender,email,hire_date,dept_id)

CREATE TABLE T_DEPT(
DEPT_ID NUMBER(3) PRIMARY KEY,
DEPT_NAME VARCHAR2(100) NOT NULL
); 

CREATE TABLE T_EMP(
ID VARCHAR2(30) PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL,
GENDER CHAR(1) CHECK(GENDER IN ('F','M')),
EMAIL VARCHAR2(100) UNIQUE,
HIRE_DATE DATE DEFAULT SYSDATE,
DEPT_ID NUMBER(3) REFERENCES T_DEPT(DEPT_ID)
);

CREATE TABLE T_EMP(
ID VARCHAR2(30) PRIMARY KEY
,NAME VARCHAR2(50) NOT NULL
,GENDER CHAR(1) CHECK(GENDER IN ('F','M'))
,EMAIL VARCHAR2(100) UNIQUE
,HIRE_DATE DATE DEFAULT SYSDATE
,DEPT_ID NUMBER(3) REFERENCES T_DEPT(DEPT_ID)
); //데이터 지울때 오류 방지!

-- 부서 테이블에 자료 입력

INSERT INTO T_DEPT VALUES(10,'총무부');
INSERT INTO T_DEPT VALUES(20,'생산부');
INSERT INTO T_DEPT VALUES(30,'판매부');

-- 제약조건 확인하기
USER_CONSTRAINTS

SELECT * FROM USER_CONSTRAINTS;
==> 유저가 만든 모든 제약조건이 나옴
==> 제약조건은 테이블 단위로 확인하는 것이 좋다.

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME="T_DEPT";
==> 테이블이름은 대문자로 검색할 것

INSERT INTO T_DEPT VALUES(30,'판매부');
==> unique constraint (AA01_SYS_C007655) violated 오류 발생!!
==> PRIMARY KEY 제약 조건 위반

INSERT INTO T_DEPT VALUES(40,NULL);
==> cannot insert NULL into ("AA01","T_DEPT","DEPT_NAME")

--T_EMP 테이블 자료 입력

INSERT INTO T_EMP VALUES('AAA','아이유','F','A@A.COM','2000-10-10',10);
INSERT INTO T_EMP VALUES('BBB','수지','F','B@A.COM','2010-10-10',20);
INSERT INTO T_EMP VALUES('CCC','송중기','M','C@A.COM','2020-10-10',30);

INSERT INTO T_EMP VALUES('DDD','윤도현','A','E@A.COM','2020-10-10,30);
==> check constrain (AA01.sys_c007657) violated
==> 오류발생시 제약조건 테이블에서 제약조건이름으로 확인

SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'T_EMP';

INSERT INTO T_EMP VALUES ('FFF','이승민','M','F@A.COM','2020-10-10',55);
==>integrity constraint (AA01_SYS_C007660) violated - parent key not found

---------------------------------------------------------------------------------------------

# 제약조건 기술 방법
-- 컬럼 레벨 기술 : 위에서 기술한 방법
-- 테이블 레벨 기술

-- 컬럼 레벨 기술
CREATE TABLE T_EMP(
ID VARCHAR2(30) PRIMARY KEY
,NAME VARCHAR2(50) NOT NULL
,GENDER CHAR(1) CHECK(GENDER IN ('F','M'))
,EMAIL VARCHAR2(100) UNIQUE
,HIRE_DATE DATE DEFAULT SYSDATE
,DEPT_ID NUMBER(3) REFERENCES T_DEPT(DEPT_ID)
); 

-- 테이블 레벨 기술
-- NOT NULL 제약조건은 반드시 컬럼 레벨에 기술할 것
-- 기본키가 복합키인 경우는 반드시 테이블 레벨에 기술해야 된다!!

CREATE TABLE T_EMP(
ID VARCHAR2(30)
,NAME VARCHAR2(50) NOT NULL
,GENDER CHAR(1)
,EMAIL VARCHAR2(100)
,HIRE_DATE DATE DEFAULT SYSDATE
,DEPT_ID NUMBER(3)

CONSTRAINT TEMP_PK PRIMARY KEY(ID), 
CONSTRAINT TEMP_GENDER_CK CHECK(GENDER IN ('F','M')),
CONSTRAINT TEMP_EMAIL_UK UNIQUE(EMAIL),
CONSTRAINT TEMP_DEPT_FK FOREIGN KEY(DEPT_ID) REFERENCES T_DEOT(DEPT_ID)
); 

-- 기본키가 복합키인 경우는 반드시 테이블 레벨에 기술해야 된다!!

CREATE TABLE TEST(
A CHAR(1) PRIMARY KEY,
B CHAR(1) PRIMARY KEY,
C CHAR(1)
);
==> table can have only one primary key 오류 발생 !!!

CREATE TABLE TEST(
A CHAR(1),
B CHAR(1),
C CHAR(1),

CONSTRAINT TEST_PK PRIMARY KEY(A,B)
);//검색을 많이 하는 것을 앞에 둔다!!!

----------------------------------------------------------------------------
# 제약조건 비활성화 / 활성화
-- 모든 제약조건 기본은 활성화
-- 검증된 자료를 다른 테이블로 이관할 때

--비활성화

ALTER TABLE 테이블명
DISABLE CONSTRAINT 제약조건명;

INSERT INTO T_EMP VALUES('GGG','최치원','A','G@A.COM','2020-10-10,30);
===> 제약조건을 비활성화 했기 때문에 입력됨

-- 활성화

ALTER TABLE 테이블명
ENABLE CONSTRAINTS 제약조건명;

---------------------------------------------------------------------------------------------------

## 테이블 생성 방법

1.CREATE TABLE 테이블명 (......);

2.CREATE TABLE TT
  AS
  SELECT * FROM T_EMP;
  ==> 스키마 + 자료 복사함
  ==> 제약조건중 NOT NULL만 복사됨

3.기존 테이블의 스키마 + 자료 일부 복사
CREATE TABLE TTT
AS
SELECT * FROM EMP
WHERE SALARY>=15000;

4.기존 테이블의 스키마만 복사
CREATE TABLE TTTT
AS
SELECT * FROM EMP
WHERE 1 <> 1;