TIL 12주차 관계형 데이터베이스

🌈 SQL

하나의 언어인 Structured Query Language (SQL)은 데이터베이스 언어로, 주로 관계형 데이터베이스에서 사용한다. 예를 들어 MySQL, Oracle, SQLite, PostgreSQL 등 다양한 데이터베이스에서 SQL 구문을 사용할 수 있다.

SQL이란 데이터베이스 용 프로그래밍 언어다. 데이터베이스에 쿼리를 보내 원하는 데이터를 가져오거나 삽입할 수 있다. 그리고 이름에서 유추할 수 있듯이, SQL은 (relation 이라고도 불리는) 데이터가 구조화된(structured) 테이블을 사용하는 데이터베이스에서 활용할 수 있다.

🔑 SQL 명령어

데이터베이스 생성

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

데이터베이스 사용

데이터베이스를 이용해 테이블을 만들거나 수정하거나 삭제하는 등의 작업을 하려면, 먼저 데이터베이스를 사용하겠다는 명령을 전달해야 한다.

USE 데이터베이스_이름;

데이터베이스 확인

SHOW DATABASES;

데이터베이스 삭제

DROP DATABASE 데이터베이스

---

테이블 생성

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

테이블 정보 확인

DESC 테이블명;

테이블 전체 삭제

DROP TABLE 테이블명

테이블 row 삽입

INSERT INTO topic (title,description,created,author,profile) VALUES('postgreSQL','postgreSQL server is.....',NOW(),'yeye','developer');

테이블 row 수정

UPDATE 테이블명 SET column1='oracle is...', column2='Oracle' WHERE id=2;

테이블 row 삭제

DELETE FROM 테이블명 WHERE id=5;

---

SELECT

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

SELECT 'value'

FROM

결과를 도출해낼 데이터베이스 테이블 명시
테이블과 관련된 작업을 할 경우 반드시 입력

SELECT 특성_1
FROM 테이블_이름

WHERE

필터 역할

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 = "특정 값"

특정 값을 제외한 값

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 <> "특정 값" 

문자열에서 특정 값과 비슷한 값들을 필터할 때에는 'LIKE'와 '\%' 혹은 '*' 를 사용

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 LIKE "%특정 문자열%"

리스트의 값들과 일치하는 데이터를 필터할 때에는 'IN' 을 사용

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 IN ("특정값_1", "특정값_2")

ORDER BY

돌려받는 데이터 결과를 어떤 기준으로 정렬하여 출력할지 결정

SELECT *
FROM 테이블_이름
ORDER BY 특성_1 DESC

SELECT *
FROM 테이블_이름
ORDER BY 특성_1, 특성_2

LIMIT

MYSQL에서만 사용
결과로 출력할 데이터의 갯수를 정할 수 있다. LIMIT은 선택적으로 사용할 수 있다. 그리고 쿼리문에서 사용할 때에는 가장 마지막에 추가한다.

SELECT *
FROM 테이블_이름
LIMIT 200

DISTINCT

중복값 제거하고 유니크한 값을 받고 싶을 때에는 SELECT DISTINCT 를 사용

SELECT DISTINCT 특성_1, 특성_2
FROM 테이블_이름

COUNT

중복값 포함되어 count

SELECT COUNT(*)
FROM 테이블
WHERE 특성1 = 조건;

NULL

SELECT *
FROM 테이블_이름
WHERE 특성_1 IS NULL

SELECT *
FROM 테이블_이름
WHERE 특성_1 IS NOT NULL

INNER JOIN

INNER JOIN 이나 JOIN 으로 실행할 수 있다.

SELECT *
FROM 테이블_1
JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B

OUTER JOIN

'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

INSERT INTO

데이터 입력

INSERT INTO 테이블명(필드1, 필드2) VALUES ('값1', '값2' );

다음과 같이 여러 row를 한번에 추가해 줄 수 있다.

INSERT INTO 테이블명(필드1, 필드2) VALUES ('값1', '값2' ), ('값3','값4'), ('값5','값6');

UPDATE SET

데이터 수정

UPDATE 테이블명 SET 필드='값', 필드2='값', 필드3='값' WHERE 필드 LIKE '조건'

ALTER TABLE

테이블의 컬럼을 추가 삭제 수정

ALTER TABLE 테이블명 ADD 컬럼 컬럼형식

ALTER TABLE Persons
ADD Birthday DATE

DELETE FROM

데이터 조건 삭제

DELETE FROM 테이블명 WHERE 조건문

TRUNCATE TABLE

테이블 안에 들어있는 모든 레코드 삭제, 부분 삭제 불가

TRUNCATE TABLE 테이블명

비교 연산자

🔑 SELECT 실행 순서

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY

쿼리문 예시

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

SQL PREACTICE

---

🌈 ACID

데이터베이스 트랜젝션이 발생할 때, 그 안정성을 보장할 수 있는 성질

🔑 트랜잭션

