##SQL 6일차 0504(테이블 생성, 제약조건)

##테이블 생성

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


STUDENT( S_NO, NAME, BIRTH )

CREATE TABLE STUDENT(
S_NO      CHAR(8)           PRIMARY KEY,
NAME     VARCHAR(100)   NOT NULL,
BIRTH     DATE
) ; 



desc student ;  => 스키마 보는거 


##테이블 구조 변경 : ALTER 

--ADD : 컬럼 추가
--MODIFY : 컬럼의 자료형 크기 확대
--RENAME COLUMN : 컬럼명 수정
--DROP : 컬럼 삭제 


##EMAIL, PHONE 컬럼 추가 
--현재 컬럼리스트 뒤로 추가 됨

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

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

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

##BIRTH -> BIRTHDAY 변경

ALTER TABLE STUDENT 
RENAME COLUMN BIRTH TO BIRTHDAY ; 

##PHONE 컬럼 삭제 

ALTER TABLE STUDENT 
DROP COLUMN PHONE ; 

ALTER TABLE STUDENT   ==> 실무에서 
DROP (PHONE) ; 

## 테이블 삭제 
-- 테이블 구조 + 입력자료 => 모두 삭제 

DROP TABLE STUDENT ; 

## 테이블의 구조는 남기고 입력자료만 삭제 

TRUNCATE TABLE STUDENT ; 

## 테이블 이름 변경 

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

RENAME TT1 TO TEST1 ;

##테이블 관련 딕셔너리 

USER_TABELS  ==(일부의 내용/편하게)==> TAB 

SELECT * FROM USER_TABLES ; 

 

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

##테이블 제약조건 

##제약조건 TYPE 
--P : PRIMARY KEY : 기본키 : 중복불가, 생략불가 
--R : FOREIGN KEY : 외래키 : 참조하는 테이블의 기본키 값 중에 하나가 와야한다, NULL 가능
--U : UNIQUE : 중복불가, NULL허용 
--C : NOT NULL : 생략불가 
--C : CHECK ( 조건식 ) : 데이터의 검증조건지정 , TRUE 경우만 입력가능 
--DEFAULT : 기본값 -> 값을 입력하지 않았을 경우 기본으로 넣어주는 값 

T_DEPT(DEPT_ID, DEPT_NAME) -> 2 

T_EMP(EMP_ID, EMP_NAME, GENDER, EMAIL, HIRE_DATE, DEPT_ID) -> 6 

## 제약조건을 추가하여 테이블 생성 : 컬럼 레벨의 제약조건 추가 
CREATE TABLE T_DEPT(
DEPT_ID  NUMBER(3)            PRIMARY KEY,
DEPT_NAME VARCHAR2(50)   NOT NULL,
);

CREATE TABLE T_EMP( 
EMP_ID         NUMBER(10)        PRIMARY KEY,
EMP_NAME    VARCHAR2(50)     NOT NULL,
GENDER        CHAR(1)              CHECK ( GENDER IN('F', 'M') ) NOT NULL  *콤마 안쓰고 스페이스바로 계속 쓰면됨
EMAIL           VARCHAR2(100)   UNIQUE,
HIRE_DATE     DATE        DEFAULT SYSDATE,
DEPT_ID       NUMBER(3)      REFERENCES T_DEPT( DEPT_ID )
);

## 제약조건을 추가하여 테이블 생성 : 테이블레벨의 제약조건 추가
## NOT NULL 제약조건 => 컬럼레벨에서만 기술 가능 
## 기본키가 복합키인 경우 (A, B)  => 반드시 테이블 레벨에서 제약조건 기술 

CREATE TABLE T_DEPT(
DEPT_ID         NUMBER(3)            ,
DEPT_NAME    VARCHAR2(50)   NOT NULL ,

CONSTRAINT T_DEPT_PK PRIMARY KEY (DEPT_ID)
);

CREATE TABLE T_EMP( 
EMP_ID         NUMBER(10)        ,
EMP_NAME    VARCHAR2(50)     NOT NULL,
GENDER        CHAR(1)              NOT NULL     
EMAIL           VARCHAR2(100)   UNIQUE,
HIRE_DATE     DATE        DEFAULT SYSDATE,
DEPT_ID       NUMBER(3)      REFERENCES T_DEPT( DEPT_ID )

CONSTRAINT T_EMP_PK                 PRIMARY KEY (   EMP_ID       ) ,
CONSTRAINT T_EMP_GENDER_CK     CHECK ( GENDER IN ('F', 'M') ) ,
);


