Elliot Kim

python3, Android development student.

SQLite 사용하기

16 분 소요

안드로이드의 Data storage option

안드로이드 내 저장소를 이용하기 위한 방법에는 세 가지가 있다.

  1. Files 사진 파일이나 음악 파일 등 Raw data를 저장하기 위한 방법이다. 폴더와 같은 개념

  2. Shared Preference “Key” 와 “Value”로 이루어진 쌍의 값으로서 app에서 user의 preference를 저장할 때 쓴다. 예를 들면 지도 앱에서 거리 단위를 mile로 할 것인지 km으로 할 것인지 지정해 두면 이후 앱이 실행될 때에도 자동으로 이전 저장 preference를 참조할 것이다.

  3. Database 아주 많은 량의 데이터를 저장할 때. Related and structured data. 즉 RDB라고 부른다. 여러 개의 논리적으로 나눠진 테이블로 데이타가 저장된다.

SQLite3

프리웨어이며 안드로이드에 기본 탑재되어있는 DB인 SQLite3 사용법을 알아보도록 하겠다. 다운로드 링크는 .. 링크 여기서 windows 32 x86 을 다운받아서, 해당 파일을 C:/sqlite폴더 밑에 풀자. sqlite3.exe파일이 생성될 것이다. 터미널을 통해 명령어를 입력해서 이용할 수 있다.

기본 명령어

C:\sqlite>sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

sqlite3를 통해 db커맨드창에 들어갈 수 있다. sqlite>로 나타나는 커맨드창에서는 반드시 ;(세미콜론)으로 명령어를 끝내야 실행됨을 명심하자.

먼저 db파일을 만들어 주자. open명령어를 이용해 존재하지 않는 db 파일을 만들어 보면 자동으로 생성한다.

sqlite> .open test.db
sqlite>

c:/sqlite폴더에 test.db파일이 생겼을 것이다.

CRUD

db 내 table에서 사용할 수 있는 명령어는 크게 4개의 카테고리로 나뉜다.

  1. CREATE : 데이터 생성. 대표 명령어 INSERT INTO

  2. READ : 데이터 읽기. SELECT *

  3. UPDATE : 기존의 데이터 수정하기

  4. DELETE : 기존 데이터 지우기

아래에서 자세히 알아보도록 하자.

SQLite의 자료형

SQLite의 자료형에는 아래와 같이 5개가있다. 아래는 Storage Classes에 속하는 것들이다.

  1. NULL - 값이 NULL
  2. INTEGER - 값이 정수
  3. REAL - 값이 실수
  4. TEXT - 값이 String
  5. BLOB - 값이 정확히 우리가 인풋한 값으로 저장(IMAGE나 이진수로 저장 가능)

이제 테이블을 한번 만들어 보자. 다른 언어에서 쉽게 볼 수 있는 boolean이 여기에는 없는데, 쉽게 integer로 대체한다.

테이블 생성 및 제거

db에는 여러 테이블이 있을 수 있다. 테이블 생성 시에는 테이블 명, 컬럼 헤드명과 컬럼에 들어갈 데이터 자료형을 선언해야 한다. 또한 무엇보다 중요한 unique 컬럼, _id를 포함시키자. CREATE TABLE 명령어로 생성할 수 있다.

아까 생성한 test.db에 접속

C:\sqlite>sqlite3 test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>

_id, name, price 컬럼을 가진 test_table 테이블 생성 .tables명령어를 통해 확인가능하다.

sqlite> CREATE TABLE test_table(_id INTEGER, name TEXT, price INTEGER);
sqlite> .tables
test_table
sqlite>

.schema는 어떤 명령어를 통해 table이 생성됐는지 추적할 수 있다.

sqlite> .schema test_table
CREATE TABLE test_table(_id INTEGER, name TEXT, price INTEGER);

PRAGMA TABLE_INFO를 통해 table값을 직접 볼 수 있다.

sqlite> PRAGMA TABLE_INFO(test_table);
0|_id|INTEGER|0||0
1|name|TEXT|0||0
2|price|INTEGER|0||0

