[ SQL ] Oracle sql문 #1 _ 데이터 정의어

오라클의 SQL 문 

• SQL 문은 관계형 데이터베이스에서 데이터베이스나 테이블을 생성하거나 데이터의 조회, 수정, 삭제, 입력 등을 하기 위해서 사용하는 언어를 의미한다.
• SQL 문은 프로그래밍 언어로 작성된 응용 프로그램에서 데이터베이스에 저장된 데이터를 관리하고 조회한다.
• SQL 문에서 사용하는 데이터 타입 중에 null 은 매우 중요한 데이터 타입으로 데이터베이스의 null 은 값이 없는 것이 값인 값과 존재하지만 불확실한 값도 포함한다.
• SQL 문의 특징: 
  *  대소문자를 구분하지 않으나 대문자 사용을 권장한다.
  *  한 줄 또는 여러 줄에 입력할 수 있다.
  *  여러 줄에 나누거나 단축될 수 없다.
  *  문은 대개 별도의 줄에 입력한다.
  *  가독성을 높이기 위해 들여쓰기를 사용한다.
  *  명령어에 대한 종료는 ;(세미콜론)으로 선언한다.
  *  문자 데이터와 날짜 데이터는 반드시 '(싱글 쿼터)를 사용하여 표현한다.
  *  자체는 대소문자를 구별하지 않지만, '(싱글 쿼터)로 저장하는 리터럴은 대소문자를 구별한다.

 

 

 

 

SQL 문의 형태 

[데이터 정의어 ]

DDL(Data Definition Language)은 데이터 정의어를 의미한다.
데이터 정의어는 테이블을 생성, 테이블 삭제, 테이블 재정의 등을 기능이 있다.

 

 

 1. CREATE TABLE 문 

• create table 문은 테이블을 생성하기 위해 사용한다.
• create table 문은 테이블을 생성할 때 맨 마지막 문에는 ,(콤마)를 생략해야 한다.

create table 테이블명 (
column1 datatype primary key,
column2 datatype [default 값],
column3 datatype not null,
ⵗ
columnN datatype
);

• create table 테이블명: 
  create table 문으로 지정한 테이블명의 테이블을 생성한다.
  지정한 테이블명은 중복을 허용하지 않는다.
• column1 datatype primary key:
  column1 은 컬럼명을 지정한다.
  datatype 은 데이터의 타입을 지정한다.
  primary key 는 제약조건인 기본키 제약조건을 지정하고 값은 not null 제약조건이 된다.
  not null 제약조건이 설정되면 컬럼값에 null 값을 입력할 수 없다.
• column2 datatype [default 값]: 
  column2 는 컬럼명을 지정한다.
  datatype 은 데이터의 타입을 지정한다.
  [default 값]은 기본값을 지정하고 날짜 입력에 주로 사용한다.
• column3 datatype not null:
  column3 는 컬럼명을 지정한다.
  datatype 은 데이터의 타입을 지정한다.
  not null 은 제약조건으로 컬럼값을 입력하지 않을 때 null 값으로 입력되는 것을 방지한다.
  not null 제약조건이 설정되면 컬럼값에 null 값을 입력할 수 없다.
• columnN datatype:
  columnN 은 컬럼명을 지정한다.
  datatype 은 데이터의 타입을 지정한다.
  마지막 문이므로 ,(콤마)를 생략해야 한다.
• ( ) :  코드블록을 적용하고 마지막에는 ;(세미콜론)을 선언해야 한다.

 

 

 ex )  사원정보를 저장하기 위한 salesman 테이블을 생성한다.

CREATE TABLE SALESMAN( ID CHAR(6),  NAME VARCHAR2(12), AGE NUMBER(3), ADDRESS VARCHAR2(60) ); --Table SALESMAN이(가) 생성되었습니다. COMMIT; --커밋 완료. DESC SALESMAN; SELECT * FROM SALESMAN;

 

 ex )  게시판 정보를 저장하기 위한 board 테이블을 생성한다.

