SQLの最低限の知識

これまで SQL の説明なしにいくつか例を示したが、SQL の入門書の最初に出て来そうな範囲のものを使用した。このあたりで、「CSV ファイルでのデータ受け渡しや grep コマンドを用いた検索作業に慣れている」程度の知識を前提に、 SQL で遊べる程度の知識を得ておく。

もう一つ前提として、ここでの説明は リレーショナルモデルでのデータの正規化理論等の完全理解をあとまわしに したいと考えているような状態に即したものとしている。 もちろん最終的には正しい理解が不可欠だが、ある程度 SQL 操作に慣れたあとで立ち戻ることで深い理解が得られるはずである。 そのときには別途リレーショナルモデルの文献を参考にされたい。 また、既にテーブル設計に慣れている場合はこの節は読み飛ばし、 「シェルスクリプトの最低限の知識」 に進んで構わない。

なお、以下のテーブル設計例では、SQLite の特性を活かしてカラムの型を積極的に無指定にしている。 型なしが気になる場合もあるが、SQLite とシェルスクリプトの組み合わせではほとんど問題にならない。

テキストファイル指向からRDBへの要点

RDBを利用する場合は リレーショナルモデルに即したテーブルを設計することになるが、 既にテキストファイル処理に心得があるのであれば 以下の点に留意するだけで十分 RDB の利用が開始できる。

1. CSV構造に落して考える
2. grep 検索で十分な結果が得られるデータ構造にする
3. 項目の修正で不整合が出にくい設計にする

列と行を揃える

「RDBを用いる」と考えた時点で無意識にできることかと思うが、 表構造のデータにすることがまず必要である。 「すべてのレコードにおいて、同じ列には同じ性質の値が定まる」ようなデータが RDB に適している。逆にいえば、システムログファイルのように いろいろな種類のデータが混在するようなものはそのままの形では RDB に入れられない。

たとえば、以下のような形式のデータはそのままではRDBに入れられない。

この例は見るからに列の意味を無視した混沌データだと分かる。 ここまでひどくはなくても、実際のデータ記録ではこれに近いものを 作りたい場合がある。たとえば上記の例の電話番号の格納で、 自宅と携帯電話を入れるために列の定義を 「氏名」、「住所」、「電話番号1」、「電話番号2」に変えたとする。 慎重な設計者であればそれでは同じ問題が起こることが容易に想像できる。

• 3つめの電話番号を持つ人が出たらどうするのか?
• そもそも電話を最大何個持つと決めてかかってよいのか?

考えた始めた瞬間に表形式でデータを持つことをやめたくなったことは ないだろうか。実際にはこのような場合も シェルスクリプトで対処する規模のデータであれば、あまりこだわらず 「電話番号」列に「090-9998-0002;0234-99-9998」などと区切り文字ごと 入れてしまう仕様にするのも設計者の自由である。 ある程度SQLに慣れれば、区切り文字なしで電話番号の個数制限のない きれいな表設計も取れるようになるので、最初は 「列の揃ったCSVにさえ直せればよし」程度の心構えからRDB移行していくとよい。

1行完結レコードにする

grep で1行の結果を得たときに、それだけで把握できるようにする。

ときおり次のようなデータをもらうことがある。

印刷して目で見て理解してもらうことだけしか考えていないと このような入力をされがちである。これがテキストファイルだと仮定して C110134 にマッチするものを grep 検索したらどうなるだろう。

grep C110134 student-seminar.txt
C110134	飯森花子  〃	専門演習2  〃

これでは必要な情報が得られない。以下のようにすべきである。

しかし、このようなデータ並びを見ると、ハッシュ(連想配列)でのデータ格納に 慣れている場合は、同じ文字列の繰り返しが無駄に思えるのではないだろうか。 次の説明と関連する。

更新管理しやすいものにする

どれか1つの(キーでない)値を修正するとき、更新箇所が1ヶ所で済むようにする。

直前の表形式データの例で、同じ値(の組)の繰り返しがあるが、 これはデータ処理効率の点で好ましいとは言えない。 データの一部を修正する必要が出たと仮定する。 たとえば上記の例では「酒田康一」のメイルアドレス「koichi@example.jp」 の変更が発生したとする。その場合、修正が必要となる箇所は 「酒田康一」に所属する学生のレコードすべてとなる。

また、あるときに新たに配属された教員が同姓同名の「酒田康一」 だったらどうなるだろう。区別が付かなくなりデータそのものの価値が 一気に下がることになる。

このような問題を防ぐには、以下の点に気をつければよい。

• 名前の変わる可能性のあるものはidを付けて管理する
• idとつねに結び付いている値は別の表に分ける
• 別の表に分けた(idでない)値は元の表から削除する

まず、教員を一意に特定できるような教員IDを付与し、 元の表の氏名は教員IDに置き換える。

元の表には学生と教員それぞれのIDと、それにつねに結び付く値があるが、 それは冗長なので除去する。学生データに関しては、 学生IDと学生氏名の対応表を作る。

このように分割管理することで、 キー以外のどの値を修正するときも更新は1ヶ所で済むようになる。 キーとなる値に修正の必要が生じた場合は1ヶ所では済まないが、 もともと一意に定まる値であるから、確実な置換は可能である。

