[SQLD] SQL 기본 : 2절 데이터 정의어

2절 데이터 정의어(DDL; Data Definition Language)

---

데이터 타입 (앞은 Oracle, 뒤는 SQL Server)

• CHAR(s) : 고정 길이 문자열, 할당된 변수 값의 길이가 s 이하일 때 뒷부분은 공백으로 채워짐
  • s는 최소 1byte, Oracle은 최대 2000 / SQL Server는 최대 8000byte
  • 비교시 뒷부분의 공백을 제거해서 비교
• VARCHAR2(s) : 가변 길이 문자열, 할당되는 변수 값의 길이의 최대값이 s으로 실제 길이(용량)은 줄어듬
  • s는 최소 1byte, Oracle은 최대 4000byte (12c 이후론 Extended Data Types로 32767byte 까지 가능)
  • SQL Server는 VARCHAR(L)을 사용하며, 최대 8000byte (VARCHAR(MAX)는 최대 2GB까지 가능)
• NUMBER(L,D) : 숫자형 (L은 전체 자리 , D는 소수점 자리 수)
  
  • SQL Server은 NUMERIC DECIMAL FLOAT REAL 등 10가지 이상의 타입이 존재
• DATE : 날짜와 시각 정보 데이터 크기 지정이 필요하지 않음
  • Oracle은 1초 단위로 관리하며 ,그 이하는 TIMESTAMP 사용
  • SQL server는 DATE는 단순 날짜만을 나타냄. 시각정보는 DATETIME을 사용하며, 3.33ms 단위로 관리

---

CREATE TABLE

CREATE TABLE 테이블명 (
	칼럼명1 데이터타입 [제약조건...],
	칼럼명2 데이터타입 [제약조건...],
	.
	.
	칼럼명n 데이터타입 [제약조건...] );

-- 예시
	칼럼명 데이터타입 PRIMARY KEY, -- 기본키
	칼럼명 데이터타입 UNIQUE, -- 유니크 제약
	칼럼명 데이터타입 CHECK (조건), -- 조건 체크
	칼럼명 데이터타입 DEFAULT 기본값, -- 기본값 설정
	칼럼명 데이터타입 NOT NULL, -- 널금지
	칼럼명 데이터타입 REFERENCES 테이블명(컬럼명) -- Oracle에서만 가능	
    
  -- CONSTRAINT 구문
    [CONSTRAINT] 제약조건명 제약조건 (컬럼명);
    CONSTRAINT 제약조건명 FOREIGN KEY (칼럼명) REFERENCES 테이블명(칼럼명),

새로운 테이블을 형성하는 SQL 구문                                      

테이블 및 칼럼 명명 규칙

• 1) 알파벳 2) 숫자 3) ‘_’(언더바) 사용가능, Oracle은 4) ‘$’(달러) 5) ‘#’(샵) 사용 가능
  • Oracle은 큰따옴표, SQL Server은 대괄호를 사용해서 명칭을 감싸면 사용가능
  • 테이블명과 컬럼명은 반드시 문자로 시작해야한다.
• 옵션에 따라 다르지만, 기본적으로 대소문자 구분하지 않음
• 테이블명은 단수형 권고

제약조건

데이터 무결성 유지가 목적

• PRIMARY KEY : 테이블 당 하나의 기본키만 정의 가능, 기본키 생성시 DBMS가 자동으로 인덱스를 생성, NULL 불가
• FOREIGN KEY : 으로 다른 테이블의 기본키를 외래키로 지정, 참조 무결성 제약조건
• UNIQUE KEY : 행 데이터를 식별하기 위해 생성함, NULL 가능
• DEFAULT : ‘DEFAULT 값’ 구문으로 기본값 설정
• NOT NULL : NULL 불가능
• CHECK : 입력값의 종류 및 범위 제한

※ NULL: 아직 정의되지 않은 값 또는 현재 데이터를 입력하지 못하는 값, NULL과의 1) 수치연산은 NULL 2) 비교연산은 FALSE 출력

 

옵션(DELETE, MODIFY)

ON DELETE CASCADE : 참조값이 삭제된다면 함께 삭제

ON DELETE SET NULL  : 참조값이 삭제된다면 NULL 로 변경

ON DELETE SET DEFAULT : 참조값 삭제시 DEFAULT 값으로 변경

ON DELETE RESTRICT : child 테이블에 PK 값이 없는 경우만 마스터 테이블 삭제가능

ON DELETE NO ACTION : 참조 무결성을 위반하는 삭제/수정 취하지 않음

 

옵션(INSERT)

ON INSERT AUTOMATIC : 마스터 테이블에 PK가 없으면 마스터 PK를 형성후 CHILD 입력

