반응형
블로그 이미지
개발자로서 현장에서 일하면서 새로 접하는 기술들이나 알게된 정보 등을 정리하기 위한 블로그입니다. 운 좋게 미국에서 큰 회사들의 프로젝트에서 컬설턴트로 일하고 있어서 새로운 기술들을 접할 기회가 많이 있습니다. 미국의 IT 프로젝트에서 사용되는 툴들에 대해 많은 분들과 정보를 공유하고 싶습니다.
솔웅

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

카테고리

[Android] Optional SQLite Tutorial

2016. 10. 23. 04:43 | Posted by 솔웅


반응형

Optional SQLite Tutorial

Sunshine 앱에서는 날씨 정보를 저장하기 위해 SQLite database를 사용할 것입니다. SQLite에 대한 기본적인 이해가 있어야 합니다. 그리고 기본적인 명령어들도 익혀야 합니다. 이 글은 SQL 데이터베이스에 초보인 사람들 혹은 refresher를 위한 글입니다.


Introduction

SQLite은 관계형 데이터베이스 관리 시스템 입니다. SQL (Structured Query Language)을 사용합니다. C++ 라이브러리처럼 Android OS에 패키지로 있어서 각 앱들마다 private database를 가질 수 있습니다 SQL은 create, search 그리고 maintain database 등에 이용됩니다. 이 글에서는 SQL의 신택스와 사용법에 대해 다룹니다. 그리고 어떻게 작은 규모의 데이터베이스를 생성하고 관리하는지에 대해서도 다룹니다. 여기서 다루는 commands들은 Sunshine에서 사용될 SQL과 비슷합니다.

Get SQLite

    1. 이곳에서 SQLite을 다운받아 설치하면 컴퓨터에서 사용할 수 있습니다.
    http://sqlite.org/download.html
    SQLite Installation

    2. 설치 후 윈도우 커맨드 창을 열어서 데이터베이스를 저장할 폴더로 이동합니다. sunshine.db라는 데이터베이스를 아래와 같이 설치 합니다.
    sqlite3 sunshine.db

    3. 모든 commands들을 보려면 이렇게 타입 합니다.
    .help

    4. 그중에 모든 데이터베이스를 보여주는 명령어가 있습니다. 현재로서는 sunshine.db에 대한 정보가 보여질 겁니다.

    .databases


    앱에서 여러분은 여러개의 데이터베이스들을 만들 수 있습니다. Sunshine 엡에서는 하나의 데이터베이스만을 가질 겁니다. 이 데이터베이스 안에는 여러개의 테이블을 만들 수 있습니다.


Create A Database Table

  1. A table is a collection of rows and columns like a spreadsheet. Use the CREATE TABLE statement to create a new database table called “weather.” Each row will be one day’s worth of weather data. It should have 6 columns of data: ID, date, min temperature, max temperature, humidity, and pressure.

    In the CREATE TABLE statement, each column definition is separated by commas, where you provide the column name and datatype for that column. We also specify that the column should be non-null. We specify the _id column to be the primary key and it’s an integer.

    CREATE TABLE weather( _id INTEGER PRIMARY KEY, date TEXT NOT NULL, min REAL NOT NULL, max REAL NOT NULL, humidity REAL NOT NULL, pressure REAL NOT NULL);

    The list of possible SQLite data types is a useful resource, or you can see this tutorial.

    Note: SQLite keywords, such as CREATE TABLE or PRIMARY KEY, are capitalized for ease of readability to distinguish them from the table and column names that we’ve selected, but you can lowercase the keywords if you want.

    Note: This is not the full table you’ll be using in Sunshine, this is just a simpler version of the table.

  2. Use this command to list out all tables. Ensure that the weather table was created.

    .tables

  3. Use a SELECT statement to return out all rows in the weather table. The * is a symbol that means “all of the columns”. At this time, nothing will be returned because the table is created, but there is no data in the table yet.

    SELECT * FROM weather;

  4. At any point, you can find out the schema of how the tables were created in the database

    .schema