テーブルの作成

分割設計した3つの表を設計してみよう。 3表それぞれのカラムについて以下のようにカラム定義してみる。

それぞれの表のテーブル名を members、students、lecturers と決めると、テーブルとカラム名は以下のようにまとめられる。

表1: members(sid, kid, semi)
表2: students(sid, name)
表3: lecturers(kid, name, email)

データベースファイルを gakuji.sq3 として、 そのなかに CREATE TABLE で実際に作成してみる。

sqlite3 gakuji.sq3
CREATE TABLE members(sid, kid, semi);
CREATE TABLE students(sid, name);
CREATE TABLE lecturers(kid, name, email);

作成状態を確認する。

.sch
CREATE TABLE members(sid, kid, semi);
CREATE TABLE students(sid, name);
CREATE TABLE lecturers(kid, name, email);

3つのテーブルにレコード挿入する。 ここでは、全レコードを記入した CSV 形式のテキストファイルからのインポートの方法を示す。 以下の3つのファイルを用意する。

C110123,F10001,専門演習1
C110134,F10001,専門演習2
C110138,F10014,専門演習2
C110140,F10014,専門演習2
C110144,F10002,専門演習2

C110123,公益太郎
C110134,飯森花子
C110138,高見台一
C110140,緑智子
C110144,海原月山

F10001,酒田康一,koichi@example.jp
F10014,鳥海三郎,chokai@example.jp
F10002,酒田康一朗,ko16@example.jp

sqlite3 の動作モードを CSV モードに切り替えてから CSVファイルをインポートする。いずれもドットコマンドで行ない、 それぞれ .mode csv と .import を使用する。

.mode csv
.import members.csv members
.import students.csv students
.import lecturers.csv lecturers

インポートの結果を確認してみよう。

SELECT * FROM members;
C110123,F10001,"専門演習1"
C110134,F10001,"専門演習2"
C110138,F10014,"専門演習2"
C110140,F10014,"専門演習2"
C110144,F10002,"専門演習2"
SELECT * FROM students;
C110123,"公益太郎"
C110134,"飯森花子"
C110138,"高見台一"
C110140,"緑智子"
C110144,"海原月山"
SELECT * FROM lecturers;
F10001,"酒田康一",koichi@example.jp
F10014,"鳥海三郎",chokai@example.jp
F10002,"酒田康一朗",ko16@example.jp

CSVモードになっているため、出力もCSV形式となる。 現在の出力モード等は .show で確認できる。

.show
        echo: off
         eqp: off
  explain: off
     headers: off
        mode: csv
   nullvalue: ""
      output: stdout
colseparator: ","
rowseparator: "\r\n"
       stats: off
       width: 

出力モードは sqlite3 コマンドを一度抜けると標準に戻る。

.quit
sqlite3 gakuji.sq3
.show
        echo: off
         eqp: off
  explain: off
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 

CREATE TABLE の詳細

SQLでのテーブルの作成にかかわる要点を示す。 要点等はさておき表の加工方法など先を急ぎたい場合は次の レコードの抽出以降を読んでからここに戻ってもよい。 ここからの数節は SQLite3 のよく使う文法を集約した箇所となっている。

テーブル作成は CREATE TABLE 文で行なう。 CREATE TABLE の書式は以下のとおり。

CREATE TABLE テーブル名(カラム定義並び [, テーブル制約並び])

テーブル名はASCIIの英字とアンダースコア、UTF文字が指定でき、 名前の先頭でなければASCIIの数字が利用できる。ただし、SQL で定義された予約語(KEYWORD)は利用できない。SQLの予約語は 今後も増える可能性があるため、英語の単語を単独で使うことを 避けておくのが安全である。 なお、予約語や先頭に数字などを入れたい場合や 記号をテーブル名に使いたい場合などは、それをダブルクォートでくくればよい。 たとえば「10/3」のようなものはそのままではテーブル名に利用できないが "10/3" とすると利用できる。この規則はカラム名にも適用される(識別子規則)。

クォートが必要なほどの変わったカラム名を作る予定はないと思うかもしれないが、 最終出力カラムに付ける「別名」は、HTML出力の見出し行として採用されるため、 ユーザの分かりやすい別名カラムをつけておくと視認性が上がる。その場合には、 ダブルクォート括りが活用できる。

簡単に例を示す。sqlite3 の HTML 出力モードを利用して先述の students テーブルを出す場合に、

.mode html
.header ON
SELECT sid, name FROM students;

とすると、

という結果が得られるが、カラム別名を利用すると以下のような見出しとなる。

.mode html
.header ON
SELECT sid "学生ID(1年生)", name "氏名" FROM students;

とすると、

のように、英数字以外も自在に使える。

カラムの定義

カラム定義並び の部分には、 「格納カラム名」または「格納カラムとカラム制約」の並びを カンマで区切って列挙する。多くの RDB ではカラムの直後に型を指定する必要があるが、 SQLite では指定を省略できる。 型は指定してもしなくても、カラムに実際に値を入れるときに 値に即した型に変換される。 整数型を指定したカラムに文字列を入れることも可能である。

