Photo by Gabriel Ghnassia on Unsplash

データの取り扱いについて

ある程度大規模だったり複雑だったりするデータを処理することはコンピュータではよくあることだ． よくあることであるので，こうしたデータを「どのように保存するか」「保存したデータを どのように取り出すか」という視点できちんと捉えて一定の規格にのっとってこれらを共通化することで，典型的な作業についてはプログラマがいちいち細かいことを考えないで済ませることができるようになる(そもそも論で言えば OS が提供する機能はある程度こうしたものばかりだが．)． こうした考え方が一体の形をとったものが以下に述べるデータベースソフトウェアなどと総称されるもの，と思えば良い．

データベース(ソフトウェア)とは

大雑把に言えば，収集されたデータの集合体そのものを指す. または，データを保存すること，また，一定のルールにそって蓄積したデータの中から必要なデータを抽出すること，等のデータ収集かつ抽出の機能を持ったプログラムやライブラリをデータベースソフトウェア，データベースライブラリなどと呼び，その略称としてデータベースと呼ぶ．

データベースソフトウェアの強みはまずはその汎用性，つまり，操作などが共通であることにある． また，データを取り扱うことに特化していることにより，操作によるデータ破壊やデータ損失に備えた機構をもたせていたり，メモリやストレージの利用効率が高くなっていたり，検索などの操作が高速であったりと，その高機能性も利点である．

ただ，こうした高機能性はソフトウェアの肥大化，煩雑さと強い相関があり，ライトユーザーからみるとかえって使いにくい，わかりにくいという欠点にも繋がる．

  そのデータ構造に特化した賢いプログラムがあるならば汎用データベースソフトウェアよりも優秀だろうから， 汎用データベースソフトウェアを使い始める前に，対象とするデータの取扱いに特化した良いプログラムがないかチェックしよう．

データベースソフトウェアの種類

非常に大雑把に分けるのならば，簡易なものから非常に大規模なものまで以下のような3つに分類できるだろう．

• 表計算ソフト，単なるテキストファイル+Unixコマンド群 等々: 非常に広義に捉えるならばこれらはもっとも簡易なデータベースソフトウェアといえるだろう．
• RDBMS(リレーショナルデータベース管理システム): データベースソフトウェアの主流と言えそうなのがこれである．データの操作には主に SQL という言語が使われる． ソフトウェアとしては簡易なものはライブラリ，コマンドとして実装され，本格的なものはサーバとして実装される．
• 構造型ストレージ(NoSQL): 超大規模なデータや，リアルタイム性を重要視するような， RDB(リレーショナルデータベース)では扱いにくいデータを取り扱う． 操作が SQL でまかなえないということでもあるので，NoSQL (Not Only SQL) とも呼ばれる．

データの規模とデータベースソフトウェアの関係

データが単純，もしくは規模が小さい場合:

全データがコンソール画面 1枚に入りきるケースなど，データ規模が小さい場合は，データベースを導入する手間やデータを処理するソフト本体とデータの間にデータベースソフトウェアが挟まる分だけ，かえってコスト高になりかねない． そうした場合は無理にデータベースソフトウェアを導入しなくても良いだろう(導入してももちろん問題はないけれども)． こうした場合は単なるテキストファイルにデータを納めておくとか，いわゆる表計算ソフトウェアなどを利用した方が無理がなさそうだ．

また，例えば単純な観測データが数字で並んでいるだけ，というようにデータが単純な場合はそれに特化した処理系を用意したほうが見通しもよく，処理速度や記憶容量の圧縮性などの面で多くの利点があるだろう． 数値解析，数値計算などの場合はこうしたケースが多そうだ．

データがある程度複雑，もしくは規模が少し大きい場合:

現実的な想定としては住所録やソフトウェアの様々な設定保存などがこのあたりだろう． これぐらいの規模から，データベースの利用を検討する価値がでてくる．

ただし，複雑さや規模がそれほどでもないこの段階では，

• SQLite などの，RDBMS のなかでもサーバシステムではないような軽量データベースソフトウェア．
• 単なる整理したテキストファイル + 小さなシェルスクリプトの組み合わせ

ぐらいが(人的投入コストの面からも)適切と考えられる．

データが複雑，もしくは大規模な場合:

もう少しデータが複雑，もしくは大規模なケースはこちらになる． 具体的には，会社の各種業務用データやオンラインショッピングサイトのデータ処理などが想定対象だろうか． こうした規模では主に RDBMS が使われている． ただし，近年ではコンピュータの性能向上により，単なるテキストファイル+シェルスクリプト でこうした用途も充分まかなえるとする動きもある．

データが超複雑，もしくは超大規模な場合:

データが非常に複雑だったりその構造が解析前は不明だったり，もしくは規模が サーバ 1台に収まらないぐらい大規模だったりする場合，RDB は不向きである． こうした場合は構造型ストレージ(NoSQL)サーバが使われる．

  データが多いとき、データベースの効果は大きいよ

データが多くて定型的なとき、データベースは特にその力を発揮する．

SQLite

