SQLの書き方・読み方のコツを紹介します。
基本情報試験のDB問題にも役立ちます。
内容は初心者向けです。
SQLって、プログラムを書いたことがある人にとっても、なんだかとっつきづらいものです。
C言語やJavaなどと比べると、何だか異なる感じがします。
なにより魔法のような言葉がたくさん出てきて、馴染めないです。
AVE, COUNT, GROUP BY, HAVING, INNER JOIN・・・・
ですが、2つのコツを知れば、SQLと仲良くなれます。
それは、
コツ1：SQLが4ステップで書かれていることを知る
コツ2：各命令で表がどう操作されるのか、例題で視覚的に理解する
です。
本記事ではSQLのコツを、図をたくさん使用して説明します。
コツ１：SQLの4ステップ
魔法のような言葉が並ぶSQLですが、基本的には4つのステップで書かれています。
１．表の結合
２．絞り込み
３．計算
４．並び替え
です。
この4つのステップ（パーツ）でできあがっていることを知ります。
そして各ステップの命令で、どのように表が操作されるのかイメージを身につければ、SQLが身近なものになります。
それでは、4. 並び替えから逆向きの順番で、命令によって表がどう変化するのかひとつずつ見ていきます。
コツ2：各命令で表がどう操作されるのか、例題で視覚的に理解する
以下の表を例に、各ステップの命令で、表がどう操作されるのかを見ていきます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /*テーブルを作成する*/ create table student(id int, name varchar(20), mathTest int, clubId int); create table club(clubId int, clubName varchar(20)); insert into student values(1, "赤井１郎", 80, 3); insert into student values(2, "石井２郎", 95,1); insert into student values(3, "上田３郎", 100, 2); insert into student values(4, "江川４郎", 50, 2); insert into student values(5, "小田５郎", 35, 3); insert into student values(6, "河井６郎", 65, 3); insert into student values(7, "木下７郎", 40, 1); insert into student values(8, "倉本８郎", 60, 2); insert into student values(9, "剣持９郎", 90, 2); insert into club values(1, "サッカー部"); insert into club values(2, "野球部"); insert into club values(3, "吹奏楽部"); |
並び替え
並び替えは、単純です。
1 2 3 | /*並び替えをする*/ select * from student order by mathTest desc; |
※SQLに改行を入れているのは、selectがステップ3の計算、order byがステップ4の並び替えに対応することを 分かりやすくするためです。
この結果表は、
計算
計算も単純です。
1 2 3 | /*計算をする 答えは9と68.3*/ select count(*) from student; select AVG(mathTest) from student; |
絞り込み
とっつきづらくなるのが、ここからです。
ですが、ひとつずつ確認することで、分かるようになります。
まずはWHERE文を使った絞り込みです。
1 2 3 | /*絞込み*/ select * from student where mathTest>=80; |
WHERE文では、不等号のほかに INやLIKEがよく使われます。
1 2 3 | /*絞込み*/ select id, name, clubId from student where clubId in (1,2); |
絞り込みにはWHERE文ではなく、GROUP BYを使用する方法もあります。
GROUP BYを使用すると、その列の値ごとに計算してくれます。
以下の例ですと、部活IDごとに計算です。
1 2 3 | /*絞込み*/ select clubId, count(*) from student group by clubId; |
GROUP BYからさらに絞り込むにはHAVINGを使用します。
1 2 3 4 | /*絞込み*/ select clubId, count(*) from student group by clubId having count(*)>=3; |
結合
ここから一番とっつきづらい結合に入ります。
INNER JOINは内部結合です。
基本的に内部結合では、基準にした表の行数は変化しません
1 2 3 | /*内部結合*/ select * from student inner join club on student.clubId=club.clubId; |
次に表を少し書き換えます。
剣持君のclubIdをnullにします。
1 2 | /*表を変更*/ update student set clubId=null where id=9; |
この状態で次に外部結合を見ていきます。
OUTER JOINは外部結合です。
外部結合では、結合相手がいない場合にはnullを入れてくれます。
1 2 3 | /*外部結合*/ select * from student left outer join club on student.clubId=club.clubId; |
ちなみに、このnull状態で内部結合すると、剣持君の行は消えてしまいます。
内部結合にしてしまうと、基準の表でnullの行は消えることに注意しましょう。
逆向きの外部結合です。
1 2 3 | /*外部結合*/ select * from club left outer join student on student.clubId=club.clubId; |
以上、ここまででSQLが
①表の結合⇒②絞り込み⇒③計算⇒④並び替え
でできていることを知り、各ステップの操作の実例を見ました。
これらの知識を用いて、例題を解いて、定着させます。
SQLの例題
まず「提出者の出席番号」と「宿題科目名」が記載された宿題テーブルを新たに追加します。
1 2 3 4 5 6 7 | /*新たなテーブルを追加*/ create table homework(id int, subjectName varchar(20)); insert homework values(1, "数学"); insert homework values(5, "数学"); insert homework values(2, "数学"); insert homework values(4, "数学"); insert homework values(7, "数学"); |
例題1
「サッカー部の宿題提出者人数を求めてください」
4ステップの逆から考えていきます。
今回並び替えはないので、ステップ3の提出者人数計算のときに、下のような表が欲しいです。
ステップ2で絞り込む前は、きっと下のような表です。
ステップ1でこの表を作ることを考えます。
まずはステップ1で内部結合です。
1 2 | select * from student inner join homework on student.id=homework.id; |
さらにステップ２の絞り込みをかけます。
1 2 3 | select * from student inner join homework on student.id=homework.id where student.clubId =1; |
最後に計算します。
1 2 3 | select count(*) from student inner join homework on student.id=homework.id where student.clubId =1; |
このように「結合、絞り込み、計算、並び替え」を一度逆から考えて、組み立てます。
例題2
「各部活ごとに宿題提出者人数を求めてください」
部活ごとなので、GROUP BYを使用します。
1 2 3 4 | /*部活IDごとに宿題提出人数を求める*/ select clubId, count(*) from student inner join homework on student.id=homework.id group by clubId; |
例題3
「各部活ごとに宿題提出率を求めてください」
提出率なので、ステップ3の提出率計算のときには下のような表が欲しいです
そこで、nullも欲しいので、外部結合を使います。
1 2 3 | /*部活IDごとに宿題提出率を求める*/ select * from student left outer join homework on student.id=homework.id; |
提出率を求めます。求める列名を宿題提出率と命名します。
1 2 3 4 5 | select clubId, (count(homework.subjectName)/count(student.id)*100) as "宿題提出率" from student left outer join homework on student.id=homework.id group by student.clubId; |
例題4
「宿題テーブルに国語も追加されました。国語の宿題の未提出者を求めてください。」
最後の例題になります。
1 2 3 4 5 6 | /*宿題テーブルに他の科目を追加します*/ insert homework values(1, "国語"); insert homework values(9, "国語"); insert homework values(2, "国語"); insert homework values(4, "国語"); insert homework values(6, "国語"); |
この状態で内部結合すると、どうなるのか見てみましょう。
1 2 3 | /*内部結合*/ select * from student inner join homework on student.id=homework.id; |
外部結合すると、どうなるのか見てみましょう。
1 2 3 | /*外部結合*/ select * from student left outer join homework on student.id=homework.id; |
先ほどの数学だけの場合とは異なり、nullは両方の宿題を出していない人です・・・
そこで次のように工夫します。
今回は国語に絞って結合させたいので、まず次のように書いて、結合させます。
1 2 3 | /*外部結合*/ select * from student left outer join homework on (student.id=homework.id and homework.subjectName="国語"); |
さらに絞り込みます。
1 2 3 | select student.id, student.name from student left outer join homework on (student.id=homework.id and homework.subjectName="国語") where homework.subjectName is null; |
これで、国語の未提出者を求めることができました。
まとめ
以上、SQLの読み方・書き方のコツでした。
コツ１：SQLは ①結合、②絞り込み、③計算、④並び替え の4ステップ
コツ２：各命令での操作実行結果を例題で理解する
実際にMYSQLなどを自宅のPCにインストールして、手を動かして勉強するのが一番です。
MYSQLの5.7系は相性が悪いようで、5.6系を私は入れています。
MYSQLを使う場合は、HeidiSQLというクライアントソフトがおすすめです。
テーブルの中身やSQL結果をすぐに表形式で見れます。
本としては「すらすらと手が動くようになる SQL書き方ドリル」がおすすめです。
以上、SQLの書き方・読み方のコツ（初級編）でした。
