220304 D+53 관계형 데이터베이스 SQL(NoSQL, MySQL), ACID, Schema & Query Design, 데이터베이스 정규화

1. 데이터베이스의 필요성

✷ 영속성(persistence): 데이터를 생성한 프로그램의 실행이 종료되더라도 사라지지 않는 데이터의 특성

✷ 데이터베이스

: 애플리케이션에서 사용할 데이터를 체계적으로 디스크에 담도록 만들어진 데이터 저장 전용 프로그램.

 

① In-Memory

‣ JavaScript에서 데이터를 다룰 때에는 프로그램이 실행될 때에만 존재하는 데이터가 있다.

‣ JavaScript에서 변수를 만들어 저장한 경우, 프로그램이 종료될 때 해당 프로그램이 사용하던 데이터도 사라진다.

=== 변수 등에 저장한 데이터가 프로그램의 실행에 의존한다.

 

⇒ 예기치 못한 상황으로부터 데이터를 보호할 수 없고,

프로그램이 종료된 상태라면 데이터를 원하는 시간에 받아올 수 없으며,

데이터의 수명이 프로그램의 수명에 의존하게 된다.

 

② File I/O

: 파일을 읽는 방식으로 작동하는 형태.

엑셀 시트나 CSV 같은 파일의 형태는

In-Memory에 비해 데이터를 저장하는 방식으로 적절해 보이지만 한계가 분명히 존재한다.

 

‣ 데이터가 필요할 때마다 전체 파일을 매번 읽어야 한다.

파일의 크기가 커질수록 이 작업은 버겁고 비효율적이어서 File I/O 방식의 큰 단점이다.

 

‣ 파일이 손상되거나 여러 개의 파일들을 동시에 다뤄야 하거나 하는 등

복잡하고 데이터량이 많아질수록 데이터를 불러들이는 작업이 점점 힘들어진다.

 

⇒ 관계형 데이터베이스에서는 하나의 CSV 파일이나 엑셀 시트를 한 개의 테이블로 저장할 수 있다.

→ 한 번에 여러 개의 테이블을 가질 수 있기 때문에 SQL을 활용해 데이터를 불러오기 수월하다.

→ 엑셀 시트와 CSV 파일 등처럼 특정 형태의 파일은 대용량의 데이터를 저장하기 위한 목적이 아니다.

 

 

2. SQL(Structured Query Language)

: 주로 관계형 데이터베이스에서 사용하는 데이터베이스용 프로그래밍 언어.

: 구조화된 쿼리 언어.

 

→ 데이터베이스에 쿼리를 보내 원하는 데이터를 가져오거나 삽입할 수 있다.

→ 데이터가 구조화된 테이블을 사용하는 데이터베이스에서 활용할 수 있다.

 

✷ 쿼리(query)

: 무언가를 검색할 때 입력하는 검색어도 일종의 쿼리라고 볼 수 있다.

→ 저장되어 있는 데이터를 필터하기 위한 질의문.

 

① NoSQL(MongoDB와 같은 문서 지향 데이터베이스)

: 데이터의 구조가 고정되어 있지 않은 데이터베이스.

→ 테이블을 사용하지 않고 데이터를 다른 형태로 저장한다.

 

✷ fault(결함): 다양한 원인에 의해 제 기능을 못할 때.

failure(실패): 어떤 기능을 수행할 수 없는 상태를 의미하며,

결함에 의해 발생하지만 결함이 있다고 항상 실패하는 것은 아님.

 

②. 데이터베이스 관련 명령어

✷ 명령어가 끝나면 뒤에 세미콜론(;) 붙이기 필수!

 

‣ 데이터베이스 / 테이블 확인하기

SHOW DATABASES;

SHOW TABLES;

 

‣ 데이터베이스 생성(SQL Create DB)

CREATE DATABASE 데이터베이스_이름;

 

‣ 데이터베이스 사용

: 데이터베이스를 이용하여 테이블을 만들거나 수정, 삭제하는 작업을 위해서는

  데이터베이스를 사용해야겠다는 명령부터 전달해야 한다.

USE 데이터베이스_이름;

 

‣ 테이블 생성(SQL CREATE TABLE)

: 테이블은 필드(표의 열)와 함께 만들어야 한다.

→ SQL 콘솔에서 Enter키를 이용하여 여러 줄의 코드를 입력할 수 있다.

필드 이름	필드 타입	그 외의 속성
id	숫자	Primary key이면서 자동 증가하도록 설정
name	문자열 (최대 255개의 문자)
email	문자열 (최대 255개의 문자)

 

ex) user 테이블을 만드는 예시

// varchar: 가변길이. 실질적인 데이터와 길이 정보도 같이 저장된다.

CREATE TABLE user (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(255),
  email varchar(255)
);

 

‣ 테이블 정보 확인

DESCRIBE user;
// desc user;

 

mysql> describe user;
// mysql> desc user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| email | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

 

‣ SQL Drop DB / TABLE

DROP DATABASE 데이터베이스_이름;

DROP TABLE 테이블_이름;

 

‣ SQL Alter Table

컬럼 / 열 추가 (Add)

ALTER TABLE table_name ADD COLUMN ex_column varchar(32) NOT NULL;
ALTER TABLE table_name ADD (새로 추가할 열 이름 varchar(자리수));
varchar처럼 데이터 타입을 넣어주면 됨.

 