本授業で用いる SQLite とは，RDBMS のなかでも最も軽量で広く使われている(有名どころだと，firefox や android で使われている)ソフトウェアで(世界で一番広く使われている SQL エンジンだと主張している)，各種言語用ライブラリやコマンドとして実装される． パブリックドメインなので自由に使えるぞ．

サーバではないため複雑なインストール作業が不要で，すぐ使えるという利点がある． また，データベース一つにつきファイルを一つ作る方式のため，バックアップなどが取り易いのも便利な点である．

データベースの操作は SQL で行うため，他の RDBMS とあまり変わらない操作が行える． ただし，データの「型」設定を要求しないという融通さを持つので，簡単に使える．

主に使われるバージョンは 2系統と 3系統であるが，ここでは 3系統(ver.3)を用いる．

ちなみに、阪大の教育計算機システムの cygwin には sqlite3 コマンドがインストールされているので、これを使って実習ができる．

資料

それぞれの学習環境での状況

今回用いる SQLite3 のインストール状況は各環境で以下のような状態だ．

(上の表の補足) Ubuntu 22.04 に sqlite3 をインストールする手順:
下記のように，普通に apt コマンドでインストールすれば十分新しい版 ver. 3.37.2-2 がインストールされるのでそうすれば良い．

1
2
3
4
5
6

sudo apt udate
sudo apt upgrade
#
# …できればいったんリブート (sudo reboot)後に以下のコマンドを…
#
sudo apt install sqlite3

  sudo apt upgrade を行うと，/boot ディレクトリの空きが少なくなってくる． そのため sudo apt autoremove をリブート後にやっておくと良い． なお，そうしたメッセージが適切なタイミングで出るはずなのでそれを見逃さなければそれで問題ない．

SQLite の操作

他の著名な RDBMS はソフトウェアとしてはサーバ形式をとることが多い. これは、ネットワーク越しにデータベースを扱うためにはこの方が好都合だからで、 ある程度大きな規模のデータベースでは(利用者が複数になることもあり)必要な形式といえる。 しかし、サーバをインストール、稼働させるためにはシステムの管理者権限が必要だったり、特殊なアカウントを用意したりせねばならず、利用するための労力は小さくない. これに対し、SQLite はサーバ形式ではなく、単なるコマンド、もしくはライブラリからデータベースを操作するという単純な仕組みである.

本授業では、もっとも簡単なコマンドからの操作を持って学ぶものとする.

SQLite で用いる言語

SQLite を使っての操作には、以下の二種類があり、それぞれについて異なる言語(コマンド)を用いる．

1. SQLite そのものの操作:
   各種設定の変更、表示など．SQLite コマンド と呼ばれるコマンドを用いる． SQLite コマンドは .(ピリオド)で始まる単語であることが特徴である．
   
   
2. データベース処理:
   データベースへの操作．テーブルの作成、データの登録、データの抽出など、本来のやりたいこと． これには SQL コマンド を用いる．
   SQL コマンドはあるていど規格化されており, 標準的な機能を使うのであれば他のデータベースソフトウェアと同様に扱える．

SQLite におけるデータの取扱い

通常は、RDBMS では一つのデータベースの中に，テーブル(表)を複数作り， そのテーブルの中に一行ずつ決まった形式でデータを入れる(さらに、こうしたデータベースを複数作る)．

テーブルは注目するデータの分類ごと(例えば顧客リストを並べて作る顧客テーブル，商品リストを並べる商品テーブル，そして誰が何を買ったかという購買リストを並べた購買テーブルなど)に作ることが想定されており，それらを関係付ける(別のデータベースのテーブルと関係づけることも出来る)ことで全体の情報がまとまることになる． このように，リストアップできる情報をテーブルとして並べ，それらの関係をまたテーブルにする，というように［関係性］で全体の情報を表すのが大雑把に言えばリレーショナルDB である．

なお、SQLite では一つのデータベースを一つのファイルに入れるので分かり易いし、バックアップも取りやすい．

この授業では、売上を管理するデータベースを想定してみる． 具体的には、一つのデータベースの中に 商品情報のテーブル、顧客情報のテーブル、売上記録のテーブルを作ってみる． そのつもりで以下を読み進めてみよう．

環境依存のトラブルが有る場合の回避

少し古めの sqlite3 on cygwin では sqlite3 が「文字端末画面で少し変わった表示をしようとするとおかしくなる」問題がある．

Using SQLite3 with Cygwin に技術的な議論と解決方法があるので気になる人は読んでもらうとして、トラブルにあう場合はとりあえずインストールされている sqlite3 を使うのを諦め、一時的に新しい sqlite3 を用意して使うことにしよう．

具体的には以下のように作業して一時的に新しい sqlite3 で実習に取り組む準備をしよう．

1. Windows の "コマンドプロンプト" を起動しよう．
2. 今回の作業専用ディレクトリを作ってそこに移動しよう．例えば z:\sql を今回の作業専用ディレクトリとするならば、 コマンドプロンプトの中で 　

md sql
cd sql

とすればよい．

3. 上の表に書かれているリンクから Windows 用 sqlite3 バイナリをダウンロードして、解凍し、出てきたファイル(3つほどあるはず)を今回の作業専用ディレクトリに置こう．
4. 以降は、このコマンドプロンプトで下記の作業を行おう．

