Database_51_SQL 오라클 데이터 유형, 테이블 생성_숫자, 날짜, 문자, 이진

   

-- 오라클 데이터 유형
        -- 숫자
            -- NUMBER( p, s ) : 자릿수가 p이고 소수점 이하 자릿수가 s인 가변 길이의 숫자
        -- 날짜
            -- DATE : 날짜 및 시간
            -- DATETIME
                -- TIMESTAMP : 소수 표시 초단위 날짜로 시간을 저장합니다.
                    -- 데이터 타입을 변환하지 않아도 되는 유형입니다.
                -- INTERVAL YEAR TO MONTH : 년 및 월 간격으로 시간을 저장합니다.
                -- INTERVAL DAY TO SECOND : 일, 시, 분 및 초 간격으로 시간을 저장합니다.
                    -- 시간 간격을 분수로 작성하지 않아도 되는 유형입니다.
        -- 문자
            -- CHAR (n) : 고정 길이가 n 바이트인 문자 데이터(최대 2,000 바이트)
                -- 우편 번호, 학번 
            -- VARCHAR2 (n) : 가변 길이가 최대 n 바이트인 문자 데이터(최대 4,000 바이트)
            -- LONG / CLOB : 최대 4GB인 문자 데이터
        -- 이진 : 최대 4GB인 이진 데이터
            -- RAW (n) : 최대 2000 바이트  
                -- 카드 사진, 지문 인식
        -- ROWID : 테이블에 저장된 각 행의 고유한 주소를 표현하는 값입니다.
            -- 총 18개의 문자로 이루어져 있습니다.
            -- 암호화되어 있는 데이터입니다.
            -- 6-3-6-3 의 형식입니다.
                -- 6글자 : 테이블 정보입니다.
                -- 3글자 : 파일 정보입니다.
                    -- USERS 파일을 사용하고 있습니다.
                        -- 대부분의 테이블이 같은 파일에 저장될 수 있습니다.
                -- 6글자 : 파일 내의 블록의 위치 정보입니다.
                    -- 여러 테이블이 하나의 블록 내에 저장될 수 없습니다.
                -- 3글자 : 블록 내에서 저장되어있는 행의 번호 정보입니다.
                    -- 인덱스에 저장되는 값입니다. 
                    -- 인덱스를 통해 위치를 바로 알 수 있습니다.
        -- LARGE DATA
            -- LONG
                -- 현재는 사용하지 않습니다.
                -- 최대 2GB까지 저장할 수 있습니다.
                -- 한 테이블당 1개의 coulmn만 지정할 수 있습니다.
                -- 문자형 : LONG
                -- 이진형 : LONGRAW
            -- LOB_Large Object
                -- 최대 4GB까지 저장할 수 있습니다.
                -- 제한이 없습니다.
                -- 문자형 : CLOB
                -- 이진형 : BLOB
            -- BFILE : 최대 4GB의 이진 데이터에 대한 참조가 포함되어있는 Oracle LOB 데이터 형식입니다.
                -- 데이터가 서버가 아닌 운영 체제의 물리적 파일에 저장됩니다.
        
        -- LONG 타입의 한계
            -- 사원 테이블
                -- 사원의 사진 : LONGRAW
                -- 자격증 사본 : LONGRAW
                -- 자기소개서 : LONG
                    -- LONG 타입은 한 컬럼만을 지정할 수 있으므로 세 컬럼이 한 테이블에 저장될 수 없습니다.
                    -- 사원 테이블에 사원의 사진만 지정합니다.
                    <<<
            -- 사원 테이블
                -- 사번 (PK)
                -- 이름
                -- 연락처
            -- 자격증 테이블
                -- 사번 (PK, 외래키)
                -- 자격증 : LONGRAW
            -- 자기소개서
                -- 사번 (PK, 외래키)
                -- 자기소개서 : LONG
                    -- 사번을 통해 세개의 테이블을 조인해야 조회할 수 있습니다.
            -- 사진 테이블
                -- 사번 (PK, 외래키)
                -- 사진 : LONGRAW
            -- 테이블의 데이터를 조회할 때 모든 데이터를 불러온 뒤 지정한 컬럼의 값만을 출력하는 것입니다.
            -- 데이터를 세분화해야 필요하지 않는 데이터를 불러오는 것을 방지할 수 있습니다.
            -- 마스터 디테일이 있는 경우 모든 관련 테이블을 풀스캔하게 됩니다. > JOIN이 효율이 낮아집니다.
                -- JOIN 대신 사용할 수 있는 명령어를 개발합니다.
        
        -- LOB 타입
            -- 사원 테이블
                -- 사번
                -- 이름
                -- 연락처
                -- 사진 : BLOB
                -- 자격증 사본 : BLOB
                -- 자기소개서 : CLOB
            -- LOB SEGMENT : 오라클 서버가 자동으로 컬럼별로 저장 공간을 할당합니다.
                -- 실제 데이터가 저장됩니다.
                -- 테이블에는 포인터 값이 저장됩니다.( 실제 데이터의 위치 값 )
                -- 테이블이 아닌 단순 데이터 저장소입니다.
            -- 행단위로 데이터를 넘겨주므로 필요하지 않은 경우 포인터의 정보만 로딩됩니다.
                -- 포인터의 용량은 크지 않으므로 용량을 크게 사용하지 않습니다.
                -- 포인터가 인덱스의 역할을 하고있습니다.
                -- JOIN을 사용하지 않을 수 있습니다.
               