컬럼 / 열 변경 (Modify)

ALTER TABLE table_name MODIFY COLUMN ex_column varchar(16) NULL;
ALTER TABLE table_name MODIFY (변경할 열 이름 varchar(자리수));
varchar처럼 데이터 타입을 넣어주면 됨.

 

컬럼 이름까지 변경 (Change)

ALTER TABLE table_name CHANGE COLUMN ex_column ex_column2 varchar(16) NULL;
varchar처럼 데이터 타입을 넣어주면 됨.

 

컬럼 삭제 (Drop)

ALTER TABLE table_name DROP COLUMN ex_column;

 

테이블 이름 변경 (RENAME)

ALTER TABLE table_name1 RENAME table_name2;

 

 

‣ SQL Not Null

‣ SQL Unique

‣ SQL Primary Key

‣ SQL Foreign Key

‣ SQL Default

‣ SQL Auto Increment

‣ SQL Dates

 

✷ 잘못 입력해서 다시 입력할 때
ctrl + C 누르고 엔터



✷ mysql 완전히 종료할 때
ctrl + Z

 

 

② -1. 기본 쿼리문 알아보기(SQL 사용에 필요한 기본 문법)

‣ Select

: 데이터셋에 포함될 특성을 특정함.

// 일반 문자열
SELECT 'hello world';

// 숫자
SELECT 2;

// 간단한 연산
SELECT 15 + 3;

 

‣ From

: 테이블 관련 작업을 할 경우 반드시 입력해야 함(필수).

FROM 뒤에는 결과를 도출해낼 데이터베이스 테이블을 명시한다.

// 특정 특성을 테이블에서 사용
SELECT 특성_1 FROM 테이블_이름;

// 몇가지의 특성을 테이블에서 사용
SELECT 특성_1, 특성_2 FROM 테이블_이름;

// 테이블의 모든 특성 선택
// *(wildcard): 전부 선택할 때 사용됨
// desc는 그 테이블의 특성이 어떤 것인지에 대해서만 나오고
// select * from 테이블_이름은 특성과 데이터의 내용까지 같이 나온다(desc와 다름)
SELECT * FROM 테이블_이름;

 

‣ Where

: 필터 역할을 하는 쿼리문. 선택적 사용 가능.

→ 저장된 레코드 필터링. 그룹화 전에 데이터를 필터 할 때 사용.

 

✷ IS NULL / NOT NULL

// 특정 값과 동일한 데이터 찾기
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 = "특정 값";

// 특정 값을 제외한 값 찾기
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 <> "특정 값";

// 특정값보다 크거나 작은 데이터를 필터할 때
// < >
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 > "특정 값";

// 비교하는 값을 포함하는 이상, 이하 값
// <= >=
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 <= "특정 값";

// 문자열에서 특정값과 비슷한 값들을 필터할 때
// 'LIKE'
// \%
// \* 사용.
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 LIKE "%특정 문자열%";
(WHERE 특정_2 LIKE "%a" --> a로 끝나는 단어를 찾을 때)
(WHERE 특정_2 LIKE "a%" --> a로 시작하는 단어를 찾을 때)

// 리스트의 값들과 일치하는 데이터를 필터할 때
// IN 사용
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 IN ("특정값_1", "특정값_2");

// 값이 없는 경우 NULL을 찾을 때는 IS 사용
SELECT * FROM 테이블_이름
WHERE 특성_1 IS NULL;

// 값이 없는 경우를 제외할 때
// NOT 앞에 붙이기
SELECT * FROM 테이블_이름
WHERE 특성_1 IS NOT NULL;

// AND, OR
SELECT * FROM 테이블_이름
WHERE 특성_1 = 결과_1 AND(OR) 특정_2 = 결과_2;

 

 

‣ Order By

: 돌려받는 데이터 결과를 어떤 기준으로 정렬하여 출력할지 결정한다. 선택적 사용 가능.

// 기본 === 오름차순(ASC) 정렬
SELECT * FROM 테이블_이름 ORDER BY 특성_1;

// 내림차순(DESC) 정렬
SELECT * FROM 테이블_이름 ORDER BY 특성_1 DESC;

 

‣ Limit

: 결과로 출력할 데이터의 개수를 정할 수 있다. 선택적 사용 가능.

  쿼리문에서 사용할 때 가장 마지막에 추가함.

// 데이터 결과를 200개만 출력한다
SELECT * FROM 테이블_이름 LIMIT 200;

 

‣ Distinct

: 유니크한 값을 받고 싶을 때. 중복된 값을 없애줌.

// 특성_1을 기준으로 유니크한 값들만 선택함
// SELECT DISTINCT
SELECT DISTINCT 특성_1 FROM 테이블_이름;

 

// 특성_1, 특성_2, 특성_3의 유니크한 '조합' 값들을 선택함.
SELECT
  DISTINCT
    특성_1
    ,특성_2
    ,특성_3
FROM 테이블_이름;

 

‣ Insert Into () Values ()

: 테이블을 select로 만들어준 후 데이터를 안에 넣어주는 것.

1. INSERT INTO 테이블_이름(원하는 컬럼 데이터의 내용), VALUES(숫자, '문자열' ... );