SQLite3 の起動、終了

  コマンドから SQLite を起動する = データベースへの接続 or データベースの作成

SQLite のコマンドは、引数に文字列を与えるとその名前のデータベース(ファイル)に接続しようとする。 もしそのデータベースがなければ新規にデータベースが作られる。 たとえば db1.db という名前のファイルでデータベースを作るとすると、 (今回は, 上で示したコマンドプロンプト中で)

1

sqlite3.exe db1.db

と入力すればよい. sqlite (ver.3) が起動してコマンド受付モードになる. プロンプトが sqlite> に変わることで判別がつくだろう.

  実習
上のようにして SQLite を起動しよう． 起動したらそのままにして、次の実習まで先へ読み進めよう．

  SQLite の終了

起動した SQLite を終了するには、

1

.exit

もしくは

1

.quit

でよい。 それぞれ一文字目がピリオドであることに注意しよう。

  SQLite3 の設定. .show コマンド.

SQLite を起動してから、まずは

1

.show

としてみよう． 現在の設定状況が以下のように表示される. もちろん、内容は設定により異なるので、これと同じとは限らない.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

         echo: off
          eqp: off
      explain: off
      headers: off
         mode: list
    nullvalue: ""
       output: stdout
 colseparator: "|"
 rowseparator: "\n"
        stats: off
        width:
     filename: db1.db

見て推測できるように、いろいろ設定はある. まあ，以下の 2つの設定のいずれかだけでもしておくと何かとわかり易くなるので, 手でデータベースにアクセスするときはこうしておこう．

(やっておくと良い設定)

1

.explain on

とするか，

1

.mode table

とする(両方を同時に有効にはできない)．

これは、画面出力をわかりやすくする設定であり、対話的に操作しているときはなにかと助かるだろう．

  実習
.show コマンドを試しに実行したのち、.explain on 設定か .mode table をしておこう．

  SQLiteコマンドのヘルプ. .help コマンド.

SQLite の中で

1

.help

としてみよう．

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?       Set output mode
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input from FILE
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save FILE               Write in-memory database into FILE
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output

という感じで，使える SQLite コマンドのリストがざっと出てきて簡単な説明が読める． 使いやすい .once や，.excel のような意外なコマンドがあるので，一度全部眺めておくとよいだろう．

SQLite3 でのテーブル「単独」操作

  テーブルを作る. create table コマンド.

リレーショナルデータベースの中身の本質はデータを列挙した塊であるテーブルである。 ただし、一つのテーブルの中に列挙されるデータはすべて同じ構造をしている必要がある。 そして、その構造をテーブル作成時に定義する必要がある。

SQLite でのテーブルの作成時におけるデータの定義は簡単だ． しかもデータの型の定義を省略することもできるので、慣れていないうちはテーブルの作成は次のようにすると良いだろう．

1
2

CREATE TABLE テーブル名
  (id INTEGER PRIMARY KEY AUTOINCREMENT, 変数名2, 変数名3, ...);

  SQLite3 は ; (セミコロン)を入力するまでは命令の解釈を待つので、長いコマンドを 無理に 1行で入力する必要はない．人間にとって分かり易いところで改行して入力を続けると良いだろう．

  慣習として SQL 命令は大文字で表記されるが、SQLite3 は小文字でも問題なく受け付ける． 対話的に入力するときは面倒だろうから、気にせずに小文字のままで入力してしまうのが簡単だろう．

  ここでは 変数名1 を id に決めてしまっているが、実は自由に決めて良い． ただし、慣れていないうちはこうしておくとなにかと後で助かるだろう．

  id の後の INTEGER PRIMARY KEY AUTOINCREMENT の意味は、 id という変数を

• 整数
• これを主キー(master)とする
• データ削除後の再利用は無しにする

という意味である． 主キーというのは、大雑把に言えば、その表の中でこの変数に対するデータは唯一であることが保証される変数である、という意味である．

  実習
"sqlite>" プロンプトに次のように入力して、商品情報の一つ目のテーブルとして fruits テーブルを作っておこう．

1
2

CREATE TABLE fruits
(id INTEGER PRIMARY KEY AUTOINCREMENT, name, price);

上の注意で書いたように、一行で一気に入力する必要はない． 最後の ; (セミコロン) を打つまでは命令は実行されないので、適当なところで改行するなどして、見やすい入力を心がけよう．

  データベースにどんなテーブルが有るか見てみる. .tables コマンド．

上で紹介したように，一つのデータベースには複数のテーブルを格納できる． そこで、データベース中にどんなテーブルがあるか調べる方法を書いておこう． これは .tables 命令(SQLite 命令)を使って

1

.tables

とするだけでよい．

  実習
上の .tables コマンドを用いて、確かに fruits テーブルが作られていることを確認しよう．

  テーブルを削除する. drop table コマンド．

作ったテーブルを削除するのは簡単で、

1

DROP TABLE テーブル名;

