(SQLD) SQL 기본 및 활용1

제1절 관계형 데이터베이스 개요

데이터베이스의 발전

- 1960년대: 플로우차트 중심의 개발 방법을 사용하였으며 파일 구조를 통해 데이터를 저장하고 관리하였다.

- 1970년대: 데이터베이스 관리 기법이 처음 태동되던 시기였으며 계층형(Hierarchical) 데이터베이스, 망형(Network) 데이터베이스 같은 제품들이 상용화 되었다.

- 1980년대: 현재 대부분의 기업에서 사용되고 있는 관계형 데이터베이스가 상용화되었으며 Oracle, Sybase, DB2와 같은 제품이 사용되었다.

- 1990년대: Oracle, Sybase, Informix, DB2, Teradata, SQL Server 외 많은 제품들이 보다 향상된 기능으로 정보시스템의 확실한 핵심 솔루션으로 자리잡게 되었으며, 인터넷 환경의 급속한 발전과 객체 지향 정보를 지원하기 위해 객체 관계형 데이터베이스로 발전하였다.

 

관계형 데이터베이스(Relational Database)

- 1970년 영국의 수학자였던 E.F. Codd 박사의 논문에서 처음으로 관계형 데이터베이스가 소개

- 파일시스템의 경우, 하나의 파일을 많은 사용자가 동시에 검색할 수는 있지만 동시에 입력, 수정, 삭제할 수 없기 때문에 정보의 관리가 어려우므로, 하나의 파일을 여러 사용자나 어플리케이션에서 동시에 사용하기 위해서 원래의 데이터 파일을 여러 개 복사하여 사용하게 된다. 이렇게 여러 개의 데이터 파일이 존재하는 경우에 동일한 데이터가 여러 곳에 저장되는 문제가 발생하고, 하나의 원본 파일에 대한 변경 작업이 발생했을 때 모든 복사본 파일에 대한 변경 작업을 한꺼번에 병행 처리하지 않으면 서로 다른 정보 파일이 존재하기 때문에 데이터의 불일치성이 발생한다.

 결과적으로 파일시스템은 분산된 데이터 간의 정합성을 유지하는데 과다한 노력이 필요하게 되고 데이터의 정합성을 보장하기 힘들게 된다.(단, 단일 사용자나 단일 어플리케이션이 파일시스템을 사용하는 경우 데이터베이스보다 처리 성능이 뛰어나므로 특정 업무에서는 아직도 파일시스템을 유용하게 사용하고 있다.)

 이러한 문제에 대해 관계형 데이터베이스는 정규화를 통한 합리적인 테이블 모델링을 통해 이상(ANOMALY) 현상을 제거하고 데이터 중복을 피할 수 있으며, 동시성 관리, 병행 제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능을 제공하고 있다.

 또한, 관계형 데이터베이스는 메타 데이터를 총괄 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법 등을 체계화할 수 있고, 데이터 표준화를 통한 데이터 품질을 확보할 수 있는 장점을 가지고 있다.

 그리고 DBMS는 인증된 사용자만이 참조할 수 있도록 보안 기능을 제공하고 있다. 테이블 생성 시에 사용할 수 있는 다양한 제약조건을 이용하여 사용자가 실수로 조건에 위배되는 데이터를 입력한다는지, 관계를 연결하는 중요 데이터를 삭제하는 것을 방지하여 데이터 무결성(Integrity)을 보장할 수 있다.

 추가로 DBMS는 시스템의 갑작스런 장애로부터 사용자가 입력, 수정, 삭제하던 데이터가 제대로 반영될 수 있도록 보장해주는 기능과, 시스템 다운, 재해 등의 상황에서도 데이터를 회복/복구할 수 있는 기능을 제공한다.

 

SQL(Structured Query Language)

- 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어.

- SQL 튜닝의 목적은 시스템에 큰 영향을 주는 SQL을 가장 효과적(응답시간, 자원 활용 최소화)으로 작성하는 것이 목표

 

SQL 문장들의 종류

1) 데이터 조작어(DML: Data Manipulation Language)

