데이터베이스 - 테이블 생성과 삭제

메모장 SQL문 입력 - 저장 ****.sql // 모든파일 - C:\Users\st09\sql에 저장 - @****.sql 메모장에 입력한 명령어 실행

 

CREATE TABLE

ALTER TABLE - VIEW 가 없다. - WHY? 물리적인 공간을 차지하지 않기때문이다.

DROP TABLE

 

TABLE, INDEX, SQEUENCE //////////////////////////// , VIEW ....... |  USER, TABLESPACE ......

  SEGMENT(물리적인 영역을 차지하는 요소)

 

테이블 생성과 삭제

 

SQL> CREATE TABLE 테이블 ( 
2 컬럼 데이터_타입 [DEFAULT default값] [컬럼 레벨 제약조건],
3 컬럼 데이터_타입 [DEFAULT default값] [컬럼 레벨 제약조건],
4 ...... 

5 [테이블 레벨 제약조건],
6 ..... 

7 );

 

- 데이터_타입
 . 컬럼에 입력될 데이터의 종류와 크기를 결정한다.
- DEFAULT 
 . 입력이 누락됐을 때 기본 입력 값을 정의한다.
 . Default 값을 지정하지 않으면 널 값이 저장된다.
- 컬럼 레벨 제약 조건
 . PK, FK, UK, CHECK, NOT NULL등을 지정한다.

- 테이블 레벨 제약 조건 
 . PK, FK, UK, CHECK만 지정한다.
 . NOT NULL은 정의할 수 없다. 

 

SQL> DROP TABLE 테이블 - 테이블을 삭제하면 PURGE RECYCLEBIN으로 간다
2 [CASCADE CONSTRAINT];

 

SQL> PURGE RECYCLEBIN; - 휴지통

 

*******************************

SQL> SELECT table_name 
2 FROM user_tables;                  중요!

*********************************

 

- select * from tab;과 같다.

dba : sys

users : 일반 사용자 

all :  

 

SQL> SELECT table_name, column_name, data_type, data_length
 2 FROM user_tab_columns
 3 [WHERE table_name = '테이블']; 

 

- desc 테이블명;과 같다.

WHERE 

 

 

* 테이블 생성에서 이름 규칙
 - 문자로 시작한다.
 - 30자 이내로 한다.
 - 영문, 숫자, _, $, #만을 사용한다.
   . 한글 사용은 가능하지만 되도록 사용하지 않는 것이 좋다.
 - 테이블의 이름은 동일한 유저(스키마) 안에서 유일해야 한다.
 - 예약어는 사용이 불가능하다.
 - 대소문자를 구별하지 않는다.

   . 생성할 때 사용한 문자와는 관계없이 모든 이름은 대문자로 정의된다.
   . 테이블 이름은 딕셔너리에 저장되는데, 모두 대문자로 저장된다.
* 데이터 타입
 - 오라클은 다양한 데이터 타입을 제공한다. 다음은 그 중 많이 사용되는 데이터 타입들이다.
 - 문자 타입
   . VARCHAR2, CHAR, LONG, CLOB
 - 숫자 타입 
   . NUMBER
 - 날짜 타입
   . DATE
 - 이진 타입
   . RAW, LONG RAW, BLOB, BFILE

 

 - ROWID 타입

   . ROWID - 테이블에 저장된 행 / 물리적인 위치값

 

EX)

SQL> SELECT ROWID, DNO, DNAME, LOC FROM DEPT;

ROWID              DNO  DNAME      LOC
------------------ ---- ---------- ------
AAAT7DAAFAAAAPnAAA 01   총무       서울    - 저장되어있는 위치
AAAT7DAAFAAAAPnAAB 02   회계       서울
AAAT7DAAFAAAAPnAAC 10   ERP        서울
AAAT7DAAFAAAAPnAAD 20   ISP        부산
AAAT7DAAFAAAAPnAAE 30   ITEA       광주
AAAT7DAAFAAAAPnAAF 40   CRM        대전
AAAT7DAAFAAAAPnAAG 50   POS

 

SQL> SELECT * FROM DEPT
  2  WHERE ROWID = 'AAAT7DAAFAAAAPnAAA';
DNO  DNAME      LOC
 ----    ----------       ------
   01   총무          서울

 

VARCHAR2(n)