とするだけでテーブルを削除できる．

  この簡単な操作であっさり大量のデータを失うことがあるので、要注意だ！！

  テーブルにデータを追加する. insert into コマンド．

さて、データの追加について話そう． 存在するテーブルにはデータを追加できる．

データの追加は INSERT 命令を使って以下のように行う．

1

INSERT INTO テーブル名 VALUES(NULL, 変数2の内容, 変数3の内容, ...);

  こうしてデータを追加すると、変数 id には連番で自動的に数字が入力される.

  実習
"sqlite>" プロンプトに次のように入力して、 fruits テーブルに商品データを登録しよう．

1
2
3
4

INSERT INTO fruits VALUES(NULL, "banana", 100);
INSERT INTO fruits VALUES(NULL, "strawberry", 200);
INSERT INTO fruits VALUES(NULL, "apple", 150);
INSERT INTO fruits VALUES(NULL, "water melon", 1500);

  もちろん、他のテーブルやデータベースからデータをコピーしてくる方法や、他の普通のファイルからデータをコピーしてくる方法なども有る． 必要になったら調べよう．

  テーブルのデータを表示する. select コマンド．

テーブルに入っているデータをまずは見てみたいという場合は SELECT 命令を使って以下のようにする．

1

SELECT * FROM テーブル名;

  上の設定で .explain on か .mode table としているとこの表示が少しだけ分かり易いようになる．

  実習
上の SELECT コマンドを用いてみよう． 具体的には， select * from fruits; と打ち込むことで fruits テーブルにデータが登録されて いることを確認しよう．
うまくいっていれば、次のように表記されるはずだ(.mode table 設定の場合)．

1
2
3
4
5
6
7
8

+----|-------------|-------+
| id |    name     | price |
+----|-------------|-------+
| 1  | banana      | 100   |
| 2  | strawberry  | 200   |
| 3  | apple       | 150   |
| 4  | water melon | 1500  |
+----|-------------|-------+

  ここで，.once コマンドの動作を確認しておこう．次のように入力すると良い． 直後の出力結果一回分のみが指定したファイル(この場合は dout.txt) に出力される. エディタ等で dout.txt の中身を見て確認しておこう．

1
2

.once "dout.txt"
select * from fruits;

  ここで，.excel コマンドの動作も確認しておこう．次のように入力すると良い．

1
2

.excel
select * from fruits;

表計算ソフトがインストールされている環境で 「native な sqlite3 を使っている」 or 「xdg-util 等がインストールされている」ならば，出力結果が一回だけ excel などに下の図のように出力されることがわかるだろう(マシンが遅いと少し待たされるが…)．

さて，さらに、条件を指定してデータを見てみたいという場合は、SELECT 命令に WHERE 句を追加して以下のようにする．

1

SELECT * FROM テーブル名 WHERE 条件;

WHERE のあとに続く条件は柔軟に指定できる． ここで説明するには多すぎるので、まずは以下の二例をみて感覚をつかもう．

  実習
次の SELECT コマンドを用いて、fruits テーブル中で単価が 1000円以上の品をリストアップしてみよう．

1

SELECT * FROM fruits WHERE price > 1000;

うまくいっていれば、次のように表記されるはずだ．

1
2
3
4
5

+----|-------------|-------+
| id |    name     | price |
+----|-------------|-------+
| 4  | water melon | 1500  |
+----|-------------|-------+

さらに今度は、fruits テーブル中で名前が "ほにゃららnana" というものをリストアップしてみよう．

1

SELECT * FROM fruits WHERE LIKE('%nana',name);

これもうまくいっていれば、次のように表記されるはずだ．

1
2
3
4
5

+----|--------|-------+
| id |  name  | price |
+----|--------|-------+
| 1  | banana | 100   |
+----|--------|-------+

  LIKE 中の % は「任意の文字列」という意味を持つ．詳しくは調べよう．

さらに、項目すべてではなく、一部の項目だけ見たいという場合は SELECT 命令のあとに * ではなく、項目名を列挙する．

1

SELECT 見たい項目名1, 見たい項目名2, ...  FROM テーブル名 WHERE 条件;

  実習
次の SELECT コマンドを用いて、fruits テーブル中の単価「のみ」リストアップしてみよう．

1

SELECT price FROM fruits;

うまくいっていれば、次のように表記されるはずだ．

1
2
3
4
5
6
7
8

+-------+
| price |
+-------+
| 100   |
| 200   |
| 150   |
| 1500  |
+-------+

  テーブルのデータを修正する. replace into コマンド， update コマンド．

テーブルに格納されているデータを修正するには、REPLACE INTO 命令と UPDATE 命令が使える． 都合に合わせて好きな方を使おう．

まず REPLACE INTO は、次のように使う．

1

REPLACE INTO テーブル名 VALUES(id番号, 変数2の内容, 変数3の内容, ...);

  REPLACE INTO 命令を使った時、その id番号 をもつデータがない場合、そのデータはその id番号で新規に「登録」される． その意図がない場合は後々の混乱のもとになるので、修正のみ行いたい場合は次の UPDATE 命令を使ったほうが良さそうだ．

次に、UPDATE の使い方だが、次のようになる．

