SQLite3 / DBeaver / ElephantSQL / NoSQL

• SQL 특징을 설명할 수 있어야 합니다.
• 데이터베이스 관계를 설정할 수 있어야 합니다.
• SQL 싱글 테이블 관련 문법을 활용할 수 있어야 합니다.

Database의 필요성

1. In-Memory

제일 먼저 파이썬에서 데이터를 다룰 때에는 프로그램이 실행될 때에만 존재하는 데이터가 있습니다.

파이썬에서 변수를 만들어 저장한다고 해도 프로그램 실행이 종료가 되는 순간 해당 프로그램이

사용하던 데이터도 같이 없어짐. 그 뜻은 데이터가 프로그램에 의존한다는 것입니다.

이렇게 된다면 원하는 데이터를 원하는 시간에 꺼내오기가 힘들어지고 데이터의 수명이

프로그램의 수명에 의존하게 됩니다.

• Data가 Program에 의존 
• 프로그램 실행 종료 --> 해당 프로그램 데이터  없어짐

2. File I/O ( Input / Output )

파일을 읽어오는 방식으로 작동하는 형태, 엑셀 시트나 CSV 와 같은 파일 형태는 얼핏 보기에 나빠 보이지 않습니다.

하지만 한계는 분명히 존재합니다.

 

* 엑셀 시트 or CSV

• 파일을 매번 읽어와야 한다는 것 자체가 하나의 단점입니다. 특히 파일의 크기가 커질수록 이 작업은 버거워집니다.
• 파일이 손상되거나 여러 개의 파일들을 동시에 다뤄야 하거나 하는 등 복잡하고 데이터량이 많아질수록 점점 힘들어지기도 합니다.

* 관계형 데이터베이스

• 하나의 CSV 파일이나 엑셀 시트를 한 개의 테이블로 저장가능
• ==> 한번에 여러 개의 테이블을 가질 수 있기 때문에 SQL 을 활용해 데이터를 가져오기가 더 수월

SQL Structured Query Language

데이터베이스 언어의 기준으로 주로 관계형 데이터베이스에서 사용

데이터가 구조화된 테이블을 사용하는 데이터베이스에서 활용 가능 = 데이터가 구조가 고정

( <-> 데이터 구조가 고정되어 있지 않은 데이터베이스들은 NoSQL )

테이블을 사전에 정의를 한 뒤에 그에 알맞은 형태의 데이터만 넣을 수 있다

 

Query ? ex 검색을 할 때 입력하는 검색어 ( 질의문 )

- 데이터베이스에 쿼리를 보내 원하는 데이터만을 가져올 수 있게 해줌

NoSQL( schema on read 라고도 불림 )

NoSQL - 관계형 데이터베이스와는 달리 테이블을 사용하지 않고 다른 형태로 데이터를 저장

= 비관계형 데이터베이스

ex  MongoDB 와 같은 문서 지향 데이터베이스 

 

 

• Key-Value 타입 : 데이터를 Key-Value의 쌍을 속성으로 하는 배열 형태로 저장합니다. 여기서 Key는 속성 이름을 뜻하고, Value는 속성에 연결된 데이터 값을 의미합니다. Redis, Dynamo 등이 대표적인 Key-Value 형식의 데이터베이스입니다.
• 문서형(Document) 데이터베이스 : 데이터를 테이블이 아닌 문서처럼 저장하는 데이터베이스를 의미합니다. JSON 유사 형식으로 데이터를 문서화하는 것이 보통입니다. 각각의 문서는 하나의 속성에 대한 데이터를 가지고 있고, 컬렉션이라고 하는 그룹으로 묶어서 관리합니다. 대표적인 문서형 데이터베이스에는 MongoDB가 있습니다.
• Wide-Column 데이터베이스 : 데이터베이스의 열(column)에 대한 데이터 관리를 집중하는 데이터베이스입니다. 각 열에는 key-value 형식으로 데이터가 저장되고, 컬럼 패밀리(column families)라고 하는 열의 집합체 단위로 데이터를 처리할 수 있습니다. 하나의 행에 많은 열을 포함할 수 있어서 높은 유연성을 자랑합니다. 데이터 처리에 필요한 열을 유연하게 선택할 수 있다는 점에서 규모가 큰 데이터 분석에 주로 사용되는 데이터베이스 형식입니다. 대표적인 wide-column 데이터베이스에는 Cassandra, HBase가 있습니다.
• 그래프(Graph) 데이터베이스 : 자료구조의 그래프와 비슷한 형식으로 데이터 간의 관계를 구성하는 데이터베이스입니다. 데이터는 노드(nodes)에 속성별(entities)로 저장됩니다. 각 노드간 관계는 선(edge)으로 연결됩니다. 대표적인 그래프 데이터베이스에는 Neo4J, InfiniteGraph가 있습니다.

