30 Jan 2019 • on database, sql

Database와 SQL

Database

살아오면서 데이터베이스라는 말을 익히 들어 보셨을 것이다.

말 그대로 데이터들을 많이 가지고 있는 저장소의 개념인데,

사실 데이터베이스 말고도 저장할 수 있는 매체들은 많다(ex text, csv, json, …)

일단 데이터베이스를 왜 써야하는지부터 알아보도록 합시다.

데이터베이스 왜 써야함?

텍스트파일은 파일을 지우고 수정하는게 쉽지 않다.

(한 줄을 지우고 싶으면 삭제할 줄을 빼고 다시 작성해야함.)

json파일은 key가 있기 때문에 사정이 좀 낫지만 그래도 여의치 않다.

따라서, 웹의 정보를 받기 위해서는 데이터베이스를 사용하는게 좋다.

데이터베이스

체계화된 데이터의 모음.

데이터의 operation : 4가지 create read update delete(CRUD)

1. RDBMS(Relational database management system)

데이터베이스를 관리하는 프로그램이다.

A relational database is a database that organizes information into one or more tables.

왜 관계라는 말이 붙는가 : 데이터베이스에 근거한 이론이 Relational algebra이기 때문임.

어렵게 생각하지 말고 그냥 관계형 데이터베이스 하면 엑셀을 떠올리면 된다.

다만 엑셀처럼 시각화되어있지는 않다 -> 머릿속에서 엑셀을 추상화하는게 중요함.

2. 다양한 데이터베이스들

MongoDB : 종종 자바스크립트와 같이 다루는데 얘는 RDB가 아니다.

SQL이 아닌 자기만의 독립적인 문법을 사용하는데, json파일 다루듯이 하는것임.
postgreSQL : 큰 데이터를 저장하기 위한 오픈소스기반의 데이터베이스.
Redis : 메모리에만 상주하는 아주 조그만 데이터베이스

보통 데이터베이스는 메모리 바깥에 있는 storage에 소속되는데, 이러한 경우에는 메모리에 비해서 저장속도가 매우 느리다.

이러한 메모리의 장점을 이용해서 인메모리 데이터베이스를 만들었는데, 요즘 메모리 성능이 워낙 좋다보니 충분히 실현가능하다고 한다.
Elasticsearch : 검색을 빠르게 하기 위해서 구성된 데이터베이스, 메모리에 올려놓고 서칭을 하기 때문에 압도적으로 빠르다.
SQLite : 매우 가벼운 데이터베이스, 서버가 아니라 응용프로그램에 넣어서 사용하는 오픈소스.

3. SQLite

데이터베이스를 하나의 파일처럼(가벼움) 쓸 수 있음.

이름에서 알 수 있듯이 SQL(structured query language)를 기반으로 동작하는데,

SQL은 데이터베이스라는 structure를 코드를 통해서 다룰 수 있다.

이것 이외에도 대부분의 데이터베이스도 전부 SQL을 기반으로 다루기때문에 SQL만 잘 다루면 된다.

4. SQL 기본 용어

scheme : 각 column은 데이터타입을 지정해야한다!

이러한 column들의 데이터타입들을 청사진, scheme라고 한다.
Table

엑셀같은 경우는 데이터베이스(엑셀 파일) + 테이블(sheet)로 구성되어있으며,

하나의 엑셀에는 여러개의 sheet가 들어갈 수 있는 것 처럼,

하나의 DB에는 table이 여러개 들어갈 수 있다.
Data record

column과 row를 의미한다.
Primary key

특정 데이터를 수정하기 위해서는 데이터를 특정할 수 있어야한다.

각 row를 특정할 수 있는 identifier가 존재하며, 이것을 어렵게 PK(primary key)로 부른다.

SQLite 다뤄보기

추천강의들)

스탠포드 강의링크 : 입문자용, Relational algebra를 약간 다룸.

버클리 강의링크 : 상급자용, 실제로 db를 어떻게 설계하는지(자료구조들이 어떻게 들어가는지 언급함)

[기본적인 sql 명령어들]

csv같은 파일을 데이터베이스에 어떻게 저장하는가?

.mode csv /* csv파일을 작동하겠다고 선언 */
.import hellodb.csv examples /* hellodb.csv를 examples라는 table에 넣겠다. */

.을 통해서 sqlite와 소통할 수 있다.

