表示させる時に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



* 参考にしたサイトは→ こちら です。

これは赤色の文字例です。

これは青色の文字例です。