2. SELECT * FROM 테이블_이름;

 

‣ a와 b 사이에 있는 값을 가져올 때 사용하는 between

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

 

‣ Null Values

 

‣ Update(Update Set)

UPDATE TABLE_NAME SET 수정할_내용;

 

✷ UPDATE와 MODIFY의 차이점

UPDATE - 데이터의 레코드를 수정할 때 사용한다.
사용법
UPDATE [테이블명] SET [컬럼명=데이터값] WHERE [컬럼명=데이터값]

MODIFY - 데이터의 컬럼 타입을 변경할 때 사용한다.
사용법
ALTER TABLE [테이블명] MODIFY [변경할 컬럼명] [컬럼 타입]

 

‣ Delete(Delete From)

// 특정 조건 삭제하기
DELETE FROM 테이블명 WHERE 필드명 = '조건';

ex) user라는 테이블에서 id가 5인 레코드 삭제하기
DELETE FROM USER WHERE ID=5;

// 전체 테이블의 내용 삭제하기
// drop은 폴더를 삭제하는 것이고 delete는 폴더의 내용을 삭제하는 것이라고 생각하면 편함
DELETE FROM 테이블명

 

‣ Like

 

‣ Wildcards

 

‣ Aliases

 

‣ Joins

: 반드시 조건을 명시한다.

 

✷ inner join, outer join

✷ join

INNER JOIN - 기준 테이블과 조인 테이블 모두 데이터가 존재해야 조회됨

OUTER JOIN - 기준 테이블에만 데이터가 존재하면 조회됨

(LEFT, RIHGT = 기준이 되는 테이블을 지정함)

- Inner Join

: 둘 이상의 테이블에서 서로 공통된 부분만 출력된다(=== 교집합).

 

→ 대부분의 JOIN문은 INNER JOIN을 의미하기 때문에

JOIN을 명시하지 않아도 기본적으로 INNER JOIN이 된다.

 

→ 대부분의 복합 질의문은 INNER JOIN문이다.

// INNER JOIN이나 JOIN으로 실행 가능.
// 둘 이상의 테이블을 서로 공통된 부분 기준으로 연결함.
SELECT * FROM 테이블_1
JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B;


// AS: 별칭 느낌으로 붙여줄 때 사용한다
// ON: ON 뒤에는 조건이 붙는다
SELECT * FROM 테이블_1 AS 테이블_1_별칭
INNER JOIN 테이블_2 AS 테이블_2_별칭
ON 테이블_1_.특성_A = 테이블_2.특성_B;

// INNER JOIN 대신 쉼표(,)를 붙이는 것만으로도
// INNER JOIN임을 기본적으로 알 수 있다
// 다만 쉼표를 붙인 INNER JOIN 쿼리문은 복합문질의문이 아닌 일반문으로 해석,
// 조건을 쓸 때 ON 대신 WHERE를 쓴다
SELECT * FROM 테이블_1 AS 테이블_1_별칭, 테이블_2 AS 테이블_2_별칭
WHERE 테이블_1_.특성_A = 테이블_2.특성_B;

 

- Outer Join

- Left Join

: 두 개의 테이블이 있다고 했을 때

왼쪽을 기준으로 왼쪽에 있는 모든 테이블의 내용이 오른쪽의 테이블과 동일한 결과가 합쳐져 출력됨.

(완벽한 합집합이 아닌 부분합집합 개념)

 

- Right Join

: 두 개의 테이블이 있다고 했을 때

오른쪽을 기준으로 오른쪽에 있는 모든 테이블의 내용이 왼쪽의 테이블과 동일한 결과가 합쳐져 출력됨.

(이 또한 부분합집합 개념)

 

→ 조건은 필수로 써야 함.

→ 왼쪽 테이블이 기준인지 오른쪽 테이블이 기준인지 명시해주어야 함.

// 다양한 선택지가 있는 OUTER JOIN
// 기본 구문
SELECT * FROM 테이블_1 AS 별칭_1 [LEFT or RIGHT] OUTER JOIN 테이블_2 AS 별칭_2 ON 조건식;


// LEFT OUTER JOIN으로 LEFT INCLUSIVE 실행
SELECT * FROM 테이블_1
LEFT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B;


// RIGHT OUTER JOIN으로 RIGHT INCLUSIVE 실행
SELECT * FROM 테이블_1
RIGHT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B;

 

 

‣ Group By

: 데이터를 조회할 때 그룹으로 묶어서 조회한다.

GROUP BY 쿼리로 간단하게 State에 따라 그룹화할 수 있다.

 

(쿼리의 결과를 확인하면, 데이터가 중간에 비어있는 것을 확인할 수 있다.

데이터베이스에서 데이터를 불러오는 과정에서

State에 따라 그룹을 지정했지만, 그룹에 대한 작업 없이 조회만 했다.

그래서 쿼리의 결과로 나타나는 데이터는 각 그룹의 첫 번째 데이터만 표현된다.)

SELECT * FROM 테이블_이름
GROUP BY State;

// COUNT(), MAX(), MIN(), SUM(), AVG()
SELECT COUNT()column_name(s) FROM table_name
GROUP BY column_name(s);

 

‣ Count()

: 레코드의 개수를 헤아릴 때 사용함.

