[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;