CREATE TABLE BOARD ( NUM NUMBER(4), TITLE VARCHAR2(30), AUTHOR VARCHAR2(12), CONTENT VARCHAR2(600), WRITEDAY DATE DEFAULT SYSDATE ); DESC BOARD;

문장 한번에 실행해야 결과값이 나온다

 

 

 2. ALTER TABLE 문 

• alter table 문은 테이블을 수정하기 위해 사용한다.
• alter table 문으로 테이블에 대한 구조를 수정할 수 있다.

alter table 테이블명 (
[add (column datatype);]
[add [constraint 제약조건명] 제약조건 (column);]
[modify (column [제약조건]);]
[drop (column);]
);

 

• alter table 테이블명 :
  alter table 문으로 지정한 테이블명의 테이블 구조를 수정한다.
• [add (column datatype);] : 
  add 문으로 ( )(퍼렌씨시스) 안의 컬럼을 추가하고 퍼렌씨시스는 생략할 수 없다.
  column 은 컬럼명을 지정한다.
  datatype 은 데이터의 타입을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.

• [add [constraint 제약조건명] 제약조건 (column);] :
  add 문으로 ( )(퍼렌씨시스) 안의 컬럼에 제약조건을 추가하고 퍼렌씨시스는 생략할 수 없다.
  constraint 명령어로 제약조건의 이름을 부여한다.
  column 은 컬럼명을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
• [modify (column [제약조건]);] : 
  modify 문으로 ( )(퍼렌씨시스) 안의 컬럼에 지정한 데이터 타입이나 제약조건을 수정하고 퍼렌씨시스는 생략할 수 없다.
  column 은 컬럼명을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
• [drop (column);] :
  drop 문으로 ( )(퍼렌씨시스) 안의 컬럼을 삭제하고 퍼렌씨시스는 생략할 수 없다.
  column 은 컬럼명을 지정한다
• ( ) : 
  코드블록을 적용하고 마지막에는 ;(세미콜론)을 선언해야 한다.

 

 

 

 

 

 3. ADD 문 

• alter table 문은 테이블을 수정하기 위해 사용한다.
• alter table 문으로 테이블에 대한 구조를 수정할 수 있다.

alter table 테이블명 (
[add (column datatype);]
[add [constraint 제약조건명] 제약조건 (column);]
[modify (column [제약조건]);]
[drop (column);]
);

 

• alter table 테이블명 :
  alter table 문으로 지정한 테이블명의 테이블 구조를 수정한다.
• [add (column datatype);] : 
  add 문으로 ( )(퍼렌씨시스) 안의 컬럼을 추가하고 퍼렌씨시스는 생략할 수 없다.
  column 은 컬럼명을 지정한다.
  datatype 은 데이터의 타입을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.

• [add [constraint 제약조건명] 제약조건 (column);] :
  add 문으로 ( )(퍼렌씨시스) 안의 컬럼에 제약조건을 추가하고 퍼렌씨시스는 생략할 수 없다.
  constraint 명령어로 제약조건의 이름을 부여한다.
  column 은 컬럼명을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
• ( ) : 
  코드블록을 적용하고 마지막에는 ;(세미콜론)을 선언해야 한다.

 

 

 ex )  salesman 테이블에 sal 컬럼을 추가한다.

ALTER TABLE SALESMAN ADD(SAL NUMBER(7,2)); --Table SALESMAN이(가) 변경되었습니다. DESC SALESMAN;

column 컬럼명을 지정 : SAL

datatype 데이터타입 지정 : NUMBER(7,2)

 

 

 

 4. modify 문 

• modify 문은 테이블을 컬럼이나 제약조건을 수정하기 위해 사용한다.

alter table 테이블명 (
modify (column [제약조건]);
);

• alter table 테이블명 :
  alter table 문으로 지정한 테이블명의 테이블 구조를 수정한다.
• [modify (column [제약조건]);] : 
  modify 문으로 ( )(퍼렌씨시스) 안의 컬럼에 지정한 데이터 타입이나 제약조건을 수정하고
  퍼렌씨시스는 생략할 수 없다.
  column 은 컬럼명을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
