SQL-테이블 생성과 데이터 타입의 이해

* 테이블 생성과 삭제

CREATE ALTER DROP를 묶어서 이해해야한다. 

 

형식

CREATE TABLE 테이블 (       
컬럼 데이터_타입 [DEFAULT default값] [컬럼 레벨 제약조건],
컬럼 데이터_타입 [DEFAULT default값] [컬럼 레벨 제약조건],
.....
[테이블 레벨 제약조건],
.....
);

 

형식에 대한 설명

데이터_타입 : 컬럼에 입력될 데이터의 종류와 크기를 결정.

DEFAULT : 입력이 누락됐을 때 기본 입력 값을 정의한다.  Default 값을 지정하지 않으면 NULL 값이 저장.

컬럼 레벨 제약 조건 : PK, FK, UK, CHECK, NOT NULL등을 지정. ex) 나이는 19살 이상만 입력 가능

- PK : 프라이머리 키. NULL값과 중복된 값이 들어갈 수 없다.

- FK : 다른 테이블에

- UK : 중복된 값이 들어갈수 없다. 단, NULL은 해당 안됨. 

- CHECK : 조건에 맞는 데이터만 입력됨

- NOT NULL : NULL값을 지정 할 수 없다.

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

제약조건은 통상적으로는 테이블을 생성할때 조건을 주는게 아니고 다른곳에서 따로 지정해준다. 추후 학습 예정

 

DROP TABLE 테이블 // 참조 당하는 테이블은 참조 하는 테이블보다 먼저 삭제 될 수 없다.
[CASCADE CONSTRAINT];

 

PURGE RECYCLEBIN; // 윈도우로 치면 휴지통 비우기.

 

SELECT table_name FROM user_tables;

-> DB내의 모든 테이블 정보 보기. SELECT * FROM tab;과 같은 기능. 앞으로는 이 명령어를 써야 한다.

    user_tables는 테이블의 정보를 조회해주는 Data Dictionary 

 

SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns

[WHERE table_name = '테이블']; // 해당 테이블의 내용을 보여주는 명령어. desc와 같은 명령어

 

테이블 생성에서 이름 규칙

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

- 생성할 때 사용한 문자와는 관계없이 모든 이름은 대문자로 정의된다.
- 테이블 이름은 딕셔너리에 저장되는데, 모두 대문자로 저장된다.

 

데이터 타입
오라클은 다양한 데이터 타입을 제공한다. 다음은 그 중 많이 사용되는 데이터 타입들이다.

 

- 문자 타입 : VARCHAR2, CHAR, LONG, CLOB

VARCHAR2(n) : 가변 길이 문자 타입 (1 < n < 4000 byte)

CHAR(n) : 고정 길이 문자 타입 (1 < n < 2000 byte) CHAR를 사용할 수는 있으니 VARCHAR2로 바꿔쓴다.

LONG :  2GB까지 저장 가능한 가변길이 문자 타입, 단, 조건 검색할 수 없다.

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

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

 

- 숫자 타입 : NUMBER

NUMBER(n,p) n은 전체 자리수이고 p는 소수점 이하 자리수이다. 전체 자리수를 초과할 경우 입력 거부되지만 소수점 이하 자리수가 초과되면 반올림되어 입력된다.

 
- 날짜 타입 : DATE

출력이나 입력 형식과 무관하게 YYYY/MM/DD:HH24:MI:SS 형태로 저장.

 
- 이진 타입(바이너리 코드 저장. 잘 쓰지 않는다) : RAW, LONG RAW, BLOB, BFILE

BLOB : 4G 까지 저장 가능한 가변길이 이진 타입.

ROW(n) : 가변길이 이진 타입 (n < 2000) 
BFILE : 외부 파일 저장을 위한 이진 타입, 4G 이내 파일을 저장한다. 

- ROWID 타입 : ROWID

ROWID : ROWID를 저장하기 위한 데이터 타입. 주로 PLSQL 프로그래밍에서 많이 사용되며 각 문자는 64진수로 엔코딩 되어있다. 일종의 고유 ID이며, 절대 중복되지 않는다. 

rowid란 column이 없지만 출력된다.

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

CREATE TABLE board ( // 괄호 사이에 컬럼리스트를 적으면 된다. 

no NUMBER,
name VARCHAR2(50), // 사람 이름의 크기는 외국인까지 고려해서 넉넉하게 한다. 
sub VARCHAR2(100),
content VARCHAR2(4000),
hdate DATE DEFAULT SYSDATE // 디폴트로 sysdate를 지정. 따라서 게시물 입력할때 따로 지정하지 않는다.
);

 

