[DB] 기초1

 

 

• 학습 내용

이번 시간에 다룰 내용은 데이터베이스이다. 원래 데이터를 저장하는 방법으로 파일로 데이터를 저장하거나 인메모리 형태로 데이터를 임시 저장하는 방법을 사용했다. 인메모리 형태의 경우 프로그램이 실행 될 때 데이터가 저장되어 있다. 그러나 문제는 프로그램이 종료되는 순간 해당 프로그램이 사용하던 데이터도 같이 사라진다. 예를 들어 프로그래밍언어를 사용하여 변수에 데이터를 저장했을 경우일 때 저장한 데이터가 프로그램의 실행에만 의존하는 것에 불구하다는 것이다.

 파일에 데이터를 저장하는 방식은 파일을 읽는 방식으로 작동하는 형태이다. 그러나 이 경우에도 한계가 존재한다. 데이터가 필요할 때마다 전체 파일을 매번 읽어야 하기 때문에 파일이 커지면 작업이 비효율적이다. 또 파일이 손상되거나 여러 개의 파일들을 동시에 다뤄야 하거나 하는 등 복잡하고 데이터량이 많아지면 데이터를 불러들이는 작업이 버겁다. 이를 대안으로 제시할 수 있는 것이 바로 데이터베이스이다. 관계형 데이터베이스를 사용하면 CSV 파일이나 엑셀 시트를 한 개의 테이블로 저장할 수 있기 때문에 한번에 여러 개의 테이블을 소유하여 SQL을 활용해 데이터를 불러오기 좋다.

 

• SQL

SQL(Structured Query Language)란 데이터베이스의 언어로 주로 관계형 데이터베이스에서 사용한다. MySQL, Oracle, SQLite, PostgreSQL이 이에 해당하는 관계형 데이터베이스이다. 즉, 데이터베이스 용 프로그래밍 언어인데 데이터베이스에 쿼리(쿼리(query)란 질의문이란 뜻으로 검색할 때 입력하는 검색어와 같다. 기존의 존재하는 데이터를 검색어로 필터링하기 때문에 저장되어 있는 데이터를 필터하기 위한 질의문으로 볼 수 있다)를 보내 원하는 데이터를 가져오거나 삽입할 수 있다. 또 데이터가 구조화된 테이블을 사용하는 데이터베이스에서도 활용할 수 있다. 이와 달리 데이터의 구조가 고정되어 있지 않은 데이터베이스를 NoSQL이라고 한다. 관계형 데이터베이스와는 달리 테이블을 사용하지 않고 데이터를 다른 형태로 저장한다. MongoDB와 같이 문서 지향 데이터베이스가 이러하다.

 

• 데이터베이스 관련 명령어

1) 데이터베이스 생성

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

 

2) 데이터베이스 사용

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

3) 테이블 생성

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

id의 경우 필드 타입으론 숫자이며 Primary key이면서 자동으로 증가하도록 설정한다. name은 문자열이고 최대 255개의 문자라는 뜻이다. email 역시 name과 동일하다.

4) 테이블 정보 확인

DESCREBE user;

 

• 간단한 SQL 명령어

1) SELECT

SELECT는 데이터셋에 포함될 특성을 특정한다.

SELECT 'hello world' #일반문자열
SELECT 2 #숫자
SELECT 15 + 3 #간단한연산

 

2) FROM

테이블과 관련된 작업을 할 경우 반드시 입력해야 한다. FROM 뒤에는 결과를 도출해낼 데이터베이스 테이블을 명시한다.

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

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

SELECT * #*은 와일드카드로 전부 선택할 때에 사용
FROM 테이블_이름 #테이블의 모든 특성을 선택

 

3) WHERE

필터 역할을 하는 쿼리문

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

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

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 > "특정 값"
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 <= "특정 값" #특정 값보다 크거나 작은 데이터를 필터할 때에는 '<', '>' 비교하는 값을 포함하는 '이상', '이하' 값은 '<=', '>='을 사용함

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 LIKE "%특정 문자열%" #문자열에서 특정 값과 비슷한 값들을 필터할 때에는 'LIKE'와 '\%' 혹은 '\*' 사용

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

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

SELECT *
FROM 테이블_이름
WHERE 특성_1 IS NOT NULL #값이 없는 경우를 제외할 때에 'NOT'을 추가해 사용

 

4) ORDER BY

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

SELECT *
FROM 테이블_이름
ORDER BY 특성_1 #오름차순으로 정렬

SELECT *
FROM 테이블_이름
ORDER BY 특성_1 DESC #내림차순으로 정렬

 

5) LIMIT

결과를 출력할 데이터의 개수를 정함. 선택적으로 사용가능하다. 쿼리문에서 가장 마지막에 추가한다.

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

 