- SELECT: 데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE라고도 한다.

- INSERT, UPDATE, DELETE: 데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어들을 말한다. 예를 들어 데이터를 테이블에 새로운 행을 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어들을 DML이라고 부른다.

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

- CREATE, ALTER, DROP, RENAME: 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라고 부른다.

3) 데이터 제어어(DCL: Data Control Language)

- GRANT, REVOKE: 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL이라고 부른다.

4) 트랜잭션 제어어(TCL: Transaction Control Language)

- COMMIT, ROLLBACK: 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말한다.

 

테이블

- 반드시 하나 이상의 칼럼을 가져야 한다.

- 데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의 기본 단위

 

ERD(Entity Relationship Diagram)

- 테이블 간 서로의 상관 관계를 그림으로 도식화한 것

- ERD의 구성 요소는 엔터티(Entity), 관계(Relationship), 속성(Attribute) 3가지

 

제2절 DDL(DATA DEFINITION LANGUAGE)

자주 쓰이는 데이터 유형

1) CHARACTER(s)

- 고정 길이 문자열 정보(Oracle, SQL Server 모두 CHAR로 표현)

- s는 기본 길이 1바이트, 최대 길이 Oracle 2,000바이트, SQL Server 8,000바이트

- s만큼 최대 길이를 갖고 고정 길이를 가지고 있으므로 할당된 변수 값의 길이가 s보다 작을 경우에는 그 차이 길이만큼 공간으로 채워진다.

2) VARCHAR(s)

- CHARACTER VARYING의 약자로 가변 길이 문자열 정보(Oracle은 VARCHAR2로 표현, SQL Server는 VARCHAR로 표현)

- s는 최소 길이 1바이트, 최대 길이 Oracle 4,000바이트, SQL Server 8,000바이트

- s만큼의 최대 길이를 갖지만 가변 길이로 조정이 되기 때문에 할당된 변수값의 바이트만 적용된다.(Limit 개념)

3) NUMERIC

- 정수, 실수 등 숫자 정보 (Oracle은 NUMBER로, SQL Server는 10가지 이상의 숫자 타입을 가지고 있음)

- Oracle은 처음에 전체 자리 수를 지정하고, 그 다음 소수 부분의 자리 수를 지정한다. 예를 들어, 정수 부분이 6자리이고 소수점 부분이 2자리인 경우에는 'NUMBER(8,2)'와 같이 된다.

4) DATETIME

- 날짜와 시각 정보(Oracle은 DATE로 표현, SQL Server는 DATETIME으로 표현)

- Oracle은 1초 단위, SQL Server는 3.33ms(millisecond) 단위 관리

 

문자열 유형 CHAR vs VARCHAR

- VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐

- CHAR에서는 문자열을 비교할 때 공백(BLANK)을 채워서 비교하는 방법을 사용. 공백 채우기 비교에서는 우선 짧은 쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한다. VARCHAR 유형에서는 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.

 

CREATE TABLE

- CREATE TABLE 테이블이름 )

    칼럼명1 DATATYPE [DEFAULT 형식],

    칼럼명2 DATATYPE [DEFAULT 형식]

);

- 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.

- 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

- 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)

- 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.

- 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

- 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.

- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

- 한 테이블 안에서 칼럼 이름은 달라야 하지만, 다른 테이블의 칼럼 이름과는 같을 수 있다.

- 테이블 생성시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

- DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.

- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

 

제약조건(CONSTRAINT)

- 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약.

제약조건의 종류

1) PRIMARY KEY(기본키): 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다. 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며, 기본키를 구성하는 칼럼에는 NULL을 입력할 수 없다. 결국 '기본키 제약 = 고유키 제약 & NOT NULL 제약'이 된다.

2) UNIQUE KEY(고유키): 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. 단, NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약위반이 되지 않는다.

3) NOT NULL: NULL 값의 입력을 금지한다. 디폴트 상태에서는 모든 칼럼에서 NULL을 허가하고 있지만, 이 제약을 지정함으로써 해당 칼럼은 입력 필수가 된다. NOT NULL을 CHECK의 일부분으로 이해할 수도 있다.

