개발경력이 대부분 모바일 환경이다보니, 오랜 경력에도 DB를 만져본적이 별로 없다. 안드로이드 앱만 개발해도 sqlite는 쓸텐데, 좀 심각하네. 대학때 배웠던 기억을 되살려보며 아주 간단한 사항만 기록해보려한다. 일단은 다루기쉬운 sqlite3만 고려한다. sqlite를 공부하기 좋은 곳은 tutorialspoint.
어 머야, 어설픈 튜토리얼 사이트보다 sqlite 개쩌는 튜토리얼 사이트가 있었네. https://www.sqlitetutorial.net/ 그냥 비교대상이 없다. 최고라서 살짝 추가함 ㅋ
DB 생성
sqlite3를 받아 설치했다면, 다음과 같이 생성할 수 있다.
$sqlite3 DatabaseName.db따로 다운로드받지 않고, python에 기본 내장된 sqlite3모듈을 쓴다면, connect()시에 DB가 없으면 생성하게 된다.
import sqlite3
conn = sqlite3.connect("employee.db")스키마(Schema)
관계형 DB 공부할 때 기억을 되살려보면 이게 전부다. 스키마란 DB가 어떻게 생겼는지 정의하는 것인데, 테이블들이 어떤 필드로 이루어지고 각 테이블들의 관계나 제약사항등을 명시하는 것이다. 관계형 DB는 입출력을 고려해서 이 스키마를 잘짜면 땡. 크고 복잡한 DB를 다룰 일은 거의 없을 것이기 때문에, 테이블을 생성하는 SQL정도만 알면 될거 같다.
테이블 생성과 삭제
개발자 기준으로 테이블을 생성하는 SQL문이 바로 스키마를 정의하는 곳이다. dbdiagram.io 란 사이트에 가보면, 스키마 다이어그램을 만들어볼 수 있다. 간단한 게시판을 예로 다음과 같이 그려보았다.
해당 사이트에서 mysql이나 postgresql 등의 SQL문을 만들어주는 기능도 있으니 참고. 위 다이어그램을 기반으로 sqlite에 맞게 SQL문을 만들어보자.
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
nationality TEXT
);
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
description TEXT NOT NULL,
user_id INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE TABLE IF NOT EXISTS comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL,
user_id INTEGER NOT NULL,
post_id INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (post_id) REFERENCES posts (id)
);하나의 SQL문 뒤에 세미콜론이 붙는걸 주의하자. CREATE문에서 “IF NOT EXISTS”를 써서 테이블이 없는경우만 만들도록 했다. AUTOINCREMENT는 인덱스가 자동으로 하나씩 증가하며 생성되는걸 말한다. 나중에 INSERT시에 따로 값을 입력할 필요가 없다. 테이블간의 관계는 외래키를 정의하는 FOREIGN KEY 를 사용했다.
NOT NULL, PRIMARY KEY등은 Constraints라고 한다.
테이블의 삭제는 쉽다. DROP을 이용하면 된다.
DROP TABLE users;CRUD : create, read, update, delete
데이터베이스는 CRUD라고 불리는 4가지 기능, create, read, update, delete를 구현하고 처리해야한다. SQL Query문으로는 각각 INSERT, SELECT, UPDATE, DELETE 가 해당 명령문이다.
INSERT
데이터를 추가하는 INSERT문의 문법은 다음과 같다.
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
앞에서 생성한 users 테이블에 적용해보면 다음과 같이 쓸 수 있다.
INSERT INTO users (name, age, nationality) VALUES ("John", 29, "Korea");SELECT
데이터를 읽어오는데에는 SELECT문이 쓰인다. 전체 데이터에서 필요한 정보들만 가져오는 방법은 때로는 매우 복잡한 논리구조를 가지며 어려울 수 있다. 어려운건 일단 미뤄두고, 가장 기본적인 것만 다루겠다.
SELECT는 기본적으로 테이블에서 원하는 column들만 ‘골라’오는 명령어이다. 그 문법은 다음과 같다.
SELECT column1, column2, columnN FROM table_name;
앞에서 생성했던 테이블에 적용하면 다음과 같이 사용된다.
SELECT title, description FROM posts;모든 컬럼을 가져오는 경우, ‘*’를 쓸 수 있다.
SELECT * FROM posts;이것만으로는 데이터를 특정하기엔 부족하다. SELECT로 column을 필터링 했다면, 뒤에 WHERE 문을 사용하여 row에 조건을 걸어 데이터를 필터링해준다.
SELECT column1, column2, columnN FROM table_name WHERE [condition]
앞에서 만든 테이블에 적용해 예를들면 다음과 같이 쓸 수 있다.
SELECT name, age FROM users WHERE age > 30;WHERE문에 논리연산만 오는건 아니다. LIKE연산자는 정규표현식 매칭과 유사하다. percent(%), under scorer( _ ) 두개의 문자가 사용된다. ‘_’ 는 하나의 문자, ‘%’는 몇개의 문자가 와도 상관없을 때(0개 포함) 사용된다. 사용예는 다음과 같다.
-- 'J'로 시작하는 모든 이름 가져오기
SELECT * FROM users WHERE name LIKE 'J%'
-- 20대만 가져오기. 200은 안된다. 실제론 없겠지만.
SELECT * FROM users WHERE age LIKE '2_'UPDATE
이전에 INSERT되어있는 내용을 변경하는 경우 사용하는 명령문이다. 형식은 다음과 같다.
UPDATE table_name
SET column1 = value1, column2 = value2…., columnN = valueN
WHERE [condition];
앞에서 생성한 테이블로 예를들면 다음과 같다.
UPDATE posts SET description=" useless post " WHERE id=3;DELETE
삭제도 UPDATE와 비슷하다.
DELETE FROM table_name WHERE [condition];
DELETE FROM comments WHERE id=2;JOIN
여러개의 테이블에서 데이터를 가져오는 경우, SELECT문에서 테이블을 나열하여 각각 가져올 수도 있지만, JOIN연산을 사용할 수도 있다. JOIN이란 테이블간 집합연산을 통해 새로운 테이블을 만든다고 생각하면된다. INNER JOIN, OUTER JOIN, NATURAL JOIN등등이 있는데, 참고할 수 있는 링크만 달아둔다.
https://www.w3schools.com/sql/sql_join.asp : W3 Scools tutorial
https://futurists.tistory.com/17 : sql join을 구글링하면 제일처음 나오는 링크인데, 꽤 상세히 설명해놨다.