테이블 삭제는 DROP TABLE이용

sqlite> DROP TABLE test_table;
sqlite> .tables
sqlite>                    //삭제됨

data 생성 (INSERT)

만들어진 테이블에 데이터를 직접 넣어보도록 하자. 아래와 같은 pets테이블이 있다.

sqlite> PRAGMA TABLE_INFO(pets);
0|_id|INTEGER|0||0
1|name|TEXT|0||0
2|breed|TEXT|0||0
3|gender|INTEGER|0||0
4|weight|INTEGER|0||0

INSERT INTO 명령어를 사용하자.

sqlite> INSERT INTO pets(_id, name, breed, gender, weight)
   ...> VALUES(1, "Tommy", "Pomeranian", 1, 4);
sqlite> INSERT INTO pets(_id, name, breed, gender, weight)
   ...> VALUES(2, "Garfield", "Tabby", 1, 8);

.header 를 on / .mode를 column으로 바꾸고 SELECT명령어를 통해 결과를 출력해 보자

sqlite> .mode column
sqlite> .header on
sqlite> SELECT * FROM pets;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
1           Tommy       Pomeranian  1           4
2           Garfield    Tabby       1           8

데이터 정합성을 위한 Column Constraints

지금까지 테이블을 만들고 데이타를 넣는 법을 배웠다. 하지만 여기엔 작은(?)문제가 있는데… 바로 _id가 제 역할을 못하고 있다는 점. _id컬럼은 분명 unique해야하는데, 새롭게 INSERT INTO 를 이용해 data를 넣으면 중복된 id값이 생겨버릴 것이다. 이를 비롯해 각종 발생할 수 있는 문제점을 사전에 방지할 수 있는 아래 네 가지 커맨드에 대해 알아보자. 아래 키워드는 컬럼 생성 시(CREATE) 덧붙일 수 있는 Constraint 조건이다.

  • PRIMARY KEY : 해당 컬럼은 유니크한 값들로만 구성되어야 한다는 뜻.

  • AUTO INCREMENT : Data INSERT시 아무 값을 넣지 않으면 자동으로 값을 증가시켜서 넣어줌. 주로 PRIMARY KEY 와 함께 쓰임.

  • NOT NULL : 해당 컬럼은 값이 반드시 들어가야 함.

  • DEFAULT : Data INSERT시 아무 값을 넣지 않으면 자동으로 defalut value가 들어감. 주로 NOT NULL과 함꼐 쓰임.

예시를 통해 알아보자. 다음과 같은 grocery_store db를 만들고 fruits 테이블을 만들자. 이 때 id 컬럼에는 PRIMARY KEY 와 AUTOINCREMENT를, name에는 NOT NULL을, price에는 NOT NULL/DEFAULT 0 커맨드를 추가로 입력했다. 단순히 space로만 구분해서 입력되는 점에 주목.

C:\sqlite>sqlite3 grocery_store.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE fuites(
   ...> _id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT NOT NULL,
   ...> price INTEGER NOT NULL DEFAULT 0
   ...> );

첫 data는 제대로 넣고, 두번쨰 data의 id를 뺴고 넣어보자.

sqlite> INSERT INTO fruits(_id, name, price)
   ...> VALUES(1, "apple", 100);
sqlite> INSERT INTO fruits(name, price) //id값 누락 
   ...> VALUES("banana", 200); 
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM fruits;
_id         name        price
----------  ----------  ----------
1           apple       100
2           banana      200
sqlite>

자동으로 2가 입력된 것을 볼 수 있다.

id에 원래 있던 값을 넣으려 하면 아래와 같은 unique constraint fail 에러가 난다.

sqlite> INSERT INTO fruits(_id, name, price)
   ...> VALUES(2, "strewberry", 300);
Error: UNIQUE constraint failed: fruits._id
sqlite>

NOT NULL을 지정해놓은 name 컬럼을 비우고 입력하려면 어떻게 될까? NOT NULL constraint failed 에러가 발생한다. 어떤 컬럼인지까지 나오니까 확인 가능.

