ホーム

⚾

ポケモンで学ぶSQL

作成日: 2024-08-02T13:38:00.000Z

最終更新: 2024-08-07T12:19:00.000Z

図鑑テーブル

参考画像

代替テキスト

表に変換

🤔 Noの25 → 025のような表記はアプリケーション側で対応がいいか

No. 名前 分類 写真 高さ 重さ 説明
25 ピカチュウ ねずみポケモン 025.png 0.4 6 しっぽをたてて　まわりのけはいをかんじとっている。だから　むやにみしっぽを　ひっぱると　かみつくよ。

テーブルに変換

CREATE TABLE pokemon_dictionary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(50) NOT NULL,
    picture VARCHAR(50) NOT NULL,
    height DECIMAL(3, 1) NOT NULL CHECK (height >= 0), -- 例: 身長として最大99.9mまで記録
    weight DECIMAL(4, 1) NOT NULL CHECK (weight >= 0), -- 例: 体重として最大999.9kgまで記録
    description TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 負数を許容しない
    • UNSIGNED
      • MySQL 8.0.17 では、FLOAT、DOUBLE および DECIMAL(およびすべてのシノニム) タイプのカラムに対して UNSIGNED 属性は非推奨になりました。
    • CHECK制約の使用を公式にて推奨
  • ENGINE
    <details>
    <summary>🤖 < AI解説</summary>
  • ENGINEは、テーブルのストレージエンジンを指定します。ストレージエンジンは、データの保存と管理の方法を決定します。MySQLにはいくつかのストレージエンジンがあり、それぞれに異なる特性があります。
    • InnoDB: トランザクションサポート、外部キー制約、行レベルロックをサポート。デフォルトのストレージエンジンとして広く使用されています。
    • MyISAM: 古いストレージエンジンで、テーブルロックを使用し、トランザクションはサポートしていません。主に読み取り性能を重視する場合に使用されます。
    • MEMORY: メモリ上にデータを保存し、高速なデータアクセスが可能ですが、サーバーの再起動でデータが失われます。

</details>

  • CHARSET
    <details>
    <summary>🤖 < AI解説</summary>

CHARSET（CHARACTER SET）は、テーブル内の文字列データの文字エンコーディングを指定します。文字セットは、テーブル内の文字列データの保存と表示に使用される文字コードを決定します。一般的な文字セットには、utf8やutf8mb4があります。

  • utf8: Unicodeの一部で、基本的な多言語サポートを提供しますが、一部の特殊な文字はサポートしていません。
  • utf8mb4: utf8の拡張版で、4バイトのUnicode文字をサポートし、絵文字や一部の特殊文字も扱えます。

</details>

データ挿入

INSERT INTO pokemon_dictionary (
    name, category, picture, height, weight, description
) VALUES
('ピカチュウ', 'ねずみポケモン', '025.png', 0.4, 6, 'しっぽをたてて　まわりのけはいをかんじとっている。だから　むやにみしっぽを　ひっぱると　かみつくよ。')

実行結果

代替テキスト

種族値テーブル

参考画像

代替テキスト

テーブルに変換