ON INSERT DEPENDENT : 마스터 테이블에 PK 존재할때만 허용

 

오라클은 제약조건을 설정할 때, 보통 CONSTRAINT 구문을 생략한다.다만, 테이블 수준 (CONSTRAINT ~)에서 따로 할 경우에는 FK의 경우 생략할 수 없다.

복제 테이블

-- Oracle
CREATE TABLE 새_테이블명
AS
SELECT 칼럼1, 칼럼2, ...
	FROM 기존_테이블명
	[WHERE 조건];
    
-- SQL Server
SELECT 컬럼1, 컬럼2, ...
	INTO 새_테이블명
	FROM 기존_테이블명
	[WHERE 조건];

기존 테이블 제약조건 중 NOT NULL만 복사되므로 제약조건을 추가하려면 이후에 ALTER TABLE로 따로 정의

• 단, SQL Server에서는 컬럼에 IDENTITY가 적용되어있다면 함께 복사될 수 있음

WHERE 1=0 을 사용하면 데이터 없이 구조만 복사가능

---

DESCRIBE

-- Oracle
DESCRIBE 테이블명;
DESC 테이블명;
-- SQL SERVER
EXEC sp_help ‘dbo.테이블명';

테이블 구조를 확인하는 SQL 구문으로, Oracle은 DESC로도 사용 가능 

ANSI/ISO 표준이 아니므로 SQL SERVER에서는 제공하지 않으며 스토어드 프로시저로 비슷한 기능 사용 

---

ALTER TABLE

테이블의 구조나 설정 등을 변경하는데 사용하는 SQL 구문

칼럼 추가

ALTER TABLE 테이블명 ADD 추가칼럼명 데이터타입;

테이블의 마지막 칼럼으로 추가됨 (칼럼 위치 지정 불가)

칼럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 삭제컬럼명;

삭제 후 복구 불가

칼럼 설정 변경

-- Oracle
ALTER TABLE 테이블명 MODIFY (칼럼명 데이터유형 [제약조건]);
-- SQL Server
ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 데이터유형 [제약조건];

데이터의 크기는 일반적으로 늘이기만 가능

• 1) NULL만 있거나 2) 아무런 행이 없는 경우에만 데이터 크기 축소 가능

NULL만 있을 때는 데이터 유형도 변경 가능

NULL이 없으면 NOT NULL 제약조건 추가 가능

DEFALUT 사용시, 기본값 변경 작업 이후 발생하는 데이터에 대해서만 기본값이 변경됨

SQL Server은 한번에 바꿀 수 없지만, Oracle은 가능

칼럼명 변경

-- Oracle
ALTER TABLE 테이블명 RENAME COLUMN (구)칼럼명 TO (신)칼럼명;
-- SQL Server
EXEC sp_rename '(구)칼럼명', '(신)칼럼명', 'COLUMN';

ANSI/ISO 표준에 명시된 기능이 아닌 Oracle에서만 사용가능하므로, SQL Server에서는 스토어드 프로시저 사용 

제약조건 추가 / 제거

-- 추가
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명)
-- 예시
ALTER TABLE Product ADD CONSTRAINT product_pk PRIMARY KEY (prod_id);

-- 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

---

RENAME TABLE

-- Oracle
RENAME (구)테이블명 TO (신)테이블명;
ALTER TABLE (구)테이블명 RENAME (신)테이블명;
-- SQL SERVER
EXEC sp_rename 'dbo.(구)테이블명', '(신)테이블명';

테이블의 이름을 변경하는 SQL 구문
Oracle에서만 사용가능하며, SQL Server에서는 스토어드 프로시저 사용

---

DROP TABLE

DROP TABLE 테이블명 [옵션];

테이블의 데이터와 구조 삭제, 복구 불가

Oracle 에서는 CASCADE CONSTRAINT 옵션으로 관련 테이블의 참조 제약조건도 삭제하여 참조 무결성을 준수할 수 있음 (CREATE TABLE에서 ON DELETE CASCADE 옵션으로도 동일 기능 실현 가능)

---

TRUNCATE TABLE 

TRUNCATE TABLE 테이블명;

테이블의 전체 데이터만 삭제하며, 구조는 그대로 남겨둠

Auto-commit으로 복구(Rollback)되지 않으며, Delete와 처리방식이 달라 전체 데이터 삭제시 부하가 더 적음

DML로도 분류되는 경우가 있으나, 내부 처리방식이나 Auto-commit 특성으로 DDL로 분류

디스크 사용량을 초기화

 

최종 수정일 : 2025/03/04

틀린 사항이 있다면 댓글로 알려주시면 감사하겠습니다.