3. SQL의 개념과 SQL로 데이터베이스를 정의(SQLの概念とSQLでＤＢを作ろう）

SQL의 기본 개념

 

relational database에서는 tuple들의 중복은 불가능하였지만,

SQL에서의 relation, 즉 table에서는 중복된 tuple이 존재 가능하다. (multiset : 동일 항목이 여러 개 출현하는 것을 허락하

는 집합체.)

또한 관계형 DMBS마다 정의된 SQL의 문법이 조금씩 다르다. ( 그러나, SQL이 표준 언어로 약속은 되어 있다.)

그래서, MySql과 Oracle 등의 관계형 DBMS들 사이에 SQL 문법이 약간씩 다르다. 

( 고로 Query문이 다른  DBMS상에서 100% 호환되지 않는다.)

 

예제를 통해 SQL로 DB 정의!!( MySQL 설치 방법 및 CMD에서의 실행법 )

InnoDB : Mysql이 제공하는 여러 DB Engine 중 하나!

 

DB 정의해보기

SHOW DATABASES : 모든 DB들의 목록을 LISTING 해준다.(적어도 Mysql에서는 대소문자를 구분을 안 한다.)

CREATE DATABASE company : company라는 이름으로 DB를 만드는 SQL문.

( 다시, show databases; 명령어를 입력하여 company라는 데이터베이스가 만들어 졌는지를 확인하자)

show databases를 통해 모든 DB 목록을 볼 수가 있다. 근데  아직까지는 company라는 DB를 만들었을 뿐이지, 어떠한 DB

를 사용할 것인지 또 현재 어떠한 DB가 활성화(사용)되고 있는지를 설정해 주지 않았다. 

SELECT database() : 현재 활성화(사용)되고 있는 DB를 출력해줌

현재 NULL로 설정이 돼 있다.

즉, 아직 아무것도 활성화시켜 주지 않았다는 뜻이다. 

USE company : company라는 DB를 활성화(사용)하겠다고 설정(아래 참조)

(다시, SELECT database()를 통해 잘 설정이 되었는지 확인)

만약, company를 삭제시키고 싶다면????

DROP DATABASE company : company 삭제!(우리는 company를 비활성화 해주지 않을 거다.)

다른 RDBMS에서는 

1. Database안에서 schema가 정의가 되고

2. schema 안에서 Table이 정의가 된다.

-> DB > Schema > Table

MySQL의 경우

1. Database 안에서 Table이 정의가 된다.( DB == SCHEMA이기에...)

 

table 정의

Company DB 안에 위와 같이 4가지의 Schema를 정의를 하였다. (보통, DB를 구축하기 전, 젤 먼저 Schema부터 먼저 정의를 한다.)

WORKS_ON : 어떤 직원(employee)가 어떤 프로젝트(project)를 맡고 있는지를 나타냄.

Attribute의 참조 관계 

DEPARTMENT :: leader_id - > EMPLOYEE::id

EMPLOYEE::dept_id - > DEPARTMENT::id

PROJECT::leader_id - > EMPLOYEE::id

WORKS_ON::empl_id - > EMPLOYEE:id

WORKS_ON:proj_id - > PROJECT:id

자 이제 4개의 schema를 바탕으로, 4개의 table을 생성해 보자. 

 

CREATE TABLE [테이블 명] : TABLE 생성.

아래에서 Attribute의 자료형과 Constraints에 대해서 상세히 살펴보자.

 

고정 소수점 

DECIMAL( precision, scale ) :  scale만큼의 소수점 자리를 포함해서 총 precision만큼의 길이를 갖는다. 

 

CHAR vs VARCHAR

결론적으로는 어떤 RDMBS를 쓰느냐에 따라 다르다.

예를 들어, MySql에서는 VARCHAR가 메모리 절약적인 면에서는 유리하지만 실행 시간 측면에서는 CHAR보다 불리하다

고 한다.

그래서 전화번호와 같은 고정 크기는 CHAR로 선언하고, 크기를 모르는 경우에는 VARCHAR를 권장한다고 한다. 

Postgre SQL의 공식 문서에서는 VARCHAR를 권장하고 있다. 

사이즈가 큰 문자열(TEXT....) : 게시판 글이나 이메일의 본문 등 정도의 길이가 긴 문자열!!!

(Postgre SQL의 경우, 사이즈가 큰 문자열의 자료형으로서 TEXT 딱 1개만 존재한다고 한다.)

 

DATATIME vs TIMESTAMP

차이점

1. 나타낼 수 있는 시간의 범위가 다르다.

2. TIMESTAMP는 time-zone의 영향을 받는다.

-> UTC(Universal Time Coordinated)는 세계 표준 시간으로서, MySql 서버에 설치된 time-zone 혹은 MySQL 내부적으로 설정한 time-zone을 거쳐, Storage에 세계 표준 시간(UTC)로 저장됐다가, 나중에 읽어 올때에 또 다시 time-zone을 거쳐서 세계 표준 시간이 다른 시간대로 변경이 된다. 

 

MySQL : NULL의 중복을 허용

MS-SQL : NULL의 중복을 허용X. 딱 1개의 NULL만이 저장되어야 한다.

 

MySQL의 경우, UNIQUE를 설정을 해도 NULL이 여러 개 있을 수 있으므로, NULL을 1개도 원하지 않는 경우에 위와 같이

{NOT NULL UNIQUE}형태로 이 2가지가 같이 사용이 되는 것이 일반적이라고 한다. 

 

자 이제 department table에 대한 설명이 끝이 났다. 이제는 EMPLOYEE 테이블을 만들어 보자.

위 그림을 아래에서 자세히 해석을 해보자.

reference_option : 참조되고 있는 값이 DELETE/UPDATE가 될 때 참조하고 있는 값, 즉 FOREIGN KEY값을 어떻게 할 것인가를 설정.

NO_ACTION : 아직 트랜잭션에 대해서는 배우지 않았지만, 간략하게 설명을 하자면, 트랙잭션 안에는 여러 개의 SQL문들이 들어 가 있다. 트랙잭션이 실행이 되고 있는 동안에는 참조값들의 삭제/변경을 허락을 하지만, 트랜잭션의 실행이 끝이 나도 Referential integrity constraint를 위반을 하고 있다면, 삭제/변경을 금지 시킨다는 것이다. 

MySQL의 경우 : SET DEFAULT를 지원하지X. (완전히 서로 다른 reference_option은 CASCADE, SET NULL, RESTRICT, 이 3개라고 생각하면 된다.)

Postgre SQL의 경우 : reference option 5가지 모두를 지원!

 

SHOW CREATE TABLE [TABLE명] : Table 생성 시, 걸어 놓았던 contraints들을 확인 가능하다. 

ALTER TABLE : Table의 schema를 변경을 할 때 사용!

-> 왜 department를 만들 때, leader_id를 foreign key로 지정하지 안 했을까? 

-> 그건 department를 만들 시점에는 employee table이 만들어 지지 않았기 때문이다. 

 

ALTER TABLE 사용 시 주의점

이미 서비스 중인 TABLE의 SCHEMA를 변경하는 것이라면, 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요