-- CREATE TABLE
    -- 권한이 필요합니다.
    -- 저장 영역
    /*
    CREATE TABLE [schema.]table(
                        column datatype [DEFAULT expr...]);
            -- column의 기본 설정을 정의합니다.
            -- DEFAULT : 널 값 대신 사용할 기본 값을 미리 정의할 수 있습니다.
    */
    -- 지정해야 할 내용
        -- 테이블 이름
        -- 열의 이름, 데이터 유형 및 크기
    --  DEFAULT 옵션
        -- 삽입을 수행할 때 사용할 열의 기본값을 지정합니다.
        -- 리터럴 값, 표현식 또는 SQL 함수는 유효한 값입니다.
        -- 다른 열의 이름이나 의사 열은 잘못된 값입니다.
        -- 기본 데이터 유형은 해당 열의 데이터 유형과 일치해야 합니다.
--------------------------------------------------------------------------------------------------------
    -- 잘 변하지 않는 부분들은 데이터베이스에 작성하는 것이 좋습니다.
    -- 웹 브라우저/ 애플리케이션 같은 경우 개편이 자주 되므로 계속 변경이 필요합니다.
    -- 데이터베이스에 기본적인 값을 설정해두면 코드의 양을 줄일 수 있습니다.
    -- 애플리케이션의 용량이 줄어들면 실행되는 데에 속도가 빨라질 수 있습니다.
    
    -- 데이터베이스 > WAS > WEB 세 분야로 구성되어 있었습니다.
    -- 이전의 개발 방식에서는 WAS에 집중되어 부하가 많이 발생했습니다.
    -- JAVA에서 WAS의 업무를 일부 처리하고 있습니다.
    -- 데이터베이스에서도 WAS의 업무를 일부 처리할 수 있다면 WAS의 부하를 줄일 수 있습니다.
--------------------------------------------------------------------------------------------------------       
-- 테이블을 생성합니다.
CREATE TABLE dept(
            deptno NUMBER(2),
            dname VARCHAR2(20),
            loc VARCHAR2(15));
    /*
    Table DEPT이(가) 생성되었습니다.
    */

SELECT * FROM dept; 
    -- 데이터는 존재하지 않습니다.

    
-- 데이터를 삽입합니다.
INSERT INTO dept
VALUES( 10, '총무', '서울' );
    /*
    1 행 이(가) 삽입되었습니다.
    */
    
INSERT INTO dept(deptno, dname)
VALUES( 20, '영업');
    /*
    1 행 이(가) 삽입되었습니다.
    */

SELECT * FROM dept;
    /*
    DEPTNO  DNAME   LOC
    ----------------------------------
    10              총무         서울
    20              영업        ( null )
    */