CREATE TABLE pokemon (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    type1 ENUM('Normal', 'Fire', 'Water', 'Electric', 'Grass', 'Ice', 'Fighting', 'Poison', 'Ground', 'Flying', 'Psychic', 'Bug', 'Rock', 'Ghost', 'Dragon', 'Dark', 'Steel', 'Fairy') NOT NULL,
    type2 ENUM('Normal', 'Fire', 'Water', 'Electric', 'Grass', 'Ice', 'Fighting', 'Poison', 'Ground', 'Flying', 'Psychic', 'Bug', 'Rock', 'Ghost', 'Dragon', 'Dark', 'Steel', 'Fairy'),
    hp INT NOT NULL,
    attack INT NOT NULL,
    defense INT NOT NULL,
    special_attack INT NOT NULL,
    special_defense INT NOT NULL,
    speed INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

データ挿入

INSERT INTO pokemon (
    name, type1, type2, hp, attack, defense, special_attack, special_defense, speed
) VALUES
('ピカチュウ', 'Electric', NULL, 35, 55, 40, 50, 50, 90)

実行結果

代替テキスト

番外編：抽出の練習

1. フシギダネ〜カメックスまでのデータを用意

ポケモン図鑑

INSERT INTO pokemon_dictionary (name, category, picture, height, weight, description) VALUES
('フシギダネ', 'たねポケモン', 'bulbasaur.png', 0.7, 6.9, '生まれたときから 背中に 不思議なタネが 植えてあって 成長するとともに 大きく育つ。'),
('フシギソウ', 'たねポケモン', 'ivysaur.png', 1.0, 13.0, '太陽の光を 浴びると 背中のツボミが 大きくなって 花が 咲くという。'),
('フシギバナ', 'たねポケモン', 'venusaur.png', 2.0, 100.0, '花が 大きく 育つと 甘い 香りが 漂い 始める。この香りは 戦う者の 心を 落ち着かせる。'),
('ヒトカゲ', 'とかげポケモン', 'charmander.png', 0.6, 8.5, 'しっぽには生まれたときからほのおがともっていて、元気なときは勢いよく燃え上がっている。'),
('リザード', 'かえんポケモン', 'charmeleon.png', 1.1, 19.0, '怒りが 爆発し しっぽが 赤い 炎に 包まれると 温度は 高くなる。'),
('リザードン', 'かえんポケモン', 'charizard.png', 1.7, 90.5, '口から 火を 吐き 岩も 溶かして しまう。意識しないで 火事に なることもある。'),
('ゼニガメ', 'かめのこポケモン', 'squirtle.png', 0.5, 9.0, '甲羅の 形と 表面の 溝に よって 水の 抵抗を 少なくし 素早く 泳げる。'),
('カメール', 'かめポケモン', 'wartortle.png', 1.0, 22.5, '甲羅に 潜み 獲物を 待つ。水中で 素早く 動くために しっぽと 耳を 使って バランスを 取る。'),
('カメックス', 'こうらポケモン', 'blastoise.png', 1.6, 85.5, '甲羅の中に 強力な 水鉄砲の ノズルがある。 高速で 水を 噴射し 攻撃する。');

種族値

INSERT INTO pokemon (name, type1, type2, hp, attack, defense, special_attack, special_defense, speed) VALUES
('フシギダネ', 'Grass', 'Poison', 45, 49, 49, 65, 65, 45),
('フシギソウ', 'Grass', 'Poison', 60, 62, 63, 80, 80, 60),
('フシギバナ', 'Grass', 'Poison', 80, 82, 83, 100, 100, 80),
('ヒトカゲ', 'Fire', NULL, 39, 52, 43, 60, 50, 65),
('リザード', 'Fire', NULL, 58, 64, 58, 80, 65, 80),
('リザードン', 'Fire', 'Flying', 78, 84, 78, 109, 85, 100),
('ゼニガメ', 'Water', NULL, 44, 48, 65, 50, 64, 43),
('カメール', 'Water', NULL, 59, 63, 80, 65, 80, 58),
('カメックス', 'Water', NULL, 79, 83, 100, 85, 105, 78);

2. 条件を考える

  1. 素早さが50以上
  2. 高さが1.0以上

3. SELECT文を考える

それぞれの条件でSELECT文を作成

  1. 素早さが50以上
SELECT name
FROM pokemon
WHERE speed >= 50;
  1. 高さが1.0以上
SELECT name
FROM pokemon_dictionary
WHERE height >= 1;

全ての条件でSELECT文を作成

SELECT pd.name, speed, height --　取得したいカラムの指定
FROM pokemon AS p
LEFT JOIN pokemon_dictionary AS pd -- 2つのテーブルを結合する
ON p.id = pd.id AND p.name = pd.name -- idと名前が一致するものだけにする
WHERE speed >= 50 AND height >= 1; -- 条件

結果

🤔 第二進化系以降のみ残る

代替テキスト

参考