SQLite3

作成日 : 2017-03-28
最終更新日 :

SQLite3とは

SQLite3とは、データベースを扱うプログラムである。 以前データベースススペシャリストに載せていた記事を独立させた。

SQLite

何より、軽さを身上とする SQLite について調べる。軽いということから、コンピュータでデータベースを扱う例として紹介されることも多い。 たとえば、作りながら学ぶ Ruby 入門で取り上げられている。

インストール

2017 年当時は次のとおりだった。Windows 10 Home Edition でのインストール手順である。

  1. https://www.sqlite.org/ にアクセスし、[Download] ボタンをクリックする。
  2. Precompiled Binaries for Windows の項の sqlite-tools-win32-x86-numbers.zip をダウンロードする。
  3. zip ファイルを展開し、適当なところに置く。私は %USERPROFILE%\sqlite に置いた。
  4. PATH を通す。これには次のようにする。設定パネルにある [設定の検索] 欄に PATH と入力してみる。検索結果で「環境変数を編集」をクリックする。 上の「xxxxxxのユーザー環境変数」の Path 行をクリックし [編集] ボタンをクリックする。 [新規] ボタンを選び %USERPROFILE%\sqlite を入力する。以下セーブする。

2023 年 2 月現在も変わらない。

起動と終了

データベースファイルは %USERPROFILE%\db に置く。以下、データーベースファイルは１ファイルとして、sample.sqlites3 であるとする。 ただし、この段階では sample.sqlites3 は作成されていないものとする。

shell を立ち上げる。いわゆる DOS 窓よりは、Windows Power Shell を使うのがよい。 キーボード入力部は下線を引いた。 

> sqlite3 sample.sqlite3
  SQLite version 3.24.0 2018-06-04 19:24:41
  Enter ".help" for usage hints.
  sqlite> .quit
>

例：好きなことか困ったこと

テーブルの作成

例は、好きなことで楽しめるか、困ったことを解決できるかで選ぶといい。私は、困ったことの解決に使おうとした。 困ったこととは、西暦と平成の年号の換算である。西暦２桁と平成２桁の差を定数として覚えればいいのだが、 今年の換算しかわからない。そこで、year という名称の テーブルを作ってみる。

sqlite> CREATE TABLE year(id integer, gen_name text, gen_int integer);

ここで、id が西暦を、gen_name が元号名を、gen_int が元号による換算年を表している。 日本語と英語が混ざって読みにくいが、勘弁してほしい。

データの追加

データベース、テーブルとそれぞれ作ってきたので、次は１つのテーブルに複数のデータを追加する。 ここでいうデータとはテーブルの１行に相当する概念で、１レコード、１タプルなどともいう。 そのときどきで単位が異なるので注意してほしい。

まず、今年は 2019 年、平成 31 年だから次のようにデータを追加する。

> sqlite INSERT INTO year VALUES(2019, "Heisei", 31);

同じように、ただし西暦と平成の年の組を違えながら作っていく。

> sqlite INSERT INTO year VALUES(2009, 'Heisei', 21);
> sqlite INSERT INTO year VALUES(1999, 'Heisei', 11);
> sqlite INSERT INTO year VALUES(1989, 'Heisei',  1);
> sqlite INSERT INTO year VALUES(1996, 'Heisei',  8);
> sqlite INSERT INTO year VALUES(1993, 'Heisei',  5);

追加の方法は以上だ。

ちなみに、INTO を抜かして

INSERT year VALUES(1988, 'Showa', 63);

とやると、

Error: near "year": syntax error

となる。当たり前だ。

データの取得：全体

では全体を見るときはどうするか。* を使う。シェルのワイルドカードに似ているのでイメージしやすい。 

> sqlite> SELECT * FROM year;
2019|Heisei|31
2009|Heisei|21
1999|Heisei|11
1989|Heisei|1
1996|Heisei|8
1993|Heisei|5
> sqlite>

しかし、テーブル名を思いつかなければ、どうしようもない。

> sqlite> SELECT * 
Error: no tables specified
> sqlite>

データの取得：射影

全ての行の、特定の属性（カラム）だけ表示するにはどうするか。 

> sqlite> SELECT id, gen_int FROM year;
2019|31
2009|21
1999|11
1989|1
1996|8
1993|5

特定の属性だけ表示することを射影という。

これまた、属性名をしらなければ何もできない。

データの取得：選択：等号条件

特定の属性（カラム）の条件が当てはまる行をすべて表示するにはどうするか。まずは１つの等号条件だけの場合を調べる。 

> sqlite> SELECT * FROM year WHERE gen_int=5;
1993|Heisei|5

思ったとおりである。しかし、順序を間違えると、出てこない。 

> sqlite> SELECT * WHERE gen_int=5 FROM year;
Error: near "FROM": syntax error