• ( ) : 
  코드블록을 적용하고 마지막에는 ;(세미콜론)을 선언해야 한다.

 

 ex )  salesman 테이블에 sal 컬럼의 최대 자릿수를 10 자리로 수정한다.

ALTER TABLE SALESMAN MODIFY(SAL NUMBER(10,2)); --Table SALESMAN이(가) 변경되었습니다. DESC SALESMAN;

 

 

 5. drop 문 

• drop 문은 컬럼을 삭제하기 위해 사용한다.

alter table 테이블명 (
drop (column);
);

• alter table 테이블명 :
  alter table 문으로 지정한 테이블명의 테이블 구조를 수정한다.
• [drop (column);] : 
  drop 문으로 ( )(퍼렌씨시스) 안의 컬럼을 삭제하고 퍼렌씨시스는 생략할 수 없다.
  column 은 컬럼명을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
• ( ) : 
  코드블록을 적용하고 마지막에는 ;(세미콜론)을 선언해야 한다.

 

 ex )  salesman 테이블에 sal 컬럼을 삭제한다.

ALTER TABLE SALESMAN DROP (SAL); --Table SALESMAN이(가) 변경되었습니다. DESC SALESMAN;

 

 

 

 6. drop table 문 

• drop table 문은 테이블을 삭제하기 위해 사용한다.
• drop table 문으로 테이블을 삭제하면 저장된 데이터도 함께 삭제된다.
• 외래키인 foreign key 제약조건이 설정된 경우에는 자식 테이블의 삭제가 불가능하며, 자식 테이블을 삭제하려면 먼저 부모 테이블을 삭제하거나 제약조건을 비활성 시키는 방법으로 해결할 수 있다.
• foreign key 제약조건을 설정하면 부모 테이블의 기본키가 자식 테이블에서는 컬럼이 된다.
• drop table 문으로 테이블을 삭제하여도 오라클 10g 버전부터는 테이블이 완전 삭제가 되지않고 윈도우의 휴지통 기능과 같은 recyclebin 에 저장된다.
• 오라클 10g 버전 이후 버전부터는 삭제된 테이블에 대한 완전 삭제와 복구에 대한 명령어와 문이 만들어졌다.

[drop table 테이블명;]
[show recyclebin;]
[flashback 테이블명 to before drop;]
[purge recyclebin;]

• [drop table 테이블명;]:
  drop table 문으로 지정한 테이블명의 테이블을 삭제하지만, 완전 삭제가 아닌 휴지통인 recyclebin 에 저장된다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
• [show recyclebin;] : 
  show recyclebin 문으로 휴지통인 recyclebin 에 저장된 테이블의 이름과 삭제 시간 등의 정보를 확인한다.
• [flashback 테이블명 to before drop;] : 
  flashback 테이블명 to before drop 문으로 삭제된 테이블을 복구한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
• [purge recyclebin;] :
  purge recyclebin 문으로 삭제된 테이블을 완전히 삭제한다.
  삭제된 테이블은 flashback 문으로도 복구할 수 없다.
  마지막에는 ;(세미콜론)을 선언해야 한다.

 

 ex )  salesman 테이블을 삭제한다.

DROP TABLE SALESMAN; --Table SALESMAN이(가) 삭제되었습니다. SELECT * FROM TAB;

 

 

 7. show 문 

• show recyclebin 문으로 휴지통의 정보를 확인하기 위해 사용한다.

show recyclebin; 

• show recyclebin; :
  show recyclebin 문으로 휴지통인 recyclebin 에 저장된 테이블의 이름과 삭제 시간 등의 정보를 확인한다.

 

 

 ex )  recyclebin 에서 삭제된 salesman 테이블의 정보를 확인한다

SHOW RECYCLEBIN;

 

 

 

 8. flashback문 

• flashback 테이블명 to before drop 문으로 삭제된 테이블을 복구한다

flashback 테이블명 to before drop;