트랜잭션이란 여러 개의 작업을 하나로 묶은 실행 유닛이다. 각 트랜잭션은 하나의 특정 작업으로 시작을 해 묶여 있는 모든 작업을 다 완료해야 정상적으로 종료한다. 만약 하나의 트랜잭션에 속해있는 여러 작업 중에서 단 하나의 작업이라도 실패하면, 이 트랜잭션에 속한 모든 작업을 실패한 것으로 판단한다.
다시 말해 작업이 하나라도 실패를 하게 되면 트랜잭션도 실패이고, 모든 작업이 성공적이면 트랜잭션 또한 성공이다. 성공 또는 실패 라는 두 개의 결과만 존재하는 트랜잭션은, 미완료된 작업없이 모든 작업을 성공해야 한다.

트렌잭션의 성공 여부에 대해 COMMIT과 ROLLBACK으로 선언해 주어야 한다.

🔑 COMMIT & ROLLBACK

• COMMIT
  하나의 트랜잭션이 성공적으로 끝나서 데이터베이스가 일관성있는 상태에 있음을 의미한다

• ROLLBACK
  트랜잭션의 원자성이 깨질 때, 즉 하나의 트랜잭션 처리가 비정상적으로 종료 되었을 때의 상태를 뜻한다.
  Rollback 이 이뤄진다면 트랜잭션을 다시 실행하거나 부분적으로 변경된 결과를 취소할 수 있다.

🔑 ACID

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

Atomicity(원자성)

트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며, 모두가 완벽히 수행되지 않고 어느하나라도 오류가 발생하면 트랜잭션 전부가 취소되어야 한다.

A 계좌에서 출금하는 일에 성공했지만, B 계좌에 입금하는 작업에 실패한다면 계좌 A에서 출금하는 작업을 포함하여 모든 작업이 실패로 돌아가야 한다는 것이 Atomicity(원자성)이다.

Consistency(일관성)

데이터베이스의 상태가 일관되어야 한다는 성질이다. 하나의 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같이 유효해야 한다. 다시 말해, 트랜잭션이 일어난 이후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다.

Isolation(격리성, 고립성)

모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다.
실제로 동시에 여러 개의 트랜잭션들이 수행될 때, 각 트랜젝션은 고립(격리)되어 있어 연속으로 실행된 것과 동일한 결과를 나타낸다.

Durability(지속성)

Durability(지속성)는 하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야 한다. 만약 런타임 오류나 시스템 오류가 발생하더라도, 해당 기록은 영구적이어야 한다.

예를 들어 은행에서 계좌이체를 성공적으로 실행한 뒤에, 해당 은행 데이터베이스에 오류가 발생해 종료되더라도 계좌이체 내역은 기록으로 남아야 한다.

---

🌈 SQL vs NoSQL

관계형 데이터베이스는 SQL을 기반으로 하고, 비관계형 데이터베이스는 NoSQL로 데이터를 다룬다.

🔑 SQL VS NoSQL

• 데이터 저장(Storage)

NoSQL은 key-value, document, wide-column, graph 등의 방식으로 데이터를 저장한다.

관계형 데이터베이스는 SQL을 이용해서 데이터를 테이블에 저장한다. 미리 작성된 스키마를 기반으로 정해진 형식에 맞게 데이터를 저장해야 한다.

• 스키마(Schema)

SQL을 사용하려면, 고정된 형식의 스키마가 필요하다. 다시 말해, 처리하려는 데이터 속성별로 열(column)에 대한 정보를 미리 정해두어야 한다. 스키마는 나중에 변경할 수 있지만, 이 경우 데이터베이스 전체를 수정하거나 오프라인(down-time)으로 전환할 필요가 있다.

NoSQL은 관계형 데이터베이스보다 동적으로 스키마의 형태를 관리할 수 있다. 행을 추가할 때 즉시 새로운 열을 추가할 수 있고, 개별 속성에 대해서 모든 열에 대한 데이터를 반드시 입력하지 않아도 된다.

• 쿼리(Querying)

쿼리는 데이터베이스에 대해서 정보를 요청하는 질의문이다. 관계형 데이터베이스는 테이블의 형식과 테이블간의 관계에 맞춰 데이터를 요청해야 한다. 그래서 정보를 요청할 때, SQL과 같이 구조화된 쿼리 언어를 사용한다.

비관계형 데이터베이스의 쿼리는 데이터 그룹 자체를 조회하는 것에 초점을 두고 있다. 그래서 구조화 되지 않은 쿼리 언어로도 데이터 요청이 가능하다. UnQL(UnStructured Query Language)이라고도 한다.

• 확장성(Scalability)

일반적으로 SQL 기반의 관계형 데이터베이스는 수직적으로 확장한다. 높은 메모리, CPU를 사용하는 확장이라고도 한다. 데이터베이스가 구축된 하드웨어의 성능을 많이 이용하기 때문에 비용이 많이 든다. 여러 서버에 걸쳐서 데이터베이스의 관계를 정의할 수 있지만, 매우 복잡하고 시간이 많이 소모된다.

NoSQL로 구성된 데이터베이스는 수평적으로 확장한다. 보다 값싼 서버 증설, 또는 클라우드 서비스 이용하는 확장이라고도 한다. NoSQL 데이터베이스를 위한 서버를 추가적으로 구축하면, 많은 트래픽을 보다 편리하게 처리할 수 있다. 그리고 저렴한 범용 하드웨어나 클라우드 기반의 인스턴스에 NoSQL 데이터베이스를 호스팅할 수 있어서, 수직적 확장보다 상대적으로 비용이 저렴하다.

---

참고
SQL 명령어 정리