SQLドリル

アイドルくじサービスのテーブルの残骸があったので、これを使ってクエリを試していく。

show columns from idols;
+————–+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+————–+——+—–+———+—————-+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| img_url | varchar(255) | YES | | NULL | |
| max_issuance | int(11) | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+————–+————–+——+—–+———+—————-+

select * from idols;
+—-+————–+—————————-+————–+———————+———————+
| id | name | img_url | max_issuance | created_at | updated_at |
+—-+————–+—————————-+————–+———————+———————+
| 4 | 広瀬すず | /idols/hirose_suzu.jpg | 10 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
| 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 3 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
| 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 7 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
| 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 10 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
| 8 | 有村架純 | /idols/arimura_kasumi.jpg | 8 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
| 9 | 本田翼 | /idols/honda_tsubasa.jpg | 10 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
| 10 | 桐谷美玲 | /idols/kiritani_mirei.jpg | 10 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
+—-+————–+—————————-+————–+———————+———————+

idolsはアイドルのマスタのテーブルで、
max_issuanceは、発行枚数を意味している。
くじをひくと、この中からランダムに1枚のアイドルが得られる。

特定カラムを指定
select name from idols;
+————–+
| name |
+————–+
| 広瀬すず |
| 川口春奈 |
| 小松菜奈 |
| 新垣結衣 |
| 有村架純 |
| 本田翼 |
| 桐谷美玲 |
+————–+

複数カラムを指定
select name, max_issuance from idols;
+————–+————–+
| name | max_issuance |
+————–+————–+
| 広瀬すず | 10 |
| 川口春奈 | 3 |
| 小松菜奈 | 7 |
| 新垣結衣 | 10 |
| 有村架純 | 8 |
| 本田翼 | 10 |
| 桐谷美玲 | 10 |
+————–+————–+

指定した列に別名をつける
select
name AS 名前,
max_issuance AS 発行上限枚数
from
idols;
+————–+——————–+
| 名前 | 発行上限枚数 |
+————–+——————–+
| 広瀬すず | 10 |
| 川口春奈 | 3 |
| 小松菜奈 | 7 |
| 新垣結衣 | 10 |
| 有村架純 | 8 |
| 本田翼 | 10 |
| 桐谷美玲 | 10 |
+————–+——————–+

列の値に対して演算を行う
select
name AS 名前,
max_issuance+10 AS 発行上限枚数プラス10
from
idols;
+————–+——————————-+
| 名前 | 発行上限枚数プラス10 |
+————–+——————————-+
| 広瀬すず | 20 |
| 川口春奈 | 13 |
| 小松菜奈 | 17 |
| 新垣結衣 | 20 |
| 有村架純 | 18 |
| 本田翼 | 20 |
| 桐谷美玲 | 20 |
+————–+——————————-+
(他にもいろいろな演算子が使えます)

集合関数を使う
mysql> select AVG(max_issuance) from idols;
+——————-+
| AVG(max_issuance) |
+——————-+
| 8.2857 |
+——————-+

文字列を連結する
select CONCAT(name, ‘ 様’) AS 名前 from idols;
+————————+
| 名前 |
+————————+
| 広瀬すず 様 |
| 川口春奈 様 |
| 小松菜奈 様 |
| 新垣結衣 様 |
| 有村架純 様 |
| 本田翼 様 |
| 桐谷美玲 様 |
+————————+

条件で絞り込む(1)
select name, max_issuance from idols where max_issuance > 8;
+————–+————–+
| name | max_issuance |
+————–+————–+
| 広瀬すず | 10 |
| 新垣結衣 | 10 |
| 本田翼 | 10 |
| 桐谷美玲 | 10 |
+————–+————–+

条件で絞り込む(パターンマッチング)
selectname from idols where name LIKE ‘%奈’;
+————–+
| name |
+————–+
| 川口春奈 |
| 小松菜奈 |
+————–+

パターンマッチングで使える記号
% 任意の文字列
_ 任意の1文字

select
name,
max_issuance,
CASE
WHEN max_issuance >= 10 THEN ‘C’
WHEN max_issuance >= 7 THEN ‘B’
ELSE ‘A’
END AS レア度
from
idols;
+————–+————–+———–+
| name | max_issuance | レア度 |
+————–+————–+———–+
| 広瀬すず | 10 | C |
| 川口春奈 | 3 | A |
| 小松菜奈 | 7 | B |
| 新垣結衣 | 10 | C |
| 有村架純 | 8 | B |
| 本田翼 | 10 | C |
| 桐谷美玲 | 10 | C |
+————–+————–+———–+

