CMU Database Systems をひたすら追っていく ~02 Advanced SQL~ - それが僕には楽しかったんです。

はじめに
今回の動画
- 余談
Advanced SQL
おわりに

この記事は「けんつの１人 DBMS アドベントカレンダー Advent Calendar 2019 - Adventar 」 2日目の記事です。

はじめに

どうも、最近なになになけんつです。という始まりをあと 24 回もやらないといけないのかと思うとネタ切れが心配になるけんつです。
ちなみに最近みて面白いなと思った映画は「ローン・サバイバー」です。
例によって CMU Database Systems を進めていきます。今回は「Advanced SQL」です。

今回の動画

www.youtube.com

余談

この講義、現在進行形で2019年版が展開されているみたい。
この記事を書いている時(2019/11/04)はまだ全部出ていないので 2018 版をもとに書いていく。

Advanced SQL

前回の記事でもリレーショナルモデルと関連して SQL について少し触れたが今回はそれをもっと掘り下げていく。

SQL History

まず SQL とは Structed Query Language の略である。
リレーショナルモデルが提唱されたあと Oracle に採用され、 IBM には「SEQUEL」という SQL が存在した。

IBM はその後、 DB2 を 1983 年にリリースした。

更にその後、 1986 年に ANSI によって規格化され、 1987 年には ISO によって SQL として規格化された。
前回の記事でも述べたように一般に MySQL や PostgreSQL で使用できる SQL は根本的には同じだが数多くの独自構文が存在する理由として、この規格以外の部分はベンダー依存となるためである。

この ANSI/ISO にもいくつかのバージョンが存在し*1

SQL:1999 で RDBMS のための完全なクエリ言語となることを目標とした上に規格化されその後も
SQL:2003, SQL:2008, SQL:2011, SQL:2016 と続いてきた。

現状、ほとんどの RDBMS は最低でも SQL:1992 を採用している。*2

準備

今回は MySQL 8.0.16 に講義と同様のテーブルを作成する。

create table student(sid int primary key auto_increment, name varchar(255), login varchar(255),age int, gpa double);
create table course(cid varchar(255), name varchar(255));
create table enrolled(sid int, cid varchar(255), grade varchar(2))

本来は外部キー制約だとか Not Null とかを入れるべきだろうけど今回は入れていない。
データも入れて次のようにする。|

MySQL [cmu]> select * from student;
+-----+--------+------------+------+------+
| sid | name   | login      | age  | gpa  |
+-----+--------+------------+------+------+
|   1 | Kanye  | kayne@cs   |   39 |    4 |
|   2 | Bieber | jbieber@cs |   22 |  3.9 |
|   3 | Tupac  | shakur@cs  |   26 |  3.5 |
+-----+--------+------------+------+------+
3 rows in set (0.00 sec)

MySQL [cmu]> select * from enrolled;
+------+--------+-------+
| sid  | cid    | grade |
+------+--------+-------+
|    1 | 15-445 | C     |
|    2 | 15-721 | A     |
|    2 | 15-826 | B     |
|    3 | 15-445 | B     |
|    1 | 15-721 | C     |
+------+--------+-------+
5 rows in set (0.00 sec)

MySQL [cmu]> select * from course;
+--------+------------------------------+
| cid    | name                         |
+--------+------------------------------+
| 15-445 | Database Systems             |
| 15-721 | Advanced Database Systems    |
| 15-826 | Data Mining                  |
| 15-823 | Advanced Topics in Databases |
+--------+------------------------------+
4 rows in set (0.00 sec)

集計

テーブルに含まれる何らかの値を集計する場合は基本的に SELECT を使って出力することが出来る。
さらに集計関数が容易されているはずなのでそれらも使える。

AVG(col): 指定した列の平均を算出する
MIN(col): 最小値
MAX(col): 最大値
SUM(col): 合計値
COUNT(col): その行の総数

例)
login の値が @cs で終わるものの総数をカウントする。

 select count(*) from student where login like '%@cs';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

例)
さらに学生の平均GPAを算出する。

SELECT AVG(gpa), COUNT(sid) FROM student WHERE login LIKE '%@cs';
+--------------------+------------+
| AVG(gpa)           | COUNT(sid) |
+--------------------+------------+
| 3.8000000000000003 |          3 |
+--------------------+------------+
1 row in set (0.04 sec)

今回のテーブルには、列によって重複があるような大きめのテーブルは無いので実例は紹介できないが
重複は DISTINCT でまとめることができる。

文字列操作

前提として SQL 標準規格では文字列は大文字小文字の区別があり、シングルクォートで囲まれたものとなっている。*3

集計の章でも登場しているが Like 句にはパターンマッチを利用することができる。*4
また文字列操作には文字列そのものを変更などできる文字列関数も利用することができる。*5

パターンマッチでは「%」をワイルドカードの様に使える。実際さっき挙げた例では @cs で終わる login カラムの検索に "%@cs" を使っている。
また別の例として例えば学生の名前が丁度５文字の人をカウントする場合、つまり任意の１文字をマッチさせたい場合は「_」を使う。
5文字ジャストの場合は「_ _ _ _ _」とアンダースコアを５つ並べる。

出力のリダイレクト

SQLは Linux のターミナルで出力をリダイレクトするのと同様に出力をリダイレクトすることが出来る。

MySQL [cmu]> insert into stdId (select sid from student);
Query OK, 3 rows affected (0.26 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL [cmu]> select * from stdId;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

出力制御

これはよくある ORDER BY, DESC, ASC のようなソート系と LIMIT のような取得の上限を決めるもの。
例) enrolled テーブルから cid = "15-721" のものを取り出して grade を基準に降順で並べる。*6

SELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY grade DESC;

例) enrolled テーブルの上から 3 件を取り出す。

SELECT * FROM enrolled LIMIT 3;
+------+--------+-------+
| sid  | cid    | grade |
+------+--------+-------+
|    1 | 15-445 | C     |
|    2 | 15-721 | A     |
|    2 | 15-826 | B     |
+------+--------+-------+

ネストしたクエリ

これはサブクエリなので省略。

Common Table Expressions

CTE は複雑なクエリを記述するために存在していてサブクエリなどの代替とすることができる。
実行されるときに一時的なテーブルを作成すると考えることができる。(そうなのか？)

ちなみにこれは MySQL 8.0 から導入されたのでそれ未満のバージョンでは使えない。

MySQL8.0 の共通テーブル式（CTE）を使ってみよう | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信

いわゆる WITH 句など。

次のような構文をとる。(MySQL 8.0 をそこまで個人以外で使ったことがないので馴染みがない)

WITH RECURSIVE cteSource (counter) AS (
(SELECT 1)
UNION
(SELECT counter + 1 FROM cteSource
WHERE counter < 10)
)
SELECT * FROM cteSource;

おわりに

めちゃくちゃ駆け足になってしまったが、割と知っていることが多かったのでざっと紹介するだけにした。
CTE なんかはあまりつかったことが無いので別途ブログにまとめたい。

で、なんでこんなに途中から駆け足になったかというと書いている途中で過ぎの Database Storage を見ていたら
めちゃくちゃ面白くて早くまとめたくなったから。

というわけで次から Database Storage の回になる。
それから先はバッファだったりハッシュテーブルだったりと面白いことが続く。はず。

これで SQL は終わり。

*1:SQL - Wikipedia

*2:Comparison of different SQL implementations

*3:MySQL などではダブルクオートも使える。あくまでも ANSI/ISO SQL の話

*4:MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.3.4.7 パターンマッチング

*5:MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数

*6:昇順の場合は ASC