[오라클(ORACLE)] 테이블 작성(CREATE),수정(ALTER),삭제(DROP) 개념 및 실습

인사 테이블 실습자료 쿼리.txt

INSA 테이블 쿼리문 은 첨부 파일로 올렸습니다. 그대로 사용하시면 될거같아요

오라클 테이블작성과 수정 그리고 삭제  에 대한 개념과 실습 내용 들입니다.

//*****************************************

-- 데이터 딕셔너리(Data Dictionary)

   -- TABLE과 VIEW들의 집합으로 DATABASE에 대한 정보를 제공하며,  DATABASE 생성시 SYS schema 안의 내부 TABLE로 구성

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

 SELECT COUNT(*) FROM DICTIONARY;

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

 SELECT COUNT(*) FROM USER_TABLES;

 SELECT COUNT(*) FROM TABS;

 SELECT * FROM TABS;  -- USER_TABLES 와 동일

 SELECT * FROM TAB;

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

-- 컬럼명 및 자료형등 출력

SELECT * FROM col WHERE tname='INSA';    -- 간단히 출력

SELECT * FROM cols WHERE table_name='INSA';

SELECT column_name, data_type, data_length,data_precision FROM user_tab_columns WHERE table_name='INSA';

SELECT column_name, data_type, data_length,data_precision FROM cols WHERE table_name='INSA';

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

-- 제약조건 확인

SELECT * FROM USER_constraints WHERE table_name='INSA';

SELECT constraint_name, table_name, constraint_type FROM user_constraints;

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

-- 사용자에게 주어진 권한

SELECT * FROM USER_sys_privs;

//*****************************************

-- 자료형

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

-- 문자 데이터 타입

   CHAR(n) : 고정길이 문자 데이터를 저장하며 최대 길이는 2000자이고 길이를 명시하지 않으면 기본 길이는 1

             NLS(국가별 언어 집합)는 한글과 영문만 가능 하다.

             남은 공간은 공백(space)로 채운다.

   VARCHAR2(n) : 가변 길이 문자 데이터를 저장하며 최대 길이는 4000자이고, 반드시 길이를 명시해야 한다.

                 NLS(국가별 언어 집합)는 한글과 영문만 가능하다.

                 VARCHAR 는 최대 2000개 문자를 저장하며 VARCHAR2와는 다르게 VARCHAR(10)로 선언하면  null을 채워 실제로는 10개의 공간을 사용한다. 하지만 VARCHAR2(10)는 필요한 문자까지만 저장하는 variable length이며 최대 4000개 문자까지 저장할 수 있다.

한글은 UTF-8(3byte)을 사용하여 저장하므로 VARCHAR2(9)는 한글 3글자만 저장 가능하다.

    NCHAR, NVARCHAR2 : 다양한 언어의 문자 값(국가별 언어 집합 : NLS)을 저장하고 조회할 수 있다.

    NVARCHAR2(2000)으로 지정하면 2000은 최대로 저장할 수 있는 글자수로 실제로는 4000byte를 확보(NVARCHAR2에 저장가능한 최대 byte)하며 유니코드 문자열은 한글이나 영어 모두 2000자까지 저장가능하다. 하지만 한글이 UTF-8로 저장되는 경우는 1333자 까지만 저장할 수 있다.

-- 숫자 데이터 타입

   NUMBER(P, S)

     - P(precision:1~38)는 정밀도로 전체 자리수를 나타내며 기본 값이 38이고 S(Scale:-84~127)는 소수점 이하의 자릿수이다.

     - 정수나 실수 저장하기 위한 가변길이의 표준 내부 형식이다.

     - NUMBER(p,s) 는 고정형 소수점 방식으로 지정된 범위 내의 수치 자료만 입력 가능하다. 즉, 최소값과 최대값이 존재하는 수치 자료를 입력할 때 사용되며 NUMBER 는 부동형 소수점 방식으로 값에 범위 없이 모든 수치 자료를 저장 가능하게 한다. 

   -- 사용 예

     - NUMBER(5, 2) → p > s : 소수점 자리를 포함한 수치 자료[±0.01 ~ 999.99]

     - NUMBER(4) → s = 0 : 정수부 수치 자료(Integer type)[± 0 ~ 9999]

     - NUMBER(2, 3 ) → p <= s : 소수부 수치 자료(Float type). 0 저장가능[± 0.001 ~ 0.099]

       소수점 3자리를 사용하며 수치 자료는 2자리만을 표현

     - NUMBER(3,-1) → s < 0  : 정수부의 지정 자릿수 만큼 0[ ± 10 ~ 9990 ]

     - NUMBER(10, 3) : 1234567.6789 입력 시 1234567.679가 저장되며 12345678.678 입력 시 Overflow 에러가 발생 한다.

     - 모든 NUMBER type 은 0 저장 가능

   FLOAT (n) : ANSI Datatype(1 <= n <= 126)

   BINARY_FLOAT : 32-bit floating point number(4 Byte)

   BINARY_DOUBLE : 64-bit floating point number(8 Byte)

 

   CREATE TABLE t1(

      col1  FLOAT,

      col2  BINARY_FLOAT,

      col3  BINARY_DOUBLE

    );

   INSERT INTO t1 VALUES (0.1234 , 0.1234 , 0.1234) ;

   SELECT * FROM t1;

    .1234   1.234E-001  1.234E-001