1

UPDATE テーブル名 SET 変数名n=変数nの内容 WHERE 条件;

  WHERE 以降を省略すると、データ全部が対象となる．気をつけよう．

  実習
次のように UPDATE コマンドを用いて、fruits テーブルのデータの中の、water melon の価格を 2000 に修正しよう．

1

UPDATE fruits SET price = 2000 WHERE id = 4;

うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．

1
2
3
4
5
6
7
8

+----|-------------|-------+
| id |    name     | price |
+----|-------------|-------+
| 1  | banana      | 100   |
| 2  | strawberry  | 200   |
| 3  | apple       | 150   |
| 4  | water melon | 2000  |
+----|-------------|-------+

  実習(応用)
さらに、次のように UPDATE コマンドを用いて、単価が 300円未満の商品を倍に値上げしよう．

1

UPDATE fruits SET price = 2*price WHERE price < 300;

うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．

1
2
3
4
5
6
7
8

+----|-------------|-------+
| id |    name     | price |
+----|-------------|-------+
| 1  | banana      | 200   |
| 2  | strawberry  | 400   |
| 3  | apple       | 300   |
| 4  | water melon | 2000  |
+----|-------------|-------+

  テーブルのデータを削除する. delete コマンド．

テーブル中のデータを削除するには、DELETE コマンドが使える．

1

DELETE FROM テーブル名 WHERE 条件;

  これも WHERE 以降を省略すると、データ全部が対象となる． つまり、データが全部消えてしまう! ので、間違えてそう操作しないよう気をつけよう．

  実習
次のように DELETE コマンドを用いて、fruits テーブルのデータの中の、 strawberry についての情報を削除しよう．

1

DELETE FROM fruits WHERE id=2;

うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．

1
2
3
4
5
6
7

+----|-------------|-------+
| id |    name     | price |
+----|-------------|-------+
| 1  | banana      | 200   |
| 3  | apple       | 300   |
| 4  | water melon | 2000  |
+----|-------------|-------+

  一度使われた id 番号 2 が欠番になっていることに着目しておこう．

  実習
次のように INSERT コマンドを用いてデータを追加し、 欠番 id がどのように扱われるか把握しよう．

1

INSERT INTO fruits VALUES(NULL, "orange", 400);

うまくいっていれば、SELECT コマンドで次のように表記されるはずだ．

1
2
3
4
5
6
7
8

+----|-------------|-------+
| id |    name     | price |
+----|-------------|-------+
| 1  | banana      | 200   |
| 3  | apple       | 300   |
| 4  | water melon | 2000  |
| 5  | orange      | 400   |
+----|-------------|-------+

  自動連番機能を採用したので id 番号 2 が欠番のまま再利用されていないことに注意しよう．

  ファイルからデータを読み込んで追加する. .import コマンド．

.import コマンドで、ファイルに書いてあるデータをテーブルへ追加できる． その .import の使い方は次のようになっていて、

1

.import ファイル名 テーブル名

このファイルにデータがどう記載されているかにはもちろんルールが有る．次のようなものだ．

• 1行に 1データ (表で言えば「1行=1データ」だったね)
• データ中の要素は、colseparator (デフォルトは | だ．.show コマンドで確認せよ)で区切る．
• 改行コードは rowseparator (デフォルトは \n だ．これも.show コマンドで確認せよ).

  実習
次のように .import コマンドを用いて、ファイルからデータを読み込もう．

まず、sqlite3 コマンドを実行しているディレクトリにファイル(この例では add-fruits.txt という名前にしておく)を作り、次のような内容を書き込もう．

1
2

6|"mango"|200
7|"kiwi"|150

それから、sqlite の中で次のようにして読み込む．

1

.import ./add-fruits.txt fruits

その後、select * fruits; コマンドで、確かにこれらがデータとして追加されて下記のようになっていることを確かめておこう．

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+----|-------------|-------+
| id |    name     | price |
+----|-------------|-------+
| 1  | banana      | 200   |
| 3  | apple       | 300   |
| 4  | water melon | 2000  |
| 5  | orange      | 400   |
| 6  | mango       | 200   |
| 7  | kiwi        | 150   |
+----|-------------|-------+

テーブルの「相互作用を考慮した」操作

データベースでのテーブル相互作用の基本は、

    何かの項目を共通キーとしてテーブルを合体表示させること

である． 難しくはないがやってみないと実感しにくいので、実習を通じて学ぶとしよう．

準備

実習を通じて学ぶために、少し準備をしよう． 具体的には、もう二つ、他のテーブルを同じデータベースの中に作成しよう．

  実習
次のように CREATE コマンドと INSERT コマンドを用いて、今のデータベースの中にもう2つのテーブルを作っておこう．

まず一つ目は、顧客リストを並べた customers テーブルである． 顧客として 3人ほどデータを入れておこう．

1
2
3
4
5
6

CREATE TABLE customers
(id INTEGER PRIMARY KEY AUTOINCREMENT, name);

INSERT INTO customers VALUES(NULL,"john");
INSERT INTO customers VALUES(NULL,"tom");
INSERT INTO customers VALUES(NULL,"mike");

