[sqld] DDL / 데이터 정의어 사용방법 / Oracle, SQL Server

 

1. CREATE TABLE 

 

기본형식

CREATE TABLE 테이블이름 (
	칼럼명1 DATATYPE [DEFAULT 형식],
    칼럼명2 DATATYPE [DEFAULT 형식],
    칼럼명3 DATATYPE [DEFAULT 형식]
    );

 

예제

테이블명 : PLAYER
테이블 설명 : K-리그 선수들의 정보를 가지고 있는 테이블
칼럼명 : PLAYER_ID (선수 ID) 문자 고정 자릿수 7자리,
	PLAYER_NAME (선수명) 문자 가변 자릿수 20자리,
        TEAM_ID (팀ID) 문자 고정 자릿수 3자리,
        E_PLAYER_NAME (영문선수명) 문자 가변 자릿수 40자리,
        NICKCNAME (선수별명) 문자 가변 자릿수 30자리,
        JOIN_YYYY (입단년도) 문자 고정 자릿수 4자리,
        POSITION (포지션) 문자 가변 자릿수 10자리,
        BACK_NO (등번호) 숫자 2자리,
        NATION (국적) 문자 가변 자릿수 10자리,
        BIRTH_DATE (생년월일) 날짜,
        SOLAR (양/음) 문자 고정 자릿수 1자리,
        HEIGHT (신장) 숫자 3자리,
        WEIGHT (몸무게) 숫자 3자리
제약조건 : 기본키(PRIMARY KEY) -> PLAYER_ID
		  (제약조건명은 PLAYER_ID_PK)
          값이 반드시 존재 (NOT NULL) -> PLAYER_NAME, TEAM_ID

 

 

ORACLE

 

CREATE TABLE PLAYER (
PLAYER_ID CHAR(7) NOT NULL,
PLAYER_NAME VARCHAR2(20) NOT NULL,
TEAM_ID CHAR(3) NOT NULL,
E_PLAYER_NAME VARCHAR2(40),
NICKNAME VARCHAR(30),
JOIN_YYYY CHAR(4),
POSITION VARCHAR2(10),
BACK_NO NUMBER(2),
NATION VARCHAR2(20),
BIRTH_DATE DATE,
SOLAR CHAR(1),
HEIGHT NUMBER(3),
WEIGHT NUMBER(3),
CONSTRAINT PLAYER_ID_PK PRIMARY KEY(PLAYER_ID)
);

 

SQL Server

 

ORACLE과 다른점 - VARCHAR2 대신 VARCHAR 사용, NUMBER의 종류 다양함

 

CREATE TABLE PLAYER (
PLAYER_ID CHAR(7) NOT NULL,
PLAYER_NAME VARCHAR(20) NOT NULL,
TEAM_ID CHAR(3) NOT NULL,
E_PLAYER_NAME VARCHAR(40),
NICKNAME VARCHAR(30),
JOIN_YYYY CHAR(4),
POSITION VARCHAR(10),
BACK_NO TINYINT,
NATION VARCHAR(20),
BIRTH_DATE DATE,
SOLAR CHAR(1),
HEIGHT SMALLINT,
WEIGHT SMALLINT,
CONSTRAINT PLAYER_ID_PK PRIMARY KEY(PLAYER_ID)
);

 

 

 

테이블 구조 확인 

 

PLAYER 테이블의 구조를 확인하는 명령어

 

ORACLE 

 

DESCRIBE PLAYER;

또는

DESC PLAYER;

 

 

SQL Server

 

exec sp_help 'dbo.PLAYER'
go

exec와 go는 명령어 실행을 도와주는 것으로 조회 명령어는 sp_help 'dbo.PLAYER' 이다.

 

 

CTAS

Create Table ~ As Select ~ 

SELECT를 사용하여 테이블을 복제한다.

제약조건은 NOT NULL만 적용된다.

 

 

ORACLE

 

CREATE TABLE PLAYER_TEMP
AS SELECT * FROM PLAYER;

 