• flashback 테이블명 to before drop; :
  flashback 테이블명 to before drop 문으로 완전히 삭제되지 않은 테이블을 복구하지만,
  purge recyclebin 문으로 완전히 삭제된 테이블은 복구할 수 없다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
  

 

 ex )  삭제된 salesman 테이블을 복구한다.

FLASHBACK TABLE SALESMAN TO BEFORE DROP; --Table SALESMAN이(가) 삭제되었습니다. SELECT * FROM TAB;

 

 9. purge문 

• purge recyclebin 문으로 테이블을 완전히 삭제하기 위해 사용한다.

purge recyclebin; 

• purge recyclebin; :
  purge recyclebin 문으로 테이블을 완전히 삭제하거나 삭제된 테이블을 완전히 삭제한다.
  삭제된 테이블은 flashback 테이블명 to before drop 문으로도 복구할 수 없다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
  

 

 ex )  salesman 테이블을 완전히 삭제한다.

DROP TABLE SALESMAN; --Table SALESMAN이(가) 삭제되었습니다. PURGE RECYCLEBIN; --RECYCLEBIN이(가) 비워졌습니다. SELECT * FROM TAB;

SELECT * FROM TAB 결과 SALESMAN 테이블이 삭제 된것을 확인 할 수 있다.

 

 

 

 10. rename…to 문 

• rename…to 문은 테이블명을 변경하기 위해 사용한다.

rename 변경 전 테이블명 to 변경 후 테이블명;

• rename 변경 전 테이블명 to 변경 후 테이블명; :
  rename 변경 전 테이블명 to 변경 후 테이블명 문은 테이블명을 변경한다.
  to 명령어는 변경할 테이블명을 지정한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.
  

 

 ex )  board 테이블의 이름을 변경한다.

RENAME BOARD TO BOARD_COPY; -- 테이블 이름이 변경되었습니다. SELECT * FROM BOARD_COPY; SELECT * FROM BOARD;

BOARD : 변경 전 테이블 이름

BOARD_COPY : 변경 후 테이블 이름

 

SELECT * FROM BOARD_COPY 실행 결과 BOARD 테이블과 같은 테이블이 나온다.

SELECT * FROM BOARD; 실행 결과 : ORA-00942: table or view does not exist

 

 

 

 11. truncate table 문 

• truncate table 문은 테이블의 저장 공간을 삭제하기 위해 사용한다.
• truncate table 문으로 삭제된 테이블은 테이블의 데이터와 저장구조가 삭제되므로 메모리를 효율적으로 관리할 수 있다.

truncate table 테이블명;

• truncate table 테이블명; :
  truncate table 테이블명 문은 지정한 테이블명의 테이블 저장 공간을 삭제한다.
  마지막에는 ;(세미콜론)을 선언해야 한다.

 ex )  copy_board 테이블의 저장 공간을 삭제한다

TRUNCATE TABLE BOARD_COPY; --Table BOARD_COPY이(가) 잘렸습니다.

 

 

 12. 제약조건 

(1) 제약조건의 개요

• 제약조건(Constraints)은 컬럼에 데이터를 저장할 때 유효한 데이터 저장을 위해서 사용한다.
• 제약조건은 부적절한 데이터가 입력되는 것을 방지하기 위하여 사용한다.
• 제약조건의 특징:
  * 제약조건은 테이블 레벨에서 규칙을 적용한다.
  * 제약조건은 종속성이 존재할 경우 테이블 삭제를 방지한다.
  * 테이블에서 컬럼값의 삽입, 갱신, 삭제될 때마다 테이블에서 규칙을 적용한다.
  * 지정된 제약조건에 어긋나면 오류가 발생하며 자주 발생하는 오류는 다음과 같다.
  * 컬럼값을 중복 저장할 때
  ORA-0001: 무결성 제약조건(SCOTT.CUSTOME_ID_PK)에 위배됩니다.
  * 값을 저장하지 않을 때
  ORA-01400: NULL 을 (“SCOTT”.“CUSTOME”.“NAME”) 안에 삽입할 수 없습니다.

 

(2) 제약 조건의 지정방식

① 컬럼 레벨 방식

