こんばんは。一週間ぶりの投稿、itoshinです！
習慣化に抵抗する反動に流され、一週間・・・。外気温が下がり、体調を崩した辺りからペースを落としてしまった感じです。
講座で学んだ範囲のアウトプットの他、少し独学でApache,PHP,Visual Studio Codeのいずれかを触ってみようかと考えてはいたのですが、
考え事ばかりが先行し過ぎて、習慣化したいルーティンが疎かになってしまうのはいただけないですね。
一日の始めか終わりに、ちゃんと振り返りとタスクの優先順位を決める時間を作ろうと思います。
（年末年始は３週間弱あるので、自分合宿的なものを計画したい）
さて、今日もMySQLアウトプットしていきましょう。
前回はレコードの並べ替えと集計を、条件指定や関数を用いて視認性のある表示を目指しました。
今回は
- 異なるテーブル同士のレコードを統合して表示させる内部（外部）結合
- 二つの命令を一つの命令としてまとめられるサブクエリ
- 大量のレコードから目的のものを効率的に探し出せるインデックス機能
- 複数の処理を行う際、更新処理によるレコードの矛盾を防ぐトランザクション機能
に触れていきます！よろしくお願いします＾＾
内部結合
少し前にですが、「主（プライマリー）キー」と「外部キー」についてお話したかと思います。
※遡る必要はないと思いますが、気になる方は{Level8}の記事をご覧ください。
あの関連付けが本日語る内部（外部）結合で効力を発揮するわけです。
最初のまとめで書いたように、
内部結合とは、『異なるテーブル同士のレコードを統合して表示させる結合』です。
後述する外部結合とは表示できるレコードの範囲が異なるものですので、その違いにも触れていきます。
簡単に言えば、
内部結合は主キーと外部キーが対応しているレコードのみを結合して表示するものです。
対して外部結合はどちらかのテーブルのキーが対応していないレコードも結合して表示するというもの。
実際に見てもらう方が理解しやすいかもですね。
select schedule.subject, schedule.pdate, schedule.ptime,
category.cname, schedule.memo from schedule
inner join category on schedule.cid = category.cid
where schedule.uid = 'yyamada';
[scheduleテーブルからsubject, pdate, ptime, memo, cname(これはcategoryテーブルのフィールド)を表示]
[categoryテーブルをscheduleテーブルに内部結合、sheduleテーブルのcid(外部キー)とcategoryテーブルのcid(主キー)が一致する、scheduleテーブルのuidである"yyamada"のレコードのみを絞り込み表示]
構文
select フィールド名, ... from テーブル名1
inner join テーブル名2 on テーブル名1.外部キー = テーブル名2.主キー
(絞り込むなら)[where/order by句]
mysql> select schedule.subject, schedule.pdate, schedule.ptime,
-> category.cname, schedule.memo from schedule
-> inner join category on schedule.cid = category.cid
-> where schedule.uid = 'yyamada';
+-----------------+------------+----------+--------+------------------+
| subject | pdate | ptime | cname | memo |
+-----------------+------------+----------+--------+------------------+
| WINGS会議 | 2018-06-25 | 15:00:00 | 会議 | 配布プリント持参 |
| MySQL本原稿提出 | 2018-07-31 | 17:00:00 | 提出 | NULL |
| WINGSメンバ面接 | 2018-08-05 | 13:00:00 | その他 | NULL |
| D企画打ち上げ | 2018-08-21 | 18:00:00 | その他 | NULL |
+-----------------+------------+----------+--------+------------------+
4 rows in set (0.00 sec)
↑scheduleテーブルからsubject, pdate, ptime, memoを、
categoryテーブルからはcnameのレコードを結合表示できました！
（yyamadaさんの予定がより詳しく分かります）
外部結合
さて、外部結合はどんなものか。前項では主キーもしくは外部キーに対応していない部分のレコードも表示するものと説明しました。
結合するどちらかのテーブル全てのレコードは見たいけど、もう一方のテーブルのレコードは一部だけでいい・・・、そういった場面に外部結合が活用されます。
↓テーブル名をas句によってusrを"u"に、scheduleを"s"に省略してます。
select s.subject, s.pdate, u.uname from usr as u
left outer join schedule as s on u.uid =s.uid;
[sテーブルのsubject, pdate,uname(これはuテーブルのもの) をuテーブルに表示]
[左側のテーブル（uテーブル全てのこと）に、sテーブルの外部キーが一致する部分（u.uidとs.uid）を取り出す]
構文
select フィールド名, ... from テーブル名1 [left/right] outer join テーブル名2 on テーブル名1.主キー = テーブル名2.外部キー
(絞り込むなら)[where/order by句]
mysql> select s.subject, s.pdate, u.uname from usr as u
-> left outer join schedule as s on u.uid =s.uid;
+-----------------+------------+----------+
| subject | pdate | uname |
+-----------------+------------+----------+
| 小学校参観日 | 2018-08-10 | 井上花子 |
| NULL | NULL | 田中美紀 |
| NULL | NULL | 原田直樹 |
| WINGS会議 | 2018-06-25 | 掛谷奈美 |
| C社打ち合わせ | 2018-07-31 | 掛谷奈美 |
| D企画打ち上げ | 2018-08-21 | 掛谷奈美 |
| WINGS会議 | 2018-06-25 | 鈴木正一 |
| B企画書提出 | 2018-07-05 | 佐藤留吉 |
| WINGS会議 | 2018-06-25 | 山田祥寛 |
| MySQL本原稿提出 | 2018-07-31 | 山田祥寛 |
| WINGSメンバ面接 | 2018-08-05 | 山田祥寛 |
| D企画打ち上げ | 2018-08-21 | 山田祥寛 |
+-----------------+------------+----------+
12 rows in set (0.00 sec)
↑山田さんの予定だけじゃなく、不要なデータを省いてチームメンバーのスケジュールも表示できました。
サブクエリ
データベースへの命令構文は基本的には一つずつ実行されます。
ですが、本来の目的を果たすための構文（メインクエリ）に予備的な問い合わせ構文（サブクエリ）を含ませることで一つの命令にまとめることが可能です。
select uname, family from usr
where family > (select avg(family) from usr);
[usrテーブルからuname, familyを]
[usrテーブルのfamily平均数より多いfamilyのみ表示]
構文
select フィールド名, ... from テーブル名
where フィールド名 比較演算子 (select命令)
この場合のサブクエリは二行目最後の↑select構文
mysql> select uname, family from usr
-> where family > (select avg(family) from usr);
+----------+--------+
| uname | family |
+----------+--------+
| 井上花子 | 4 |
| 掛谷奈美 | 5 |
| 鈴木正一 | 4 |
| 山田祥寛 | 4 |
+----------+--------+
4 rows in set (0.00 sec)
他にもサブクエリ構文として扱えるのはinsert,update,delete構文などがあり、多様です。
インデックス
データベースにおける索引機能です。
インデックスを設置しない場合の状態をexplain構文で見てみます。
（explain構文はselect構文がどのようにインデックスを利用しているか確認するもの）↓
mysql> explain select uname from usr
-> where uname ='山田祥寛'\g
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | usr | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
usrテーブルに7つのレコードがあると分かります。何万以上のレコード数がもしあったら目当てのレコードを探し出すのはかなり手間が掛かるでしょう。
なので、
- レコード件数が多いテーブル
- よく検索されるテーブル名（とフィールド名）である
- そのフィールドが検索キーまたは外部キー
- そのフィールドで検索することで、全レコードの一割程度まで絞り込める
そんな条件であれば、インデックス設置が効果的になります。
create index idx_usr on usr (uname);
[usrテーブルのunameフィールドをidx_usr（インデックス名）でインデックス作成して]
構文
create index インデックス名 on テーブル名 (フィールド名, ...)
mysql> create index idx_usr on usr (uname);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select uname from usr
-> where uname ='山田祥寛'\g
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | usr | NULL | ref | idx_usr | idx_usr | 62 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑usrのunameをインデックスしたので、rows（走査したレコード数）が7から1になり、目当てのレコードにたどり着く速度が上がりました！
トランザクション
アプリ開発していく中で複数の処理を意味的に一つの処理として扱いたい場面が出ます。
例えば、「注文」「在庫数加減」はそれぞれ別々の処理です。
それらをまとめて、あたかも一つの処理として扱うのがトランザクション機能です。
流れとしては
開始（処理の記録）→処理→終了（処理の確定orキャンセル）
begin[トランザクション開始]
commit[トランザクション確定]
rollback[トランザクションキャンセル]
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into usr
-> values ('akimura', '38271', '木村愛子', 2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from usr
-> where uid = 'akimura';
+---------+--------+----------+--------+
| uid | passwd | uname | family |
+---------+--------+----------+--------+
| akimura | 38271 | 木村愛子 | 2 |
+---------+--------+----------+--------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from usr
-> where uid = 'akimura';
Empty set (0.00 sec)
入力したはずの木村愛子さんのレコードを最後にselect構文で探しても出なかったのは、rollbackでbeginからこれまでの処理をキャンセルしたからですね。
このようにトランザクション機能を活用すると、注文キャンセルされたとしても注文レコードと在庫レコードを探し出して削除する手間を掛けなくて済むわけです。
（想像するだけでもかなり時間の無駄になりそうですもんね）
ここまでMySQLの操作についてアウトプットしてきました！
一通り基本的、高度な操作について触れていけたかなと思います。
更に深堀していくなら、Webアプリ開発に触れてデータベースを活用していく方向となるでしょう。
興味はありますが、セキュリティエンジニアを目指す身なのでまず優先度は低いと思われます。
余裕があったら触れていけたらいいな。
では、おやすみなさい。