表示させる時に2つのテーブルの別々のカラムに格納されている値を1つに結合して表示させる練習を行なった。 free84
Sunday,July,28,2013 ○○値を表示させる時に、2つのテーブルの別々のカラムに格納されている値を結合させて表示させる。 /////////////////////////////同一のみ表示する//////////////////////////////// 同一の値のみの表示させる場合。 ↓甲db名 ↓甲テーブル名 ↓乙db名 ↓乙テーブル名 ↓甲カラム名 ↓乙カラム名 mysql> select * FROM sadachika_shop.sadachi_products INNER JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; ↑*テーブル名sadachi_productsにテーブル名kitta_productsを結合する意味。カラム名sada_categoryにカラム名kit_categoryを結合している。 ↓表示させるカラム名を変更してみる mysql> select sada_id, sada_name, kit_name AS sadakit_name from sadachika_shop.sadachi_products ->INNER JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; ↑*テーブル名sadachi_productsにテーブル名kitta_productsを結合する。カラム名sada_categoryにカラム名kit_categoryを結合している。 /////////////////////////////同一以外も表示する//////////////////////////////// カラム名sada_category と カラム名kit_category を 結合させて、カラム名kitta_productsを基準に値を表示させる。 ↓LEFT JOINではFROMの後、カラム名sadachi_productsが基準 mysql> select * FROM sadachika_shop.sadachi_products LEFT JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; ↑甲db名 ↑甲テーブル名 ↑乙db名 ↑乙テーブル名 ↑甲カラム名 ↑乙カラム名 カラム名sada_category と カラム名kit_category を 結合させて、カラム名kitta_productsを基準に値を表示させる。 ↓RIGHT JOINではJOINの後、カラム名kitta_productsが基準 mysql> select * FROM sadachika_shop.sadachi_products RIGHT JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; ↑甲db名 ↑甲テーブル名 ↑乙db名 ↑乙テーブル名 ↑甲カラム名 ↑乙カラム名 /////////////////////////////省略文、同一以外も表示する//////////////////////////////// ○○カラムの値を結合して表示 USING句 ↓カラム名sadachi_infoが基準となる。 select * FROM sadachika_shop.sadachi_info LEFT JOIN sadachika_shop.sadachi_stock USING(sada_id); ○○甲乙の同一の値みを表示する 2つのテーブルのカラム内の同一の値を基準に結合して表示させる。 ○テーブルの中身の表示 mysql> select * from sadachika_shop.sadachi_products; *甲db甲テーブル甲カラム sada_id sada_name sada_category sada_price sada_releasedate 1 牛乳 飲み物 198 2013-10-18 2 たこ焼き 食べ物 100 2012-07-25 3 りんご 果物 150 2013-01-08 4 ワイン 飲み物 250 2013-03-12 5 ピザ 食べ物 450 2011-05-03 6 ブドウ 果物 300 2013-07-16 7 にんじん 野菜 35 2013-03-17 8 コーラ 飲み物 98 2012-05-21 9 牛丼 食べ物 350 2011-02-25 10 キューイフルーツ 果物 160 2013-09-19 11 ほうれん草 野菜 145 2013-04-25 12 ビール 飲み物 220 2012-07-01 13 天丼 食べ物 460 2013-06-25 ○カラムの構造を見る mysql> show columns from sadachika_shop.sadachi_products; Field Type Null Key Default Extra sada_id int(11) NO PRI NULL sada_name varchar(20) YES NULL sada_category varchar(20) YES NULL sada_price int(11) YES NULL sada_releasedate date YES NULL 5 rows in set (0.00 sec) ○データベースを表示させる。 MySQL> show databases; kittaka ○データベースを作る mysql> create database kittaka_shop; Query OK, 1 row affected (0.13 sec) MySQL> show databases; Database Kittaka_shop *新しく作成された mysql> create table kittaka_shop.kitta_products (kit_id int not null primary key, kit_name varchar(20), kit_category varchar(20)); *乙db乙テーブル乙カラム Query OK, 0 rows affected (0.56 sec) ○カラムの構造を見る mysql> show columns from kittaka_shop.kitta_products; Field Type Null Key Default Extra kit_id int(11) NO PRI NULL kit_name varchar(20) YES NULL kit_category varchar(20) YES NULL 3 rows in set (0.08 sec) 1 酒屋 飲み物 2 外食産業 食べ物 3 フルーツパーラー 果物 4 八百屋 野菜 5 文房具店 筆記具 6 ファストファッション店 衣類 7 シューズショップ 靴 ○乙のテーブルへ値を入力する mysql> insert into kittaka_shop.kitta_products(kit_id, kit_name, kit_category) values(1, '酒屋', '飲み物'), ->(2, '外食産業', '食べ物'), (3, 'フルーツパーラー', '果物'), (4, '八百屋', '野菜'); Query OK, 4 rows affected (0.11 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into kittaka_shop.kitta_products(kit_id, kit_name, kit_category) values(5, '文房具屋', '筆記具'), ->(6, 'ファストファッション店', '衣類'), (7, 'シューズショップ', '靴'); 靴にスペース入をれたいが検索に Query OK, 3 rows affected (0.11 sec) Records: 3 Duplicates: 0 Warnings: 0 ○テーブルの中身の表示 mysql> select * from kittaka_shop.kitta_products; *乙db乙テーブル乙カラム kit_id kit_name kit_category 1 酒屋 飲み物 2 外食産業 食べ物 3 フルーツパーラー 果物 4 八百屋 野菜 5 文房具屋 筆記具 6 ファストファッション店 衣類 7 シューズショップ 靴' 7 rows in set (0.00 sec) ○○甲乙の同一の値のみを表示する ↓甲db名 ↓甲テーブル名 ↓乙db名 ↓乙テーブル名 ↓甲カラム名 ↓乙カラム名 mysql> select * from sadachika_shop.sadachi_products inner join kittaka_shop.kitta_products on sadachi_products.sada_category = kitta_products.kit_category; ↑*テーブル名sadachi_productsにテーブル名kitta_productsを結合する意味。カラム名sada_categoryにカラム名kit_categoryを結合している。 ↓kit_categoryとsada_categoryの同じ値を基準に結合するとこのようになる。sadaが飲み物の時はkitも飲み物、sadaが野菜のときはkitも野菜になっている sada_id sada_name sada_category sada_price sada_releasedate kit_id kit_name kit_category 1 牛乳 飲み物 198 2013-10-18 1 酒屋 飲み物 2 たこ焼き 食べ物 100 2012-07-25 2 外食産業 食べ物 3 りんご 果物 150 2013-01-08 3 フルーツパーラー 果物 4 ワイン 飲み物 250 2013-03-12 1 酒屋 飲み物 5 ピザ 食べ物 450 2011-05-03 2 外食産業 食べ物 6 ブドウ 果物 300 2013-07-16 3 フルーツパーラー 果物 7 にんじん 野菜 35 2013-03-17 4 八百屋 野菜 8 コーラ 飲み物 98 2012-05-21 1 酒屋 飲み物 9 牛丼 食べ物 350 2011-02-25 2 外食産業 食べ物 10 キューイフルーツ 果物 160 2013-09-19 3 フルーツパーラー 果物 11 ほうれん草 野菜 145 2013-04-25 4 八百屋 野菜 12 ビール 飲み物 220 2012-07-01 1 酒屋 飲み物 13 天丼 食べ物 460 2013-06-25 2 外食産業 食べ物 ○○表示するカラムを任意に選ぶ 甲乙の同一の値のみを表示する ↓表示させたいカラム名、カラム名が2つのテーブルで重複していないのでテーブル名の表記をしなくてもよい。(sadachi_products.sada_id) mysql> select sada_id, sada_name, kit_name from sadachika_shop.sadachi_products ->inner join kittaka_shop.kitta_products on sadachi_products.sada_category = kitta_products.kit_category; ↑*テーブル名sadachi_productsにテーブル名kitta_productsを結合する。カラム名sada_categoryにカラム名kit_categoryを結合している。 ○テーブルの中身の表示 sada_id sada_name kit_name 1 牛乳 酒屋 2 たこ焼き 外食産業 3 りんご フルーツパーラー 4 ワイン 酒屋 5 ピザ 外食産業 6 ブドウ フルーツパーラー 7 にんじん 八百屋 8 コーラ 酒屋 9 牛丼 外食産業 10 キューイフルーツ フルーツパーラー 11 ほうれん草 八百屋 12 ビール 酒屋 13 天丼 外食産業 ○○AS句を用い表示時に任意のカラム名に「乙kit_name → 甲sadakit_name」 ↓表示させるカラム名を変更してみる mysql> select sada_id, sada_name, kit_name AS sadakit_name from sadachika_shop.sadachi_products ->INNER JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; ↑*テーブル名sadachi_productsにテーブル名kitta_productsを結合する。カラム名sada_categoryにカラム名kit_categoryを結合している。 ↓カラム名が変わった kit_name → sadakit_name sada_id sada_name sadakit_name 1 牛乳 酒屋 2 たこ焼き 外食産業 3 りんご フルーツパーラー 4 ワイン 酒屋 5 ピザ 外食産業 6 ブドウ フルーツパーラー 7 にんじん 八百屋 8 コーラ 酒屋 9 牛丼 外食産業 10 キューイフルーツ フルーツパーラー 11 ほうれん草 八百屋 12 ビール 酒屋 13 天丼 外食産業 ○○レフトLEFT、FROMの後ろ、JOINの左の甲を基準とする。同一以外の値も表示 ○テーブルの中身の表示 mysql> select * from sadachika_shop.sadachi_products; sada_id sada_name sada_category sada_price sada_releasedate 1 牛乳 飲み物 198 2013-10-18 2 たこ焼き 食べ物 100 2012-07-25 3 りんご 果物 150 2013-01-08 4 ワイン 飲み物 250 2013-03-12 5 ピザ 食べ物 450 2011-05-03 6 ブドウ 果物 300 2013-07-16 7 にんじん 野菜 35 2013-03-17 8 コーラ 飲み物 98 2012-05-21 9 牛丼 食べ物 350 2011-02-25 10 キューイフルーツ 果物 160 2013-09-19 11 ほうれん草 野菜 145 2013-04-25 12 ビール 飲み物 220 2012-07-01 13 天丼 食べ物 460 2013-06-25 14 包丁 調理器具 525 20130728 15 お椀 食器 105 20130728 16 鍋 調理器具 1050 20130728 17 まな板 調理器具 420 20130728 18 シャツ 衣類 1575 20130728 ○値を入力する mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category, sada_price, sada_releasedate) ->values(14, '包丁', '調理器具', 525, 20130728), (15, 'お椀', '食器', 105, 20130728), (16, '鍋', '調理器具', 1050, 20130728), ->(17, 'まな板', '調理器具', 420, 20130728), (18, 'シャツ', '衣類', 1575, 20130728); Query OK, 5 rows affected (3.47 sec) Records: 5 Duplicates: 0 Warnings: 0 ○テーブルの中身の表示 mysql> select * from sadachika_shop.sadachi_products; *甲db名 甲テーブル名 sada_id sada_name sada_category sada_price sada_releasedate 1 牛乳 飲み物 198 2013-10-18 2 たこ焼き 食べ物 100 2012-07-25 3 りんご 果物 150 2013-01-08 4 ワイン 飲み物 250 2013-03-12 5 ピザ 食べ物 450 2011-05-03 6 ブドウ 果物 300 2013-07-16 7 にんじん 野菜 35 2013-03-17 8 コーラ 飲み物 98 2012-05-21 9 牛丼 食べ物 350 2011-02-25 10 キューイフルーツ 果物 160 2013-09-19 11 ほうれん草 野菜 145 2013-04-25 12 ビール 飲み物 220 2012-07-01 13 天丼 食べ物 460 2013-06-25 14 包丁 調理器具 525 2013-07-28*新しく追加した 15 お椀 食器 105 2013-07-28*新しく追加した 16 鍋 調理器具 1050 2013-07-28*新しく追加した 17 まな板 調理器具 420 2013-07-28*新しく追加した 18 シャツ 衣類 1575 2013-07-28*新しく追加した ○テーブルの中身の表示 mysql> select * from kittaka_shop.kitta_products; *乙db名 乙テーブル名 kit_id kit_name kit_category 1 酒屋 飲み物 2 外食産業 食べ物 3 フルーツパーラー 果物 4 八百屋 野菜 5 文房具屋 筆記具 6 ファストファッション店 衣類 7 シューズショップ 靴' お手本 SELECT table_name.col_name, ... FROM tbl_name1 LEFT JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2; SELECT table_name.col_name, ... FROM tbl_name1 RIGHT JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2; LEFT JOINではFROMの後 RIGHT JOINではJOINの後 ↓LEFT JOINではFROMの後、カラム名sadachi_productsが基準になっている JOINより左が基準となるためレフト mysql> select * FROM sadachika_shop.sadachi_products LEFT JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; *この甲カラム(sada_category)に乙カラム(kit_category)を結合する。 ↑甲db名 ↑甲テーブル名 ↑乙テーブル名↑乙カラム名 ↑甲カラム名 ↑乙db名 ↓甲(sada_category)が飲み物なら、乙(kit_category)も飲み物 ↓甲が飲み物のため、乙も飲み物になる 甲カラム(sada_category)を基準にするためどうしても乙カラムの値が足りなくなる そのためNULL表示が下に表示される。 sada_id sada_name sada_category sada_price sada_releasedate kit_id kit_name kit_category 1 牛乳 飲み物 198 2013-10-18 1 酒屋 飲み物 4 ワイン 飲み物 250 2013-03-12 1 酒屋 飲み物 8 コーラ 飲み物 98 2012-05-21 1 酒屋 飲み物 12 ビール 飲み物 220 2012-07-01 1 酒屋 飲み物 2 たこ焼き 食べ物 100 2012-07-25 2 外食産業 食べ物 5 ピザ 食べ物 450 2011-05-03 2 外食産業 食べ物 9 牛丼 食べ物 350 2011-02-25 2 外食産業 食べ物 13 天丼 食べ物 460 2013-06-25 2 外食産業 食べ物 3 りんご 果物 150 2013-01-08 3 フルーツパーラー 果物 6 ブドウ 果物 300 2013-07-16 3 フルーツパーラー 果物 10 キューイフルーツ 果物 160 2013-09-19 3 フルーツパーラー 果物 7 にんじん 野菜 35 2013-03-17 4 八百屋 野菜 11 ほうれん草 野菜 145 2013-04-25 4 八百屋 野菜 18 シャツ 衣類 1575 2013-07-28 6 ファストファッション店 衣類 14 包丁 調理器具 525 2013-07-28 NULL NULL NULL 15 お椀 食器 105 2013-07-28 NULL NULL NULL 16 鍋 調理器具 1050 2013-07-28 NULL NULL NULL 17 まな板 調理器具 420 2013-07-28 NULL NULL NULL ↓LEFT JOINではFROMの後、カラム名sadachi_productsが基準になっている mysql> select sada_id, sada_name, kit_name FROM sadachika_shop.sadachi_products LEFT JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; ↑表示するカラムの順番に記載する sada_id sada_name kit_name sada_id sada_name kit_name 1 牛乳 酒屋 4 ワイン 酒屋 8 コーラ 酒屋 12 ビール 酒屋 2 たこ焼き 外食産業 5 ピザ 外食産業 9 牛丼 外食産業 13 天丼 外食産業 3 りんご フルーツパーラー 6 ブドウ フルーツパーラー 10 キューイフルーツ フルーツパーラー 7 にんじん 八百屋 11 ほうれん草 八百屋 18 シャツ ファストファッション店 14 包丁 NULL 15 お椀 NULL 16 鍋 NULL 17 まな板 NULL ○○基準が逆になる ○○ライトRIGHT、JOINより右側の乙を基準とする。 カラム名sada_category と カラム名kit_category を 結合させて、カラム名kitta_productsを基準に値を表示させる。 ↓RIGHT JOINではJOINの後、カラム名kitta_productsが基準 JOINより右側の乙カラム名kitta_productsが基準になる だからライト mysql> select * FROM sadachika_shop.sadachi_products RIGHT JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; *この甲カラム(sada_category)に乙カラム(kit_category)を結合する。 ↑甲db名 ↑甲テーブル名 ↑乙db名 ↑乙テーブル名 ↑甲カラム名 ↑乙カラム名 ↓甲(sada_category)が果物なら、乙(kit_category)も果物 ↓甲が食べ物のため、乙も食べ物になる 乙カラム(kit_category)を基準にするためどうしても甲カラムの値が足りなくなる そのためNULL表示が下に表示される。 sada_id sada_name sada_category sada_price sada_releasedate kit_id kit_name kit_category 1 牛乳 飲み物 198 2013-10-18 1 酒屋 飲み物 2 たこ焼き 食べ物 100 2012-07-25 2 外食産業 食べ物 3 りんご 果物 150 2013-01-08 3 フルーツパーラー 果物 4 ワイン 飲み物 250 2013-03-12 1 酒屋 飲み物 5 ピザ 食べ物 450 2011-05-03 2 外食産業 食べ物 6 ブドウ 果物 300 2013-07-16 3 フルーツパーラー 果物 7 にんじん 野菜 35 2013-03-17 4 八百屋 野菜 8 コーラ 飲み物 98 2012-05-21 1 酒屋 飲み物 9 牛丼 食べ物 350 2011-02-25 2 外食産業 食べ物 10 キューイフルーツ 果物 160 2013-09-19 3 フルーツパーラー 果物 11 ほうれん草 野菜 145 2013-04-25 4 八百屋 野菜 12 ビール 飲み物 220 2012-07-01 1 酒屋 飲み物 13 天丼 食べ物 460 2013-06-25 2 外食産業 食べ物 18 シャツ 衣類 1575 2013-07-28 6 ファストファッション 衣類 NULL NULL NULL NULL NULL 5 文房具屋 筆記具 NULL NULL NULL NULL NULL 7 シューズショップ 靴 ↓RIGHT JOINではJOINの後、カラム名kitta_productsが基準 mysql> select sada_id, sada_name, kit_name, kit_id, kit_category FROM sadachika_shop.sadachi_products RIGHT JOIN kittaka_shop.kitta_products ON sadachi_products.sada_category = kitta_products.kit_category; ↑表示するカラムの順番に記載する sada_id sada_name kit_name kit_id kit_category 1 牛乳 酒屋 1 飲み物 2 たこ焼き 外食産業 2 食べ物 3 りんご フルーツパーラー 3 果物 4 ワイン 酒屋 1 飲み物 5 ピザ 外食産業 2 食べ物 6 ブドウ フルーツパーラー 3 果物 7 にんじん 八百屋 4 野菜 8 コーラ 酒屋 1 飲み物 9 牛丼 外食産業 2 食べ物 10 キューイフルーツ フルーツパーラー 3 果物 11 ほうれん草 八百屋 4 野菜 12 ビール 酒屋 1 飲み物 13 天丼 外食産業 2 食べ物 18 シャツ ファストファッション 6 衣類 NULL NULL 文房具屋 5 筆記具 NULL NULL シューズショップ 7 靴 ○○ USING句 同じ値以外も表示する その場合の表示はNULLとなる ○特定のデータベース内のテーブルを表示させる SHOW TABLES FROM sadachika_shop; *テーブルを作成する前に同名のテーブルがないか調べる ↓乙のカラム名と同名 mysql> create table sadachika_shop.sadachi_info (sada_id int not null primary key, sada_name varchar(10), sada_price int); *甲db名甲テーブル甲カラム名 ↓甲のカラム名と同名 mysql> create table sadachika_shop.sadachi_stock (sada_id int not null primary key, sada_stockcount int); *乙db名乙テーブル乙カラム名 ○カラムの構造を見る mysql> show columns from sadachika_shop.sadachi_info; Field Type Null Key Default Extra sada_id int(11) NO PRI NULL sada_name varchar(20) YES NULL sada_price int(11) YES NULL 3 rows in set (0.39 sec) mysql> show columns from sadachika_shop.sadachi_stock; Field Type Null Key Default Extra sada_id int(11) NO PRI NULL sada_stockcount int(11) YES NULL 2 rows in set (0.08 sec) 1 週刊誌 400 2 経済紙 700 3 新聞 130 4 漫画コミック 370 ○値を入力する mysql> insert into sadachika_shop.sadachi_info(sada_id, sada_name, sada_price) values(1, '週刊誌', 400), ->(2, '経済紙', 700), (3, '新聞', 130), (4, '漫画コミック', 370); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 1 65 2 124 4 8 mysql> insert into sadachika_shop.sadachi_stock(sada_id, sada_stockcount) values(1, 65), (2, 124), (4, 8); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 ○テーブルの中身の表示 mysql> select * from sadachika_shop.sadachi_info; sada_id sada_name sada_price *sada_idは他のテーブル名(sadachi_stock)のカラム名と同じ 1 週刊誌 400 2 経済紙 700 3 新聞 130 4 漫画コミック 370 mysql> select * from sadachika_shop.sadachi_stock; sada_id sada_stockcount *sada_idは他のテーブル名(sadachi_info)のカラム名と同じ 1 65 2 124 4 8 ○○カラムの値を結合して表示 USING句 ↓甲カラム名sadachi_infoが基準となる。 ↓カラム名が甲、乙ともに同じ名前ならUSING句を用い省略できる。 select * FROM sadachika_shop.sadachi_info LEFT JOIN sadachika_shop.sadachi_stock USING(sada_id); ↑甲db名 ↑甲テーブル名 ↑乙db名 ↑乙テーブル名 ↑甲乙同名のカラム名 sada_id sada_stockcount sada_price sada_stockcount 1 週刊誌 400 65 2 経済紙 700 124 4 漫画コミック 370 8 3 新聞 130 NULL
* 参考にしたサイトは→ こちら です。
これは赤色の文字例です。
これは青色の文字例です。