sqlite> INSERT INTO fruits(price)
   ...> VALUES(300);
Error: NOT NULL constraint failed: fruits.name

NOT NULL 이지만 DEFAULT value를 지정해 놨던 price를 비워보면 어떨까? 초기설정값 0과 함꼐 제대로 들어간다.

sqlite> INSERT INTO fruits(name)
   ...> VALUES("strewberry");
sqlite> SELECT * FROM fruits;
_id         name        price
----------  ----------  ----------
1           apple       100
2           banana      200
3           strewberry  0

WHERE / ORDER BY

지금까지는 SELECT * 로 전체 값을 항상 봤지만, 데이터베이스의 양이 방대할 때, 원하는 조건의 값들만 선택하고 싶을 때가 있다. 이때 사용하는 것이 WHERE 조건이다.

아래와 같은 명령어를 이용해 pets table을 만들어 보자.

  CREATE TABLE pets (
       _id INTEGER PRIMARY KEY AUTOINCREMENT,
       name TEXT NOT NULL,
       breed TEXT,
       gender INTEGER NOT NULL,
       weight INTEGER NOT NULL DEFAULT 0);

  INSERT INTO pets (name, breed, gender, weight) VALUES ( "Tommy", "Pomeranian", 1, 4);
  INSERT INTO pets (name, breed, gender, weight) VALUES ("Garfield", "Tabby", 1, 14);
  INSERT INTO pets (name, breed, gender, weight) VALUES ("Binx", "Bombay", 1, 6);
  INSERT INTO pets (name, breed, gender, weight)  VALUES ( "Lady", "Cocker Spaniel", 2, 14);
  INSERT INTO pets (name, breed, gender, weight) VALUES ("Duke", "Unknown", 1, 70);
  INSERT INTO pets (name, breed, gender, weight) VALUES ("Cat", "Tabby", 0, 7);
  INSERT INTO pets (name, breed, gender, weight) VALUES ("Baxter", "Border Terrier", 1, 8);
  INSERT INTO pets (name, gender, weight) VALUES ("Arlene", 2, 5);

sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM pets;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
1           Tommy       Pomeranian  1           4
2           Garfield    Tabby       1           14
3           Binx        Bombay      1           6
4           Lady        Cocker Spa  2           14
5           Duke        Unknown     1           70
6           Cat         Tabby       0           7
7           Baxter      Border Ter  1           8
8           Arlene                  2           5

WHERE뒤에 각종 조건을 붙여서 원하는 값만 출력할 수 있다.

sqlite> SELECT * FROM pets WHERE _id == 2;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
2           Garfield    Tabby       1           14


sqlite> SELECT * FROM pets WHERE weight >= 10;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
2           Garfield    Tabby       1           14
4           Lady        Cocker Spa  2           14
5           Duke        Unknown     1           70


sqlite> SELECT name, breed, gender FROM pets WHERE gender == 1;
name        breed       gender
----------  ----------  ----------
Tommy       Pomeranian  1
Garfield    Tabby       1
Binx        Bombay      1
Duke        Unknown     1
Baxter      Border Ter  1

ORDER BY 커맨드를 이용해서는 데이터를 새롭게 정렬할 수 있다. (ASC는 오름차순, DESC는 내림차순)

sqlite> SELECT * FROM pets ORDER BY name ASC;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
8           Arlene                  2           5
7           Baxter      Border Ter  1           8
3           Binx        Bombay      1           6
6           Cat         Tabby       0           7
5           Duke        Unknown     1           70
2           Garfield    Tabby       1           14
4           Lady        Cocker Spa  2           14
1           Tommy       Pomeranian  1           4


sqlite> SELECT * FROM pets ORDER BY name DESC;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
1           Tommy       Pomeranian  1           4
4           Lady        Cocker Spa  2           14
2           Garfield    Tabby       1           14
5           Duke        Unknown     1           70
6           Cat         Tabby       0           7
3           Binx        Bombay      1           6
7           Baxter      Border Ter  1           8
8           Arlene                  2           5

data UPDATE