6) DISTINCT

유니크한 값을 받고 싶을 때 사용한다.

SELECT DISTINCT 특성_1
FROM 테이블_이름 #특성_1을 기준으로 유니크한 값을 선택

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

 

7) INNER JOIN

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

SELECT *
FROM 테이블_1
JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B #둘 이상의 테이블을 서로 공통도니 부분을 기준으로 연결

 

8) OUTER JOIN

Outer JOIN은 다양한 선택지가 있다.

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

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

 

9) INSERT INTO

하나의 완전한 행을 삽입하거나 하나의 부분적인 행을 삽입 또는 Query 결과를 삽입할 때 사용한다.

INSERT INTO 테이블_1
SET 열1=값1,열2=값2
WHERE 조건

 

10) UPDATE

데이터베이스 테이블에서 데이터를 갱신할 때 사용한다. 셀 단위로 데이터를 갱신할 수 있고 WHERE을 생략할 경우 테이블의 모든 행이 갱신된다.

UPDATE 테이블_1
SET 열1=값1, 열2=값2
WHERE 조건

 

11) DELETE

데이터베이스 테이블에서 행을 삭제할 때 사용한다. 삭제는 행 단위로 수행되고 열을 지정하여 해당 열만 삭제할 수는 없다.

DELETE FROM 테이블_1
WHERE 조건

 

12) GROUP BY

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

SELECT * FROM customers
GROUP BY State; #customers 테이블의 모든 레코드를 State에 따라 그룹화

 

13) HAVING

HAVING을 사용하면 GROUP BY로 조회된 결과를 필터링할 수 있다.

SELECT CustomerId, AVG(Total)
FROM Involces
GROUP BY CustomerId
HAVING AVG(Total) > 6.00 #Involces 테이블을 CustomerId로 그룹화하고 그 평균이 6을 초과한 결과를 조회

HAVING은 그룹화한 결과에 대한 필터이고, WHERE는 저장된 레코드를 필터링한다. 그래서 그룹화 전에 데이터를 필터해야 한다면 WHERE를 쓰는게 맞다.

 

14) COUNT()

COUNT 함수는 레코드의 개수를 셀 때 사용한다.

SELECT State, COUNT(*) FROM customers
GROUP BY State; #각 State에 해당하는 레코드의 개수를 확인

 

15) SUM()

SUM 함수는 레코드의 합을 반환한다.

SELECT InvolceId, SUM(UnitPrice)
FROM Involce_Items
GROUP BY InvolceId;

 

16) AVG()

AVG 함수는 레코드의 평균값을 구하는 함수이다.

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

 

17) MAX(), MIN()

MAX,MIN 함수는 각각 레코드의 최대값과 최솟값을 반환한다.

SELECT CustomerId, MAX(Total)
FROM Involces
GROUP BY CustomerId

 

• ACID

트랜잭션이란 여러 개의 작업을 하나로 묶은 실행 유닛이다. 각 트랜잭션은 하나의 특정 작업으로 시작을 해 묶여 있는 모든 작업을 다 완료해야 정상적으로 종료한다. 만약 하나의 트랜잭션에 속해있는 여러 작업 중에서 단 하나의 작업이라도 실패하면 이 트랜잭션에 속한 모든 작업을 실패한 것으로 판단한다. 즉, 작업이 하나라도 실패하면 트랜잭션도 실패이고 모든 작업이 성공하면 트랜잭션 또한 성공이다. 그래서 트랜잭션에는 미완료된 작업 없이 모든 작업이 성공 또는 실패라는 두 개의 결과만을 가진다. ACID는 데이터베이스 내에서 일어나는 하나의 트랜잭션의 안전성을 보장하기 위해 필요한 성질이다. ACID는 Atomicity(원자성), Consistency(일관성), Isolation(고립성), Durablilty(지속성)의 약자이다.

1) Atomicity(원자성)

원자성은 하나의 트랜잭션에 속해있는 모든 작업이 전부 성공하거나 전부 실패해서 결과를 예측할 수 있어야 한다는 특성이다. 만약 이 원자성이 지켜지지 않으면 하나의 단위로 묶여있는 여러 작업이 부분적으로 실행되면 업데이트가 일어났지만 누가 업데이트에 성공하고 실패했는지 알 수 없기 때문에 데이터에 큰 혼동을 줄 수 있다.

2) Consistency(일관성)

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

3) Isolation(격리성, 고립성)

격리성은 모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다는 뜻이다. 동시에 여러 개의 트랜잭션들이 수행된다면 각 트랜잭션이 고립되어 있어야 연속으로 실행된 것에 동일한 결과를 낸다.

4) Durabillity(지속성)

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