SQLite3を用いたSQL入門（その3）

はじめに_

以下を実行していることを前提とする。

本ページでのLinux/Unixコマンドの表記方法_

本ページではターミナル上で入力する命令（Linux/Unixコマンド、あるいはコマンドという）をパーセント記号（％）の後ろに表記する。たとえば以下のように表記する。

% Linux/Unixコマンド

Linux/Unixコマンドの一つであるlsを表記する場合は以下のようにあらわす。これはターミナル上でlsという文字列を入力し、そのごEnterキーを押すという意味である。

% ls

また、必要に応じてLinux/Unixコマンドを実行した際の出力結果も例として示す。出力結果は冒頭にパーセント記号がついていない文字列である。以下の表記例は ls -1 というコマンドを実行した結果としてgotohという文字列が表示されたことを表す。

% ls -1
gotoh

これとは別に、SQLite3中で入力するコマンドは「>」に並べて以下のように表現している。

> SELECT * FROM goods;

トラブルシューティング_

SQLite3を用いたSQLの高度な問合せ（続）_

演習の準備_

先週の作業ディレクトリとデータベースファイルが存在するか確かめる。存在する場合は、それをそのまま使う。

% cd

% ls IntroSQL/
create_table.sql  edu.db

% cd IntroSQL
% sqlite3 edu.db

先週のディレクトリとデータベースファイルが存在しない場合は作業ディレクトリの作成とデータベースのダウンロードを行う。

% cd
% mkdir -p IntroSQL
% cd IntroSQL

% which wget
/usr/bin/wget

% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/edu.db
% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/create_table.sql

% ls
create_table.sql  edu.db

% sqlite3 edu.db

副問合せ（SELECT文の入れ子）_

SELECT文中にSELECT文を入れ子にすることができる。このとき、入れ子のSELECT文を副問合せ、外側のSELECT文を主問合せという。

副問合せを用いたSQL文では、まず、副問合せのSELECT文が実行され、その結果を用いて主問合せの文が実行される。主問合せに連携するための演算子として、比較演算子、INおよびNOT IN演算子、EXISTSおよびNOT EXISTS演算子、ANY（SOME）演算子、そして、ALL演算子がある。

比較演算子_

全学生の修得単位数の平均値よりも多く単位を修得している学生IDを副問合せを用いて取得する。以下のSQL文を実行する。

sqlite> SELECT sid, unit FROM credits; -- 学生IDと修得単位数を列挙する。
501|48
501|40
501|38
601|39
701|37
702|45

sqlite> SELECT avg(unit) FROM credits; -- avg()は平均値を求める集約関数。後ほど説明
41.1666666666667

sqlite> SELECT sid, unit FROM credits
   ...> WHERE unit >= (SELECT avg(unit) FROM credits); -- 全学生の修得単位数の平均値よりも多く単位を修得している学生ID
501|48
702|45

比較演算子は、＝、<、>、>=、=<などがある。

INおよびNOT IN演算子_

副問合せの結果に検討対象の属性が含まれているかどうかを判定するためにIN演算子を用いる。含まれていないことを判定するためにはNOT IN演算子を用いる。

以下の例は、Web Designを履修している学生の学生ID（sid）と名前（sname）を表示するSQL文である。

sqlite> SELECT sid FROM classes WHERE cname = 'Web Design'; -- Web Designを履修している学生一覧
601
603
702

sqlite> SELECT sid, sname FROM students; -- 学生IDと名前の一覧
501|Keitha Teague
601|Marybeth Boros
602|Shasta Pepper
701|Peg Massingill
702|Eugenio Dengler

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid IN (SELECT sid FROM classes WHERE cname = 'Web Design');
601|Marybeth Boros
702|Eugenio Dengler

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid NOT IN (SELECT sid FROM classes WHERE cname = 'Web Design');
501|Keitha Teague
602|Shasta Pepper
701|Peg Massingill

INとNOT IN演算子は副問合せをつなぐためでなく、通常のSELECT文でも利用できる。

sqlite> SELECT * FROM students;
501|Keitha Teague|Urawa High School
601|Marybeth Boros|Warabi High School
602|Shasta Pepper|Saitama Sakae High School
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

sqlite> SELECT * FROM students WHERE highschool IN ('Urawa High School', 'Johoku High School');
501|Keitha Teague|Urawa High School
702|Eugenio Dengler|Johoku High School

単位数が４、６、８の科目名を降順で列挙することもできる。