// 모든 레코드에 대한 COUNT 함수 사용 예시
// 각 그룹의 첫 번째 레코드와 각 그룹의 레코드 개수를 집계하여 리턴
SELECT *, COUNT(*) FROM 테이블_이름 GROUP BY STATE;


// 각 state에 해당하는 레코드 개수를 확인하는 COUNT 함수 사용 예시
SELECT STATE, COUNT(*) FROM 테이블_이름 GROUP BY STATE;

 

‣ Sum()

: 레코드의 합 리턴하는 함수.

// invoice_items 테이블에서 invoiceId 필드를 기준으로 그룹화, unitprice 필드 값의 합 구하기

SELECT INVOICEID, SUM(UNITPRICE) FROM INVOICE_ITEMS
GROUP BY INVOICEID;

 

‣ AVG()

: 레코드의 평균값 계산 함수.

SELECT TrackId, AVG(UnitPrice) FROM invoice_items GROUP BY TrackId;

 

‣ MAX() / MIN()

: 레코드의 최댓값 / 최솟값 리턴.

// 각 고객이 지불한 최대 / 최소 금액 리턴
SELECT CustomerId, MAX(Total) FROM invoices GROUP BY CustomerId
SELECT CustomerId, MIN(Total) FROM invoices GROUP BY CustomerId

 

‣ HAVING

: GROUP BY로 조회된 결과를 필터링하는 쿼리.

→ 그룹화한 결과에 대한 필터.

// invoices 테이블을 CustomerId로 그룹화하고 그 평균이 6을 초과한 결과 조회
SELECT CUSTOMERID, AVG(TOTAL) FROM INVOICES
GROUP BY CUSTOMERID HAVING AVG(TOTAL) > 6.00

 

 

✷ 여러 쿼리문 한 번에 쓰기

// 브라질에서 온 고객들을 도시별로 묶은 후,
// 각 도시 수에 따라 내림차순 정렬한다.
// CustomerId에 따라 오름차순으로 정렬한 3개의 결과만을 요청하는 예시

SELECT c.CustomerId, c.FirstName, count(c.City) as 'City Count'
FROM customers AS c
JOIN employees AS e ON c.SupportRepId = e.EmployeeId
WHERE c.Country = 'Brazil'
GROUP BY c.City
ORDER BY 3 DESC, c.CustomerId ASC
LIMIT 3;

 

// 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 구하고
// invoices 테이블에 접근.
SELECT CustomerId, AVG(Total) FROM invoices

// CustomerId 필드가 10 이상인 레코드들을 조회
WHERE CustomerId >= 10

// CustomerId를 기준으로 그룹화
GROUP BY CustomerId

// Total 필드의 총합이 30 이상인 결과들만 필터링
HAVING SUM(Total) >= 30

// 필드를 기준으로 오름차순 정렬한 결과를 리턴
ORDER BY 2

 

 

 

2-1. SQL / NoSQL(구조화 쿼리 언어 / 비구조화 쿼리 언어)

→ 만들어진 방식, 저장하는 정보의 종류, 저장하는 방법 등에 차이가 있음.

 

① 관계형 데이터베이스

(MySQL, Oracle, SQLite, PostgresSQL, MariaDB)

→ 테이블의 구조와 데이터 타입 등을 사전에 정의하고,

테이블에 정의된 내용에 알맞은 형태의 데이터만 삽입할 수 있다.

 

→ 행(row)과 열(column)로 구성된 테이블에 데이터를 저장한다.

각 열은 하나의 속성에 대한 정보를 저장하고, 행에는 각 열의 데이터 형식에 맞는 데이터가 저장된다.

⇒ 특정한 형식을 지키기 때문에, 데이터를 정확히 입력했다면 데이터를 사용할 때에는 매우 수월하다.

 

→ 관계형 데이터베이스에서는 데이터를 입력할 때 스키마에 맞게 입력해야 한다.

 

→ SQL을 활용해 원하는 정보를 쿼리할 수 있다. === 관계형 데이터베이스에서는 스키마가 뚜렷하게 보인다.

⇒ 관계형 데이터베이스에서는 테이블 간의 관계를 직관적으로 파악할 수 있다.

 

→ 데이터베이스의 ACID 성질을 준수해야 하는 경우

✷ ACID(Atomicity(원자성), Consistency(일관성), Isolation(격리성), Durability(지속성)) 

: 데이터베이스 내에서 일어나는 하나의 트랜잭션에 의한 상태의 변화를 수행하는 과정에서

  안정성을 보장하기 위해 필요한 성질.

 

✷ 트랜잭션

: 여러 개의 작업을 하나로 묶은 실행 유닛.

: 데이터베이스의 상태를 변환시키는 논리적 기능을 수행하기 위해 행해지는

  하나 이상의 쿼리를 모아놓은 하나의 작업 단위.

 

→ 각 트랜잭션은 하나의 특정 작업으로 시작을 해 묶여 있는 모든 작업을 다 완료해야 정상적으로 종료한다.

작업이 하나라도 실패를 하게 되면 트랜잭션도 실패이고, 모든 작업이 성공적이면 트랜잭션 또한 성공이다.

성공 또는 실패 라는 두 개의 결과만 존재하여 미완료된 작업 없이 모든 작업을 성공해야 한다.

 

