SQL 入門

SQLite 入門

Kotlin を使った Android アプリの開発コースでは、次の内容を理解していることを前提としています。

  • データベース（概要の理解）
  • SQL データベース（詳細の理解）
  • データベースを操作するために使用する SQL 言語

このページは復習に使用するためのクイック リファレンスです。

SQL データベース

SQL データベースでは、行と列からなる表にデータが保存されます。

  • 行と列の交差部分はフィールドと呼ばれます。
  • フィールドには、データ、他のフィールドへの参照、他の表への参照が含まれます。
  • 各行にはエンティティが 1 つずつあります。エンティティは一意の ID で識別されます。この ID は通常、主キーとして使用されます。
  • 各列は表ごとに一意の名前で識別されます。

SQLite

SQLite は、次の特性を持つ SQL データベース エンジンを実装しています。

  • 自己完結（他のコンポーネントを必要としない）
  • サーバーレス（サーバー バックエンドを必要としない）
  • 設定不要（アプリ用に設定する必要がない）
  • トランザクション（SQLite 内の 1 つのトランザクション内の変更が完全に実行されるか、まったく実行されない）

SQLite は世界で最も広く導入されているデータベース エンジンです。SQLite のソースコードはパブリック ドメインにあります。SQLite データベースについて詳しくは、SQLite ウェブサイトをご覧ください。

表の例

  • DATABASE_NAME という名前のデータベース
  • WORD_LIST_TABLE という名前の表
  • _id、word、definition の列

alpha と beta（alpha には 2 つの定義があります）という単語を挿入すると、表は次のようになります。

DATABASE_NAME

WORD_LIST_TABLE

_id

単語

定義

1

"アルファ"

"1 番目の文字"

2

"ベータ"

"2 番目の文字"

3

"アルファ"

"助詞"

特定の行の内容を確認するには、_id を使用するか、クエリ（制約を指定して表から行を選択する）を作成して行を取得します。

トランザクション

トランザクションとは、単一の論理単位として実行されるオペレーションのシーケンスです。トランザクションとみなされるには、論理作業単位が 4 つのプロパティ、つまりアトミック性、一貫性、分離性、耐久性（ACID）を保持する必要があります。

  • アトミック性。 トランザクションのデータ変更がすべて実行されるか、変更が実行されません。プログラムのクラッシュ、オペレーティング システムのクラッシュ、停電によるディスクへの書き込み作業の中断などにおいても、アトミック性は変化しません。
  • 一貫性。 トランザクションが完了した後、すべてのデータは一貫した状態である必要があります。
  • 分離性。 同時トランザクション間で行われた変更は、互いから分離している必要があります。トランザクションは、別の同時トランザクションがデータを変更する前の状態でデータを認識するか、2 番目のトランザクションが完了した後にデータを認識します。トランザクションは中間状態を認識しません。
  • 耐久性。トランザクションが完了した後、その影響はシステム内に永久に残ります。システム障害が発生した場合でも、変更は保持されます。

トランザクションの例:

  • 普通預金口座から当座預金口座へ資金を送金する。
  • 辞書に用語と定義を入力する。
  • 変更のリストを master ブランチに送信する。

トランザクションについて詳しくは、SQLite のアトミック コミット をご覧ください。

クエリ言語

データベースを操作するには、SQL クエリ言語を使用します。クエリは非常に複雑になる場合がありますが、基本的な操作は次の 4 つです。

  • 行の挿入
  • 行の削除
  • 行内の値の更新
  • 特定の条件を満たす行の取得

Android では、データアクセス オブジェクト（DAO）により、データベースの挿入、削除、更新を簡単に行うことができます。クエリ言語について詳しくは、SQLite が理解できる SQL をご覧ください。

クエリの構造

SQL クエリは高度に構造化されています。サンプルクエリ:

  • SELECT word, definition FROM WORD_LIST_TABLE WHERE word="alpha"

サンプルクエリの汎用バージョン:

  • SELECT columns FROM table WHERE column="value"

サンプルクエリの各部:

  • SELECT columns: 返す列を選択します。すべての列を返すには、* を使用します。
  • FROM table: 結果を取得する表を指定します。
  • WHERE: 満たす必要がある条件よりも優先される省略可能なキーワード（例: column="value"）。一般的な演算子は、=、LIKE、<、> です。複数の条件を接続するには、AND または OR を使用します。

クエリの他の部分:

  • ORDER BY: 結果を列で並べ替えるためのオプションのキーフレーズ。昇順の場合は ASC を指定し、降順の場合は DESC を指定します。順序が指定されていない場合は、デフォルトの順序で並べ替えられます（並べ替えられない場合があります）。
  • LIMIT: 結果数の制限を指定するためのキーワード。

サンプルのクエリと結果

次のクエリは、事前に定義された表を使用します。

SELECT * FROM WORD_LIST_TABLE

WORD_LIST_TABLE 表のすべての行が取得される。
SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2 id が 2 より大きいすべてのアイテムの word 列と definition 列が選択される。
戻り値

[["alpha", "particle"]]

SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"

定義に部分文字列 art を含む単語 alpha の id が返されます。

[["3"]]

SELECT definition FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1

すべての定義が選択されます。並べ替え順序を逆にして、並べ替えを行った後にリストの最初の行が取得されます。並べ替えは、指定した列（word）で実行されます。返されない列で並べ替えることができます。

[["second letter"]]

SELECT * FROM WORD_LIST_TABLE LIMIT 2,1

位置 2 から始まるアイテムが 1 つ返されます。位置のカウントは 0 ではなく 1 から始まります。戻り値 [["2", "beta", "second letter"]]

データベースの作成とクエリの練習については、この SQL Fiddle ウェブサイトをご覧ください。

Android SQLite のクエリ

クエリを未加工のクエリまたはパラメータとして、Android システムの SQLite データベースに送信できます。

rawQuery(String sql, String[] selectionArgs) メソッドは、指定された SQL を実行します。このメソッドは結果セットの Cursor を返します。次の表は、上記の最初の 2 つのサンプルクエリが未加工のクエリとしてどのように見えるかを示しています。

1

String query = "SELECT * FROM WORD_LIST_TABLE";
rawQuery(query, null);
2

query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";
String[] selectionArgs = new String[]{"2"}
rawQuery(query, selectionArgs) ;

query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) メソッドは、指定された表に対してクエリを実行します。このメソッドは、結果セットに対して Cursor を返します。引数の入力方法を次のクエリに示します。

SELECT * FROM WORD_LIST_TABLE
WHERE word="alpha"
ORDER BY word ASC LIMIT 2,1;

クエリから次の結果が返されます。

[["alpha", "particle"]]

使用できる引数の例:

String table = "WORD_LIST_TABLE"
String[] columns = new String[]{"*"};
String selection = "word = ?"
String[] selectionArgs = new String[]{"alpha"};
String groupBy = null;
String having = null;
String orderBy = "word ASC"
String limit = "2,1"

query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

注: 実際のコードでは、null 値の変数は作成されません。このメソッドのバージョンごとのパラメータを確認するには、Android SQLiteDatabase のドキュメントをご覧ください。

カーソル

カーソルは、構造化データの行へのポインタです。カーソルは表の行へのポインタと考えることができます。

クエリは、クエリ結果の最初の要素を指す Cursor オブジェクトを返します。Cursor クラスは、クエリ結果からカーソルを移動するためのメソッドと、結果の各行の列からデータを取得するメソッドを提供します。

メソッドから Cursor オブジェクトが返されたら、結果の反復処理、データの抽出、データに対する目的の操作を行ってから、カーソルを閉じてメモリを解放します。

詳細