4) CHECK:입력할 수 있는 값의 범위 등을 제한한다. CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정한다.

5) FOREIGN KEY(외래키): 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다. 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있다.

 

생성된 테이블 구조 확인: Oracle의 경우 "DESCRIBE 테이블명;" 또는 간략히 "DESC 테이블명;"으로 해당 테이블에 대한 정보를 확인할 수 있다. SQL Server의 경우 "sp_help 'dbo.테이블명'":으로 해당 테이블에 대한 정보를 확인할 수 있다.

 

SELECT 문장을 통한 테이블 생성 사례

- SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법(CTAS: Create Table ~ As Select ~)이 있다. 기존 테이블을 이용한 CTAS 방법을 이용할 수 있다면 칼럼별로 데이터 유형을 다시 재정의 하지 않아도 되는 장점이 있다.

 그러나 CTAS 기법 사용시 주의할 점은 기존 테이블의 제약조건 중에 NOT NULL만 새로운 복제 테이블에 적용이 되고, 기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건은 없어진다는 점이다. 제약 조건을 추가하기 위해서는 뒤에 나오는 ALTER TABLE 기능을 사용해야 한다.

 SQL Server에서는 Select ~ Into ~ 를 활용할 수 있다.

<Oracle>

CREATE TABLE TEAM_TEMP

AS SELECT * FROM TEAM;

<SQL Server>

SELECT * INTO TEAM_TEMP FROM TEAM;

 

ALTER TABLE

1) ADD COLIMN

- 기존 테이블에 필요한 칼럼을 추가하는 명령

ALTER TABLE 테이블명

ADD 추가할 칼럼명 데이터 유형;

- 주의할 것은 새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치를 지정할 수는 없다.

ALTER TABLE PLAYER

ADD (ADDRESS VARCHAR2(80));

2) DROP COLUMN

- 테이블에서 필요 없는 칼럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다. 한 번에 하나의 칼럼만 삭제 가능하며, 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 한다. 주의할 부분은 한 번 삭제된 칼럼은 복구가 불가능하다.

ALTER TABLE 테이블명

DRP COLUMN 삭제할 칼럼명;

[예제] Oracle

ALTER TABLE PLAYER

DROP COLUMN ADDRESS;

3) MODIFY COLUMN

- 칼럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경을 포함할 수 있다.

<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 제약조건을 추가할 수 있다.

[예제] Oracle

ALTER TABLE TEAM_TEMP

MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

[예제] SQL Server

ALTER TABLE TEAM_TEMP

ALTER COLUMN ORIG_YYYY VARCAHR(8) NOT NULL;

명령이 완료되었다.

ALTER TABLE TEAM_TEMP

ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY;

4) RENAME COLUMN

- 테이블을 생성하면서 만들어졌던 칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우

ALTER TABLE 테이블명

RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명;

- 칼럼명이 변경되면, 해당 칼럼과 관계된 제약조건에 대해서도 자동으로 변경되는 장점이 있지만, ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시되어 있는 기능이 아니고 Oracle 등 일부 DBMS에서만 지원하는 기능이다.

ALTER TABLE PLAYER

RENAME COLUMN PLAYER_ID TO TEMP_ID;

- SQL Server에서는 sp_rename 저장 프로시저를 이용하여 칼럼 이름을 변경할 수 있다.

sp_rename 변경해야 할 칼럼명, 새로운 칼럼명, 'COLUMN';

sp_rename 'dbo.TEAM_TEMP.TEAM_ID', 'TEAM_TEMP_ID', 'COLUMN';

5) DROP CONSTRAINT

- 테이블 생성 시 부여했던 제약조건을 삭제하는 명령어

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

[예제] Oracle

ALTER TABLE PLAYER

DROP CONSTRAINT PLAYER_FK;

6) ADD CONSTRAINT

- 테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.

ALTER TABLE 테이블명

ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

[예제] Oracle

ALTER TABLE PLAYER

ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);