현재 존재하는 테이블들을 전부 다 보기
```
.tables
```
테이블에서 데이터를 가져오기
```
SELECT * FROM examples;
```
- *는 전부다 가져오겠다는 의미임.
- 보시다시피 매우 직관적이다.
- 대문자로 시작하는 쿼리문장은 항상 ;로 끊어줘야한다.
```
sqlite> SELECT first_name FROM examples;
"길동"
sqlite> SELECT last_name, first_name FROM examples;
"홍","길동"
```
이런식으로 * 말고도 column을 지정할 수 있다.
```
sqlite> SELECT id, name, age FROM classmates limit 1;  
sqlite> SELECT id, name, age FROM classmates limit 1 offset 1;  
```
추가적인 옵션도 지정가능하다.

offset을 쓰는 이유는 게시판같은걸 구현할 때 유용하다

예를 들어서 이 사이트는 1페이지당 20개의 글을 담는데,

offset을 이용해서 한꺼번에 모든 데이터를 뽑지 않고 부분 데이터만 뽑아서 페이징을 할 수 있다.

ex) page = 2 -> variable routing : 2 획득 -> SELECT * FROM table LIMIT 20 OFFSET 2*20;

이런식으로 구현할 수 있다.
```
.mode column /* column을 보여주겠다 */
.headers on /* 출력값 위에 헤더를 달아주겠다 */
```
다음 명령어를 통해서 더 예쁘게 뽑을 수 있다.
```
sqlite> SELECT last_name, first_name FROM examples;
last_name   first_name
----------  ----------
홍         길동    
```

그런데 이건 우리가 파이썬코드를 작동하듯이 sql console이랑 대화하는것일 뿐이고,

결과가 저장되지는 않는다. 따라서 데이터베이스를 따로 생성해줘야한다.

Database 만들기

$ sqlite3 tutorial.sqlite3 /* tutorial.sqlite3 이라는 파일에 */
   
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
   
sqlite> .databases /* 데이터베이스를 만들겠다 */
   
seq  name             file                                                     
---  ---------------  --------------------------------------------------------
0    main             /home/ubuntu/workspace/tutorial.sqlite3     

[기타 명령어들]

.schema table /* 컬럼들의 정보를 반환함. */
DROP TABLE classmates; /* 테이블을 삭제함 */
.read 파일이름.sql /* 콘솔에서 작업하는건 불편하니 sql파일에서 작업하고 불러오겠다 */

[SQL 데이터 처리하기] : statement를 통해서 처리가능하다.

A statement is a string of characters that the database recognizes as a valid command.

CREATE TABLE creates a new table.
INSERT INTO adds a new row to a table.
SELECT queries data from a table.
ALTER TABLE changes an existing table.
UPDATE edits a row in a table.
DELETE FROM deletes rows from a table.

CREATE TABLE : Table 만들기
```
CREATE TABLE 테이블이름(
    column1 datatype1 constraint1
    column2 datatype2 constraint2
);
```
테이블이름을 만들때는 복수형으로 만드는게 관례임.

위에서 보았듯이 column에 반드시 데이터타입을 지정해야합니다.

가능한 데이터타입으로는 INTEGER, TEXT(문자열), REAL, NUMERIC 등이 있는데요.

그 외에도 constraint라는 제한을 따로 줄 수 있습니다.

INSERT INTO : 새로운 row 추가

INSERT INTO 테이블이름 (COLUMN1, COLUMN2, ...) 
VALUES (VALUE1, VALUE2, ...);

컬럼을 생략하고 값을 넣을 수도 있는데, 이럴때는 모든 값을 정의해줘야한다.

sqlite> INSERT INTO classmates VALUES ('홍길동',30,'SEOUL');
Error: table classmates has 4 columns but 3 values were supplied

Alter TABLE : 새로운 column 추가

ALTER TABLE celebs /* 테이블에 변화를 좀 주겠습니다? */
ADD COLUMN twitter_handle TEXT; /* 새 컬럼을 추가 */

Update : 이미 가지고 있는 row를 조작함.
```
UPDATE 테이블
SET 변수 = 지정할 값
WHERE 조건문
```
조건문은 ID == 4와 같이 프로그래밍적으로 해도 되지만,