CREATE TABLE inttable(x INTEGER);
INSERT INTO inttable values(3);
INSERT INTO inttable values(3.14);
INSERT INTO inttable values("foo");
.mode column
.header 1
SELECT x, typeof(x) FROM inttable;
x           typeof(x) 
----------  ----------
3           integer   
3.14        real      
foo         text      

SQLite では取り出した値の実際の型を typeof 関数で確認できる。

「型指定」というよりは、カラムの値の挿入時に起こる 型変換のときに「優先される型」で、 SQLite ではこれを Type Affinity と呼ぶ。

Type Affinity

SQLite のテーブル定義で、 カラム名に添えて型指定する場合に指定できる型には以下のものがある。

SQLite に慣れるまではこれを気持ち悪いと感じるかもしれないが、 シェルスクリプトのように値に型を持たないものから利用する場合は、 型の検査はスクリプトの責任と強く意識することにつながるため、 整数型主キー以外のカラムであれば、型指定なしでの宣言を積極的に利用するのは 賢明な選択のひとつである。

別の型に変換するには cast 関数を利用する(SQLite)。

cast(値 as 型)

として対象型に変換する。数値のつもりが文字列で入れてしまった場合などは

UPDATE inttable SET x=cast(x as integer);

のようにすることで修正できる(UPDATE 文に関しては「データの更新」参照)。

なお、SQLite では「日付型」は存在せず、日付もたんなる TEXT として管理する。日付の書式は必ず以下のいずれかに揃えておく。

• YYYY-MM-DD (日付のみ)
• HH:MM:SS (時刻のみ)
• YYYY-MM-DD HH:MM:SS (日付と時刻)

これらは、SQLite ではそれぞれ

date('now', 'localtime')
time('now', 'localtime')
datetime('now', 'localtime')

で得られる。第2引数 'localtime' を省略すると UTC (協定世界時)での値となる。

「カラム制約」とは、当該カラムだけに課すべき制約の指定で、 以下のいずれかが適用できる。[ ] 内は省略可能である。

シェルスクリプトからの利用に視点を絞ると、 UNIQUE 制約は効果的に指定することで、 スクリプトでのデータ構成の検査を省力化できる。 本稿の例でも積極的に利用する。

rowidの活用

複数のRDBMSを経験していると、「自動的に増える整数を持つカラム」の 作り方が気になることが多い。その欲求には、SQLite ではカラムの型に INTEGER PRIMARY KEY AUTOINCREMENT を指定することで応えられるが、 実際のところこれはほぼ不要である。SQLite には、 すべてのテーブルにデフォルトで rowid カラムが暗黙のうちに付加される。 rowid は行ごとに重複しない整数のIDを持つカラムで、SELECT * では出てこず、明示的に SELECT rowid,* のように指定しなければ得られない。 行を一意に特定できるカラムであるため、行の更新を予定している問い合わせには rowid も取得するとよい。rowid の存在をうまく活用すれば、人工的な ID を無駄に増やすことなく設計できる。

テーブル制約の指定

カラム定義に続けて、必要ならテーブル制約 を指定できる。既に述べたカラム制約もこの位置に書けるが、 テーブル制約の位置には複数のカラムにまたがる制約も指定できる。 カラム制約で説明した「PRIMARY KEY」、「UNIQUE」、「CHECK」の他に、 投票システムの例で紹介した外部キー制約 「FOREIGN KEY」を、任意個数のカラムについて設定できる。 たとえば、以下のような表を考える。

ある講義は全15回開講される。それぞれについて5点満点の 出席課題を課していると想定する。講義完了後に、 学生ごとの出席課題の合計点を出すことを想定した場合、 値に関して以下の制約が考えられる。

1. 学生IDは履修者一覧テーブル(students)にある id カラムにあるものが正当なものである
2. ある学生(sid)のある回(lect)の出席課題得点のレコード(行)は 1つである(重複禁止)
3. 講義回は 1 から 15 の整数である
4. 出席課題点は 0 ≦ att ≦ 5 を満たしていなければならない

これらを順に制約定義に書き下すとそれぞれ以下のようになる。

1. FOREIGN KEY(sid) REFERENCES students(id)
2. UNIQUE(sid, lect)
3. CHECK(lect IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15))
4. CHECK(att BETWEEN 0 AND 5) あるいは CHECK(att>=0 and att<=5)

これを CREATE TABLE 文にまとめると以下のようになる(students テーブルは既に作られているものと仮定する)。

CREATE TABLE attpoints(
	sid TEXT, lect INT, att INT,
	-- ここまでがカラム定義、以下がテーブル制約
	FOREIGN KEY(sid) REFERENCES students(id),
	UNIQUE(sid, lect),
	CHECK(lect IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)),
	CHECK(att>=0 and att<=5));

登録される値の健全性を(スクリプトから見た)データ側が持つことで、 プログラムの構造を単純化できる。

レコードの挿入

レコードの挿入には INSERT 文を用いる。 ここでは値そのものを直接的に挿入する方法と、 他の問い合わせ結果を挿入する2つの方法について説明する。