select * from customers; 命令で中身をみると次のようになっているはずだ．

1
2
3
4
5
6
7

+----|------+
| id | name |
+----|------+
| 1  | john |
| 2  | tom  |
| 3  | mike |
+----|------+

二つ目は、売上リストを並べた sales テーブルである． 売上リストは、
     id,   顧客id,   商品id,   売れた個数
という並びでデータを格納することにしよう．

1
2

CREATE TABLE sales
(id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id, fruit_id, quantity);

としてテーブルを作ってから、例えば次のような 5 回の売上げ情報を入れておこう．

1
2
3
4
5

INSERT INTO sales VALUES(NULL,2,5,3);
INSERT INTO sales VALUES(NULL,1,1,1);
INSERT INTO sales VALUES(NULL,2,3,2);
INSERT INTO sales VALUES(NULL,2,1,4);
INSERT INTO sales VALUES(NULL,3,4,10);

select * from sales; 命令で sales テーブルの中身をみると次のようになっているはずだ．

1
2
3
4
5
6
7
8
9

+----|-------------|----------|----------+
| id | customer_id | fruit_id | quantity |
+----|-------------|----------|----------+
| 1  | 2           | 5        | 3        |
| 2  | 1           | 1        | 1        |
| 3  | 2           | 3        | 2        |
| 4  | 2           | 1        | 4        |
| 5  | 3           | 4        | 10       |
+----|-------------|----------|----------+

  この sales テーブルは他のテーブルの情報があって初めて意味があることに注意しよう．

  共通情報をもとにテーブルを合体表示させる. select の中で使う join オプション．

これでこのデータベースには計 3つのテーブルが有るはずである． 一応、.tables コマンドで

1

.tables

とすると、

1

customers  fruits     sales

と出力されることを確認しよう．

この 3つのテーブルのうち他のテーブルの情報を引用する形になっているのは sales テーブルであるので、 sales テーブルに他のテーブルの情報を合体(join)させてみよう． 具体的には、SELECT と同時に JOIN 命令を使って、合体させたテーブルから情報を取り出す形をとる． WHERE 句に似た使い方と言えばわかるだろうか．

文法としては次のような感じだ．

1
2

SELECT * FROM テーブル名1 
    JOIN テーブル名2 ON テーブル名1.項目名=テーブル名2.項目名;

  読み易いように上のコマンドは適当に改行して二行になっているが、行末の ;(セミコロン)の有無を見れば分かるように、SQLite からみたら 全体で 1行のコマンドである．

上の文法の 「= で繋がれている二つの項目が、このテーブル間で共通に扱われる情報」 である．

まあ、次の実習をやってみるとわかりやすいだろう．

  実習
次のように, sales テーブルの売上情報で、共通に入っている顧客の id を利用してテーブルをつなげて、顧客の名前が見えるようにしてみよう．

1
2

SELECT * FROM sales
    JOIN customers ON sales.customer_id=customers.id;

すると、次のような感じでデータが得られるはずである．

1
2
3
4
5
6
7
8
9

+----|-------------|----------|----------|----|------+
| id | customer_id | fruit_id | quantity | id | name |
+----|-------------|----------|----------|----|------+
| 1  | 2           | 5        | 3        | 2  | tom  |
| 2  | 1           | 1        | 1        | 1  | john |
| 3  | 2           | 3        | 2        | 2  | tom  |
| 4  | 2           | 1        | 4        | 2  | tom  |
| 5  | 3           | 4        | 10       | 3  | mike |
+----|-------------|----------|----------|----|------+

全体の、第2項目(sales テーブルの customer_id) と第5項目(customers テーブルの id)が一致していることを確認しておこう．

これでは見にくい、というようであれば出力を見やすいものだけにすればよい． 例えば次のような感じだ．

1
2

SELECT sales.id, name, fruit_id, quantity FROM sales
    JOIN customers ON sales.customer_id=customers.id;

次のような出力が得られるはずだ．

1
2
3
4
5
6
7
8
9

+----|------|----------|----------+
| id | name | fruit_id | quantity |
+----|------|----------|----------+
| 1  | tom  | 5        | 3        |
| 2  | john | 1        | 1        |
| 3  | tom  | 3        | 2        |
| 4  | tom  | 1        | 4        |
| 5  | mike | 4        | 10       |
+----|------|----------|----------+

  テーブルを合体させると、同じ項目名が使われていることがある． これを曖昧に記述して命令するとエラーになるので、そういう時は上のように テーブル名.項目名 として指定して、区別をする． 実際、上の例でも SELECT の対象で sales.id として区別しているのに注意しよう．

JOIN は同時に複数使える! (3つ以上のテーブルを一度に合体できる)

JOIN はいくつでもテーブルを合体させられる． 単に後ろに続けて記述すればいいだけである． 次の実習で確かめよう．

  実習
sales テーブルの売上情報で、顧客の名前だけでなく、商品の名前や単価も見えるようにしよう． 具体的には先の例にさらにもう一回 join をすれば良い．

1
2
3