-------------------------------------------
----------관리자 데이터베이스-----------

-- 데이터베이스에 있는 객체정보를 확인합니다.
SELECT object_name, object_type, owner
FROM dba_objects
WHERE object_name='DEPT';   
    -- 딕셔너리에 저장되는 모든 데이터는 대문자로 검색해야 합니다.
    /*
   OBJECT_NAME  OBJECT_TYPE     OWNER
   ---------------------------------------------
   DEPT             TABLE               HR
   DEPT             TABLE               SCOTT
        -- 방금 생성한 HR 계정의 DEPT가 존재합니다.
        -- SCOTT 계정이 소유하는 DEPT 테이블이 존재합니다.
        -- 계정이 다른 경우 테이블의 이름이 중복될 수 있습니다.
    */

-------------------------------------------
----------인사관리 데이터베이스-----------

    -- 동일한 테이블을 생성하려고 합니다.
CREATE TABLE dept(
            deptno NUMBER(2),
            dname VARCHAR2(20),
            loc VARCHAR2(15));
    /*
        -- 이미 존재하는 테이블 명이므로 오류가 발생합니다.
        <오류>----------------------------------------------------------
        오류 보고 -
        ORA-00955: name is already used by an existing object
        00955. 00000 -  "name is already used by an existing object"
        *Cause:    
        *Action:
        ------------------------------------------------------------------
    */

-- 테이블을 삭제합니다.
DROP TABLE dept PURGE;
    /*
    Table DEPT이(가) 삭제되었습니다.
    */

SELECT * FROM dept;
    /*
        -- 테이블이 존재하지 않아 오류가 발생합니다.
        <오류>----------------------------------------------------------
        ORA-00942: table or view does not exist
        00942. 00000 -  "table or view does not exist"
        *Cause:    
        *Action:
        276행, 15열에서 오류 발생
        ------------------------------------------------------------------
    */


-------------------------------------------
----------관리자 데이터베이스-----------

-- 데이터베이스에 있는 객체정보를 확인합니다.
SELECT object_name, object_type, owner
FROM dba_objects
WHERE object_name='DEPT';   
    /*
    OBJECT_NAME  OBJECT_TYPE     OWNER
    ---------------------------------------------
    DEPT             TABLE               SCOTT
        -- 테이블을 삭제하는 순간 정보도 삭제됩니다.
        -- 결국 CREATE란 데이터 딕셔너리에 대한 DDL입니다.
    */

    
-------------------------------------------
----------인사관리 데이터베이스-----------

    -- 동일한 테이블을 생성하려고 합니다.
CREATE TABLE dept(
            deptno NUMBER(2),
            dname VARCHAR2(20),
            loc VARCHAR2(15),
            create_date DATE DEFAULT sysdate    -- 기본 값을 설정합니다.
            );
    /*
    Table DEPT이(가) 생성되었습니다.
    */

-------------------------------------------
----------관리자 데이터베이스-----------

-- 데이터베이스에 있는 객체정보를 확인합니다.
SELECT object_name, object_type, owner
FROM dba_objects
WHERE object_name='DEPT';   
    /*
   OBJECT_NAME  OBJECT_TYPE     OWNER
   ---------------------------------------------
   DEPT             TABLE               HR
   DEPT             TABLE               SCOTT
        -- 테이블 정보가 다시 추가되었습니다.
    */

    

-------------------------------------------
----------인사관리 데이터베이스-----------

INSERT INTO dept
VALUES (10, '총무', '서울', NULL);
    /*
    1 행 이(가) 삽입되었습니다.
    */
    
INSERT INTO dept( deptno, dname)
VALUES ( 20, '영업' );
    /*
    1 행 이(가) 삽입되었습니다.
    */
    
SELECT * FROM dept;
    /*
    DEPTNO DNAME    LOC     CREATE_DATE
    -----------------------------------------
    10          총무      서울      ( null )
    20          영업      ( null )    23/07/07
        -- 20번 데이터를 삽입할 때 LOC, CREATE_DATE의 값을 입력하지 않았습니다.
        -- CREATE_DATE는 NULL 값이 아닌 설정한 기본 값으로 저장됩니다.
    */

    
