こんにちは。ジョニー別府です。イェー!
idols
, brands
, type
という別々のテーブルに入っているデータを基に stage
のデータを作りたい!
テーブルの構成
idols
table: アイドルハニーたちの名前とタイプ
+----+-----------------+---------+ | id | name | type_id | +----+-----------------+---------+ | 1 | 星宮いちご | 1 | | 2 | 霧矢あおい | 2 | | 3 | 紫吹蘭 | 3 | | 4 | 有栖川おとめ | 4 | | 5 | 藤堂ユリカ | 2 | | 6 | 神崎美月 | 3 | | 7 | 夏樹みくる | 4 | | 8 | 大空あかり | 1 | +----+-----------------+---------+
brands
table: ブランド名
+----+----------------+---------+ | id | name | type_id | +----+----------------+---------+ | 1 | Angely Sugar | 1 | | 2 | FUTURING GIRL | 2 | | 3 | SPICY AGEHA | 3 | | 4 | HAPPY RAINBOW | 4 | | 5 | LoLi GoThiC | 2 | | 6 | LOVE MOONRISE | 3 | | 7 | ViVid Kiss | 4 | | 8 | Dreamy Crown | 1 | +----+----------------+---------+
type
table: キュート, クール, セクシー, ポップのタイプ
+----+------+ | id | name | +----+------+ | 1 | cute | | 2 | cool | | 3 | sexy | | 4 | pop | +----+------+
idols.type_id
と type.id
、brans.type_id
と type.id
は紐付いているけど、idole
と brands
には関連性がありません。
これらのテーブルのデータを使って次のような stage
テーブルにデータを入れたいんだ。イェー
stage
table
+----+------+------+-------+------+------+ | id | name | idol | brand | type | date | +----+------+------+-------+------+------+
これを実現するには2つのステップが必要になりますんだ イェー
- 関連性のないテーブルを結合したデータを SELECT 文でつくる
- SELECT で作成されたデータをテーブルに INSERT する
1. 関連性のないテーブルを結合する方法
テーブルを,
区切りで複数指定すればOK
条件を指定しないと全ての組み合わせ(デカルト積)で表示される
SELECT * FROM idols, type;
id | name | type_id | id | name |
---|---|---|---|---|
1 | 星宮いちご | 1 | 1 | cute |
1 | 星宮いちご | 1 | 2 | cool |
1 | 星宮いちご | 1 | 3 | sexy |
1 | 星宮いちご | 1 | 4 | pop |
2 | 霧矢あおい | 2 | 1 | cute |
2 | 霧矢あおい | 2 | 2 | cool |
... | ... | ... | ... | ... | ... |
e.g. idol
星宮いちご, brand
Angely Sugar, type
cute なデータを作成
SELECT idols.name AS "idol", brands.name AS "brand", type.name AS "type" FROM idols, brands, type WHERE idols.name LIKE "星宮%いちご" AND brands.name = "Angely Sugar" AND type.name = "cute";
👇
idol | brand | type |
---|---|---|
星宮いちご | Angely Sugar | cute |
⚠️ WHEREの条件はAND
にしないと全ての組み合わせが取得されてしまいます。
JOIN を使う場合は FROM節のテーブルを( )
で囲う
FROM で複数テーブルを指定している かつ JOIN を使用している時、FROMで指定しているテーブルを( )
で囲わないとエラーに場合がある
e.g. type
が cute な idols
で、brands
が Dreamy Crown なデータを作成
SELECT idols.name AS "idol", brands.name AS "brand", type.name AS "type" FROM ( idols, brands ) INNER JOIN type ON idols.type_id = type.id WHERE type.name = "cute" AND brands.name = "Dreamy Crown";
👇
idol | brand | type |
---|---|---|
星宮いちご | Dreamy Crown | cute |
大空あかり | Dreamy Crown | cute |
テーブルを( )
で囲ってなくてエラーになる場合
SELECT idols.name AS "idol", brands.name AS "brand", type.name AS "type" FROM idols, brands INNER JOIN type ON idols.type_id = type.id WHERE type.name = "cute" AND brands.name = "Dreamy Crown";
👉 Unknown column 'idols.type_id' in 'on clause'
結合条件が存在しない場合、
INNER JOIN
と,
(カンマ) は意味的に同等です。どちらも、指定されたテーブル間のデカルト積を生成します (つまり、最初のテーブル内のすべての各行が 2 番目のテーブル内のすべての各行に結合されます)。ただし、カンマ演算子の優先順位は、
INNER JOIN
、CROSS JOIN
、LEFT JOIN
などの優先順位より低くなります。結合条件が存在するときにカンマ結合をほかの結合型と混在させた場合は、「カラム 'col_name' は 'on clause' にはありません」 という形式のエラーが発生する可能性があります。この問題への対処に関する情報は、このセクションのあとの方で提供します。
cf. MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.2 JOIN 構文
つまり、上記の例ではJOINの優先度が高くなるために
brands INNER JOIN type ON idols.type_id = type.id
と解釈され Unknown column 'idols.type_id' in 'on clause'
というエラーが発生するということのようです。
FROMのテーブルの順番を変更すればエラーになりませんが、取ってくるテーブルを ( )
で囲い明示的にグループ化する方が間違いがないと思います。
2. SELECT した結果をテーブルに INSERT する方法
通常の INSERT 文
INSERT INTO ${table} (${col_1}, ${col_2}, ...) VALUES (${val_1_1}, ${val_1_2}, ...), (${val_2_1}, ${val_2_2}, ...), ...;
SELECT した値を INSERT する場合は VALUES の代わりに直接 SELECT 文を書けばOK
INSERT INTO ${table} (${col_1}, ${col_2}, ...) SELECT ${col1_value_column}, ${col2_value_column}, ... FROM ${table} WHERE ...;
SELECT するカラム部分に直接固定値を指定することもできる
INSERT INTO ${table} (${col_1}, ${col_2}, ${col_3}...) SELECT ${col1_value_column}, "static value", now() ... FROM ${table} WHERE ...;
上記の例では col2
には"static value"
が、col3
にはnow()
で取得された時間が入るようになります。
複数のテーブルから取得したデータを INSERT する
e.g. 1
作成したいデータ
name: 大スター宮いちごまつり
idol: 星宮いちご
brand: Angely Sugar
type: cute
INSERT INTO stage (name, idol, brand, type, date) SELECT "大スター宮いちごまつり", idols.name, brands.name, type.name, now() FROM idols, brands, type WHERE idols.name LIKE "星宮%いちご" AND brands.name = "Angely Sugar" AND type.name = "cute" ORDER BY idols.id ASC LIMIT 1;
👇 (名前を直接入れるならSELECT使う意味殆ど無いけど…)
id | name | idol | brand | type | date |
---|---|---|---|---|---|
1 | 大スター宮いちごまつり | 星宮いちご | Angely Sugar | cute | 2019-04-05 |
e.g. 2
作成したいデータ
name: Dreamy party
idol: type がcute
のアイドル
brand: Dreamy Crown
type: cute
INSERT INTO stage (name, idol, brand, type, date) SELECT "Dreamy party", idols.name, brands.name, type.name, now() FROM ( idols, brands ) INNER JOIN type ON idols.type_id = type.id WHERE type.name = "cute" AND brands.name = "Dreamy Crown" ORDER BY idols.id ASC;
👇
id | name | idol | brand | type | date |
---|---|---|---|---|---|
2 | Dreamy party | 星宮いちご | Dreamy Crown | cute | 2019-04-05 |
3 | Dreamy party | 大空あかり | Dreamy Crown | cute | 2019-04-05 |
これでジョニー別府もハニーたちのステージの情報のデータを自在に作れるようになりました。イェー!!!
感想
SELECT はデータを取ってくるというイメージが強かったのですが、tableを複数選択したり、固定値を入れられたり、DBにあるデータをつかってテーブルを作るSQLコマンドなんだなーと感じました。
SELECT 文が上手くなれば効率的にデータ作ったりできるようになりそうです!イェー
[参考]
- テーブルから取得したデータを別のテーブルに追加 | SQLite入門
- MySQLのSELECT結果(サブクエリ)でUPDATEをする - 塩焼きブログ
- MySQLで全く関係のないテーブルをJOINする - まーぽんって誰がつけたの?
- MySQL5で「Unknown column … in ‘on clause’」エラーが出る場合の対処法 | colori
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.2 JOIN 構文
- パターンマッチングで比較(LIKE演算子) - データの取得 - MySQLの使い方
- 出版社/メーカー: Happinet(SB)(D)
- 発売日: 2015/06/02
- メディア: Blu-ray
- この商品を含むブログ (7件) を見る
アイカツ!ミュージックアワード みんなで賞をもらっちゃいま SHOW! [レンタル落ち]
- 発売日: 2016/02/02
- メディア: DVD
- この商品を含むブログを見る