‣ 원자성(Atomicity)

: 하나의 트랜잭션에 속해있는 모든 작업이 전부 성공 / 전부 실패해서 결과를 예측할 수 있어야 함.

→ 둘 중 하나의 작업이라도 실패한다면 하나의 단위로 묶여있는 모든 작업이 실패하게 만듦

    (기존 데이터 보호)

    (때때로 충돌 요인에 대해 선택지를 제공함.)

 

→ 묶여있는 여러 작업이 부분 실행되는 경우 데이터가 오염될 수 있음

(업데이트를 누가 했는지 모름 / 업데이트 날짜 누락 등)

 

 

‣ 일관성(Consistency)

: 데이터베이스의 상태가 일관되어야 한다는 성질.

→ 트랜잭션이 일어난 이후의 데이터베이스의 상태는 이전과 같이

    제약이나 규칙을 만족해야 한다(유효해야 한다).

 

→ 모든 고객은 반드시 이름을 가지고 있어야 한다는 데이터베이스의 제약이 있다는 가정 하에

이름 없는 새로운 고객을 추가하는 쿼리 / 기존 고객의 이름을 삭제하는 쿼리 등의

트랜잭션은 일관성을 위반하는 경우이다(일관되지 않은 상태를 가지게 된다).

 

 

‣ 격리성 / 고립성(Isolation)

: 모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다.

→ 다른 트랜잭션의 작업 내용을 알 수 없다.

 

→ 트랜잭션이 동시에 실행될 때 / 연속 실행될 때의 데이터베이스 상태는 동일해야 한다.

 

→ 실제로 동시에 여러 개의 트랜잭션들이 수행될 때,

각 트랜잭션은 고립(격리) 되어 있어 연속으로 실행된 것과 동일한 결과를 나타낸다.

 

ex)

A가 B 계좌로 10만원을 보내는 것과 C 계좌로 10만원을 보내는 것은 각각 다른 트랜잭션이다.

(동시 / 연속으로 일어나도 계좌에서 돈이 빠져나간다는 상태는 동일해야 한다)

 

 

‣ 지속성(Durability)

: 하나의 트랜잭션이 성공적으로 수행되었을 때

해당 트랜잭션에 대한 로그가 남아야 하며, 영구적이어야 한다(런타임 / 시스템 오류가 발생할지라도).

→ 계좌이체를 성공적으로 실행한 후 해당 은행 데이터베이스에 오류가 발생하여 종료되더라도

계좌이체 내역은 기록으로 남아야 한다.

 

→ 계좌이체 하기 전 시스템 오류 등에 의해 종료되면 해당 내역은 실패로 돌아가고

각 계좌들은 계좌 이체하기 전 상태들로 돌아간다.

 

⇒ SQL을 사용하면 데이터베이스와 상호 작용하는 방식을 정확하게 규정할 수 있기 때문에

데이터베이스에서 데이터를 처리할 때 발생할 수 있는 예외적인 상황을 줄이고,

데이터베이스의 무결성을 보호할 수 있다.

 

전자 상거래를 비롯한 모든 금융 서비스를 위한 소프트웨어 개발에서는

반드시 데이터베이스의 ACID 성질을 준수해야 하기 때문에

일반적으로 SQL을 이용한 관계형 데이터베이스를 사용합니다.

 

→ 소프트웨어에 사용되는 데이터가 구조적이고 일관적인 경우

: 소프트웨어(프로젝트)의 규모가 많은 서버를 필요로 하지 않고 일관된 데이터를 사용하는 경우,

  관계형 데이터베이스를 사용하는 경우가 많다.

  다양한 데이터 유형과 높은 트래픽을 지원하도록 설계된 NoSQL 데이터베이스를 사용해야만 하는 이유가 없다.

 

 

② NoSQL

(MongoDB , Cassandra)

: 주로 데이터가 고정되어 있지 않은 데이터베이스.

→ NoSQL에 스키마가 반드시 없는 것은 아니다.

→ 데이터를 읽어올 때 schema on read 방식에 따른다(스키마에 따라 데이터를 읽어 온다).

→ 데이터를 입력하는 방식에 따라, 데이터를 읽어올 때 영향을 미친다.

(읽어올 때에만 데이터 스키마가 사용된다고 하여, 데이터를 쓸 때 정해진 방식이 없다는 의미는 아니다.)

 

‣ key-value 타입

: 속성을 key(속성 이름)-value(속성에 연결된 데이터 값)쌍으로 나타내는 데이터를 배열의 형태로 저장한다.

ex) Redis, Dynamo

 

‣ 문서형(document)데이터베이스

: 데이터를 문서처럼 저장하는 데이터베이스.

JSON과 유사한 형식의 데이터를 문서화하여 저장한다.

각각의 문서는 하나의 속성에 대한 데이터를 갖고 있고, 컬렉션이라고 하는 그룹으로 묶어 관리함.

ex) MongoDB

 

‣ wide-column 데이터베이스

: 데이터베이스의 열에 대한 데이터를 집중 관리하는 데이터베이스.

각 열에는 key-value 형식으로 데이터가 저장되고,

컬럼 패밀리(column families)라고 하는 열의 집합체 단위로 데이터를 처리할 수 있다.

 

하나의 행에 많은 열을 포함할 수 있어 유연성이 높다.

 