グループ単位の集計
select max_issuance AS 発行枚数, count(*) AS アイドル何人いる from idols GROUP BY max_issuance;
+————–+————–+
| 発行枚数 | 何人いる |
+————–+————–+
| 3 | 1 |
| 7 | 1 |
| 8 | 1 |
| 10 | 4 |
+————–+————–+
selectで指定できるのはGROUP BYで指定したキーまたは集合関数のみ

グループ単位で集計した結果を絞り込む
select max_issuance AS 発行枚数, count(*) AS
何人いる from idols GROUP BY max_issuance HAVING COUNT(*) > 2;
+————–+————————–+
| 発行枚数 | アイドル何人いる |
+————–+————————–+
| 10 | 4 |
+————–+————————–+

グループ単位で集計した結果を絞り込む
mysql> select max_issuance AS 発行枚数, COUNT(*) AS 何人いる from idols where id = 4 OR id = 7 OR ID= 5 GROUP BY max_issuance;
+————–+————–+
| 発行枚数 | 何人いる |
+————–+————–+
| 3 | 1 |
| 10 | 2 |
+————–+————–+

クロス集計
atode

並び替え
select name, max_issuance from idols ORDER BY max_issuance ASC;
+————–+————–+
| name | max_issuance |
+————–+————–+
| 川口春奈 | 3 |
| 小松菜奈 | 7 |
| 有村架純 | 8 |
| 広瀬すず | 10 |
| 新垣結衣 | 10 |
| 本田翼 | 10 |
| 桐谷美玲 | 10 |
+————–+————–+
ASC 昇順
DESC 降順

重複の削除
mysql> select distinct max_issuance from idols;
+————–+
| max_issuance |
+————–+
| 10 |
| 3 |
| 7 |
| 8 |
+————–+

ここからは複数テーブルに対するクエリを見ていく。
mysql> select * from draw_idols;
+—–+———+———+————–+—————————-+———————+———————+
| id | user_id | idol_id | name | img_url | created_at | updated_at |
+—–+———+———+————–+—————————-+———————+———————+
| 74 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-25 14:34:33 | 2017-12-25 14:34:33 |
| 75 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-25 14:34:36 | 2017-12-25 14:34:36 |
| 76 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-25 14:34:38 | 2017-12-25 14:34:38 |
| 78 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:34:42 | 2017-12-25 14:34:42 |
| 79 | 3 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:34:43 | 2017-12-25 14:34:43 |
| 80 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-25 14:34:45 | 2017-12-25 14:34:45 |
| 81 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-25 14:34:46 | 2017-12-25 14:34:46 |
| 82 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-25 14:34:47 | 2017-12-25 14:34:47 |
| 83 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:34:49 | 2017-12-25 14:34:49 |
| 84 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-25 14:34:50 | 2017-12-25 14:34:50 |
| 85 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-25 14:34:51 | 2017-12-25 14:34:51 |
| 86 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:35:04 | 2017-12-25 14:35:04 |
| 87 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:35:05 | 2017-12-25 14:35:05 |
| 88 | 2 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-25 14:35:06 | 2017-12-25 14:35:06 |
| 89 | 2 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:35:07 | 2017-12-25 14:35:07 |
| 90 | 2 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-25 14:35:31 | 2017-12-25 14:35:31 |
| 91 | 1 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-25 14:35:33 | 2017-12-25 14:35:33 |
| 92 | 1 | 10 | 桐谷美玲 | /idols/kiritani_mirei.jpg | 2017-12-25 14:35:34 | 2017-12-25 14:35:34 |
| 93 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-25 14:38:23 | 2017-12-25 14:38:23 |
| 94 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-27 03:15:09 | 2017-12-27 03:15:09 |
| 95 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-27 11:57:54 | 2017-12-27 11:57:54 |
| 96 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-27 12:41:48 | 2017-12-27 12:41:48 |
| 98 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-27 12:48:04 | 2017-12-27 12:48:04 |
| 99 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-27 12:50:11 | 2017-12-27 12:50:11 |
| 100 | 1 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-27 12:54:53 | 2017-12-27 12:54:53 |
+—–+———+———+————–+—————————-+———————+———————+
25 rows in set (0.00 sec)

