데이터정의어DDL : table 생성 table 복사, varchar2와 varchar와 char비교

Table 생성

테이블 생성시 규칙이 있다.

테이블이름 작성규칙

• 이름시작시 : 반드시 문자로 시작 (숫자,특수문자로 시작할수없음)
• 30byte까지만 작성가능
• 포함가능 : 특수문자는 (_ , # , $)만 포함가능
• 포함불가능 : 공백

컬럼의 데이터타입

• 가변길이문자열 : varchar2(컬럼사이즈)
• 고정길이문자열 : char(컬럼사이즈)
• 숫자 : Number(p, s)
  • p는 정수자리수
  • s는 소수점이하 자리수
  • (ex) number(4) => 0 ~ 9999 (음수포함)
  • (ex) number(4,2) => 0 ~ 9999.99 (음수포함)
• 날짜 : date
  • 컬럼사이즈없음

varchar2와 varchar와 char비교

데이터를 저장할때 두가지 크게 고정길이와 가변길이 형식으로 쓸 수 있다
공간효율 : 메모리가 아니고 디스크의 물리적 공간을 의미.

1. char : 고정길이 문자열 형식
   예를 들어 char(20)는 20byte를 가지는 문자열 형식이 있다
   여기에 ninano라는 6자짜리 문자열을 넣으면 남은 공간인 14byte는 공백으로 채워 총 20byte를 사용한다
   따라서 고정길이가 정확한 문자열에만 사용해야한다 아니면 공간의 낭비가 발생할 수 있다

• 장점 : 성능 상대적으로 좋음. 즉,데이터입력속도가 빠름 (고정길이기때문에 길이계산할 필요가 없다)
• 단점 : 공간효율이 안좋음. (고정길이기때문에 공간을 다 쓰지않아도 공간을 잡고있음)

2. varchar2 : 가변길이 문자열 형식
   길이정보도 같이 저장되는 문자열 형식이다.
   예를들어 varchar2(20)일때 ninano라는 6자짜리 문자열을 넣으면 6byte + 길이정보저장공간 1byte까지 총 7byte만 사용한다.
   공간을 적게 차지할 수 있기에 보통 varchar2를 이용한다

• 장점 : 공간효율이 좋음 (가변길이기때문에 필요한만큼만 공간을 잡음)
• 단점 : 성능이 상대적으로 안좋음

• 상황에 맞게 char나 varchar2를 선택해서 사용하면되지만 오라클의 권장사항은 varchar2이다. why? 성능차이가 크게 나지않지만 공간효율의 차이는 티가 확난다.

• 참고로 varchar1은 없다. varchar와 varchar2만 있을뿐…ㅋㅋㅋㅋㅋ

  varchar와 char 비교링크

옵션 : default값, 제약조건

• 테이블생성시 필수가 아님.
• default값이 선언되어있는 폴더는 insert나 update시 null값이 아닌 자동으로 기본값이 입력된다.
• 컬럼마다 차이가 있지만 보통 컬럼명과 제약조건 사이에 작성한다
• 디폴트값작성위치에 따라 오류가 나타날 수 있다.
• 예시 :

create table dept
(deptno number(2), dname varchar2(14), loc varchar2(13), 
join_date date CONSTRAINT mk_jd_nn not null  default sysdate);

//출력값
ORA-00907: 누락된 우괄호 - "missing right parenthesis"

• 이때 디폴트값을 제약조건앞에 위치하게 하면 에러가 해결된다

create table dept
(deptno number(2), dname varchar2(14), loc varchar2(13), 
join_date date default sysdate CONSTRAINT mk_jd_nn not null);

//출력값
테이블생성완료

테이블생성 및 테이블복사 명령어

1. 테이블생성

create table 테이블명
컬럼명 컬럼데이터타입(컬럼사이즈) constraint 제약조건명 제약조건 

//예시
create table employee 
(employee_id number(6) constraint emp_emp_id_pk privary key,
first_name varchar2(20));

2. 테이블복사 (서브쿼리구문 사용) : 기존테이블을 복사한 테이블을 생성할 수 있다.
   • 주 사용처 :
     • 백업
     • 테스트 : 뭔가를 테스트하고싶을 때 원본테이블을 복사한 뒤 복사본에 테스트를 하여 안전성을 높인다.
   • * : 전체 컬럼과 그 안의 데이터 전체를 복사함.
   • 컬럼명설정 : 원하는 컬럼과 그 안의 데이터만 복사함.
   • 테이블을 복사하면 제약조건도 같이 복사될까?
     • NOPE. NOT NULL 제약조건만 복사되고 나머지 제약조건은 필요시 직접 추가해야한다.

//서브쿼리 구문을 사용한 테이블 생성
create table 테이블명
as select 원하는컬럼명 또는 *
from 복사하고싶은테이블

//서브쿼리 구문을 사용하는데 데이터는 빈채로 틀만 가져오고싶을때
create table 테이블명
as select *
from 복사하고싶은테이블
where 1 = 2; //항상 거짓이기때문에 컬럼틀만 복사되고 안의 데이터는 복사되지않는다.

예시 :

//서브쿼리 구문을 사용한 테이블 생성
create table dept80
as select employee_id, last_name, salary*12  annsal, hire_date
from employees
where department_id = 80;

제약조건

• 테이블에 부정확한 데이터 입력 방지를 위해 필수!

제약조건명

• 제약조건 작성시 필수 입력

• 제약조건명은 중복불가

• 제약조건 사용처 : 제약조건 정보 확인할때, 해당 제약조건삭제할때.

• 의미있는 제약조건명필수

  • 오라클 권장 : 테이블명약자_컬럼명약자_제약조건유형약자
  • ex) emp_empid_pk
  • ex) dept_dname_nn
  • ex) emp_mgrid_fk
  • ex) sal_ck
  • ex) emp_email_uk