sqlite> SELECT cname, unit FROM classes;
Graduation Research|8
Web Design|2
Image Processing|4
Computer Graphics|2
Database|6
Web Design|2
Information Retrieval|2
Programming Lang Java|4
Programming Lang Java|4
Web Design|2

sqlite> SELECT DISTINCT cname, unit FROM classes
   ...> WHERE unit IN (4, 6, 8) ORDER BY unit DESC;
Graduation Research|8
Database|6
Image Processing|4
Programming Lang Java|4

EXISTSおよびNOT EXISTS演算子_

EXISTS演算子は副問合せの結果が空かどうかを判定する。空でない（1行以上ある）ならば真（true）、そうでなければ偽（false）となる。NOT EXISTSはEXISTSの反対の値をとる。

学生テーブル（students）と履修テーブル（classes）の双方に登場する学生の学生IDと名前を列挙する。

sqlite> SELECT * FROM students AS S, classes AS R WHERE S.sid = R.sid; -- 内部結合
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Web Design|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

sqlite> SELECT sid, sname FROM students AS S
   ...> WHERE EXISTS (SELECT * FROM classes AS R WHERE S.sid = R.sid);
501|Keitha Teague
601|Marybeth Boros
701|Peg Massingill
702|Eugenio Dengler

sqlite> SELECT sid, sname FROM students AS S
   ...> WHERE NOT EXISTS (SELECT * FROM classes AS R WHERE S.sid = R.sid);
602|Shasta Pepper

ANY（SOME）演算子_

副問合せの結果を用いた条件式のうち、１つでも条件に当てはまるものがあれば真（true）を返す演算子、SOME演算子はANY演算子と同じ働きの演算子である。

