093 일반 테이블 생성하기(CREATE TABLE)
테이블의 종류 2가지
1. 영구히 data를 저장하는 테이블
2. 임시로 data를 저장하는 테이블
구조 :
CREATE TABLE 테이블명 (컬럼명 데이터 유형, 컬럼명 데이터 유형, .....); |
* 테이블명과 컬럼명 지을 때 주의사항
1. 테이블명과 컬럼명은 반드시 문자로 시작
2. 테이블명의 길이는 30자를 넘을 수 없다.
3. 테이블명 이름에 특수 문자는 $, _, #만 포함가능
* 데이터 유형
유형 | 종류 | 의미 | |
문자형 |
char( ) | 고정 길이 문자 데이터 유형, 최대 길이 2000 |
|
varchar2( ) | 가변 길이 문자 데이터 유형, 최대 길이 4000 |
||
long | 가변 길이 문자 데이터 유형, 최대 2GB |
||
clob | 문자 데이터 유형, 최대 4GB |
||
blod | 바이너리 데이터 유형, 최대 4GB |
||
숫자형 | number( ) | 숫자 데이터 유형, 십진수 기준 최대 38자리, 소숫점 이하 -84~127 |
|
날짜형 | date | 날짜 데이터 유형, 기원전 4712년 01월 01일부터 기원후 9999년 12월 31일까지 |
* 모델링 : char( ), varchar2( )
* 데이터 이행 : long, clob
* 각 나라의 언어에 맞춰서 지원하는 문자형 데이터 유형 :
nvarchar2(길이)
nchar(길이)
ex) nvarchar(20) : 한글20자리
* char, varchar2의 차이:
char() : 고정형, 10자리 배정 후 문자 덜 입력했더라도 공백 그대로 자리 둠.
m | i | r | a | c | l |
↓
m | i | r | a | c | l | e |
차후 데이터를 추가하려고 할 경우, 빈 공백에 그대로 입력 가능
데이터의 변경이 많이 발생 할 것 같은 컬럼이면 처음부터 char로 지정하는 것이 이득
varchar2() : 가변형, 10자리 배정 후 문자 덜 입력하면 빈 공간 회수
공간 절약이라는 장점이 있음.
업데이트가 잘 일어나지 않는 데이터이면 varchar2로 하는 것이 이득
하지만 공간 절약이 무조건적인 장점은 아님.
m | i | r | a | c | l |
↓
m | i | r | a | c | l |
빈공간 회수된 상태에서 데이터를 추가하려고 한다면?
행이 어디로 이사간다는 위치 정보를 남겨두고, Row Migration 진행 (성능 느려짐)
m | i | r | a | c | l | e |
새로운 곳에서 전체 데이터 새로 입력
* Row migration 현상이 일어남에도 불구하고 char로 잘 생성 안하고 varchar2로 생성하는 이유?
아래 예시 참고
사전 작업
1. varchar2를 컬럼으로 하는 테이블 하나 생성 & 데이터 삽입
CREATE TABLE emp600
(ename VARCHAR2(10),
sal NUMBER(10),
deptno VARCHAR2(10) ); -- deptno 컬럼을 varchar2로 만듬
INSERT INTO emp600 (ename, sal, deptno)
SELECT ename, sal, to_char(deptno)
FROM emp;
2. char를 컬럼으로 하는 또다른 테이블 생성 & 데이터 삽입
CREATE TABLE deptno600
(deptno CHAR(10), -- deptno 컬럼을 char로 만듬
loc VARCHAR2(10) );
INSERT INTO dept600 (deptno, loc)
SELECT to_char(deptno), loc
FROM dept;
문제 510. dept600 테이블에서 deptno가 10번인 deptno와 loc를 출력하시오.
SELECT deptno,loc
FROM dept600
WHERE deptno = '10';

char(10)인 deptno 컬럼
굳이 deptno = '10 ' 이렇게 공백 8자리를 두지 않아도, 내부적으로 공백을 채워서(char(10)) 비교해줌. 결과 출력 잘 됨.
char(10)과 char(10)간의 비교
문제 511. emp600 테이블에서 deptno가 10번인 사원들의 ename, sal을 출력하시오.
SELECT ename, sal
FROM emp600
WHERE deptno = '10';

빈공간 회수해가서 varchar2(2)인 deptno 컬럼에 2자리 수 조회 조건( varchar2(2))을 줘서 결과 출력 잘 됨.
varchar2(2)과 varchar2(2)간의 비교
문제 512. dept600 테이블과 emp600 테이블을 서로 조인해서 ename, sal, loc를 출력하시오.
SELECT e.ename, e.sal, d.loc
FROM emp600 e, dept600 d
WHERE e.deptno = d.deptno;

선택된 레코드가 없음.
이유 :
e.deptno ( varchar2(2) ) ≠ d.deptno( char(10) )
▶ emp600, dept600에서 각각 부서번호 10번 잘 조회됐지만, 조인했을 때는 결과가 나오지 않음.
varchar2(2)가 char(10)을 맞춰주기 위해 공백 8개를 채우지 않음.
데이터 유형이 같을 때만 맞춰줌. 다를 경우에는 맞춰주지 않음.
이런 상황을 대비하기 위해 대부분 varchar2로 테이블 설계함.
데이터가 조인으로 인해 보여지지 않는 문제가
Row migration으로 성능떨어지는 것보다 더 심각하므로, 성능상 이슈 감안함.
문제 517. dept 테이블에 data를 입력하기 위한 dept_my테이블을 생성하고, dept 테이블의 모든 데이터를 dept_my 테이블에 입력하시오.
CREATE TABLE dept_my
(deptno NUMBER(10),
dname VARCHAR2(14),
loc VARCHAR2(13) );
INSERT INTO dept_my(deptno, dname, loc)
SELECT deptno, dname, loc
FROM dept;
문제 518. 아래의 데이터를 dept_my 데이터에 입력하시오.
부서 번호 | 50 |
부서 명 | HR |
부서 위치 | Chaubunagungamaug |
INSERT INTO dept_my(deptno, dname, loc)
VALUES (50, 'HR', 'Chaubunagungamaug');

컬럼에 할당된 공간보다 더 긴 데이터를 입력하려고 하기 때문에 오류 발생.
문제 519. dept_my 테이블에 loc컬럼의 길이를 varchar2(50)으로 늘리시오.
ALTER TABLE dept_my
MODIFY(loc varchar2(50) );

DESCRIBE dept_my;

* 주의사항:
위의 ALTER문을 이용한 변경작업은 업무시간(바쁠 때) 수행하면 안되고, db가 한가한 밤이나 주말에 수행해야함.
INSERT INTO dept_my(deptno, dname, loc)
VALUES (50, 'HR', 'Chaubunagungamaug');

* 위와 같은 변경 사항에 대한 이력을 별도의 테이블로 만들어서 관리하면 나중에 필요할 때 쉽게 확인 가능
테이블 정의서(엑셀파일)와 실제 db(물리적 db 설계)가 일치하는지 확인하는 경우 있음 (si 프로젝트 시 감사)
방법: Python or PL/SQL