데이터 처리에 필요한 열을 유연하게 선택할 수 있다는 점에서

규모가 큰 데이터 분석에 주로 사용되는 데이터베이스 형식.

ex) Casandra, HBase

 

‣ 그래프(graph) 데이터베이스

: 자료구조의 그래프와 비슷한 형식으로 데이터 간의 관계를 구성하는 데이터베이스.

노드(nodes)에 속성별(entities)로 데이터를 저장하고 각 노드 간 관계는 선(edge)으로 표현한다.

ex) Neo4J, InfiniteGraph

 

→ 데이터의 구조가 거의 / 전혀 없는 대용량의 데이터를 저장하는 경우

: 대부분의 NoSQL 데이터베이스는 저장할 수 있는 데이터 유형에 제한이 없다.

언제든지, 필요에 따라 데이터의 새 유형 추가 가능.

소프트웨어 개발에 정형화되지 않은 많은 양의 데이터가 필요한 경우 NoSQL 적용이 더 효율적일 수 있음.

 

→ 클라우드 컴퓨팅 및 저장 공간을 최대한 활용하는 경우

: 클라우드 기반으로 데이터베이스 저장소를 구축하면 저렴한 비용의 솔루션 제공받기 가능.

소프트웨어 데이터베이스의 확장성이 중요하다면 번거로움 없이 확장할 수 있는 NoSQL.

 

→ 빠르게 서비스를 구축하는 과정에서 데이터 구조를 자주 업데이트하는 경우

: 스키마를 미리 준비할 필요가 없기 때문에 빠르게 개발하는 과정에서 유리함.

시장에 빠르게 프로토타입을 출시해야 하는 경우,

버전별로 많은 다운타임 없이 데이터 구조를 자주 업데이트해야 하는 경우에 적합함

 

✷ 다운타임: 데이터베이스 서버를 오프라인으로 전환하여 데이터 처리를 진행하는 작업 시간.

 

 

③ SQL 기반의 데이터베이스 / NoSQL 데이터베이스의 차이점

‣ 데이터 저장(Storage)

→ NoSQL: key-value, document, wide-column, graph

→ 관계형 데이터베이스

: SQL을 이용해 데이터를 테이블에 저장하는 방식.

미리 작성된 스키마를 기반으로 정해진 형식에 맞게 데이터를 저장해야 함.

 

‣ 스키마(Schema)

→ SQL: 고정된 형식의 스키마 필요.

처리하려는 데이터 속성별로 열에 대한 정보를 미리 정해두어야 함.

스키마는 나중에 변경할 수 있지만, 데이터베이스 전체를 수정 / 오프라인(down-time)으로 전환할 필요가 있음.

 

→ NoSQL: 동적으로 스키마 형태 관리 가능.

행을 추가할 때 즉시 새로운 열 추가 가능.

개별 속성에 대해 모든 열에 대한 데이터를 반드시 입력하지 않아도 됨.

 

‣ 쿼리(Querying)

→ 관계형 데이터베이스는 테이블의 형식과 테이블 간의 관계에 맞춰 데이터를 요청해야 하기 때문에

    정보를 요청할 때 SQL과 같이 구조화된 쿼리 언어를 사용한다.

 

→ 비관계형 데이터베이스의 쿼리는 데이터 그룹 자체를 조회하는 것에 초점을 둔다. 

구조화되지 않은 쿼리 언어로도 데이터 요청이 가능함(UnQL / Unstructured Query Language).

 

 

‣ 확장성(Scalability)

→ SQL 기반 관계형 데이터베이스: 수직적 확장(높은 메모리, CPU를 사용하는 확장).

데이터베이스가 구축된 하드웨어의 성능을 많이 이용하기 때문에 비용이 많이 듦.

여러 서버에 걸쳐 데이터베이스 관계를 정의할 수 있으나 매우 복잡하고 시간이 많이 소모됨.

 

→ NoSQL: 수평적 확장(보다 값싼 서버 증설, 클라우드 서비스 이용 확장).

많은 트래픽을 보다 편리하게 처리할 수 있으며 상대적으로 비용이 저렴함.

(저렴한 범용 하드웨어 / 클라우드 기반의 인스턴스에 NoSQL 데이터베이스 호스팅이 가능하기 때문에)

 

 

3. Schema & Query Design

‣ 스키마(schema === 데이터베이스의 청사진)

: 데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티 간의 관계에 대한 설명 

→ 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세를 정의한 메타데이터의 집합.

(메타데이터: 어떠한 목적을 가지고 만들어진 데이터)

 

‣ 데이터(data): 각 항목에 저장되는 값.

‣ 테이블(table): 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적됨.

 

 

‣ 엔티티(Entity): 고유한 정보의 단위. 데이터로 표현하려고 하는, 여러 속성들로 구성된 객체.

수강신청 데이터베이스 예시에서 세 개의 엔티티는 교수진, 수강 강의, 학생이 된다.

이러한 엔티티는 데이터베이스에서 테이블로 표시할 수 있다.

 

 ‣ 필드(Field)

: 각 엔티티의 특성을 설명함. 열(column)에 해당됨.

테이블에 저장된 모든 항목에는 해당 필드가 포함된다.

 

‣ 레코드(Record)