SQL Server

 

SELECT * INTO PLAYER_TEMP FROM PLAYER;

 

 

2. ALTER TABLE

기존에 있는 테이블을 수정한다.

ADD COLUMN

기존 테이블에 새로운 칼럼 추가. 

추가한 칼럼은 테이블의 마지막 칼럼이 되고 위치 선택은 불가능하다.

 

예제) PLAYER TABLE에 ADDRESS 문자열 가변자릿수 80인 칼럼을 추가한다.

 

ORACLE

 

ALTER TABLE PLAYER
ADD (ADDRESS VARCHAR2(80));

 

SQL SERVER

 

ALTER TABLE PLAYER
ADD ADDRESS VARCHAR2(80);

 

DROP COLOMN

기존의 테이블에서 칼럼을 삭제한다. 한 번에 하나의 칼럼만 삭제 가능하다. 삭제 후 최소 하나의 칼럼이 남아있을 때 삭제할 수 있다.

 

예제) PLAYER 테이블의 ADDRESS 칼럼 삭제

 

ORACLE, SQL Server

 

ALTER TABLE PLAYER
DROP COLUMN ADDRESS;

 

 

MODIFY COLUMN

칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건을 변경할 수 있다.

 

예제) PLAYER_TEMP 테이블의 JOIN_YYYY를 크기 8의 가변길이 문자열로 바꾸고, 향후 입력되는 데이터의 기본 값을 20220304로 하고 NOT NULL을 적용한다.

 

ORACLE

 

ALTER TABLE PLAYER_TEMP
MODIFY (JOIN_YYYY VARCHAR2(8) DEFAULT '20220304' NOT NULL);

 

SQL Server

 

ALTER TABLE PLAYER_TEMP
ALTER COLUMN JOIN_YYYY VARCHAR(8) NOT NULL;

ALTER TABLE PLAYER_TEMP
ADD CONSTRAINT DF_JOIN_YYYY DEFAULT '20220304' FOR JOIN_YYYY;

 

 

RENAME COLUMN

칼럼의 이름 변경. 일부 DBMS에서만 지원한다.

 

ORACLE

 

ALTER TABLE PLAYER_TEMP
RENAME COLUMN PLAYER_ID TO PLAYER_TEMP_ID;

 

SQL Server

 

sp_rename 변경해야할 칼럼명, 새로운 칼럼명, 'COLUMN';
sp_rename 'dbo.PLAYER_TEMP.PLAYER_ID', 'PLAYER_TEMP_ID', 'COLUMN';

 

 

ADD CONSTRAINT

제약조건을 추가한다.

예제) PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다. 제약조건명: PLAYER_FK

PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조한다.

 

ORACLE, SQL Server

 

ALTER TABLE PLAYER
ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

 

 

DROP CONSTRAINT

제약조건을 삭제한다.

 

ORACLE, SQL Server

 

ALTER TABLE PLAYER
DROP CONSTAINT PLAYER_FK;

 

 

3. RENAME TABLE

테이블의 이름을 변경한다.

 

예제) PLAYER_TEMP의 이름을 PLAYER_T로 변경

 

ORACLE

 

RENAME PLAYER_TEMP TO PLAYER_T;

 

SQL Server

 

sp_rename 'dbo.PLAYER_TEMP','PLAYER_T';

 

 

4. DROP TABLE

테이블을 삭제한다.

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

CASCADE CONSTRAINT를 포함할 시 해당 테이블과 관계가 있었던 참조 제약조건도 함께 삭제한다.

SQL Server의 경우 CASCADE CONSTRAINT 옵션이 존재하지 않는다. 따라서 제약조건 삭제 후에 테이블을 삭제할 수 있다.

 

ORACLE, SQL Server

 

DROP TABLE PLAYER_T;

 

 

5. TRUNCATE TABLE

 

테이블 구조는 유지한 채, 테이블에 들어있는 데이터를 모두 삭제한다.

 

ORACLE, SQL Server

TRUNCATE TABLE PLAYER;