- 가변 길이 문자 타입 (1 < n < 4000 byte)
CHAR(n) 

- 고정 길이 문자 타입 (1 < n < 2000 byte)
NUMBER(n,p)
- 숫자 타입, n은 전체 자리수이고 p는 소수점 이하 자리수이다.

- 전체 자리수를 초과할 경우 입력 거부되지만 소수점 이하 자리수가 초과되면 반올림되어 입력된다. 

DATE

- 날짜 타입, 출력이나 입력 형식과 무관하게 YYYY/MM/DD:HH24:MI:SS 형태로 저장된다. 

  (기원전 4712년 1월1일 ~ 서기 9999년 12월 31일)
LONG
- 2GB까지 저장 가능한 가변길이 문자 타입, 단 조건 검색할 수 없다.

- 테이블에는 하나의 LONG 컬럼만 정의할 수 있다.

CLOB
 - LONG를 개선한 타입, 최대 4G까지 저장 가능하고 한 테이블의 여러 컬럼에 정의할 수 있다. 

BLOB 

 - 4G 까지 저장 가능한 가변길이 이진 타입
ROW(n)

- 가변길이 이진 타입 (n < 2000)
BFILE

- 외부 파일 저장을 위한 이진 타입, 4G 이내 파일을 저장한다.

ROWID
- ROWID를 저장하기 위한 데이터 타입, 주로 PLSQL 프로그래밍에서 많이 사용되며 

  각 문자는 64진수로 엔코딩 되어있다.

 

예제 1. 다음과 같은 구조의 테이블을 생성한다.(데이터 타입은 입력될 내용에 따라 결정한다.)
 * 테이블명 : board
 - 컬럼 구성 : no(게시물번호), name(작성자), sub(제목), content(내용), hdate(입력일시)

 

SQL> CREATE TABLE board ( 

 2 no NUMBER,
 3 name VARCHAR2(50),
 4 sub VARCHAR2(100),
 5 content VARCHAR2(4000),
 6 hdate DATE DEFAULT SYSDATE
 7 );
테이블이 생성되었습니다. 

 

SQL> SELECT table_name FROM user_tables
 2 WHERE table_name = 'BOARD';
TABLE_NAME
--------------------
BOARD

 

SQL> DESC board; 

이름                    널?     유형
---------------------- -------- ---------------
NO                                NUMBER
NAME                           VARCHAR2(50)
SUB                              VARCHAR2(100)
CONTENT                    VARCHAR2(4000)
HDATE                         DATE

SQL> SELECT table_name, column_name, data_type, data_length
 2 FROM user_tab_columns
 3 WHERE table_name = 'BOARD';
TABLE_NAME   COLUMN_NAME   DATA_TYPE   DATA_LENGTH
-------------------   ------------------------   -----------------   ----------------------
BOARD             NO                          NUMBER                               22
BOARD             NAME                     VARCHAR2                           50
BOARD             SUB                        VARCHAR2                         100
BOARD             CONTENT              VARCHAR2                       4000
BOARD             HDATE                   DATE                                       7

 

SQL> INSERT INTO board (no) VALUES (1);
1 개의 행이 만들어졌습니다. 

 

SQL> COMMIT;
커밋이 완료되었습니다. 

 

SQL> SELECT * FROM board;

NO        NAME      SUB          CONTENT         HDATE

----        ----------     -------         ---------------        ----------
   1                                                               2018/01/04

 

* 데이터 딕셔너리(Data Dictionary)
데이터 딕셔너리는 데이터베이스의 상태나 내부 운영과 관련된 정보를 저장해주는 테이블이다. 

 

* 딕셔너리 이름의 접두어
- DBA_ : 관리자만 검색 가능한 딕셔너리에 붙는 접두어
 (DBA_TABLES, DBA_INDEXES, DBA_TABLESPACES, DBA_DATA_FILES ...) 

- ALL_ : 일반 사용자가 검색 가능한 딕셔너리, 접근 가능한 개체에 대한 정보를 담고 있다.
 (ALL_TABLES, ALL_CONSTRAINTS, ALL_INDEXES, ALL_VIEWS, ...) 

- USER_ : 일반 사용자가 검색 가능한 딕셔너리, 소유하고 있는 개체에 대한 정보를 담고 있다.
 (USER_TABLES, USER_CONSTRAINTS, USER_INDEXES, USER_VIEWS, ...)

 