ACID Atomicity, Consistency, Isolation, Durability

- 데이터베이스 내에서 일어나는 하나의 트랜잭션 (transaction) 의 안전성을 보장하기 위해 필요한 성질

트랜잭션이란 여러 개의 작업들을 하나의 실행 유닛으로 묶어준 것

트랜잭션 = 성공 혹은 실패 이 두 개의 결과만 존재

ex) 한 개의 작업이라도 실패하게 된다면 전부 실패 / 모든 작업이 성공적이면 트랜잭션 또한 성공적

즉, 트랜잭션은 미완료된 단계 없이 전부를 성공해야함

Atomicity 원자성
하나의 트랜잭션이 전부 성공하거나 전부 실패 ==> 부분적으로 실행이 되면 안되는 성질
특정 쿼리를 실행했는데 부분적으로 실패하는 부분이 있다면 전부 실패하게 되어 있습니다
(충돌 요인에 대해서 선택지를 제공하기도 합니다).

Consistency 일관성
하나의 트랜잭션 이전과 이후 데이터베이스 상태는 이전과 같이 유효해야 한다는 뜻
==> 데이터베이스의 제약이나 규칙에 의거한 데이터베이스이어야 한다는 뜻

각 고객은 이름이 있어야 하는 데이터베이스 제약이 있다고 가정

다음과 같은 트랜잭션들은 해당 성질을 위반
* 이름 없는 새로운 고객을 추가하는 쿼리 x
* 기존 고객의 이름을 삭제하는 쿼리 x

Isolation 고립성 ==> ?? 잘 이해가 안감
하나의 트랜잭션이 다른 트랜잭션과 독립되어야 한다는 뜻
==>실제로 동시에 여러 개의 트랜잭션들이 수행될 때에
      각 트랜젝션은 고립되어 있어 연속으로 실행된 것과 동일한 결과를 나타내야함

예를 들어 게좌에 만 원이 있다고 가정해 보겠습니다.

• 해당 계좌로부터 계좌 B 로 6천 원의 계좌 이체와 계좌 C 에 6천 원의 계좌 이체를 동시에 하는 경우 연속으로 계좌 B 에 먼저 보낸 뒤 계좌 C 에 보내는 결과와 동일해야 한다는 뜻입니다.

즉, 동시에 한다고 해서 각각 6천 원씩 계좌 이체 후에 마이너스 통장이 되는 것이 아닌 연속으로 하는 것과 동일한 결과를 나타내야 합니다. 이에 따라 각 트랜젝션은 다른 트랜젝션의 연산 내용을 알 수 없습니다
또한 동시에 실행될 때와 연속으로 실행될 때의 데이터베이스 상태가 동일해야함

모 커뮤니티의 자유게시판에 두 사람이 글을 거의 동시에 올린다고 하자. 그러면 두 트랜젝션에 충돌이 일어나서 User A의 제목이 저장되고 내용은 User B가 저장되는게 아니라 User A의 트랜잭션이 종료 되기 전까지 User B의 트랜젝션은 실행되지 않는 것을 말한다.
다수의 트랜잭션이 동시에 수행중인 상황에서 하나의 트랜잭션이 완료될 때까지는 현재 실행 중인 트랜잭션의 중간 수행결과를 다른 트랜잭션에서 보거나 참조 할 수 없다.