SELECT table_name FROM user_tables
WHERE table_name = 'BOARD'; // 테이블 이름은 무조건 대문자로 들어가기때문에 소문자로 검색하면 안된다.

DESC board;

SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'BOARD'; // DESC board와 같은 명령어

INSERT INTO board (no) VALUES (1);

COMMIT;

SELECT * FROM board;

 

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

[예제 2-1]

CREATE TABLE t1(

no NUMBER(4,2) // 전체 숫자 4자리에 소수점 이하는 2자리. 99.99를 입력할 수 있다.

);
INSERT INTO t1 VALUES (12.12);
INSERT INTO t1 VALUES (1.789); // 소수점 이하는 입력범위를 넘어가면 반올림 된다.
SELECT * FROM t1;

INSERT INTO t1 VALUES (123.1); // 소수점 앞쪽은 입력 범위를 넘어가면 입력 자체가 되지 않는다.
[예제 2-2]

CREATE TABLE t2 (name VARCHAR2(2)); // VARCHAR2(2)의 2는 byte이다.

INSERT INTO t2 VALUES ('AA'); 

INSERT INTO t2 VALUES ('장강'); // 한글은 글자 하나당 2byte라서 장강은 4byte이기 때문에 입력되지 않는다.

INSERT INTO t2 VALUES ('장');

SELECT * FROM t2;

 

[예제 4] 다음 실습을 통해 DATE 타입에 대해서 이해해 보자

CREATE TABLE hd (
no NUMBER,
hdate DATE
);

INSERT INTO hd VALUES (1, sysdate); // 입력당시 날짜가 자동으로 입력 

SELECT * FROM hd;

SELECT * FROM hd WHERE hdate = '2019/12/12'; // 0시 0분 0초를 찾기때문에 찾지 못한다. 

SELECT no, TO_CHAR(hdate,'YYYY/MM/DD:HH24:MI:SS') FROM hd; // 따라서 시간까지 같이 출력해준다.

SELECT * FROM hd WHERE hdate BETWEEN '2019/12/12' AND '2019/12/13';

// 시, 분, 초가 들어가 있을경우 이렇게 검색해주는 방법이 있다.

SELECT * FROM hd WHERE TRUNC(hdate) = '2019/12/12';

// TRUNC함수는 조건이 없을경우 숫자는 소수점이하, 날짜는 '시, 분, 초'를 버린다.

 

따라서 날짜 데이터의 시, 분, 초가 의미가 없으면 들어가지 않도록 TRUNC함수를 이용해서 년, 월, 일만 들어가도록 한다.

INSERT INTO hd VALUES (2, TRUNC(sysdate));

SELECT *

FROM hd

WHERE hdate = '2019/12/12';

-> TRUNC를 사용한 2번째 데이터는 검색되는 것을 확인할 수 있다.

 

[예제 5] 다음에 제시된 테이블을 생성한다. 테이블명이나 컬럼의 이름, 컬럼의 길이는 임의로 정한다.

- 테이블명 : 고객
- 컬럼 : 고객관리번호, 고객명, 주소, 전화

C:\sql\client.sql 파일 생성

DROP TABLE client; // client 테이블이 기존에 있다면 삭제

PURGE RECYCLEBIN; // 완전히 깨끗하게 삭제. 기존 테이블이 없다면 오류나는 명령어

@c:\sql\client

SELECT table_name FROM user_tables; 으로 만들어진 테이블 확인

 

앞으로 모든 테이블 생성 예제는 예제 5번과 같은 형태로 진행한다.

 

[실습]

1. 예제를 통해 만들어진 테이블을 확인하고 모두 삭제한다.

SELECT table_name FROM user_tables; 로 예제를 통해 만들어진 테이블 확인

예제로 만들어진 테이블을 삭제해주는 q1.sql 파일 생성 후 @c:\sql\q1.sql 으로 실행

예제로 만들어진 테이블이 지워 졌음을 확인

2. 다음 구조와 같은 테이블을 생성하는 스크립트를 작성하고 실행해본다.
- 테이블명이나 컬럼명, 데이터 타입 등은 입력될 데이터의 성격에 따라 임의로 정할 수 있다.

테이블 상세 도표

q2 이름의 sql 파일 생성 후 실행

@c:\sql\q2.sql

 

SELECT table_name FROM user_tables; 으로 만들어진 테이블 확인

 

3. 다음 표에 제시한 테이블을 생성한다.

q3 이름의 sql 파일 생성 후 실행

@c:\sql\q3.sql

SELECT table_name FROM user_tables;를 입력해서 기존의 3개 테이블 외에 4개의 테이블이 더 생성된 것을 확인 가능

 

* 용어 및 개념정리

Data Dictionary : SQL의 관리 정보. 일종의 DB안의 사전.