INDEX

SQLite3単体でデータベース操作

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

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

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

仮にSQLiteから抜けたい場合は次のコマンドを打ち込む。 データベースの構成は、大雑把に言うとデータベースの中にテーブルがあり、そのテーブルの中にレコードがある。

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

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

存在していないデータベースを指定した際はこのように新規作成され、すでに存在する場合はそのデータベースに接続する。
次にテーブルを追加していく。
レコードを差し込む前に、テーブルの段階であらかじめ必要なカラムを指定する必要がある。
後ほどのレコード追加時には、存在しないカラムにはデータを登録できない。
下のcreate文を打ち込む。
friendというテーブルを土台に、name(名前)、attribute(属性)、explain(説明)というカラムを同時に追加している。
末尾の;を忘れやすいので注意。
最新のSQLite3ならテーブルの土台だけ作って、後からカラムを追加したり削除したりもできるが、結構面倒な事が起こる。
一緒にやってしまった方がいい。
ちなみこのcreate文がこれまでで初めて出てきたSQL言語になる。
これまでの.openや.exitはSQLite独自の命令であって、ほかのSQLクライアントでは使えない。
概ね「.」から始まる文がSQLite独自の命令で「;」で終わる文がSQL言語になる。
create文を打ち込んだら、下のコマンドで開いているデータベースの全てのテーブルが表示される。

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

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

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

テーブルを指定してinsert文を打つことで、そのテーブルにレコードを追加できる。
一般的なプログラミング言語と違い、文字列を囲む記号はシングルクォートのみしか受け付けないので注意。
追加されたレコードは、select文を使うことで確認できる。

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

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

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

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

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

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

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


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

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

delete文を打つことで、指定したテーブル内、条件に合致したレコードを削除できる。

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