-- 테이블을 삭제합니다.
DROP TABLE dept PURGE;
    /*
    Table DEPT이(가) 삭제되었습니다.
    */

DESC employees;
    /*
    이름                   널?                 유형           
    --------------       --------       ------------ 
    EMPLOYEE_ID      NOT NULL    NUMBER(6)       : 정수 6자리
    FIRST_NAME                         VARCHAR2(20) 
    LAST_NAME        NOT NULL    VARCHAR2(25) 
    EMAIL               NOT NULL    VARCHAR2(25) 
    PHONE_NUMBER                   VARCHAR2(20) 
    HIRE_DATE         NOT NULL    DATE         
    JOB_ID              NOT NULL    VARCHAR2(10) 
    SALARY                              NUMBER(8,2)     : 정수 6자리 + 소수 2자리  
    COMMISSION_PCT                NUMBER(2,2)     : 소수 2자리
    MANAGER_ID                       NUMBER(6)       : 정수 6자리
    DEPARTMENT_ID                   NUMBER(4)       : 정수 4자리
    */

    
    
-- ROWID : 모든 데이터를 암호화하여 알려주는 것입니다.
SELECT employee_id, last_name, ROWID
FROM employees;
    /*
    EM_ID         LAST_NAME     ROWID        
    -----------------------------------------------------------
    214         Kim             AAAE5pAAEAAAADMAAB
    215         Lee             AAAE5pAAEAAAADMAAC
    114         Raphealy         AAAE5pAAEAAAADMAAD
    100         King             AAAE5pAAEAAAADPAAA
    101         Kochhar         AAAE5pAAEAAAADPAAB
    102         De Haan         AAAE5pAAEAAAADPAAC
    103         Hunold         AAAE5pAAEAAAADPAAD
    104         Ernst             AAAE5pAAEAAAADPAAE
    107         Lorentz         AAAE5pAAEAAAADPAAF
    124         Mourgos         AAAE5pAAEAAAADPAAG
    141         Rajs             AAAE5pAAEAAAADPAAH
    142         Davies         AAAE5pAAEAAAADPAAI
    143         Matos            AAAE5pAAEAAAADPAAJ
    144         Vargas         AAAE5pAAEAAAADPAAK
    149         Zlotkey         AAAE5pAAEAAAADPAAL
    174         Abel             AAAE5pAAEAAAADPAAM
    176         Taylor             AAAE5pAAEAAAADPAAN
    178         Grant             AAAE5pAAEAAAADPAAO
    200         Whalen         AAAE5pAAEAAAADPAAP
    201         Hartstein         AAAE5pAAEAAAADPAAQ
    202         Fay             AAAE5pAAEAAAADPAAR
    205         Higgins         AAAE5pAAEAAAADPAAS
    206         Gietz             AAAE5pAAEAAAADPAAT
    */

SELECT department_id, department_name, ROWID
FROM departments;
    /*
    DP_ID     DEPARTMENT_NAME     ROWID        
    --------------------------------------------------------------
    40         Human Resource         AAAE5lAAEAAAACrAAA
    30         Purchasing                 AAAE5lAAEAAAACrAAB
    10         Administration             AAAE5lAAEAAAACvAAA
    20         Marketing                 AAAE5lAAEAAAACvAAB
    50         Shipping                     AAAE5lAAEAAAACvAAC
    60         IT                             AAAE5lAAEAAAACvAAD
    80         Sales                         AAAE5lAAEAAAACvAAE
    90         Executive                 AAAE5lAAEAAAACvAAF
    110     Accounting                 AAAE5lAAEAAAACvAAG
    190     Contracting                 AAAE5lAAEAAAACvAAH
    70         Public Relations             AAAE5lAAEAAAACvAAI
    */

-- 테이블을 생성합니다.
CREATE TABLE dept(
            deptno NUMBER(2),
            dname VARCHAR2(20),
            loc VARCHAR2(15),
            create_date DATE DEFAULT sysdate    -- 기본 값을 설정합니다.
            );
    /*
    Table DEPT이(가) 생성되었습니다.
    */

