LAMPのお勉強日記 (12) - reponの忘備録

LAMP環境のお勉強日記

承前

サバイバルのための人体実験を公開すること - My Life Between Silicon Valley and Japan

上記エントリに呼応して、自分を「人体実験」することにした。

目的

自分が何をやったのか記録する
自分が何を出来るのか公開する

経過

まず、MySQLの勉強をする（その後、PHPとの連携の勉強に移る予定）。

XAMPPにより、コマンドプロンプトで作業する。

リレーショナルデータベースとは（再掲）

はじめに

MySQLモニタに入る。
コマンドプロンプトで

>mysql -u root -p
Enter password:[パスワード]
mysql>

使用するデータベースを指定する

mysql>use db1

今日学んだこと

外部結合

ex)
mysql>select * from tb;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| a103 |  101 |    4 |
| a102 |   54 |    5 |
| a104 |  181 |    4 |
| a101 |  184 |    4 |
| a103 |   17 |    5 |
| a101 |  300 |    5 |
| a102 |  205 |    6 |
| a104 |   93 |    5 |
| a103 |   12 |    6 |
| a107 |   87 |    6 |
+------+------+------+
mysql>select * from tb1;
+------+-----------+------+
| bang | nama      | tosi |
+------+-----------+------+
| a101 | sato      |   40 |
| a102 | takahashi |   28 |
| a103 | nakagawa  |   20 |
| a104 | watanabe  |   23 |
| a105 | nishizawa |   35 |
+------+-----------+------+
mysql>select x.bang,y.bang,x.uria from tb as x join tb1 as y on x.bang=y.bang;
+------+------+------+
| bang | bang | uria |
+------+------+------+
| a103 | a103 |  101 |
| a102 | a102 |   54 |
| a104 | a104 |  181 |
| a101 | a101 |  184 |
| a103 | a103 |   17 |
| a101 | a101 |  300 |
| a102 | a102 |  205 |
| a104 | a104 |   93 |
| a103 | a103 |   12 |
+------+------+------+

データ「a107」が消えている。

「内部結合」では、複数のテーブルに一致していないデータは取り出されない。

すべてのデータを抽出するためには「外部結合」を使う。

「外部結合」では、一致していなくても一方のテーブルのレコードはすべて取り出す

外部結合の種類

左外部結合　「left join」
右外部結合　「right join」

ex)
mysql>select x.bang,y.nama from tb as x left join tb1 as y on x.bang=y.bang;
+------+-----------+
| bang | nama      |
+------+-----------+
| a103 | nakagawa  |
| a102 | takahashi |
| a104 | watanabe  |
| a101 | sato      |
| a103 | nakagawa  |
| a101 | sato      |
| a102 | takahashi |
| a104 | watanabe  |
| a103 | nakagawa  |
| a107 | NULL      |
+------+-----------+

「a107」のデータが抽出された。

mysql>select x.bang,y.nama from tb as x right join tb1 as y on x.bang=y.bang;
+------+-----------+
| bang | nama      |
+------+-----------+
| a101 | sato      |
| a101 | sato      |
| a102 | takahashi |
| a102 | takahashi |
| a103 | nakagawa  |
| a103 | nakagawa  |
| a103 | nakagawa  |
| a104 | watanabe  |
| a104 | watanabe  |
| NULL | nishizawa |
+------+-----------+

データ「nishizawa」さんに対応するカラム「bang」は無いので、「null」となっています。

自己結合

mysql>select * from tb1 as a join tb1 as b;
※必ずエイリアスを使う
+------+-----------+------+------+-----------+------+
| bang | nama      | tosi | bang | nama      | tosi |
+------+-----------+------+------+-----------+------+
| a101 | sato      |   40 | a101 | sato      |   40 |
| a102 | takahashi |   28 | a101 | sato      |   40 |
| a103 | nakagawa  |   20 | a101 | sato      |   40 |
| a104 | watanabe  |   23 | a101 | sato      |   40 |
| a105 | nishizawa |   35 | a101 | sato      |   40 |
| a101 | sato      |   40 | a102 | takahashi |   28 |
| a102 | takahashi |   28 | a102 | takahashi |   28 |
| a103 | nakagawa  |   20 | a102 | takahashi |   28 |
| a104 | watanabe  |   23 | a102 | takahashi |   28 |
| a105 | nishizawa |   35 | a102 | takahashi |   28 |
| a101 | sato      |   40 | a103 | nakagawa  |   20 |
| a102 | takahashi |   28 | a103 | nakagawa  |   20 |
| a103 | nakagawa  |   20 | a103 | nakagawa  |   20 |
| a104 | watanabe  |   23 | a103 | nakagawa  |   20 |
| a105 | nishizawa |   35 | a103 | nakagawa  |   20 |
| a101 | sato      |   40 | a104 | watanabe  |   23 |
| a102 | takahashi |   28 | a104 | watanabe  |   23 |
| a103 | nakagawa  |   20 | a104 | watanabe  |   23 |
| a104 | watanabe  |   23 | a104 | watanabe  |   23 |
| a105 | nishizawa |   35 | a104 | watanabe  |   23 |
| a101 | sato      |   40 | a105 | nishizawa |   35 |
| a102 | takahashi |   28 | a105 | nishizawa |   35 |
| a103 | nakagawa  |   20 | a105 | nishizawa |   35 |
| a104 | watanabe  |   23 | a105 | nishizawa |   35 |
| a105 | nishizawa |   35 | a105 | nishizawa |   35 |
+------+-----------+------+------+-----------+------+

何のために行うのか？

順位付け

ex)
mysql>select a.nama,a.tosi,count(*) from tb1 as a join tb1 as b where a.tosi<=b.tosi group by a.bang;
+-----------+------+----------+
| nama      | tosi | count(*) |
+-----------+------+----------+
| sato      |   40 |        1 |
| takahashi |   28 |        3 |
| nakagawa  |   20 |        5 |
| watanabe  |   23 |        4 |
| nishizawa |   35 |        2 |
+-----------+------+----------+

サブクエリ

「where 〜 in」

例1）値を返すサブクエリ

「uria」の最大値を返すクエリ

mysql>select * from tb where uria=max(uria);
ERROR 1111 (HY000): Invalid use of group function

エラーが出てしまう。

ex)
mysql>select * from tb where uria in (select max(uria) from tb);
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| a101 |  300 |    5 |
+------+------+------+

例2）平均以上のレコードを抽出するクエリ

ex)
mysql>select * from tb1 where tosi>=(select avg(tosi) from tb1);
+------+-----------+------+
| bang | nama      | tosi |
+------+-----------+------+
| a101 | sato      |   40 |
| a105 | nishizawa |   35 |
+------+-----------+------+

例3）一つのテーブルから抽出したデータに該当する別テーブルのカラムを抽出する

ex)
mysql>select * from tb1 where bang in (select bang from tb where uria>=200);
+------+-----------+------+
| bang | nama      | tosi |
+------+-----------+------+
| a101 | sato      |   40 |
| a102 | takahashi |   28 |
+------+-----------+------+

今回はここまで。

次回は「exists」から。