出てきたのはよかったが、平成 5 年は１行しかないからありがたみがない。しかし、たとえば、昭和も交えてみよう。 

> sqlite INSERT INTO year VALUES(1988, 'Showa', 63);
> sqlite INSERT INTO year VALUES(1985, 'Showa', 60);
> sqlite INSERT INTO year VALUES(1981, 'Showa', 56);
> sqlite INSERT INTO year VALUES(1978, 'Showa', 53);
> sqlite INSERT INTO year VALUES(1960, 'Showa', 35);

これらも交えて gen_name が 'Heisei' のみというのを取り出すことには大いに意味がある。 

> sqlite> SELECT * FROM year;
2019|Heisei|31
2009|Heisei|21
1999|Heisei|11
1989|Heisei|1
1996|Heisei|8
1993|Heisei|5
1988|Showa|63
1985|Showa|60
1981|Showa|56
1978|Showa|53
1960|Showa|35
sqlite> SELECT * FROM year WHERE gen_name='HeiSei';
2019|Heisei|31
2009|Heisei|21
1999|Heisei|11
1989|Heisei|1
1996|Heisei|8
1993|Heisei|5

WHERE の書き方さえうまくできれば、と思える。

データの取得：選択：不等号条件

特定の属性（カラム）の条件が当てはまる行をすべて表示するにはどうするか。 １つの等号を、１つの不等号に拡張する。 

sqlite> SELECT * FROM year WHERE gen_int > 5;
2019|Heisei|31
2009|Heisei|21
1999|Heisei|11
1996|Heisei|8
1988|Showa|63
1985|Showa|60
1981|Showa|56
1978|Showa|53
1960|Showa|35

当たり前だが、平成も昭和も一緒に出てしまった。平成だけにするには複数の条件句を指定すればよい。 

sqlite> SELECT * FROM year WHERE gen_int > 5 and gen_name='Heisei';
2019|Heisei|31
2009|Heisei|21
1999|Heisei|11
1996|Heisei|8

一休み

ここは SQL とは関係ない箇所である。SQLite の設定を見てみる。

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: .\sample.sqlite3
sqlite>

今まででお分かりの通り、私が SELECT コマンドを実行しても列名が出てこなかった。 これは headers: off のことだと思われる。これを on にすれば列名が出てくるのではないか。

sqlite> .headers on
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: .\sample.sqlite3
sqlite> SELECT * FROM year WHERE gen_int > 5 and gen_name='Heisei';
id|gen_name|gen_int
2019|Heisei|31
2009|Heisei|21
1999|Heisei|11
1996|Heisei|8

これで列名が出るようになった。

さて、もう一つテーブルを作ってみよう。日本語が出るかどうか。

sqlite> CREATE TABLE gengou(gen_name text, gen_ja text);
sqlite> INSERT INTO gengou VALUES("Reiwa", "令和");
sqlite> INSERT INTO gengou VALUES("Heisei", "平成");
sqlite> INSERT INTO gengou VALUES("Showa", "昭和");
sqlite> INSERT INTO gengou VALUES("Taisho", "大正");
sqlite> INSERT INTO gengou VALUES("Meiji", "明治");
sqlite> INSERT INTO gengou VALUES("Keio", "慶応");
sqlite> SELECT * FROM gengou;
Reiwa|令和
Heisei|平成
Showa|昭和
Taisho|大正
Meiji|明治
Keio|慶応

漢字も出ることがわかった。

では二つのテーブルの連携ができるだろうか。

sqlite> SELECT * FROM year, gengou WHERE year.gen_name = gengou.gen_name;
2019|Heisei|31|Heisei|平成
2009|Heisei|21|Heisei|平成
1999|Heisei|11|Heisei|平成
1989|Heisei|1|Heisei|平成
1996|Heisei|8|Heisei|平成
1993|Heisei|5|Heisei|平成
1988|Showa|63|Showa|昭和
1985|Showa|60|Showa|昭和
1981|Showa|56|Showa|昭和
1978|Showa|53|Showa|昭和
1960|Showa|35|Showa|昭和

音楽を例に

元号と西暦では生々しいので、もう少し穏やかに音楽を題材にとろう。

GROUP BY と HAVING

Q. “音楽家”表から、肩書がピアニストである音楽家が３人以上いる国の国名を取得する SQL 文を書け。ここで、実線の下線は主キーを表す。

音楽家(音楽家ID, 国名, 音楽家名, 肩書)

A. 次のように考える。

  1. 肩書がピアニストである音楽家を“音楽家”表から抽出する
    SELECT 肩書 FROM 音楽家
    　　WHERE 肩書 = 'ピアニスト'
  2. 1. で抽出された行を国名別にグループ化する
    　　GROUP BY 国名
  3. 2. でグループ化された行が 3 以上の（すなわち、国別のピアニストの人数が 3 人以上いる国を抽出する。
    　　HAVING COUNT(*) >= 3