このテーブルはくじを引いて出たアイドルがユーザごとに保存されている

一回もくじで出ていないアイドルが存在する確認する
サブクエリ

mysql> select * from idols where id not in (select idol_id from draw_idols);
+—-+———–+————————–+————–+———————+———————+
| id | name | img_url | max_issuance | created_at | updated_at |
+—-+———–+————————–+————–+———————+———————+
| 9 | 本田翼 | /idols/honda_tsubasa.jpg | 10 | 2017-12-25 14:33:48 | 2017-12-25 14:33:48 |
+—-+———–+————————–+————–+———————+———————+
1 row in set (0.00 sec)

テーブルに別名をつける
複数テーブルがターゲットになる場合、テーブルに名前をつけたほうがよいことがある。
mysql> select i.name from idols AS i;
+————–+
| name |
+————–+
| 広瀬すず |
| 川口春奈 |
| 小松菜奈 |
| 新垣結衣 |
| 有村架純 |
| 本田翼 |
| 桐谷美玲 |
+————–+
7 rows in set (0.01 sec)

複数テーブルの結合を行う
ユーザごとに何枚のおみくじを引いたかを取得する
結合 + グルーピング + 集計

mysql> select u.email, count(*) from users as u join draw_idols as d on d.user_id = u.id group by u.email;
+——————+———-+
| email | count(*) |
+——————+———-+
| otaku2@gmail.com | 3 |
| otaku3@gmail.com | 1 |
| otaku@gmail.com | 21 |
+——————+———-+
3 rows in set (0.00 sec)

ユーザごとに、どの種類のおみくじを引いたかを取得する
group by にnameを追加すれば良い

mysql> select u.email, d.name, count(*) from users as u join draw_idols as d on d.user_id = u.id group by u.email, d.name;
+——————+————–+———-+
| email | name | count(*) |
+——————+————–+———-+
| otaku2@gmail.com | 新垣結衣 | 1 |
| otaku2@gmail.com | 有村架純 | 2 |
| otaku3@gmail.com | 新垣結衣 | 1 |
| otaku@gmail.com | 小松菜奈 | 4 |
| otaku@gmail.com | 川口春奈 | 4 |
| otaku@gmail.com | 広瀬すず | 5 |
| otaku@gmail.com | 新垣結衣 | 5 |
| otaku@gmail.com | 有村架純 | 2 |
| otaku@gmail.com | 桐谷美玲 | 1 |
+——————+————–+———-+
9 rows in set (0.00 sec)

一枚もくじを引いていないユーザがいた場合?

mysql> select * from draw_idols;
+—–+———+———+————–+—————————-+———————+———————+
| id | user_id | idol_id | name | img_url | created_at | updated_at |
+—–+———+———+————–+—————————-+———————+———————+
| 74 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-25 14:34:33 | 2017-12-25 14:34:33 |
| 75 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-25 14:34:36 | 2017-12-25 14:34:36 |
| 76 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-25 14:34:38 | 2017-12-25 14:34:38 |
| 78 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:34:42 | 2017-12-25 14:34:42 |
| 79 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:34:43 | 2017-12-25 14:34:43 |
| 80 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-25 14:34:45 | 2017-12-25 14:34:45 |
| 81 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-25 14:34:46 | 2017-12-25 14:34:46 |
| 82 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-25 14:34:47 | 2017-12-25 14:34:47 |
| 83 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:34:49 | 2017-12-25 14:34:49 |
| 84 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-25 14:34:50 | 2017-12-25 14:34:50 |
| 85 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-25 14:34:51 | 2017-12-25 14:34:51 |
| 86 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:35:04 | 2017-12-25 14:35:04 |
| 87 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:35:05 | 2017-12-25 14:35:05 |
| 88 | 2 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-25 14:35:06 | 2017-12-25 14:35:06 |
| 89 | 2 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-25 14:35:07 | 2017-12-25 14:35:07 |
| 90 | 2 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-25 14:35:31 | 2017-12-25 14:35:31 |
| 91 | 1 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-25 14:35:33 | 2017-12-25 14:35:33 |
| 92 | 1 | 10 | 桐谷美玲 | /idols/kiritani_mirei.jpg | 2017-12-25 14:35:34 | 2017-12-25 14:35:34 |
| 93 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-25 14:38:23 | 2017-12-25 14:38:23 |
| 94 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-27 03:15:09 | 2017-12-27 03:15:09 |
| 95 | 1 | 6 | 小松菜奈 | /idols/komatsu_nana.jpg | 2017-12-27 11:57:54 | 2017-12-27 11:57:54 |
| 96 | 1 | 4 | 広瀬すず | /idols/hirose_suzu.jpg | 2017-12-27 12:41:48 | 2017-12-27 12:41:48 |
| 98 | 1 | 7 | 新垣結衣 | /idols/aragaki_yui.jpg | 2017-12-27 12:48:04 | 2017-12-27 12:48:04 |
| 99 | 1 | 5 | 川口春奈 | /idols/kawaguti_haruna.jpg | 2017-12-27 12:50:11 | 2017-12-27 12:50:11 |
| 100 | 1 | 8 | 有村架純 | /idols/arimura_kasumi.jpg | 2017-12-27 12:54:53 | 2017-12-27 12:54:53 |
+—–+———+———+————–+—————————-+———————+———————+
25 rows in set (0.00 sec)

