10複数のテーブルを利用する(備忘録)→ > 01p > 02p > 03p > 04p > 05p > END
10複数のテーブルを利用する(p181)
(1)《複数のテーブルのレコードを合わせて表示する》p181
(2)<複数のテーブルを結合して表示する(内部結合)>
(3)<複数のテーブルのすべてのレコードを表示する(外部結合)p200>
(4)自己結合
(5)《SELECTしたレコードからSELECTする(サブクエリ)p213》
<まとめ>
<チェック>
<練習問題>
END
10 複数のテーブルを利用する
単純にINSERT、SELECT、DELETEするだけでデータベースが運用できるわけではありません。実践では複雑に絡み合った複数のテーブルを使って大量のデータを扱い、処理の効率を考え、そして不慮のデータ消滅も許されません。MySQLの勉強も佳境を迎えています。条件を設定した抽出、編集から、リレーショナルの名にふさわしい複数のテーブルの利用。そして「ビュー」「ストアドプロシージャ」「トランザクション」「ファイル操作」という、より実践的で必要とされる内容を解説します。
☆CHAPTER08⇒いろんな条件で抽出する
☆CHAPTER09⇒テーブルを編集する
★CHAPTER10⇒複数のテーブルを利用する
☆CHAPTER11⇒ビューを使いこなす
☆CHAPTER12⇒ストアドプロシージャを使いこなす
☆CHAPTER13⇒トランザクションを使いこなす
☆CHAPTER14⇒ファイルを使ったやり取り
★CHAPTER10⇒複数のテーブルを利用する(p181)
ここでは複数のテーブルを扱う方法を紹介します。そもそもリレーショナルデータベースというのは、「リレーショナル」(Relational:関連のある)なところが特徴です。インターネット上の通販サイトなどで実際に稼動しているMySQLデータベースでは、恐ろしいほどたくさんのテーブルが絡み合っています。ここでは、絡み合ったテーブルを結合し、組み合わせて使うテクニックを学びます。
(1)《テーブルのカラム構造やデータのコピー》
(1)《複数のテーブルのレコードを合わせて表示する》p181
(1-1)<複数のテーブルの利用で使うサンプル確認>
次は「社員情報テーブルtd1」「売上情報テーブルtb」、そして新たに登場する「別の営業所の社員情報テーブルtb2」及び「社員出身県情報テーブルtb3」です。 あらかじめ「tb2」「tb3」の準備をしておきます。
●各テーブルが次のようになっているものとして解説 p181
●tb(売上情報テーブル)
mysql> SELECT * FROM tb; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 101 | 4 | | A102 | 54 | 5 | | A104 | 181 | 4 | | A101 | 184 | 4 | | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A104 | 93 | 5 | | A103 | 12 | 6 | | A107 | 87 | 6 | +------+------+------+ 10 rows in set (0.00 sec) |
●tb1(社員情報テーブル)
mysql> SELECT * FROM tb1; +------+------+------+ | bang | nama | tosi | +------+------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | +------+------+------+ 5 rows in set (0.00 sec) |
●tb2(別の営業所の社員情報テーブル)
mysql> SELECT * FROM tb2; +------+------+------+ | bang | nama | tosi | +------+------+------+ | A106 | 中村 | 26 | | A107 | 田中 | 24 | | A108 | 鈴木 | 23 | | A109 | 村井 | 25 | | A110 | 吉田 | 27 | +------+------+------+ 5 rows in set (0.00 sec) |
●tb3(社員出身県情報テーブル)
mysql> SELECT * FROM tb3; +------+----------+ | bang | ken | +------+----------+ | A101 | 東京都 | | A102 | 埼玉県 | | A103 | 神奈川県 | | A104 | 北海道 | | A105 | 静岡県 | +------+----------+ 5 rows in set (0.00 sec) |
(1-2)<複数の抽出結果を合わせて表示 p182>
複数のテーブルからデータを取り出し、寄せ集めて表示してみましよう。単純に複数のテーブルから抽出したデータを集めるときには、「UNION」を使います。 たとえば、「昨年の顧客テーブルから得意先を抽出、さらに「今年の顧客テーブルから抽出して両方を一度に表示する」というような使い方をします。構造が異なるテーブルからデータを集めることができます。いっしょに表示するカラムのデータ型は、基本的に一致している必要があります。ただし、実際にはデータ型が異なっても、MySQLは「集められるものは集めてしまう」という、大らかな対応をすることが多い。
◇図10-1 UNION
図省略 ※構文は簡単です。通常の「SELECT カラム名 FROM テーブル名」を「UNION」で結ぶだけです。 |
●書式⇒2つのテーブルのレコードを集めて表示
SELECT カラム名1 FROM テーブル2 UNION SELECT カラム名2 FROM テーブル名2; ※一行で書くとわかりにくいので、次のように記述。 |
●書式⇒2つのテーブルのレコードを集めて表示(改行+インデント)
SELECT カラム名1 FROM テーブル名1 UNION SELECT カラム名2 FROM テーブル名2; ※「SELECT~FROM~」という2つの結果を、「UNION」で集めているわけです。 まずは、単純に、同じカラム構造をしたテーブル「tb1」とテーブル「tb2」のレコードを集めることにしましょう。すべてのカラムを集めるので「SELECT * FROM~」を「UNION」でつないで記述してください。 |
●実行される内容 p183
●操作手順
①次を実行する SELECT * FROM tb1 UNION SELECT * FROM tb2; |
□【実行結果】
mysql> SELECT * -> FROM tb1 -> UNION -> SELECT * -> FROM tb2; +------+------+------+ | bang | nama | tosi | +------+------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | | A106 | 中村 | 26 | | A107 | 田中 | 24 | | A108 | 鈴木 | 23 | | A109 | 村井 | 25 | | A110 | 吉田 | 27 | +------+------+------+ 10 rows in set (0.02 sec) mysql> (SELECT * FROM tb1) UNION (SELECT * FROM tb2); +------+------+------+ | bang | nama | tosi | +------+------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | | A106 | 中村 | 26 | | A107 | 田中 | 24 | | A108 | 鈴木 | 23 | | A109 | 村井 | 25 | | A110 | 吉田 | 27 | +------+------+------+ 10 rows in set (0.00 sec) mysql> ※見やすくするために、それぞれの「SELECT~」を()で囲んで記述することもあります。 (SELECT * FROM tb1) UNION (SELECT * FROM tb2); |
(1-3)<3つ以上のテーブルから「UNION」で集める>p184
前項では「bang」「A102」「A104」「A107」のレコードをわざわざ1つずつ「SELECT」して、「UNION」で集めた例です。
①
(SELECT * FROM tb WHERE bang='A102')
UNION
(SELECT * FROM tb WHERE bang='A103')
UNION
(SELECT * FROM tb WHERE bang='A104')
UNION
(SELECT * FROM tb WHERE bang='A107');
結局「A101」以外を抽出しているので
②
SELECT *
FROM tb
WHERE bang
NOT IN('A101');
③あるいは
SELECT *
FROM tb
WHERE bang
IN('A102','A103','A104','A107');
を実行すると同じ結果になります。
なお、表示されるレコードは同じですが、順番は異なるので注意。
(1-4)<条件を付けて複数の抽出結果を合わせて表示する>p185
「UNION」で複数のレコードを「SELECT」するときには条件を付けて集めるには、その命令の最後に「WHERE 条件」を付けます。
次に2種類の抽出をして、その社員番号(bang)を集めてみます。
①テーブル「tb」売上(uria)が200以上のレコードを持つ社員番号(bang)
②テーブル「tb1」で年齢(tosi)が35以上のレコードを持つ社員番号(bang)
●実行される内容 p186
●操作手順
①次を実行する (SELECT bang FROM tb WHERE uria>=200) UNION (SELECT bang FROM tb1 WHERE tosi>=35); |
□【実行結果】
mysql> (SELECT -> bang -> FROM -> tb -> WHERE uria>=200) -> -> UNION -> (SELECT -> bang -> FROM -> tb1 -> WHERE tosi>=35); +------+ | bang | +------+ | A101 | | A102 | | A105 | +------+ 3 rows in set (0.61 sec) mysql> |
(1-5)<複数の抽出をあわせて表示する(重複を許容)>
前項では、整数と1人ずつの社員番号と氏名が表示されました。 この結果は、複数のテーブルからレコードを集めています。つまり①で「A101」「A102」が抽出され②からは「A101」「A105」が抽出されているのです。 ところが「A101」は1レコードしか表示されません。これは、抽出するときに「重複しているデータを省く」という作業も合わせて実行されているのです。 ※大量のデータでこの「重複を省く」作業が入ると、無視できない待ち時間が生じます。従って、このような場合には、「UNION」にALLを付けます。 ※前項のUNIONの部分を「UNION ALL」とすると、次のようになります。
●操作手順
①次を実行します (SELECT bang FROM tb WHERE uria>=200) UNION (SELECT bang FROM tb1 WHERE tosi>=35); |
□【実行結果】
mysql> (SELECT bang FROM tb WHERE uria>=200) -> UNION -> (SELECT bang FROM tb1 WHERE tosi>=35); +------+ | bang | +------+ | A101 | | A102 | | A105 | +------+ 3 rows in set (0.48 sec) mysql> ※「A101」は両方の処理により、2件表示されます。 |
(2)<複数のテーブルを結合して表示する(内部結合)>
前項の「UNION」は、単純に複数のテーブルのレコードを合わせるものでした。ここでは内部結合「JOIN」について解説します。
(2-1)<別のテーブルのデータも使って処理する>
複数のテーブルを何かのキーで結びつけて処理するのが「結合」です。ここまで、テーブル「tb」と「tb1」を扱ってきました。会社の「売上情報」と「社員情報」を、分散して管理しているモデルです。 テーブル「tb」で売上の処理を行なって、職員の氏名は必要なときにテーブル「tb1」から引っ張ってくることができます。
●テーブル p188
※さて、テーブル「tb」の「bang」が「A101」なのは年齢40の「佐藤」さんですが、テーブル「tb」だけだと社員の氏名がわかりません。そこで、売上情報テーブル「tb」と社員情報テーブル「tb1」を、両方に共通の社員番号のカラム「bang」をキーにして結合して表示してみましょう。 ※2つのテーブルを結合して使うには、JOINを使います。
●書式⇒2つのテーブルを結合する
SELECT カラム名 FROM テーブル1 JOIN 結合するテーブル2 ON テーブル1のカラム=テーブル2のカラム; ※ON~、2つのテーブルを結び付るキーとなるカラムの条件を書きます。たとえば、テーブル「tb」とテーブル「tb1」なら、カラム「bang」が共通の値を持っているので、これを設定します。次のようになります。 ON tb.bang=tb1.bang では、具体的に処理してみましょう。表示するカラムは選択できますが、取り敢えずは「*」で全部表示してみましょう。 |
●実行される内容 p189
●操作手順
①次を実行する SELECT * FROM tb JOIN tb1 ON tb.bang=tb1.bang; |
□【実行結果】
mysql> SELECT * -> FROM tb -> JOIN tb1 -> ON tb.bang=tb1.bang; +------+------+------+------+------+------+ | bang | uria | tuki | bang | nama | tosi | +------+------+------+------+------+------+ | A103 | 101 | 4 | A103 | 中川 | 20 | | A102 | 54 | 5 | A102 | 高橋 | 28 | | A104 | 181 | 4 | A104 | 渡辺 | 23 | | A101 | 184 | 4 | A101 | 佐藤 | 40 | | A103 | 17 | 5 | A103 | 中川 | 20 | | A101 | 300 | 5 | A101 | 佐藤 | 40 | | A102 | 205 | 6 | A102 | 高橋 | 28 | | A104 | 93 | 5 | A104 | 渡辺 | 23 | | A103 | 12 | 6 | A103 | 中川 | 20 | +------+------+------+------+------+------+ 9 rows in set (0.00 sec) mysql> |
●内部結合
結果を見ると、テーブル「tb1」から「bang」が一致するレコードを選び出し、結合して表示しています。(「A107」のレコードが表示されない理由は前項参照)。
このように一致するレコードを取り出すような結合を、内部結合といいます。「内部結合」であることをはっきりと示すときは、「JOIN」の部分を「INNER JOIN」と記述することもできます。次のように「INNER」を付けても、結果は同じ。 SELECT * FROM tb INNER JOIN tb1 ON tb.bang=tb1.bang; |
◇図10-2 内部結合 p191
図省略 ※「内部結合」に対して、一方のテーブルにあるレコードは一致していなくてもすべて取り出すことを「外部結合」といいます。 |
(2-2)<カラムを選んで表示するp191>
前項では、「*」でカラムのすべてを表示しました。今度は、表示するカラムを選ぶこととしましょう。この場合、カラムの記述は「テーブル名.カラム名」とします。
たとえばテーブル「tb」のカラム「uria」なら「tb.uria」となります。表示する順番も指定した通りになります。同じカラムを何回表示してもかまいません。たとえば、
SELECT tb.bang,tb1.nama,tb.uria
FROM tb
JOIN tb1
ON tb.bang=tb1.bang;
□【実行結果】
mysql> SELECT tb.bang,tb1.nama,tb.uria -> FROM tb -> JOIN tb1 -> ON tb.bang=tb1.bang; +------+------+------+ | bang | nama | uria | +------+------+------+ | A103 | 中川 | 101 | | A102 | 高橋 | 54 | | A104 | 渡辺 | 181 | | A101 | 佐藤 | 184 | | A103 | 中川 | 17 | | A101 | 佐藤 | 300 | | A102 | 高橋 | 205 | | A104 | 渡辺 | 93 | | A103 | 中川 | 12 | +------+------+------+ 9 rows in set (0.00 sec) mysql> |
(2-3)<テーブル名にエイリアスを使う>
エイリアスは、テーブル名に設定することもできます。方法はカラムに付けるときと同じ。
●書式⇒テーブルにエイリアスを付ける
テーブル名 AS エイリアス ※たとえば次は、テーブル「tb」に「x」というエイリアスを付けた例です。 SELECT * FROM tb AS x ; ※テーブルにエイリアスを付けるメリットは、テーブルを結合するときに現わします。 実際に使われるテーブルの多くは、「Kig_uriage2008_2」のような複雑なものです。 複数のテーブルを結合する場合、必ず「正式なテーブル名.カラム名」という記述が必要です。 しかし、「Kig.uriage2008_2.uria」は、「x.uria」等で記述します。 それでは、売上情報テーブル「tb」の社員番号と売上、そして社員情報テーブル「tb1」の名前を選んで、わかりやすくエイリアスも付けて表示してみます。 テーブル「tb」の「bang」、「tb1」のカラム「bang」が一致するレコードを結合し、「tb」の「bang」、「tb1」の「nama」、「tb」の「uria」のカラムを付けて実行しましょう。 |
●実行される内容 p193
●操作手順
①次を実行する SELECT x.bang, y.nama, x.uria FROM tb as x JOIN tb1 as y ON x.bang=y.bang; |
□【実行結果】
mysql> SELECT x.bang, y.nama, x.uria -> FROM tb as x -> JOIN tb1 as y ON x.bang=y.bang; +------+------+------+ | bang | nama | uria | +------+------+------+ | A103 | 中川 | 101 | | A102 | 高橋 | 54 | | A104 | 渡辺 | 181 | | A101 | 佐藤 | 184 | | A103 | 中川 | 17 | | A101 | 佐藤 | 300 | | A102 | 高橋 | 205 | | A104 | 渡辺 | 93 | | A103 | 中川 | 12 | +------+------+------+ 9 rows in set (0.00 sec) mysql> |
(2-4)<USINGを使ってON~の部分を見やすくするp194>
ここまでの例では、社員番号のカラム「bang」を結合の条件として「tb.babg=tb1.bang」を指定しました。キーが両方とも「bang」というカラムでした。
ここで、カラムが同じである必要はありません。キーとなる内容さえ同じであれば、例えば「tb1」の「bang」のカラム名が「bangou」であってかまわないのです。
この場合、単に「ON tb.bang=tb1.bangou」となるだけです。
この例では、たまたま「bang」という同じカラム名になっています。このように、同じカラム名で指定する場合は、USING(キーとなるカラム名)と簡単に記述することができます。
※これを使うと、次のように見やすくなります。
SELECT tb.bang,tb1.nama,tb.uria
FROM tb
JOIN tb1
JOIN tb1
USING(bang);
(2-5)<結合したテーブルから「WHERE」で設定した条件で抽出するp195>
結合したテーブルから「WHERE」で条件を設定して、該当したデータだけを表示してみよう。最後に「WHERE」の条件を付けるだけです。カラム名は必ず「テーブル名.カラム名」のようにテーブル名を付けて記述します。
もし、テーブルにエイリアスが設定されている場合は「エイリアス.カラム名」と記述することもできます。
売上情報テーブル「tb」で、100以上の優秀な売上だけに限定して、社員情報テーブル「tb1」の氏名と合わせて表示します。さらに、表示する項目にもエイリアスを付けでみます。
※具体的には、テーブル「tb」にテーブル「tb1」を結合し、テーブル「tb」のカラム「uria」の値が100以上のレコードを表示します。表示するにはカラム「bang」「nama」「uria」で、それぞれに「社員情報」「氏名」「売上」のエイリアスをエイリアスを付けましょう。
●実行される内容 p195
●操作手順
SELECT tb.bang AS '社員番号',tb1.nama AS '氏名',tb.uria AS '売上' FROM tb JOIN tb1 USING(bang) WHERE tb.uria >= 100; |
□【実行結果】
mysql> SELECT tb.bang AS '社員番号',tb1.nama AS '氏名',tb.uria AS '売上' -> FROM tb -> JOIN tb1 -> USING(bang) -> WHERE tb.uria >= 100; +----------+------+------+ | 社員番号 | 氏名 | 売上 | +----------+------+------+ | A103 | 中川 | 101 | | A104 | 渡辺 | 181 | | A101 | 佐藤 | 184 | | A101 | 佐藤 | 300 | | A102 | 高橋 | 205 | +----------+------+------+ 5 rows in set (0.00 sec) mysql> |
(2-6)<たくさんのテーブルのデータを使って抽出する>
「JOIN」するテーブルは1つだけ、という制限はありません。2つでも3つでも結合することが可能です。「SELECT~JOIN~ON~」でさらに「JOIN~ON~」を記述して結合していきます。ただし、たくさんのテーブルを結合すればそれだけ処理に時間がかかり、記述も難関になります。2つ以上のテーブルを内部結合する場合は、次のようにします。
●書式⇒複数のテーブルを内部結合する
SELECT ~ FROM テーブル名1 JOIN テーブル名2 結合条件 JOIN テーブル名3 結合条件 .... .... ; ※ここで、D株式会社の社員の「出身県」の情報が、次のようなテーブル「tb3」にあるとします。テーブル「tb3」は次のように、社員番号「bang」と出身都道府県を表すカラム「ken」から構成されます。 このテーブル「tb3」を用意してください。 |
テーブル「tb3」 p197
※社員番号「A101」の「佐藤」さんは「東京都」出身だったわけです。 テーブル「tb」「tb1」「tb3」には、共通の値を持ったカラム「bang」があります。 では、カラム「bang」をキーにして、売上情報テーブル「tb」に社員情報テーブル「tb1」と社員出身県テーブル「tb3」を結合し、「社員番号」(テーブル「tb」の「bang」)、「出身県」(テーブル「tb3」の「ken」)の3つを表示してみましょう。 |
●実行される内容 p198
●操作手順
①次を実行する SELECT tb.bang,tb.uria,tb1.nama,tb3.ken FROM tb JOIN tb1 USING(bang) JOIN tb3 USING(bang) ; |
□【実行結果】
mysql> SELECT -> tb.bang,tb.uria,tb1.nama,tb3.ken -> FROM -> tb -> JOIN -> tb1 -> USING(bang) -> JOIN -> tb3 -> USING(bang) -> ; +------+------+------+----------+ | bang | uria | nama | ken | +------+------+------+----------+ | A103 | 101 | 中川 | 神奈川県 | | A102 | 54 | 高橋 | 埼玉県 | | A104 | 181 | 渡辺 | 北海道 | | A101 | 184 | 佐藤 | 東京都 | | A103 | 17 | 中川 | 神奈川県 | | A101 | 300 | 佐藤 | 東京都 | | A102 | 205 | 高橋 | 埼玉県 | | A104 | 93 | 渡辺 | 北海道 | | A103 | 12 | 中川 | 神奈川県 | +------+------+------+----------+ 9 rows in set (0.08 sec) mysql> ※結合の条件には、共通の「bang」を使って「USING(bang)」としましたが、「ON」を使って 「ON tb.bang=tb1.bang」 「ON tb.bang=tb3.bang」とすると。次のようになります。 SELECT tb.bang,tb.uria,tb1.nama,tb3.ken FROM tb JOIN tb1 ON tb.bang=tb1.bang JOIN tb3 ON tb.bang=tb3.bang ; |
(3)<複数のテーブルのすべてのレコードを表示する(外部結合)p200>
(3-1)<外部結合とは>
さて、ここまで2つのテーブル「tb」と「tb1」を中心に使って結合の説明してきました。
●もう一度テーブルの内容を確認してみます。 p200
※前項では、社員番号「bang」をキーにして、この2つのテーブルを「内部結合」しました。その時に、気になるのが1つ残っていました。実はテーブル「tb」には1人、テーブル「tb1」の社員一覧に載っていない人物「bang=A107」さんがいます。
以下は、前項の結果です。内容をもう一度確認してみましょう。 SELECT x.bang,y.nama,x.uria FROM tb as x JOIN tb1 as y ON x.bang=y.bang; |
●tb(売上テーブル) p200_2
※実は「A107」は別の営業所の社員なので、テーブル「tb1」にはデータはありません。 そのため、「JOIN」で結合してもそのレコードは表示されません。さらに、「tb1」の社員名簿には「A105」の西沢さんの情報がありますが、売上一覧にその情報がありません。 つまり、名簿には記載されているものの、売上がないということらしく、「A105」のデータも表示されないのです。 でも、「レコードがあるんだから表示させなきゃ困る!」ということもあります。 このような場合には、外部結合を使います。外部結合は、以下の特徴があります。 「外部結合」は、一致していなくても、一方のテーブルのレコードはすべて取り出す。 |
(3-2)<外部結合の種類>
外部結合には、結合するときにどちらのテーブルのレコードをすべて書き出すかによって、
以下の2つの種類があります。
①左外部結合(LEFT JOIN)
「一致したレコード」および「テーブル1(左側に相当)の全データ」を表示します。
②右外部結合(RIGHT JOIN)
「一致したレコード」および「結合するテーブル2(右側に相当)の全データ」を表示します。
【図10-3 左外部結合(LEFT JOIN)省略】
【図10-4 右外部結合(RIGHT JOIN)省略】
(3-3)<左側外部結合を使うp202>
売上情報テーブル「tb」に売上実績があるのに、社員情報テーブル「tb1」に載っていない「A107」さんのレコードを含めて表示してみましょう。 ※左外部結合にするときには、内部結合での記述「JOIN」を「LEFT JOIN」に変えるだけです。
●書式⇒左外部結合
SELECT カラム名 FROM テーブル1 LEFT JOIN 結合するテーブル名2 ON テーブル1のカラム=テーブル2のカラム; ※「bang」をきーにして、テーブル「tb」と「tb1」で一致したレコードと、テーブル「tb」のすべてのレコードを「左外部結合」で表示しましょう。ただし、表示するテーブル「tb」の「bang」と「tb1」の「nama」です。 |
●実行される内容 p203
画像】 |
●操作手順
①次を実行する SELECT tb.bang,tb1.nama FROM tb LEFT JOIN tb1 USING(bang) ; |
□【実行結果】
mysql> SELECT -> tb.bang,tb1.nama -> FROM -> tb -> LEFT JOIN -> tb1 -> USING(bang) -> ; +------+------+ | bang | nama | +------+------+ | A103 | 中川 | | A102 | 高橋 | | A104 | 渡辺 | | A101 | 佐藤 | | A103 | 中川 | | A101 | 佐藤 | | A102 | 高橋 | | A104 | 渡辺 | | A103 | 中川 | | A107 | NULL | +------+------+ 10 rows in set (0.00 sec) mysql> ※左側に相当するテーブル「tb」だけにある「A107」さんのレコードが表示されます。 そして、右側に相当するテーブル「tb1」だけにある「A105」の「西沢」さんのデータは表示されません。 |
(3-4)<右外部結合を使うp205>
今度は逆に、「一致したレコード」と「結合された右側テーブルの全データ」を表示する「右外部結合」の例です。
単に、RIHT? JOINを返すだけでOKです。
●書式⇒右外部結合
SELECT カラム名 FROM テーブル1 RIGHT JOIN 結合するテーブル2 ON テーブル1のカラム=テーブル2のカラム; |
●実行される内容 p205
画像】 |
●操作手順
①次を実行する SELECT tb.bang,tb1.nama FROM tb RIGHT JOIN tb1 USING(bang) ; |
□【実行結果】
mysql> SELECT -> tb.bang,tb1.nama -> FROM -> tb -> RIGHT JOIN -> tb1 -> USING(bang) -> ; +------+------+ | bang | nama | +------+------+ | A101 | 佐藤 | | A101 | 佐藤 | | A102 | 高橋 | | A102 | 高橋 | | A103 | 中川 | | A103 | 中川 | | A103 | 中川 | | A104 | 渡辺 | | A104 | 渡辺 | | NULL | 西沢 | +------+------+ 10 rows in set (0.00 sec) mysql> |
(3-5)<(注意)左外部結合と右外部結合の混在は避ける>
外部結合を使う場合、重要な注意点があります。同じデータベースの中で、左外部結合と左外部結合を混在することもできます。また、右か左を選ぶ代わりに、結合するテーブルを逆にすることも可能です。 ※しかし、左外部結合と右外部結合の両方が存在すると、のちのちに間違いを引き起こす原因になりかねません。従って、両方を混在させるべきでない。
(4)自己結合
(4-1)自己結合とは
テーブルは自分自身、つまり同じ名前のテーブルを結合することができます。 これを自己結合といいます。テーブルに同じ名前のテーブルを結合することで、単純に実行すると同じカラム名が2つ表示されてしまいます。これはカラムが識別できない(エラーになる)ので、結合するときには必ずエイリアスを付けます。
●書式⇒自己結合
SELECT カラム名 FROM テーブル名 as エイリアス1 JOIN テーブル名 AS エイリアス2; ※たった1つの同じテーブルに、2つのエイリアス(別名)を付けます。テーブルは1つなのに、名前だけ2つある状態です。これを実行すると、大変なことになります。社員情報テーブル「tb1」を自己結合して、すべてのカラムを表示してみましょう。 |
●実行される内容 p208
画像】 |
●操作手順
SELECT * FROM tb1 AS a JOIN tb2 AS b; |
【実行結果】
mysql> SELECT * -> FROM tb1 -> AS a -> JOIN tb2 -> AS b; +------+------+------+------+------+------+ | bang | nama | tosi | bang | nama | tosi | +------+------+------+------+------+------+ | A101 | 佐藤 | 40 | A106 | 中村 | 26 | | A102 | 高橋 | 28 | A106 | 中村 | 26 | | A103 | 中川 | 20 | A106 | 中村 | 26 | | A104 | 渡辺 | 23 | A106 | 中村 | 26 | | A105 | 西沢 | 35 | A106 | 中村 | 26 | | A101 | 佐藤 | 40 | A107 | 田中 | 24 | | A102 | 高橋 | 28 | A107 | 田中 | 24 | | A103 | 中川 | 20 | A107 | 田中 | 24 | | A104 | 渡辺 | 23 | A107 | 田中 | 24 | | A105 | 西沢 | 35 | A107 | 田中 | 24 | | A101 | 佐藤 | 40 | A108 | 鈴木 | 23 | | A102 | 高橋 | 28 | A108 | 鈴木 | 23 | | A103 | 中川 | 20 | A108 | 鈴木 | 23 | | A104 | 渡辺 | 23 | A108 | 鈴木 | 23 | | A105 | 西沢 | 35 | A108 | 鈴木 | 23 | | A101 | 佐藤 | 40 | A109 | 村井 | 25 | | A102 | 高橋 | 28 | A109 | 村井 | 25 | | A103 | 中川 | 20 | A109 | 村井 | 25 | | A104 | 渡辺 | 23 | A109 | 村井 | 25 | | A105 | 西沢 | 35 | A109 | 村井 | 25 | | A101 | 佐藤 | 40 | A110 | 吉田 | 27 | | A102 | 高橋 | 28 | A110 | 吉田 | 27 | | A103 | 中川 | 20 | A110 | 吉田 | 27 | | A104 | 渡辺 | 23 | A110 | 吉田 | 27 | | A105 | 西沢 | 35 | A110 | 吉田 | 27 | +------+------+------+------+------+------+ 25 rows in set (0.09 sec) mysql> 「佐藤」さんのレコードに「佐藤」「高橋」「中川」「渡辺」「西沢」が付き、さらに「高橋」さんに「佐藤」「高橋」「中川」「渡辺」「西沢」がくっ付くと、レコード数×レコード数という、恐ろしい数が結合してしまいます。 ※この例では5レコードなので5×5=25ですが、もし1000件を超えるようなレコードだったら?・・・と思うと、自己結合なんて無謀なことのように思えてきます。 ※でも、決して無謀ではありません、データベースの世界では、こうした力技のような手法が、当たり前のように使われているのです。 |
(4-2)<順位付けの妙技 その1>
テーブルに、まったく同じテーブルを結合させることで、いったい何が出来るのでしょうか? ※少なくても自己結合させた結果には、すべての組合せが存在します。ですから、求める組合せがそこに存在するのでしたら、後は条件を設定して必要なものを選べばよいのです。 ※データベースにおける順位は、「ORDER」や「GROUP」などを組み合わせて処理をしないと出せない、やっかいな存在です。
●自己結合の確認
ここでは、社員情報テーブル「tb1」で年齢「tosi」の大きいものからの順位を付けてみることにしましょう。 【●テーブル「tb1」を「自己結合」で表示(抜粋)省略】 ※たとえば右側の「佐藤40」に対して、左側に「佐藤40」「高橋28」「中川20」「渡辺23」「西沢35」のテーブルが並んでいる5行に注目してください。この5行の中で、右側の「佐藤40」の40以上である左側の数値は40のたった1つです。「40以上」というのは40も含みますから。佐藤さんの順位は1番です。 つまり、「自己結合」して、左側の「tosi」が右側の「tosi」以上になっている行の数を「bang」のグループごとに数えればよいことになります。 数は「COUNT(*)」で数えます。SQL文を考えてみましょう。 |
●テーブル構造 p212
●操作手順
①次を実行する SELECT a.nama,a.tosi,COUNT(*) FROM tb1 AS a JOIN tb1 AS b WHERE a.tosi<=b.tosi GROUP BY a.bang; |
□【実行結果】
mysql> SELECT a.nama,a.tosi,COUNT(*) -> FROM tb1 AS a -> JOIN tb1 AS b -> WHERE a.tosi<=b.tosi -> GROUP BY a.bang; +------+------+----------+ | nama | tosi | COUNT(*) | +------+------+----------+ | 佐藤 | 40 | 1 | | 高橋 | 28 | 3 | | 中川 | 20 | 5 | | 渡辺 | 23 | 4 | | 西沢 | 35 | 2 | +------+------+----------+ 5 rows in set (0.00 sec) mysql> ※まず、自己結合なので基本は「SELECT * FROM tb1 AS a.JOIN tb1 b;」です。 これはWHEREの条件「a.tosi<=b.tosi」を設定します。すると「a.bang」のグループごとに、「a.tosi」以上の「b.tosi」であるレコードが抽出され、この個数を「COUNT(*)」で数えれば順位になるということです。 |
(5)《SELECTしたレコードからSELECTする(サブクエリ)p213》
(5-1)サブクエリを使えるバージョン
サブクエリを使えるのはMySQLのバージョン4.1以降です。
(5-2)サブクエリとは
サブクエリは「副問い合わせ」ともいいます。サブクエリを使うと、「クエリを実行して取り出されたデータを使って、さらに、クエリを発行する」という、2段階の処理が可能になります。※例えば1段階で「売上テーブルで売上200以上という社員番号を抽出」し、さらに2段階目で「抽出された社員番号に該当する氏名を抽出する」というような処理です。1段階目のクエリを「サブクエリ」と呼んでいます。
1段階目のサブクエリが返すのは、値であったり、カラムであったり、レコードであったりさまざまです。
【図10-5 サブクエリ 省略】
(5-3)<最大の値を持つものを表示 --値を返すサブクエリ その1-->
たとえば次のようなケースは、いったいどうやって処理したらよいのでしょうか?
●テーブル「tb」で、カラム「uria」が最大値であるレコードを表示する
「売上情報テーブルtbで、最も売上の高い(uriaが最大)の社員データを表示」しろ、
という問題です。次のテーブル「tb」の構造を見て、ちょっと考えてみましょう。
●テーブル「tb」 p214
【図10-6 サブクエリの流れ 省略】
※GROUP BY関数
MAXやAVG、SUMなどの集計を行なう関数は、「GROUP BY関数」とも呼ばれ、本来はグループ化したときの値を処理するものです。ただし、「GROUP BY…」がない場合は、テーブル全体を1つのグループとして処理が行なわれます。
●実行される内容 p216
画像の誤り |
●操作手順
①次を実行する SELECT * FROM tb WHERE uria IN (SELECT MAX(uria) FROM tb); |
□【実行結果】
mysql> SELECT * -> FROM tb -> WHERE uria -> IN (SELECT MAX(uria) FROM tb); +------+------+------+ | bang | uria | tuki | +------+------+------+ | A101 | 300 | 5 | +------+------+------+ 1 row in set (0.00 sec) mysql> |
(5-4)<平均以上のレコードを抽出するーー値を返すサブクエリ その2-->
前項でMAX関数を使ったサブクエリを紹介しました。もう1つ、関数を使った例を考えてみましょう。社員情報テーブル「tb1」に登録されている社員の平均年齢を出し、平均年齢以上の1段階目では、テーブル「tb1」でのカラム「tosi」の平均を「AVG」関数を使って出します。 さらに、2段階目で、この平均値以上の「tosi」であるレコードを抽出することになります。 それでは、テーブル「tb1」での年齢(tosi)の平均値を出し、この値以上の年齢(tosi)のレコードを表示してみましょう。
●実行される内容 p217
●操作手順
①次を実行する SELECT * FROM tb1 WHERE tosi >=(SELECT AVG(tosi) FROM tb1); |
□【実行結果】
mysql> SELECT * -> FROM tb1 -> WHERE tosi -> >=(SELECT AVG(tosi) FROM tb1); +------+------+------+ | bang | nama | tosi | +------+------+------+ | A101 | 佐藤 | 40 | | A105 | 西沢 | 35 | +------+------+------+ 2 rows in set (1.35 sec) mysql> |
(5-5)<INを使う --カラムを返すサブクエリーー>
今度は、サブクエリがカラムを返す例です。1段階目のサブクエリで条件に一致したカラムを返し、そのカラムを含むものを2段階目で抽出します。この場合、次のように「IN」を使います。
●書式⇒サブクエリの構文
SELECT 表示するカラム FROM テーブル名 WHERE カラム名 IN (SELECTによるサブクエリでカラムを抽出); ※1段階目のクエリ(サブクエリ)を、必ず()で囲んで記述します。1段階目の抽出を行い、その値をカラムを含む(IN)ことを条件として、2段階目の抽出を実行します。 ※サブクエリを使って、テーブル「tb」で売上(uria)が200以上の「bang」を抽出し、テーブル「tb1」でその該当するレコードを表示してみましょう。 |
●実行される内容 p219
画像】 |
●操作手順
SELECT * FROM tb1 WHERE bang IN(SELECT bang FROM tb WHERE uria>=200); |
□【実行結果】
mysql> SELECT * -> FROM tb1 -> WHERE bang -> IN(SELECT bang FROM tb WHERE uria>=200); +------+------+------+ | bang | nama | tosi | +------+------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | +------+------+------+ 2 rows in set (0.00 sec) mysql> |
(5-6)<INの代わりに「=」を使うとエラーになる?>
※省略
(5-7)<EXISTSで、存在するレコードだけを対象にするp223>
前項で学んだサブクエリは、「WHERE~IN(SELECT・・・)」のようなタイプでした。 これは、1段階目のサブクエリが該当するカラムのデータを返すものです。たとえば、「A101,A102のカラム」というカラムのデータが返され、その値を含むレコードを対象に抽出が行なわれました。※これに対して、特定のカラムはなく1段階目のサブクエリが「対象となるレコードが存在する」という情報を返すのがEXISTSです。 ※実例を見てみることにしましょう。社員情報テーブル「tb1」には、売上情報テーブル「tb」にその社員番号がないレコードも含まれています。これは「A105」のレコードです。
●実行される内容 p223
※つまり、「A105」の西沢さんは、売上がないということです。では、「売上のある社員の、社員情報テーブルのレコード」を、サブクエリを使って表示してみましょう。具体的には、次のように処理します。 (テーブル「tb」に存在するレコードを抽出し、テーブル「tb1」から該当するレコードを表示) ※ます、社員情報テーブル「tb1」のレコードを表示ということなので、 □SELECT * FROM tb1 が基本となります。次に「売上のある社員だけ」というのは、「tb」と「tb1」の両方に「bang」が存在するとことなので □WHERE * tb.bang=tb1.bang が条件となります。「1段階目」として、この条件に合うレコードを「tb」から選びます。 □SELECT * FROM tb WHERE tb.bang=tb1.bang これは、特定の「カラム」でなく、条件に合う「レコード」を抽出しているわけです。さらに「EXISTS」を使って「2段階目」としてこのレコードが存在しているものだけテーブル「tb1」から抽出します。結局次のようになります。 |
●操作手順
①次を実行する SELECT * FROM tb1 WHERE EXISTS (SELECT * FROM tb WHERE tb.bang=tb1.bang); |
●実行結果
mysql> SELECT * -> FROM tb1 -> WHERE EXISTS -> (SELECT * FROM tb WHERE tb.bang=tb1.bang); +------+------+------+ | bang | nama | tosi | +------+------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | +------+------+------+ 4 rows in set (0.00 sec) mysql> |
(5-8)<今度はNOTEXISTS p225>
逆に「NOT EXISTS」は、サブクエリによって抽出されないレコードを対象にして処理を行います。前項の逆に「売上のない社員だけ」のレコードを、サブクエリを使って教示してみましょう。これは、前項の「EXISTS」を「NOT EXISTS」に変えるだけです。 NOT EXISTSを使って、テーブル「tb」に存在しないレコードの「bang」だけ抽出し、テーブル「tb1」で該当するレコードを表示しよう。
●実行される内容 p225
画像】 |
●操作手順
①次を実行する SELECT * FROM tb1 WHERE NOT EXISTS (SELECT * FROM tb WHERE tb.bang=tb1.bang); |
□【実行結果】
mysql> SELECT * -> FROM tb1 -> WHERE NOT EXISTS -> (SELECT * FROM tb WHERE tb.bang=tb1.bang); +------+------+------+ | bang | nama | tosi | +------+------+------+ | A105 | 西沢 | 35 | +------+------+------+ 1 row in set (0.00 sec) mysql> |
(5-9)<順位付けの妙技 その2>p226
RDBMSで順位を入力するのはけっこう面倒な処理である・・・と自己結合で学びました。
ところが、サブクエリを使うと、いろんな方法で順位付けが出来るのです。しかも自己結合でやるよりも、わかりやすくなります。ここではサブクエリを使った順位付けの方法をご紹介します。
※売上情報テーブル「tb」の、売上の順位を付けます。今回は別のテーブルを用意し、順位をカラムに入力します。次の考え方で、順位を付けます。
a.連続番号機能を付けたテーブルに、「uria」の順に並べたレコードを挿入。
b.自動的に入力される連続番号が順位になる。
具体的には次の手順で処理します。
①テーブル「tb」と同じ構造のテーブル「tb_zyun」を作成
②テーブル「tb_zyun」に連続番号機能のあるカラム「zyun」を追加
③テーブル「tb」を「uria」の順に「SELECT」するサブクエリを実行
④サブクエリの結果をテーブル「tb_zyun」に「INSERT」
テーブル「tb_zyun」のカラム「zyun」に順位を入力します。
さて、順位を入れるクエリは具体的にはどうなるでしょうか?
連続番号機能とサブクエリを使う方法を考えて見ましょう。
※テーブル「tb」は「bang」「uria」「tosi」からできています。これにカラム「zyun」を加えたテーブル「tb_zyun」を作成し、テーブル「tb」のレコードをコピーします。そしてカラム「zyun」に「uria」の順位を入力してみましょう。
●実行される内容 p227
画像】 |
●操作手順
①次を実行する CREATE TABLE tb_zyun LIKE tb; ②次を実行する ALTER TABLE tb_zyun ADD zyun INT AUTO_INCREMENT PRIMARY KEY; ③次を実行する INSERT INTO tb_zyun (bang,uria,tuki) (SELECT bang,uria,tuki FROM tb ORDER BY uria DESC); ※上記①~③は次のような処理を行なっています。 ①テーブル「tb」のカラム構造だけをコピーして、テーブルtb_zyunを作成 ②テーブル「tb_zyun」に、連続番号機能を持つカラム「zyun」を加える ③サブクリエを使い、テーブル「tb」をカラム「uria」の降順に並べ替え、 「bang」「uria」「tuki」を「tb_zyun」に挿入 ※③で、カラム「zyun」には連続番号が自動的に入力され、これが順位になります。 |
□【実行結果】
①mysql> CREATE TABLE tb_zyun LIKE tb; Query OK, 0 rows affected (0.29 sec) ②mysql> ALTER TABLE tb_zyun ADD zyun INT AUTO_INCREMENT PRIMARY KEY; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 ③mysql> INSERT INTO tb_zyun -> (bang,uria,tuki) -> (SELECT -> bang,uria,tuki -> FROM tb -> ORDER BY uria DESC); Query OK, 10 rows affected (0.10 sec) Records: 10 Duplicates: 0 Warnings: 0 ④mysql> SELECT * FROM tb_zyun; +------+------+------+------+ | bang | uria | tuki | zyun | +------+------+------+------+ | A101 | 300 | 5 | 1 | | A102 | 205 | 6 | 2 | | A101 | 184 | 4 | 3 | | A104 | 181 | 4 | 4 | | A103 | 101 | 4 | 5 | | A104 | 93 | 5 | 6 | | A107 | 87 | 6 | 7 | | A102 | 54 | 5 | 8 | | A103 | 17 | 5 | 9 | | A103 | 12 | 6 | 10 | +------+------+------+------+ 10 rows in set (0.00 sec) mysql> |
<まとめ>
●複数のテーブルを合わせて表示する方法 ●複数のテーブルを内部結合して表示する方法 ●内部結合と外部結合の違い ●サブクエリによる2段階の抽出
<チェック>
□UNIONを使って、複数のテーブルのデータを合わせてSELECTできる □キー指定して内部結合することで、複数のテーブルからSELECTできる □左外部結合・右外部結合により、複数のテーブルの必要なデータをSELECTできる □サブクエリの意味を理解している □サブクエリによって得た値を条件にして、さらに抽出ができる
<練習問題>
問題1
テーブル「tb」で、「uria」の少ないものから順位を付け、1番から順に、「bang」、「uria」、順位を表示させて下さい。
問題2
次のテーブル「tb1」および「tb」で、テーブル「tb1」に存在するレコードだけテーブル「tb」から抽出して下さい。
●実行される内容 p230
《解答1》
問題1
次を実行します
SELECT a.bang,a.uria,COUNT(*)
FROM tb AS a
JOIN tb AS b
WHERE a.uria>=b.uria
GROUP BY a.uria;
●実行結果
mysql> SELECT a.bang,a.uria,COUNT(*) -> FROM tb AS a -> JOIN tb AS b -> WHERE a.uria>=b.uria -> GROUP BY a.uria; +------+------+----------+ | bang | uria | COUNT(*) | +------+------+----------+ | A103 | 12 | 1 | | A103 | 17 | 2 | | A102 | 54 | 3 | | A107 | 87 | 4 | | A104 | 93 | 5 | | A103 | 101 | 6 | | A104 | 181 | 7 | | A101 | 184 | 8 | | A102 | 205 | 9 | | A101 | 300 | 10 | +------+------+----------+ 10 rows in set (0.20 sec) mysql> |
《解答2》
問題2
次を実行します
SELECT *
FROM tb
WHERE EXISTS
(SELECT * FROM tb1 WHERE tb.bang=tb1.bang);
【実行結果】
mysql> SELECT * -> FROM tb -> WHERE EXISTS -> (SELECT * FROM tb1 WHERE tb.bang=tb1.bang); +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 101 | 4 | | A102 | 54 | 5 | | A104 | 181 | 4 | | A101 | 184 | 4 | | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A104 | 93 | 5 | | A103 | 12 | 6 | +------+------+------+ 9 rows in set (0.05 sec) mysql> ※なお、テーブル「tb1」に「A107」が存在しないため、「A107」は表示されません。 |