MySQL の便利機能をメモ
先日、台風が来てるというのに終電間際の電車で寝過ごし、どしゃ降りの中土地勘のない場所を 1 時間ほど歩いた kimoto です。ネタとして楽しんでいた iOS6 の地図をこんなに恨めしく思う事になるとは…。いまだに脚が痛い。
今回は MySQL で、頻繁に使うことはないものの便利なためちょいちょい使う。そして、その度にググっている…。
そんな機能達をまとめてメモっておこうと思います。
目次はこんな感じ。ではどうぞ。
まずはこんなデータを用意してみました
mysql> SELECT * FROM users;
+----+-----------+-----+-----+------+
| id | name | sex | age | have |
+----+-----------+-----+-----+------+
| 1 | sato | 1 | 30 | 2 |
| 2 | suzuki | 1 | 15 | 1 |
| 3 | takahashi | 2 | 26 | 0 |
| 4 | tanaka | 1 | 19 | 10 |
| 5 | ito | 2 | 49 | 12 |
| 6 | watanabe | 1 | 51 | 8 |
| 7 | yamamoto | 2 | 34 | 4 |
| 8 | nakamura | 2 | 22 | 16 |
| 9 | kobayashi | 1 | 23 | 2 |
| 10 | kato | 2 | 31 | 5 |
+----+-----------+-----+-----+------+
サンプルのデータはこんな感じです。users というテーブルに、10件のデータを入れてみました。
name、sex、age はまあいいとして、have というのは適当です。みな、何かを複数持っているという事で。
「name」は、全国の名字ランキングを上から入れたもので他意はありませんのであしからず。
こうしてみると「山田」さんって10位に入ってないんですが、テストなどで良く使われるのはなぜなんでしょうね…。
年齢ももちろん適当です。
GROUP で集計したデータの合計値を出力したい！
男女での「何か」の所持数の比較をしたいとします。その場合 GROUP BY を使いますよね。
mysql> SELECT sex, SUM(have) FROM users GROUP BY sex;
+-----+-----------+
| sex | SUM(have) |
+-----+-----------+
| 1 | 23 |
| 2 | 37 |
+-----+-----------+さて、これでさらに男女の合計を出したい、という時に便利なのが「WITH ROLLUP」です。
mysql> SELECT sex, SUM(have) FROM users GROUP BY sex WITH ROLLUP;
+------+-----------+
| sex | SUM(have) |
+------+-----------+
| 1 | 23 |
| 2 | 37 |
| NULL | 60 |
+------+-----------+このように、合計値を算出してくれます。
結果を結合した状態で取得したい！
GROUP_CONCAT を使えば、条件ごとで分けた物の文字列をまとめることができます。
今度は男女別で、名前を羅列させてみましょう。
mysql> SELECT GROUP_CONCAT(name SEPARATOR ', '), sex FROM users GROUP BY sex ORDER BY id;
+-------------------------------------------+-----+
| GROUP_CONCAT(name SEPARATOR ', ') | sex |
+-------------------------------------------+-----+
| sato, kobayashi, watanabe, tanaka, suzuki | 1 |
| ito, takahashi, yamamoto, nakamura, kato | 2 |
+-------------------------------------------+-----+「SEPARATOR」で好きな物で区切れます。
そして、これと先ほどの「WITH ROLLUP」を組み合わせれば、最初のデータもさらに有用なものに。
mysql> SELECT GROUP_CONCAT(name SEPARATOR ', '), sex, SUM(have) FROM users GROUP BY sex WITH ROLLUP;
+-------------------------------------------------------------------------------------+------+-----------+
| GROUP_CONCAT(name SEPARATOR ', ') | sex | SUM(have) |
+-------------------------------------------------------------------------------------+------+-----------+
| sato, kobayashi, watanabe, tanaka, suzuki | 1 | 23 |
| ito, takahashi, yamamoto, nakamura, kato | 2 | 37 |
| sato, kobayashi, watanabe, tanaka, suzuki, ito, takahashi, yamamoto, nakamura, kato | NULL | 60 |
+-------------------------------------------------------------------------------------+------+-----------+名前も全て合わせてくれました。
ただ、順番が微妙なのが難点ですが…。
SELECT の結果を INSERT したい！
users に関連したテーブル、「services」が追加され、サービスを利用する人の id がここに格納されるとします。
mysql> DESC services;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| start_date | datetime | NO | | NULL | |
+------------+----------+------+-----+---------+----------------+user_id は users の ID、start_date はサービス利用開始日とします。
今回、女性のみ特別に、自動でサービスを使えるようにする、という事になりました。
そういう場合に「INSERT … SELECT」で一気に流し込めます。
まず、INSERT したいデータを確認。
SELECT id, NOW() FROM users WHERE sex = 2;
+----+---------------------+
| id | NOW() |
+----+---------------------+
| 3 | 2012-10-19 15:58:42 |
| 5 | 2012-10-19 15:58:42 |
| 7 | 2012-10-19 15:58:42 |
| 8 | 2012-10-19 15:58:42 |
| 10 | 2012-10-19 15:58:42 |
+----+---------------------+これをそのまま INSERT の VALUES の部分に入れれば OK です。
INSERT INTO services (user_id, start_date) (SELECT id, NOW() FROM users WHERE sex = 2);注意としては、「VALUES」も書かない、と言うことです。ここを忘れてググりがちです。
これで入ったデータは以下。
mysql> SELECT * FROM services;
+----+---------+---------------------+
| id | user_id | start_date |
+----+---------+---------------------+
| 1 | 3 | 2012-10-19 16:00:51 |
| 2 | 5 | 2012-10-19 16:00:51 |
| 3 | 7 | 2012-10-19 16:00:51 |
| 4 | 8 | 2012-10-19 16:00:51 |
| 5 | 10 | 2012-10-19 16:00:51 |
+----+---------+---------------------+先ほど SELECT で出力したデータがまるっと INSERT されたのがわかると思います。
結果をランダムに取得したい！
簡易的に結果をランダムで取得したい場合は、「ORDER BY RAND()」で OK です。
mysql> SELECT * FROM users ORDER BY RAND();
+----+-----------+-----+-----+------+
| id | name | sex | age | have |
+----+-----------+-----+-----+------+
| 3 | takahashi | 2 | 26 | 0 |
| 7 | yamamoto | 2 | 34 | 4 |
| 5 | ito | 2 | 49 | 12 |
| 9 | kobayashi | 1 | 23 | 2 |
| 2 | suzuki | 1 | 15 | 1 |
| 1 | sato | 1 | 30 | 2 |
| 4 | tanaka | 1 | 19 | 10 |
| 8 | nakamura | 2 | 22 | 16 |
| 10 | kato | 2 | 31 | 5 |
| 6 | watanabe | 1 | 51 | 8 |
+----+-----------+-----+-----+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY RAND();
+----+-----------+-----+-----+------+
| id | name | sex | age | have |
+----+-----------+-----+-----+------+
| 1 | sato | 1 | 30 | 2 |
| 4 | tanaka | 1 | 19 | 10 |
| 10 | kato | 2 | 31 | 5 |
| 5 | ito | 2 | 49 | 12 |
| 8 | nakamura | 2 | 22 | 16 |
| 2 | suzuki | 1 | 15 | 1 |
| 7 | yamamoto | 2 | 34 | 4 |
| 6 | watanabe | 1 | 51 | 8 |
| 9 | kobayashi | 1 | 23 | 2 |
| 3 | takahashi | 2 | 26 | 0 |
+----+-----------+-----+-----+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY RAND();
+----+-----------+-----+-----+------+
| id | name | sex | age | have |
+----+-----------+-----+-----+------+
| 10 | kato | 2 | 31 | 5 |
| 9 | kobayashi | 1 | 23 | 2 |
| 5 | ito | 2 | 49 | 12 |
| 6 | watanabe | 1 | 51 | 8 |
| 8 | nakamura | 2 | 22 | 16 |
| 7 | yamamoto | 2 | 34 | 4 |
| 1 | sato | 1 | 30 | 2 |
| 4 | tanaka | 1 | 19 | 10 |
| 2 | suzuki | 1 | 15 | 1 |
| 3 | takahashi | 2 | 26 | 0 |
+----+-----------+-----+-----+------+
10 rows in set (0.00 sec)「LIMIT」をつけてやれば、取ってくるデータ数も制限できます。
まとめ
ということで、たまに使うときに忘れてしまっている事が多い物を個人的にメモしてみました。
参考になれば幸いです。
