SQLiteでデータベースの基礎学習

関連ページ 参考URL ゼロベースでブログのシステムを構築する際、データベース機能はほぼ必須の機能になります。
このページではまずSQLite単体の機能を最低限レベルで確認し、データベースへの理解を深めます。
次のページ でTomcatサーブレットとの連携について書きます。
SQLとはデータベースを扱うための言語のことで、データベースに接続するソフトをSQLクライアントと呼びます。
フリーのSQLクライアントで最も有名なのはMySQL。しかし小規模ブログを運営する程度なら機能過多でちょっと覚えることが多いです。
なので今回はさくっと気軽に使える、SQLiteというソフトを使います。
SQLiteのホームページ から、Downloadというボタンをクリックします。
すると様々なソフトが表示されますが、今回必要なものはsqlite-toolsとなります。
自分の環境にあったものをダウンロードします。(下はWindowsの例)

DLしたファイルはただの圧縮ファイルのため解凍して適当なフォルダに入れます。
ここではCドライブ直下にSQLiteというフォルダを作りその下に突っ込んでいます。

解凍した結果展開されるのは驚異の3ファイルのみです。
メインはsqlite3.exeでそれ以外はサポートツールになります。削除してもデータベース機能自体は使えます。
SQLは圧倒的に軽量がゆえに、GUIをサポートしていません。
コマンドプロンプトでsqlite3.exeがあるディレクトリまで降り、次のコマンドを打ち込みます。 これでSQLiteのターミナルに接続します(末尾の3を忘れずに)。ここからはWindowsのコマンドは使えません。

Linuxである場合は、最初にsudoを入れて管理者権限でsqlite3に入った方が良いかも。
管理者権限で入らないと、データベースの作成やテーブルの作成をすることが出来ません。
この際には「Error: unable to open database」や「Error: attempt to write a readonly database」といったエラーが出ます。
仮にSQLiteから抜けたい場合は次のコマンドを打ち込みます。 データベースの構成は、大雑把に言うとデータベースの中にテーブルがあり、そのテーブルの中にレコードがあります。

レコードはさらに、カラムと言う要素に分けられます。
下の画像で言うと名前、属性、補足の縦の列要素をカラムと言います。

まず一番上の要素のデータベースから作っていきます。名前はなんでも良いですがここはmydb.dbとします。
すると同一階層のディレクトリにデータベースファイルが作られます。

存在していないデータベースを指定した際はこのように新規作成され、すでに存在する場合はそのデータベースに接続します。
今回の場合あんまり関係ないですが、Linuxで作業する場合、作成したデータベースファイルはchmodで適切に権限を開放してあげる必要があります。
例えばサーブレットからデータベースにアクセスする場合、権限の開放がされてないとアクセスできません。
次にテーブルを追加していきます。
レコードを差し込む前に、テーブルの段階であらかじめ必要なカラムを指定する必要があります。
後ほどのレコード追加時には、存在しないカラムにはデータを登録できません。
下のcreate文を打ち込みます。
friendというテーブルを土台に、name(名前)、attribute(属性)、explain(説明)というカラムを同時に追加しています。
末尾の;を忘れやすいので注意。
このcreate文がこれまでで初めて出てきたSQL言語になります。
これまでの.openや.exitはSQLite独自の命令であって、ほかのSQLクライアントでは使えません。
概ね「.」から始まる文がSQLite独自の命令で「;」で終わる文がSQL言語になります。
create文を打ち込んだら、下のコマンドで開いているデータベースの全てのテーブルが表示されます。

テーブルはいくらでも追加できます。stationery(文房具)のテーブルに、カラムはname(名前)とprice(値段)を指定します。

下のコマンドを打つと、それぞれのテーブルが何のカラムを持ってるか簡単に確認できます。

drop文を打つことで、作成済みのテーブルを削除できます。

最新のSQLite3ならテーブルの土台だけ作って、後からカラムを追加したりもできます。
下のコードはfriendテーブルにpriority(優先順位)のカラムを追加しています。 また下のコードでカラムの削除もできます。 テーブルを指定してinsert文を打つことで、そのテーブルにレコードを追加できます。
一般的なプログラミング言語と違い、文字列を囲む記号はシングルクォートのみしか受け付けないので注意。
追加されたレコードは、select文を使うことで確認できます。

*の部分はここでは「全てのカラム」を意味する。
つまりこのコマンドでは、friendというテーブルに追加された全てのレコードの全カラムを表示しています。
もちろんレコードも幾らでも追加できる。

さきほど使ったselect文では、条件を指定して複雑な検索をかけることが可能です。
この条件指定はSQLの本丸と言えるような部分で、その分覚えることもとても多いです。ほんの触りだけ紹介。
まず、*の代わりにカラム要素を指定すればそこだけを表示できます。

後ろにwhere句を付けることで、表示するレコードを絞り込むことが可能です。
下の例では=を使って完全一致の条件判定をしていますが、C#やjavaのように==でも問題なく判定できます。

!=を使うと条件を反転させてそれ以外を取得できます。

whereのカラム名の後ろにlikeを付けることで、完全一致ではなく部分一致を指定できます。
同時に%を検索文字列に含める必要があるのが少しややこしいです。

not likeを使うことで条件を反転させることができます。

一般的なプログラミング言語のように、andやorを使ってどんどん条件を追加もできます。


update文を打つことで、指定したテーブル内、条件に合致したレコードのカラムを更新できます。
下の例では、friendテーブル内、指定したレコードにexplainカラムに更新をかけています。
カラムの指定が先でレコードの指定が後ろなのが少し直感的ではないです。

where句以降はselect文と同じことが出来るので、下のような更新の仕方もできます。

全てのレコードの特定のカラムを更新したい時は次の通り。単純にwhere句を削るだけでOKです。
delete文を打つことで、指定したテーブル内、条件に合致したレコードを削除できます。

where句以降はselect文と同じことが出来るので、下のような削除の仕方もできます。