以下の例は履修（classes）に存在する学生ID（sid）について学生IDと名前(sname）を列挙するSQL文である。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid = ANY (SELECT sid FROM classes);
Error: near "SELECT": syntax error

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid = SOME (SELECT sid FROM classes); -- 上と同義
Error: near "SELECT": syntax error

SQLite3においてはANY演算子およびSOME演算子は使用できない。今回の例はIN演算子を用いることで実現できる。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid IN (SELECT sid FROM classes);
501|Keitha Teague
601|Marybeth Boros
701|Peg Massingill
702|Eugenio Dengler

ALL演算子_

ALL演算子はANY演算子とは異なり、副問合せの結果すべてが条件式と一致するときに真（true）となる。

以下の例は履修（classes）に含まれない学生ID（sid）について学生IDと名前(sname）を列挙するSQL文である。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid != ALL (SELECT sid FROM classes);
Error: near "ALL": syntax error

SQLite3においてはALL演算子は使用できない。今回の例はNOT IN演算子を用いることで実現できる。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid NOT IN (SELECT sid FROM classes);
602|Shasta Pepper

FROM句での副問合せ_

FROM句では複数のテーブルを列挙することができる。このテーブル列挙の代わりに副問合せの結果を用いることもできる。

まず、修得単位数（creditsのunitの値）が44単位以上の行を列挙するSELECT文を考える。

sqlite> SELECT * FROM credits WHERE unit >= 44;
501|1|48
702|1|45

このSELECT文を副問合せとしてFROM句に埋め込む。これは修得単位数が44単位以上の学生IDと氏名を列挙するSELECT文である。

sqlite> SELECT DISTINCT S.sid, sname, degree, unit
   ...> FROM students AS S,
   ...> (SELECT sid, degree, unit FROM credits WHERE unit >= 44) AS VTB
   ...> WHERE S.sid = VTB.sid;
501|Keitha Teague|1|48
702|Eugenio Dengler|1|45

また、SELECT文の列の列挙部分（「SELECT 列の列挙 FROM テーブルの列挙」)に副問合せを用いることもできる。

先ほどの例と同様に修得単位数が44単位以上の学生とその名前を列挙する。

sqlite> SELECT sid, degree, unit, (SELECT sname FROM students AS S
   ...> WHERE S.sid = C.sid) FROM credits AS C WHERE unit >= 44; 
501|1|48|Keitha Teague
702|1|45|Eugenio Dengler

副問合せの練習1_

学生ID（sid）が701と702の学生が両方とも履修している科目名（cname）を抽出するSELECT文をANY演算子を用いて作ると以下のようになる。

sqlite> SELECT DISTINCT cname FROM classes  WHERE sid = ANY ('701', '702'); 
Error: no such function: ANY

これをIN演算子を用いて書き直せ。

副問合せの練習2_

学生ID（sid）が701の学生が履修しているが、702の学生は履修していない科目を抽出するSELECT文を考える。exceptを用いたSELECT文は以下のようになる。

sqlite> SELECT sid, cname FROM classes WHERE sid = '701';
701|Information Retrieval
701|Programming Lang Java

sqlite> SELECT sid, cname FROM classes WHERE sid = '702';
702|Programming Lang Java
702|Web Design

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> EXCEPT
   ...> SELECT cname FROM classes WHERE sid = '702';
Information Retrieval

ALL演算子を用いると以下のようになる。

sqlite> SELECT cname FROM classes WHERE sid = '701' AND cname != ALL (SELECT cname FROM classes WHERE sid = '702');
Error: near "ALL": syntax error

IN演算子あるいはNOT IN演算子を用いて同等の働きをするSQL文を作れ。

集約関数_

DBMSの製品の多くは組み込み関数を提供している。SELECT文においてGROUP BY句やHAVING句と一緒に組み込み関数を用いる場合が多い。

COUNT関数_

列名を引数としてその列に含まれる行数をカウントする。一般に COUNT(*)はテーブルに含まれる行数をカウントするために使われる。

classesテーブルの行数をカウントする場合は以下のように入力する。

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

sqlite> SELECT COUNT(*) FROM classes;
10

「COUNT(*) AS 略称」という表記で名前をつけることができる。

sqlite> SELECT COUNT(*) AS num_of_reg FROM classes;
10

classesには科目ごとにどの学生が履修しているのかの情報が格納されている。一般に学生は複数の科目を履修する。このため、履修している学生のユニークな数を得たい場合はDISTINCT句を用いてカウントすることができる。

sqlite> SELECT COUNT(DISTINCT sid) AS num_of_reg_students FROM classes;
5

max, min関数_

列に含まれる最大値と最小値を得るための関数がMAX関数とMIN関数である。

creditsテーブルから修得単位数が最大の学生および最小の学生を列挙する。

sqlite> SELECT sid, MAX(unit) FROM credits;
501|48

sqlite> SELECT sid, MIN(unit) FROM credits;
701|37

AVG, STDDEV, VARIANCE関数_

統計処理を行う際に平均値、標準偏差、分散を用いることが多い。平均値はAVG関数で、標準偏差はSTDDEV関数で、分散はVARIANCE関数で得ることができる。

sqlite> SELECT AVG(unit) FROM credits;
41.1666666666667

sqlite> SELECT STDDEV(unit) FROM credits;
Error: no such function: STDDEV

sqlite> SELECT VARIANCE(unit) FROM credits;
Error: no such function: VARIANCE

SQLite3ではSTDDEV関数、VARIANCE関数は提供されていない。

GROUP BY句とHAVING句_

GROUP BY句はテーブルの特定の列で行をグループ化し、個々のグループに対して集約関数を適用するときに使用する。以下の例は科目名ごとに履修学生の数を数えるSQL文である。

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

sqlite> SELECT cname,COUNT(*) FROM classes GROUP BY cname;
Computer Graphics|1
Database|1
Graduation Research|1
Image Processing|1
Information Retrieval|1
Programming Lang Java|2
Web Design|3

HAVING句は集約された値に対して条件を付与するときに用いる。たとえば、履修登録者が2名以上の科目だけを列挙したい場合は以下のように入力する。

sqlite> SELECT cname,COUNT(*) FROM classes GROUP BY cname;
Computer Graphics|1
Database|1
Graduation Research|1
Image Processing|1
Information Retrieval|1
Programming Lang Java|2
Web Design|3

sqlite> SELECT cname,COUNT(*) FROM classes
   ...> GROUP BY cname HAVING COUNT(*) >= 2;
Programming Lang Java|2
Web Design|3

sqlite> SELECT sid, sum(unit) FROM credits
   ...> GROUP BY sid HAVING sum(unit) >= 80;
501|126

creditsテーブルには学年ごとの総修得単位数が記載されているため、creditsテーブルに登録されている学生IDごとの行数をつかうことで現在の学年を得ることができる。行数＋１が現在の学年となる。

sqlite> SELECT sid, degree FROM credits;
501|1
501|2
501|3
601|1
701|1
702|1

sqlite> SELECT sid, count(sid)+1 AS current_degree FROM credits GROUP BY sid;
501|4
601|2
701|2
702|2

ビュー（再）_

ビューの使い方_

前回紹介したビューは今回紹介した複雑な問合せを繰り返し実行するときに非常に役立つ。

たとえば、年間の修得単位数が40単位以上の学生だけを列挙するビュー students_40を作成してみる。

sqlite> CREATE VIEW students_40
   ...> AS SELECT S.sid, sname, highschool, degree, unit FROM students AS S,
   ...> credits AS C
   ...> WHERE S.sid = C.sid and unit >= 40;

sqlite> .tables
classes      credits      students     students_40

sqlite> SELECT * FROM students_40;
501|Keitha Teague|Urawa High School|1|48
501|Keitha Teague|Urawa High School|2|40
702|Eugenio Dengler|Johoku High School|1|45

作成したビューを用いて結合演算を行うこともできる。年間の修得単位数が40以上の学生が履修している科目名を列挙する。

sqlite> SELECT DISTINCT cname FROM students_40 AS S, classes AS R
   ...> WHERE S.sid = R.sid;
Graduation Research
Programming Lang Java
Web Design

ビューに対して集約関数を適用することもできる。students_40に含まれる学生と全学生の年間平均修得単位数の差を表示する。

sqlite> SELECT AVG(S.unit), AVG(C.unit), AVG(S.unit)-AVG(C.unit)
   ...> FROM students_40 AS S, credits AS C;
44.3333333333333|41.1666666666667|3.16666666666667

集約関数を含むビュー_

ビューの定義に集約関数を含めることもできる。以下の例は学生の学年と現時点での積算修得単位数を列挙するビューである。

sqlite> CREATE VIEW total_credits 
   ...> AS SELECT sid, count(sid)+1 AS current_degree, sum(unit) AS total_unit 
   ...> FROM credits GROUP BY sid;

sqlite> .tables 
classes        credits        students       students_40    total_credits

sqlite> SELECT * FROM total_credits;
501|4|126
601|2|39
701|2|37
702|2|45

作成したビューを用いて現時点で80単位以上修得している学生を列挙する。

sqlite> SELECT * FROM total_credits WHERE total_unit >= 80;
501|4|126

更新可能ビュー_

ビューはテーブルと同じように使うことができるが、格納されている値の変更（INSERT, UPDATE, DELETE）については特定の条件を満たしたビューのみ実行できる。

その条件は以下のとおり

  • ビューの列と実テーブルの列とが1対1対応になっている
  • ビューを構成する実テーブルにおいて、実テーブルには存在するがビューに現れない列に対してNOT NULL制約がかけられていない、あるいはデフォルト値が定められている（ビュー経由の更新ではNULLが挿入されるため）

別の言い方をすると以下の条件のうちどれかを満たしているビューは更新可能ビューではない。

  • 集約関数、GROUP BY句、HAVING句を使用している
  • DISTINCT、UNIONなどの集合演算を使用している
  • 派生列（たとえば COUNT(sid)+1など）を使用している

先の例で作ったstudents_40は更新可能ビューである。

sqlite> .schema students_40
CREATE VIEW students_40
AS SELECT S.sid, sname, highschool, degree, unit FROM students AS S,
credits AS C
WHERE S.sid = C.sid and unit >= 40
/* students_40(sid,sname,highschool,degree,unit) */;

sqlite> SELECT * FROM students_40;
501|Keitha Teague|Urawa High School|1|48
501|Keitha Teague|Urawa High School|2|40
702|Eugenio Dengler|Johoku High School|1|45

更新してみる。students_40に登録されている学生の履修単位数を1増やす。

sqlite> UPDATE students_40 SET unit = unit + 1;
Error: cannot modify students_40 because it is a view

ただし、SQLite3ではビューからの更新に対応していない。

ビューの練習1_

studentsテーブルから学生ID（sid）が7で始まる学生だけを抜き出したビュー students7を作成せよ。なお、ある列においてある文字列を含む行を抜き出す条件式は以下のように記述する。

  • 特定の文字列から始まる場合： 「列名 like "文字列%"」
  • 特定の文字列で終わる場合： 「列名 like "%文字列"」
  • 特定の文字列が含まれる場合：「列名 like "%文字列%"」
  • 解答例

ビューの練習2_

以下の条件を満たすビューを作成せよ

  • ビュー名が current_students
  • 列が学生ID (sid)、学生名 (sname)、現在の学年 (degree)、出身高校 (highschool)

ヒント

  • 現在の学年の求め方は今日のページ内にある
  • 解答例

発展：データサイエンス100本ノック_

以下はBigSur上で構築する方法だが、Montereyでもほぼ同様にできる。データサイエンス100本ノックでは、多くのSQLコマンドの練習ができる。

リンク_

戻る_