샘플 스키마 : HR
설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.
DB에서 데이터를 저장하는 가장 기본적인 구조는 테이블(TABLE)이다. 오라클에는 테이블 이외에 뷰(VIEW), 인덱스(INDEX), 시퀀스(SEQUENCE)등 여러 개체가 있다.
No. 01 |
|
| |
테이블 생성에서 이름 규칙 | |
- 문자로 시작한다.
- 30자 이내로 한다.
- 영문(A-Z, a-z), 숫자, _, $, #만을 사용한다.
- 한글 사용은 가능하지만 되도록 사용하지 않는 것이 좋다.
- 객체는 동일한 유저(스키마) 안에서 유일해야 한다.
- 예약어는 사용이 불가능하다.
- 오라클에서 명령어로 사용하고 있는 INSERT, DELETE 등 …
- 대소 문자를 구별하지 않는다.
- 생성할 때 사용한 문자와는 관계없이 모든 이름은 대문자로 저장된다.
- 예) eMPloyees, eMpLOYEES 등으로 지정하여도 EMPLOYEES로 저장된다.
No. 02 |
|
| |
테이블 생성 | |
기분 구조
CREATE TABLE [스키마.]테이블이름(
컬럼 데이터타입 [DEFAULT default값] [컬럼 레벨 제약조건],
컬럼 데이터타입 [DEFAULT default값] [컬럼 레벨 제약조건],
…
[테이블 레벨 제약조건],
…
);
- 데이터 타입
- 컬럼에 입력될 데이터의 종류와 크기글 결정한다.
- DEFAULT
- 입력이 누락됐을 때 기본 입력 값을 정의한다.
- Default 값을 지정하지 않으면 널 값이 저장된다.
- 컬럼 레벨 제약 조건
- PK, FK, UK, CHECK, NOT NULL 등을 지정한다.
- 테이블 레벨 제약 조건
- PK, FK, UK, CHECK만 지정한다.
- NOT NULL은 정의할 수 없다.
제약 조건의 설정 및 관리 방법은 다음 포스트부터 자세히 다룬다.
◈ 예제
create table example1 (
depno number(2),
dname varchar2(14),
loc varchar2(13),
start_date date default sysdate
);
Table EXAMPLE1이(가) 생성되었습니다.
desc example1;
No. 03 |
|
| |
데이터 타입 | |
오라클은 다양한 데이터 타입을 제공하는데 다음은 그 중 많이 사용되는 데이터 타입들이다.
♠ 문자 타입
- VARCHAR2, CHAR, LONG ,CLOB
♠ 숫자 타입
- NUMBER
♠ 날짜 타입
- DATE
♠ 이진 타입
- RAW, LONG RAW, BLOB, BFILE
♠ ROWID 타입
- ROWID
이 중에서도 VARCHAR2, NUMBER, DATE 이 세 가지가 99%이상 사용된다.
Data Type |
설 명 |
VARCHAR2(size) |
가변 길이 문자 타입 (1 < n < 4000 byte) |
CHAR(size) |
고정 길이 문자 타입 (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까지 저장 가능하고 한 테이블의 여러 컬럼에 정의할 수 있다. |
RAW(size) |
가변길이 이진 타입 (n < 2000) |
LONG RAW |
가변길이 이진 타입, 최대 2G까지 저장 가능하다. |
BLOB |
4G까지 저장 가능한 가변길이 이진 타입 |
BFILE |
외부 파일 저장을 위한 이진 타입, 4G 이내 파일을 저장한다. |
ROWID |
ROWID를 저장하기 위한 데이터 타입 주로 PLSQL 프로그래밍에서 많이 사용되며 각 문자는 62진수로 엔코딩(encoding) 되어있다. |
Guidelines
- LONG 타입 컬럼은 서브쿼리를 사용하여 테이블을 생성할 때 복사되지 않는다.
- LONG 타입 컬럼은 GROUPY BY나 ORDER BY절에 포함될 수 없다.
- LONG 타입 컬럼에는 제약조건이 정의하지 못한다.
TIP
▣ CHAR vs. VARCHAR2
CHAR와 VARCHAR2는 둘 다 문자를 저장하지만 저장된 결과는 서로 다를 수 있다. CHAR(10)와 VARCHAR2(10)에 'ABC'를 저장하면 CHAR(10)에는 10Byte가 저장되고 VARCHAR2(10)에는 3byte만 저장된다. CHAR 타입은 저장되는 입력 값과 상관없이 고정된 길이의 문자열을 갖는 반면에 VARCHAR2 타입은 입력 데이터만큼의 길이만 저장된다.
create table comp (
char_col char(4),
varchar2_col varchar2(4)
);
Table COMP이(가) 생성되었습니다.
insert into comp values ('AA', 'AA');
1 행 이(가) 삽입되었습니다.
insert into comp values ('AAAA', 'AAAA');
1 행 이(가) 삽입되었습니다.
select * from comp;
CHAR_COL 과 VARCHAR2_COL 모두에 동일한 데이터가 저장된 것처럼 보이지만 다음 예제를 보면 다르다는 것을 알 수 있다.
select * from comp where char_col = 'AA';
select * from comp where varchar2_col = 'AA';
select * from comp where char_col = varchar2_col;
두 컬럼이 동일한 데이터가 저장된 행을 검색했지만 'AA'인 행은 검색되지 않는다.
CHAR 타입은 빈 공간을 차지하고 있어 위의 경우는 'AA '와 같다.
문자열을 비교할 때 CHAR 타입과 문자열을 비교하거나 CHAR 타입 간에 비교하는 경우는 이런 문제가 나타나지 않는다. 길이가 짧은 쪽을 긴 쪽과 길이를 맞추어 비교하기 때문이다. 예를 들어 CHAR_COL의 'AA '를 문자열 'AA'와 비교하면 문자열에 공백 두 개를 추가하여 'AA '으로 맞추고 비교한다.
그러나 CHAR와 VARCHAR2형과 비교하는 경우는 이런 과정을 거치지 않고 비교한다. 그래서 같은 문자열이라도 다르다고 판단한다.
이런 이유로 CHAR 타입은 고정된 데이터 길이를 가지고 컬럼에만 지정하는 것을 추천한다.
예) 주민등록번호, 학번 …
데이터 타입이 DATE타입인 경우 날짜를 입력하는 가장 흔한 방법은 sysdate를 이용하는 것이다.
그러나 이와 같은 날짜 입력은 나중에 특정일을 검색할 때 문제를 일으킬 수 있다.
insert into example1(start_date) values (sysdate);
1 행 이(가) 삽입되었습니다.
select start_date from example1;
select start_date from example1 where start_date = '2017/12/15';
위와 같이 입력에 sysdate를 이용하는 경우 화면에 대부분 YYYY/MM/DD 만을 검색하지만 실제 저장된 데이터는 YYYY/MM/DD/HH:24MI:SS 이므로 start_date='2017/12/15'와 같은 조건 검색을 수행할 때 원치 않는 결과가 나올 수 있다. 이것은 오라클이 start_date='2017/12/15'을 해석할 때 stat_date='2017/12/15:00:00:00'으로 해석하기 때문이다.
실제 저장된 데이터를 보면
select to_char(start_date, 'YYYY/MM/DD:HH24:MI:SS') from example1;
▶ 검색 1: '2017/12/15:00:00:00' 부터 '2017/12/16:00:00:00'까지 검색한다.
select start_date
from example1
where start_date between '2017/12/15' and '2017/12/16';
▶ 검색2
select start_date
from example1
where start_date like '17/12/15%';
위와 같은 두 가지 방법으로 특정일을 검색할 수 있다. 그러나 검색2의 방법 같은 경우 날짜형인 start_date 컬럼에 LIKE와 같은 문자형 연산을 수행하면 데이터를 문자형으로 자동 형 변환하기 때문에 대용량 테이블에서 검색의 성능을 저해할 가능성이 있다.
이런 문제를 해결하는 방법은 여러 가지가 있지만 가장 간단한 해결 방법은 데이터 타입을 DATE로 정의하지 않고 VARCHAR2(10)이나 CHAR(10)으로 정의하는 것이다. 이렇게 정의하고 sysdate로 날짜를 입력하는 경우 시분초는 입력되지 않는다. 단, 시분초가 중요한 의미가 잇는 경우는 사용하지 않는 것이 좋다. 그리고 또 한가지 방법은 데이터 타입을 DATE 그대로 이용하고 날짜를 입력할 때 항상 TRUNC(sysdate)를 이용하는 것이다. 시분초가 항상 00:00:00으로 결정됨으로 위의 여러 가지 문제를 피할 수 있다.
No. 04 |
|
| |
테이블 삭제 | |
기본 구조
DROP TABLE [스키마.]테이블이름 [CASCADE CONSTRAINT];
- 테이블을 삭제한다.
- CASCADE CONSTRAINT : 테이블이 다른 테이블로부터 참조되는 경우 해당 제약조건을 먼저 삭제한 후 테이블을 삭제한다.
- 한번 실행되면 roll back 되지 않기 때문에 사용에 주의한다.
drop table example1;
Table EXAMPLE1이(가) 삭제되었습니다.
'SQL' 카테고리의 다른 글
| [SQL 24] 제약조건 - PK(Primary Key), FK(Foreign Key) (1) | 2017.12.17 |
|---|---|
| [SQL 23] 제약 조건 - NOT NULL, UK(Unique Key), CHECK (0) | 2017.12.16 |
| [SQL 21] 서브쿼리를 사용한 DML 문장과 다이렉트 로드 (0) | 2017.12.15 |
| [SQL 20] DML(INSERT, UPDATE, DELETE), TRUNCATE (3) | 2017.12.11 |
| [SQL 19] 트랜잭션 & TCL(COMMIT, ROLLBACK, SAVEPOINT) (0) | 2017.12.10 |