: 테이블의 행에 저장된 데이터(항목).

예시 항목처럼 '성악기초론'을 가르치는 '성악과'의 '에밀리' 교수님.

 

‣ 교수진과 수강 강의의 관계: 한 교수님이 여러 수강 과목을 강의한다.

⇒ 1:N(one-to-many, 일대다)관계.

⇒ 하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우를 말한다.

 

① 스키마에서 정의하기

→ 교수진 테이블에 수강 강의 테이블을 저장하는 방법으로 데이터베이스 관리해보기

 

‣ 키(key): 테이블의 각 레코드를 구분할 수 있는 값.

 

‣ primary key(기본 키)

: 어떠한 테이블에 있는 고유(변하지 않는)한 값(예시에서는 ID).

 

‣ foreign key(외래 키)

: 다른 테이블에서 어떠한 테이블의 기본 키(primary key)를 참조할 때 해당 값.

 

→ 교수진 테이블에 있는 ClassId라는 필드는

    수강 강의 테이블에서 특정 레코드를 고유하게 식별하는 외래 키이다.

 

→ 만약 교수가 맡아야 하는 최대 수업 수를 늘리게 되면(맥시멈을 모른다),

열의 크기는 고정되기 때문에 수업 Id를 담을 공간이 부족할 수 있다.

 

→ 한 열에 여러 값을 저장하면 검색하는 데에 시간이 오래 걸린다.

(한 명의 교수의 강의에서 학생을 검색하기 위해 해당 교수의 ClassId 열의 값을 반복해야 함).

⇒ 이 방법은 별로 좋은 것 같지 않아 다른 방법을 생각해보자.

 

 

② 교수진 테이블을 수강 강의 테이블에 저장하는 방법

 

 

‣ 수강 강의 테이블을 학생명 테이블과 연결하는 방법

: 여러 강의는 여러 명의 학생들로 구성되어 있다.

  한 학생은 여러 개의 강의들을 들을 수 있다.

→ N:N(many to many, 다대다)관계.

⇒ 여러 개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우.

    양방향에서 다수의 레코드를 가질 수 있다.

 

 

(색칠한 부분은 해당 학생이 해당 강의를 수강한다는 의미)

해당 표를 좌표 찍듯이 써본다고 했을 때

 

학생명과 수강 강의명을 각각 번호로 할당된 ID로 교체하면 된다.

(1, 2) ⇒ classID(1) StudentID(2)를 포함한다는 의미이다.

 

→ 가운데에 있는 수강 강의 / 학생 테이블은 SQL원칙들을 위반하지 않고

스키마에서 수업 대 학생 관계를 잘 보여주므로, 조인 테이블이다.

=== 일대다를 두 번 쓴 것과 같다. 

 

⇒ 다대다 관계를 위해 스키마를 디자인할 때 조인 테이블을 만들어 관리한다.

 

→ 한 학생이 조인 테이블에서 여러 번 등장하기 때문에

학생 테이블과 수강 강의 / 학생 테이블은 일대다 관계이다.

 

→ 새롭게 추가된 조인 테이블이 기존의 다대다 관계를 두 개의 일대다 관계로 나눈 것을 알 수 있다.

 

 

✷ 자기 참조 관계(Self Referencing Relationship)

: 테이블 내에서의 관계.

ex) 추천인이 누구인지 파악하기 위해 사용하는 경우

‣ user_id: 기본 키(primary key)

‣ name: 사용자 이름

‣ recommend_id: 추천인 아이디

→ User 테이블의 recommend_id는 User 테이블의 user_id와 연결되어 있다.

→ 한 명의 유저(user_id)는 한 명의 추천인(recommend_id)을 가질 수 있다.

→ 여러 명이 한 명의 유저를 추천인으로 등록할 수 있다.

⇒ 일대다 관계라고 생각할 수 있지만,

일대다 관계는 일반적으로 서로 다른 테이블의 관계를 나타낼 때 표현하는 방법.

 

✷ SQL-Cheat-Sheet

 

4. SQL 종류

① DDL(Data Definition Language)

: 데이터를 정의할 때 사용하는 언어.

→ CREATE(테이블을 만들 때), DROP(테이블을 제거할 때) 등이 이에 해당함.

→ 데이터베이스 테이블 같은 오브젝트를 정의할 때 사용함.

 

② DML(Data Manipultation Language)

: 데이터베이스에 데이터를 저장할 때 사용하는 언어.

→ INSERT, DELETE, UPDATE 등

새로운 레코드를 추가하거나 데이터를 삭제, 변경하는 문법이 이에 해당함.

 

③ DCL(Data Control Language)

: 데이터베이스에 대한 접근 권한과 관련된 문법.

→ 어느 유저가 데이터베이스에 접근할 수 있는지 권한을 설정한다.

→ GRANT(권한을 주는 문법), REVOKE(권한을 가져가는 문법).

 

④ DQL(Data Query Language)

: 정해진 스키마 내에서 쿼리할 수 있는 언어.

→ SELECT가 이에 해당함.

→ DQL은 DML의 일부분으로 취급하기도 함.

 

⑤ TCL(Transaction Control Language)

: DML을 거친 데이터의 변경 사항을 수정할 수 있음.

→ COMMIT(DML이 작업한 내용을 데이터베이스에 커밋),

    ROLLBACK(커밋했던 내용을 다시 롤백하는 문법)

 