-- 데이터를 삽입합니다.
INSERT INTO dept
VALUES (10, '총무', '서울', NULL);
    /*
    1 행 이(가) 삽입되었습니다.
    */
    -- 같은 행을 한 번 더 삽입합니다.

-- 커밋합니다.
COMMIT;
    /*
    커밋 완료.
    */
    
-- 조회합니다.
SELECT * FROM dept;
    /*
    DEPTNO DNAME    LOC     CREATE_DATE
    ---------------------------------------------
    10          총무      서울      ( null )
    10          총무      서울      ( null )
        -- 하나의 데이터만 삭제하고 싶으나 특정지을 수 있는 데이터가 없습니다.
    */

-- ROWID를 조회합니다.
SELECT deptno, dname, ROWID
FROM dept;
    /*
    DEPTNO  DNAME   ROWID
    -------------------------------------------------
    10         총무      AAAFUZAAEAAABP2AAA
    10         총무      AAAFUZAAEAAABP2AAB
    */

-- ROWID를 이용하여 데이터를 삭제합니다.
DELETE FROM dept
WHERE ROWID='AAAFUZAAEAAABP2AAB';
    /*
    1 행 이(가) 삭제되었습니다.
    */

-- 조회합니다.
SELECT * FROM dept;
    /*
    DEPTNO DNAME    LOC     CREATE_DATE
    ---------------------------------------------
    10          총무      서울      ( null )
    */

-- 커밋합니다.
COMMIT;
    /*
    커밋 완료.
    */

-- 테이블을 삭제합니다.
DROP TABLE dept PURGE;
    /*
    Table DEPT이(가) 삭제되었습니다.
    */





-- 서브 쿼리 구문을 사용한 테이블 생성
    /*
    CREATE TABLE table
            [(column, column...)]
    AS subquery;
    */
    -- 지정한 열의 수를 서브 쿼리 열 수와 일치시켜야 합니다.
    -- 열의 이름 및 기본값을 사용하여 열을 정의합니다.

-- 원하는 데이터를 조회합니다.
SELECT employee_id, last_name, salary*12, hire_date
FROM employees
WHERE department_id=80;
    /*
    EM_ID   LAST_NAME   SALARY*12   HIRE_DATE
    ------------------------------------------------------
    215         Lee             132000      99/11/05
    149         Zlotkey        126000      15/01/29
    174         Abel            132000      11/05/11 
    176         Taylor          103200      13/03/24
    */

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12, hire_date
FROM employees
WHERE department_id=80;
    /*
        <오류>---------------------------------------------------------------
        오류 보고 -
        ORA-00998: must name this expression with a column alias
        00998. 00000 -  "must name this expression with a column alias"
        *Cause:    
        *Action:
        -----------------------------------------------------------------------
            -- 표현식으로 값을 지정하기 위해서 salary*12는 필수적으로 별칭으로 작성해야 합니다.
    */

CREATE TABLE dept80
    AS
        SELECT employee_id, last_name, salary*12 annsal, hire_date
        FROM employees
        WHERE department_id=80;
    -- = CREATE TABLE dept80(empno, empname, annsal, hired) > 테이블 컬럼 이름을 지정해주어도 됩니다.
    /*
    Table DEPT80이(가) 생성되었습니다.
    */
    
DESC dept80;
    /*
    이름                  널?              유형           
    -----------         --------        ------------ 
    EMPLOYEE_ID                      NUMBER(6)    
    LAST_NAME       NOT NULL    VARCHAR2(25) 
    ANNSAL                             NUMBER       
    HIRE_DATE       NOT NULL     DATE         
    */

SELECT * FROM dept80;
    /*
    EM_ID   LAST_NAME     ANNSAL    HIRE_DATE
    ------------------------------------------------------
    215         Lee             132000      99/11/05
    149         Zlotkey        126000      15/01/29
    174         Abel            132000      11/05/11 
    176         Taylor          103200      13/03/24
        -- 행 복사를 막기 위해서는 WHERE 1=2; ???
    */