예제 2. 테이블을 생성하고 데이터를 입력 확인한다. 

SQL> CREATE TABLE t1 (no NUMBER(4,2)); 

테이블이 생성되었습니다.

 

SQL> INSERT INTO t1 VALUES (12.12);
1 개의 행이 만들어졌습니다. 

 

SQL> INSERT INTO t1 VALUES (1.789); 
1 개의 행이 만들어졌습니다. 

 

SQL> SELECT * FROM t1;
NO
----------

12.12
1.79

 

SQL> INSERT INTO t1 VALUES (123.1);
INSERT INTO t1 VALUES (123.1)
 *
1행에 오류:
ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다. 

 

SQL> CREATE TABLE t2 (name VARCHAR2(3));

테이블이 생성되었습니다.

 

SQL> INSERT INTO t2 VALUES ('AAA');
1 개의 행이 만들어졌습니다. 

 

SQL> INSERT INTO t2 VALUES ('장강'); 
INSERT INTO t2 VALUES ('장강')
 *
1행에 오류:
ORA-12899: "ST"."T2"."NAME" 열에 대한 값이 너무 큼

 

SQL> INSERT INTO t2 VALUES ('장');
1 개의 행이 만들어졌습니다. 

 

SQL> SELECT * FROM t2;
NAME
----
AAA
장

 

예제 3. 다음 예제를 통해 CHAR와 VARCHAR2의 차이를 이해해 보자
SQL> CREATE TABLE comp( 
 2 co1 CHAR(4),
 3 co2 VARCHAR2(4)
 4 );
테이블이 생성되었습니다. 

 

SQL> INSERT INTO comp VALUES ('AA','AA'); 
1 개의 행이 만들어졌습니다. 

 

SQL> SELECT LENGTHB(co1), LENGTHB(co2) FROM comp;
LENGTHB(CO1)   LENGTHB(CO2)
----------------------   -----------------------
                        4                            2

 

SQL> INSERT INTO comp VALUES ('AAAA','AAAA');

1 개의 행이 만들어졌습니다. 

 

SQL> SELECT * FROM comp; 
CO1     CO2
------     ------
AA        AA
AAAA   AAAA

 

SQL> SELECT * FROM comp WHERE co1='AA';
CO1   CO2
------   ------
AA      AA

 

SQL> SELECT * FROM comp WHERE co2='AA';
CO1   CO2
------   ------
AA      AA

 

SQL> SELECT * FROM comp WHERE co1=co2;
CO1     CO2
------     -------
AAAA   AAAA

 

예제 4. 다음 실습을 통해 DATE 타입에 대해서 이해해 보자
SQL> CREATE TABLE hd (
 2 no NUMBER,
 3 hdate DATE
 4 );
테이블이 생성되었습니다. 

 

SQL> INSERT INTO hd VALUES (1, sysdate);
1 개의 행이 만들어졌습니다. 

 

SQL> SELECT * FROM hd;
 NO           HDATE
------          ----------
    1      2018/01/04

 

날짜만 쓰는 명령어는 시분초를 입력하면 안된다.

SQL> SELECT * FROM hd WHERE hdate = '2018/01/04';
선택된 레코드가 없습니다. 

 

SQL> SELECT no, TO_CHAR(hdate,'YYYY/MM/DD:HH24:MI:SS') FROM hd;
 NO           TO_CHAR(HDATE,'YYYY
-----            ----------------------------------
    1                    2018/01/04:23:23:07

 

SQL> SELECT * FROM hd 
 2 WHERE hdate BETWEEN '2018/01/04' AND '2018/01/05'; 
 NO         HDATE
-----         ----------
    1    2018/01/04

 

SQL> SELECT * FROM hd 
 2 WHERE TRUNC(hdate) = '2018/01/04';
 NO       HDATE
------      ----------
 1     2018/01/04

 

SQL> SELECT * FROM hd 
 2 WHERE hdate like '2018/01/04%';

 

 

예제 5. 다음에 제시된 테이블을 생성한다. 테이블명이나 컬럼의 이름, 컬럼의 길이는 임의로 정한다.
 - 테이블명 : 고객
 - 컬럼 : 고객관리번호, 고객명, 주소, 전화 
* C:\sql\client.sql 파일 생성

파일삭제 - 휴지통 비우기 - 테이블 생성