今後ホームページを作成していくにあたり、情報をデータベースで管理していく必要があるとわかりました。
そのデータベースへアクセスするための専用の操作言語として、 SQL というものがあり、今回はそれに触れてみようと思います。
学習のために使う書籍は、「中山清喬 飯田理恵子（2022）『スッキリわかるSQL入門 第3版 ドリル256問付き! (スッキリわかる入門シリーズ) 』株式会社フレアリンク」です。
その中でも第２章・第８章・第９章を読んで学んだことを、そこで紹介されているコードを実際に試しながら（本書のコンテンツの一部です）、ここに記載していこうと思います。
エンジニア歴１ヶ月の人間が初めてSQLに触れて学んだことを記載するので、私と同じようにSQLのことをよく知らない方はSQLの簡単な紹介として、すでにSQLをご存知の方は「自分もこんなことを昔学習したな〜」と振り返っていただけるものとして、この記事を見ていただければ幸いです。（今回かなり長くなってしまいましたが、温かい目🥺で見ていただけると幸いです）
SQLとは
SQLとは、データベースやデータを操作するための操作言語です。データベースは主に複数の表の形式（RDB）で管理されており、SQLで書かれた命令をデータベース管理システム（DBMS）に送信することで、データの検索・追加・更新・削除などを行うことができます。
例えば、
家計簿というデータ（テーブル）に右上のSQLの命令を送信すると、3000円超の出金額のデータを、日付、費目とともに取得することができます。
Excelと似てるなと思いましたが、データを直接編集するのではなく、外部から命令を送りデータを編集したり取り出したりするところがSQLの特徴なのかなと思いました。
第２章：SQLの基本ルールと４大命令
SQLには特徴的なルールがあります。
- 文の途中に改行を入れることができます。行の先頭や途中に 半角 の空白を入れることもできます（全角だとダメ）。セミコロン（；）をつけることもできます。（CSSのように必須というわけではないです）
- ハイフン２つ（ーー）から行末、/＊から＊/まではコメントとして扱われます。（CSSやPHPと似てる）
- Selectやfromなどの命令に使う単語を「予約語」といい、大文字小文字どちらでも良いですが、家計簿などのテーブル名、日付や費目などの列名として用いることはできません。
- 日付や費目などの具体的なデータをリテラルといいますが、文字はシングルクォーテーション（’）で囲み、数値は（’）で囲まず、日付は（’XXXX-XX-XX’）で記述します。それぞれの列にはデータ型というものがあり（日付・費目の下にあるDATE・VARCHARがそれです）、指定された型で入力する必要があります。（誤った型を入力すると、処理が中断されるか強制的に正しい型に変換されます）
また、SQLには４大命令というものがあり、これを理解すればほとんどの処理を実行できます。４大命令をざっくりまとめると、
| 命令 | 命令固有の部分 | 絞り込み | その他 |
|---|---|---|---|
| Select | 列名... from テーブル名 | where | 加工 |
| Update | テーブル名 set 列名 = 値 | where | |
| Delete | from テーブル名 | where | |
| Insert | into テーブル名(列名...) Values (値) |
このような体系図にできます。以下、ひとつづつ紹介します。
Select文
Select文は、テーブルから目的のデータを取得する命令で、取得したい列名を記述します。fromでデータを取得したいテーブルを指定して（テーブルからデータを取得する場合は必須）、whereで条件の指定、asで列の別名を設定したりして使います。
例えば、
このように記述することで、費目が給料となっているデータの中でも、費目（item）・入金額（receive）・出金額（pay）のデータを取得できます。
Update文
Update文は、すでに存在するデータを書き換える命令で、後には更新したいテーブル名を指定し、setで更新したい列名とその値を＝で繋ぎ、whereで更新したい行を指定します。（setは必須で、whereも、全ての行を更新しない限りは使います。）
例えば、
このように記述することで、給料の入金額を変更することができます。（今回は2024年2月10日の項目が一つになっていますが、もし複数ある場合はそれらも変更されますし、一つもない場合は変更されません）
Delete文
Delete文は、すでにある行を削除する命令です。
Delete from /*ここにテーブル名*/
where /*ここに列名 ＝ 値（リテラル）*/
という形で記述します。
（deleteは変化がわかりづらいですし、仮にwhereをつけないとデータを全削除してしまうことになり怖いので、やめておきました
）
Insert文
Insert文は、テーブルに新しいデータを追加する命令で、後にinto テーブル名 列名（全ての列に追加したければ列名は不要）と続きます。その後に、values （追加したい値）と記述します。（valuesの部分は、default valuesやselect fromに変えることもできるそうです）
例えば、
このように記述することで、一番下に新たな行が追加されました。
４大命令で大事な２つのこと
- 分類で覚える！
- テーブル名は必ず必要！
特に１.は、４大命令それぞれの概念の理解に役立ちました！
具体的には、
| 検索系・既存系 | 更新系・既存系 | 更新系・新規系 |
|---|---|---|
| Select | Update・Delete | Insert |
このような感じです。（Update文とDelete文は性質としては近いんですね
）
この後、本書では第３章からさらに詳しい構文解説が続きます。そちらは、実務でSQLを実際に触れてから学習しようと思います。
第８章：複数テーブルの結合
これまでは一つのテーブルを扱ってきましたが、例えば家計簿は上記だけで完結しないほど量の多いものですし、費目が重複することもあります。このように一つのテーブルだけでは情報が多すぎる場合は、各命令の処理にも時間がかかったりします。そのような場合は複数のテーブルに分けたりすることができます。
例えば、家計簿の中の、給料という費目を給料手当と更新したい場合、
一つのテーブルだと、全家計簿の中から給料の行を探して処理することになりますが、
新たに費目というテーブルを追加すれば費目の種類の中から探すだけで済むので、処理が速くなります。
コンピュータにとっては良くても人間にとってはテーブルが増えるとややこしいよ！ と思いましたが、人間にとってわかりやすいテーブルを作ってくれる結合（join）という機能がSQLにはあります。
例えば、
この二つのテーブルも、右上のコードを書けば、
自分の指定した列だけを結合してくれた、わかりやすいテーブルを作ることができます！この場合は家計簿のテーブルに費目のテーブルをjoinで結合させ、onで家計簿の費目IDと費目テーブルのIDを照らし合わせて結合させています。（結合条件の指定）
このほかにも、
- ２つのテーブルにあるメモなどの項目同士を結合させたり
- 家計簿や費目と記入するのが大変なのでasで別名を設定したり
- ３つ以上の表を結合させたり
- 同じテーブルを結合できたりもします！
（全て紹介するとかなり長くなるので割愛します。本書では上記の画像のように、実際にコードを入力するコンテンツが備わっているので皆さんも試してみてください。参考文献にURLを記載しております。）
第９章：トランザクション
ここまで、SQLを使った、データベース内にあるデータの操作についてざっくり触れてきましたが、今後SQLを使って実務に取り組むにあたり、データベースそのものへの理解も深めることが必要であると感じました。（取引相手のことをよく知らなければ最高の成果を提供することができないのと同じ感じでしょうか
）
そこでこの章では、データベース管理システム（DBMS）の機能のうち、トランザクションについて紹介しています。
トランザクションとは
例えば、銀行で10000円振り込む時、自分の口座残高を10000円減らす処理が行われ、相手の口座残高を10000増やす処理が行われます。しかし、停電などにより処理が中断されたとき、自分の口座からは10000円減ったのに相手の口座は増えていないという事態が発生するかもしれません。
そこでDBMSには、トランザクション制御 という機能が備わっています。SQL文を複数DBMSに送る際、１つ以上のSQL文をひとかたまりにして扱うよう指示することができるのですが、そのかたまりのことを トランザクション と言います。
先程の例で言うと、自分の口座残高を10000円減らすUpdate文と、相手の口座残高を10000円増やすUpdate文をトランザクションとすることで、たとえ停電が起きても、一部の文だけが実行されることがないようにDBMSが処理してくれます。
このようにDBMSが、トランザクションを「一部だけが実行されることはあってはならない、途中で分割不可能なもの」として取り扱うことを、トランザクション制御と言います。
具体的なコード
トランザクションに関する具体的なコードを紹介します。
この処理は、2022年1月31日以前の家計簿データを家計簿アーカイブテーブルにコピー（処理１）して、元のデータを削除する（処理２）SQL構文です。もし、処理１と処理２の間で処理が中断されたら、家計簿データが重複してしまうことになります。
そこで、初めにbegin、最後にcommitと入力することで、その間のコードをトランザクションとすることができ、処理の分割を防げます。（自動でトランザクションが行われるDBMSの場合は、beginは使用しない）
処理が途中された場合はrollback（ロールバック）という、ここまでの処理をキャンセルして無かったことにする、という機能が働きます。（このrollbackはcommitの部分に書くことで、手動でのキャンセルもできます。ただし、多くのDBMSでは自動コミット機能がデフォルトで備わっているため注意が必要です）
トランザクションの分離
トランザクションには、同時実行の副作用というものがあります。
例えば、30000円の残高がある口座から10000円を引き出すという処理と、電力会社が5000円を引き落とすという処理がほぼ同時に行なわれたとします。この結果自分の手元には10000円があり、残高は15000円になるはずです。
しかし、もし10000円の引き出しが途中で中断された場合、残り20000円となった残高から5000円引き落とす電力会社の処理は実行されるため、手元に10000円がないにも関わらず残高が15000円になってしまうという問題が発生するかもしれません。これは同時実行の副作用によって引き起こされる問題です。
同時実行の副作用としては、３つ代表的なものがあります。以下に先程の銀行口座を例に挙げて紹介します。
- ダーティーリード：30000円の口座から10000円の引き出し処理が完了する前に、電力会社が残り20000円という残高情報を確認できてしまう問題
- ノンリピータブルリード：その残高情報を元に5000円引き落とす（残高を15000円にする）処理が行なわれたため、10000円の引き出し処理が行なわれていないにも関わらず、残高が15000円になってしまう問題
- ファントムリード：口座残高を確認する処理を行なっている最中に、別の人から送金があった場合に予期せぬ金額が表示されてしまう問題
などがあります。
これを解決してくれるのが、トランザクションの分離です。これは、DBMSが内部でロックという仕組みを使い、自分がトランザクションを実行してデータを操作している際は、他の人がそのデータに関するトランザクションを実行できないようにするものです。
トランザクションの分離にはレベルがあり、そのレベルに応じて対処したい副作用が変わってきます。
| 分離レベル | ダーティーリード | ノンリピータブルリード | ファントムリード | |
|---|---|---|---|---|
| 高速・危険 | Read Uncommitted | 恐れあり | 恐れあり | 恐れあり |
| ↑ | Read Committed | 発生しない | 恐れあり | 恐れあり |
| ↓ | Repetable Read | 発生しない | 発生しない | 恐れあり |
| 安全・低速 | Serializable | 発生しない | 発生しない | 発生しない |
そのレベルの指定は、
set transaction isolation level /*ここに分離レベル名*/
/*または*/
set current isolation /*ここに分離レベル名*/
/*どちらを使うかはDBMSによって異なります*/
によって変わります。
また、上記のロックはDBSによって自動的に行なわれるものですが、行・表・データベースなどへのロックを明示的に行なうことも出来ます。
コード例：行のロック
コード例：表のロック
上記の図の「EXCLUSIVE」という部分は、ロックのモードの一つである「排他ロック」を指定するコードであり、他からのロックを許可しません。他のモードとしては「共有モード（share）」があり、他からのロックも可能になります。
ロックの注意点
ロックは前述した、同時実行の副作用に対して有効なものです。しかし、ロックが多発すると待ち時間が多くなり、DBMSが重くなるので注意が必要です。また、大勢の人が同時に利用できるというDBMSの利点も失われてしまいます。
ロックをするときは、
- 明示的にロックする時は、必要最小限の範囲に留める
- 排他ロックの代わりに共有ロックを使用できないか検討する
といった注意点を覚えておくことが必要です。
また、排他ロックには、デッドロックという問題も存在します。
例えば、Xをロックしている人がYをロックしようとしたとします。同時にYをロックしている人がXをロックしようとした時、両者ともロックの解除待ちという状態になるので、永久に処理が止まってしまいます。これをデッドロックと言います。
多くのDBMSには、片方のトランザクションを強制的にロールバックさせることで、デッドロック状態から抜け出させる仕組みが備わっていますが、
- トランザクションの時間（ロックの時間）を短くする
- X→Yのように同じ順番でロックする
といったことでデッドロックを予防できます。
まとめ
ここまでご覧くださりありがとうございました。今回初めて、データベースを管理できるプログラムに触れてみました。今まで学んだ言語（CSS・PHPなど）よりは理解しやすかったです。実戦で使ったことはまだないので、今後のホームページ作成で活用しつつ、今回触れられなかった箇所の学習を進めたいと思いました！
参考文献