SELECT * FROM sales
    JOIN customers ON sales.customer_id=customers.id
    JOIN fruits ON sales.fruit_id=fruits.id;

すると、横に長いが, 次のような出力を得るはずだ．

1
2
3
4
5
6
7
8
9

+----|-------------|----------|----------|----|------|----|-------------|-------+
| id | customer_id | fruit_id | quantity | id | name | id |    name     | price |
+----|-------------|----------|----------|----|------|----|-------------|-------+
| 1  | 2           | 5        | 3        | 2  | tom  | 5  | orange      | 400   |
| 2  | 1           | 1        | 1        | 1  | john | 1  | banana      | 200   |
| 3  | 2           | 3        | 2        | 2  | tom  | 3  | apple       | 300   |
| 4  | 2           | 1        | 4        | 2  | tom  | 1  | banana      | 200   |
| 5  | 3           | 4        | 10       | 3  | mike | 4  | water melon | 2000  |
+----|-------------|----------|----------|----|------|----|-------------|-------+

わかりやすくするならば、次のような感じか．

1
2
3

SELECT sales.id, customers.name, fruits.name, price, quantity FROM sales
   JOIN customers ON sales.customer_id=customers.id
   JOIN fruits ON sales.fruit_id=fruits.id;

すると、次のような分かり易い出力を得るだろう．

1
2
3
4
5
6
7
8
9

+----|------|-------------|-------|----------+
| id | name |    name     | price | quantity |
+----|------|-------------|-------|----------+
| 1  | tom  | orange      | 400   | 3        |
| 2  | john | banana      | 200   | 1        |
| 3  | tom  | apple       | 300   | 2        |
| 4  | tom  | banana      | 200   | 4        |
| 5  | mike | water melon | 2000  | 10       |
+----|------|-------------|-------|----------+

これをみれば、例えば第4回目の取引きでは tom が200円のバナナを4本買っていったことがすぐわかる．

  こんな面倒なことをしないで、いろいろ書いた表を「1つだけ用意して」データを足していけばいいんじゃないかと思う人もいるだろう．
しかしそれをやると、途中で何かを変更すると過去のデータとの一貫性を失ってしまったり、 ある欄では tom と書き他の欄では ton と記載ミスすると別人扱いになってしまったりするのだ．

商品情報は商品テーブル単独で、顧客情報は顧客テーブル単独で，と管理でき、かつ、それらの情報を 他のテーブルから参照できる、というのが RDB のポイントなのである． これはデータの管理の「一貫性」や「安全性」，そして「柔軟性」を意味する．

データが多く、複雑になればなるほどこのことの重要性は増すだろう．

さて、さらに, データをただ見るだけではなくて、計算するなどの加工を少ししてみよう．

1
2
3
4

SELECT sales.id, customers.name, fruits.name, price, quantity, price*quantity
    FROM sales
        JOIN customers ON sales.customer_id=customers.id
        JOIN fruits ON sales.fruit_id=fruits.id;

すると次のように、単価x個数=小計 もリストアップできる．

1
2
3
4
5
6
7
8
9

+----|------|-------------|-------|----------|----------------+
| id | name |    name     | price | quantity | price*quantity |
+----|------|-------------|-------|----------|----------------+
| 1  | tom  | orange      | 400   | 3        | 1200           |
| 2  | john | banana      | 200   | 1        | 200            |
| 3  | tom  | apple       | 300   | 2        | 600            |
| 4  | tom  | banana      | 200   | 4        | 800            |
| 5  | mike | water melon | 2000  | 10       | 20000          |
+----|------|-------------|-------|----------|----------------+

(応用)
さて、さらに、誰にいくら請求するべきかも合計を見てみよう． 顧客情報をもとにまとめるわけだが、これには GROUP BY という命令を使う(詳しくは各人で調べよう)． なお、合計には単なる SUM という命令が使える．

1
2
3
4

SELECT customers.name, SUM(price*quantity) FROM sales
   JOIN customers ON sales.customer_id=customers.id
   JOIN fruits ON sales.fruit_id=fruits.id
       GROUP BY customers.id;

すると、次のような出力を得ることができる．

1
2
3
4
5
6
7

+------|---------------------+
| name | SUM(price*quantity) |
+------|---------------------+
| john | 200                 |
| tom  | 2600                |
| mike | 20000               |
+------|---------------------+

これで、だれに合計でいくら支払ってもらえばよいか、一目瞭然である．

ここまではコマンドを対話形式で入力したが，自動化しにくいよね？

ここまでは SQLite や SQL コマンドに慣れるために対話形式でコマンドを入力するやり方で学習してきたが，自動化等のためには他のコマンド入力方法が必要だろう．

もちろんそうしたことは考えられている． シンプルなのは以下の

• コマンドをファイルに記載し，そのファイルの中身を cat 等で SQLite に流し込む方法.
  
  
• SQLite 起動時のデータベース名のあとにコマンドをそのまま記載する方法. ただしコマンドを一つしか渡せない.

という方法だろう． 以下の実習で確かめよう．

  実習．例えば下記のようにやってみて，用意したコマンドで sqlite を動かしてみよう．

1. 以下の内容を書き込んだ ds.sql という名前のファイルを用意する．