Durability 지속성
나의 트랜잭션이 성공적으로 수행되었다면 해당 트랜잭션에 대한 로그가 남고
런타임 오류나 시스템 오류가 발생해도 해당 기록은 영구적이어야함

은행에서 계좌이체를 성공적으로 한 뒤에 해당 은행 데이터베이스에 오류가 발생해
종료가 되어도 계좌이체 내역은 남아야 합니다.

마찬가지로 계좌이체를 로그로 기록하기 전에 시스템 오류 등에 의해 종료가 된다면 해당 이체 내역은 실패로 돌아가고 각 계좌들은 계좌이체 이전 상태들로 돌아가게 됩니다

 

 

 

SQL 종류

Data Definition Language (DDL)

DDL 은 데이터를 정의할 때 사용되는 언어를 가리킵니다. 예를 들어 테이블을 만들 때 사용하는 CREATE 이나 테이블을 제거할 때 사용되는 DROP 등 입니다. 데이터베이스의 테이블과 같은 오브젝트를 정의할 때 사용됩니다.

 

Data Manipulation Language (DML), 데이터 조작 언어

DML 은 데이터베이스에 데이터를 저장할 때 사용되는 언어를 가리킵니다. 예를 들어 INSERT 처럼 새로운 레코드를 추가할 때 사용되는 문법들이 포함됩니다. 따라서 데이터를 삭제하는 DELETE 라든가 변경하는 UPDATE 등이 여기에 포함됩니다.

 

Data Control Language (DCL), 데이터 제어 언어

DCL 은 데이터베이스에 대한 접근 권한과 관련된 문법입니다. 어느 유저가 데이터베이스에 접근할 수 있는지 권한을 설정하거나 없애는 역할이죠. 예를 들어 권한을 주는 GRANT 나 권한을 가져가는 REVOKE 등이 포함됩니다.

 

Data Query Language (DQL)

DQL 은 정해진 스키마 내에서 쿼리를 할 수 있는 언어입니다. 여기에 포함된 문법은 SELECT 정도가 있습니다. 물론

이렇게 따로 언어가 분류되지만 DQL 을 DML 의 일부분으로 말하곤 합니다. ( DML이 포괄적임 )

 

Transaction Control Language (TCL)

TCL 은 DML 을 거친 데이터 변경사항을 수정할 수 있습니다. 예를 들어 COMMIT 처럼 DML 이 작업한 내용을 데이터베이스에 커밋하거나 ROLLBACK 처럼 커밋했던 내용을 다시 롤백하는 문법들이 있습니다.(??)

• COMMIT 트렌젝션 수행
• ROLLBACK 트렌젝션 취소

 

관계형 데이터베이스

구조화된 데이터가 하나의 테이블로 표현이 되는데 이처럼 사전에 정의된 테이블을 relation이라고 함

즉, 테이블을 사용하는 데이터베이스를 뜻하는 의미

• 데이터 : 각 항목에 저장되는 값입니다.
• 테이블 (혹은 relation) : 사전에 정의된 행과 열로 구성되어 있는 체계화된 데이터입니다.
• 필드 (혹은 column) : 테이블의 열을 가리킵니다.
• 레코드 (혹은 tuple) : 테이블의 한 행의 저장된 정보입니다.
• 키 key : 테이블의 각 레코드를 구분할 수 있는 값. 각 레코드마다 고유값이어야 하며 기본키 (primary key) 와 외래키 (foreign key) 등이 있을 수 있습니다.

관계 종류

• 1:1 관계
• 1:N 관계 1 대 다

유저가 한 전화번호를 가질 수 있는 것이 아니라 여러 개의 전화번호를 가질 수 있습니다.

하지만 그 반대는 성립할 수가 없습니다. 즉, 한 전화번호는 한 명의 유저만 가질 수 있습니다.

 

• N:N 관계  

  

  

해당 관계의 경우에는 따로 '조인 테이블'을 만들어 관리

여러 개의 여행 상품이 있고 여러 명의 고객들이 있을 때 한 고객은 여러 개의 여행 상품을 사용할 수 있습니다.

