前回の【SQLの学習】Webサイトを作る為のデータベース操作方法の学習では基本の4つの構文について記事にさせていただきました。
今回はより実用的な部分での解説を行っていければと思います。
ログイン処理
webサービスにおけるSQLの利用で最もよく使われるのがログインの処理を実装するためのSQLかと思います。
これは前回の記事でお伝えした4つの基本構文のうちの「SELECT（選択）」を利用して実現します。テーブルの構造は以下のような形だとします。
こちら以下の記事でも利用した形です、もしまだテーブル構造についてよくわからないという方がいらっしゃいましたら、こちらの記事も参考にしてみてください。
user_id
nickname
password
この場合、まずはユーザーにメールアドレスとパスワードを入力してもらうログインフォームの画面を用意します。
入力部分はHTMLで以下のような形で実装可能です。
<input type="text" name="email"> <input type="password" name="password">
あとはこの入力された値をPHPなどで受け取りSQLに利用することになります。
こんな感じですね。
正確にはユーザーが入力した値をそのままSQLに組み込むのはSQLインジェクションという脆弱性になるので絶対にやめなければいけない行為です。
なので「（エスケープ済みであること）」と追記していますが、要はSQLで利用されるような文字列をエスケープして構文として見なさないようにするということです。
このあたりは自分で実装するよりも、Laravelなどのフレームワークを利用して既にエスケープ済みで利用できる状況を作っておくのが楽だと思います。
ユーザーの関連するコンテンツを持ってくる
上記でユーザーのログイン処理に利用するSQLを紹介させていただきました。次はログインしたユーザーの関連する情報を持ってくる部分です。
例えばログインしたユーザーのつぶやきを取得するとしましょう。つぶやきテーブルは以下のような形になっているとします。
tweet_id
user_id
tweet_text
ログインしているユーザーのユーザーIDは99とすると、以下のようなSQLで持ってくることが可能です。
一つWHEREの部分で先ほどと違う部分があります。前回が「email = ‘値’」であったのに対し今回は「user_id = 値」となっており値がシングルクォーテーションで囲まれていません。
これは単純に数字はそのまま、文字の場合はシングルクォーテーションで囲む必要があると覚えておけば大丈夫かと思います。
そして上記のSQLではuser_id = 99のtweetがすべて取得できるという形になります。
userのnicknameも取得したい場合
上の例ではユーザーのtweet一覧は取れるのですが、実際に画面に表示する際にはそのuserのnicknameも表示しますよね。
解決策としては二つ。一つはtweetテーブルにnicknameという項目を追加してtweetテーブルにSELECTをかけるだけで一気に取得できるようにするというもの。
しかしそれではもし大元のuserのnicknameが変更されると過去分のtweetのnicknameは古いままということになります。
join構文の利用
そこでtweetテーブルを取得する際に、userテーブルも関連付けて持ってくる方法としてjoin構文があります。
具体的には以下のようなSQLになります。
JOIN user on tweet.user_id = user.user_id
これでtweetテーブルのuser_idと一致するuserテーブルのuserの情報も結合されて取得されてきます。
ただしjoinは複数やりすぎるとSQLが重くなりすぎて最悪サイトが落ちる原因にもなりかねません。
場合によってはjoinを利用せずにそれぞれ別で持ってくることの方が最適解の場合もあります。
項目の並び順
例えばユーザー一覧を取得する場合のSQLは以下の様になります。
もし登録が新しい＝user_idが大きい順に並び変えて取得したいという場合には以下のようなSQLになります。
逆に登録が古い＝user_idが小さい順にの場合は以下です。
DESCは降順、ASCは昇順での並び替えになります。降順は大きいものから降りていく（10→9→8）、昇順は小さいものから上がっていく（1→2→3）ということですね。
SQLで取得してからプログラムで並べ替える方法もありますが、取得＋並び替え処理と二度手間になるので、SQLで片づけられる並び順はSQLで片づけてしまった方が楽です。
投稿の新しい順、古い順に並べ替えるというのはよくあることだと思うので、覚えておいて損はないはずです。
トランザクションのお話
今回は主にSELECTについての実例を見てきたのであまり関係ないのですが、データベースにはトランザクションという考え方があります。
今回の例でいえばtweetに新規投稿が行われるとuserテーブルに新たに追加したこれまでの投稿数がupdateされるとします。
tweetの投稿には成功、しかしuserテーブルの投稿数のupdateに失敗した場合、実際のtweetは1件なのにuserの投稿数は0のまま、ということになりますよね。
このような一連の関連する処理のことをトランザクション処理と言います。
ロールバックとコミット
これは学習始めの段階ではまだ詳しく理解する必要はありませんが、実務では必須のスキルになるので軽く把握だけしておくと良いと思います。
トランザクションを開始、コミットで確定するまでの間のSQLはすべて途中でロールバックすると元に戻せるという仕組みになります。
つまり上記の例で行くとtweetへの新規投稿はうまくいったけど、userテーブルの投稿数のupdateが失敗した場合にはロールバックすればtweetへの新規投稿もなかったことにできるというわけです。
- トランザクション開始
- tweet投稿
- 投稿数更新
- コミット
1～4の間であればどこであってもロールバックを行い1の状態に戻せるというわけですね。
まとめ
基本の4構文から少し発展させた内容を紹介させていただきました。実務でよく使うパターンですのでぜひ覚えて活用してみてください！