본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.
 |
|
2. DDL
1. 데이터 유형
데이터 유형은 데이터베이스의 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준이라고 생각하면 된다. 즉 특정 칼럼을 정의할 때 선언한 데이터 유형은 그 칼럼이 받아들일 수 있는 자료의 유형을 규정한다. 따라서 선언한 유형이 아닌 다른 종류의 데이터가 들어오려고 하면 데이터베이스는 에러를 발생시킨다.
또한 데이터 유형과 더불어 지정한 크기(SIZE)도 중요한 기능을 제공한다. 즉 선언 당시에 지정한 데이터의 크기를 넘어선 자료가 입력되는 상황도 에러를 발생시키는 중요한 요인이기 때문이다.
ANSI/ISO 기준에서는 NUMERIC Type 의 하위 개념으로 NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, REAL, DOUBLE PRECISION 을 소개하고 있다. SQL Server 와 Sybase 는 ANSI/ISO 기준의 하위 개념에 맞추어서 작은 정수형, 정수형, 큰 정수형, 실수형 등 여러 숫자 타입을 제공하고 있으며, 추가로 MONEY, SMALLMONEY 등의 숫자 타입도 가지고 있다. 반면, Oracle 은 숫자형 타입에 대해서 NUMBER 한 가지 숫자 타입의 데이터 유형만 지원한다.
대표적인 데이터 유형
- CHARACTER(s)
- 고정 길이 문자열 정보 (CHAR로 표현)
- s는 기본 길이 1바이트, 최대 길이 Oracle 2,000 바이트, SQL Server 8,000 바이트
- 고정 길이를 가지고 있으므로 변수 값의 길이가 s보다 작을 경우, 그 차이는 빈 공간으로 채워진다.
- VARCHAR(s)
- 가변 길이 문자열 정보 (Oracle은 VARCHAR2, SQL Server는 VARCHAR로 표현)
- s는 기본 길이 1바이트, 최대 길이 Oracle 4,000 바이트, SQL Server 8,000 바이트
- s만큼의 최대 길이를 갖지만 길이가 가변적으로 조정되기 때문에 할당된 변수값의 바이트만 적용된다.
- NUMERIC
- 정수, 실수 등 숫자 정보 (Oracle은 NUMBER, SQL Server는 다양한 숫자 타입 제공)
- Oracle은 처음에 전체 자리 수를 지정하고, 그 다음 소수 부분 자리 수를 지정 (Ex) 'NUMBER(8, 2)')
- DATETIME
- 날짜와 시각 정보 (Oracle은 DATE로 표현, SQL Server는 DATETIME으로 표현)
- Oracle은 1초 단위, SQL Server는 3.33ms 단위 관리
CHAR 에서는 문자열을 비교할 때 공백(BLANK)을 채워서 비교하는 방법을 사용한다. 공백 채우기 비교에서는 우선 짧은 쪽의 끝에 공백을 추가하여 2 개의 데이터가 같은 길이가 되도록 한다. 그리고 앞에서부터 한 문자씩 비교한다. 그렇기 때문에 끝의 공백만 다른 문자열은 같다고 판단된다. 그에 반해 VARCHAR 유형에서는 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.
VARCHAR, NUMERIC 유형에서 정의한 길이나 자릿수의 의미는 해당 데이터 유형이 가질 수 있는 최대한의 한계값을 정의한 것이라고 보아야 한다.
2. CREATE TABLE
CREATE TABLE 테이블이름
(
칼럼명 1 DATATYPE [DEFAULT 형식],
칼럼명 2 DATATYPE [DEFAULT 형식],
칼럼명 3 DATATYPE [DEFAULT 형식]
);
주의할 규칙
- 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
- 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
- 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
- 테이블 이름을 지정하고 각 칼럼들은 괄호 "( )" 로 묶어 지정한다.
- 각 칼럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다.
- 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)
- 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
- 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
- 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
- 테이블 생성시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명은 대문자로 만들 어진다.
- DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.
- 칼럼에 대한 제약조건이 있으면 CONSTRAINT 를 이용하여 추가할 수 있다.
한 테이블 안에서 칼럼 이름은 달라야 하지만, 다른 테이블의 칼럼 이름과는 같을 수 있다. 예를 들면 선수 테이블의 TEAM_ID, 팀 테이블의 TEAM_ID 는 같은 칼럼 이름을 가지고 있다. 실제 DBMS 는 팀 테이블의 TEAM_ID 를 PC 나 UNIX 의 디렉토리 구조처럼 ‘DB 명+DB 사용자명+테이블명+칼럼명’처럼 계층적 구조를 가진 전체 경로로 관리하고 있다. 이처럼 같은 이름을 가진 칼럼들은 기본키와 외래키의 관계를 가지는 경우가 많으며, 향후 테이블 간의 조인 조건으로 주로 사용되는 중요한 연결고리 칼럼들이다.
제약조건은 PLAYER_NAME, TEAM_ID 칼럼의 데이터 유형 뒤에 NOT NULL 을 정의한 사례와 같은 칼럼 LEVEL 정의 방식과, PLAYER_PK PRIMARY KEY, PLAYER_FK FOREIGN KEY 사례처럼 테이블 생성 마지막에 모든 제약조건을 기술하는 테이블 LEVEL 정의 방식이 있다. 하나의 SQL 문장 내에서 두 가지 방식은 혼용해서 사용할 수 있으며, 같은 기능을 가지고 있다.
제약조건(CONSTRAINT)이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약이다. 테이블을 생성할 때 제약조건을 반드시 기술할 필요는 없지만, 이후에 ALTER TABLE 을 이용해서 추가, 수정하는 경우 데이터가 이미 입력된 경우라면 처리 과정이 쉽지 않으므로 초기 테이블 생성 시점부터 적합한 제약 조건에 대한 충분한 검토가 있어야 한다.
제약 조건의 종류
- PRIMARY KEY
- 행 데이터를 고유하게 식별하기 위한 기본키 정의
- 한 테이블에 하나의 기본키 제약만 정의할 수 있음
- 기본키 제약을 정의하면, DBMS는 자동으로 UNIQUE 인덱스 생성 및 NULL 입력이 불가
- 즉, 기본키 제약 = 고유키 제약 & NOT NULL 제약
- UNIQUE KEY
- 행 데이터를 고유하게 식별하기 위한 고유키 정의
- NULL 값을 가진 행이 여러 개 있어도 고유키 제약 위반되지 않음
- NOT NULL
- NULL 값 입력 금지
- 디폴트 상태에서는 모든 컬럼에 NULL을 허가
- CHECK
- 입력할 수 있는 값의 범위 등을 제한
- TRUE or FALSE 평가할 수 있는 논리식 지정
- FOREIGN KEY
- 테이블 간의 관계 정의를 위해 기본키를 다른 테이블 외래키로 복사할 때 생성
- 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있음
NULL이란?
- '아직 정의되지 않은 미지의 값'이거나 '현재 데이터를 입력하지 못하는 경우'
- 공백이나 숫자 0과는 전혀 다른 값
DEFAULT란?
- 값이 명시적으로 지정되어 있지 않을 경우의 기본값
- 데이터 입력시 명시된 값을 지정하지 않은 경우 NULL 값이 입력되고, DEFAULT 값을 정의했다면 사전에 정의된 기본값이 입력된다.
생성된 테이블 구조 확인을 위해, Oracle 의 경우 “DESCRIBE 테이블명;” 또는 간략히 “DESC 테이블명;”으로 해당 테이블에 대한 정보를 확인할 수 있다. SQL Server 의 경우 “sp_help ‘dbo.테이블명’”으로 해당 테이블에 대한 정보를 확인할 수 있다.
CTAS 기법: SELECT 문장을 활용한 테이블 생성 방법
CREATE TABLE TEAM_TEMP AS
SELECT *
FROM TEAM;
3. ALTER TABLE
ADD COLUMN
ALTER TABLE 테이블명 ADD 추가할 컬럼명 데이터 유형;
-- Oracle
ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));
-- SQL Server
ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);-
새롭게 추가되는 컬럼의 위치를 지정할 수 없고, 추가된 컬럼은 마지막 컬럼이 된다.
DROP COLUMN
ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;
-- Oracle
ALTER TABLE PLAYER DROP COLUMN ADDRESS;
-- SQL Server
ALTER TABLE PLAYER DROP COLUMN ADDRESS;- 한 번에 하나의 컬럼만 삭제 가능하며, 컬럼 삭제 후 테이블 내에 최소 하나 이상의 컬럼이 존재해야 한다.
- 주의할 점은 한 번 삭제된 컬럼은 복구가 불가능하다.
MODIFY COLUMN
-- Oracle
ALTER TABLE 테이블명
MODIFY (컬럼명 1 데이터 유형 [DEFAULT 식] [NOT NULL],
컬럼명 2 데이터 유형 …);
-- SQL Server
ALTER TABLE 테이블명
ALTER (칼럼명 1 데이터 유형 [DEFAULT 식] [NOT NULL],
칼럼명 2 데이터 유형 …);-
컬럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. - NULL 값만 있거나, 아무 행도 없으면 컬럼의 폭을 줄일 수 있다.
- NULL 값만 가지고 있으면 데이터 유형을 변경할 수 있다.
- DEFAULT 값을 변경하면 추후 입력 값에만 영향을 미친다.
- NULL 값이 없을 경우에만 NOT NULL 제약 조건을 추가할 수 있다.
RENAME COLUMN
-- Oracle
ALTER TABLE 테이블명 RENAME COLUMN 변경해야 할 컬럼명 TO 새로운 컬럼명;
-- SQL Server
sp_rename 변경해야 할 컬럼명, 새로운 컬럼명, 'COLUMN';
DROP/ADD CONSTRAINT
-- DROP CONSTRAINT
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;
-- ADD CONSTRAINT
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);
ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID)
REFERENCES TEAM(TEAM_ID);
4. RENAME TABLE
-- Oracle
RENAME 기존 테이블명 TO 새로운 테이블명;
-- SQL Server
sp_rename 기존 테이블명, 새로운 테이블명;
5. DROP TABLE
-- Oracle
DROP TABLE 테이블명 [CASCADE CONSTRAINT];
- CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조 제약조건에 대해서도 삭제한다는 의미
- SQL Server 에서는 CASCADE 옵션이 존재하지 않으므로 테이블 삭제 전에 제약 조건 또는 참조 테이블을 먼저 삭제해야 한다.
6. TRUNCATE TABLE
TRUNCATE TABLE 테이블명;- 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE 을 실행하면 된다.
- TRUNCATE 는 데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML 로 분류할 수도 있지만 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DDL 로 분류하였다.
- 테이블의 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE 을 권고한다. 단, TRUNCATE TABLE 의 경우 정상적인 복구가 불가 능하므로 주의해야 한다.
↓SQL 전문가 가이드 요약 목록
1장. 데이터 모델링의 이해
Part 1. 데이터 모델링의 이해
Part 2. 데이터 모델과 성능
2장. SQL 기본 및 활용
Part 1. SQL 기본
Part 2. SQL 활용
Part 3. SQL 최적화 기본원리
따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.
'데이터 사이언스 > SQL' 카테고리의 다른 글
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.4. TCL (0) | 2020.11.22 |
|---|---|
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.3. DML (0) | 2020.11.19 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.1. 관계형 데이터베이스 개요 (6) | 2020.11.18 |
| [SQLD 학습 자료 요약] 데이터 모델링의 이해 2.6. 분산 데이터베이스와 성능 (0) | 2020.11.18 |
| [SQLD 학습 자료 요약] 데이터 모델링의 이해 2.5. 데이터베이스 구조와 성능 (0) | 2020.11.17 |