테이블 내 data를 수정하기 위해서는 UPDATE명령어를 사용하자.

형식은 다음과 같다. UPDATE <테이블명> SET <컬럼명 = 원하는값> WHERE <조건을 통한="" ROW선택="">;

sqlite> SELECT * FROM pets ORDER BY name DESC;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
1           Tommy       Pomeranian  1           4
4           Lady        Cocker Spa  2           14
2           Garfield    Tabby       1           14
5           Duke        Unknown     1           70
6           Cat         Tabby       0           7
3           Binx        Bombay      1           6
7           Baxter      Border Ter  1           8
8           Arlene                  2           5

sqlite> UPDATE pets SET weight = 20 WHERE _id == 1;

sqlite> SELECT * FROM pets ORDER BY name DESC;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
1           Tommy       Pomeranian  1           20
4           Lady        Cocker Spa  2           14
2           Garfield    Tabby       1           14
5           Duke        Unknown     1           70
6           Cat         Tabby       0           7
3           Binx        Bombay      1           6
7           Baxter      Border Ter  1           8
8           Arlene                  2           5

주의!! WHERE조건을 통해 특정 row를 선택하지 않으면…전체 column 값이 바뀐다.

sqlite> UPDATE pets SET gender = 1;
sqlite> SELECT * FROM pets ORDER BY name DESC;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
1           Tommy       Pomeranian  1           20
4           Lady        Cocker Spa  1           14
2           Garfield    Tabby       1           14
5           Duke        Unknown     1           70
6           Cat         Tabby       1           7
3           Binx        Bombay      1           6
7           Baxter      Border Ter  1           8
8           Arlene                  1           5

DELETE

DELETE FROM <테이블명> WHERE <조건> 명령을 통해 table내 특정 row의 데이터를 지울 수 있다.

매우 주의!! WHERE조건을 통해 row를 특정하지 않을 시 전체 테이블이 날라간다… ……

sqlite> DELETE FROM pets WHERE _id == 4;
sqlite> SELECT * FROM pets;
_id         name        breed       gender      weight
----------  ----------  ----------  ----------  ----------
1           Tommy       Pomeranian  1           20
2           Garfield    Tabby       1           14
3           Binx        Bombay      1           6
5           Duke        Unknown     1           70
6           Cat         Tabby       1           7
7           Baxter      Border Ter  1           8
8           Arlene                  1           5

댓글남기기

참고

[boost course] 핀치 줌, 아웃 구현하기

1 분 소요

두 손가락을 이용해 이미지를 확대 축소하는 기능을 라이브러리 없이 구현하는 방법이다. ScaleGestureDetector를 이용한다 공식홈링크 이미지 뷰 생성 확대할 이미지뷰를 xml파일에 생성한다. <ImageView android:id="@+id/imageView"...

[boost course] SQLiteOpenHelper 알아보기

4 분 소요

SQLiteDataBase를 쉽게 다룰 수 있게 하는 SQLiteOpenHelper 클래스에 대해 알아보자. 공식 홈페이지를 해석하며 분석해 보았다. 공홈 SQLiteOpenhelper Database 생성과 버전 컨트롤을 쉽게 할 수 있게 도와주는 helper class이다....

[boost course] Glide 라이브러리 사용법

2 분 소요

이번 4번째 PJT인 네트워킹을 하면서, 이미지 로딩 라이브러리인 Glide를 사용할 기회가 있어 정리해 둔다. Glide란? 글라이드는 안드로이드의 빠르고 효과적인 오픈소스 미디어 매니지먼트 및 이미지 로딩 프레임워크로서, 미디어 디코딩/메모리 & 디스크 캐싱, 리소스...

[boost course] DrawerLayout

2 분 소요

Drawer Layout 이번 프로젝트에서는 DrawerLayout을 사용했다. 햄버거 버튼을 누르면 왼쪽에서 튀어나오는 그것…오늘은 공홈에 대해 번역해 정리해 두도록 하겠다. 공홈DrawerLayout DrawerLayout은 어느 한 쪽 (왼/오) 켠에서 “drawer” ...