【新人教育 資料】第5章 SQLへの道 〜絞込編〜
あらすじ
新人がいっぱい入ってくる。新人のレベルもバラバラ。教育資料も古くなっているので、更新しましょう。
どうせなら、公開しちゃえばいいじゃん。という流れになり、新人教育用の資料を順次更新していくことにしました。
※後々、リクエストに応じて更新することが多いのでストックしておくことをおすすめします。
自分はTEMONA株式会社でCTOをしていますが、頭でっかちに理論ばっかり学習するよりは、イメージがなんとなく掴めるように学習し、実践の中で知識を深めていく方が効率的に学習出来ると考えています。
※他の登壇やインタビュー記事はWantedlyから見てください。
教育スタイルとしては正しい事をきっちりかっちり教えるのではなく、未経験レベルの人がなんとなく掴めるように、資料を構成していきます。
以下のようなシリーズネタで進めます。
では、今回もはじめていきましょう！
SQL 絞り込み WHERE
今回の章では、実際に、SQLのWHERE句を使ってデータを絞り込む方法を勉強します。
データの全体母数からWHERE句を使ってデータ集合を絞り込むために利用します。
WHERE句が使えるのはSQL4大命令のうち「SELECT」、「UPDATE」、「DELETE」。
さぁ見ていきましょう。
勉強用に公開のレポジトリを用意しましたので
以下の準備編でgit clone するなりしてください。
用意しているテストデータは本番っぽく作ってありますが
ランダムで作ったテストデータなので安心してください。
メールアドレスなども、「example.com」、「example.net」、「example.org」などの
例表示用の予約ドメインなので、事故が起こらないように配慮しています。
事前準備
下記のレポジトリをForkして、Readmeを参考に環境構築をしてください。
https://github.com/TEMONA/mysql_study
※全てにおいて自己責任でお願いします。
上記の事前準備が終わっていることとして、下記の説明を進めていきます。
$ mysql -u root
mysql> use mysql_study;
で"mysql_study"に接続している状態で、実際にコマンドを打ちながら見ていくと理解度が高まります。
WHERE
特定の条件を持った情報を取得する際に、WHEREを使うことによって、条件を指定することが可能です。
ex. id100を持っているお客様を取得する
SELECT * from users WHERE id = 928;
WHERE解説
SELECT構文の基本は、
SELECT ”取得したい情報” from "テーブル名";
でしたね？その後ろにwhere句がつきます。
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" = "foo";
で、条件の指定が出来ます。
「以上」、「以下」と「より大きい」、「より小さい」
〇〇以上、〇〇未満の情報を取得するというケースに関して説明を致します。
ex. 成人(20歳以上)のusersの情報を全て取得する
SELECT * from users WHERE age >= 20;
##「以上」、「以下」と「より大きい」、「より小さい」解説
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" >= "hogehoge";
で、条件の指定が出来ます。
その他にも使える演算子を紹介します。
| 名前 | 説明 |
|---|---|
| = | 等価 (等しい) 演算子 |
| <=> | NULL 安全等価演算子 |
| > | 右不等 (より多い) 演算子 |
| >= | 以上 (より多いか等しい) 演算子 |
| < | 左不等 (より少ない) 演算子 |
| <= | 以下 (より少ないか等しい) 演算子 |
| !=, <> | 不等価 (等しくない) 演算子 |
正直使ったこと無い不等号もあります。
書き方によっては、違う表現が出来る内容もあるので、SQLの構文は自由度が高いですね！
補足ですが、
複数の条件を複合する場合には、"AND"を使うことによって、検索条件の追加が出来ます。
SELECT * from users WHERE age >= 20 AND age <= 30;
BETWEEN
〇〇以上、〇〇未満の情報を取得するというケースに関して説明を致します。
上記の不等号を使っても表現することが出来ますが、BETWEENを使うことでも表現することが可能です。
ex. 20代のusersの情報をすべて取得する
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
BETWEEN解説
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" BETWEEN "最小値" AND "最大値";
BETWEEN句以降に最小値と最大値を指定します。
この時最小値と最大値の順番は重要です。
BETWEEN句が使えるのは、整数型を代表する、以上以下で表現が出来る型のみです。
#LIKE
部分一致で検索する例を説明致します。
ex. mail_addressに"ky"を含むusersの情報を全て取得する
select * from users where mail_address like "%ky%";
LIKE解説
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" LIKE "%絞込をしたい文字列%";
LIKE句で使う"%"は、ワイルドカードと読んでおり、"%ky%"としたときには、kyを含む文字列を取得することが出来ます。
これを応用して、"ky%"と書いた時には、kyから始まる文字列の絞込が出来ます。
#日付範囲
ex. "2015-04-01"以前の注文を全て取得する
select * from orders where created_at <= "2015-04-01 00:00:00";
##日付範囲解説
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" <= "○○-○○-○○ ○○:○○:○○";
日付の絞込の際にもWHERE句を使って表現が出来ます。
日付は、date型、datetime型などの種類を持っており、
複雑なクエリを表現したい場合には、関数を使って表現出来ます。
参考
MySQLリファレンス(日付及び時間関数): https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html
#INとNOT IN
配列を渡して、含む、含まないという検索について説明致します。
ex. idが108,966,982を持っているusersの情報を全て取得する
select * from users where id in (108,966,982);
ex. idが108,966,982を持っていないusersの情報を全て取得する
select * from users where id not in (108,966,982);
INとNOT IN解説
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" in ("指定したい情報をカンマ区切りで指定");
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" not in ("指定したい情報をカンマ区切りで指定");
INで指定するときには、カンマ区切りの配列で複数の条件を指定することが出来ます。
文字列の場合にも("foo","bar","baz")という形で指定をすることが出来ます。
EXISTS
2つのテーブルが関係する場合の検索処理に関して解説を致します。
ex. ordersを持っているusersの情報を全て取得する(購入履歴のあるお客様の情報を全て取得する)
SELECT * from users WHERE EXISTS (SELECT user_id FROM orders where users.id = orders.user_id);
EXISTS解説
SELECT ”取得したい情報” from "テーブル名" WHERE EXISTS (SELECT "column名" FROM "判定に使うテーブル名" WHERE "テーブル名.column名" = "判定に使うテーブル名.判定に使うcolumn名");
SQLの中にSQLが書かれている形のことをサブクエリーと呼びます。
サブクエリーが何なのか？という説明よりも、()の中で問い合わせた結果を使って再度検索が動いているのをイメージ出来るようになるとレベルアップ出来ると思います。
演習
- 東京都に住んでいる20代男性の全ての情報を取得
- 東京都渋谷区に住んでいる人の全ての情報を取得
- 今週が誕生日のusersの全ての情報を取得
- メールアドレスが"bet"で始まる人の情報を取得
- 犬の楽しい話を買っているusersの情報を全て取得
参考文献
mysql5.6 リファレンスマニュアル: https://dev.mysql.com/doc/refman/5.6/ja/
あとがき
この章まで来るとなんかSQLって簡単みたいに感じる頃でしょう。
新人教育資料では入門編なので、まだ触れていませんが、実行されるコスト(時間やCPUやメモリ)などの話や複雑なクエリの話などもあって、実に奥が深いものです。
次回は「【新人教育 資料】第6章 SQLへの道 〜ソート編〜」をお送りする予定です。
今まで勉強してきた「【新人教育 資料】第2章 SQLへの道 〜3値論理編〜」や「【新人教育 資料】第3章 SQLへの道 〜基本数学編〜」の話がSQLに絡んでないじゃんって思う人もいると思うので、そろそろ絡めて話を作っていこうと思います。