테이블 생성과 데이터 타입

CREATE TABLE employees (
    id         NUMBER        PRIMARY KEY,
    name       VARCHAR2(100) NOT NULL,
    email      VARCHAR2(200) UNIQUE,
    department VARCHAR2(50)  DEFAULT '미배정',
    salary     NUMBER(10,2),
    hire_date  DATE          DEFAULT SYSDATE
);

CREATE TABLE [스키마.]테이블명 (
    컬럼명  데이터타입  [DEFAULT 기본값]  [제약조건],
    컬럼명  데이터타입  [DEFAULT 기본값]  [제약조건],
    ...
    [테이블 수준 제약조건]
);

1. 영문, 숫자, 밑줄(_)만 사용 (한글 가능하지만 비권장)
2. 첫 글자는 영문이어야 함
3. 예약어(SELECT, TABLE 등) 사용 불가
4. 같은 스키마 내에서 테이블명 중복 불가
5. 같은 테이블 내에서 컬럼명 중복 불가
6. 대소문자 구분은 DBMS에 따라 다름
   Oracle: 대문자로 저장 (큰따옴표 사용 시 원본 유지)
   MySQL: OS에 따라 다름 (Linux: 구분, Windows: 비구분)
   PostgreSQL: 소문자로 저장 (큰따옴표 사용 시 원본 유지)

CHAR(10)에 'abc' 저장:     'abc       '  (7바이트 공백 패딩)
VARCHAR(10)에 'abc' 저장:   'abc'         (3바이트만 사용)

CHAR가 적합한 경우:
  * 고정 길이 코드: 우편번호(5자리), 국가코드(2자리)
  * 성별(M/F), Y/N 플래그
  * 길이가 항상 같은 해시값

VARCHAR가 적합한 경우:
  * 이름, 이메일, 주소 등 가변 길이 데이터
  * 대부분의 문자열 데이터

VARCHAR 최대 길이:
  Oracle VARCHAR2: 4000바이트 (확장 시 32767)
  MySQL VARCHAR: 65535바이트 (행 전체 크기 제한)
  PostgreSQL VARCHAR: 약 1GB (실질적으로 무제한)

Oracle:
  * BOOLEAN 타입 없음 (PL/SQL에서만 사용 가능)
  * NUMBER(1) 또는 CHAR(1)로 대체
  * 0/1 또는 'Y'/'N' 관례

MySQL:
  * BOOLEAN = TINYINT(1) (내부적으로 0과 1)
  * TRUE = 1, FALSE = 0

PostgreSQL:
  * 네이티브 BOOLEAN 타입 지원
  * TRUE, FALSE, NULL

-- 컬럼 추가
ALTER TABLE employees ADD phone VARCHAR2(20);
ALTER TABLE employees ADD (phone VARCHAR2(20), address VARCHAR2(300));

-- 컬럼 수정 (타입, 크기 변경)
ALTER TABLE employees MODIFY name VARCHAR2(200);

-- 컬럼 삭제
ALTER TABLE employees DROP COLUMN phone;

-- 컬럼 이름 변경
ALTER TABLE employees RENAME COLUMN name TO full_name;  -- Oracle
ALTER TABLE employees CHANGE name full_name VARCHAR(200); -- MySQL

-- 테이블 이름 변경
ALTER TABLE employees RENAME TO staff;  -- Oracle
RENAME TABLE employees TO staff;        -- MySQL

-- 제약 조건 추가
ALTER TABLE employees ADD CONSTRAINT emp_dept_fk
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- 제약 조건 삭제
ALTER TABLE employees DROP CONSTRAINT emp_dept_fk;

-- 테이블 삭제 (구조 + 데이터 모두 삭제)
DROP TABLE employees;

-- Oracle: 휴지통에 보관 (복구 가능)
DROP TABLE employees;              -- 휴지통으로
FLASHBACK TABLE employees TO BEFORE DROP;  -- 복구
DROP TABLE employees PURGE;        -- 완전 삭제

-- TRUNCATE: 데이터만 삭제 (구조는 유지)
TRUNCATE TABLE employees;

-- Oracle: 글로벌 임시 테이블
CREATE GLOBAL TEMPORARY TABLE temp_results (
    id     NUMBER,
    value  VARCHAR2(100)
) ON COMMIT DELETE ROWS;  -- 커밋 시 데이터 삭제
-- ON COMMIT PRESERVE ROWS: 세션 종료 시 삭제