1
2
3

INSERT INTO fruits VALUES(NULL, "avocado", 550);
INSERT INTO fruits VALUES(NULL, "coconut", 1050);
INSERT INTO fruits VALUES(NULL, "peach", 830);

2. もしも sqlite3 コマンドを実行中ならば .exit でいったん終了しよう． それから次のようにして，sqlite3 コマンドにこの内容を実行させよう．

1

cat ds.sql | sqlite3 db1.db

3. 以下のようにして select コマンドを直接実行させ，確かに先ほどのコマンドが実行されてデータベース db1.db の fruits テーブルにデータが追加されていることを確認しておこう．
     SQLite3 は，データベース名のあとにコマンド１つのみなら直接渡すことが出来る．

1

sqlite3 db1.db "select * from fruits;"

4. それから次のようにして，1つだけならコマンドを直接渡すことが出来る機能を使って sqlite3 にさらにコマンドを実行させよう．

1

sqlite3 db1.db "INSERT INTO fruits VALUES(NULL, 'lemon', 180);"

3. 再び select コマンドを直接実行させ，確かに先ほどのコマンドが実行されてデータベース db1.db の fruits テーブルにさらにデータが追加されていることを確認しておこう．

1

sqlite3 db1.db "select * from fruits;"

まとめ

今回は SQLite をコマンドラインから直接操作して RDBMS の入門的な部分を理解できるような内容とした． RDB を実用に供する場合は，ライブラリ経由かサーバ経由となるため，さらに理解しないといけないことが増えるが，基本は同じである． そういった際によくわからなくなったら，今回のように直接コマンドラインから該当する操作をしてみて，実際に何が起きるか確認してみるのも良いだろう．

おまけ. Emacs で使いやすい？ SQL モード on Emacs

Emacs にはデフォルトでデータベースと SQL でつながるモードが有り，SQLite にももちろんつながる． コマンドを書き込む操作はエディタからのほうが断然楽なので¹，Emacs 使いにはおすすめだ．

  Emacs 中で M-x sql-sqlite としてみよう．
この場合は「接続するデータベースファイルを教えろ」と言ってくるので，例えば先に作った db1.db を指定すればそこへつながる．

あとは今回の実習とほぼ同じ操作が可能だ． 下の絵は実際に Emacs 上の sql-mode で今回の実習を行ったときの最後の join を使った画面だ．

  Emacs 使いで余裕のある人は，このモードを使って上の実習と同様のことを行ってみよう． terminal やコマンドプロンプトに書き込むよりも操作が楽なので，気楽に操作できるぞ．

おまけ. GUI で見やすい？ SQLite3 ツール．

SQLite3 で作ったデータベースファイルを読み書きできて，かつ， SQLite3 コマンドとほぼ同様のことができる public domain な GUI ツール DB Browser for SQLite というものがある．

Windows でも Mac でも Linux でも動くというすぐれものだ．

  余裕のある人は，このツールを使って上の実習と同様のことを行ってみよう． 状況を見ながら操作できるので，理解がより深まるし，安心して操作できるぞ．

下の絵は，このツールで上の最後の join を使った実習を行ったときの画面だ．確かに買い物の合計金額が出ていることが見てわかるだろう．

レポート

以下の課題について能う限り賢明な調査と考察を行い，
2022-AppliedMath7-Report-10
という題名をつけて e-mail にて教官宛にレポートとして提出せよ. なお，レポートを e-mail の代わりに TeX で作成した書面にて提出してもよい.

  注意
　　近年はセキュリティ上の懸念から，実行形式のプログラムなどをメールに添付するとそのメールそのものの受信を受信側サーバが拒絶したりする． そういうことを避けるため，レポートをメールで提出するときは添付ファイルにそういった懸念のあるファイルが無いようにしよう．

課題

1. 上の実習を続けて、さらに、どの商品が合計でいくら売れたか、をどうやって出力させたら良いか sql コマンドを考えよう．結果は以下のようになるはずだ．

1
2
3
4
5
6
7
8

+-------------|---------------------+
|    name     | SUM(price*quantity) |
+-------------|---------------------+
| banana      | 1000                |
| apple       | 600                 |
| water melon | 20000               |
| orange      | 1200                |
+-------------|---------------------+

2. SQLite でデータベースの情報をテキストファイル等にバックアップしておく方法を調べよう．
   
   ヒント .dump コマンドを使うと良い． 直前で .once コマンドを使うと良いね．
   
   
3. 自分の生活の中で SQLite を利用すると便利になりそうなシーンがないか、考えてみよ．
   
   
4. SQLite のデータベースを GUI で操作できるアプリケーションとして DB Browser for SQLite を紹介した．授業中にこれに触らなかった者は，できればこれを使ってみよう． また，他に似たようなツールがないか探してみよう．
   
   
5. (チャレンジ問題) シェルスクリプト, C，Python, Go, PHP等のどのような言語でもよいので，sqlite を使ってみるプログラム相当を書いてみよう．ちなみに，ネットワークで検索すると山のようにサンプルが出てくるので前例には困らないはずだ．