create table 테이블명 (
column1 datatype [constraint 제약조건명] 제약조건,
column2 datatype,
column3 datatype,
ⵗ
columnN datatype
);

• 컬럼 레벨 방식은 컬럼을 선언하면서 제약조건도 같이 지정하는 방식이다.
• not null 제약조건은 컬럼 레벨 방식으로만 사용할 수 있다.
• constraint 제약조건명은 생략할 수 있다.
• 제약조건명은 테이블명_컬럼명_제약조건 약자로 표현한다.

 

② 테이블 레벨 방식

create table 테이블명 (
column1 datatype,
column2 datatype,
column3 datatype,
ⵗ
columnN datatype
[constraint 제약조건명] 제약조건(column1)
);

• 테이블 레벨 방식은 컬럼을 모두 지정하고 나중에 제약조건을 지정하는 방식이다.
• constraint 제약조건명은 생략할 수 있다.
• not null 제약조건은 사용할 수 없다.
• 제약조건명은 테이블명_컬럼명_제약조건 약자로 표현한다.

 

(3) 제약 조건의 형태

① primary key 제약조건

• 컬럼값의 중복을 방지할 목적으로 사용된다.
• 중복되는 컬럼값을 사용할 수 없으며 일반적으로 변경할 수 없다.
• 테이블에 대한 기본키를 생성한다.
• 생성한 기본키는 일반적으로 테이블에 하나만 생성한다.
• 테이블에서 컬럼값을 유일하게 식별하는 컬럼이다.
• 지정한 컬럼은 null 값을 사용할 수 없다.
• unique 제약조건과 not null 제약조건을 만족해야 한다.
• primary key 제약조건의 약자는 pk 다.

 

② foreign key 제약조건

• 테이블 간의 관계를 맺기 위해서 사용된다.
• 테이블에 대한 외래키를 생성하며 외래키는 컬럼이다.
• 생성된 외래키는 부모 테이블의 기본키가 된다.
•  다른 테이블의 primary key 제약조건을 참조해야 한다.
• 지정한 컬럼값은 다른 테이블의 primary key 제약조건의 컬럼값이다.
• foreign key 제약조건의 약자는 fk 다.

 

③ unique 제약조건

• 지정한 컬럼은 반드시 유일한 값을 가진다.
• unique 제약조건의 약자는 uk 다.

 

 

④ not null 제약조건

• 지정한 컬럼은 반드시 값을 가져야 한다.
• not null 은 제약조건으로 컬럼값을 입력하지 않을 때 null 값으로 입력되는 것을 방지한다.
• not null 제약조건이 설정되면 컬럼값에 null 값을 입력할 수 없다.
• 컬럼 레벨 방식으로만 사용할 수 있다.
• not null 제약조건의 약자는 nn 다.

 

 

⑤ check 제약조건

• check 제약조건은 지정한 조건에 일치하는 컬럼값만 저장할 수 있다.
• check 제약조건의 약자는 ck 다

 ex )  컬럼 레벨 방식으로 제약조건을 지정하여 customer 테이블을 생성한다.

CREATE TABLE CUSTOMER( NUM NUMBER(4) PRIMARY KEY, NAME VARCHAR2(12) NOT NULL, ADDRESS VARCHAR2(60) UNIQUE, AGE NUMBER(3) CHECK(AGE >= 30) ); --Table CUSTOMER이(가) 생성되었습니다.

▼ DESC CUSTOMER;

▼ SELECT * FROM CUSTOMER;

 

cf )  primary key & foreign key

당사의 직원 데이터베이스는 표 1(emp_dets)에 직원 ID, 직원 이름, 관리자 ID와 같은 직원 정보가 포함되어 있다.

표 2(supervisor_dets)에는 슈퍼바이저에 관한 정보, 즉 슈퍼바이저의 ID와 이름이 포함되어 있다.
표 1에는 프라이머리 키로서emp_id가, 표 2에는 프라이머리 키로서 supervisor_id가 있다.

표 1에서 supervisor_id는 표2를 참조합니다.따라서 표 1의 외부 키입니다.