SQLite는 ID = 4 // ID is 4 // ID IS 4 와 같은 문법이 모두 허용된다.
Delete FROM : 해당하는 row를 삭제한다.
```
DELETE FROM 테이블
WHERE 조건문
```
항상 where문을 쓸때에는 id와 같은 primary key를 쓰는게 효율적이다.

왜냐면 검색속도가 더 빠르기 때문이다.
Constraints

어떤 column의 데이터타입을 선언한 이후에 그 컬럼을 어떻게 쓸건지 명시해주는 것을 의미함.

ex) 특정 제한조건에 해당하지 않는 데이터의 추가를 허용하지 않음.
- PRIMARY KEY
  
  행의 유니크한 식별자를 지정해준다.
  
  이미 PRIMARY KEY가 존재하는 데이터를 넣으려고 할 시에 constraint violation 에러를 반환한다.
  
  아무런 값을 넣지 않아도 자동으로 카운팅해서 올려준다.
- UNIQUE
  
  PRIMARY KEY와 비슷하게 서로 다른 값으로만 구성되어야하지만, UNIQUE constraint는 여러개가 될 수 있다.
- NOT NULL
  
  무조건 값을 가져야한다. 만약 값을 넣지 않고 INSERT 연산을 취한다면 추가되지 않음.
  
  비어있는 데이터를 만들 일은 거의 없기 때문에 이것을 항상 정의해주는게 좋다!
- DEFAULT
  
  만약 새로운 row를 추가할 때 값을 선언하지 않았다면 디폴트값으로 자동으로 매겨지게된다.
- AUTOINCREMENT
  
  자동으로 증가한다!, 정수 자료형에만 적용가능
  
  물론 primary key를 선언하면 자동으로 올라가긴하는데, AUTOINCREMENT는 조금 특별하다.
```
sqlite> INSERT INTO classmates (name,age,address) VALUES ('손지명',30,'서울');
sqlite> SELECT id, name, address FROM classmates WHERE address='서울';
     
id          name        address   
----------  ----------  ----------
2           이하동   서울    
3           손지명   서울    
     
sqlite> DELETE FROM classmates where id = 3;
sqlite> INSERT INTO classmates (name,age,address) VALUES ('손지명',30,'서울');
sqlite> SELECT id, name, address FROM classmates WHERE address='서울';
     
id          name        address   
----------  ----------  ----------
2           이하동   서울    
4           손지명   서울    
```
  ID가 3짜리를 생성하고 삭제했는데, 다시 ID를 선언하지않고 데이터를 생성하면 ID가 3이 아니라 4가 된다. 이는 몇가지 이점이 있는데,
  1. 과거에 삭제된 데이터의 HISTORY를 알 수 있다.
  2. 지워진 ID와 현재 데이터의 ID가 겹칠 수 있다(타겟팅이 중복되는 것을 막음.)

SQL의 함수 다뤄보기(엑셀의 함수랑 비슷함)

COUNTS: 해당하는 데이터의 개수를 세서 반환한다.

sqlite> SELECT COUNT(*) FROM users WHERE age >= 30;                                                              
COUNT(*)
414

AVG, SUM, MIN, MAX와 같은 함수도 가능하다.

sqlite> SELECT *,MAX(age) from users;                                                                            
id,first_name,last_name,age,country,phone,balance,MAX(age)
1,"정호","유",40,"전라북도",016-7280-2855,370,40
   
sqlite> SELECT *,MAX(balance) from users;                                                                        
id,first_name,last_name,age,country,phone,balance,MAX(balance)
627,"선영","김",37,"전라북도",02-1610-2940,990000,990000

함수에 해당하는 다른 컬럼들도 수집할 수 있다.

물론 count()같은 함수는 해당하는 조건의 가장 마지막까지 카운트하므로 제일 나중에 측정한 컬럼을 반환하지만,

max()같은 함수는 타이가 생긴다면 가장 낮은 인덱스의 컬럼을 반환한다.

정규표현식(LIKE)

%	2%	2로 시작
	%2	2로 끝남
	%2%	2가 들어가는 값
_	_2%	아무값이나 들어가고 두번째가 2로 시작
	1__	1로 시작하고 3자리인값
	`2_%_%`	2로 시작하고 적어도 3자리인 값

ORDER

오름차순이나 내림차순으로 정렬해서 출력한다.
```
ORDER BY column1, column2 [ASC|DESC]
```
보통 LIMIT와 결합해서 상위 몇개만 뽑는 경우가 많음.