Insert rows

  1. Use an INSERT statement to insert a new row of data into the weather table. The following INSERT statement inserts a row into the weather table for June 25th, 2014, which had a low of 16 degrees, a high of 20 degrees, 0 humidity and 1029 pressure. The _id of this row is 1.

    INSERT INTO weather VALUES(1,'20140625',16,20,0,1029);

  2. Query for all rows in the weather table, and you should see the one row of data you just inserted.

    SELECT * FROM weather;

  3. To have the column name be printed out as well (for easier readability as to what value corresponds to which column), turn the header on. Then do the query again.

    .header on
    SELECT * FROM weather;

  4. Experiment by inserting another 3 rows of data into the weather table. INSERT INTO weather VALUES(2,'20140626',17,21,0,1031); INSERT INTO weather VALUES(3,'20140627',18,22,0,1055); INSERT INTO weather VALUES(4,'20140628',18,21,10,1070);

    Query for all rows to verify they were inserted properly.

    SELECT * FROM weather;

Query rows

  1. Practice doing queries where you provide a selection WHERE clause to narrow down the number of rows that are returned in the result. Always remember the semicolon at the end of a statement!

    For all possible SQLite operators, see this link.

    This query returns rows from the weather table where the date column exactly equals the 20140626.

    SELECT * FROM weather WHERE date == 20140626;

  2. This query returns rows from the weather table where the date column is between 20140625 and 20140628. However, all columns are not returned, we just return the 4 specified columns (_id, date, min, and max) of the rows that match the query.

    SELECT _id,date,min,max FROM weather WHERE date > 20140625 AND date < 20140628;

  3. This query returns rows where the minimum temperature is greater than or equal to 18. Based on those matching rows, we order them based on increasing (also known as ascending or “ASC” for short) max temperature. The first row of the result that is printed out to the command line will be the row (with min temperature >= 18) with max temperature that is lowest out of all rows, so that subsequent rows will have higher max temperature.

    SELECT * FROM weather WHERE min >= 18 ORDER BY max ASC;

Update rows

  1. You can also update existing rows in the database with an UPDATE statement. This statement updates the weather table by setting the minimum temperature to be 0 and maximum temperature to be 100 for rows where the date is greater than 20140626 but less than 20140627.

    UPDATE weather SET min = 0, max = 100 where date >= 20140626 AND date <= 20140627;

    When you print out the whole weather table again, you can see that 2 rows were changed.

    SELECT * FROM weather;

Delete rows

  1. Use a DELETE statement to delete rows from a database table that match the given selection clause. In this case, we delete any rows from the weather table where humidity is not equal to 0.

    DELETE FROM weather WHERE humidity != 0;

Add columns

  1. If you have released a version of your app to users, and then decide you need to change the database schema, such as adding columns, then you’ll need to upgrade your database. You can alter existing tables, by using the ALTER TABLE command.

    Note: In general, you shouldn’t alter a table to remove a column because you’re deleting data and other tables could depend on that column. Instead you can just null out all values in that column.

    This statement alters the weather table by adding another column to the table called description, which will always be non-null and contain text. It will also default to the value ‘Sunny’ if no value is provided. In reality, you would choose a more reasonable default, but this is just for example purposes.

    ALTER TABLE weather ADD COLUMN description TEXT NOT NULL DEFAULT 'Sunny';

    Verify that the new description column exists when you query all rows and all columns.

    SELECT * FROM weather;

Delete table

  1. Delete the weather table by using the DROP TABLE command. Verify there are no more tables in the database.

    DROP TABLE weather;
    .tables

These are just the basics. Feel free to play around with SQLite some more. See this link: http://www.sqlite.org/cli.html

When you’re done, enter .quit to exit, and you can move onto the quiz on the next page!

반응형