Backend boot camp/Session2
Relational DataBase
by orioncsy
2022. 10. 3.
SQL
개념
DB의 필요성
- 파일에 데이터를 저장하거나 인메모리 형태로 임시 저장하는 방법의 한계를 극복
- File I/O
- 엑셀 시트나 CSV 같은 파일 형태
- 데이터를 가져올 때마다 전체 파일 읽어 비효율적
- 여러 파일을 다루거나 파일 손상된 경우 데이터 부르는 작업이 복잡
- In-Memory
- JavaScript에서 프로그램 실행할 때만 존재
- 관계형 DB는 한 개의 CSV나 엑셀 시트를 테이플 형태로 저장하여 여러 데이터를 SQL을 활용해 불러올 수 있다.
SQL
- Structured Query Language (SQL)
- 주로 관계형 데이터 베이스에 사용하는 DB Language
Query
- 질의라는 뜻으로 데이터를 필터링하는 작업
- SQL을 사용할 수 있는 relation table과 달리 데이터 구조가 고정되지 않는 NoSQL 존재
- NoSQL의 대표적인 예는 MongoDB(문서 지향 DB)
SQL Basics
- DB 생성 및 제거
- CREATE DATABASE 데이터베이스 이름;
- DROP DATABASE 데이터베이스 이름;
- DB 사용
- TABLE 생성 및 제거
- CREATE TABLE person { name varchar(255), id int PRIMARY KEY AUTO_INREMENT, phone } DROP TABLE person TRUNCATE TABLE person //테이블 내 모든 데이터 제거
- TABLE 조회
- DISCRIBE person;
- SHOW TABLES; 모든 테이블 조회
- TABLE column 추가 및 제거
- ALTER TABLE 테이블_이름 ADD column_이름 타입 ALTER TABLE 테이블_이름 DROP COLUMN column_이름
- SELECT
- 데이터셋에 포함될 특성
- SELECT 실행 순서
FROM
WHERE
GROUP BY
HAVING
ORDER BY
- FROM
SELECT 특성1, 특성2
FROM 테이블_이름
SELECT * // 전부선택
FROM 테이블_이름
- WHERE
SELECT *
FROM 테이블_이름
WHERE 특성1<>"값"
WHERE 특성1 LIKE "값%" //값이 앞에 있는 경우
WHERE 특성1 LIKE "?값%" //두번째에 값이 있는 경우
WHERE 특성1 LIKE "[ac]%" //첫번째에 a or c인 경우
WHERE 특성1 LIKE "[!ac]%" //첫번째에 a, c가 아닌 경우
WHERE 특성1 LIKE "[a-c]%" //첫번째에 a 에서 c 사이인 경우
WHERE 특성1 IN ("값","값2") //특성1에 값이 있는 경우
WHERE 특성1 IS NULL// 특성1이 NULL일 경우
WHERE 특성1 IS NOT NULL //특성1이 값이 있는 경우
WHERE 특성1 BETWEEN 2 AND 4 //특성1의 범위
- ORDER BY
SELECT 특성1, 특성2
FROM 테이블_이름
WHERE 특성1<>"값"
ORDER BY 1 DESC //내림차순으로 특성1 기준 정렬
//DEFUALT는 오름차순
- LIMIT
SELECT *
FROM 테이블_이름
WHERE 특성1<>"값"
ORDER BY 특성1 DESC
LIMIT 10 // 특성1 기준 가장 큰 값부터 10개 출력
- DISTINCT
- 중복 없이 값을 받을 때는 SELECT DISTINCT
SELECT DISTINCT 특성1
FROM 테이블_이름
- INNER JOIN
- 둘 이상의 테이블에서 서로 공통된 부분 기준 연결
SELECT *
FROM 테이블1
(INNER) JOIN 테이블2 ON 테이블1.특성1 - 테이블2,특성2
- OUTER JOIN
- LEFT OUTER JOIN 왼쪽은 조건에 부합하지 않아도 모두 결합
- RIGHT OUTER JOIN 오른쪽은 조건에 부합하지 않아도 모두 결합
SELECT *
FROM 테이블1
LEFT (OUTER) JOIN 테이블2 ON 테이블1.특성1 - 테이블2,특성2
SELECT *
FROM 테이블1
RIGHT (OUTER) JOIN 테이블2 ON 테이블1.특성1 - 테이블2,특성2
- GROUP BY
- SELECT * FROM 테이블_이름 WHERE 특성 1<>"값" ORDER BY 특성1 DESC HAVING 그룹 조건
- INSERT INTO -VALUES, UPDATE - SET WHERE, DELETE FROM - WHERE
- INSERT INTO 테이블_이름(column1, colunm2) VALUES (value1, value2) UPDATE 테이블_이름 SET column1 = value1 WHERE 조건 DELETE FROM 테이블_이름 WHERE 조건
- COUNT, MIN, MAX, AVG, SUM
- SELECT COUNT(*) FROM 테이블_이름 WHERE 특성 1 <>"값" SELECT MIN(*) SELECT MAX(*) SELECT AVG(*) SELECT SUM(*)
ACID
Transaction
- 여러 개의 작업을 하나로 묶은 유닛
- 성공/실패를 결과로 가진다
Atomicity(원자성)
- 하나의 트랜잭션에 속한 작은 모두 성공하거나 모두 실패로 처리한다
- 트랜잭션 안의 어떤 하나의 작업이 실패하면 모두 실패로 처리
- 은행 송금에서 출금은 됐지만 입금이 안되면 실패로 처리
Consistency(일관성)
- DB의 상태가 일관되어야 한다
- 고객의 이름과 번호를 가지고 있는 DB 규칙이 있다면 하나라도 누락되면 규칙을 위반
Isolation(고립성)
- 모든 트랜잭션은 독립적이어야 한다
- 계좌를 송금할 때 서로 다른 계좌에 대한 송금은 서로 영향을 주어서는 안 된다
Durability(지속성)
- 하나의 트랜젝션이 성공하면 로그가 남아야 한다
- 시스템이 오류가 발생해도 기록은 영구적
SQL vs NoSQL
관계형 DB - SQL
- 테이블 구조, 데이터 타입 정의 및 저장
- 데이터 열에는 속성, 데이터 행은 데이터
- SQL을 사용하여 쿼리 진행 가능
- 스키마가 뚜렷
- MySQL, MariaDB, Oracle, SQLite, PostgresSQL
비관계형 DB - NoSQL
- 데이터를 읽어올 때 스키마에 따라 데이터 읽어옴
- 몽고DB, Casndra
NoSQL
- Key-Value : key-vale 형태로 데이터를 배열로 저장(Redis, Dynamo)
- Document DB : 문서 형태로 데이터 저장, JSON과 유사한 방식(MongoDB)
- Wide-Column DB : 각 열에 key-value 형태의 데이터 저장, 규모가 큰 DB에 사용(Cassandra, HBase)
- Graph DB : 그래프와 비슷한 형식으로 데이터 간 관계 구성, 노드에 속성을 두고 데이터 저장, 관계는 edge로 표현(Neo4J, InfiniteGraph)
SQL 기반 DB VS NoSQL 기반 DB
- Storage
- SQL을 이용해 테이블로 저장, 지정된 schema에 따라 저장
- NoSQL은 key-value, document, wide-column, graph 방식
- Schema
- SQL은 고정된 형식의 schema가 필요, 열은 속성, 행은 데이터
- NoSQL은 동적 스키마 형태 관리, 행 추가할 때 새로운 열을 추가 가능, 개별 속성에 대해 모든 열에 대한 데이터 입력 불필요
- Querying
- SQL에서 DB 정보를 요청할 때 질의를 테이블 형식에 맞춰 요청
- NoSQL에서는 데이터 그룹 자체를 조회
- Scalability
- SQL에서는 수직 확장(높은 메모리, CPU 사용), 하드웨어 비용 큼
- NoSQL은 수평 확장, 클라우드 기반이나 저렴한 하드에 서버 추가 구축 가능
SQL & NoSQL 선택
- NoSQL은 확장성, 속도에 유리, SQL에서 좋은 성능인 경우도 존재
- SQL case
- ACID 성질 준수
- transaction을 처리할 때 안전성을 위한 규칙을 규정 가능, 무결성 보호
- 전자 상거래 등 금융 서비스에 적합
- 데이터가 구조적이고 일관되는 경우
- NoSQL case
- 데이터 구조가 없는 대용량 데이터를 저장하는 경우
- 클라우드를 활용하는 경우
- 데이터 구조가 자주 업데이트되는 경우
Schema & Query Design
관계형 데이터베이스(Relational DataBase) 용어
- data - 각 항목에 저장되는 값
- table, relation - 데이터가 행으로 축적되는 테이블
- column, field - 데이터 속성
- record, tuple - 행에 저장된 데이터
- key - 레코드를 구분하는 값
- primary key - 기본키는 테이블의 고유한 값으로 구분
- foreign key - 외래 키는 다른 테이블로부터 값을 참조하는 키
관계 종류
1:1 관계
- 하나의 레코드가 다른 테이블의 한 개와 연결되는 경우
- 하나의 테이블에 직접 저장하면 되기 때문에 자주 사용하지 않는다
1:N 관계
- 하나의 레코드가 다른 테이블의 여러 레코드와 연결되는 경우
- 이런 경우에는 N 쪽에 존재하는 테이블에 값을 저장
N:N 관계
- join table 생성
- 1:M를 형성하는 관계를 양쪽에 두어 하나의 테이블 생성
Self referencing 관계
More About
DB Normalization
Data redundancy
- 중복 데이터는 일관된 자료 처리가 어렵고 공간 낭비와 비효율성을 감소하기 위해 제거한다
Data Integrity
- 데이터 무결성은 데이터 정확도와 일관성을 유지
Anomaly
- update anomaly
- insertion anomaly
- 특정 column에 대한 값이 없는 데이터를 삽입할 때
- delete anomaly
- 데이터의 특정 부분을 삭제할 때 다른 데이터도 같이 삭제되는 경우
SQL 종류
- Data Definition Language(DDL)
- CREATE, DROP 같은 테이블 같은 오브젝트를 정의할 때 사용
- Data Manipulation Language(DML)
- INSERT, DELETE, UPDATE처럼 데이터 저장할 때 사용
- Data Control Language(DCL)
- GRANT, REVOKE 등 데이터 접근 권한에 사용
- Data Query Language(DQL)
- Transaction Control Language(TCL)
- COMMIT, ROLLBACK처럼 DML을 거친 데이터 수정
SQL Advanced
CASE
SELECT CASE
WHEN personId <=10 THEN 'G1'
WHEN personId <=20 THEN 'G2'
ELSE 'G3'
END
FROM person
SUBQUERY
- 쿼리문 안에 다른 쿼리를 작성 가능
- IN, NOT IN
- SELECT * FROM person WHERE pid IN (SELECT pid FROM person WHERE pid <5) //NOT IN을 사용할 경우 5 이상의 pid를 가진 데이터 출력
- EXISTS, NOT EXISTS
- SELECT pid FROM person p WHERE EXISTS( SELECT 1 // TRUE를 의미 FROM teacher t WHERE p.pid=t.tid ) ORDER BY pid
- FROM
- SELECT * FROM ( SELECT pid FROM person WHERE pid>20 )