5. SQL Advanced

① CASE 사용하기

→ if문 같은 기능

→ 특정 조건에 따라 다른 결과를 받을 수 있음.

SELECT CASE
      WHEN CustomerId <= 25 THEN 'GROUP 1'
      WHEN CustomerId <= 50 THEN 'GROUP 2'
      ELSE 'GROUP 3'
    END
  FROM customers
    
    
CustomerId 필드값에 따라 3개의 그룹('GROUP 1', 'GROUP 2', 'GROUP 3')으로 나뉜다.
CustomerId 필드값이 25 이하인 경우에는 'GROUP 1',
26부터 50 사이인 경우에는 'GROUP 2', 51 이상은 'GROUP 3' 으로 분류한다.

 

② SUBQUERY

→ 쿼리문 작성 시 포함되는 다른 쿼리문

→ 실행되는 쿼리에 중첩으로 위치해 정보를 전달함.

⇒ 서브 쿼리는 소괄호로 감싸줘야 함.

 

→ 서브 쿼리의 결과는 개별 값이나 레코드 리스트이다.

→ 서브 쿼리의 결과를 하나의 칼럼으로 사용할 수 있다.

SELECT CustomerId, 
CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2)
FROM customers
WHERE CustomerId < 6;

 

‣ IN / NOT IN

IN: 특정 값이 서브쿼리에 있는 지 확인할 수 있음
NOT IN을 사용하면 서브쿼리에서 조회된 10 미만을 제외한(10 초과)레코드를 조회함.

// customers 테이블에서 
// CustomerId의 값이 서브쿼리에서 돌려받는 값에 속한 결과들만 조회하는 쿼리문
SELECT *
FROM customers
WHERE CustomerId IN (SELECT CustomerId FROM customers WHERE CustomerId < 10);

서브쿼리에서는 CustomerId가 10 이하인 데이터를 돌려줌
최종 조회된 데이터의 CustomerI도 10 이하.

 

‣ EXISTS / NOT EXISTS

돌려받는 서브쿼리에 존재하는 레코드 확인.
존재한다면 TRUE(참), 아니면 FALSE(거짓)


// employees 테이블에서부터 Employeed필드 조회
// 서브쿼리로 customers 테이블의 SupportRepld 필드값과 
// employees 테이블의 EmployeeId 필드값을 비교,
// 일치하는 레코드들을 가져옴.
SELECT EmployeeId
FROM employees e
WHERE EXISTS (
	SELECT 1
	FROM customers c
	WHERE c.SupportRepId = e.EmployeeId
	)
ORDER BY EmployeeId;

 

‣ FROM

→ 쿼리문과 서브 쿼리를 사용해 조회된 결과를 하나의 테이블이나 조회할 대상으로 지정하여 사용할 수 있음.

SELECT *
FROM (
  SELECT CustomerId
  FROM customers
  WHERE CustomerId < 10
  );

 

 

6. 데이터베이스 정규화

→ 데이터베이스 설계에 따라 데이터가 어떻게 저장될 지 그 구조를 결정하기 때문에

데이터베이스 정규화는 데이터베이스의 설계와 관련이 있다.

 

① Data Redundancy(데이터 중복)

: 실제 데이터의 동일한 복사본 / 부분적인 복사본.

→ 데이터를 복구할 때 더 수월할 수 있지만 대체로 문제점을 몇 가지 지닌다.

• 일관된 자료 처리의 어려움

• 저장 공간 낭비

• 데이터 효율성 감소

 

② Data Integrity(데이터 무결성)

: 데이터 수명 주기 동안 정확성과 일관성을 유지하는 것.

→ 데이터 정규화는 데이터 무결성 강화 목적을 지님.

⇒ 입력된 데이터가 오염되지 않고 입력된 데이터 그대로 데이터를 사용할 수 있다.

 

③ Anomaly(데이터 이상 현상)

: 기대한 데이터와 다른 이상 현상

 

• update anomaly(갱신 이상)

: 여러 행(레코드)에 걸쳐 동일한 데이터가 있을 때,

어떤 행을 갱신해야 하는 지 논리적인 일관성이 없는 경우 발생함.

 

다음과 같은 테이블이 존재하고 두 개의 레코드가 동일한 사람일 때,

519번을 갱신하는 경우 어떤 행의 데이터를 갱신해야 하는지 알 수 없다.

 

• insertion anomaly(삽입 이상)

: 데이터를 삽입하지 못하는 경우.

 

새로운 직원이 들어왔을 때, Dr.Newsome이 가르칠 수업이 정해지지 않은 경우,

수업을 NULL값으로 지정하지 않는 이상 담당 수업이 없으면 테이블에 데이터를 추가하지 못한다.

담당 수업이 있어야만 테이블에 추가할 수 있는 이상 현상이 발생한다.

 

• deletion anomaly(삭제 이상)

: 데이터의 특정 부분을 지울 때 의도치 않게 다른 부분도 함께 지우는 현상.

한 직원이 담당하는 수업을 삭제하는 경우, 이 수업 데이터를 삭제하려면 레코드 전체가 사라지며

결국 의도치 않게 직원의 다른 데이터도 함께 삭제되는 현상이 발생한다.