-- 날짜 데이터 타입

 DATE : 『년/월/일 시:분:초』까지 저장하며, 기본적으로 년/월/일 정보를 출력한다.

 TIMESTAMP : 『년/월/일 시:분:초.밀리초』까지 저장

-- 대용량 자료 저장 타입

  CLOB : 입력되는 데이터가 대용량의 텍스트 유형(doc, txt, hwp 등)을 가질 때 저장할 수 있는 타입(최대 4GB)

  BLOB : 입력되는 데이터가 이미지 유형 등의 이진 데이터를 저장할 수 있는 타입으로 4G 까지 저장 가능하다.

  CLOB, NCLOB, BLOB, BFILE 등은 11g에서는 최대 8TB to 128TB 가능[(4 Gigabytes - 1) * (database block size)]

//*****************************************

-- 테이블 작성

CREATE TABLE 테이블_이름 ( 

      컬럼명  데이터타입 [DEFAULT 표현식] [제약조건] 

      [, 컬럼명  데이터타입 [DEFAULT 표현식] [제약조건] ] 

      [ ,...]

      [ ,CONSTRAINT 제약조건명 제약조건]

) [TABLESPACE tablespace명];

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

우편번호, 학번, 사원번호, 전화번호 => 문자

엔티티명 : member

엔티티성격 : 테이블

구조

   컬럼명      자료형  크기    제약조건

   아이디      문자    10     기본키

   패스워드     문자   500    NOT NULL

   이름        문자   30     NOT NULL

   생년월일     날짜 

   전화번호     문자    25

   포인트       숫자   10

CREATE  TABLE  member (

    id       VARCHAR2(10)

,pwd    VARCHAR2(500)   NOT  NULL

,name   VARCHAR2(30)    NOT   NULL

,birth  DATE

,tel    VARCHAR2(25)

,point  NUMBER(10)

,CONSTRAINT  pk_member_id  PRIMARY  KEY(id)

);

-- 테이블명 확인

SELECT * FROM tab;

-- 테이블 구조 확인

SELECT * FROM col WHERE tname = 'MEMBER';

-- 제약조건 확인

SELECT * FROM user_constraints WHERE table_name = 'MEMBER';

   

//*****************************************

-- 테이블에 필드 추가

   ALTER TABLE 테이블이름 ADD (컬럼명 datatype [DEFAULT 값] [, 컬럼명 datatype]...);

   -- bigo VARCHAR2(100) 추가

   ALTER TABLE member ADD (bigo VARCHAR2(100));

   

   SELECT * FROM col WHERE tname = 'MEMBER';

//*****************************************

-- 테이블의 컬럼폭 및 자료형 변경

    ALTER TABLE 테이블명 MODIFY (컬럼명 datatype [DEFAULT 값]  [, 컬럼명 datatype]...);

   -- bigo 의 폭을 200으로 

   ALTER TABLE member MODIFY (bigo VARCHAR2(200));

   

    SELECT * FROM col WHERE tname = 'MEMBER';

    -- 컬럼에 데이터가 존재하는 경우 컬럼의 폭을 줄일 경우 존재하는 데이터의 가장 긴 길이보다는 크거나 같아야 한다.

-- 컬럼에 데이터가 존재하는 경우 데이터와 컬럼의 자료형이 일치해야 자료형 변경이 가능하다.

   

//*****************************************

-- 테이블의 필드명 변경

   ALTER TABLE 테이블명 RENAME COLUMN 변경전컬럼명 TO 새로운컬럼명;

   

   -- bigo => memo

   ALTER TABLE member RENAME COLUMN bigo TO memo;

   

   SELECT * FROM col WHERE tname = 'MEMBER';

   

//*****************************************