마찬가지로 한 여행 상품은 여러 명의 고객을 가질 수 있죠. => 다대다 관계는 두 개의 일대다 관계라고 볼 수 있습니다

* 'customer_package' 테이블의 역할은 그저 customer_id 와 package_id 를 묶어주는 역할
이 테이블을 통해서 어떤 고객이 어떤 여행 상품들을 가지고 있는지
혹은 어떤 여행 상품이 어떤 고객들을 가지고 있는지 등을 확인
중요한 것은 이를 따로 테이블로 생성했을 때에도 동일하게 기본키가 있어야 하는 겁니다.

• self referencing 관계

테이블 자체 관계

• self referencing 관계 -테이블 내에서도 관계가 필요할 때
• 추천인이 누구인지 파악하기 위한 목적, 혹은 조직 내에 상하 관계 등을 표현하기 위한 것

한 유저당 한 명의 추천인을 입력할 수 있습니다.

즉, 한 유저당 하나의 추천인을 가질 수 있습니다. 하지만 추천인 입장에서는 여러 개의 유저를 가질 수 있습니다.

복수로 추천될 수 있다는 뜻입니다.

정리하자면 각 유저당 한 명만 추천할 수 있지만 추천 받은 사람은 여러 명에서부터 추천을 받게 되는 셈이죠.

* 1:N 관계와 비슷하다고 보실 수 있습니다.

스키마( schema )

CREATE_USER_TABLE = """
CREATE TABLE User(
    id INTEGER PRIMARY KEY,
    username VARCHAR,
    password VARCHAR
)
"""

CREATE_PRODUCT_TABLE = """
CREATE TABLE Product(
    id INTEGER PRIMARY KEY,
    product_name VARCHAR,
    product_price INTEGER
)
"""

CREATE_USER_PRODUCT_TABLE = """
CREATE TABLE User_Product(
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    product_id INTEGER,
    FOREIGN KEY(user_id) REFERENCES User(id),
    FOREIGN KEY(product_id) REFERENCES Product(id)
)
"""

SQL SELECT 실행 순서

SELECT CustomerId,  AVG(Total) FROM invoices WHERE  CustomerId >= 10 GROUP BY CustomerId HAVING SUM(Total) >= 30 ORDER BY 2

FROM WHERE GROUP BY HAVING ==> GROUP BY 한 이후 사용 가능 <-> WHERE의 차이 SELECT ORDER BY

FROM invoices: 먼저 invoices 테이블에 접근을 합니다. WHERE CustomerId >= 10: 'CustomerId' 필드가 10 이상인 레코드들을 조회합니다. GROUP BY CustomerId: 'CustomerId' 를 기준으로 그룹화합니다. HAVING SUM(Total) >= 30: 'Total' 필드의 값들의 합이 30 이상인 결과들만 필터합니다.(GROUP BY로 조회된 결과) SELECT CustomerId, AVG(Total): 조회된 결과에서 'CustomerId' 필드와 'Total' 필드의 평균값을 가져옵니다. ORDER BY 2: AVG(Total) 필드를 기준으로 오름차순 정렬을 합니다.

SQL CASE(= if 문과 같은 기능 )

SELECT 
	CASE
       WHEN CustomerId <= 25 THEN 'GROUP 1'
       WHEN CustomerId <= 50 THEN 'GROUP 2'
       ELSE 'GROUP 3'
	END
FROM customers

SUBQUERY

다른 쿼리문을 포함하는 것 = 실행되는 쿼리에 중첩으로 위치해 정보를 전달

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

-- IN, NOT IN
SELECT * FROM customers
WHERE CustomerId IN (
	SELECT 
    	CustomerId 
    FROM customers WHERE CustomerId < 10)

-- EXISTS - EXITS 안에 조건문이 일치하는게 있다면 EmployeeID를 출력하라
SELECT EmployeeId FROM employees e
WHERE EXISTS (
	SELECT 
    	1 
    FROM customers c WHERE c.SupportRepId = e.EmployeeId)
ORDER BY EmployeeId (default = ASC )

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