補足：上記データベースは、肩書は一人につき一つのみである。 コンポーザーピアニスト（作曲家兼ピアニスト）など、兼任のときは（そして音楽家は当然そうあるべきなのだが）、 別に考えないといけない。

Q. 次の“音楽家”表から新たに得られる表を答えよ。

音楽家

音楽家名享年国名
フォーレ79フランス
アルベニス48スペイン
コープランド90アメリカ
ドビュッシー55フランス
ラヴェル62フランス
グラナドス48スペイン

SQL 文

SELECT AVG(享年) FROM 音楽家 GROUP BY 国名 HAVING COUNT(*) > 1

A. “GROUP BY 国名”句で，音楽家表をグループ化した表は次のようになる。

音楽家名享年国名
コープランド90アメリカ
アルベニス48スペイン
グラナドス48スペイン
ドビュッシー55フランス
フォーレ79フランス
ラヴェル62フランス

HAVING COUNT(*) > 1 の条件から、グループ化された行が 2 行以上ある国名、すなわちスペインとフランスが選択される。
集合関数 AVG(享年)から、平均享年を計算する。
スペイン　……　(48+48) / 2 = 48.0 フランス　……　(55+79+62) / 3 = 65.3333333333

したがって得られる表は次の通り。

AVG(享年)
48.0
65.3333333333333

副問い合わせ

まず、副問い合わせとは、SELECT …… FROM …… WHERE …… IN (SELECT …… FROM …… WHERE ……) のような問い合わせで、 カッコ内の問い合わせのことをいう。サブクエリーとも呼ぶ。

（相関ではない）副問い合わせは、カッコ内の SELECT 文の WHERE 句のなかに、カッコの外にある SELECT 文の FROM 句で指定された表の名前が使用されていない場合をいう。

問. “作曲家”表と“国”表に対して、次の SQL 文を実行した結果はどうなるか。
　　SELECT 国名, 作曲家ID FROM 国
　　　　WHERE 作曲家ID IN
　　　　(SELECT 作曲家ID FROM 作曲家 WHERE 没年 <= '1900')

作曲家

作曲家ID没年作曲者
071711890フランク
106221894ルクー
048611918ドビュッシー
045741979デュレ
040111983タイユフェール
089621974ミヨー
072011963プーランク
010231955オネゲル
010721983オーリック
014121918キュイ
104811908リムスキー＝コルサコフ
061511910バラキレフ
085921887ボロディン
090631881ムソルグスキー

国

国名作曲家ID
ベルギー10622
フランス07171
フランス04861
フランス04574
フランス04011
フランス08962
フランス07201
フランス01023
フランス01072
ロシア 01412
ロシア 10481
ロシア 06151
ロシア 08592
ロシア 09063

ここで、生まれ年が1900年より前(1900年は含まず)の演奏家がメンバーとなっている、 演奏団体名と演奏家IDを表示したい。

相関副問い合わせ

（考慮中）

概念データモデル

データベーススペシャリストの試験でよく使われる概念データモデルを SVG で描いてみた例をもとに、 いくつかの典型例を挙げる。

自己参照

自己参照のある階層構造の例として、「組織」や「部品」などがある。「楽曲」と比較してみよう。

組織

上位組織と下位組織があれば、自己参照がある。

部品と部品表

部品は製品の一部をなす構成物である。 部品表は“部品構成”テーブルと“部品・製品”テーブルの総称である。部品・製品が他の部品・製品に使われるイメージである。

楽曲

楽曲にも再帰構造がみられる。たとえば、バッハの平均律（１巻、２巻）は、２４の「前奏曲とフーガ」からなる。 １つの「前奏曲とフーガ」は１つの「前奏曲」と１つの「フーガ」からなる。強引だが、平均律全体も、「前奏曲とフーガ」も、「前奏曲」も「フーガ」も、 １つの楽曲としてみれば楽曲という自己参照のある階層構造をなしている。

ER 図

自己参照のある ER 図を下に示した。

テーブル

部品表の例のテーブルは次の通りとなる。

部品・製品（部品製品番号）, 名称, 部品・製品区分, …）
部品構成（上位部品製品番号, 下位部品製品番号, 構成数量）

連関エンティティ

核エンティティの主キーを組にして新たな主キーを作る。このときのエンティティを連関エンティティと呼ぶ。 実際には、n : m の関連を 1 : n の関連にするときに作るエンティティが連関エンティティである。

＊＊＊構成

＊＊＊構成と名がつくインスタンスは、連関エンティティである可能性が高い。

所要量展開

部品表を使って製品を組み立てるために必要な部品およびその量を求めることを所要量展開という。連関エンティティである可能性が高い。

参考 WEB サイト

まりんきょ学問所 ＞ コンピュータの部屋 ＞ SQLite3

MARUYAMA Satosi