値を指定しての代入

INSERTとVALUESの組み合わせで即値を代入する。

INSERT INTO テーブル VALUES(値1, 値2, ...);

テーブルのカラム数と、VALUESに与えた値の個数は一致させる。 ただし、テーブルのカラムのうち一部のものだけ値を決めて 挿入するには、以下のように代入したいカラムをテーブル名のあとに指定する。

INSERT INTO テーブル(カラム1, カラム2, ...) VALUES(値1, 値2, ...);

この場合、値を挿入するカラム以外のカラムは、NULL が入るか、 テーブル作成時にDEFAULT指定された値が入る。

問い合わせ結果によるCREATE TABLE/INSERT INTO

VALUES指定の代わりに、別のSELECT文を指定して、その問い合わせ結果を 挿入することができる。テーブルの作り換えなどでよく使う。たとえば、 出席集計テーブル定義例の attpoints テーブルで、講義回を保持する lect カラムの制約を変える必要が生じたとする。

CHECK(lect IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15))

講義に「補講」ができて第16回目の値が必要になったら、

CHECK(lect IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16))

にしなければならないが、あとからCHECK制約を変えられない。 「別のテーブル名で attpoints テーブルを作り直し、元の attpoints から値を」という手順で行なう。

CREATE TABLE new_attpoints(
	sid TEXT, lect INT, att INT,
	FOREIGN KEY(sid) REFERENCES students(id),
	UNIQUE(sid, lect),
	CHECK(lect IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
	CHECK(att>=0 and att<=5));
INSERT INTO new_attpoints SELECT * FROM attpoints;	-- 値をコピー
DROP TABLE attpoints;					-- 元のテーブルを削除
ALTER TABLE new_attpoints RENAME TO attpoints;		-- 新テーブルをリネーム

INSERT INTO の値供給の部分に SELECT文を用い、その SELECT 結果を挿入行としている。CREATE TABLE でも使える。 上記のテーブル変更手順は以下のように、 既存テーブルのバックアップ用の一時テーブルを1文で作ってから書き戻す手順でも書ける(一部省略表記にしてある)。

/* 「SELECT * FROM attpoints」の結果で得られるテーブルを新規作成する。 */
CREATE TEMPORARY TABLE bak_attpoints AS SELECT * FROM attpoints;
DROP   TABLE attpoints;
CREATE TABLE attpoints( 新しい制約 );
INSERT INTO  attpoints SELECT * FROM bak_attpoints;	-- 値の書き戻し
/* TEMPORARY TABLE はsqlite3セッション終了すると自然に消える */

SELECT の応用例をテーブルの再構築例を用いて2つ示したが、 既に稼動しているデータベースでは、 上記のような操作時に別のクライアントからアクセスがくると一時的にテーブルが見えない状態からエラーが発生する。 テーブルそのものを変えるような重い操作は次で述べるトランザクションを利用する。

トランザクション

テーブル再構築例の上記2手順では、一時的に attpoints テーブルが存在しない瞬間が発生する。一連の流れを不可分化し、 すべてが正常終了したときのみ修正をデータに書き込むようにするには トランザクション を用いる。

SQLiteでは BEGIN; ... COMMIT; で処理単位を挟んでトランザクションを実現する。

BEGIN [ { DEFERRED | IMMEDIATE | EXCLUSIVE } ] [ TRANSACTION ] ;
  ...
{ COMMIT | END } [ TRANSACTION ] ;

BEGIN に続けて、開始すべきトランザクションの種別を指定する(省略可)。

トランザクションを用いてテーブル再構築を行なう記述例を示す。

BEGIN;
CREATE TEMPORARY TABLE bak_attpoints AS SELECT * FROM attpoints;
DROP   TABLE attpoints;
CREATE TABLE attpoints( 新しい制約でテーブルを再作成 );
INSERT INTO  attpoints SELECT * FROM bak_attpoints;
COMMIT;

他にも、複数のテーブルを更新するときに、 すべてのテーブルの更新をひとまとめにしたいときにトランザクションを利用する。

また、SQLite3 はデフォルトで自動コミットモードになっていて、 1文ごとにコミットされる。そのため一度に大量の INSERT 文を実行するとその度にトランザクションが発生し遅くなる。 その場合は一連の問い合わせ文を「BEGIN;」と「COMMIT;」で挟み、 明示的にトランザクション化するとよい。

UNIQUE制約とREPLACE

UNIQUE(またはPRIMARY KEY)制約のあるテーブルに、重複するレコードを 挿入しようとした場合、通常はエラーとなるが、既存レコードの置き換えを させることもできる。SQLite では「INSERT INTO テーブル ...」の代わりに 「INSERT OR REPLACE INTO テーブル ...」とすることで、既存のものと 一致するレコードがあった場合に、それを置き換えることができる。 REPLACE INTO

何かの情報を保存するときに「変数=値」という流儀で進める場合、 ある変数の値を新しい値で置き換える場合には単純に「変数=新しい値」と するのが自然で、この流儀と「UNIQUE制約とREPLACE」の組み合わせは よく馴染む。スクリプトプログラミングではREPLACEを効果的に利用するとよい。 これについては、データの更新節で実例を示す。

CHECK制約と外部キー制約

上記の制約再設定例で用いている CHECK 制約はテーブル構築時に指定した静的な条件を使い続けなければならず 状況の変化に対応できない。 この使用が適しているのは曜日のようによほどのことがない限り変動しないものに限る。 取りうる値が変化する可能性があるものは外部キー制約の使用を検討するのがよい。

   :
 FOREIGN KEY(lect) REFERENCES ON lects(lect),
   :

CREATE TABLE lects(lect PRIMARY KEY);
WITH RECURSIVE from1to16(x) AS ( -- 再帰問い合わせの定義
    SELECT 1			 -- SELECT 1 と再帰問い合わせをUNIONする
      UNION
    SELECT x+1 FROM from1to16 WHERE x<16
) INSERT INTO lects SELECT x FROM from1to16;

lects テーブルに1から16の連番を入れるのに再帰問い合わせを利用した。 再帰問い合わせについては本稿では触れないが、 木構造の追跡などが可能となる記法である。

レコードの抽出

「SELECT * FROM テーブル;」とすることで、 テーブルに登録された全レコードが得られる。すべてのレコード・カラムを 引き出すことは実用上はまれで、実際には特定の条件に適合する レコードのうち、特定のカラムを選択抽出する。

条件指定例

先に、いくつかの選択例を簡単に示してから詳細に触れる。 選択条件は WHERE 句で指定する。

SELECT カラム指定 テーブル WHERE 条件;

• students テーブルから、学生IDが C110144 のもののみ選択する。 等しいかどうかは = で比較する。

  SELECT * FROM students WHERE sid='C110144';
  C110144,"海原月山"
  

• students テーブルから、学生IDが C11013 で始まるもののみ選択する。 文字列の部分一致の判定は LIKE を用いる。

  SELECT * FROM students WHERE sid LIKE 'C11013%';
  C110134,"飯森花子"
  C110138,"高見台一"
  

  「%」が任意の文字列にマッチさせるための特殊文字である。

• students テーブルから、学生IDが C11013 または C11014 で始まるもののみ選択する。

  指定方法はいくつか考えられる。

  SELECT * FROM students WHERE sid LIKE 'C11013%' OR sid LIKE 'C11014%';
  SELECT * FROM students WHERE sid BETWEEN 'C110130' AND 'C110149';
  SELECT * FROM students WHERE sid GLOB 'C1101[34]*';
  

  いずれも結果は

  C110134,"飯森花子"
  C110138,"高見台一"
  C110140,"緑智子"
  C110144,"海原月山"
  

  となる。3つめの GLOB は、SQLite 拡張で、シェルのファイル名マッチと同様の * ? [ ] が利用できる。

• members テーブルから、「教員IDがF10014」かつ 「履修科目が専門演習2」のもののみ選択する。

  SELECT * FROM members WHERE kid='F10014' AND semi='専門演習2';
  C110138|F10014|専門演習2
  C110140|F10014|専門演習2
  

条件指定で利用できる演算子

SQLite では、以下の二項演算子が利用できる(優先順位の高い順)。

|| (文字列連結)
*   /   %
+   -
<<  >>  &   |  (ビット演算)
=   ==  !=  <>   IS   IS NOT  IN  LIKE  GLOB
AND
OR

このうち、IS と IS NOT は NULL との比較に用いる。

また単項演算子は

-   +   ~   NOT

が利用できる。

BETWEEN 演算子は、「x BETWEEN a AND b」の書式で用い、 「x>=a AND x<=b」と同じ意味を持つ。

% (LIKE演算子) _ (LIKE演算子) ESCAPE (LIKE演算子) LIKE 演算子では、「%」文字が任意の文字列(0文字含む)にマッチし、 「_」(アンダースコア)が任意の1字にマッチする。もし、 「%」や「_」自身をマッチ対象にしたい場合は、ESCAPE 句でエスケープ文字を指定する。たとえば、 「50%で始まる文字列」を選択したいとするときに、 以下のようにすると / 文字で %_ をエスケープできる。

... WHERE somecolumn LIKE '50/%%' ESCAPE '/';

また、LIKEとGLOBは関数形式で呼ぶこともできる。たとえば、 「x LIKE pattern」は「LIKE(pattern,x)」とも表せる(GLOBも同様)。

ORDER BY 句

WHERE 句の来るべき位置に続けて ORDER BY 句で並べ換え条件を指定できる。 ORDER BY の次に並べ換え規準となる式を1つ以上記述する(カンマ区切り)。 降順指定はカラム指定のあとに DESC と指定する(昇順は ASC)。 たとえば、

... ORDER BY x DESC, y ASC;

は、並べ換えの第1規準 x で降順、第2規準 y で昇順に並べ換えを意味する。値の直後に COLLATE 指定が書ける。

• COLLATE BINARY

  memcmp() 関数の結果で並べ換え。バイト列での並べ換えとなる。

• COLLATE NOCASE

  BINARYと同じだが、ASCII 英字は大文字小文字を同一視する。

• COLLATE RTRIM

  BINARYと同じだが末尾に連続する空白は無視する。

ORDER BY 句をつけない場合の出力順は保証されれない。

LIMIT/OFFSET句

最大レコード数の指定 ORDER BY 句の来るべき位置に続けて LIMIT 句、または「LIMIT句+OFFSET 句」を書ける。LIMIT 数 で取り出しの最大レコード数を、 OFFSET 数 で先頭から抜き取るレコード数を指定する。たとえば

... LIMIT 3 OFFSET 2;

を付加すると、付加しなかった場合の最初から2行を除外し、 3つ目に該当するものから最大3行を選択する。OFFSETだけ指定することはできない。 もし「先頭から3つ除外した残り全部」を得たいときは

... LIMIT -1 OFFSET 3;

のように LIMIT に負数を指定する。負数はすべての取り出しを意味する。

データの更新

既存レコードの一部の値を更新するときは UPDATE 文を使う。

UPDATE テーブル SET カラム=値 [ 条件指定 ];

条件指定 を省略するとすべての行のカラムの値が更新される。 「カラム=値」のペアは、必要なカラムの分だけ カンマ区切りで複数指定できる。

以下の例は「ゼリー」という文字列で終わる商品の

「○○ゼリー」という商品を「○○ゼリー(改)」という名前に変え、 単価を5円増額する、というUPDATE操作を行なう。

UPDATE itemlist SET item=item||'(改)', price=price+5
WHERE item LIKE '%ゼリー';

以下のように更新される(SETで用いた || はSQLの文字列結合演算子)。

この例のように、1つのルールで複数箇所の更新を期待できる場合は UPDATE 文が必要だが、置き換え前と置き換え後が1対1対応する場合は 「INSERT OR REPLACE」(省略形 REPLACE)で更新する方がスクリプトの 流れを単純化できる。

上記 itemlist の例で、商品コード(code)をPRIMARY KEYで宣言し、 初期データを入力する。

sqlite3 itemlist.sq3
CREATE TABLE itemlist(code PRIMARY KEY, item, price);
INSERT INTO itemlist VALUES('i001', 'りんごゼリー', 80);
INSERT INTO itemlist VALUES('i002', 'みかんゼリー', 81);
INSERT INTO itemlist VALUES('i003', 'ポテトプリン', 90);
INSERT INTO itemlist VALUES('i004', 'いちごゼリー', 82);

この状態で、新しい商品リストが届き i003 の価格改定があった(以下は CSV による更新データ例)。

i003,"ポテトプリン",93

これをUPDATE文で更新する場合、以下のような流れが必要になる。

• codeがi003のものが既にあるか調べる
• あるならUPDATE文を発行
• ないならINSERT文を発行

REPLACEであれば、既存レコードの有無にかかわらず、

REPLACE INTO itemlist VALUES('i003', 'ポテトプリン', 93);

1文で済む。テーブルへの初期データの挿入も 「INSERT INTO」ではなくつねに「REPLACE INTO」で構わないケースは多々ある。

集約関数

複数の行にまたがるカラム値をまとめて1つの値にする働きを持つ関数を 集約関数 という。次のようなテーブル minitest があるとする。

6件あるが、pt(得点)の合計値を求める sum 関数で集約する。

SELECT sum(pt) FROM minitest;
52

6行が1行にまとまる。集約する単位を GROUP BY 句で決められる。 たとえば生徒(student)ごとに合計を求めたければ GROUP BY student を付加する。

SELECT student,sum(pt) FROM minitest GROUP BY student;
hanako,27
taro,25

科目ごとにまとめたければ GROUP BY subj とする。

SELECT subj,sum(pt) FROM minitest GROUP BY subj;
数学,16
英語,36

上記2例の "SELECT" 直後で指定しているように、集約関数抜きで選択できるのは GROUP BY で指定したカラムのみである。GROUP BY でのグループ化をしていないカラムを選択すると無意味な結果を返す。

SELECT student,subj,sum(pt) FROM minitest GROUP BY subj;
hanako,数学,16
hanako,英語,36
: ※結果は異なる場合がある

集約した結果に対する絞り込み条件は HAVING句 で指定できる。 たとえば上記の例で「合計点が30点以上の科目」を取り出してみる。

SELECT subj,sum(pt) FROM minitest GROUP BY subj HAVING sum(pt)>30;
英語,36

先述のカラム別名を使うと見やすくなる。

SELECT subj,sum(pt) s FROM minitest GROUP BY subj HAVING s>30;
英語,36

ちなみに WHERE 句による絞り込みは集約前に行なわれるので 次のような WHERE 指定はエラーとなる。

SELECT subj,sum(pt) FROM minitest WHERE sum(pt)>30 GROUP BY subj;
Error: misuse of aggregate: sum()

SQLite3 で利用できる集約関数は以下のとおりである。 avg() count() group_concat() max() min() sum() total()

各関数の仕様の詳細は SQL As Understood By SQLite (https://www.sqlite.org/lang_aggfunc.html) に記述がある。

表の結合

学生情報を記録した members テーブルの例に戻る。 たとえば、教員IDがF10001の教員の 専門演習を履修する学生一覧を得たい場合を考える。履修関係は members テーブルにあるから以下のようなSQLを発行すると結果が得られる。

SELECT * FROM members WHERE kid='F10001';
C110123|F10001|専門演習1
C110134|F10001|専門演習2

確かに分かるが、人間がこれを見る場合、該当者が誰なのか一瞥で分からない。 「学生IDと氏名の関係」を持つ students テーブルと結合することで氏名も分かる。

JOIN操作

これには「membersのsidとstudentsのsidが同じであるレコードを結合」 する操作(JOIN)を行なう。

SELECT * FROM members JOIN students ON members.sid=students.sid;
C110123|F10001|専門演習1|C110123|公益太郎
C110134|F10001|専門演習2|C110134|飯森花子
C110138|F10014|専門演習2|C110138|高見台一
C110140|F10014|専門演習2|C110140|緑智子
C110144|F10002|専門演習2|C110144|海原月山

結合条件は ON の後ろに指定する。 2つのテーブルどちらにも sid カラムが含まれるので、 「テーブル名.カラム名」の形式でどちらの表に含まれるカラムなのかを 明示的に指定する。

USING 句を ON の代わりに指定して、「カラム名が同じものどうしが等しい」 という条件を記述できる。上記の例の場合は以下のようになる。

SELECT * FROM members JOIN students USING(sid);
C110123|F10001|専門演習1|公益太郎
C110134|F10001|専門演習2|飯森花子
C110138|F10014|専門演習2|高見台一
C110140|F10014|専門演習2|緑智子
C110144|F10002|専門演習2|海原月山

結合条件で用いた sid は1つにまとめられる。ただし、USING は同名のカラムが「等しい」という条件でしか使えない。

テーブルへの一時的な別名付加

別名(テーブルへの) JOIN操作は頻繁に用いる。結合条件の記述を簡略化するため 結合元に一時的な短い別名を付けるとやりやすい。 別名を利用した結合は以下のような流れで行なう。

1. members テーブルを仮に t1、students テーブルを仮に t2 とおく。
2. t1のsid と t2のsid が等しいという条件で結合する。
3. ただし、得たいカラムは以下のものに限る。
   • t1のsid (学生ID)
   • t2のname (学生氏名)
   • t1のkid (教員ID)
   • t1のsemi (履修科目)

これを SQL に置き換えると以下のようになる。

SELECT t1.sid, t2.name, t1.kid, t1.semi
FROM members t1 JOIN students t2 ON t1.sid=t2.sid;
C110123|公益太郎|F10001|専門演習1
C110134|飯森花子|F10001|専門演習2
C110138|高見台一|F10014|専門演習2
C110140|緑智子|F10014|専門演習2
C110144|海原月山|F10002|専門演習2

テーブル指定の直後に別名を書くと、カラム条件の指定も別名経由となる。 これは別名を使わず以下のように書くのと同じ結果になる。

SELECT members.sid, students.name, members.kid, members.semi
FROM members JOIN students ON members.sid=students.sid;

別名を付けると余計面倒では、と感じる場合もあるが、実際に SQL を組み立てるときには、 別名にしておくことでテーブルの差し替えなどがやりやすくなるなどのメリットがある。

抽出カラムへの別名付加

SQLite では出力結果にカラム名の見出しを付けることができる。 直前の問い合わせを見出しつきで出してみる。 見出し表示はドットコマンドの .head で行なう。

.head ON
SELECT members.sid, students.name, members.kid, members.semi
FROM members JOIN students ON members.sid=students.sid;
sid|name|kid|semi
C110123|公益太郎|F10001|専門演習1
C110134|飯森花子|F10001|専門演習2
C110138|高見台一|F10014|専門演習2
C110140|緑智子|F10014|専門演習2
C110144|海原月山|F10002|専門演習2

利用者に分かりやすい見出しに変えることもできる。 抽出カラムに別名を付けるとそれが見出しにも利用される。

.head ON
SELECT members.sid 学生ID, students.name 学生氏名,
       members.kid 教員ID, members.semi 履修
FROM members JOIN students ON members.sid=students.sid;
学生ID|学生氏名|教員ID|履修
C110123|公益太郎|F10001|専門演習1
C110134|飯森花子|F10001|専門演習2
C110138|高見台一|F10014|専門演習2
C110140|緑智子|F10014|専門演習2
C110144|海原月山|F10002|専門演習2

シェルスクリプトでの利用を想定した場合、 見出し表示は出力モードがHTMLのときに非常に有用で、 Web出力時にスタイルシート(CSS)を工夫しておくことで、 高度にデザインされたものを簡単に得られる。

ビュー

JOIN を使った出力は人間にとって分かりやすいものであるため、 しばしば利用することが予想される。 いくつかのテーブルに対して なんらかの操作をして得られた生成結果としてのテーブルは、 ビュー という仮想的なテーブルとして定義できる。

上記のJOINの例のSQL文を、v_members というビューにする。 VIEW の作成は CREATE VIEW で行なう。

CREATE VIEW v_members AS
  SELECT t1.sid, t2.name, t1.kid, t1.semi
  FROM members t1 JOIN students t2 ON t1.sid=t2.sid;

定義した v_members はあたかも普通のテーブルのように振舞う。

SELECT * from v_members;
sid|name|kid|semi
C110123|公益太郎|F10001|専門演習1
C110134|飯森花子|F10001|専門演習2
C110138|高見台一|F10014|専門演習2
C110140|緑智子|F10014|専門演習2
C110144|海原月山|F10002|専門演習2

WHERE による条件指定も利用できる。 ただし SQLite におけるビューは読み取り専用テーブルで、VIEW に対する更新はできない。

SELECT * FROM v_members WHERE kid='F10001';
sid|name|kid|semi
C110123|公益太郎|F10001|専門演習1
C110134|飯森花子|F10001|専門演習2

コメント

SQLのコメント文のは1行コメント(--)とブロックコメント(/* */)が使用できる。

SELECT x		-- ハイフン2個で行末までのコメントが書ける
  /* C言語と同様の方式で
     複数行可の
     コメントも書ける。
  */
FROM tbl;

他のRDBMSでは /* */ の内部にまた /* */ ネストさせて書けるものもあるが SQLite ではネストは許されない。

練習問題: 情報をすべて含むビューの作成

この章で例に用いた gakuji データベースの表を改めて見てみよう。

• ヒント1

  3つの表の結合は「x JOIN y JOIN z」と続けて書けばよい。 結合条件はANDでつないで書けばよい。

  例として、以下のような3つの表からの結合例を示す。

  r

  results
  学生ID	科目コード	得点
  s001	110	78
  s001	220	80
  s002	110	82
  s003	441	86

  s

  sids
  学生ID	氏名
  s001	岩手盛夫
  s002	宮城迫志
  s003	福島奥松

  l

  lectcodes
  科目コード	科目名
  110	基礎もっけ論
  220	応用もっけ論
  441	基礎もっけ論

  これらから以下の表を得たい。

  学生ID	氏名	科目名	得点
  s001	岩手盛夫	基礎もっけ論	78
  s001	岩手盛夫	応用もっけ論	80
  s002	宮城迫志	基礎もっけ論	82
  s003	福島奥松	基礎もっけ論	86

  元となる3つのテーブルを左から順に r、s、l とおく。 学生IDと氏名を結び付けるには r.学生ID=s.学生ID の条件でで結合、 科目コードと科目名を結びつけるには r.科目コード=l.科目コード の条件で結合すればよい。よって、次の問い合わせ文で結果が得られる。

  SELECT r.学生ID,s.氏名,l.科目名,r.得点
  FROM results r JOIN sids s JOIN lectcodes l
       ON r.学生ID=s.学生ID AND r.科目コード=l.科目コード;
  

• ヒント2

  表の見出しの「学生ID」等は、元のテーブルのカラム名とは異なる。 この場合は抽出カラム名の直後の別名指定でカラム名を付ければよい。

練習問題: 解答例

gakuji データベースに取り込んだ3つの表は以下のとおりとなっている。

それぞれの表を t1、t2、t3 とおくと、以下の2条件で結合すればよい。

• t1.sid=t2.sid
• t1.kid=t3.kid

また、結果として得たいカラムは以下の5つである。

1. 学生ID(t1.sid)
2. 学生氏名(t2.name)
3. 所属先教員(t3.name)
4. 所属区分(t1.semi)
5. 教員email(t3.email)

カラムの別名を付けつつ結合条件を示すと以下の問い合わせ文になる。

SELECT t1.sid	学生ID,
       t2.name	学生氏名,
       t3.name	所属先教員,
       t1.semi	所属区分,
       t3.email	教員email
FROM   members t1 JOIN students t2 JOIN lecturers t3
       ON t1.sid=t2.sid AND t1.kid=t3.kid;

これをビュー定義すればよいので、解答の1つは以下のようになる。

CREATE VIEW v_all AS 
SELECT t1.sid	学生ID,
       t2.name	学生氏名,
       t3.name	所属先教員,
       t1.semi	所属区分,
       t3.email	教員email
FROM   members t1 JOIN students t2 JOIN lecturers t3
       ON t1.sid=t2.sid AND t1.kid=t3.kid;

この表からHTML出力を得るには以下のようなシェルスクリプトを作ればよい。

#!/bin/sh
db=gakuji.sq3

cat<<EOF
<!DOCTYPE html>
<html lang="ja">
<head><title>得点表</title></head>
<body>
<h1>得点表</h1>
<table border="1">
EOF

sqlite3 -header -html $db "SELECT * FROM v_all;"

cat<<EOF
</table>
</body>
</html>
EOF

このスクリプトから分かるように、sqlite3 コマンドの -header オプションは見出しを出力し、-html オプションは出力形式を HTML にする。また、HTML 出力モードでは「<table>」と「</table>」 は出力しないので、sqlite3 呼び出しの事前事後に補う必要がある。

参考文献

1. SQLite Query Language: CRATE TABLE. https://sqlite.org/lang_createtable.html
2. SQLite Query Language: CRATE VIEW. https://sqlite.org/lang_createview.html
3. Datatypes in SQLite Version 3. https://sqlite.org/datatype3.html