user_id が3のユーザはくじを引いていなかったとする。
その場合、上記のjoin(内部結合)を使った場合、user_id = 3のユーザは取得できない。

mysql> select u.email, count(*) from users as u join draw_idols as d on d.user_id = u.id group by u.email;
+——————+———-+
| email | count(*) |
+——————+———-+
| otaku2@gmail.com | 3 |
| otaku@gmail.com | 22 |
+——————+———-+
2 rows in set (0.00 sec)

結合条件で一致するデータで抽出されるため。このような場合は外部結合を使う
mysql> select u.email, count(*) from users as u left outer join draw_idols as d on d.user_id = u.id group by u.email;
+——————+———-+
| email | count(*) |
+——————+———-+
| otaku2@gmail.com | 3 |
| otaku3@gmail.com | 1 |
| otaku@gmail.com | 22 |
+——————+———-+
3 rows in set (0.01 sec)

user_id = 3も表示できているが、1件となっている。
外部結合では結合条件に合致しないところはnullで取得されるため
正しく取得するにはcase を使って当該テーブルのカラムがnullの場合

mysql> select u.email, case when d.user_id is null then 0 else count(*) end from users as u left outer join draw_idols as d on d.user_id = u.id group by u.email;
+——————+——————————————————+
| email | case when d.user_id is null then 0 else count(*) end |
+——————+——————————————————+
| otaku2@gmail.com | 3 |
| otaku3@gmail.com | 0 |
| otaku@gmail.com | 22 |
+——————+——————————————————+
3 rows in set (0.00 sec)

UNIAN ALL
複数のselectを1つの結果にまとめるイメージ
mysql> select name AS 名前 from idols union all select email from users AS 名前;
+——————+
| 名前 |
+——————+
| 広瀬すず |
| 川口春奈 |
| 小松菜奈 |
| 新垣結衣 |
| 有村架純 |
| 本田翼 |
| 桐谷美玲 |
| otaku2@gmail.com |
| otaku3@gmail.com |
| otaku@gmail.com |
+——————+
10 rows in set (0.00 sec)
(こんな使い方はしないが)
利用シーンの例としては↓がわかりやすい
https://qiita.com/suin/items/3ada90eaf2e808e510b8

UNIAN
UNIAL ALLとの違いは、UNIANは結果から重複行を削除してくれること。
mysql> select name AS 名前 from idols union select email from users AS 名前;
+——————+
| 名前 |
+——————+
| 広瀬すず |
| 川口春奈 |
| 小松菜奈 |
| 新垣結衣 |
| 有村架純 |
| 本田翼 |
| 桐谷美玲 |
| otaku2@gmail.com |
| otaku3@gmail.com |
| otaku@gmail.com |
+——————+
10 rows in set (0.00 sec)

今回重複行はないので、結果は同じ。
重複がないことがわかっているなら、パフォーマンスの観点でUNIAN ALLを使ったほうが良い

スポンサーリンク
スポンサーリンク
スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

スポンサーリンク
スポンサーリンク