-- 테이블의 필드 삭제

   ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

   -- memo 삭제

   ALTER TABLE member DROP COLUMN memo;

   

   SELECT * FROM col WHERE tname = 'MEMBER';

//*****************************************

-- 테이블명 변경 : 테이블, 뷰, 시퀀스, 시너님의 이름을 변경

   RENAME old_name TO new_name;

   RENAME member TO join;

   

   SELECT * FROM TAB;

   

//*****************************************

-- 기존 테이블을 이용하여 테이블 작성

    -- 제약조건중 NOT NULL 속성만 복사

    CREATE TABLE 테이블명 [(컬럼명 , 컬럼명 ,...)] AS subquery;

CREATE TABLE insa2 AS

  SELECT  num, name, TO_DATE(SUBSTR(ssn, 1, 6)) birth, basicpay, sudang,

        basicpay+sudang pay

    FROM insa;

    SELECT * FROM insa2;

SELECT * FROM col WHERE tname = 'INSA2';

SELECT * FROM user_constraints WHERE table_name = 'INSA';

SELECT * FROM user_constraints WHERE table_name = 'INSA2';

-- 구조만 복사(자료 X)

CREATE TABLE insa3 AS

     SELECT * FROM insa WHERE 1=0;

    SELECT * FROM insa3;  

//*****************************************

-- 테이블 삭제

DROP TABLE 테이블명 [CASCADE CONSTRAINTS] [PURGE];

DROP TABLE insa3;  -- 휴지통에 버림

DROP TABLE insa2;  -- 휴지통에 버림

SELECT * FROM tab;

DROP TABLE  join  PURGE;  -- 완전 삭제

SELECT * FROM tab;

//*****************************************

-- 가상 컬럼 테이블 생성

   가상 컬럼은 테이블에있는 하나 이상의 컬럼값을 기반으로 계산식을 적용할 수 있는 기능(11g 부터 가능)

※ 특징 

  - 가상 컬럼은 데이터베이스 내의 메타데이터로 저장된다.

  - 물리적인 공간을 차지 하지 않는다.

  - 파티션 테이블 사용 시 적당한 컬럼이 없을 경우 가상 컬럼을 통해 적용 가능하다.

  - 가상 컬럼에 인덱스(함수 기반 인덱스) 생성이 가능하며, 통계 정보 수집도 가능하다.

  - 다른 테이블의 컬럼을 이용한 가상 컬럼을 생성할 수 없다.

  - 가상 컬럼에는 데이터를 INSERT 할 수 없다.

※ 형식 

   컬럼명 자료형 GENERATED ALWAYS AS ( 수식 ) VIRTUAL

//*****************************************

-- 휴지통

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

-- 휴지통 확인

SHOW RECYCLEBIN;  -- 휴지통 확인하기(사용자마다 따로 갖고있다)

SELECT object_name, original_name, droptime, dropscn

    FROM RECYCLEBIN;

-- 휴지통 복원

FLASHBACK TABLE  삭제전테이블명 TO BEFORE DROP;

FLASHBACK TABLE "BIN 이름" TO BEFORE DROP;

-- insa2, insa3 복원

-- 휴지통 복원(다른 이름으로 복구)

FLASHBACK TABLE 삭제전테이블명 TO BEFORE DROP RENAME TO 새로운테이블명;

-- 삭제 테이블 복구 후 제약조건(기본키) 확인

SELECT  a.table_name

       ,b.column_name

       ,a.constraint_name

       ,a.constraint_type

       ,a.search_condition

FROM   user_constraints a

JOIN   user_cons_columns b

ON     a.constraint_name = b.constraint_name

WHERE  a.table_name      = '테이블명'

AND    a.constraint_type = 'P';

  복구된 테이블에 대한 제약정보를 확인하게 되면 “BIN$9~” 형식으로 표시된다. 해당 제약조건 이름을 원래의 이름으로 변경한다. 변경을 하지 않아도 동작에는 문제가 없지만, 추후 유지보수를 위한 식별이 가능하도록 변경한다.

ALTER TABLE 테이블이름 RENAME CONSTRAINT "변경할제약조건이름" TO 새로운제약조건이름;

-- 휴지통 전체 비우기

PURGE RECYCLEBIN;

DROP TABLE insa2;

DROP TABLE insa3;

SELECT * FROM tab;

PURGE RECYCLEBIN;

SELECT * FROM tab;

-- 특정 테이블만 비우기

PURGE TABLE 비울테이블명;

-- 휴지통에 버리지 않고 바로 테이블 삭제

DROP TABLE 테이블명 PURGE;