-- MySQL: 세션 임시 테이블
CREATE TEMPORARY TABLE temp_results (
    id     INT,
    value  VARCHAR(100)
);
-- 세션 종료 시 자동 삭제

-- PostgreSQL: 세션 임시 테이블
CREATE TEMPORARY TABLE temp_results (
    id     INTEGER,
    value  VARCHAR(100)
);

1. 복잡한 쿼리의 중간 결과 저장
2. ETL 작업 시 데이터 변환 단계
3. 보고서 생성을 위한 집계 데이터 임시 저장
4. 대량 데이터 처리 시 성능 최적화

-- 구조와 데이터 복사
CREATE TABLE employees_backup AS
SELECT * FROM employees;

-- 구조만 복사 (데이터 없이)
CREATE TABLE employees_template AS
SELECT * FROM employees WHERE 1 = 0;

-- 특정 조건의 데이터만 복사
CREATE TABLE senior_employees AS
SELECT id, name, salary, hire_date
FROM employees
WHERE hire_date < '2020-01-01';

복사되는 것: 컬럼명, 데이터 타입, 데이터
복사되지 않는 것: 기본키, 외래키, 인덱스, 트리거, 기본값
NOT NULL 제약은 복사되지만, 명시적 이름이 있는 제약은 복사되지 않음

→ 제약 조건은 ALTER TABLE로 별도 추가해야 합니다.
→ 인덱스가 필요하면 CREATE INDEX로 별도 생성합니다.

-- Oracle: 시퀀스
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, '김철수');
SELECT emp_seq.CURRVAL FROM DUAL;  -- 현재 값 확인

-- MySQL: AUTO_INCREMENT
CREATE TABLE employees (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
INSERT INTO employees (name) VALUES ('김철수');
SELECT LAST_INSERT_ID();  -- 방금 생성된 ID 확인

-- PostgreSQL: SERIAL / IDENTITY
CREATE TABLE employees (
    id   SERIAL PRIMARY KEY,          -- 전통적
    name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 표준
    name VARCHAR(100) NOT NULL
);

-- 회원 테이블
CREATE TABLE users (
    user_id    NUMBER PRIMARY KEY,
    username   VARCHAR2(50) NOT NULL UNIQUE,
    email      VARCHAR2(200) NOT NULL UNIQUE,
    password   VARCHAR2(200) NOT NULL,
    created_at DATE DEFAULT SYSDATE
);

-- 상품 테이블
CREATE TABLE products (
    product_id   NUMBER PRIMARY KEY,
    name         VARCHAR2(200) NOT NULL,
    price        NUMBER(10,2) NOT NULL CHECK (price > 0),
    stock        NUMBER DEFAULT 0 CHECK (stock >= 0),
    category     VARCHAR2(50),
    description  CLOB
);

-- 주문 테이블
CREATE TABLE orders (
    order_id    NUMBER PRIMARY KEY,
    user_id     NUMBER NOT NULL REFERENCES users(user_id),
    order_date  DATE DEFAULT SYSDATE,
    status      VARCHAR2(20) DEFAULT 'PENDING'
                CHECK (status IN ('PENDING','PAID','SHIPPED','DELIVERED','CANCELLED'))
);

-- 주문 상세 테이블
CREATE TABLE order_items (
    item_id    NUMBER PRIMARY KEY,
    order_id   NUMBER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id NUMBER NOT NULL REFERENCES products(product_id),
    quantity   NUMBER NOT NULL CHECK (quantity > 0),
    unit_price NUMBER(10,2) NOT NULL
);

1. 주문과 주문 상세를 분리한 이유
   하나의 주문에 여러 상품이 포함될 수 있음 (1:N 관계)
   정규화: 반복 그룹을 별도 테이블로 분리

2. unit_price를 order_items에 저장하는 이유
   상품 가격은 변동될 수 있음
   주문 시점의 가격을 보존해야 정확한 매출 계산 가능

3. ON DELETE CASCADE
   주문이 삭제되면 주문 상세도 함께 삭제
   고아 레코드(orphan record) 방지

4. CHECK 제약
   가격 > 0, 재고 >= 0, 수량 > 0
   비즈니스 규칙을 DB 수준에서 강제