1. 데이터베이스(Database)
데이터베이스(Database, DB)는 통합하여 관리되는 데이터의 집합체를 의미합니다. 데이터베이스는 자료를 구조화하여 중복된 데이터를 없애고 효율적인 처리를 할 수 있도록 관리되는데, 데이터베이스를 관리 및 제어하고 다수의 사용자가 데이터에 접근하여 이용할 수 있도록 해주는 소프트웨어를 데이터베이스 관리시스템(Database Management System, DBMS)라고 합니다.
DBMS의 유형은 계층형(Hierarchical), 네트워크형(Network), 관계형(Relational), 객체지향형(Object-Oriented), 객체관계형(Object-Relational) 등으로 분류됩니다.
1) 계층형 모델
계층형 모델은 데이터 간의 관계가 트리 형태로 구성되어 있는 모델입니다. 계층 간 1:M 형태로 구성 가능하며, 데이터를 세그먼트 단위로 관리하고 세그먼트 간의 계층을 트리 구조로 관리합니다.
대통령 아래 국무총리가 있고, 국무총리 아래 부가 있으며, 부 아래 청이 있습니다. 부모 세그먼트는 다수의 자식 세그먼트와 연결되지만 자식 세그먼트는 오직 하나의 부모 세그먼트와 연결됩니다.
이 모델은 한번 구축하면 구조 변경이 어렵고 같은 계층에 접근하는 것이 비효율적이기 때문에 현재는 거의 쓰이고 있지 않습니다.
2) 네트워크형 모델
네트워크형 모델은 계층형 DBMS의 단점을 보완하여 데이터 간에 M:N 구성이 가능한 모델입니다.
계층형 모델의 경우 경찰청에서 국세청에 연결되기 위해서는 행정안전부 -> 국무총리 -> 기획재정부 -> 국세청 순서를 거쳐야 하지만, 네트워크형 모델의 경우 바로 연결될 수 있습니다.
하지만 구조가 복잡하여 유지보수 비용이 많이 들기때문에 계층형과 마찬가지로 현재는 거의 사용하지 않습니다.
3) 관계형 모델
관계형 모델은 키(key)와 값(value)으로 이루어진 데이터들을 행(row)과 열(column)로 구성된 테이블(table) 형태로 저장하고 관리하는 데이터베이스 모델입니다.
오라클(Oracle), SQL Server, MySQL, MariaDB, PostgreSQL 등 대부분의 DBMS는 관계형 DBMS에 해당하며, SQL을 사용하여 데이터를 처리합니다.
2. SQL(Structured Query Language)
SQL(Structured Query Language)은 관계형 데이터베이스에서 사용되는 비절차적 언어로, 데이터의 정의(Definition), 조작(Manipulation), 제어(Control) 등을 위해 사용됩니다.
DBMS 벤더에 따라 사용하는 SQL에 다소 차이가 있기때문에 미국 표준 협회(American National Standards Institute, ANSI)에서는 SQL에 대한 표준을 정하여 발표하였으며, 이를 표준 SQL(ANSI SQL)이라고 합니다.
각각의 벤더는 표준 SQL을 포함하여 추가로 자신만의 기능을 가지고 있는데, 이렇게 변형된 SQL을 오라클에서는 PL/SQL, SQL Server에서는 T-SQL, MySQL에서는 SQL 이라고 부릅니다.
현업에서는 오라클을 많이 사용하고 있으므로 PL/SQL 위주로 설명하도록 하겠습니다.
3. 데이터 정의어(Data Definition Language, DDL)
데이터 정의어(Data Definition Language, DDL)는 데이터베이스의 테이블과 같은 데이터 구조나 구성 요소를 생성, 삭제, 변경하는 언어입니다.
DDL의 대상은 테이블(table), 뷰(view), 인덱스(index), 스키마(Schema) 등의 데이터베이스 객체이며, 명령어는 CREATE, ALTER, RENAME, DROP, TRUNCATE가 있습니다.
1) CREATE
CREATE 명령어는 데이터베이스, 테이블, 인덱스 등을 생성하는 명령어이며, 테이블을 생성하기 위한 CREATE TABLE 문법은 다음과 같습니다.
CREATE TABLE 문법
CREATE TABLE 테이블명( 컬럼1 컬럼1_데이터타입 [NOT NULL] , 컬럼2 컬럼2_데이터타입 [NOT NULL] ... , CONSTRAINT 기본키명 PRIMARY KEY(컬럼명) , CONSTRAINT 고유키명 UNIQUE(컬럼명[, 컬럼명, ...]) , CONSTRAINT 외래키명 FOREIGN KEY(컬럼명) REFERENCES 참조테이블(참조테이블_기본키) , CONSTRAINT 제약조건명 CHECK(조건식) ) |
데이터 타입에는 고정형 길이 문자 데이터인 CHARACTER, 가변형 문자 데이터인 VARCHAR, 숫자 데이터인 NUMERIC, 날짜와 시각 데이터인 DATETIME이 있습니다.
오라클에서는 주로 CHARACTER를 CHAR, VARCHAR를 VARCHAR2, NUMERIC을 NUMBER, DATETIME을 DATE로 표현합니다. 또한 대용량 데이터의 경우 CLOB, BLOB 타입을 사용합니다.
제약조건은 데이터베이스 객체 중 하나이며, 데이터 무결성을 보장하기 위한 용도로 사용됩니다.
무결성은 데이터베이스에 저장된 값들이 정확하고 일관성 있는 데이터임을 나타내는 특성으로, 여기에는 하나의 테이블에 중복된 행이 존재하지 않도록 보장하는 개체 무결성과 참조 관계에 있는 두 테이블의 데이터가 항상 일관된 값을 가짐을 보장하는 참조 무결성이 있습니다.
제약조건에는 NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK 등이 있습니다. UNIQUE와 PRIMARY KEY는 개체 무결성을 보장하고, FOREIGN KEY는 참조 무결성을 보장합니다.
NOT NULL의 경우 해당 컬럼에 반드시 데이터가 입력되도록 합니다. 만약 NULL이 입력된다면 오류가 발생합니다.
NOT NULL 제약조건
CREATE TABLE EMP ( EMP_ID CHAR(8) NOT NULL , EMP_NAME VARCHAR2(20) NOT NULL , BIRTH_DAY DATE ) |
DEFAULT는 기본값을 나타내며, 데이터가 입력되지 않으면 기본값이 입력됩니다.
DEFAULT 제약조건
CREATE TABLE EMP ( EMP_ID CHAR(8) NOT NULL , EMP_NAME VARCHAR2(20) DEFAULT 'anonymous' NULL , BIRTH_DAY DATE ) |
UNIQUE는 테이블의 행을 고유하게 식별하기 위한 고유키를 정의하는 제약조건입니다. 동일한 컬럼에 대하여 중복된 값을 입력할 수 없지만, NULL의 경우 고유키의 제약대상이 아니므로 중복 입력이 가능합니다.
UNIQUE 제약조건
CREATE TABLE EMP ( EMP_ID CHAR(8) UNIQUE , EMP_NAME VARCHAR2(20) DEFAULT 'anonymous' NULL , BIRTH_DAY DATE ) |
해당 쿼리의 경우 EMP_ID가 고유키가 되며, 중복된 값이 입력되면 개체 무결성 제약조건 위반으로 오류가 발생합니다. 다만, NULL의 경우 중복입력이 되더라도 오류를 일으키지 않고 정상적으로 데이터가 입력됩니다.
CONSTRAINT 키워드를 사용하여 고유키 이름을 지정할 수도 있씁니다.
고유키 이름 설정
CREATE TABLE EMP ( EMP_ID CHAR(8) , EMP_NAME VARCHAR2(20) DEFAULT 'anonymous' NULL , BIRTH_DAY DATE , CONSTRAINT EMP_UNIQUE UNIQUE(EMP_ID) ) |
PRIMARY KEY는 테이블의 행을 고유하게 식별하기 위한 기본키를 정의하는 제약조건입니다. UNIQUE + NOT NULL에 해당하기 때문에 NULL의 입력이 불가합니다.
PRIMARY KEY 제약조건
CREATE TABLE EMP ( EMP_ID CHAR(8) PRIMARY KEY , EMP_NAME VARCHAR2(20) DEFAULT 'anonymous' NULL , BIRTH_DAY DATE ) |
UNIQUE와 마찬가지로 CONSTRAINT 키워드를 사용하여 선언할 수도 있습니다.
기본키 이름 설정
CREATE TABLE EMP ( EMP_ID CHAR(8) , EMP_NAME VARCHAR2(20) DEFAULT 'anonymous' NULL , BIRTH_DAY DATE , CONSTRAINT EMP_PK PRIMARY KEY(EMP_ID) ) |
FOREIGN KEY는 테이블 간의 참조 무결성을 위한 제약조건입니다.
FOREIGN KEY 제약조건
CREATE TABLE BOARD ( DOCUMENT_IDX NUMBER PRIMARY KEY , TITLE VARCHAR2(50) NOT NULL , CONTENTS CLOB );
CREATE TABLE COMMENTS ( COMMENT_IDX NUMBER PRIMARY KEY , DOCUMENT_IDX NUMBER NOT NULL REFERENCES BOARD(DOCUMENT_IDX) , CONTENTS CLOB ); |
하나의 게시글에는 여러 댓글이 달릴 수 있으므로, BOARD COMMENTS는 1:M의 관계를 가집니다. 이때 댓글이 어느 게시글에 달렸는지 알기 위해서 DOCUMENT_IDX 컬럼을 통해 게시글을 참조하게 되므로, 참조 무결성을 보장하기 위해 DOCUMENT_IDX를 외래키로 설정합니다.
다른 제약조건과 마찬가지로 CONSTRAINT 키워드를 통해 외래키 이름을 지정할 수 있습니다.
외래키 이름 설정
CREATE TABLE COMMENTS ( COMMENT_IDX NUMBER PRIMARY KEY , DOCUMENT_IDX NUMBER NOT NULL , CONSTRAINT COMMENT_FK FOREIGN KEY(DOCUMENT_IDX) REFERENCES BOARD(DOCUMENT_IDX) , CONTENTS CLOB ); |
참조 무결성 규칙은 자식 테이블의 입력 참조 무결성 규칙과 부모 테이블의 삭제/수정 참조 무결성 규칙으로 나눠집니다.
입력 참조 무결성 규칙에는 DEPENDENT, AUTOMATIC, SET NULL, SET DEFAULT가 있습니다.
DEPENDENT는 대응되는 부모 테이블에 인스턴스가 존재할 때만 자식 테이블에 데이터의 입력을 허용하여 무결성을 보장합니다.
외래키를 설정할 때 NOT NULL 제약조건을 설정해준다면 자식 테이블이 부모 테이블에 대하여 DEPENDENT 상태가 되어 무결성을 보장할 수 있습니다.
만약 아래 쿼리처럼 존재하지 않는 document_idx가 1인 게시글에 댓글을 입력한다면 참조 무결성 위반이 되어 오류를 발생시킵니다.
참조 무결성 제약 조건 위반
INSERT INTO COMMENTS VALUES (1, 1, '댓글'); |
참조 무결성 제약 조건 위반으로 인한 오류 발생 
AUTOMATIC은 자식 테이블의 인스턴스 입력을 항상 허용하며, 대응되는 부모 테이블에 인스턴스가 없다면 이를 자동으로 생성하여 무결성을 보장합니다.
SET NULL은 자식 테이블의 인스턴스 입력을 항상 허용하며, 대응되는 부모 테이블에 인스턴스가 없다면 외래키를 NULL값으로 입력하여 무결성을 보장합니다. 이때 NOT NULL 제약조건이 설정되면 안됩니다.
SET DEFAULT도 자식 테이블의 인스턴스 입력을 항상 허용하며, 대응되는 부모 테이블에 인스턴스가 없다면 외래키를 지정된 기본값으로 입력합니다. 이때 외래키가 설정되면 안됩니다.
삭제/수정 참조 무결성 규칙에는 RESTRICT, CASCADE, SET NULL, SET DEFAULT가 있습니다.
RESTRICT는 대응되는 자식 테이블의 인스턴스가 없는 경우에만 부모 테이블의 인스턴스 삭제/수정을 허용하여 무결성을 보장합니다.
CASCADE는 부모 테이블의 인스턴스 삭제/수정을 항상 허용하고, 대응되는 자식 테이블의 인스턴스를 자동으로 삭제/수정하여 무결성을 보장합니다.
SET NULL은 부모 테이블의 인스턴스 삭제/수정을 항상 허용하고, 대응되는 자식 테이블의 인스턴스의 외래키를 NULL값으로 수정하여 무결성을 보장합니다.
SET DEFAULT는 부모 테이블의 인스턴스 삭제/수정을 항상 허용하고, 대응되는 자식 테이블의 인스턴스의 외래키를 기본값으로 수정하여 무결성을 보장합니다.
외래키를 설정하면 기본적으로 RESTRICT가 적용되어 부모 테이블의 인스턴스 삭제/수정에 제약이 생깁니다. 만약 아래 쿼리처럼 document_idx가 1인 게시글에 댓글이 있을 때 게시글을 삭제한다면 참조 무결성 위반이 되어 오류를 발생시킵니다.
참조 무결성 제약 조건 위반
INSERT INTO BOARD VALUES (1, '제목1', '내용1'); INSERT INTO COMMENTS VALUES (1, 1, '댓글1'); DELETE BOARD WHERE DOCUMENT_IDX = 1; |
참조 무결성 제약 조건 위반으로 인한 오류 발생 
SQL에서는 ON DELETE 또는 ON UPDATE 키워드를 통해 부모 테이블의 인스턴스 삭제/수정에 제약을 줄 수 있습니다.
ON DELETE CASCADE 제약조건을 적용한 후 document_idx가 1인 게시글을 삭제한다면 해당 게시글과 연관된 모든 댓글이 자동으로 삭제됩니다.
아래 쿼리를 순차적으로 실행하세요.
ON DELETE CASCADE
DROP TABLE COMMENTS;
CREATE TABLE COMMENTS ( COMMENT_IDX NUMBER PRIMARY KEY , DOCUMENT_IDX NUMBER NOT NULL REFERENCES BOARD(DOCUMENT_IDX) ON DELETE CASCADE , CONTENTS CLOB );
INSERT INTO COMMENTS VALUES (1, 1, '댓글1'); DELETE BOARD WHERE DOCUMENT_IDX = 1;
SELECT * FROM COMMENTS; |
게시글을 삭제할 때 댓글이 함께 삭제되었기 때문에 COMMENTS 테이블에는 인스턴스가 없는 것을 확인할 수 있습니다.
COMMENTS 테이블 
오라클에서는 ON UPDATE 키워드를 지원하지 않습니다. SQL Server에서 아래와 같이 테이블을 생성한 후 게시글의 document_idx를 수정한 후 COMMENTS 테이블을 조회하면 document_idx가 자동으로 수정된 것을 확인할 수 있습니다.
ON UPDATE CASCADE
CREATE TABLE BOARD ( DOCUMENT_IDX INT PRIMARY KEY , TITLE NVARCHAR(50) NOT NULL , CONTENTS TEXT );
CREATE TABLE COMMENTS ( COMMENT_IDX INT PRIMARY KEY , DOCUMENT_IDX INT NOT NULL REFERENCES BOARD(DOCUMENT_IDX) ON DELETE CASCADE ON UPDATE CASCADE , CONTENTS TEXT );
INSERT INTO BOARD VALUES (1, '제목1', '내용1'); INSERT INTO COMMENTS VALUES (1, 1, '댓글1'); UPDATE BOARD SET DOCUMENT_IDX = 2 WHERE DOCUMENT_IDX = 1;
SELECT * FROM COMMENTS; |
COMMENTS 테이블 
CASCADE 대신에 SET NULL을 설정하면 부모 테이블의 삭제 또는 수정시 자식 테이블의 외래키는 NULL값으로 설정됩니다. 이때 주의할 것은 NOT NULL 제약조건을 설정하면 안됩니다.
SET DEFAULT의 경우 DEFAULT 제약 조건을 설정해야하며, 오라클은 SET DEFAULT를 지원하지 않습니다.
CHECK는 입력할 수 있는 값의 범위를 제한하는 제약조건 입니다.
CHECK 제약조건
DROP TABLE COMMENTS;
CREATE TABLE COMMENTS ( COMMENT_IDX NUMBER PRIMARY KEY , DOCUMENT_IDX NUMBER NOT NULL REFERENCES BOARD(DOCUMENT_IDX) ON DELETE CASCADE , CONTENTS CLOB CHECK (LENGTH(CONTENTS) > 10) ); |
COMMENTS의 CONTENTS 컬럼은 10글자 이상이어야 합니다. 만약 10글자 미만이라면 제약조건 위반으로 오류를 발생시킵니다.
제약조건 위반으로 인한 오류 발생 
2) ALTER
ALTER 명령어는 컬림이나 제약조건을 추가/삭제하기 위한 명령이며, 문법은 아래와 같습니다.
컬럼 추가(ADD)
ALTER TABLE 테이블명 ADD 컬럼명 데이터유형 [기본값] [NOT NULL]; |
컬럼 수정(MODIFY) ALTER TABLE 테이블명 MODIFY 컬럼명 데이터유형 [기본값] [NOT NULL]; |
컬럼 삭제(DROP) ALTER TABLE 테이블명 DROP COLUMN 컬럼명; |
컬럼명 수정(RENAME) ALTER TABLE 테이블명 RENAME COLUMN 기존_컬럼명 TO 새로운_컬럼명; |
제약조건 삭제(DROP CONSTRAINT) ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명; |
제약조건 추가(ADD CONSTRAINT) ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건; |
3) RENAME
RENAME 명령어는 테이블 등 데이터베이스 객체의 이름을 변경하는 명령어입니다.
테이블 이름 변경
RENAME 기존_테이블명 TO 새로운_테이블명; |
SQL Server의 경우 RENAME 대신에 SP_RENAME을 사용합니다.
SQL Server 테이블 이름 변경 SP_RENAME 기존_테이블명, 새로운_테이블명; |
4) DROP
DROP 명령어는 테이블 등 데이터베이스 객체를 삭제하는 명령어 입니다.
테이블 삭제
만약 해당 테이블을 참조하는 다른 테이블이 있다면, 참조 무결성 제약을 제거해야만 테이블을 삭제할 수 있습니다. 오라클의 경우 CASCADE CONSTRAINT를 설정하면 해당 테이블을 참조하는 참조 무결성 제약을 함께 삭제할 수 있습니다.
참조 무결성 제약 삭제 DROP TABLE BOARD CASCADE CONSTRAINT; |
해당 쿼리를 실행한다면 COMMENTS 테이블에서 DOCUMENT_IDX의 외래키가 삭제됩니다.
5) TRUNCATE
TRUNCATE 명령어는 테이블에 저장된 모든 인스턴스를 삭제하는 명령어 입니다. 테이블 스키마까지 삭제하는 DROP 명령어와 달리 테이블 스키마는 남겨두고 테이블에 속하는 인스턴스만 제거하여 저장공간을 재사용할 수 있습니다.
테이블 초기화
DML의 DELETE와 유사하지만 ROLLBACK이 가능한 DELETE와 달리 TRUNCATE는 ROLLBACK이 불가능합니다.
| 
스팟 
