表示させる時に２つのテーブルの別々のカラムに格納されている値を１つに結合して表示させる練習を行なった。 free84
Sunday,July,28,2013
○○値を表示させる時に、２つのテーブルの別々のカラムに格納されている値を結合させて表示させる。
/////////////////////////////同一のみ表示する////////////////////////////////
同一の値のみの表示させる場合。
↓甲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);
○○甲乙の同一の値みを表示する
２つのテーブルのカラム内の同一の値を基準に結合して表示させる。
○テーブルの中身の表示
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 外食産業 食べ物
○○表示するカラムを任意に選ぶ 甲乙の同一の値のみを表示する
↓表示させたいカラム名、カラム名が２つのテーブルで重複していないのでテーブル名の表記をしなくてもよい。(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
* 参考にしたサイトは→ こちら です。
これは赤色の文字例です。
これは青色の文字例です。