##기본키가 복합키인 경우 (A,B) => 반드시 테이블 레벨에서 제약조건 기술 

CREATE TABLE TEST2 (
A NUMBER(2)   references emp(a) ,  
B NUMBER(3),         
C DATE,

CONSTRAINT TEST2_PK PRIMARY KEY ( A, B) 
) ; 






CREATE TABLE TEST2 (
A NUMBER(2)         PRIMARY KEY, 
B NUMBER(3)         PRIMARY KEY,         
C DATE 
) ; 

==> 오류발생 !!  table can have only one primary key

================실습=============
장르, 회원등급, 언어 , 나라 테이블 생성 
회원, 도서 테이블 생성 
제약조건 추가하여 생성하기

CREATE TABLE 장르 (
    장르번호  CHAR(4)  PRIMARY KEY,
    장르명  VARCHAR2(20)  NOT NULL
);
CREATE TABLE 회원등급 (
    회원등급번호  CHAR(10)  PRIMARY KEY,
    회원등급명  VARCHAR2(20),
    회원등급설명  VARCHAR2(100),
    회원등급조건  VARCHAR2(100),
    회원등급혜택  VARCHAR2(100)
);
CREATE TANLE 쿠폰 (
쿠폰번호  VARCHAR2(20) PRIMARY KEY,
쿠폰명  VARCHAR2(100) NOT NULL,
쿠폰설명  VARCHAR2(100),
쿠폰금액  NUMBER(10),
유효일  NUMBER(3)
);

CREATE TABLE 회원 (
    아이디  VARCHAR2(20)  RIMARY KEY,
    비밀번호  VARCHAR2(100)  NOT NULL,
    생일  DATE  NOT NULL,
    성별  CHAR(1)  NOT NULL,
    이메일  VARCHAR2(50),
    전화  VARCHAR2(15),
    추천인  VARCHAR(20),
    우편번호  CHAR(5)  NOT NULL,
    기본주소  VARCHAR2(200)  NOT NULL,
    나머지주소  VARCHAR2(200) NOT NULL,
    내외국인구분 CHAR(1)  NOT NULL,
    마일리지  NUMBER(10)          DEFAULT 0,
    선호장르1 CHAR(4)  REFERENCES 장르(장르번호),
    선호장르2           CHAR(4)  REFERENCES 장르(장르번호),
    회원등급번호       CHAR(10) REFERENCES 회원등급(회원등급번호),
    가입일  TIMESTAMP  DEFAULT SYSDATE
);

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

## 제약조건 기술시 제약조건명을 생략하면 
--시스템이 알아서 제약조건이름 준다 => SYS_Cxxxxxx
--제약조건명으로 어느 테이블에 어느 컬럼에 있는 무슨 제약조건 확인하기 힘들다. 
--그래서 제약조건명 명시적으로 지정해주면 좋다 

##제약조건명 기술시 예시
--테이블명_컬럼명_제약조건타입 => EMP_SALARY_CK
--제약조건타입_테이블명_컬럼명 => PK_EMP_EMPID

##제약조건 확인하기 

--USER_CONSTRAINTS

SELECT * 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'T_EMP' ; 
==> 테이블명 대문자 저장 


=======================================
## 제약조건을 비활성화 / 활성화 

--기본 => 활성화 

--비활성화 

ALTER TABLE T_EMP
DISABLE CONSTRAINT SYS_C00239062 ;           *제약조건 확인하기에서 따옴 

--활성화

ALTER TABLE T_EMP
ENABLE CONSTRAINT SYS_C00239062 ;

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

##테이블 생성 방법 

1. CREATE TABLE TEST ( ... ) ; 

2. CREATE TABLE TEST
   AS 
   SELECT * FROM T_EMP ;
=> 테이블 스키마 + 자료 => 복사됨 
=> 제약조건은 NOT NULL 만 복사됨 ! 

3. 기존 테이블 스키마 + 자료 일부 

CREATE TABLE TEST_EMP
AS 
SELECT * FORM EMP 
WHERE SALARY >= 10000 ; 

4. 기존 테이블 스키마 복사 

CREATE TABLE TEST_EMP
AS                                      
SELECT * FROM EMP 
WHERE 1 <> 1 ;      * 1 <> 1 => '무조건 안하겠다' , 1 = 1 => '무조건 하겠다' 
          
        |  | 

CREATE TABLE TEST_EMP
AS                                      
SELECT * FROM EMP  ; 

TRUNCATE TABLE TEST_EMP ;