• 제약조건명을 꼭 적야아할까?
  • NOPE! 생략가능.
  • 생략하면 oracle이 자동으로 고유한 제약조건명 지정
  • ex) sys_cXXXXX(X는 숫자) : 이런 패턴의 제약조건명을 자동으로 지정
  • 하지만 전혀의미없는 제약조건명이므로 제약조건명 지정 권장!

제약조건 선언하는 시기

1. 테이블 생성 시 :
   • 일반사용
   • 서브쿼리구문 사용 : 기존테이블을 복사한 테이블을 생성할 수 있다.
     • 주 사용처 :
       • 백업
       • 테스트 : 뭔가를 테스트하고싶을 때 원본테이블을 복사한 뒤 복사본에 테스트를 하여 안전성을 높인다.
     • * : 전체 컬럼과 그 안의 데이터 전체를 복사함.
     • 컬럼명설정 : 원하는 컬럼과 그 안의 데이터만 복사함.

create table 테이블명
컬럼명 컬럼데이터타입(컬럼사이즈) constraint 제약조건명 제약조건

예시 :

create table employee 
(employee_id number(6) constraint emp_emp_id_pk privary key,
first_name varchar2(20));

2. 테이블 성성 후 :
   • 문법이 두가지이다. : 테이블레벨문법과 컬럼레벨문법

//테이블레벨 문법
alter table 테이블명
add constraint 고유한제약조건명 제약조건유형(컬럼명);

//컬럼레벨 문법
alter table 테이블명
modify 컬럼명 contsraint 고유한제약조건명 제약조건유형;

제약조건문법 2가지

1. Column-level 문법

• Not Null 제약조건사용시 column-level문법으로 작성해야한다!
• 예시

  create table employee 
  (employee_id number(6) constraint emp_emp_id_pk_ privary key,
  first_name varchar2(20));

2. Table-level 문법

• 예시

  create table employee 
  (employee_id number(6),
  first_name varchar2(20),
  constraint emp_emp_id_pk_ privary key(employee_id));

참고링크 : 제약조건문법 2가지

제약조건종류 5가지

참고링크 : 제약조건 5가지

제약조건 확인하는 쿼리

• 저장해놓고 필요시에 불러서 사용가능.
• 저장시 파일확장자는 .sql

select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints
where lower(table_name) = lower( '&table_name');